AzureFeeds

Introduction

The migration component in SQL Server Management Studio (SSMS) is designed to facilitate the upgrade and compatibility checks from lower versions of SQL Server to higher versions, whether running on-premises or on virtual machine environments. The experience in SSMS is designed to bring migration capabilities directly to users, aligning with the philosophy of meeting users where they are.

This component identifies compatibility issues related to breaking changes, behavior changes, deprecated features, and other critical information. Additionally, it provides a feature parity check for cross-platform migrations. The assessment report generated by the upgrade adviser outlines the impact on objects, potential causes, and remediation steps.

Migration Capabilities

If you need to migrate your database from a lower version, the migration extension offers a robust solution. It employs the backup-copy-restore technology to transfer data from the source to the target, requiring a backup folder and optionally a copy folder. This extension also simplifies side-by-side migrations by migrating logins.

Pre-requisites

Before you begin, ensure you have:

• SQL Server Management Studio 21 or above.


• A login with sysadmin permissions.

Installation and Configuration

1. Install SSMS 21 Management Studio: Once the installation is complete, click Modify and navigate to Individual Components.


2. Select Migration Extension: Complete the installation process.

Assessment Process

1. Connect to the SQL Server: Connect to the server that needs to be assessed for an upgrade.


2. Initiate a New Assessment: Click on the Instance, then Migrate SQL Server, and select New Assessment.


3. Select Assessment Type:
   
   
   
   • Name: Provide a unique assessment name.
   
   
   • Target Name: Specify the target version for the upgrade.
   
   
   • Assessment Type:
     
     
     
     • Compatibility Type: Identify breaking changes, behavior changes, and deprecated features.
     
     
     • Feature Parity: Discover partially supported features that might require re-engineering (enabled for SQL on Linux).
     
     
     
     
   
   
   
   


4. Select Databases: Choose the databases to assess. If you have extended event files or profiler trace files, provide the directory containing these files.


5. Verify Selection: Review your selections and click finish. The duration of the assessment will depend on the number of databases and the complexity of their schemas.


6. Review the Assessment Report: The report will automatically open upon completion, detailing issues under each category, impacted objects, and recommendations for fixing the issues.

Migration Process

1. Connect to the SQL Server: Connect to the server that needs to be assessed for an upgrade.


2. Initiate a New Migration: Click on the Instance, then Migrate SQL Server, and select New Migration.


3. Provide Target Server Details:
   
   
   
   • Migration Name: Provide a unique migration name.
   
   
   • Target SQL Server Instance Name.
   
   
   • Authentication Type: Choose between Windows authentication or SQL authentication.
   
   
   • User Credentials: Enter the necessary credentials.
   
   
   • Connection Settings: Adjust other settings as per your environment.
   
   
   
   


4. Select Databases: Choose the databases to migrate and edit the target database name if needed.


5. Backup Folder Path: Provide the local drive or file share path for the backup folder. If the target instance cannot access the source file share, select a copy directory.


6. Backup Details: Edit the backup and copy path for specific databases if required. You can also modify the target data and log file paths.


7. Logins: Migrate eligible logins.


8. Review Selection: Once you review your selections, click finish. The migration wizard will show the progress of each database migration. Upon completion, connect to the target SQL Server to verify the successful migration of databases and logins.

DMA Replacement 

For upgrade scenarios involving a version change from an older to a newer release, we recommend using this SSMS component instead of the Database Migration Assistant, which has been deprecated for several months.

Conclusion

The migration extension in SQL Server Management Studio is an essential tool for database administrators looking to upgrade and migrate SQL Server instances efficiently. By following the steps outlined above, you can ensure a smooth transition from lower to higher versions of SQL Server, whether on-premises or in virtual environments.

This blog post provides a detailed guide on using the migration extension in SQL Server Management Studio, covering installation, assessment, and migration processes. If you have any questions or need further assistance, feel free to reach out.