SQL Server实时同步更新远程数据库遇到的问题

共计 3668 个字符，预计需要花费 10 分钟才能阅读完成。

工作中遇到这样的情况，需要在更新表 TableA（位于服务器 ServerA 172.16.8.100 中的库 DatabaseA）同时更新 TableB（位于服务器 ServerB 172.16.8.101 中的库 DatabaseB）。

TableA 与 TableB 结构相同，但数据数量不一定相同，应为有可能 TableC 也在更新 TableB。由于数据更新不频繁，为简单起见想到使用了触发器 Tirgger。记录一下遇到的一些问题：

1. 访问异地数据库

在 ServerA 中创建指向 ServerB 的链接服务器，并做好账号映射。addlinkedserver 存储过程创建一个链接服务器，参数详情参见官方文档。第 1 个参数 LNK_ServerA 是自定义的名称；第 2 参数产品名称，如果是 SQL Server 不用提供；第 3 个参数是驱动类型；第 4 个参数是数据源，这里写 SQL Server 服务器地址

exec sp_addlinkedserver 'LNK_ServerB_DatabaseB','','SQLNCLI','172.16.8.101'

配置链接服务器后，默认使用同一本地账号登陆远程数据库，如果账号有不同，还需要进行账号映射。sp_addlinkedsrvlogin 参数详情参见官方文档。第 1 个参数同上；第 2 个参数 false 即使用后面参数提供的用户密码登陆；第 3 个参数 null 使所有本地账号都可以使用后面的用户密码来登陆链接服务器，如果第 3 个参数设置为一个本地 SQL Server 登陆用户名，那么只有这个用户才可以使用远程账号登陆链接服务器；最后两个是登录远程服务器的用户和密码。

exec sp_addlinkedsrvlogin 'LNK_ServerB_DatabaseB','false',null,'user','password'

如果要删除以上配置可以如下

exec sp_droplinkedsrvlogin 'LNK_ServerB_DatabaseB',null
exec sp_dropserver 'LNK_ServerB_DatabaseB','droplogins'

上面的配置在 SQL Server Management Studio 管理器里 Server Objects 下 LinkedServers 可以查询到，如果一切链接正常，可以直接打开链接服务器上的库表

值得注意的是以上两个存储过程不能出现在触发器代码中，而是事先在服务器 ServerA 中运行完成配置，否则触发器隐式事务的要求会报错“The procedure ‘sys.sp_addlinkedserver’ cannot be executed within a transaction.”

2. 配置分布式事务

SQL Server 的触发器是隐式使用事务的，链接服务器是远程服务器，需要在本地服务器和远程服务器之间开启分布式事务处理，否则会报“The partner transaction manager has disabled its support for remote/network transactions”的错误。我在 ServerA 和 ServerB 中都开启分布式事务协调器，并进行适当配置，以支持分布式事务。ServerA 和 ServerB 都是 Windows Server 2012 R2，其他版本服务器类似。

（1）首先在 Services.msc 中确认 Distributed Transaction Coordinator 已经开启，其他版本的服务器不一定默认安装，需要安装 windows features 的方式先进行该特性的安装。

（2）在服务器管理工具 Administrative Tools 中找到 Component Services，在 Local DTC 中属性 Security 选项卡中配置如下，打开相关安全设置，完成后会重启服务，也有文档称需要重启服务器，但是至少 2012 R2 不用。

（3）配置防火墙，Inbound 和 Outbound 都打开

3. 数据库字段 text, ntext 的处理

业务中表 TableA 中有一个 Content 字段是 ntext 类型，同步到 TableB 时需要对内容做一些替换处理。对于 text 和 ntext 类型是一个过时的类型，微软官方建议用 (N)VARCHAR(MAX) 替换，可查阅这里。今后设计时可以考虑，这里我们考虑对 ntext 进行处理。

但是在触发器中，inserted 和 deleted 表都是不允许对 text/ntext/image 类型进行处理的，这里我们采用一个曲线救国的办法，从数据库中把记录读取到临时表中，然后通过 textptr 和 patindex 函数和 updatetext 命令完成字符串替换处理

特别注意以上代码对于 text 类型处理中文时会出问题，由于 text 存储 non-unicode 的数据，patindex 会将中文字符解释为 1 个字符，而 updatetext 命令却将中文字符解释为 2 个字符。SQL Server 2005 以上版本可以这样做替换：

update #temp_tablea set Content=cast(replace(cast(Content as nvarchar(max)),@s,@d) as text)

4. 执行远程数据库操作

当配置链接服务器时，我们可以直接访问远程数据库表了，如下

insert into LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB ...
update LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB set ...

但简陋的 SQL 编辑器往往会对语法报错，另外为方便编程，我们希望通过 exec sp_executesql 的方式获得更多的灵活性。其实 exec 就可以直接执行 sql 语句，但如果有返回值就比较困难了。如下，从远程服务器上通过 ID 查询表 TableB 后返回 Name，sp_executesql 存储过程可以使用 output 关键字定义变量为返回变量，其中 @Name output 为返回变量，@ID 则是传入变量。

declare @sql nvarchar(500), @Name nvarchar(50),@ID nvarchar(40)
set @SQL=N'select @Name=Name from LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB where ID=@ID'
exec sp_executesql @SQL,N'@Name nvarchar(50) output,@ID nvarchar(40)',@Name output,@ID

另外 exec 直接执行 sql 语句，本质上是执行拼接后的 sql 字符串，有时将变量拼接进字符串会困难的多（到底需要几个单引号），而 sp_executesql 则清晰多了

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-05/143670.htm