Step by Step Guide to Migrate SQL Server Data to SQL Server 2017

In this article, we will walk through a step by step approach to assess your SQL Server migration using DMA. This activity will help you to understand the behavior of the existing SQL Server and what changes need to be made to migrate to a newer version of SQL Server.

SQL Server Migration Activities

You must have a prepared master plan for a successful SQL Server migration. I’ve captured a high-level list of activities, all of which are required in the migration process.

Sr. #	SQL Server Migration steps
1	Document environment pre-requisite
2	Study deprecated features and discontinued features
3	Run data migration assistant to know Breaking changes and Behavior Changes
4	Identify SQL Server services - Database engine, SSIS, SSAS, SSRS, etc.
5	Database collation upgrade considerations
6	Application connection requirement
7	Plan to manage other technology features - log shipping, clustering, database mirroring, replication, full-text search, DQS, etc.
8	Manage LinkedServer availability
9	Plan database backups – Full backup, differential backup and transactional log backup
10	Manage required disk size
11	Manage separate service accounts
12	Check data consistency
13	Pre upgrade - performance metrics to compare after upgrade performance
14	Estimate downtime
15	Finalize upgrade approach
16	Upgrade validation criteria
17	Final acceptance criteria
18	Rollback plan and testing
19	Notification to all involved and impacted stakeholders
20	Post deployment steps
21	Ready with new/modified database maintenance plan

The above list is a reference to trigger your migration planning. You must add and modify the list of activities and decide the right order as per your project situation and need.

Step by Step SQL Server Migration Assessment

In this section, we discuss how DMA can help in the initial assessment of SQL Migration before the actual migration takes place. First, you can get the copy of latest version of DMA from Microsoft download center.

After installing DMA, either on your server or client machine, when you run DMA you will will see the screen below.   As you are opening this wizard for the first time, you need to create a new project. Let’s click on the + sign on the left side of the screen.

Figure 1 DMA start screen

The next screen will give you two options:

1) Assessment

2) Migration

Here we will select assessment, as we are not starting the actual migration. We want to assess all the facts and analyze the reports before starting the actual migration. You can refer to the previous article to learn all of the facts and potential issues that can be discovered with the help of the initial assessment.

Figure 2 Project type and Project name

After selecting the Assessment option, you need to specify a name for the project. I’ve used SQL2K14toSQL2K17.

In the remaining part, you need to select your options for the source and target server type:

Figure 3 Source server type and Target server type

Source Server type is the data source type that you are planning to migrate. I have selected SQL Server. If you have data source other than SQL Server then you need to use SQL Server Migration Assistant (SSMA) or Azure Database Migration Service (DMS), which support other data sources.

Target Server Type is the target SQL Server type, either SQL Server or Azure SQL Database. I have selected SQL Server from the drop down list.

After completing all options, click on the create button on the bottom of the screen.

Figure 4 Create project

This will create your SQL assessment project and open a new screen. The new screen gives you an option to select a target SQL Server version. I’ve selected SQL Server 2017 from the drop down list. The values in the drop down will be different, based on your Target server Type selection in the previous screen. If you had selected Azure SQL Database as the Target Server Type then you will not see any drop down (shown in the screen below) to select a target version of SQL Server.

Figure 5 Select Target version

Let’s select SQL Server 2017 as the target and move on.

You can select the check box for Comatibility Issues and New features recommendation. The third check box, Check feature parity, is disabled for selected value from the drop down. The Check feature parity check box will be enabled if you select “SQL Server 2017 on Linux” from the drop down.

Figure 6 Select report type

After selecting Source and Target, you need to connect to Source SQL Server to provide server details and authentication type. Once you provide the required details, you need to make sure that SQL Server has enabled encryption connection. If not, you need to uncheck the box in the given screen.

In the bottom of the screen, you can see the information about the set of permissions needed on SQL Server instance for successful assessment.  

Click on the connect button to connect the source SQL Server.

Figure 7 Select Source SQL Server and Authentication type

Once it makes a successful connection it will show you all the available databases on the server.  Select database/s to assess for migration. You have the flexibility to either select one database at a time or select all available databases together.

Figure 8 Select databases to assess

I’ve selected both of the databases available on my test server and included them in the assessment. After clicking the add button, I land on the next screen, which will start the migration assessment.

Figure 9 Start migration assessment

The assessment will take some time depending on the size and number of databases included for assessment. In the screen below, you can see the progress of the assessment.

Figure 10 Migration assessment progress

Finally, DMA completes the assessment and shows the result on your screen [see the screen below]. This screen provides informative information and holds the health of your SQL migration. It contains a lot of information in various sections to review before you trigger the actual SQL Migration.

Figure 11 Assessment report with Export report option

DMA provides you an option to export your assessment results in two formats; 1. JSON 2. CSV.

Figure 12 Report format options to save

You can save these results for a detail analysis with your team.

Summary

DMA is a great tool provided by Microsoft to assess and migrate SQL Server to a newer/higher SQL Server version to meet your organization and business needs. This tool helps in migrating SQL Server to on-premise SQL Server or helps to migrate to Azure SQL Database. in this article, we explored a step by step process to assess SQL Server migration from SQL Server 2014 to SQL Server 2017. Once you have  reviewed the published report by DMA and achieved rest of the tasks of your master migration plan you can trigger the actual SQL Migration and accomplish your goal.