What are the SQL Database Migration Tools available?

SQL Server 2008/R2 End of Support is Looming, What options are available for customer?

"First, let’s have a quick look at the options available."

If you are planning an upgrade from SQL Server 2008/2008 R2, Microsoft has made things much easier to test and validate your environment. Numerous tools exist that can assist with migration assessments and even handle migration tasks, and they’re all slightly different. These tools include:

1. Data Migration Assistant (DMA) ~ my personal favorite
2. Microsoft Assessment and Planning Toolkit (MAP Toolkit)
3. Azure Database Migration Service (DMS)
4. Database Experimentation Assistant (DEA)

Let's have a look at these Four Migration Tools in detail:

The Data Migration Assistant helps you to upgrade to a modern data platform. It does this by detecting compatibility issues that can impact functionality on the newer version of SQL Server and makes recommendations for performance and reliability improvements for the new environment.

Your source can be SQL Server 2005+ with a target of SQL 2012+ and Azure SQL Database.

To install DMA, download the latest version of the tool from the Microsoft Download Center, and then run the DataMigrationAssistant.msi file.

Capabilities

• Assess on-premises SQL Server instance(s) migrating to Azure SQL database(s). The assessment workflow helps you to detect the issues that can affect Azure SQL database migration and provides detailed guidance on how to resolve them.
• Discover issues that can affect an upgrade to an on-premises SQL Server. 
• Discover new features in the target SQL Server platform that the database can benefit from after an upgrade.
• Get SKU recommendations for Azure SQL database targets and the ability to provision all your databases to Azure in bulk.
• Migrate on-premises SQL Server instance to a modern SQL Server instance hosted on-premises or on an Azure virtual machine accessible from your on-premises network (via VPN or other technologies).

The Microsoft Assessment and Planning Toolkit has been around for many years and is often referred to as the MAP Tool. It’s great for doing an inventory of your current environment to find where SQL Server (and other applications) exist. MAP is an agentless utility which automates getting the inventory for the Microsoft Platform.

Though this utility does more than what I mentioned but I will focus on SQL Server related inventory information which this utility collects very easily.

Here you will see that it has detected 3 instances on my machine with the version details. This is really good information. So, click on the “SQL Server Discovery” to see more details in graphical model about the SQL Server instances discovered.

You will see some pie charts which will display really good and easy to read information about the SQL Server discovery. The mentioned below screen shows 3 SQL Server Instances with version details:

You can download the MAP from the link here. It’s a simple download like 150 MB file(s) with a pretty simple setup to install. 

The Azure Database Migration Service integrates some of the functionality of existing tools and services to provide customers with a comprehensive solution for migrating to Azure. The tool generates assessment reports that provide recommendations to guide you through any changes required prior to performing a migration. This service currently requires a VPN or Express Route.

The Azure Database Migration Service integrates some of the functionality of our existing tools and services. It provides customers with a comprehensive, highly available solution. The service uses the Data Migration Assistant to generate assessment reports that provide recommendations to guide you through the changes required prior to performing a migration.

It's up to you to perform any remediation required. When you're ready to begin the migration process, the Azure Database Migration Service performs all of the required steps. You can fire and forget your migration projects with peace of mind, knowing that the process takes advantage of best practices as determined by Microsoft.

Finally, The Database Experimentation Assistant is the new A/B testing solution for SQL Server upgrades. It enables customers to gather performance insights for upgrades by customers to conduct experiments on production database workloads across two versions of SQL Server.

Analysis metrics reports provided by the tool, will give information about queries that have compatibility errors, queries performance and other workload comparison data so it can transmit to the customer a higher confidence for a successful migration project.

It supports migrating from SQL Server 2005 or higher versions to SQL Server 2012 and higher versions. Dowanload Database Experimentation Assistant 2.6 here

DEA: High-level Architecture

Watch the following video for a 19-minute introduction to DEA and a demonstration!

The tool has three main features:

1. Capture Workload: Ability to automatically capture a production workload trace with only a few inputs.
2. Replay Workload: Ability to replay a trace on current and new/proposed instances of SQL.
3. Analysis Reports: Ability to generate new reports which provide insight into how workload performance changes across versions of SQL. In addition to generating a new report, there is also the ability to view reports which have been previously generated.

Thanks

Susanth