#042 How to migrate SQL Server schema and data to Azure SQL

Introduction

In this video, we'll explore how to use Visual Studio not only to compare the structure and objects between disparate databases, but also to analyze and migrate the data, focusing on the differences between the records. The objective is to demonstrate in a practical way the migration process from an on-premises SQL Server database to an Azure SQL Server in the cloud, an increasingly common task in the IT infrastructure modernization scenario.

Preparing the Environment in Visual Studio

To get started, it's essential to have Visual Studio installed and configured. We'll be using Visual Studio 2022 in this guide. A key prerequisite is the installation of the "SQL Server Integration Services Projects" extension. If you don't already have it, you can easily add it by going to the "Extensions" menu > "Manage Extensions" within Visual Studio. Search for the extension and proceed with the installation. Also ensure that the connection parameters to the source (on-premises) and target (Azure SQL) databases, including server, user, and password, are ready to use.

Connect to Azure SQL Database (AZURE PORTAL, CONNECT WITH, VISUAL STUDIO): You can connect SQL Server Data Tools (SSDT) to Azure SQL Database directly from Visual Studio. This allows you to develop and manage database schemas and objects.

View available SQL Server instances and objects (SQL SERVER OBJECT EXPLORER): SQL Server Object Explorer in SSDT allows you to view available SQL Server instances and objects, making it easier to navigate and manage your databases.

Starting the Data Comparison

With the extension installed and the environment ready, the next step is to start the comparison tool. In Visual Studio, go to the "Tools" menu > "SQL Server" and select the "New Data Comparison" option. This will open the main feature window, where we will define the databases that will be compared.

Compare existing data between On-Premises and Azure SQL Database tables (DATA COMPARISON): The data comparison feature allows you to identify differences between data from tables in on-premises environments and Azure SQL Database.

(Note: Visual Studio also offers similar functionality for comparing schemas)

Schema Compare Between SQL Server On-Premises and Azure SQL Database (SCHEMA COMPARE): The schema compare functionality allows you to compare the structure of objects between an on-premises environment and an Azure SQL Database, identifying differences.

Defining Source and Destination

In the "New Data Comparison" window, we need to clearly specify which is the source database and which is the target database. Click "Select Source" to choose the connection to your on-premises SQL Server database. If the connection is not already configured in Visual Studio, use the "New Connection" button to create it, entering the details of your local server. After selecting the origin, confirm with "Ok".

Repeat the process for the target by clicking "Select Target". Select the connection that corresponds to your Azure SQL Server. Likewise, if necessary, create a new connection through "New Connection", providing the access data to your bank in the cloud. With both connections (source and destination) properly configured, click "Next" to proceed.

Selecting Objects for Comparison

On the next screen, the tool will list all the tables and views detected in both databases (source and destination). By default, all supported objects are pre-selected for comparison. However, you can customize this selection. For this example, we'll focus on migrating data from a specific table, the "SalesFact". Deselect all other objects and keep only "SalesFact" selected.

Identify different objects between instances (OBJECT DEFINITION, ADD, TABLE): SSDT helps you identify different objects, such as tables, between instances. You can view the object definitions and decide on the actions to be taken.

After adjusting the selection, click "Next".

Setting Up and Running the Comparison

The subsequent step presents options for how the data comparison should be performed. For most scenarios, the default options are adequate. They instruct the tool to perform a thorough analysis and identify any differences, suggesting the necessary actions (insert, update, or delete data in the destination). Keep the default options and click "Finish" (or the corresponding button to start the comparison). Visual Studio will process the request and display the results, showing the differences found row by row for the "SalesFact" table.

The Importance of the Primary Key

It is essential to highlight that the effective comparison of data between tables depends on the existence of a Primary Key in both tables (source and destination). The tool uses the primary key as a reference to identify which records correspond and, consequently, which columns have changed. Without a defined primary key, record-level data comparison is not feasible through this tool. Fortunately, in our example, the "SalesFact" table has the required primary key.

Updating the Target and Generating Scripts

After the analysis, the tool will present a summary of the required actions. For example, if the destination table is empty, it indicates the need to insert all records from the source. To apply the changes, click the "Update Target" button. Visual Studio will perform the corresponding SQL operations (INSERT, UPDATE, DELETE) on the target database (Azure SQL).

Update the records on the target Azure SQL Database instance (UPDATE TARGET): Based on the data comparison, you can update the records on the target instance in Azure SQL Database.

(Note: Similarly, after a schema comparison, it would be possible to update the structure of the target bank.)

Update the schema on the target Azure SQL Database instance (UPDATE): Based on the schema comparison, you can update the schema on the target instance in Azure SQL Database.

A powerful alternative is to use the "Generate Script" button. Clicking on it does not execute the changes immediately, but generates a SQL script containing all the commands that would be executed by Update Target. This script can be saved, reviewed, modified (for example, to adjust schemas or add custom logic), and run manually at the desired time, giving you greater control over the migration process.

Analyzing the Comparison Results

In the comparison results window, look at the tabs available at the bottom. They provide a detailed view of the differences found: * Different Records: Records that exist on both sides but have different data. * Only in Source: Records present only in the source database. * Only in Target: Records present only in the target database. * Identical Records: Records that are exactly the same in both databases.

Identify DIFFERENT RECORDS, ONLY IN SOURCE, ONLY IN TARGET, IDENTICAL RECORDS: You can identify different records, unique records in the source environment, unique records in the target environment, and identical records during data comparison.

These tabs are essential to understand in detail what will change during the synchronization.

Final Verification and Next Steps

After completing the update of the data in the target (either via "Update Target" or by running the generated script), it is highly recommended to connect directly to Azure SQL Server (using SQL Server Management Studio - SSMS, for example) and verify that the data in the "SalesFact" table has been migrated correctly. Confirm the record count and inspect a few rows to ensure integrity.

Visualize the data in the Azure SQL Database instance (VIEW DATA): The SSDT allows you to view the data directly in the Azure SQL Database instance, making it easier to analyze and manage the stored data.

With data successfully migrated to the cloud, it is ready to be used in new applications, reporting or advanced analytics, leveraging the benefits of the Azure platform.

Conclusion

Visual Studio, with the Integration Services Projects extension, offers a robust and visually intuitive solution for comparing and migrating schemas and data between SQL Server instances, significantly facilitating the transition from on-premises environments to Azure SQL. The process, as demonstrated, covers everything from setting up the connections to performing and verifying the migration. We hope this detailed guide will be a valuable resource in your cloud migration projects. If you have any questions or suggestions, leave your comment!