In previous articles, we have seen how to create Cosmos DB account and how to create containers and insert item to the containers.

But if you were already using some kind of data store and now you are moving to Cosmos DB, it would be handy to know about the Data Migration Tool.

What is it ?

Data migration tool is an open source utility which can import data into Cosmos DB database from various data sources like JSON documents, CSV files, MongoDB, SQL Server, Amazon Dynamo DB, or you can migrate data from Azure Cosmos DB also.

If you view the source code of data migration utility at this repository, there are two executables – dtui.exe and dt.exe. The dtui.exe is with GUI and while dt.exe can be driven from command line interface.

The thing I liked about dtui.exe is you can output the associated command after setting up import options through UI.

Using data migration tool, you can not only transform SQL server tables to JSON documents, but you can also transform tabular source to create hierarchical relationships during import.

Installing

You will need to install Microsoft .NET Framework 4.51 or higher.

The data migration tool is an open source tool, the code is available on GitHub at this repository. For installing, you can download the source code from the repository and compile the solution locally.

Another alternative is, you can download a pre-compiled binary and then run one of the two executables.

There are two executables which you can use:

• Dtui.exe: Graphical interface version of the tool
• Dt.exe: Command-line version of the tool

Migrating Data

Let’s try to migrate some tables from the AdventureWorks database. You dan download the database backup from this location and restore it on your local machine.

Before migrating data in real world, Microsoft recommends to increase the throughput of the Cosmos DB account until migration completes. Once the migration is done, the throughput can again be decreased to lower the cost.

You should also have database and container already created before running the data migration tool.

Then you can download a pre-compiled binary of data migration utility. This will download a .zip file and you will have to extract it to some location on your disk.

Then you can go to the extract location and double click on dtui.exe to launch migration tool.

SQL Source

Then select the Import From SQL option and enter the connection string of the SQL database where AdventureWorks database is restored. Then you can click on Verify button to make sure that connection string is correct.

Next, you have two options, you can either specify a query to extract data, or you can specify a file in which query is written. Let this option be default and then paste below query in the text area below the radio buttons.

SELECT	CAST(BusinessEntityID AS varchar) as Id, [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'

Please note down how we how the “AS” keyword has been used in the query. The “AS” keyword is to rename or alias a column from database table. This decides the hierarchy of the JSON properties.

Note the aliases such as Address.AddressType and Address.Location.StateProvinceName. By specifying a nesting separator of ‘.’, the import tool creates Address and Address.Location subdocuments during the import. 

Target

When you click on next, you need to enter details of target Cosmos DB.

You can copy the primary connection string from the Cosmos DB keys and paste it in the data migration tool. Also do not forget to append database name in the connection string.

<primary-connection-string-of-cosmos-db>;Database=dbname;

You can provide collection name (i.e. container name) and partition key (i.e. Id from SQL query result in our case). If the container with the provided name does not exist, then new container is created by data migration tool.

Then click on Next.

Advanced

On Next panel, you can provide the error log file, but for keeping the demo simple, just proceed to Next screen in wizard.

Summary and Import

On Summary screen of wizard, you can see a View command button which will show you the command you need to execute using dt.exe on command line. For now, just click on Import button.

That’s it, the data is imported to the Cosmos DB account. If you go to Cosmos DB account, you would be able to view the data you just imported as shown in below snapshot.

I hope you enjoyed this article. Let me know your thoughts.