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.
The next screen will give you two options:
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.
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:
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.
You can find the complete migration path in the previous article.
After completing all options, click on the create button on the bottom of the screen.
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.
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.
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.
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.
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.
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.
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.
DMA provides you an option to export your assessment results in two formats; 1. JSON 2. CSV.
You can save these results for a detail analysis with your team.
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.