DocumentDB - SQL Server

The JSON files are a natural fit, and they may just be able to be imported as is to DocumentDB. However, importing from a relational database like SQL Server is going to require some sort of transformation, meaning we need to somehow bridge the gap between the normalized data in SQL Server and its denormalized representation in DocumentDB.

Let’s take a look at a simple example in which we will see how the Migration Tool can import from a SQL Server database. In this example, we will import data from the AdventureWorks 2014 database. AdventureWorks is a popular sample database that you can download from CodePlex using the following steps.

Step 1 − Go to

Step 2 − Search for the AdventureWorks 2014 in the search box.

Search Box

Step 3 − Pick the recommended release for the sample databases.

pick recommended release

The easiest download to choose is the recommended one, which is the Full Database Backup.

choose recommended one

Step 4 − Click and save the zip file to any folder and extract the zip file which contains Database Backup file.

Step 5 − Open SQL Server Management Studio, connect to my local SQL Server instance and restore the backup.

Connect local SQL Server

Step 6 − Right-click Databases → Restore Database. Click ‘browse’ button.

Restore Database

You will see the following window.

Select backup devices

Step 7 − Click the ‘Add’ button.

Click Add button

Step 8 − Browse the database backup file and click OK. Then OK one more time, and off goes the restore.

Click Ok

We've got a successful restore.

successful restore

Well, this is a large database, and there sure are a lot of tables, so let’s take a look at the Views instead.

This looks a bit more manageable, and most of these views work by joining multiple related tables together, so let’s have a look at this one called vStoreWithAddresses, which is defined in the Sales schema.

Sales schema

We're selecting from the view, which joins all the tables, and we're filtering on AddressType, which gives us only the Main Offices.

   CAST(BusinessEntityID AS varchar) AS [id], 
   Name AS [name], 
   AddressType AS [address.addressType], 
   AddressLine1 AS [address.addressLine1], 
   City AS [], 
   StateProvinceName AS [address.location.stateProvinceName], 
   PostalCode AS [address.postalCode], 
   CountryRegionName AS [address.countryRegionName] 
   AddressType='Main Office' 

When the above query is executed, you will receive the following output.


Let's launch the GUI version Migration tool.

GUI Version

Step 1 − On the Welcome page, click ‘Next’ for the Source Information page.

Step 2 − Select the SQL from dropdown menu and specify the database connection string.

specify connection

Step 3 − Click ‘Verify’ button.

Click Verify

If you specify the correct connection string, then it will display the successful message.

Step 4 − Enter the query which you want to import.

Enter query

Step 5 − Click ‘Next’.

Click Next

Step 6 − Specify the Connection String from your DocumentDB account which can be found from the Azure Portal.

Specify Connection String

Step 7 − Specify the Primary Connection String and don’t forget to add the database name at the end of connection string.

Primary Connection

Step 8 − Specify the Collections to which you want to add the JSON files.

Step 9 − Click on the Advanced Options and scroll down the page.

Advanced Options

Step 10 − Specify the indexing policy, let’s say Range indexing policy.

Range indexing

Step 11 − Click ‘Next’ to Continue.

Click Next

Step 12 − Click ‘Next’ again to continue.

SQL Data Migration Next

Step 13 − Here you can see the summary, now click the ‘Import’ button.

Click Import

It will start importing data. Once it is completed, you can see on Azure Portal.

Sql Import Completed