A flexible new distributed query capability is now available in preview in Azure SQL Database. Elastic database query allows tools and applications to retrieve data from an entire collection of databases through a single connection. You can find a step-by-step tutorial here for getting started, along with detailed documentation.

Imagine you are a software provider in the cloud offering online ordering services for a collection of large stores. To support this and allow unlimited scale, you’ve provisioned separate databases in Azure to handle each store’s business — and this set of databases shares a common schema. While your application directs customer transactions to the appropriate store’s database, you periodically want to generate reports that query activity across all stores, or extract all sales data from the prior hour into a BI system or even an Excel spreadsheet. These types of reports and data integration scenarios require you to query the entire set of databases at once and generate a unified result set. Elastic database query lets you achieve this simply by issuing standard SQL requests to a single SQL database.

If you already have a collection of databases with common schema in Azure that you would like to query as a set, getting started with elastic database query is straightforward. During the initial setup, you define remote databases using the Elastic DB client library, and you also define “external tables” that function like a view over the data on the underlying databases. Once those are defined, you can query them from your tools or applications – just like regular SQL Server tables.  Our tutorial walks you through the necessary steps with a sample schema and set of databases.

Initially, elastic database query is supported on the Premium service tier of SQL DB as the query endpoint, while the collection of databases involved in queries may be any tier. (This Premium restriction is temporary for the early stages of Preview.) Support and community discussion of the feature is available through MSDN Forums and StackOverflow.

We look forward to your feedback!