Migrating SQL Server Database to Azure SQL (PaaS)

In this article, we will highlight SQL Server Data migration to Azure SQL (PaaS).  You can find different approaches for this migration process because SQL Server offers different types of tools and features for this migration. Some of these are:

• Database Migration Assistant
• SQL Server Management Studio Deployment Wizard
• SQL Server to Azure SQL replication

These migration approaches have some pros and cons. But if you ask my opinion, Data Migration Assistant is better than others because, in this migration process, you will face fewer migration problems or issues. Data Migration Assistant will be more helpful to handle these issues.

In this article, we will specifically mention Database Migration Assistant, also known as DMA. At the same time, we will make a small demo of SQL Server Management Studio Deployment Wizard.

Data Migration Assistant

Data Migration Assistant enables migration of an on-premises database to SQL Server Azure (PaaS).  When we look at these tools they offer two project types. These are assessment and migration project types. The assessment project type helps us to analyze unsupported and partially-supported features of a source database before migration. At the same time, this project type includes compatibility level check. This option analyzes compatibility level issues which can affect the migration and offers recommendations about these compatibility level issues. Shortly, two features which are provided by Database Migration Assistant play a key role in the migration process.

These are:

• DMA explores compatibility problems which can block or stop migration.
• DMA explores the current database features which are unsupported or partially-supported by SQL Azure.

These two features are very useful for us because when we start to migrate an on-premises database to SQL Azure, some of its features or objects may not be supported by Azure SQL. In this case, we need to fix or find the solution to migration issues. At this point, we will take advantage of Database Migration Assistant.

Assessment

In this demo, we will use the AdventureWorks sample database.  We will try to migrate the AdventureWorks database to Azure SQL database.  In the first step, we will run Assessment project to assess our on-premises database.

• Click the New (+) button and select the Assessment project type.
• Set the SQL Server source server type and select the Azure SQL Database target server type
• Click Create.

• Click Check database compatibility and Check feature parity

• Define the name and connection of the on-premises database server and then select a database which you want to migrate.
• Click Connect.

• Click Start Assessment.

As shown in the screenshot below, DMA offers unsupported or partially-supported features which you use on the on-premises server, such as Change Data Capture is not supported by your Azure SQL. If you use change data capture in your source database you have to handle this issue.

DMA detects 2 unsupported features and 1 partially-supported feature for AdventureWorks database.

Unsupported features

• Cross-database references are not supported in Azure SQL Database.
• FILESTREAM is not supported in Azure SQL Database.

Partially-supported features

• Full-text search is partially supported in Azure SQL Database.

When we select Compatibility issues, DMA shows some compatibility level problems.

Migration

In this heading, we will migrate the on-premises database to Azure SQL.

• Click (+) in the New tab and select required fields.
• Create new migration project.

• Define the connection string properties of the source (on-premises) server.

• Select source database and click Next.

• Select target database and click Next.

• Select the objects which you want to migrate.

In the above image, you can see a warning sign. It means that these objects can be migrated but you have to fix the issue which is described on the right side or you can uncheck these objects and eliminate them from the migration process.

In the above image, you can see an error sign. It means that these objects cannot be migrated. For this reason, we have to remove these objects from the migration.

• Click Generate SQL Script.

• Select tables from which you want to migrate data and click Start data migration.

• The below screen shows the status of data migration process.

Finally, I would like to make some recommendations about Data Migration Assistant.

• First, run the assessment project and fix issues and then run the migration project.
• Try to run in development or test system before the production system.
• Do not run at the peak-time of the production system.
• If it is possible, use the encrypted connection.

SQL Server Management Studio Deployment Wizard

Now we will look at how to transfer the on-premises database to Azure SQL using SQL Server Management Studio.

• Open SQL Server Management Studio and select the database which you want to migrate to SQL Server Azure.
• Select Deploy Database to Microsoft Azure SQL Database.

• Click Next.
• In the deployment setting screen, fill the New database name field.
• Select Azure SQL service tier options.
• Click Next and then Finish.

In the below screen, you can see some error message. This message identifies memory-optimized tables not supported by standard service level. In this migration method, you don’t have any chance to estimate this error. But when we run the DMA assessment project, the Review results screen notices some information about this case.

Conclusion

In this article, we discussed different types of strategies of how to transfer on-premises data to Azure SQL.  As I mentioned at the beginning of the post, “Data Migration Assistant” is more helpful than other migration types. But you can decide according to your business or project requirements which type to use.

References

Migrate SQL Server to Azure SQL Database using DMS

Migrate on-premises SQL Server using Data Migration Assistant

Best practices for running Data Migration Assistant