通过SQL Server 2008数据库复制实现数据库同步备份

谁借莪１个温暖的怀抱￠ 2022-08-19 02:21 141阅读 0赞

SQL Server 2008数据库复制是通过发布/订阅的机制进行多台服务器之间的数据同步，我们把它用于数据库的同步备份。这里的同步备份指的是备份服务器与主服务器进行实时数据同步，正常情况下只使用主数据库服务器，备份服务器只在主服务器出现故障时投入使用。它是一种优于文件备份的数据库备份解决方案。

在选择数据库同步备份解决方案时，我们评估了两种方式：SQL Server 2008的数据库镜像和SQL Server 2008数据库复制。数据库镜像的优点是系统能自动发现主服务器故障，并且自动切换至镜像服务器。但缺点是配置复杂，镜像数据库中的数据不可见（在SQL Server Management Studio中，只能看到镜像数据库处于镜像状态，无法进行任何数据库操作，最简单的查询也不行。想眼见为实，看看镜像数据库中的数据是否正确都不行。只有将镜像数据库切换主数据库才可见）。如果你要使用数据库镜像，强烈推荐killkill写的[SQL Server 2005 镜像构建手册][SQL Server 2005]，我们就是按照这篇文章完成了数据库镜像部署测试。

最终，我们选择了SQL Server 2008数据库复制。

下面通过一个示例和大家一起学习一下如何部署SQL Server 2008数据库复制。

测试环境：Windows Server 2008 R2 + SQL Server 2008 R2（英文版），两台服务器，一台主数据库服务器CNBlogsDB1，一台备份数据库服务器CNBlogsDB2。

复制原理：我们采用的是基于快照的事务复制。主数据库服务器生成快照，备份库服务器读取并加载该快照，然后不停地从主数据库服务器复制事务日志。见下图：

[![grid.ai][]][grid.ai 1]

图片来自[SQL Server联机丛书][SQL Server]

安装与配置步骤：

一、在两台服务器上安装好SQL Server 2008 R2，主要安装的组件：Database Engine(含SQL Server Replication)，Management Tools。

二、主数据库服务器（发布服务器）的配置：

1. 在主数据库服务器CNBlogsDB1新建示例数据库CNBlogsDemo（注意Recovery mode要使用默认值Full，只有这个模式才能进行事务复制），然后建立一张测试表，比如：CNBlogsTest。

[![cnblogs\_test][cnblogs_test]][cnblogs_test_cnblogs_test]

2. 设置存放快照的文件夹：

创建发布之前，先设置一下存放快照的文件夹，创建发布后会在该文件夹生成快照文件，订阅服务器需要在初始化时加载该快照文件。

选择Replication》Local Publications》属性，在出现的窗口中选择Publishers，如下图：

![20100826-8][]

点击红框处的按钮，出现设置窗口：

[![20100826-9][]][20100826-9 1]

在Default Snapshot Folder中设置快照文件存放路径。

3. 在主数据库服务器创建发布：

在Replication》Local Publications中选择New Publication，出现一个向导。先选择要发布的数据库CNBlogsDemo，然后选择发布类型Transational publication，如下图：

![Transationalpublication][]

点击Next，出现错误：

![20100826-1][]

原来所有要复制的表都需要有主键，刚才建CNBlogsTest表时，没有建主键。建一下主键，并重新启动向导就可以了。

接着选择要复制的对象：

![20100826-2][]

点Next，Next，进入Snapshot Agent窗口，选择Create a snapshot immediately and keep the snapshot available to initialize subscriptions，见下图：

[![20100826-3][]][20100826-3 1]

Next，进入Agent Security：

![20100826-4][]

选择Security Settings，进行相应的帐户设置：

![20100826-5][]

一个是设置运行Snapshot Agent的Windows帐户，我们这里选择与SQL Server Agent同样的帐户。

一个是设置连接发布服务器的SQL帐户，我们这里就用主数据库服务器的sa帐户。

继续：OK，Next，Next，为这个发布起个名字：

[![20100826-7][]][20100826-7 1]

点击Finish，就开始正式创建发布，创建成功就会出现如下窗口：

![20100826-10][]

这时查看快照文件夹，就会看到unc文件夹，快照文件就在这个文件夹中。

这里要考虑这样一个问题，如何让订阅服务器通过网络访问这个快照文件夹。

我们在这个问题上折腾了一些时间，本来想通过共享文件夹的方式，但又不想打开匿名共享，折腾了半天，没搞定订阅服务器访问共享文件夹用户验证的问题。于是采用了FTP的方式，所以，下面介绍一下如何让订阅服务器通过FTP访问快照文件。

4. 设置快照的FTP访问

首先在主数据库服务器上开通FTP服务，建立一个指向快照文件夹的FTP站点，设置好可以远程连接的FTP帐户。然后在这台发布服务器设置一下FTP客户端配置。配置方法如下：

在Replication》Local Publications中选择刚才创建的发布\[CNBlogsDemo\]:CNBlogsDemo\_Publication，选择属性》FTP Snapshot，如下图：

![20100826-11][]

选中Allow Subscribers to download snapshot files using FTP，并设置一下FTP客户端连接参数，订阅服务器就是通过这里的设置连接FTP服务器的（注：Path from the FTP root folder的设置要和上图一样，设置为：/ftp）。

点击OK，这时会在快照文件夹中创建一个ftp文件夹，并在该文件夹中生成快照文件。

这样，发布服务器就配置好了，下面配置订阅服务器。

三、备份数据库服务器（订阅服务器）的配置：

进入订阅服务器CNBlogsDB2，创建与发布服务器同名的数据库CNBlogsDemo，使用完全恢复模式。

在Replication》Local Subscriptions中选择New Subscriptions，进入向导。

Next，进入选择发布服务器的窗口，选择Find SQL Server Publisher，出现服务器连接窗口：

![20100826-12][]

这里要注意的是Server Name中一定要填写发布服务器的计算机名，如果计算机名连接不上，要在hosts文件中加一个IP地址解析。

成功连接发布服务器之后，就可以看到刚才在主数据库服务器上创建的发布：

[![20100826-13][]][20100826-13 1]

Next，进入“分发代理工作位置”的选择窗口：

![20100826-14][]

我们这里选择pull subscriptions，把数据给拉过来，这样主数据库服务器的负担会轻些。

Next，选择订阅服务器上的数据库，之前我们已经建好同名的数据库，所以系统自己会找到。

Next，进入分发代理安全设置窗口：

![20100826-15][]

点击红框内的按钮，进入设置窗口：

![20100826-16][]

设置如上图，Connect to the Distributor处设置的是发布服务器的sa帐户。

OK, Next, Next, Next:

![20100826-17][]

Next, Finish, Success:

![20100826-18][]

备份数据库的订阅就建好了！

现在来瞧一瞧订阅服务器CNBlogsDB2上的用于复制的数据库CNBlogsDemo：

![20100826-19][]

看！我们在发布服务器上建立的表CNBlogsTest复制过来了。

现在我们去发布服务器CNBlogsDB1上添加一条记录：

![20100826-20][]

再去订阅服务器CNBlogsDB2瞧一瞧：

![20100826-21][]

数据立即同步过来了！搞定！

![20791975316932][]

遇到的问题：

在测试过程中被两个问题折腾了很长时间。

1）发布服务器的Log Reader Agent不能启动，错误信息：

· The process could not execute 'sp\_replcmds' on 'YCSERVER006'. (Source: MSSQL\_REPL, Error number: MSSQL\_REPL20011)  
Get help: http://help/MSSQL\_REPL20011  
· Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)  
Get help: http://help/15517  
· The process could not execute 'sp\_replcmds' on 'YCSERVER006'. (Source: MSSQL\_REPL, Error number: MSSQL\_REPL22037)  
Get help: http://help/MSSQL\_REPL22037

开始测试时，附加了一个现有数据库进行复制遇到了这个问题，附加的是一下SQL Server 2005数据库文件，Owner为空，改为sa问题就解决了，如下图：

![20100826-22_3.jpg][]

2）第二个问题就是前面已经描述过的订阅服务器访问发布服务器上的快照文件夹的问题，后来通过FTP的方式解决的。

对于SQL Server 2008数据库复制，目前我就学习了这些，期待园子里有这方面经验的朋友也来分享一下，在分享过程中你也会学到很多。

[SQL Server 2005]: http://www.cnblogs.com/killkill/archive/2008/05/23/1205792.html
[grid.ai]: /images/20220731/96b924ba21fc449db9869f07260bade9.png
[grid.ai 1]: http://images.cnblogs.com/cnblogs_com/dudu/WindowsLiveWriter/SQLServer2008_13F7F/IC75251_2.gif
[SQL Server]: http://msdn.microsoft.com/zh-cn/library/ms151706.aspx
[cnblogs_test]: http://images.cnblogs.com/cnblogs_com/dudu/WindowsLiveWriter/SQLServer2008_13F7F/cnblogs_test_thumb.jpg
[cnblogs_test_cnblogs_test]: /images/20220731/527267b81a32425ab6ffd928c5fea2d6.png
[20100826-8]: /images/20220731/68519de2d4f04c5c821aeb701589f9f1.png
[20100826-9]: /images/20220731/ed0eee0d178c4da3abd05f98573e65bb.png
[20100826-9 1]: http://images.cnblogs.com/cnblogs_com/dudu/WindowsLiveWriter/SQLServer2008_13F7F/20100826-9_2.jpg
[Transationalpublication]: /images/20220731/f2b8c58c524b43e6a336a645a5d9806d.png
[20100826-1]: /images/20220731/3e28604d6bc9406b9c7107f2be63141a.png
[20100826-2]: /images/20220731/e1f28ead38f74d6fb30e12a3fd53a5e9.png
[20100826-3]: /images/20220731/1ffcb14b987340c0816943824c332ef2.png
[20100826-3 1]: http://images.cnblogs.com/cnblogs_com/dudu/WindowsLiveWriter/SQLServer2008_13F7F/20100826-3_2.jpg
[20100826-4]: /images/20220731/de5d3758df3444ab81d4dd0f1f7237cd.png
[20100826-5]: /images/20220731/277db608997c487ea139a0f4d6fedc54.png
[20100826-7]: /images/20220731/16042361c506493291b2bafc3bdabde0.png
[20100826-7 1]: http://images.cnblogs.com/cnblogs_com/dudu/WindowsLiveWriter/SQLServer2008_13F7F/20100826-7_2.jpg
[20100826-10]: /images/20220731/f29bd2674b534e34a039241a483b540d.png
[20100826-11]: /images/20220731/d46ece87d6874749bd0fa5af152df9a6.png
[20100826-12]: /images/20220731/a82eee2cb07344b29363781620f2046d.png
[20100826-13]: /images/20220731/67c035bcdff846a2a3017c4ae000a589.png
[20100826-13 1]: http://images.cnblogs.com/cnblogs_com/dudu/WindowsLiveWriter/SQLServer2008_13F7F/20100826-13_2.jpg
[20100826-14]: /images/20220731/e68f773071fb430e8064a18b2410f353.png
[20100826-15]: /images/20220731/491c3ffa332d4ef19a0ee7e6f2fe377a.png
[20100826-16]: /images/20220731/d2f3acee374d45a0b485c30b678d66e2.png
[20100826-17]: /images/20220731/278c4f2d409b47389df560e3235b6e80.png
[20100826-18]: /images/20220731/2d8f41bf6cb04a72a1c7ec2e416fe2d6.png
[20100826-19]: /images/20220731/eb14be3ed2124fde856792f255b20a5e.png
[20100826-20]: /images/20220731/949d4c40a2c94fb6bc843e8380ec7a06.png
[20100826-21]: /images/20220731/3c09dec0386b451f99a65c5024eb6519.png
[20791975316932]: /images/20220731/2a1b821e0640466db8429fddff9f5bd0.png
[20100826-22_3.jpg]: /images/20220731/a1d7827ce91445cb8a3cf0476fccbef7.png