Migrating SQL Database to Azure Cosmos DB using Migration Tool

Today’s applications are required to be highly responsive and always online. To achieve low latency and high availability, instances of these applications need to be deployed in data centers that are close to their users. Applications need to respond in real-time to large changes in usage at peak hours, store ever-increasing volumes of data and make this data available to users in milliseconds.

It is a NoSQL Database provided by Azure called as Cosmos DB

Advantages of NoSQL database

• Scale-out
• Schema Free
• Distributed (Replicas)

Advantages of NoSQL Cosmos database by Azure

• Low Latency
• High Availability
• Multiple API (Core SQL API )
• No Schema Management

In Cosmos DB to save the data we require Container- Inside the containers, the data is saved in the terms of partitions and if the data is increased Azure automatically increases the partitions.

We can also Acquire multiple containers for different data as it can be stored in the same containers but in different partitions, but some data must process with more Ru’s and some with less so technically we choose different containers.

We must specify the partition key also at the time of making the containers, this is for SQL API if we deploy a MongoDB API then we must use Collections instead of containers

Data Migration from SQL Server

As we know that cosmos DB is an unstructured database so when migrating from SQL Server, we will take care of the Schema whether it is well defined or not because in cosmos DB we can’t create joins and hence need to make a good relationship between the documents.

We will Require Migration Tool- Azure Cosmos DB Data Migration Tool

Step 1:

We need to select the migration option as the SQL and put the connection string as data source=.;initial catalog=AdventureWorks2017;integrated security=true;

Before pasting the query in the box, remember to create a normalize view with all the related information and the number of rows will define the number of documents to be created, we, must use (.) Operator to create the partitions as Cosmos DB will use that operator to distinguish the nested properties.

Step 2:

Now we will put the connection string for the cosmos DB, we will choose the primary connection string as it gives us full access to the account which we will get from the keys section in the portal

After pasting the connection string, it is very important that you create a database before as it will not be auto-generated

No need to select the Advanced Option and start importing, to verify if it is done properly, we can see the number of Transferred items is the same as the number of rows in SSMS by running the above query expected 701 rows.

Step 3:

And then we can head over to the portal to check where the migration was successful.