【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

时间:2021-10-30 19:46:00

原文:【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表


经常在论坛中看到有人问数据同步的技术,如果只是同步少量的表,那么可以考虑使用链接服务器+触发器,来实现数据同步,但当要同步的数据表比较多,那么可以考虑用数据库复制技术,来实现数据的同步。

一、使用场景

数据发布和订阅,就是sql server的数据库复制技术,用于同步数据。我觉得使用情况主要有2种:

1、适合于数据分散在不同的地区的情况。

比如,总公司,还有分公司,可能在北京,上海,广州,等地区,那么每个地区都有地区数据库,都会有销售数据,那么通过对每个分公司的数据库进行发布,然后总公司进行订阅,那么数据就从分公司复制到总公司,也就是发布端复制到了订阅端。

2、如果数据库很大,压力很大,读写很频繁,那么为了分担单个数据库服务器的压力,可以通过数据库复制技术,来实现读写分离。

可以从一个数据库,把数据复制到多台服务器上,主数据库用于写,而其他几台服务器用于读取数据,减轻主数据库的压力。

3、总结一下:

一个是用于分布式数据的情况。

一个是数据库的高可用性,不仅能通过读写分离,提高性能,减轻压力,同时还能起到数据库备份的作用。

因为太大的数据库,要再备份,估计需要花很长的时间,所以通过数据库复制,也保证了数据的安全。

二、发布类型

不同的发布类型,会有不同的效果,适用于不同的场景,特别是下面的2种发布类型,需要特别注意:

快照发布:

发布服务器按预定的时间间隔向订阅服务器发送已发布数据的快照。也就是间隔时间一到,2000把数据的快照,也就是所有数据,都发生到订阅端,一般适合同步间隔时间比较长的,比如1周才同步一次。

事务发布:

在订阅服务器收到已发布数据的初始快照后,发布服务器将事务流式传输到订阅服务器。

也就是,设置好后,第一次会进行初始化,然后以后每次同步的时候,只是把修改的数据,比如insert、update、delete,同步到订阅端,一般适合对同步时间要求比较严格的情况,间隔时间可能就10秒,或者几分钟。

总结一下:

快照复制,每次都是复制所有的数据,所以如果数据量大,那么往往复制的时间会很长。

而事务复制,每次只复制变化的部分,所以速度较快。

三、实验环境

一台是笔记本,机器名:ggg-pc,实例名:MSSQLSERVER

另一台是安装的虚拟机,机器名:DTSCIOW1G2GJOED,实例名:MSSQLSERVER。

那么,如果不是虚拟机,能否配置成功呢?

我也尝试过在两台笔记本之间,也做了事务复制,配置与上面的类似,也同步成功了。

需要注意的是在配置完成订阅端成功后,发现在复制目录下面本地订阅目录下,没有出现订阅,这个多刷新几次就好了。

还有就是数据库引擎服务和代理服务,都是通过Local System帐号来启动的,不一定需要Administrator帐号。

需要特别说明的是这个数据库复制,并不是实时的,最短复制间隔为10秒,所以不能完全满足高安全性的要求,也就是当出现故障时,会有少量数据没有复制到订阅端。

四、首先,分别在两个实例中分别运行下面的代码,来创建数据库:


  1. --在笔记本上
  2. create database wc
  3. go
  4. use wc
  5. go
  6. create table A
  7. (
  8. id int primary key,
  9. v varchar(100)
  10. )

  1. --在虚拟机上
  2. create database wc
  3. go
  4. use wc
  5. go
  6. create table B
  7. (
  8. id int primary key,
  9. v varchar(100),
  10. vv varchar(100) --注意:这里比A表多了一列
  11. )
  12. go

五、然后,接下来就是要配置同步与订阅,来实现把A表的2列,同步到B表的2列中:

首先是设置发布:

1、展开“复制”目录,选择“本地发布”,右键,选择“新建发布”

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

2、弹出新建发布向导,单击下一步

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

3、选择:把ggg-pc作为自己的分发服务器,单击下一步

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

4、选择:是,将SQL Server的代理服务器配置为自动启动

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

5、选择:快照文件夹,默认是

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

6、选择:要发布的数据库,这里选wc

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

7、选择:发布类型,这里选择“事务发布”

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

8、选择要发布的表A,以及要发布的列:id和v

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

然后,点一下A表,点击右边的“项目属性”按钮,在弹出的菜单中选择“设置突出显示的 表的项目属性”,弹出“项目属性”对话框:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

然后,往下拉,找“目标对象”,由于需要同步到的不是A表,而是已存在的B表,所以把“目标对象名称”改为“B”,同时把“名称已被使用时的操作”改为“现有对象保持不变”:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

9、在筛选表行中,你可以设置只同步满足条件的记录,而不是所有的记录:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

10、设置快照代理,在两个复选框上都打上勾,也就是立即启动快照、计划快照代理:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

然后,单击右边的”更改“按钮,设置同步的时间间隔为 10秒:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

11、代理安全性:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

然后,单击 安全设置 ,弹出 快照代理安全性 对话框,选择 在SQL Server代理账户下运行,在“连接到订阅服务器”中,选择“使用以下SQL Server登录名”,输入:sa和密码。

12、如果你希望生成脚本,也可以勾选”生成包含创建发布的脚本文件“

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

13、输入发布名称:wc_publisher,然后下一步,点击完成,就发布成功了。

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

接下来,是设置订阅,这个是在虚拟机上设置的:

1、展开“复制”,右键“本地订阅”,选择“新建订阅”:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

2、弹出新建订阅对话框,单击下一步:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

3、在发布服务器下拉框中,选择“查找SQL Server发布服务器”:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

然后,选择ggg-pc服务器,然后输入连接到ggg-pc的用户名和密码:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

4、分发代理位置, 选择“在分发服务器ggg-pc上运行所有代理”:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

5、选择:订阅服务器和订阅数据库:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

6、设置分发代理安全性

点击按钮,在弹出的对话框中设置:“在SQL Server代理账户下运行”,在连接到订阅服务器上,选择“通过以下SQL Server登录名”,并输入连接到订阅服务器的:用户名和密码

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

7、同步计划,点击下一步

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

8、初始化订阅,单击下一步,最后点击完成

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

9、创建订阅成功

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

六、最后,验证一下数据是否同步

我们在ggg-pc的wc数据库的A表中插入数据:


  1. --插入数据
  2. insert into a
  3. values(1,'aaa')

然后,到虚拟机的wc数据库中,查询B表的数据:

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表

如果不需要再进行数据库的复制,那么只需要直接在发布端进行删除就可以,主要就是删除复制目录-》本地发布-》发布的项目删除,然后,右键“复制”,选择“禁用本地发布”,就可以删除分发数据库distribution,会自动删除订阅端,不需要在订阅端进行删除。

【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表
【SQL Server高可用性】数据库复制:SQL Server 2008R2中通过数据库复制,把A表的数据复制到B表
发布了416 篇原创文章 · 获赞 135 · 访问量 94万+