Recently I had a requirement to combine data that I already had in SQL Server (2016) with JSON document data already stored in Azure CosmosDB. Both databases were operational and continuously accepting data so I didn’t want to go to the trouble of doing the delta load thing between them, instead I just wanted to be able to query directly on demand.
And so – the purpose of this article is to outline the method to connect direct to Azure CosmosDB from SQL Server using a SQL Linked Server.
Finally … SQL & NoSQL … together at last!
For those interested to learn more about Azure CosmosDB, check out my previous blog post here – https://mrfoxsql.wordpress.com/2016/05/11/azure-documentdb-preparing-loading-querying-data/
Or the official documentation here – https://docs.microsoft.com/en-us/azure/cosmos-db/
And so right up front – this solution only works for SQL Server on VM/IaaS – and is not supported for Azure SQL DB (ASDB) – mainly as ASDB doesn’t support SQL Linked Servers! (Damn, they say!)
Recently I have been using Azure DocumentDB (aka CosmosDB) to validate several business use cases for a variety of application purposes.
For those SQL DBA’s and others who are new to Azure CosmosDB, its a recent entrant to the NoSQL document database world, and as its a PaaS document database cloud service it has the agility, scalability and availability of the Azure Cloud.
Being a schema-less Azure PaaS “document database” for my use case I wanted to verify…
- basic costing and performance levels
- methods to create valid JSON documents from SQL Server
- methods to load JSON documents into Azure CosmosDB
- performing basic like-for-like document query comparisons with SQL Server
Some homework reading for those interested…
22 May 2017 [EDIT]
As hinted above, Microsoft have just recently added significant new functionality and also formally renamed Azure DocumentDB as Azure CosmosDB – a major evolution of the NoSQL database engine. The details on the renamed service is here – https://azure.microsoft.com/en-au/blog/dear-documentdb-customers-welcome-to-azure-cosmos-db/
And so… let get into the belly of Azure