Report across scaled-out cloud databases (preview)

2025-06-16

You can create reports from multiple databases from a single connection point using an elastic query. The databases must be horizontally partitioned (also known as "sharded").

If you have an existing database, see Migrate existing databases to scale out.

To understand the SQL objects needed to query, see Reporting across scaled-out cloud databases (preview).

Prerequisites

Download and run the Get started with Elastic Database Tools.

Create a shard map manager using the sample app

Here you'll create a shard map manager along with several shards, followed by insertion of data into the shards. If you happen to already have shards set up with sharded data in them, you can skip the following steps and move to the next section.

Build and run the Getting started with Elastic Database tools sample application by following the steps in the article section Download and run the sample app. Once you finish all the steps, you'll see the following command prompt:
In the command window, type 1 and press Enter. This creates the shard map manager, and adds two shards to the server. Then type 3 and press Enter; repeat the action four times. This inserts sample data rows in your shards.
The Azure portal should show three new databases in your server:

At this point, cross-database queries are supported through the Elastic Database client libraries. For example, use option 4 in the command window. The results from a multi-shard query are always a UNION ALL of the results from all shards.

In the next section, we create a sample database endpoint that supports richer querying of the data across shards.

Create an elastic query database

Open the Azure portal and sign in.
Create a new database in Azure SQL Database in the same server as your shard setup. Name the database ElasticDBQuery.

You can use an existing database. If you can do so, it must not be one of the shards that you would like to execute your queries on. This database is used for creating the metadata objects for an elastic database query.

Create database objects

Database-scoped master key and credentials

These are used to connect to the shard map manager and the shards:

Open SQL Server Management Studio or SQL Server Data Tools in Visual Studio.

Connect to ElasticDBQuery database and execute the following T-SQL commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master_key_password>';

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
WITH IDENTITY = '<username>',
SECRET = '<password>';

Both "username" and "password" should be the login information you created in previous steps.

External data sources

To create an external data source, execute the following command on the ElasticDBQuery database:

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
    (TYPE = SHARD_MAP_MANAGER,
    LOCATION = '<server_name>.database.windows.net',
    DATABASE_NAME = 'ElasticScaleStarterKit_ShardMapManagerDb',
    CREDENTIAL = ElasticDBQueryCred,
    SHARD_MAP_NAME = 'CustomerIDShardMap'
) ;

CustomerIDShardMap is the name of the shard map, if you created the shard map and shard map manager using the elastic database tools sample. However, if you used your custom setup for this sample, then it should be the shard map name you chose in your application.

External tables

Create an external table that matches the Customers table on the shards by executing the following command on ElasticDBQuery database:

CREATE EXTERNAL TABLE [dbo].[Customers]
( [CustomerId] [int] NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [RegionId] [int] NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc,
    DISTRIBUTION = SHARDED([CustomerId])
) ;

Execute a sample elastic database T-SQL query

Once you have defined your external data source and your external tables, use T-SQL to query your external tables.

Execute this query on the ElasticDBQuery database:

select count(CustomerId) from [dbo].[Customers];

You'll notice that the query aggregates results from all the shards and gives the following output:

Output details.

Import elastic database query results to Excel

You can import the results from of a query to an Excel file.

Launch Microsoft Excel.
Navigate to the Data ribbon.
Select From Other Sources and select From SQL Server.
In the Data Connection Wizard, type the server name and login credentials. Then select Next.
In the dialog box Select the database that contains the data you want, select the ElasticDBQuery database.
Select the Customers table in the list view and select Next. Then select Finish.
In the Import Data form, under Select how you want to view this data in your workbook, select Table. Select OK.

All the rows from Customers table, stored in different shards populate the Excel sheet.

You can now use Excel's powerful data visualization functions. You can use the connection string with your server name, database name, and credentials to connect your BI and data integration tools to the elastic query database. Make sure that SQL Server is supported as a data source for your tool. You can refer to the elastic query database and external tables just like any other SQL Server database and SQL Server tables that you would connect to with your tool.

Cost

There's no additional charge for using the Elastic Database Query feature.

For pricing information, see SQL Database Pricing Details.

Share via