In my last post I showed you how to conduct an assessment using DMA – Database Migration Assessment tool v2.0
This time let’s do an actual migration from SQL 2008R2 to SQL 2014.
I created a new migration project as shown above.
Next section is all about the permissions for the source and target SQL Servers. For the Source you will need CONTROL SERVER permission and the target you will need to be SYSADMIN.
Now we can add databases and setup the share to where the backup operation will take place. The share must be accessible for both the source and target server as the backup file will be the object used for migration.
You then need to enter the details to where you want the data files and log files to be placed on the target SQL Server.
Select the logins to move.
The tool tells you to validate the login(s) after migration – we don’t want them orphaned.
The stages that the tool goes through are as follows (well this is what I observed anyways):
- Verifies metadata for the migration.
- Takes the backup.
Nice name for a backup. (This is the share mentioned earlier)
- Restores the database to the destination (it actually does something before this but it was so quick I missed it – maybe it validates the backup file)
- Move the logins associated with the database.
A report is generated which is in CSV format.
By the way the backup file created via the tool is temporary, after a migration it is deleted. Also the compatibility level DOES NOT change, you need to do this yourself.