SQL Server中怎么实时同步更新远程数据库
SQL Server中怎么实时同步更新远程数据库,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。
1. 访问异地数据库
在ServerA 中创建指向ServerB的链接服务器,并做好账号映射。addlinkedserver存储过程创建一个链接服务器,参数详情参见官方文档。第1个参数LNK_ServerA是自定义的名称;第2参数产品名称,如果是SQL Server不用提供;第3个参数是驱动类型;第4个参数是数据源,这里写SQL Server服务器地址
execsp_addlinkedserver'LNK_ServerB_DatabaseB','','SQLNCLI','172.16.8.101'
配置链接服务器后,默认使用同一本地账号登陆远程数据库,如果账号有不同,还需要进行账号映射。sp_addlinkedsrvlogin参数详情参见官方文档。第1个参数同上;第2个参数false即使用后面参数提供的用户密码登陆;第3个参数null使所有本地账号都可以使用后面的用户密码来登陆链接服务器,如果第3个参数设置为一个本地SQL Server登陆用户名,那么只有这个用户才可以使用远程账号登陆链接服务器;最后两个是登录远程服务器的用户和密码。
execsp_addlinkedsrvlogin'LNK_ServerB_DatabaseB','false',null,'user','password'
如果要删除以上配置可以如下
execsp_droplinkedsrvlogin'LNK_ServerB_DatabaseB',nullexecsp_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命令完成字符串替换处理
ifexists(select*fromtempdb..sysobjectswhereid=object_id('tempdb..#temp_tablea'))droptable#temp_tableaselect*into#temp_tableafromTableAwhereID=@IDdeclare@svarchar(200),@dvarchar(200)select@s='="/_target/',@d='="/_replacement/'declare@pvarbinary(16),@postionint,@lintselect@p=textptr(Content),@l=len(@s),@postion=patindex('%'+@s+'%',Content)+1from#temp_tableawhile@postion>1beginupdatetext#temp_tablea.Content@p@postion@l@dselect@postion=patindex('%'+@s+'%',Content)+1from#temp_tableaend
4. 执行远程数据库操作
当配置链接服务器时,我们可以直接访问远程数据库表了,如下
insertintoLNK_ServerB_DatabaseB.DatabaseB.dbo.TableB...updateLNK_ServerB_DatabaseB.DatabaseB.dbo.TableBset...
但简陋的SQL编辑器往往会对语法报错,另外为方便编程,我们希望通过exec sp_executesql的方式获得更多的灵活性。其实exec就可以直接执行sql语句,但如果有返回值就比较困难了。如下,从远程服务器上通过ID查询表TableB后返回Name,sp_executesql存储过程可以使用output关键字定义变量为返回变量,其中@Name output为返回变量,@ID则是传入变量。
declare@sqlnvarchar(500),@Namenvarchar(50),@IDnvarchar(40)set@SQL=N'select@Name=NamefromLNK_ServerB_DatabaseB.DatabaseB.dbo.TableBwhereID=@ID'execsp_executesql@SQL,N'@Namenvarchar(50)output,@IDnvarchar(40)',@Nameoutput,@ID
另外exec直接执行sql语句,本质上是执行拼接后的sql字符串,有时将变量拼接进字符串会困难的多(到底需要几个单引号),而sp_executesql则清晰多了
declare@SQLnvarchar(500),@Namenvarchar(50),@Countint,@IDnvarchar(40)set@Name=N'Cat'set@Count=0set@ID=N'{00000000-0000-0000-0000-000000000000}'set@SQL=N'updateTableAsetName='''+@Name+''',Count='+@Count+'whereID='''+@ID+''''exec(@SQL)set@SQL=N'updateTableAsetName=@Name,Count=@CountwhereID=@ID'execsp_executesql@SQL,N'@Namenvarchar(50),@Countint,@IDnvarchar(40)',@Name,@Count,@ID
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注亿速云行业资讯频道,感谢您对亿速云的支持。
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。