fr

SQL Server Database Migration Best Practice

To migrate the content of your SQL Server database from on premise to cloud or visa versa or any other combination thereof. We suggest you follow the recommended best practice detailed below.

Two tools are available, you can choose between the Data Migration Assistant (DMA) from Microsoft or the Microsoft SQL Server Management Studio (SSMS).

Setup

We strongly suggest you clean up your database prior to the move.

Solution 1

  1. Download and install the Data Migration Assistant (DMA) from Microsoft.

  2. Launch the DMA application.

  3. Click on the plus + sign to create a new Migration.

  4. Select Migration and name the Project

  5. Select the Source of your server type and the Target of your server type , from the drop down menu, and leave the Migration scope to Schema and data Click Create

    KB4728.png

  6. Enter the local Server name and choose an Authentication type

Make sure you have sufficient rights and permission to perform this action.

  1. Click Connect
  2. Choose your database in the selection loaded and click Next
    KB4729.png
  3. Enter the target Server name and choose an Authentication type

Make sure you have sufficient rights and permission to perform this action.

  1. Click Connect
  2. Choose your database in the selection loaded and click Next

Your new database should already have been created to appear in this list.

KB4730.png

  1. Validate if you have issues by scrolling down the schema objects list on the left. You can click on an item for more details on the specific issue and if a fix is available.

The users account with the error Windows users can be converted to external users in Azure SQL Database needs to be deselected from the list for the migration to work.

Those specific users will need to export their User vault and configuration prior to the migration Failing to do so will loose the data saved under those sections: My account settings , User specific settings and any entry made in their User vault

After exporting the user data, you will need to create a new user in your list and reimport the data.

  1. When all the issues are fixed or deselected, click Generate SQL script
    KB4731.png
  2. Once the script has been generated, validate if there are any issues then click Deploy schema
    KB4732.png

This may take some time to execute depending on how many connection history you have in your database. Please be patient.

  1. Once the Deployment results is done executing, validate if there are any issues then click Migrate data
    KB4734.png
  2. Click Start data migration , note that the number of tables might be different depending on your version.
    KB4735.png
  3. Wait for the migration to complete. When done you can close the Data Migration Assistant.
    KB4736.png
  4. You are now ready to create the new data source in Remote Desktop Manager or update the Devolutions Server console

Solution 2 BACPAC

  1. Using Microsoft SQL Server Management Studio (SSMS).
  2. Connect to your source SQL Server database.
  3. Right-click on the database name (node) - Tasks - Export Data-tier Application…
    1. Follow the wizard steps.
  4. Using SSMS, connect to the destination SQL Server.
  5. Right-click on the Databases (node) - Import Data-tier Application…
    1. Follow the wizard steps.
  6. You are now ready to create the new data source in Remote Desktop Manager or update the Devolutions Server console.

Solution 3

This solution is not supported for a local to local migration. Here is the error message from SSMS when trying to:

You cannot use this Wizard to move databases between local instances of SQL Server. Supported operations include deploying a local instance of SQL Server to Microsoft Azure SQL Database, from Microsoft Azure SQL Database to a local instance of SQL Server, or from one Microsoft Azure SQL Database to another Microsoft Azure SQL Database.

  1. Using Microsoft SQL Server Management Studio (SSMS).
  2. Right-click on the database name (node) - Tasks - Deploy Database to Microsoft Azure SQL Database…
  3. Follow the wizard steps.
  4. You are now ready to create the new data source in Remote Desktop Manager or update the Devolutions Server console.

Connect to Remote Desktop Manager

  1. Create a new data source in File - Data Sources - Add a New Data Source
  2. On first connect you may receive the following error message.
    KB4646.png
  3. Click on Fix It

This may take some time to execute depending on how many connection history you have in your database. Please be patient.

Connect to Devolutions Server

Update the Host and Database name for the Devolutions Server console in Server - Edit - Database

KB4971.png