Azure SQL Database跨库查询不支持问题及迁移适配咨询

Hey there! I’ve helped several folks migrate from on-prem SQL Server 2012 to Azure SQL Database with cross-database dependencies, so let’s break down exactly how to replace those cross-db queries with external tables, plus key performance tweaks to keep your workload running smoothly.

1. 前期准备 #

Before diving into external tables, let’s lay the groundwork:

• Keep databases in the same Azure region: Cross-region calls add significant latency, so if possible, host all your Azure SQL Databases in the same region.
• Create a database master key in each database that needs to access external data (if you don’t already have one):

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourSuperStrongPassword123!';
  

  This encrypts the credentials we’ll use to connect to remote databases.
• Create a database-scoped credential for each remote database you need to access. This uses a valid login from the remote SQL DB:

  CREATE DATABASE SCOPED CREDENTIAL RemoteDB2_Credential
  WITH IDENTITY = 'YourRemoteDBAdminLogin',
  SECRET = 'YourRemoteDBAdminPassword';
  

  Replace the identity and secret with the actual login credentials for your target remote database.

2. 配置外部数据源 #

Next, define the connection to your remote database using the credential we just created:

CREATE EXTERNAL DATA SOURCE RemoteDB2_Datasource
WITH (
    TYPE = RDBMS,
    LOCATION = 'your-azure-sql-server-name.database.windows.net',
    DATABASE_NAME = 'Database2', -- 替换为你的远程数据库名称
    CREDENTIAL = RemoteDB2_Credential
);

Repeat this step for each remote database (比如Database3) you need to query.

3. 创建外部表 #

Now map the remote tables to external tables in your local database. The schema must match exactly—same column names, data types, and nullability as the remote table. For example, to mirror Database2.dbo.table1:

CREATE EXTERNAL TABLE [dbo].[External_Database2_Table1]
(
    ID INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    CreatedDate DATETIME2(7) NULL
)
WITH (
    DATA_SOURCE = RemoteDB2_Datasource,
    SCHEMA_NAME = 'dbo', -- 远程表的Schema
    OBJECT_NAME = 'table1' -- 远程表的名称
);

• 小提示：如果后续远程表结构变更，你需要删除并重新创建外部表以保持结构一致。
• 对于存储过程，只需将Database2.dbo.table1这类引用替换为dbo.External_Database2_Table1即可，基础查询无需额外修改。

4. 更新存储过程中的跨库逻辑 #

对于使用跨库JOIN或子查询的存储过程，将远程表引用替换为新的外部表即可。例如：

原本地查询： #

SELECT t1.ID, t2.CustomerName
FROM Database1.dbo.Orders t1
JOIN Database2.dbo.Customers t2 ON t1.CustomerID = t2.CustomerID
WHERE t1.OrderDate > '2024-01-01';

更新后的Azure SQL DB查询： #

SELECT t1.ID, t2.CustomerName
FROM dbo.Orders t1
JOIN dbo.External_Database2_Customers t2 ON t1.CustomerID = t2.CustomerID
WHERE t1.OrderDate > '2024-01-01';

External tables work great, but you’ll want to optimize them to avoid slow queries. Here’s what to focus on:

1. 减少数据传输量 #

• 避免使用SELECT *：只查询你需要的列，这能减少数据库间传输的数据量。
• 将过滤逻辑推送到远程数据库：编写WHERE子句让数据在源端先过滤，而不是全量拉取到本地后再筛选。Azure SQL会自动将这类过滤逻辑推送到远程数据库（如果可行）。例如：

  -- 推荐写法：过滤逻辑在远程端执行
  SELECT ID, Name FROM dbo.External_Database2_Table1 WHERE CreatedDate > '2024-01-01';
  

  你可以通过查看执行计划中的Remote Query算子，确认过滤逻辑是否已推送到远程端。

2. 优化远程表索引 #

确保远程表上有支持你查询模式的索引。比如如果你经常用CustomerID做JOIN，就在远程Customers表的CustomerID列上创建索引，这能加速远程端的查询执行，减少传输的数据量。

3. 为频繁查询创建索引视图 #

如果某个跨库查询经常运行，可以在本地数据库创建索引视图来缓存结果，避免重复的跨库调用：

CREATE VIEW dbo.vw_OrderCustomerSummary
WITH SCHEMABINDING
AS
SELECT t1.OrderID, t2.CustomerName, COUNT_BIG(*) AS OrderCount
FROM dbo.Orders t1
JOIN dbo.External_Database2_Customers t2 ON t1.CustomerID = t2.CustomerID
GROUP BY t1.OrderID, t2.CustomerName;

CREATE UNIQUE CLUSTERED INDEX IX_vw_OrderCustomerSummary ON dbo.vw_OrderCustomerSummary(OrderID);

现在查询这个视图时，会直接使用缓存的索引数据，而不是每次都执行跨库JOIN。

4. 减少网络往返次数 #

如果你有多个小型跨库查询，将它们合并为一个查询，减少网络延迟。比如用JOIN或UNION把多个查询的结果合并，一次获取所有需要的数据。

5. 监控与调优 #

• 使用Azure门户中的Azure SQL Database 查询性能洞察识别慢跨库查询，重点关注高耗时的Remote Query算子。
• 查看执行计划寻找瓶颈：如果远程查询耗时过长，调整查询逻辑或为远程表添加索引。

6. 使用弹性池（如果适用） #

如果所有Azure SQL数据库都在同一个弹性池中，跨库查询的性能会更好——因为数据库共享资源，内部网络延迟更低。

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