Edit

Share via

Use the SQL Server migration component in SQL Server Management Studio

The migration component in SQL Server Management Studio (SSMS) checks upgrade and compatibility issues from lower versions of SQL Server to higher versions of SQL Server, running on-premises and on other virtual machine (VM) environments. The migration component finds compatibility issues related to breaking changes, behavior changes, deprecated features, and other information. The report also provides a feature parity check if you want to migrate from one cross-platform database to another. The upgrade adviser assessment report provides the effect of objects, the possible cause, and remediation steps.

You can also physically migrate your database using the migration component. It follows the backup-copy-restore technology to move data from the source to the target by providing a backup folder and, optionally, a copy folder. Along with the data, the migration wizard transfers logins to simplify side by side migration.

Prerequisites

  • SQL Server Management Studio 21 and later versions.
  • A SQL Server instance login with sysadmin permissions.

Installation and configuration

  1. Make sure that SQL Server Management Studio (SSMS) 21 is installed. Once the installation is complete, select Modify > Individual Components.

  2. Select the Migration component, choose Install while downloading from the dropdown list, and select Modify to complete the installation.

Assess your environment

  1. Connect to the SQL Server instance that needs to be assessed for upgrade from an earlier version of SQL Server to the later version.

  2. Right-click on the source instance, and navigate to Migrate SQL Server > New Assessment....

  3. Select the assessment type using the following information:

    • Name: The unique assessment name

    • Target Name: The target version that you would like to upgrade

    • Assessment Type: One of the following options:

      • Compatibility Type: Identify breaking changes, behavior changes, and deprecated features

      • Feature Parity: Discover partially supported features which might require re-engineering

        Note

        This option is enabled for when target is SQL Server on Linux.

  4. Select the databases you would like to assess. If you want to analyze ad hoc queries using Extended Event or Profiler trace files, you can provide the directory containing all the trace or Extended Event files.

  5. Verify the selection and select Finish. The duration of the assessment rules depends on the number of databases and the complexity of their schemas. Once the assessment is completed, it automatically opens the report.

  6. Review the assessment report. This report provides a list of issues under each category. Each issue displays an issue type, any affected objects, and recommendations to fix the issues.

You can change the compatibility to identify issue for a particular database compatibility level, as shown in the following screenshot.

Screenshot of the SQL migration report, featuring a filter on the database compatibility level.

If you already saved an assessment report, you can open the assessment by navigating to View Assessment Report > Open Assessment, and choosing the assessment_<name>.json file.

Migrate your database

  1. Connect to the SQL Server that needs to be assessed for upgrade from lower version of SQL Server to higher version.

  2. Right-click on the SQL Server instance, and navigate to Migrate SQL Server > New Migration....

  3. Provide the target server details:

    • Migration Name: A unique migration name
    • Target SQL Server instance name
    • Authentication Type – Windows authentication or SQL authentication
    • Provide the user credentials
    • Select other connection settings as per your environment

  4. Databases:

    • Select the databases that you want to migrate
    • You can edit the target database name
    • Provide the backup folder path, which can be a local drive or file share path
    • If the target instance can't access the source file share, you can select a directory to copy the backup files

  5. Backup details:

    If you want to edit the backup and copy path of a particular database, you can select the database name and change the backup and copy path. You can also edit the target data and log file path.

  6. Logins:

    Choose logins that are eligible for migration.

  7. Once you review selection and select Finish.

The migration wizard shows the migration of each database. Once complete, you can connect to the target SQL Server and verify that databases and logins are migrated successfully.

Related content