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

这篇文章主要为大家详细介绍了SQL Server实时同步更新远程数据库遇到问题的简单示例，具有一定的参考价值，可以用来参考一下。

感兴趣的小伙伴，下面一起跟随四海网的小编两巴掌来看看吧！

工作中遇到这样的情况，需要在更新表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字段是text类型，同步到TableB时需要对内容做一些替换处理。对于text类型是一个过时的类型，微软官方建议用(N)VARCHAR(MAX)替换，可查阅这里。今后设计时可以考虑，这里我们考虑对text进行处理。

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

代码如下：

if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#temp_tablea')) 
 drop table #temp_tablea
select * into #temp_tablea from TableA where ID = @ID
declare @s varchar(200),@d varchar(200)
select @s='="/_target/',@d='="/_replacement/'
declare @p varbinary(16),@postion int,@l int
select @p=textptr(Content),@l=len(@s),@postion=patindex('%'+@s+'%',Content)+1 from #temp_tablea
while @postion>1
begin
 updatetext #temp_tablea.Content @p @postion @l @d
 select @postion=patindex('%'+@s+'%',Content)+1 from #temp_tablea
end

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则清晰多了

代码如下：

declare @SQL nvarchar(500),@Name nvarchar(50),@Count int,@ID nvarchar(40)
set @Name=N'Cat'
set @Count=0
set @ID=N'{00000000-0000-0000-0000-000000000000}'
set @SQL=N'update TableA set Name='''+@Name+''', Count='+@Count+' where ID='''+@ID+''''
exec(@SQL)
set @SQL=N'update TableA set Name=@Name,Count=@Count where ID=@ID'
exec sp_executesql @SQL, N'@Name nvarchar(50),@Count int,@ID nvarchar(40)',@Name,@Count,@ID

以上所述是小编给大家介绍的SQL Server实时同步更新远程数据库遇到的问题小结，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对四海网网站的支持！

本文来自：http://www.q1010.com/179/9187-0.html

注：关于SQL Server实时同步更新远程数据库遇到问题的简单示例的内容就先介绍到这里，更多相关文章的可以留意四海网的其他信息。

关键词：SQL SERVER