Understanding Migration from SQL Server to SQL Server 2017 using Data Migration Assistant

Overview

Technology is changing rapidly, and newer versions of software products and tools are coming to market. It’s always recommended to migrate the applications to newer versions to get the benefit of the upgraded and enhanced versions. However, migrating to a newer version is a big challenge for organizations who don’t have a fully functional IT shops to understand the overall impact on their existing applications and who can continue to support their business without any impact after migration. Most of the time all these new products are being launched with some migration assistant tools. These tools help organizations to understand the impact on an application before migrating the whole application to the newer version.

Introduction of Data Migration Assistant

Microsoft introduced SQL Server 2017, a newer version of RDBMS for On-premise and Azure. SQL Server 2017 has lots of newer and upgraded capabilities. To plan for migrating your SQL Server databases to the latest SQL Server 2017, you need to understand the impact on the functionality of existing databases. You do not need to be concerned with the impact on database functionality before initiating the actual migration as Microsoft introduced a support tool along with the newer version of SQL Server.

 The tool is Data Migration Assistant (DMA). The DMA not only helps to upgrade to a new data platform by spotting compatibility issues that can impact your database functionality on your newer version of SQL Server, but also recommends performance and reliability improvements for your new environment.

DMA Supported SQL Server Version

You can plan the migration of database and data to newer version of SQL Server using the latest DMA V3.3. DMA V3.3 helps in migrating from SQL Server to SQL Server 2012 and the rest of the newer versions of SQL Server. Also, you can plan to migrate from on-premise SQL Server to Azure SQL Database. Refer to the table below to understand your migration path using latest DMA V3.3.

DMA Features and Capabilities

DMA is a valuable tool and has a lot of features to help you in successful SQL Server Migration. DMA analyzes your source SQL Server instance against your target SQL Server instance (newer/upgraded version) and prepares a comprehensive report to tell you how you will be able to achieve your migration successfully. Here are some of the key features of DMA:

DMA Workflows – DMA has multiple workflows to fulfil your migration needs. DMA serves the purpose of migrating your database to an on-premise SQL Server or to Azure SQL Database.

Component supported in Migration – DMA includes schema, data and all types of logins to evaluate the impact. Below is the list of components supported while migration evaluation is in place:

• Schema of Database
• Data
• Database users
• Server roles
• Logins – SQL Server & Windows

Issue Discovery – The primary feature of DMA is to discover issues under various categories that can affect an upgrade to an on-premises SQL Server. These categories help users to understand the issues and set the right priorities to resolve them as per their time lines. Below are the tlhree major categories under which DMA reports all issues:

• Breaking changes – are changes which can break the migration and migration will not be completed successfully.
• Behavior changes – are changes which can affect the behavior of application and may require minor change before initiating any kind of migration.
• Deprecated features – a list of features that have been used in the application that have been identified for removal in future versions of SQL Server.

Feature Recommendation – one of the useful features of DMA is to discover new features in the target SQL Server platform that the database can benefit from after an upgrade. DMA organizes these feature recommendations in the following categories:

• Performance
• Security
• Storage

These recommendations are helpful in leveraging new features available in the new SQL Server version to enhance the application usability.

SQL Server Migration to Azure SQL Database

We can use on-premise SQL Server to Azure SQL Database assessment workflow to evaluate the migration. DMA assesses the migration under two major categories: 1) migration blocking issues 2) partially supported or unsupported features

Migration Blocking Issues – DMA spots the compatibility issues that can block on-premise SQL Server to Azure SQL Database migration. DMA not only discovers issues, it also provides recommendations to handle all discovered migration blocking issues.

Partially supported or unsupported features – DMA spots features on your on-premise SQL Server that are either partially supported or unsupported on Azure SQL Database. DMA prepares a detailed report with a list of partially supported or unsupported features and a comprehensive set of recommendations as alternative approaches available in Azure SQL Database. You can use this extensive report and follow a step by step process for successful migration.  

Summary

Data Migration Assistant is a great tool to assist you in migrating a SQL Server instance to on-premise instance as well as on Azure SQL Database. With the help of DMA, you can uncover your application risks and understand the behavior of the database before starting the actual SQL Server migration. You can review the DMA report carefully to learn the discovered issues that can affect on-premise SQL Server and Azure SQL database migration and get detailed guidance on how to resolve the issues.

See all articles by Anoop Kumar