无复制方案下将Azure托管SQL Server数据同步至本地服务器的需求

一、解决同名Azure服务器的连接冲突问题 #

由于两个Azure SQL Server实例名称相同，直接通过SSMS配置链接服务器会出现冲突，借助ODBC数据源可绕过该限制：

• 打开对应位数的ODBC数据源管理器（匹配你的SQL Server版本），添加两个ODBC Driver 17 for SQL Server数据源
• 为每个数据源自定义名称（比如Azure_Report_DB和Azure_Test_DB），分别配置对应Azure数据库的完整服务器地址（xxx.database.windows.net）、数据库名及只读权限的认证信息，测试连接确保正常访问
• 后续可通过两种方式访问这两个库：
  1. 创建独立链接服务器：在SSMS中新建链接服务器，选择「其他数据源」→「ODBC驱动」，指定自定义的ODBC数据源名称，两个链接服务器用不同标识（比如LS_Azure_Report和LS_Azure_Test）
  2. 直接用OPENQUERY调用：无需创建链接服务器，语法示例：SELECT * FROM OPENQUERY(Azure_Report_DB, 'SELECT * FROM dbo.YourTable')

二、定期数据同步的实现方案 #

结合你可用的SQL Server 2012/2019版本，推荐以下无复制的同步方案：

方案1：T-SQL脚本 + SQL Server Agent（全版本兼容） #

适合轻量同步需求，通过脚本实现增量/全量同步，借助Agent定期执行：

1. 编写同步逻辑：优先采用增量同步减少数据传输，依赖Azure表中的时间戳/自增ID字段（比如LastUpdated），示例脚本：

-- 同步Azure_Report_DB的Customer表到本地Report_DB
MERGE Local_Report_DB.dbo.Customer AS t
USING (
    SELECT CustomerID, Name, Email, LastUpdated 
    FROM OPENQUERY(Azure_Report_DB, 'SELECT * FROM dbo.Customer WHERE LastUpdated > ''' + CONVERT(VARCHAR(20), (SELECT MAX(LastUpdated) FROM Local_Report_DB.dbo.Customer), 120) + '''')
) AS s
ON t.CustomerID = s.CustomerID
WHEN MATCHED THEN 
    UPDATE SET t.Name = s.Name, t.Email = s.Email, t.LastUpdated = s.LastUpdated
WHEN NOT MATCHED THEN 
    INSERT (CustomerID, Name, Email, LastUpdated)
    VALUES (s.CustomerID, s.Name, s.Email, s.LastUpdated);

• 若没有增量字段，只能用全量同步（效率较低）：

TRUNCATE TABLE Local_Test_DB.dbo.Product;
INSERT INTO Local_Test_DB.dbo.Product
SELECT * FROM OPENQUERY(Azure_Test_DB, 'SELECT * FROM dbo.Product');

2. 创建SQL Server Agent作业：
   • 新建作业，添加「T-SQL脚本」步骤执行同步脚本
   • 设置作业计划（比如每天凌晨2点）
   • 配置通知机制（邮件/日志）追踪同步状态

方案2：SQL Server Integration Services (SSIS)（全版本兼容） #

适合多表、需数据转换的复杂同步场景：

1. 新建SSIS项目，添加三个连接管理器：
   • 两个ODBC连接管理器，关联之前创建的Azure_Report_DB和Azure_Test_DB
   • 一个OLE DB连接管理器，关联本地目标数据库
2. 设计数据流任务：
   • 从ODBC源拉取数据，用「查找」组件对比本地表的增量字段，筛选出需更新/插入的数据
   • 将处理后的数据写入本地OLE DB目标表
   • 添加「日志记录」组件追踪同步错误
3. 部署与调度：
   • 将SSIS包部署到SQL Server（2012可用文件系统或SSIS目录，2019推荐SSIS目录）
   • 通过SQL Server Agent创建作业，定期执行SSIS包

方案3：PolyBase（仅SQL Server 2019及以上） #

适合大规模数据同步，支持直接查询外部数据源：

1. 启用PolyBase：

sp_configure 'polybase enabled', 1;
RECONFIGURE;

2. 创建数据库级凭据存储Azure只读账号信息：

CREATE DATABASE SCOPED CREDENTIAL AzureSQL_Read_Credential
WITH IDENTITY = 'your_read_username',
SECRET = 'your_read_password';

3. 创建对应两个Azure数据库的外部数据源：

-- 关联Report库
CREATE EXTERNAL DATA SOURCE Azure_Report_External
WITH (
    LOCATION = 'odbc://<azure_server_name>.database.windows.net',
    CONNECTION_OPTIONS = 'Database=Report_DB',
    CREDENTIAL = AzureSQL_Read_Credential,
    TYPE = ODBC
);

-- 关联Test库
CREATE EXTERNAL DATA SOURCE Azure_Test_External
WITH (
    LOCATION = 'odbc://<azure_server_name>.database.windows.net',
    CONNECTION_OPTIONS = 'Database=Test_DB',
    CREDENTIAL = AzureSQL_Read_Credential,
    TYPE = ODBC
);

4. 创建外部表映射Azure源表：

CREATE EXTERNAL TABLE dbo.External_Customer (
    CustomerID INT,
    Name VARCHAR(100),
    Email VARCHAR(100),
    LastUpdated DATETIME
)
WITH (
    LOCATION = 'dbo.Customer',
    DATA_SOURCE = Azure_Report_External
);

5. 编写同步脚本并通过Agent定期执行：

INSERT INTO Local_Report_DB.dbo.Customer
SELECT * FROM dbo.External_Customer
WHERE LastUpdated > (SELECT MAX(LastUpdated) FROM Local_Report_DB.dbo.Customer);

三、注意事项 #

• 确保ODBC驱动版本与SQL Server版本兼容（推荐ODBC Driver 17 for SQL Server）
• 增量同步依赖Azure表的可排序字段（时间戳/自增ID），若无此类字段，建议联系Azure库管理员添加，否则只能用全量同步
• 同步作业尽量避开业务高峰，减少对Azure数据库的只读访问压力
• 定期检查同步日志，处理连接超时、权限变更等异常情况

内容的提问来源于stack exchange，提问作者Schloer Family Rusty Nail Forg