How to migrate data from SQL Server to Azure Cosmos DB

In this blog you will learn How to migrate data from SQL Server to Azure Cosmos DB, Azure Cosmos DB provide flexibility to importing documents from SQL Server to Azure Cosmos DB by using the Azure Cosmos DB Data Migration tool, which can import data from various sources into Azure Cosmos containers and tables.

In this blog, We will see how we can import data from the adventure work sample database in SQL Server into Cosmos DB.

Prerequisite 

1- Azure Cosmos DB emulator
2-Azure Cosmos DB Data Migration tool
3-SQL Server

You can download and install the Azure Cosmos Emulator from the Microsoft Download Center , Azure Cosmos DB Migration Tool  and SQL backup for AdventureWorks DB AdventureWorksDW2017.bak and restore DB in local db server.

Step-1 Let’s start cosmos db local emulator.

DB Migration Tool

Step-2 – Choose source information import from SQL and connection string and verify.

data source=DESKTOP-FF83641\MSSQLSERVER01;initial catalog=AdventureWorks2017;integrated security=true

Step-3 – We need to give the Nesting Operator as “.” As we have used to merge the Address parts as one object. Once this is done click on next and SQL query which data we want to migrate to cosmos db.

SELECT
	CAST(BusinessEntityID AS varchar)	                        AS [id],
	Name								AS [name],
	AddressType							AS [address.addressType],
	AddressLine1						        AS [address.addressLine1],
	City								AS [address.location.city],
	StateProvinceName					        AS [address.location.stateProvinceName],
	PostalCode							AS [address.postalCode],
	CountryRegionName					        AS [address.countryRegionName]
 FROM
	Sales.vStoreWithAddresses
 WHERE
	AddressType='Main Office'

Step-4 – In next step,we will copy connection string of COSMOS DB emulator . Mention in step -1 and make sure to add database name mydb ,add collection name ,partition key (In our case in above query postalcode can be partition key.). Now click next

Step-5 – After click next it will show summary page.

Step-6– Click import and data will start importing .

Step-7 Verify data in cosmos db account.

Summary

In this blog, you have learnt how can you import data from the adventure work sample database in SQL Server to Azure Cosmos DB. If you have any question related to Azure cosmos db feel free to contact us