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.
1- Azure Cosmos DB emulator
2-Azure Cosmos DB Data Migration tool
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
- attachments (optional)
- Azure Account
- 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
_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