Friday, 22 December 2017

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

We discussed Data Migration Assistant (DMA) and how DMA can help in migrating an older version of SQL Server 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.

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.

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
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.

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
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:

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
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.

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
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.

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
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.

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
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.

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
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.

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
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.

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
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.

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
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.

Oracle Database Tutorials and Materials, Oracle Database Guides, Oracle Database Learning
Figure 12 Report format options to save

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