Data Migration Service – Part 2

Offline Migration

Migration Steps (SQL Server to Azure SQL Database)

Prerequisites:

• Create a Vnet for DMS
• Vnet’s nsg should not block communication ports 443, 53, 9354, 445, 12000
• Configure your windows firewall
• open port 1433
• If you’re running multiple named SQL Server instances using dynamic ports, you may wish to enable the SQL Browser Service and allow access to UDP port 1434 through your firewalls so that the Azure Database Migration Service can connect to a named instance on your source server.
• Create a server-level firewall rule for the Azure SQL Database server. Provide the subnet range of the VNET used for DMS
• Ensure that the credentials used to connect to source SQL Server instance have control server permissions.
• Ensure that the credentials used to connect to target Azure SQL Database instance have CONTROL DATABASE permission on the target Azure SQL databases.
• The source SQL Server version must be SQL Server 2005 and above.
• Database(s) must be in either Bulk-logged or Full recovery mode and take their full, transactional log backup.
• If any of the tables don’t have a primary key, enable Change Data Capture (CDC) on the database and specific table(s).
• The LOB tables must have primary key.

Migration steps

In DMA assessment and migration can be performed. Assessment is carried out in order to find the compatibility issues in a database. The migration blockers are solved for further migration process. To run an assessment, you have to be a member of the SQL Server sysadmin role

Migrate the sample schema

1. In the Data Migration Assistant, select the New (+) icon, and then under Project type, select Migration.
2. Specify a project name, in the Source server type text box, select SQL Server, and then in the Target server type text box, select Azure SQL Database.
3. Under Migration Scope, select Schema only.
4. Select Create to create the project.
5. In the Data Migration Assistant, specify the source connection details for your SQL Server, select Connect, and then select the  database
6. Select Next, under Connect to target server, specify the target connection details for the Azure SQL Database, select Connect, and then select your database you had pre-provisioned in Azure SQL Database .
7. Select Next to advance to the Select objects screen, on which you can specify the schema objects in your database that need to be deployed to Azure SQL Database.
   By default, all objects are selected .
8. Select Generate SQL Script to create the SQL scripts, and then review the scripts for any errors.
9. Select Deploy schema to deploy the schema to Azure SQL Database, and then after the schema is deployed, check the target server for any anomalies.

Post Migrating Sample Schema

Step 1

• Select new migration project. For Offline migration, premium pricing tier should can be choosen. It is not compulsory to select premium tier but in future if one wishes to perform online migration then premium tier is needed.
• Select create and run activity.

Step 2

Specify source and target details.

Step 3

• Map to target databases screen, map the source and the target database for migration.
• If the target database contains the same database name as the source database, the Azure Database Migration Service selects the target database by default.

Step 4

Select Save, on the Select tables screen, expand the table listing, and then review the list of affected fields.

The Azure Database Migration Service auto selects all the empty source tables that exist on the target Azure SQL Database instance. If you want to remigrate tables that already include data, you need to explicitly select the tables on this blade

Step 5

• on the Migration summary screen, in the Activity name text box, specify a name for the migration activity, and then review the summary to ensure that the source and target details match what you previously specified. Select run migration.
• Run the migration

Run the Migration.

Select Run migration.

The migration activity window appears, and the Status of the activity is Pending.

Monitor the Migration

1. On the migration activity screen, select Refresh to update the display until the Status of the migration shows as Completed.
2.  
3. After the migration completes, select Download report to get a report listing the details associated with the migration process.
4. Verify the target database(s) on the target Azure SQL Database server

Interested in Microsoft Azure, Let’s CONNECT!