sql server 远程同步数据库

2017-09-07 10:31:54来源:oschina作者:_van_luo人点击

分享

在本地局域网内或者外网有两台安装有sqlserver2008的机器(注意:已发布的快照版本无法向老版本数据库兼容,意味着2008下创建的事务或快照发布,无法被sqlserver2005订阅)


1.在要发布的数据库上创建一个数据库(这里叫做dnt_new),然后在该数据库实例的左侧导航的“复制”--“本地发布”上击右键,然后选择“新建发布”,如下:



这样,系统就会启动‘发布向导’来引导大家,点击"下一步”,然后在当前窗口中选择要发布的数据库,如下:



点击下一步,然后在接下来的窗口中选择“事务发布”,如下图:



然后点击下一步,选择要同步的数据对象(数据表,存储过程,自定义函数等),如下:



然后就是“项目问题窗口”,因为之前已用dbo身份登陆,所以这里只要点击下一步即可,如下图:



这里可以通过“添加”方式来过滤要同步的数据信息,因为要做全表数据同步,所以这里不设置



然后在‘代理安全性’窗口中,点击“安全设置”按钮:



在弹出的‘安全设置’子窗口中设置如下信息,并点击‘确定’按钮:



然后点击下一步按钮:



选择“创建发布”复选框,然后点击下一步,这时向导会让您输入“发布名称”,这里命名为“dnt_new_snap”:



点击“完成按钮”,这里系统就开始根据之前收集的信息来创建该发布对象信息了,如下:



到这里,‘创建发布’的工作就完成了。下面介绍一下创建订阅的流程。在另一个机器的sqlserver实例上,打开该实例并使用“复制”—“新建订阅”,如下图:



这时系统就会启动“新建订阅”向导,点击下一步,并在“发布”窗口中的“发布服务器”下拉框中选择“查打发布sqlserver服务器”项,如下



然后在弹出窗口中选择之前‘创建发布时所使用的数据库实例’并进行验进登陆,这时,发布服务器的信息就会出现在下方的列表框中:



选择之前我们创建的那个发布对象“dnt_new_snap”,接着点击下一步:



在分发代理位置窗口中,选择“在分布服务器上运行所有代理”,然后点击下一步,然后在“订阅服务器”窗口中的订阅数据库列表框中选择一下要同步的订阅数据库名称(可新建):



点击下一步,然后在‘分发代理安全性’窗口中,点击列表框中的‘…’来设置安装性,并做如下设置(注意红框部分):



然后点击“确定”按钮,之后在向导上点击“下一步”按钮,这时系统就会显示“代理计划执行方式”窗口,选择“连续运行”或者自定义时间(自定义可以实现定时备份数据):



点击下一步,在窗口中选择“立即执行”:



完成了这一步,点击下一步按钮,然后就可以创建该订阅对象了,如果一切运行正常,sqlserver就会从‘发布服务器’那边,将之前指定的数据表和存储过程等同步到当前的‘订阅数据库’中了。这时我们可以在源数据库(发布服务器)上的表中添加或修改指定表数据信息,在等待1-3秒(或设定的时间)之后,所做的添加和修改就会同步到‘订阅数据库’上的相应表中


注:本文中的两台机器必定是可以使用sqlserver客户端互联(在sqlserver studio中设置'允许远程链接',同时要设置相应的ip地址,以及在配置管理器中开启tcp/ip协议即可)


注:


局域网SQL远程连接方法:


SQL2005 SQL2008远程连接配置方法


第一步(SQL2005、SQL2008):


开始-->程序-->Microsoft SQL Server 2008(或2005)-->配置工具-->SQL Server配置管理器-->SQL Server网络配置-->MSSQLSERVER(这个名称以具体实例名为准)的协议-->TCP/IP-->右键-->启用


第二步:


SQL2005:


开始-->程序-->Microsoft SQL Server 2005-->配置工具-->SQL Server 2005外围应用配置器-->服务和连接的外围应用配置器-->Database Engine -->远程连接,选择本地连接和远程连接并选上同时使用Tcp/Ip和named pipes.


SQL2008:


打开SQL Server Management Studio-->在左边[对象资源管理器]中选择第一项(主数据库引擎)-->右键-->方面-->在方面的下拉列表中选择[外围应用配置器]-->将RemoteDacEnable置为True.


Express:


如果XP有开防火墙,在例外里面要加入以下两个程序: C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Binn/sqlservr.exe, C:/Program Files/Microsoft SQL Server/90/Shared/sqlbrowser.exe


不仅要关闭Windows防火墙,杀毒软件防火墙也要关闭。


第三步:


开始-->程序-->Microsoft SQL Server 2008(或2005)-->配置工具-->SQL Server配置管理器-->SQL Server服务-->右击SQL Server(MSSQLSERVER)(注:括号内以具体实例名为准)-->重新启动

工作中遇到这样的情况,需要在更新表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可以查询到,如果一切链接正常,可以直接打开链接服务器上的库表


image


值得注意的是以上两个存储过程不能出现在触发器代码中,而是事先在服务器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的方式先进行该特性的安装。


image

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


image


(3)配置防火墙,Inbound和Outbound都打开


image


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


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


但是在触发器中,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>0
begin
updatetext #temp_tablea.Content @p @postion @l @d
select @postion=patindex('%'+@s+'%',Content)-1 from #temp_tablea
end

复制代码


特别注意以上代码对于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则清晰多了


复制代码


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

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台