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:

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.

Data Migration Assistant

  • Click Check database compatibility and Check feature parity

Select report type

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

Connect to a server

  • Click Start Assessment.

AdventureWorks2012

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.

Review results

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.

Compatibility issues

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.

Create migration project

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

Connection string properties

  • Select source database and click Next.

Select source database

  • Select target database and click Next.

Select target database

  • Select the objects which you want to migrate.

select objects 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.

fix issues

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.

Generate SQL script

Script & deploy schema

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

Select tables to migrate

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

Migrate data

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.

Deploy data to Azure

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

Deployment settings

Deployment results

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.

Review results

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

 

Esat Erkeç

Esat Erkeç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.
Esat Erkeç
721 views