Migrate Data from SQL to 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.

Video :- https://youtu.be/qv724E-R2k8

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.

• Azure Cosmos DB Resource Model
  • Azure Account
    • Databases
      • Collections
        • Documents
          • resource/data
          • attachments (optional)
• Indexing Policies are of 3 types, Hash Index, Range Index and Spatial Index.
• Resource properties which you find when you fetch data from the collection are listed below:
  • id: user-defined unique identifier. It should be string
  • _rid: auto-generated resource id
  • _ts: auto-generated timestamp (last updated) epoch value
  • _etag: auto-generated GUID. Used for optimistic concurrency
  • _self: auto-generated URI path to the resource. This is very useful when using with SQL API for Azure Cosmos DB
  • _attachments: URI path suffix to the resource attachment