Publish date June 21, 2019
Last updated on June 28, 2019
To cop up with emerging technology changes, applications are required to handle different variety and volume of data and needs to be highly responsive. SQL server database is not able to handle these features and that’s where the need for migrating data from SQL server to Cosmos DB arises.
In this article, we will see how to migrate data from SQL server to Azure Cosmos DB using Data Migration Tool.
DATA MIGRATION TOOL
Data Migration tool can import data from variety of sources into Azure Cosmos DB collections and tables. It can import SQL, MongoDB, Azure Table storage, JSON files, CSV files and even Azure Cosmos DB SQL API collections. Data migration tool includes:
This tool can be downloaded from here.
For this article, we are going to import SQL server data to Cosmos DB using Data Migration tool GUI.
IDENTIFY THE DATA
The first and the most important step for every data migration process are to identify the data that needs to be migrated. The main point to be considered while identifying the data to import from SQL Server to Cosmos DB is the data require some sort of transformation as it needs to bridge the gap between the normalized data in SQL Server and its de-normalized representation in JSON.
So keeping this in mind query can be constructed as
1.Simple Select Query:
Like select * from < table name > where each row will be transformed into a flat document with each column exported as a simple property in the root.
Like multiple tables joined query, where each row will be transformed into complex JSON document with nested properties.
Take an example of complex query, examine each column and define an alias name with dot symbol to infer it in desired nested property. The dotted notation of these column names means nothing to SQL Server but it tells the migration tool how to shape each document being imported.
Here in this example the tool will figure out that each document has an ID, name, and address property in its root and an address property as object with nested properties. Also inside the address, there are several address-related properties grouped together. There is another property location, with more nested properties for city and state province name. The main point here to note is each row of the SQL query result will map to self-contained JSON document for Cosmos DB. As this example query results in 701 records in SQL so it will be converted into 701 documents in Cosmos DB.
1. Run Dtui.exe
Welcome page will appear then click Next.
2. Provide Source Information
3. Provide Target Information
If you want to generate Error log file, you can mention it or skip this step.
5. Check Summary
Verify the Summary page and click Import.
6. Migration Completed
7. Verify in Azure portal
Go to Cosmos DB Account -> Data explorer and click refresh
Here you will see database; collection and documents migrated by the tool.
We have successfully migrated data from SQL Server to Cosmos DB.
Type in a topic service or offering and then hit enter to search