SQL Server HA - 数据库复制（Replication）

最新推荐文章于 2024-06-03 17:16:35 发布

weixin_33690963

最新推荐文章于 2024-06-03 17:16:35 发布

阅读量491

点赞数

原文链接：http://blog.51cto.com/57388/1852208

版权

From: http://www.tuicool.com/articles/z2Y36f

一、名词解释

1、复制的机构组成（类比报纸流通）：

1)、发布服务器（报社出版）

生产维护数据源，审阅所有出版数据的更改发送给分发服务器（邮局）

2)、分发服务器（邮局）

分发服务器包括分发数据库，并且存储元数据、历史数据和事务。

3)、订阅服务器（订报人，读者）

保持数据的副本，并接收对所修改出版的更改。取决于所实现的复制选项，可能还允许更新者更新数据，并将其复制回服务器或者其它订阅者。

2、复制类型

1）快照复制

快照复制是完全按照数据和数据库对象出现时的状态来复制和分发它们的过程。快照复制不需要连续地监控数据变化，因为已发布数据的变化不被增量地传播到订阅服务器，而是周期性的被一次复制。适用于数据主要是静态的，比如将数据仓库复制到数据集市中一段时间内允许有已过时的数据拷贝的情况

2）事务复制

使用事务复制，初始快照数据将被传播到订阅服务器，因此该订阅服务器就具有了一个所谓的初始负载，这是可以开始工作的内容。当出版服务器上发生数据修改时，这些单独的事务会被及时捕获并复制到订阅服务器。并保留事务边界，当所有的改变都被传播后，所有订阅服务器将具有与传播服务器相同的值需要数据修改经常在其发生的几秒钟内被传播到订阅服务器需要事务是原子性的订阅服务器在通常是连接到出版服务器上的应用程序不能忍受订阅服务器接收改变的高延迟

3）合并复制

合并复制允许一组站点自治工作，在线或离线。然后在将来的某个时刻，数据按照在多个已复制站点上发生的修改或插入情况被合并成一个统一的结果。在订阅服务器上应用初始快照，作为其初始负载，然后SQL Server跟踪在出版服务器上和订阅服务器上已发布数据的更改。数据按照预先定义或调度的时间，或者按需在服务器间同步。然后更新被独立应用在多个服务器上。这意味着相同的数据可能由出版服务器或多个订阅服务器进行了更新，因而当数据更新合并时将发生冲突。多个订阅服务器需要在不同时刻更新数据，并将这些数据传播到出版服务器和其他订阅服务器。订阅服务器需要接收数据，脱机更改数据，然后将更改同步到出版服务器和其他订阅服务器应用程序的延迟需求可高可低站点的自治性很关键

3、复制模式

1）、推模式（Push）

分发代理程序在分发服务器上运行，即为推模式

2）、拉模式（Pull）

分发代理在订阅服务器运行，即为拉模式

二、工作流程

1、快照复制

1）、发布服务器，将要发布的数据库整个做一个快照，

2）、订阅服务器的快照代理程序把发布服务器的快照读取过来，放在本地的快照文件夹内

3）、订阅服务器的发布代理程序把快照文件夹中的快照发布到订阅服务器上。历史记录和快照记录在分发服务器中。

2、事务复制

1）、初始数据和架构（通过快照复制来完成），从这里可以体现出来快照复制，是所有复制的基础。快照复制在订阅服务器上建立了订阅数据库。

2）、发布服务器的数据修改后，写事务日志，

3）、分发服务器的日志读取代理读取发生改变的数据的事务日志，把这些事务日志保存在发布服务器的发布数据库中。

4）、分发服务器的分发代理程序将分发数据库中的事务日志分发到各个订阅服务器上，然后把历史记录和错误记录在分发数据库中

三、具体操作流程

1、发布复制（推送模式）

1）、展开SQL Server 2008 服务器下的【复制】节点，会发现有【本地发布】和【本地订阅】两个节点，右击【本地发布】节点，选择【新建发布】

2）、设置分发服务器和发布服务器为同一台（推送模式）

3）、选择数据元位置

4、选择同步的数据库对象

5、发布复制类型

6、设置同步计划

7、设置代理安全性

8、发布完成取名字

2、订阅复制

1）、右键点击【本地订阅】节点，打开，【新建订阅向导】对话框

2）搜索发布服务器去订阅

3）、由于上面发布的和分发的是同一台服务器所以选择推送订阅

4）、选择订阅接受的对象（也就是获取数据的数据库，上面发布了 test 库 pce_admin_info,这里用本机的test1来接受，同步时如果没有表会自动在test1库创建表）

5）、设置连接安全性

6）后面一直默认点下一步，完成订阅

3、查看效果

同步上面步骤，把 A服务器的 test 库的 pce_admin_info 表同步到 B服务器的 test1 ,在第一次执行订阅后自动创建了表，之后会根据计划设置同步的更新表pce_admin_info

四、注意事项

1、无论是发布复制还是订阅服务一定要在sql server安装的服务器本机操作，不能是远程连接操作

2、将这个文件夹共享出来，然户用共享文件夹的UNC路径作为快照文件夹路径。这个文件夹要赋予SQL Server Service和Agent Service读写权限。

3、sql server 另外一种镜像实现数据副本，镜像是只能主服务器读写，从服务器是不支持读写的，而复制是可以从服务器读主服务器写。所以我们实现读写分离往往是通过数据库的复制来实现。

=============================================================

From: http://blog.csdn.net/sqlserverdiscovery/article/details/17059073

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

一、使用场景

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

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

2、如果数据库很大，压力很大，读写很频繁，那么为了分担单个数据库服务器的压力，可以通过数据库复制技术，来实现读写分离。
可以从一个数据库，把数据复制到多台服务器上，主数据库用于写，而其他几台服务器用于读取数据，减轻主数据库的压力。

3、总结一下：
一个是用于分布式数据的情况。
一个是数据库的高可用性，不仅能通过读写分离，提高性能，减轻压力，同时还能起到数据库备份的作用。

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

二、发布类型

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

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

事务发布:
在订阅服务器收到已发布数据的初始快照后，发布服务器将事务流式传输到订阅服务器。
也就是，设置好后，第一次会进行初始化，然后以后每次同步的时候，只是把修改的数据，比如insert、update、delete，同步到订阅端，一般适合对同步时间要求比较严格的情况，间隔时间可能就10秒，或者几分钟。

总结一下：

快照复制，每次都是复制所有的数据，所以如果数据量大，那么往往复制的时间会很长。
而事务复制，每次只复制变化的部分，所以速度较快。

三、实验环境

一台是笔记本，机器名：ggg-pc，实例名：MSSQLSERVER

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

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

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

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

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

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

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

[sql] view plain copy print ?

--在笔记本上
create database wc
go
use wc
go
create table A
(
id int primary key,
v varchar(100)
)

--在笔记本上
create database wc
go

use wc
go

create table A
(
id int primary key,
v varchar(100)
)

[sql] view plain copy print ?

--在虚拟机上
create database wc
go
use wc
go
create table B
(
id int primary key,
v varchar(100),
vv varchar(100) --注意：这里比A表多了一列
)
go

--在虚拟机上
create database wc
go

use wc
go

create table B
(
id int primary key,
v varchar(100),
vv varchar(100)   --注意：这里比A表多了一列
)
go

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

首先是设置发布：

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

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

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

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

5、选择：快照文件夹，默认是

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

6、选择：要发布的数据库，这里选wc

7、选择：发布类型，这里选择“事务发布”

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

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

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

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

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

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

11、代理安全性：

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

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

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

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

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

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

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

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

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

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

6、设置分发代理安全性

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

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

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

9、创建订阅成功

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

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

[sql] view plain copy print ?

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

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

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

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

=============================================================

Error messages:
The process could not access database 'test' on server 'NEWTESTAG4'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20052)
Get help: http://help/MSSQL_REPL20052
Cannot open database "test" requested by the login. The login failed. (Source: MSSQLServer, Error number: 4060)
Get help: http://help/4060
Login failed for user 'EXSP\_TPSQLAccount'. (Source: MSSQLServer, Error number: 18456)
Get help: http://help/18456

Command attempted:
exec sp_MSreplcheck_subscribe

Error messages:
Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation. (Source: MSSQLServer, Error number: 21050)
Get help: http://help/21050
Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation. (Source: MSSQLServer, Error number: 21050)
Get help: http://help/21050

Root Cause:
The sql account permission is not enough, so it can't access the database on the subscriber.

Workround:
Add the sysadmin to sql account
subscriber--Security--logins--sql account (the user is used for sql server running)

参考：
http://www.tuicool.com/articles/z2Y36f
http://blog.csdn.net/sqlserverdiscovery/article/details/17059073
http://www.sqlservercentral.com/Forums/Topic1467931-1549-1.aspx
http://blog.sina.com.cn/s/blog_81b2b2a101010b6i.html
http://www.2cto.com/database/201409/331752.html
http://www.tuicool.com/articles/z2Y36f
http://www.cnblogs.com/chenmh/p/3671030.html
http://www.cnblogs.com/chenmh/p/4487766.html#概述
http://www.cnblogs.com/yuqilin/archive/2011/04/28/2031274.html
http://wenku.baidu.com/link?url=IhBU-BIaCigrdHV2u0rFCcXngdqkPZCfmoCu4txc7rOv8ToCC6JUJDYaHI2j9_OZo0y8zHbgMJPojvVT79VKBkA4Zfa-A-3si7J5oQm8mNS
http://www.cnblogs.com/dudu/archive/2010/08/26/1808540.html
http://www.cnblogs.com/tyb1222/archive/2011/05/31/2064944.html
http://www.cnblogs.com/tyb1222/archive/2011/05/27/2060075.html
SQL Server 2008 数据库复制实现数据同步常见问题：http://kb.cnblogs.com/page/71979/
英文：http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/
SqlServer 使用脚本创建分发服务及事务复制的可更新订阅 -- 脚本配置：http://blog.csdn.net/kk185800961/article/details/44719685

SQL Server 2012 复制（发布订阅的研究）：http://www.cnblogs.com/suger/p/4274610.html

转载于:https://blog.51cto.com/57388/1852208