1  Introduction

It is always important to adopt latest technologies to improve business and achieve better results. With technology constantly evolving, there is a need to upgrade/migrate to the newer. This wiki is meant to help readers with the tasks and processes of migration of the SQL Server Database engine. The migration process can be split into two sub-parts: infrastructure and software. This wiki primarily focuses on the software portion of this migration.

Since the task in its entirety is so mammoth and involves many intricacies, even though this may not be a complete reference for the same, it aspires to be a starting point for the migration, to give readers a general idea for the process that they can follow.

Database migration from SQL Server 2005 to SQL Server 2016 requires the right amount of planning for each phase of the migration. This wiki will primarily focus on the Database Engine Migration, for any further references regarding the migration please refer to Upgrade to SQL Server 2016.

For additional references for 2005 to 2016 migration, and the inter-compatibility, between these two versions, and some known limitations please refer to SQL Server 2016 Support for SQL Server 2005.

To find additional resources and factors concerning the planning of your upgrade visit Plan your upgrade.

The steps for migration are listed below:

2 Discover your DB Environment

Evaluating your current Database environment, taking inventory of the features you would like to migrate is the first critical step. Amongst your inventory, identifying the types of your databases, based on factors such as their Recovery Time Objectives (RTOs), mission criticality and latency tolerance.

Check Discontinued Features
While migrating from SQL Server 2005 to SQL Server 2016, it is important to realize that the customer needs to check for discontinued features listed for all versions from SQL Server 2008 through SQL Server 2016 which can be found at Discontinued Database Engine Functionality in SQL Server 2016.

3 Target Destinations for each Application and Workload

During the migration, dependencies of your applications need to be re-evaluated to get the best ROI (Return on Investment). Especially since now, Microsoft provides support for JSON and R integration. This will help to get more out of underutilized resources.  However, each additional integration also needs proper evaluation as it may be taking away compute resource cycles which the database engine may need.

Type of destination environment
When moving from legacy hardware, it is possible to consolidate multiple workloads on separate servers onto less number of target environment servers. This not only enables cost saving in terms of power consumption, but also makes administration and management tasks easier.
Moreover, keeping in mind the above mentioned factors it becomes crucial to assess the applications and workloads which can be shifted to the cloud. These typically include workloads that are latency tolerant, have sudden spikes in usage and usually non-production.

4 Upgrade Strategies

 i)In-place

An in-place upgrade involves replacing the pre-existent SQL Server 2005 by installing the newer SQL Server version on top of it and using the same underlying hardware and platform.  Even though this is possible up to SQL Server 2014, it is not so for SQL Server 2016.

ii)Side-by-Side

For a Side-by-Side upgrade the newer version of SQL Server is installed as a secondary instance on the already existing system. Even though a side-by-side install of SQL Server 2014 along with SQL Server 2005 is possible, the same is not true for SQL Server 2016.

iii)New Instance Installation

This involves a fresh installation of SQL Server 2016. While upgrading from SQL Server 2005 to SQL Server 2016, this is the only upgrade strategy that is viable. Since a fresh install is mandatory, it is advisable to perform hardware refreshes required for the system to take advantage of the latest technologies, which gives a better opportunity to accommodate future growth.

NOTE: You can’t upgrade SQL Server 2005 to 2016 side-by-side or in-place, only a new instance installation is possible.

Now that the decisions and planning regarding the upgrade has been completed, we can proceed to upgrading the DB Engine:

 

Pre-upgrade checklist

Now that you have gone through and utilized the above resources. It is advisable to create pre-upgrade checklist which is specific to your own environment. The main theme of the same should be to look for and root out potential problems. A broad outline for the pre-upgrade checklist can be seen below:

>Run Upgrade Advisor, and address problems which show up.
>Run the System Configuration Checker to confirm compatibility and address issues. https://msdn.microsoft.com/en-us/library/ms143753.aspx
>Verify that your system does not utilize any discontinued features.
>Record all the software and hardware changes you plan to make.
>If your storage system has changed, you can run SQLIO tool to verify that there are no bottlenecks or misconfigurations.
> Perform a profiler/readtrace replay: Capture at least one profiler trace using the replay template on production. A RML tool or profiler can be used to generate the same.
>While testing your destination setup, do not just test your application, but all maintenance activities including disaster recovery.
>It is advisable to shift a development or test system to the newer configuration to assess it’s viability, be sure to mimic your peak workloads and typical workloads to test all required functionality.
>This is also a good time to assess future growth and functionality needs, so the same can be mimicked and tested.


Once all the items on your checklist are complete, you can proceed with the upgrade for your system.

5 Methods to Migrate Databases

Two methods with which customers can proceed with individual migration of their databases are listed below:


i)Backup+Restore:

This is the approach that is preferred  and is generally considered the safer of the two approaches. On the legacy server, there is always at least one backup file which remains unedited during the migration. It gives you the option to check and possibly rectify any mismatches during the migration; to proceed with this method, first create backups of the databases. Shift these to read-only mode on the source server, so that the backup has the latest copy of data. Restore this backup on the target server and now you can switch over from the source to the target. This approach needs less downtime as compared to “Detach+Attach”.

ii)Detach+Attach:

Detaching a database removes it from the current instance of SQL Server leaving the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server. The Detach+Attach approach is generally not preferred since, it is not considered as safe as the first option. This is because, if something goes wrong during the attach process, you lose a fail-safe as the data file will be modified during the upgrade. Due to this reason, you will be unable to re-attach it to the legacy SQL Server instance as well. Therefore, it is highly advisable to first take the database offline, keep a copy of the files of the detached database, then re-attach to the legacy server, and utilize the copy on the newer machine.