Server Archives | CDW Solutions Blog

As we continue to discuss the looming end of support for SQL Server 2005, I want to share simple steps to upgrade a 2005 database to SQL Server 2014. By no means is this list complete, but my goal is to touch on the high-level points to plan and perform a successful upgrade. If you missed the first two posts on SQL Server 2005 end of support, you can find those here: What does SQL Server 2005 End of Life Mean for Your Business? The Clock is Ticking: Migration Options for SQL Server 2005 End of Life Planning Review What Has Changed First, you need to review what has changed with each versioned release to understand how those changes could affect your application. This information can be found here. Below is a breakdown of the categories of each change: Discontinued Features: Features that are no longer available Deprecated Features: Features that will be removed in a future release Breaking Changes: Database engine changes that will affect applications Behavioral Changes: Database engine changes that could affect applications Run the SQL Server Upgrade Advisor The SQL Server Upgrade Advisor is a great tool provided by Microsoft to help you understand what needs to be changed within the database before and after migration. The Upgrade Advisor will capture the vast majority of issues to help make your migration successful, but keep in mind, it is not a silver bullet for every detail. Be sure to review all the breaking and behavioral changes mentioned above. Back Up the Database Protect yourself and your data by taking a full backup of the database prior to any migration. This way you have the ability to recover the database in the event anything goes sideways with the migration. Migration With planning work complete, we can now move on to the migration. Each scenario below assumes there is a shiny new SQL Server 2014 instance as the migration target. In-place upgrades should typically be avoided as they can be very risky with minimal rollback options. With that said, there are few ways we will discuss to migrate a database to SQL Server 2014: Backup and Restore This is the safest and simplest migration, as a full database backup can be restored to the 2014 instance without affecting the 2005 instance, leaving us with a stable rollback option. To minimize downtime, the 2005 instance can remain online while the full backup is restored to the 2014 instance. Once the restore of the full backup has completed and we are ready to migrate, a differential backup can be used to further synchronize the 2014 instance. At this point you’ll need a maintenance window to take the 2005 instance database offline, take a final transaction log backup on the 2005 instance and restore the transaction log backup to the 2014 instance. Now that the databases are fully synchronized we can point the application to the 2014 instance and validate. This approach helps to mitigate risk as there is a simple rollback to the 2005 instance if anything goes wrong during the migration window. Detach and Attach Another simple migration option is to detach the database from the 2005 instance and then attach the database to the 2014 instance. This is a good option if your storage system is not changing and your database size might make it prohibitive to consume storage for duplicate copies of multiple databases. (Please note: This won’t allow for a quick rollback option if something goes wrong with the migration. You can restore your initial backup before migration to the 2005 instance, but that will take time depending on the size of the database.). Server Objects Be sure to identify and migrate server logins, SSIS packages, SQL Jobs, and linked servers. This can be a great time to clean up and carry forward only the needed server level objects. For assistance with identifying and scripting logins and roles for a migration see here. But what about migrating DTS packages? Unfortunately, DTS is a discontinued feature that is no longer supported, which means all DTS packages need to be converted to SSIS packages moving forward. Microsoft shipped a DTS Package Migration Wizard in SQL Server 2008 and 2008 R2 but it only worked for very simple packages, resulting in rewrites for most packages. DTS xChange is a Microsoft-recommended third-party tool and available from Pragmatic Works. While this tool does have an associated cost, it is more successful at converting DTS packages to SSIS packages and can help avoid long hours rewriting packages manually. Post Migration After the migration is complete it is important to perform the following tasks: Upgrade Database Compatibility Level The database compatibility level needs to be manually changed on the 2014 instance Run DBCC CHECKDB WITH DATA_PURITY Checks for invalid data values based on column data type Run DBCC UPDATEUSAGE Corrects page count inaccuracies to reflect accurate information when using sp_spaceused Run sp_refreshview Ensures that views are up to date Update Statistics Brings database statistics are up to date to allow the query optimizer to choose the best query plan Take a Full Backup of the Database Make sure you can recover the database with all of the migration efforts Monitor Performance between Cardinality Estimators It’s important to note: SQL Server 2014 received a major overhaul of the cardinality estimator. For the vast majority of queries, performance will improve due to better row estimations. However, in some scenarios estimations could be worse which may result in performance degradation. It’s important to monitor most commonly used queries to see if any performance degradation is resulting from poor estimations. It is possible to utilize the old cardinality estimator by leveraging a database compatibility level of 110 or lower or by leveraging trace flag 9481. This white paper discusses this topic in further depth: Additional Resources As I mentioned, this is a high-level overview of important considerations when performing an upgrade. For more details, please see this white paper from Microsoft: How Can CDW Help? CDW has experts to help you modernize your SQL Server environment. Through our planning and design engagements we can recommend the best approach for upgrade, migration and consolidation to ensure your future state environment will meet your business requirements. For more information, check out our SQL Server 2005 Migration Data Sheet, or see the CDW SQL Server homepage and ask your CDW Account Manager for assistance.

Explore CDW.com

Topics

Tech Tips: Migrating a SQL Server 2005 Database to 2014

April 12, 2016 marks the end of support for SQL Server 2005 — is your business ready?

IBM Building Simplicity into PureSystems

Most Popular

Prioritize These Security Considerations as Users Work from Home More Frequently

8 Things to Consider Prior to Deploying Microsoft Teams

Why Adoption Services Are More Important Than Ever

5G Is the Future of the Internet of Things

Overcoming the Cybersecurity Skills Gap with Automation

Topics

Topics

Most Popular

Recommended for You

Topics

Connect With CDW