Migrating Data from SQL Server to Azure DocumentDB

In this post I’d like to demonstrate how fairly easy it is to migrate data from SQL Server to Azure DocumentDB, for those of you who may be considering creating your next application using a NoSQL document database option. To follow along you’ll need the following:

1. Access to SQL Server
2. Microsoft Azure account

I’ll outline where to obtain the rest of the tools needed to get the job done in this post. On with it then!

Creating a DocumentDB Account

The first thing you’ll need to do to work with Azure DocumentDB is create a new DocumentDB account. This is necessary because the DocumentDB account is the root from which all databases will be created.

Create a DocumentDB Account

Once created, you will then have an account with zero databases and zero collections:

New DocumentDB Account

In DocumentDB each database is a container for one or more collections and each collection can hold up to 10 GB of schema free JSON documents.

SQL Server Database

I will be using the AdventureWorks2014 database which can be obtained from here. I’ll use the Sales.vStoreWithContacts view to shape data in the desired format prior to importing that data into DocumentDB. Below is a snapshot of some of the data and note that the query returned 266 rows:

Sales.vStoreWithContacts Sample data

Let’s look at the SQL for the view:

Sales.vStoreWithContacts View’s SQL

As you can see, the view joins 7 tables to provide a flattened view of stores and associated contacts. As previously mentioned, DocumentDB stores schema free JSON documents, so the next thing you’ll want to do is create a query to format the data in preparation for migration. I decided the structure the query as shown below so that the migration tool would produce a data structure that would project documents that have a main object (storecontact) that contains a nested contact object which also has an embedded phone object. This is denoted by the dot (.) notation that I’ve used to alias column names. I will expand on this in just a moment.

Sample Query to format data for import

DocumentDB Data Migration Tool

You can download the open source DocumentDB data migration tool from here. Once downloaded extract the files and run the dtui executable which is the graphical user interface version of the migration tool. You will then see the initial screen:

DocumentDB Migration Tool Welcome Screen

Select SQL from the list of import data sources, enter the database connection string and paste in the query created to format the data. You will also need to add a nesting operator in my case I’m using a dot (.) so that the tool will know that whenever a dot is used it indicates a nesting relationship and click Next:

DocumentDB Data Migration Tool Source Information Screen

On the Target Information screen, you’ll need to select the export type which can be sequential, bulk or a JSON file and in my case I selected the Sequential record import option. You’ll then enter the connection string to your DocumentDB account via copying it from the Keys blade in the Azure portal. The connection string will not include a database name so you will need to manually append the database name to the connection string (Database={your DB name};). The database does not have to be a preexisting database as the migration tool will create it for you if it does not already exist; I will name my database stores. You will then need to name your collection, select a Partition Key (if desired) and enter the ID field value. I have opted to leave the default value for Collection Throughput.

DocumentDB Data Migration Tool Target Information Screen

Next, expand the Advanced options section, scroll down to the Indexing policy section, right-click the textbox and select your indexing policy and click Next

Target Information Screen – Advanced options

You can choose to skip the next screen pertaining to the creation of an error log file or create a log file and click Next

DocumentDB Migration Tool Advanced Configuration Screen

You’ll then reach the summary screen where you can review and verify your information and click the Import button

DocumentDB Data Migration Tool Summary Screen

DocumentDB Data Migration Tool Successful Import Screen

As you can see 266 records were successfully imported into Azure DocumentDB. Now let’s look go back to the Azure portal to view the structure of one of the imported documents to see if what was projected was as expected.

Verify Migrated Data

If you refresh the overview blade of you DocumentDB account, you’ll now see that there is a stores database that contains a storecontacts collection as defined in the migration tool:

Imported database and collection

We’ll then go to the Document Explorer section to view one of the imported documents. I have opted to view the document for ID 298.

Imported JSON Document

As you can see the resulting JSON has a storecontact object that has a contact object defined which also has an embedded phone object defined as denoted in the SQL using the dot notation. This is truly a powerful feature of the DocumentDB data migration tool.