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.
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.
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.
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