New upgrade capability in SSMS gives you readiness assessment and upgrade the SQL Server within SSMS.
In this blog post we are going to talk about the new SQL Server version upgrade assessment and migration experiences that will be shipped out in SSMS 21 soon.
Current SQL Server version upgrade experience
Upgrading a SQL Server from one version to another is a two-step process: 1) Assessment and 2) Migration. Assessment step is required to determine if there are any blockers for the version upgrade and steps to remediate the blockers and to know about the new capabilities applicable to your databases and tables in the upgraded version. Migration step migrates the data from the earlier version to the new version of SQL Server. Currently DMA provides both Assessment and Migration capabilities for version upgrade scenarios. Alternatively, you can use SQL Server setup to perform migration but must manually perform version upgrade Assessment before the migration.
Why SQL Server version upgrade experience in SSMS 21
SQL Server Management Studio (SSMS) is the preferred application for managing and monitoring SQL Server anywhere (on-prem or any cloud including Azure SQL). To perform version assessment and upgrade, currently users would have to install DMA. To reduce the burden of having to install an additional tool, we are introducing the SQL Server edition upgrade Assessment and migration capabilities in February preview release of SSMS 21, so that you can perform in place version upgrade of SQL Server within SSMS 21.
New SQL Server version upgrade experience in SSMS 21
Here is how the new SQL Server version upgrade experience in SSMS would look like:
Please note the images shown below are only for illustration and not the actual experience.
1) Right-click on the SQL Server database -> click on Tasks -> Upgrade SQL Server -> New Assessment in the menu.
2) In the database upgrade wizard, click Next and provide the Assessment Name, Target SQL Server version and the Assessment Type. Since this is a Azure SQL Cloud migration readiness assessment, default selection is Compatibility Issues.
3) Select databases which you would like to assess for migration to Azure SQL -> Click Next -> Click Finish to complete the Assessment wizard.
4) View the migration readiness Assessment. The readiness assessments gives a summary of all the rules that are checked, blockers for migration and the remedial actions to solve the blockers.
5) Once the migration assessment is completed and the blockers are resolved, Right click on the SQL Server database -> Tasks -> Upgrade SQL Server -> New Migration to upgrade the SQL Server within SSMS without having to switch to another tool:
6) Select Target SQL Server Instance and the source SQL Server databases that need to be upgraded.
7) Specify the folders that contain the backups for the selected databases in the upgrade wizard and click finish:
8) You will be able to monitor the detailed upgrade progress:
Hope you like this new upgrade SQL Server experience in SSMS, please do share your thoughts and migration capabilities you would like to see in SSMS in the ideas site: Bringing SQL Server Upgrade experiences to SSMS ยท Community or please share your thoughts in the comments below.
Updated Jan 22, 2025
Version 2.0Sudhir_Raparla
Microsoft
Joined February 01, 2022
Microsoft Data Migration Blog
Follow this blog board to get notified when there's new activity