Today, we will try to learn how you can import data (documents) from SQL Server to Azure Cosmos DB.
This article is for the developers and architects who already have basic understanding of Azure Cosmos DB and are looking for data migration from SQL Server to Azure Cosmos DB Data using Migration tool, which can import data from various data sources into Azure Cosmos DB.
Learn more about Azure Cosmos DB from my previous article – Azure CosmosDB emulator
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:
- dtui.exe – Graphical User Interface.
- dt.exe – Command Line Interface.
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.
Once you have downloaded it, you need to extract the folder as you don’t have to install it. After extracting folder, click on the file dtui file and below screen appear.
Click on Next button, to move to next screen where you need to specify Source Information which will be our SQL Server database as shown in next screen.
In above screen, we are fetching data from Northwind database of Product table which we want to export to Azure Cosmos DB. So, let us move to next screen in Data migration tool where we will provide Source Information details with Query as shown in below screen.
There are multiple options to Import from like JSON file, MongoDB, SQL Server, CSV File and many more. As we are importing date from SQL server, so we will select SQL option here. Let us go to next screen, where we will provide connection string of database connection with SQL Query to fetch data from SQL server.
Next, we will setup Cosmos DB connection setup as shown in below screen:
- Connection String – Provide Cosmos DB Account primary connection string and append it with database name (database=LabLesson) If database does not exist, tool will create it automatically.
- Collection name – demo
- Partition Key – /products
- Collection Throughput– 1000 (default Request Unit per second)
- ID – it is optional. If we have some other unique identifier property then ID in our query, it can be mentioned here and if nothing has been provided neither in query nor here then Cosmos will automatically generate GUID as Id. Remember that ID property is always string.
- Go to Advance options and provide Indexing Policy either by entering or providing a file. So select Enter Indexing policy, right click in the textbox we have two options either default or range. I have selected Range here as it helps with range queries and sorting.
Next screen, If you want to generate Error log file, you can mention it or skip this step.
Summary Screen : Next screen is where we can review source and destination connection details, once you found you are good to start import, click Next and start importing data to Cosmos DB from SQL server.
Verify in Azure portal / Cosmos DB Emulator : Go to Cosmos DB Account -> Data explorer and click refresh. Here you will see database; collection and documents migrated by the tool as seen in below screen:
We have successfully migrated data from SQL Server to Cosmos DB.