Your browser (Internet Explorer 7 or lower) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.


Migrate to Azure by using SSMS 18.x

The cases of migrating our database in Azure become more and more every day. Azure SQL Database is the flagship SaaS service Microsoft Provides for hosting a relational database. But no matter it is the same engine there are still many features not supported or with limited functionalities in Azure SQL DB comparing to on premises SQL Server versions. For example, all cross-database references are possible in on premises SQL Server databases but is not supported in Azure SQL Database.

If we could check in advance and plan our migration based on those checks it would be time and effort saving. This is what Migrate to Azure new SSMS features are built for.

In SSMS 18 a new menu item has been added named Migrate to Azure under the Tools menu. There are several new commands included in the menu to provide quick and easy access to help accelerate your migrations to Azure. SQL Server Assessment and Migrate SQL Server to Azure point as to Data Migration Assistant and Data Migration Service for assessing, planning and even running migration of our database in Azure.

And the other two Schedule SSIS Packages and Configure Azure-enabled DTExec connected to scheduling SSIS packages in Azure Services environment.

New Migrate To Azure Menu

Let’s discover the first two first.

SQL Server Assessment command in Migrate to Azure menu runs Microsoft Data Migration Assistant tool. In case we don’t have the tool installed, it directs us to a web page where we download and install the tool.

The Microsoft Data Migration Assistant (DMA) allow us to run assessment workflows to help detect and avoid incompatibilities and blocking issues which prevent databases from upgrade to new version of SQL Server or Azure SQL Database.

The latest release of DMA V4.5 adds support for Assessment activity for migrating SQL Server Integration Services (SSIS) packages to Azure SQL DB and Azure SQL DB Managed Instance. At the time of writing the assessment of packages hosted in File system is supported only. The assessment of packages hosted in MSDB, Package Store or SSISDB comes later

DMA Assessment is organized as a project which we can save and even Upload to Azure Migrate Service. The assessment performs series of checks against the source database and depending on the specified migration target gives us detailed information on how to prevent issues and what steps to perform for a smooth migration from on premise to Azure SQL Database. The potential problems it can detect are grouped in two groups:

  • Blocking issues which prevent migration and need to be removed or changed to continue the migration process
  • Partially or unsupported features, with a list of alternatives to choose from and mitigating steps to perform for migration

The DMA can be used not only for migration to Azure assessments but for any target including on-premises SQL Server. For example, let’s say we have SQL Server 2016 databases which we would like to plan to migrate to a new SQL Server 2019. The DMA would be the tool to help in planning the migration. The first think we would like to know is if there are some objects in the database which would block the migration or any others which would reduce the performance of our databases once they have been migrated to the new version. In DMA these are described as compatibility issues and are organized in the following categories:

  • Breaking changes
  • Behavior changes
  • Deprecated features

More than that, we would probably like to know which new features in the target SQL Server platform the database can benefit from after an upgrade. These are described as feature recommendations and are organized in the following categories:

  • Performance
  • Security
  • Storage

Seems that we have the right tool in place for planning our upgrades. Let’s try it with an example DMA Project assessing AdventureWorks Database for migration to Azure SQL DB.

When we start the DMA we create a new project by specifying the name of the Project, the source where the database comes from or currently exists and the target which is where do we need to migrate this database to. Its important to specify the project type as assessment, not migration as the later will perform a real transferring of the database.

We name the project as CheckAWDBMigration, with a Project type of Assessment. The assessment type is Database Engine, not SSIS

DMA Starting a project Window

Currently supported targets are SQL Server on premises or on Azure VM and Azure targets – Azure SQL Database and Azure SQL Managed Instance. At the time of writing SQL Server 2019 target is not supported yet.

Currently Supported Targets in DMA

After defining the project type, source and the target we are ready to specify what type of check we would like the tool to perform. The options depend on the target we have chosen. For our example of Azure SQL Database, the check will be possible for blocking and deprecated issues (Check database compatibility) and all of the potentially unsupported or partially supported features (Check feature parity). We check them both. Unfortunately, by the time of writing the third option Benefit from new features is not available yet:

DMA Check Options

After we specify the source database, we are ready to start the assessment project:

DMA Select sources step

The results are listed in both groups separately so we can discover them in detail. We can see them in the group of SQL Server Feature parity and Compatibility issues:

DMA Review Results Screen - Feature Parity

Our Adventureworks database doesn’t have any blockers, which means we can safely plan its migration to Azure. What we need to consider is listed in Behavior changes part, meaning there is a risk for a query to behave differently or to probably return different results once it runs in the target environment. So even if it is not a blocker, a behavior change is equally important to put attention on.

DMA Review Results Screen – Compatibility Issues

We have an old syntax join in one of the procedures specified in the right (dbo.GetRecentSalesHelper) which is not using the JOIN syntax but the old WHERE clause in specifying the join criteria. Using this syntax creates a huge risk of reducing query performance which on the case of transferring the database to Azure is not very likely to have. We have everything we need to plan to remove this from the code – the procedure name, the statement where the problem has been located. SO based on the output of DMA we can easily plan for making the changes before performing the migration.

In the feature parity group there have been some issues found. Let’s take a look on them. The most important one is the cross database references found which are not supported in Azure SQL Database. This is more difficult to change because we need to consider either changing the code and having the referenced objects in the AdventureWorks database or planning for upgrading the database which contains referencing object and using rather Azure SQL Managed Instance as a target instead. This would potentially change our migration plan and could define a different target architecture. No matter what we decide, at this point after running the assessment we can say that we feel more confident of what we could expect, what we should perform as action to plan our migration. Seems that DMA is of great help!

The DMA goes even further by providing a very important information which could be of help – it can Identify the right Azure SQL Database/Managed Instance SKU for your on-premises database, which on migration to Azure is of critical importance since the SKU dictates the pricing tier, compute level, and max data size, as well as estimated cost per month.

This functionality is currently available only via the Command Line Interface (CLI).

DMA can also be used for real migrations.

Migrate an on-premises SQL Server instance to a modern SQL Server instance hosted on-premises or on an Azure virtual machine (VM) that is accessible from your on-premises network. The Azure VM can be accessed using VPN or other technologies. The migration workflow helps you to migrate the following components:

  • Schema of databases
  • Data and users
  • Server roles
  • SQL Server and Windows logins

Migrate SQL Server To Azure is the second command in the new Migrate to Azure menu we have in SSMS. By starting it we are forwarded to the web page of Azure Database Migration Service documentation. While the Azure DMS is way above the scope of this chapter, there are some important considerations which are worthy to mention here.

DMS v/s DMA Web Page

Azure Database Migration Service is a fully managed service designed to enable seamless migrations from multiple database sources to Azure Data platforms with minimal downtime. At the time of writing the service is in General Availability. There are a few important differences between DMA and DMS which is worth mentioning and they define their cases. The DMS is a PaaS not a tool, so we need to provision it in Azure, to define a compute power for running it. The DMS supports more data source comparing to DMA, these are Oracle, MySQL, PostgreSQL and MongoDB. We can use DMS to perform offline and online migrations comparing to DMA where we can perform offline migrations only. Online migrations are planned when we need to reduce the downtime to as low as possible. With an offline migration, application downtime starts when the migration starts. With an online migration, downtime is limited to the time to cut over at the end of migration.

Azure Database Migration Service is the preferred method for database migration to Microsoft Azure at scale.

1,435 total views, 3 views today