In this article, we will walk through the process of migrating data from a SQL Server database to Azure Cosmos DB. Azure Cosmos DB is a globally distributed, multi-model database service provided by Microsoft. It offers high scalability, low latency, and global distribution, making it an ideal choice for modern applications.

Step 1: Identify the Data to Export

The first step in the migration process is to identify the data that you want to export from your SQL Server database. For example, you may want to export a specific table or a set of tables. In our example, we will export the data from the [Purchasing].[Suppliers] table of the “wideworldimporters” database.

Here is an example query to retrieve the data:

SELECT supplierid as [Supplier.SupplierID],
       suppliername as [Supplier.suppliername],
       bankaccountname as [Supplier.bankaccountname],
       bankaccountcode as [Supplier.bankaccountcode],
       bankaccountnumber as [Supplier.bankaccountnumber],
       LTRIM(ISNULL(deliveryaddressline1, '') + ', ' + deliveryaddressline2) AS [Supplier.Address],
       cityname as [Supplier.cityname]
FROM wideworldimporters.purchasing.suppliers suppliers
INNER JOIN wideworldimporters.application.cities cities
ON suppliers.deliverycityid = cities.cityid

This query will return 13 rows of data.

Step 2: Install Azure Cosmos DB Emulator

Before we can migrate the data, we need to install the Azure Cosmos DB emulator. The emulator provides a local environment that simulates the Cosmos DB services. You can download and install the emulator from the official Microsoft website.

Step 3: Perform Data Migration

Once the emulator is installed, we can proceed with the data migration process. Microsoft provides a migration toolkit that allows you to import data from various sources, including SQL Server, JSON files, CSV files, MongoDB, and Amazon DynamoDB.

To migrate data from SQL Server to Azure Cosmos DB, follow these steps:

1. Download and extract the migration toolkit.
2. Run the “Dtui.exe” file to launch the graphical user interface version of the tool.
3. On the welcome screen, you will find information about the tool and a link to the online documentation. Click “Next” to continue.
4. On the “Specify source information” screen, choose “SQL” from the “Import from” drop-down box.
5. In the connection string text box, enter the connection string for your SQL Server database. The connection string should be in the standard format: “Data source=<SQL Server name>; Initial catalog=<database name>; User id=<username>; password=<password>”.
6. Verify the connection string by clicking the “Verify” button.
7. Choose the “Enter Query” option and enter the SQL query that retrieves the data you want to migrate.
8. Click “Next” to configure the target.
9. On the “Specify target information” screen, choose “Azure Cosmos DB – Sequential record import (partitioned collection)” from the “Export to” drop-down box.
10. In the connection string text box, enter the connection string for your Azure Cosmos DB. The connection string should be in the format: “AccountEndPoint=<URLEndPoint>; AccountKey=<Account Key>; Database=<Cosmos DB name>”.
11. Specify the collection name and partition key.
12. Click “Next” to continue.
13. Review the import settings on the summary screen and click “Import” to start the migration process.

Once the migration is complete, you can verify the data in Azure Cosmos DB by executing queries in the data explorer.

Step 4: View Data in Azure Cosmos DB

To view the migrated data in Azure Cosmos DB, open the Azure Cosmos DB emulator and click on the explorer in the left pane of the screen. You can execute queries like “SELECT * FROM c” or “SELECT * FROM SupplierData” to retrieve the data from the collection.

That’s it! You have successfully migrated data from a SQL Server database to Azure Cosmos DB using the Azure Cosmos DB migration toolkit.

Summary:

In this article, we have covered the step-by-step process of migrating data from a SQL Server database to Azure Cosmos DB. Azure Cosmos DB offers a powerful and scalable solution for storing and querying data in the cloud. By following the steps outlined in this article, you can easily migrate your data and take advantage of the benefits offered by Azure Cosmos DB.