Migrating to Azure Cosmos DB using Data Migration Tool

Azure Cosmos DB is a fully managed database service. It is a globally distributed NoSQL and multi-model database service. Azure Cosmos DB offers different ways to interact including SQL API, MongoDB API, Cassandra API, Table API and Gremlin (Graph) API. Each API is used according to different requirements and scenarios.

In this example I am using Azure SQL Database as my source database from where I am migrating customers data to Azure Cosmos DB. I am using Azure Cosmos DB Data Migration Tool for the migration and this is just one of the ways to migrate.

SQL Database is a family of relational databases whereas Cosmos DB is NoSQL database so there are major differences between the two, link below provides a good explanation.

I am using Azure SQL Database as the source database, I have used the sample database provided in Azure for this example.

I am planning to migrate data from Customer table for customers belonging to Sales Persons. Throughout this example I am considering mostly the default values. It is highly advisable to go over the Azure Cosmos Capacity Calculator for better understanding and cost saving for a given workload.

Now I am starting to create an Azure ComosDB Account as shown below.

As mentioned before I am using Core(SQL) API to create document database and query the database using SQL Syntax. Azure Cosmos container which will have collection, table or graph depending on the type of API used. Link below provides a good exercise to understand the differences between various APIs available in Azure Cosmos DB.

Next I am starting the deployment. It takes about 10 minutes for Azure Cosmos DB deployment to be completed (at this time). Now I can go the resource and I can see a similar screen as shown below.

Now if I go to Data Explorer I can see a similar screen.

I need to create a new database at this point. An Azure Cosmos database is a unit of management for a set of containers. A container is schema-agnostic and the unit of scalability. It is horizontally partitioned and replicated across multiple regions.

There are two ways to provision with Azure Cosmos DB and which are Azure Cosmos containers and Azure Cosmos databases. Azure Cosmos containers uses dedicated throughput when provisioned compared to Azure Cosmos DB which uses shared throughput mode. Each of them depends on the requirements. Link below provides a detail explanation.

Now I have created a database and called it sales.

In this example I am going to configure the network settings from Firewall and virtual networks tab. I have added my machine IP as this is where I am planning to use the migration tool.

 Azure Cosmos DB Data Migration Tool is available on Github.

Once I start the tool a similar screen appears, I am using the GUI based version, there is also a command line version of this tool.

I can start now with my source connection details.

I can obtain the connection string details from the Azure SQL Server connection string tab.

I am modified the original query to the only columns needed. I am using comma as the delimiter to split down the columns into sub-documents.

On the next screen I need to obtain the connection string and decide on a partition key. Id Field a unique key.

Partition key acts as a logical partition and it is used to group similar objects together. Link below contains a good guidance for choosing a partition key.

Now I need to obtain the connection string for Azure Cosmos DB which I can from the Keys tab. I am using the connection string from the Read-Write Keys windows. Keys have already been embedded in the connection strings.

After copying the connection string, I still have to add the database name to the connection string.

Next I mention the path to an error log file and set the error information level.

Finally I can review the details before starting the import process. I can also script the commands and use the command line interface for the tool. Now the process is started I can see that the rows were migrated.

Now I can use the Data Explorer to validate.

Now I open a New SQL Query window and perform count operation.

Azure Cosmos DB SQL Syntax is different compared to Transact-SQL. I found the following link to a good starting point.

Here in this example I provided an overview for Azure Cosmos DB and used one of the ways to migrate.