This will be a very simple guide on migrating databases from a Microsoft SQL Server 2008 to a Microsoft SQL Server 2016, using the Data Migration Assistant.
You can download the current Data Migration Assistant (v5.0) from Microsoft here: https://www.microsoft.com/en-us/download/details.aspx?id=53595
Lab environment overview
Our lab environment consists out of 3 servers.
- 1 Domain Controller : SRV-DC-01
- 1 MS SQL Server 2008 : SRV-SQL2008-01
- 1 MS SQL Server 2016 : SRV-SQL2016-01
I have attached a very simple database to my SQL Server 2008, courtesy of sqlskills: https://www.sqlskills.com/sql-server-resources/sql-server-demos/
Note: If you are having troubles connecting to your SQL 2008 server instance from any other server, please check if you have TCP/IP and Named Pipes enabled in the Configuration Manager.
Creating an Assessment
Before migrating, it’s best to run an assessment. Simply start up the Data Migration Assistant and click on the + button.
Select “Assessment” as your project type and enter a project name. Next we we select Database Engine and set our source and target as SQL Server.
Select the “Compatibility Issues” and optionally the “New Server Recommendation” options.
Now we click on “Add Source” and enter the details of our SQL 2008 server.
You might want to check Trust Server Certificate if you don’t have any valid certificates installed.
Once it’s connected to the source server, we can select the database we want to assess.
Click on “Start Assessment”.
And once it has finished processing, you will see any compatibility issues (or in this case, none).
Optionally you can also check for any new features that might be handy for your database, if any.
Since we don’t have any compatibility issues, we can now migrate the database.
Migrating the database
Note: Before starting the migration it might be a good idea to set the database to Read Only mode. This way the data won’t be changes while we are migrating.
To start the migration, click on that + button again and select “Migration” as the Project Type. Give your project a name and set the Source Server Type and Destination Server Type to SQL Server.
Enter your source server details and target server details. I’m using the default instances on both servers in this case. If you are using Named Instances, enter those (i.e. SERVER\INSTANCE). Click on Next.
We will need a share that both servers can connect to. Also note that the service account running on the source server must have write permissions and the service account running on the target server must have read permissions.
In my case I created a share on my Domain Controller, as this is only a lab environment.
You can also specify the location the database should be restored (migrated) to on the target server.
Click on Next
Select any of the logins you want to migrate along the database. Click on Start Migration.
The database will now be backed up to the share and restored on the target server. Once it’s completed, you get a report with any warnings and/or errors.
After this is completed, you should see the database running on the new server.
As said before, you might want to set the database to read only. This will avoid users/applications from writing new data to the database. After migrating the database will also be in read only on the target server.
After the databases have successfully migrated to the new server you might want to rename the old server and change the IP setting. In dns you can create a new ALIAS record with the old server name and point it to your new server. Optionally you can also change the IP of the new server to the IP of the old server (in case certain software connects to the server via IP and not hostname). Or you could ofcourse update all the connection strings and point them to the new server instead.