Export data from DocumentDB / CosmosDB to SQL Server

CosmosDB is Microsoft’s latest NoSQL database offering, a metamorphosed form of DocumentDB, so to speak. Read more about CosmosDB here.

Here is a quick note on how to export data from CosmosDB to SQL and do data comparison. This is going to be handy when migration to CosmosDB is still in progress, and you want to check data integrity.

There are a few ways to go about this. The quickest option is to use Document DB Migration Tool. This is a tool provided by Microsoft that helps to migrate data from various sources like MongoDB, csv, SQL Server to CosmosDB. Tool also helps to export data out of CosmosDB, which is what we will be doing in this case.

1. Use Document DB Migration Tool to export data to json files
2. Import json files to SQL Server using a json parse function such as OPENJSON
3. Run SQL scripts in SSMS to check data integrity

Please be mindful of RU costs when doing Step 1 and scale your collection up as required to avoid throttling.

SQL 2016 introduced the OPENJSON table-valued function that lets us read from json files. If your SQL Server instance is 2016, but all user databases are still in compatibility level 2014 or lower, make sure to context-switch to a system database to use this function.


SQL Developer / DBA and roadie scum!

Tagged with: , , , , , , , , , , , ,
Posted in CosmosDB, Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: