Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted November 27, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

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

By Anoop Kumar

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.

Source SQL Server

Target SQL Server (On-premise)

Target SQL Server (Cloud)

2005

2012

2014

2016

2017

Azure SQL Database

2008

2012

2014

2016

2017

2008 R2

2012

2014

2016

2017

2012

X

2014

2016

2017

2014

X

X

2016

2017

2016

X

X

X

2017

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM