SQL Server DMA v2.0 – Migration Time

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.

dea.JPG

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.

dea1.JPG

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.

dea3

Select the logins to move.

dea4

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.

dea9

Nice name for a backup. (This is the share mentioned earlier)

dea11

  • 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)

dea12

  • Move the logins associated with the database.

dea13

A report is generated which is in CSV format.

dea101

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.

dea111111

One thought on “SQL Server DMA v2.0 – Migration Time

  1. Pingback: SQL Server DMA v2.0 – Migration Time - SQL Server Blog - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s