Migrate SQL Server Database to Azure SQL Database using Data Migration Assistant – final steps

The first two steps can be found at http://thedataengineer.com/2018/08/29/migrate-sql-server-to-azure-sql-server/

Part 3: Migration Assistant

Open the Data Migration Assistant and click on +New to start a new Migration

Choose Migration

Fill in the project name and keep the defaults for the other options and click Create

On the first screen, fill in the Server name and make your choice connection properties and click connect. Reminder the credentials used to connect must have Control Server permission

After connecting, choose the Source database. Keep Assess the database before Migration checked and click next

On the select target screen you have the option to either use an existing server to connect to and choose the target database or you can click on Create a new Azure SQL Database, which will take you to the Azure portal to create the new database.

I went out to the Azure portal and created a new server and database for the migration. I then came back in to the DMA window and filled out the info requested and clicked connect. I am also using SQL Server Authentication to connect.

Choose the database that is the target and click next

Select the objects you want to migrate

Check the Assessment and determine if you can fix the issue or uncheck the issue if you wish to not migrate the blocks

I also had an issue that I could not migrate some of the users, as they were in the form domain\user I just left these unchecked and will manually add any users to the new Azure SQL Server Database

Click generate sql script. This may take a little time to run

Once completed, review the script. When you are satisfied with the script, click deploy schema.

The following screen will appear

On completion, check for errors and determine if you can move on. Once you are satisfied with the schema deploy, click Migrate Data

Check that all of your tables are marked as OK. Also, you can see at the top, that Microsoft strongly recommends that you temporarily change your Azure SQL database to performance level P15during the migration process. This database that I am migrating is so small, that I am going to leave it as is. Click Start data migration

The in-progress screen appears. You can also monitor some of the activity in the Azure portal. For instance, Resource Utilization and used space

Once the data migration has completed you will see at the bottom right that Migration is complete and the Duration

I then connected to the Azure SQL database using SQL Server Management Studio and ran some comparisons to the original source and detected no differences.

Reminder, add any users in to the Azure SQL Server Database that were not automatically migrated.

You have now completed the migration.




Migrate SQL Server Database to Azure SQL Database using Data Migration Assistant

We will be using the Data Migration Assistant to assess and then migrate an on-prem SQL Server 2012 SP2 database to an Azure SQL Database

Part 1: Download and install Data Migration Assistant

To install DMA, download the latest version of the tool from the Microsoft Download Center, and then run the DataMigrationAssistant.msi file.

Click next

Accept the license agreement

Click install

Wait for the install to complete

Check the box beside Launch Microsoft Data Migration Assistant and click Finish

Part 2: Open Data Migration Assistant and create assessment

We will now start with assessing our on-prem SQL Server

  1. Click the +New icon and select Assessment project type
  2. Give your assessment a name
  3. We will leave the Source server type and Target server type as is.
  4. Click create

I left the defaults checked and clicked next

Fill in the server name and authentication type. *Notice that the credentials used to connect must be a member of the sysadmin role

Click connect

Choose the sources and click add. I am only interested in the CentralDB database at this time

If you are satisfied with the sources that you have added, click Start Assessment

Obviously, the assessment run time depends on how many and what size of the sources

When the assessment completes

You can see the results on the screen or you can export the report as a json or csv file.

In this example, you can see that there are 3 Unsupported features and 1 Partially supported feature.

With each issue the details section will give the impact. Recommendation and more info

One last screenshot to show the assessment options

You can open, restart or delete an assessment

In part 3, we will look at Migrating a SQL Server database to Azure SQL Database.