跨数据库查询：如何正确使用跨库功能？Azure SQL拆分方案选择

Hey there! Let's break down these two tools and figure out which fits your cross-database query scenario best. I’ve worked with both on Azure SQL projects, so I’ll share practical insights instead of just regurgitating docs.

First, What Are They? #

EXTERNAL DATA SOURCE #

This is the low-level building block in Azure SQL Database that lets you define a connection to an external data source—could be another Azure SQL DB, an on-prem SQL Server instance, even Blob Storage. To use it, you’ll pair it with either:

• EXTERNAL TABLE: Maps a table in the external DB to a "virtual table" in your local DB, so you can query it like a native table.
• OPENQUERY: Runs a pass-through query directly on the external source, returning results to your local DB.

Here’s a quick example of setting it up for another Azure SQL DB:

-- Create a master key to encrypt credentials
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword123!';

-- Create a database scoped credential for the external DB
CREATE DATABASE SCOPED CREDENTIAL ExternalDBCredential
WITH IDENTITY = 'ExternalDBUser', SECRET = 'ExternalDBPassword';

-- Define the external data source
CREATE EXTERNAL DATA SOURCE ExternalSalesDB
WITH (
    TYPE = RDBMS,
    LOCATION = 'externalsalesdb.database.windows.net',
    DATABASE_NAME = 'SalesDB',
    CREDENTIAL = ExternalDBCredential
);

-- Create an external table pointing to the Sales table in the external DB
CREATE EXTERNAL TABLE dbo.ExternalSales (
    SaleID INT,
    CustomerID INT,
    Amount DECIMAL(18,2)
)
WITH (
    DATA_SOURCE = ExternalSalesDB,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'Sales'
);

-- Now you can query it like a local table
SELECT * FROM dbo.ExternalSales WHERE CustomerID = 123;

Elastic Query #

This is a higher-level solution built on top of EXTERNAL DATA SOURCE, specifically designed for Azure SQL Database scenarios like:

• Sharding (splitting a single DB into multiple "shard" DBs, e.g., one per region or customer segment)
• Cross-database joins/aggregations that need performance optimizations

Elastic Query adds smart features like shard routing: if you define a shard key (e.g., CustomerID), it will automatically send your query only to the relevant shards instead of hitting all of them, which saves bandwidth and improves speed. It also lets you write seamless cross-database queries without messy pass-through syntax.

Example setup for a sharded environment:

-- First, create the external data source (similar to above)
CREATE EXTERNAL DATA SOURCE ShardDataSource
WITH (
    TYPE = SHARD_MAP_MANAGER,
    LOCATION = 'shardmapmanager.database.windows.net',
    DATABASE_NAME = 'ShardMapDB',
    CREDENTIAL = ShardMapCredential
);

-- Define the shard map (maps shard keys to specific DBs)
CREATE EXTERNAL TABLE dbo.ShardedSales (
    SaleID INT,
    CustomerID INT, -- This is our shard key
    Amount DECIMAL(18,2)
)
WITH (
    DATA_SOURCE = ShardDataSource,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'Sales',
    DISTRIBUTION = HASH(CustomerID) -- Tells Elastic Query how data is sharded
);

-- Query only the shards that have CustomerID 123
SELECT * FROM dbo.ShardedSales WHERE CustomerID = 123;
-- Elastic Query automatically routes this to the correct shard!

Key Differences to Help You Choose #

Practical Recommendations #

• Go with Elastic Query if:

  • You’re using Azure SQL Database sharding (splitting your DB into multiple shards)
  • You need to run frequent, complex cross-database joins or aggregations
  • You want minimal changes to your existing queries (since it uses native SQL syntax)
  • Performance is a top priority (its shard routing will save you a lot of overhead)

• Go with EXTERNAL DATA SOURCE if:

  • You only need to run occasional, simple queries against another Azure SQL DB
  • You need to connect to non-SQL external sources (like Blob Storage)
  • You want a lightweight, low-maintenance setup
  • Your cross-db needs are small-scale (no sharding involved)

Quick Final Tip #

If you’re just starting out with splitting your DB, try EXTERNAL DATA SOURCE first for simple cross-db queries. If you later scale to a sharded architecture, you can easily transition to Elastic Query since it builds on the same external data source foundation.

内容的提问来源于stack exchange，提问作者Support Ukraine