SQL Server database migration best practices

To migrate the contents of your on-premises SQL Server database, we suggest you follow the steps of the respective solutions 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.

• Administration – Clean Up Deleted History
• Administration – Clean Up Entry History
• Administration – Clean Up Activity Logs

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.

   

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

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

7. Click Connect.

8. Choose your database in the selection loaded and click Next.

   

9. Enter the target Server name and choose an Authentication type.

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

10. Click Connect.

11. Choose your database in the selection loaded and click Next.

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

    

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

    The user accounts 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.

13. When all the issues are fixed or deselected, click Generate SQL script.

    

14. Once the script has been generated, validate if there are any issues, then click Deploy schema.

    

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

15. Once the Deployment results is done executing, validate if there are any issues, then click Migrate data.

    

16. Click Start data migration. Note that the number of tables might be different depending on your version.

    

17. Wait for the migration to complete. When done, you can close the Data Migration Assistant.

    

18. 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….
4. Follow the wizard steps.
5. Using SSMS, connect to the destination SQL Server.
6. Right-click on the Databases (node) – Import Data-tier Application….
7. Follow the wizard steps.
8. Only for Devolutions Server: In the case of a SQL data source, automatic detection already exists when exporting and the query is launched automatically, but not in Devolutions Server. Therefore, if you are migrating a Devolutions Server, you also need to run this query after the import: UPDATE dbo.ConnectionHistory SET Version = 0x0000000000000000; UPDATE dbo.DatabaseInfo SET ConnectionCacheID = NEWID(), IntelligentCacheID = NEWID();
9. You are now ready to create the new data source in Remote Desktop Manager or update the Devolutions Server Console.

Solution 3

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. If so, click on Fix It.

   

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

Connect to Devolutions Server

Update the Host and Database name for the Devolutions Server Console in Server – Edit – Database.