One useful tool that you should definitely take advantage of during an upgrade and migration effort is the Microsoft Data Migration Assistant (DMA), which replaces the old SQL Server Upgrade Advisor. Microsoft released the new DMA 3.2 on August 25, 2017.

This tool can be used for three main purposes. First, you can use it to find breaking changes, behavior changes, and deprecated features between a legacy version of SQL Server as the source (SQL Server 2005 and later) and a newer version of SQL Server as the target (SQL Server 2012, SQL Server 2014, SQL Server 2016, and Azure SQL Database).

Second, it can be used to identify new features in the target version of SQL Server (or Azure SQL Database) that might be useful given your legacy database schema and data size. Be forewarned that DMA has a heavy bias towards Azure SQL Database and other cloud-dependent features. For example, it is very eager to recommend Stretch database for large tables.

Finally, DMA can be used to actually migrate databases between legacy versions of SQL Server and a modern version of SQL Server or Azure SQL Database. An example of an assessment project is shown in Figure 1.

image

Figure 1: Microsoft Data Migration Assistant 3.2

If you are considering using DMA 3.2 to migrate existing on-premises databases to an Azure SQL Database, Microsoft’s Harini Gupta did some interesting benchmark testing showing how the Azure SQL Database’s service objective (S0, P1, P15, etc.) and the number of parallel tasks for the migration can affect the elapsed time for the migration.

 

Additional Resources

My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017. You can use this code: BL150GG to register for the PASS 2017 Summit to get a $150 discount.

Here is a link to the complete series about upgrading SQL Server.