We are going to show how a migration test from SQL Server 2016 to SQL Server 2019 would be performed with the Query Performance tool. To perform the migration test it is necessary to replicate the data from the original environment to the new environment. In this case it has been done with a backup/restore by uploading the compatibility of the databases to be tested. With the two environments available we performed a capture of queries from 2016 and launched the replay on 2019, testing that the queries still work and the performance of the queries in the new environment.
Testing steps for migrating from SQL Server 2016 to 2019
1-. Creation of a WORK for migration testing.
From the WORKS window we generate a new one:
In the work data we enter:
Once the job has been created we proceed to give it content. What we will do is generate a snapshot of the SQL Server 2016 queries we want to test.
2-. Capture queries from memory of a particular type (select in this case):
We access our newly created job and launch a capture:
In the capture filter we simply mark the type “SELECT”. We do not filter by any other field to get all queries:
In this case, we capture the queries from memory and create a snapshot called Capture SQL Server 2016.
When we launch the capture we can see the progress on the main screen and the capture data:
Once the process is finished, we can see that we have captured 911 queries and that we have them in the snapshot:
3. On the queries of this version we are going to replay them in 2019 to see the sharing of the sentences in the new environment. To do this we select the replay option in the same work and select the connection to the new server:
We select the snapshot replay option and a normal replay (not fast replay) as we want to measure the time spent by the queries and with the fast replay we only capture the execution plans. We select the Capture SQL Server 2016 snapshot and we will save the execution in Replay SQL Server 2019:
We launch the replay and we can see the progression of the job until it is finished:
When we go back to the detail of the work we can see the two snapshots with the number of queries of each one, in this case we can see that the same queries have been executed:
4. Once we have the two snapshots we can generate a report to see the behaviour we can foresee in the migration.
We choose the previous work and the two snapshots we have loaded with data. In this case we can compare snapshots for example by CPU time if this is the metric we are concerned about and name the report:
Once generated, we can view the details of the report by choosing the report:
In the header of the report we can see the data of the report:
We also have a Summary section with comparative data:
We can see the number of successful and failed queries. We also have the data with the predicted improvement or regression of the queries that have been successfully executed. In the lower session we see which queries have been executed well and which of them have been executed with errors, so that we can navigate through them and correct possible errors:
In this case, there are none, so we only have to focus on studying possible improvements or degradation of queries.
If we check the Improved queries section, we can see which queries are improved with a different execution plan and a comparison of the plans:
We can do the same for those that get worse:
As you can see, in a very quick way we can make a study of how the queries of our application will behave in a migration between different versions of SQL Server, obtaining the queries that will fail as well as their performance in the new environment. We can also see which queries work best and which queries need database query optimisation to have a smooth migration.
Contact us