非域环境基于发布/订阅模式的SQL Server 数据库同步

■ 唐山市教育局 孟令旸

编者按：笔者在查看单位在线教学平台的数据库时发现其数据库系统采用的是SQL Server 2012。鉴于SQL Server 2012 自带发布/订阅功能，通过该功能就可以实现数据库同步，因此，笔者决定采用发布/订阅模式实现数据库同步。本文将详细介绍操作过程。

为了安全起见，笔者没有直接为在线教学平台的数据库做同步，而是搭建了一个简单的实验环境：创建两台虚拟机，将两台虚拟机组成一个独立的局域网。两台虚拟机上均部署SQL Server 2012 数据库系统。笔者将一台虚拟机用作发布服务器，另一台虚拟机用作订阅服务器。在发布服务器上创建了数据库“student”，该数据库只有一张表“cjb”。这是笔者虚拟出来的学生成绩表，用来完成数据实验。两台虚拟机主要实验数据如表1 所示。

可能是非域环境，笔者进行的数据库同步实验并不顺利，遇到了很多问题。由于SQL Server 给出的错误提示并不具体，很难根据错误提示找到故障原因。例如，创建同步和订阅后，在复制监视器中总提示“未初始化的订阅”，无论做多少次重新初始化操作，仍出现这个提示。又如，出现“对路径C:Program FilesMicrosoft SQL ServerMSSQL 11.MSSQLSERVERMSSQLRepl Dataunc……的访问被拒绝”错误，出现错误的这个文件路径是快照文件的存放路径，于是笔者为快照文件重新指定路径，仍报这个错误。类似这样的问题还有很多：“无法读取文件xxxx.pre”“工作目录错误”……。

当然，在创建发布或创建订阅过程中，也不是所有问题都这样，有一些问题是可以根据错误提示判断出故障原因的。例如，当遇到“SQL Server 无法将SQL Server 代理配置为自动启动”错误时，这个错误提示说明没有启动SQL Server代理服务。此时可以通过运行“services.msc”，出现“服务”窗口后，手动将SQL Server 代理服务配置为自动并启动该服务即可。

表1 测试用虚拟机主要实验数据一览表

互联网上有很多关于SQL Server 发布订阅方面的教程，笔者参照这些教程进行了尝试，也以失败告终。于是笔者在参考这些教程的基础上，对SQL Server 发布订阅机制进行分析，通过不断实验，终于完成了数据库同步实验。

必要的准备工作

在域控制器下，出现SID重复、计算机名重复、端口等问题，在加入域的过程中就会提示这些问题的。在非域控制器下，要保证数据库成功同步，建议先做好以下准备工作。

1.确保发布服务器和订阅服务器数据库之间能相互访问

在确保发布服务器和订阅服务器之间能正常通信的基础上，要注意以下事项：第一，避免SID 重复；第二，避免计算机名重复；第三，开放必要端口。

2.确保订阅服务器有权限读写发布服务器上的快照文件夹

（1）在发布服务器上，创建一个新文件夹。以后在创建发布时，指定该文件夹为快照文件夹。在本例中，创建的快照文件夹是“d:student epldata”。

（2）在发布服务器和订阅服务器上，创建一个完全相同的Windows 系统用户，并设置好密码。将该用户加入到“administrators”用户组，即将该用户设置为管理员权限。在本例中，添加的用户是“synch”。

（3）将发布服务器上的快照文件夹“d:student epldata”设置为共享。设置共享时，将用户“synch”添加到共享用户列表中，通过“权限级别”下拉菜单将该用户的访问权限设置为“读写”权限。

（4）验证共享文件设置是否成功。可以在订阅服务器上，用“synch”用户登录系统，打开资源管理器后，用共享路径访问位于发布服务器上的快照文件夹，在该文件夹下创建文件夹和文件，测试该用户是否有读写权限。在本例中，共享文件夹的路径是“\143PSPFER23 epldata”。

3.将SQL Server 代理服务的登录身份更改为指定用户账户

（1）运行命令“services.msc”，打开服务窗口。

（2）打开服务窗口后，找到“SQL Server 代理”服务。

（3）双击“SQL Server代理”服务，打开服务属性对话框窗口后，单击“登录”选项卡，勾选“此账户”，然后单击右侧的“浏览”按钮，将SQL Server 代理的登录身份更改为前面所创建的用户账户“synch”，并输入与该用户账户对应的密码。

（4）点击“常规”选项卡，启动“SQL Server 代理”服务，将服务启动方式配置为“自动”。

4.发布和订阅服务器之间相互进行注册

（1）首先要保证在安装SQL Server 之后没有修改服务器的计算机名。如果安装SQL Server 之后修改过服务器的计算机名，在注册SQL Server 服务器时容易出现“SQL Server replication requires the actual server name to make a connection to the server”之类的错误。如果遇到此类错误，最简单有效的方法就是卸载并重新安装SQL Server。

（2）新建并配置SQL Server 登录名

①用SQL Server Manage ment Studio 连接到数据库服务器后，展开“（计算机名）”→“安全性”→“登录名”，右击“登录名”，在右键菜单中选择“新建登录名”。

②出现“登录名-新建”对话框窗口后，单击“搜索”按钮，将前面所创建的本地用户“synch”配置为SQL Server 登录名。

③单击左侧“服务器角色”，在右侧“服务器角色”列表框中勾选“public”和“sysadmin”。给新建的登录名synch 分配sysadmin 权限（SQL Server 超级管理员权限）。

（3）在发布服务器上备份需要发布的数据库（备份过程略）。

在本例中，需要发布的数据库是“student”是用来做同步实验的数据库，数据库里面的学号、姓名、成绩、班级等所有数据均有计算机随机产生，如有雷同，纯属偶然。

（4）将备份文件复制到订阅服务器上，并在订阅服务器上还原备份的数据库（数据库还原过程略）。

（5）将新建的本地用户账户配置为发布（订阅）数据库的拥有者。

①用SQL Server Manage ment Studio 连接到数据库服务器后，展开“（计算机名）”→“数据库”→“student”→“安全性”→“用户”，右击“用户”，在右键菜单中选择“新建用户”。

②出现“数据库用户-新建”对话框后，在“用户类型”下拉列表中选择“带登录名的SQL 用户”。在“用户名”文本框中为新建用户取名，单击“登录名”文本框右侧的按钮进一步指定登录名。

③出现“选择登录名”对话框窗口后，单击“浏览”按钮，选择前面新创建的登录名“synch”。

④为发布数据库选择登录名“synch”后，右击该用户，在右键菜单中选择“属性”。

⑤出现用户属性窗口后，单击窗口左侧的“成员身份”，在“角色成员”列表中勾选“db_owner”，即可将本地用户账户“synch”配置为发布数据库的拥有者。

同样，在订阅服务器上将本地用户账户“synch”也配置为数据库“student”的拥有者。

（6）将SQL Server 服务器配置为允许远程连接。

用SQL Server Manageme nt Studio 连接到数据库服务器后，右击服务器名称，在右键菜单中选择“属性”。出现服务器属性对话框后，单击左侧的“连接”，在右侧“远程服务器连接”栏目中勾选“允许远程连接到此服务器”。

（7）开启SQL Server 的网络协议TCP/IP 和管道命名协议

通过Windows 的开始菜单，打开SQL Server 配置管理器。然后依次单击“SQL Server 配置管理器”→“SQL Server 网络配置”→“MSSQL SERVER 的协议”，将右侧的“Named Pipes”和“TCP/IP”两个协议的协议状态配置为“已启用”。

（8）发布和订阅服务器之间相互注册

①重新启动服务器操作系统，用“synch”用户账户登录操作系统。

②用SQL Server Manage ment Studio 连接到数据库服务器后，依次单击“视图”菜单→“已注册的服务器”。

③出现“已注册的服务器”选项卡后，右击“本地服务器组”，在右键菜单中选择“新建服务器注册”。

④出现“新建服务器注册”对话框后，在“服务器名称”文本框中输入订阅服务器的服务器名称，身份验证选择“Windows 身份验证”。当前登录系统的用户是“synch”，此时Windows 身份验证的用户账户是“synch”。此时单击“测试”按钮可以测试连接是否成功。单击“保存”按钮完成订阅服务器的注册。

按照同样方法，在订阅服务器上注册发布服务器。

发布数据库

采用发布/订阅方式同步SQL 数据库，数据库需要通过分发服务器对外发布，因此，在发布数据库之前，需要配置分发服务器，然后再创建发布。第一次创建发布时，可以按发布向导指引配置分发服务器。分发服务器是包含分发数据库并为所有类型的复制存储元数据和历史记录数据的服务器，除此之外，它还为事务复制存储事务。分发服务器与发布服务器可以是同一台服务器，也可以是不同的服务器。在本例中，是在创建第一个发布的时候，按照向导指引配置的分发服务器，也就是当前服务器既是发布服务器，也是分发服务器。

1.打开“SQL Server Ma nagement Studio”后，依次展开“对象资源管理器”树型目录“SQL Server 实例名”→“复制”→“本地发布”。右击“本地发布”，在右键菜单选择“新建发布”。

2.出现“新建发布向导”对话框窗口后，单击“下一步”，按照向导指引新建发布；出现“分发服务器”页面后，选择“（服务器名称）将充当自己的分发服务器，SQL Server 将创建分发数据库和日志”；出现“快照文件夹”页面后，指定将要存储快照的根位置。在本例中，指定的快照位置是前面所创建的共享文件夹“\服务器名 epldata”；出现“发布数据库”页面后，选择要同步的数据库；出现“发布类型”页面后，根据实际需求选择发布类型。

SQL Server 支持的发布类型包括“快照发布”“事务发布”“对等发布”和“合并发布”四种发布类型。如果选择快照发布，在同步数据库时，它是按照数据和数据库对象出现时的状态来复制和分发的。

需要注意的是，因为已发布数据的变化不被增量地传播到订阅服务器，而是周期性的被一次复制。采用此数据同步方式，即便在发布服务器上更新了数据，订阅服务器上的数据不会被立即更新，而是要根据同步周期，等下次数据更新。

如果想要实现实时同步，即发布服务器上的数据更新后，订阅服务器上的数据立即更新，此时可以选择事务发布。不过，如果选择事务发布，所发布数据库的每张表都必须有主键。采用快照发布方式同步数据时，对表结构没有任何要求。

采用快照发布或事务发布同步数据时，所同步的数据都是单向的，都是从发布服务器到订阅服务器。如果需要双向同步数据，可以选择合并发布。但创建合并发布时，所发布数据库的表要求有rowguid 列，也就是每张表必须有全局唯一标识符。

3.出现“项目”页面后，根据实际需要选择项目发布的表和其它对象；出现“筛选表行”页面后，根据需要将不需要同步的对象筛选出去；出现“快照代理”页面，勾选“立即创建快照并使快照保持可用状态，以初始化订阅”。

4.出现“代理安全性”页面后，单击“安全设置”按钮。出现“快照代理安全性”页面后，勾选“在以下Windows账户下运行”，然后在“进程账户”文本框中输入前面所创建SQL Server 登录名，在“密码”和“确认密码”框输入与该登录名对应的密码。在“连接到发布服务器”栏选择“通过模拟进程账户”。

5.出现“向导操作”页面后，勾选“在向导结束时”→“创建发布”。

6.出现“完成该向导”页面后，在“发布名称”页面为新建的发布取一个名字。检查在向导中所做出的选择，如果没有问题，单击“完成”按钮，创建发布。

7.出现“正在创建发布”页面后，可以看到创建操作状态及其结果。

订阅数据库

在发布服务器（源数据库服务器）发布数据库后，目标数据库可以通过订阅已发布的数据库实现数据库同步，下面是在订阅服务器上订阅数据库的具体过程：

1.打开“SQL Server Ma nagement Studio”后，依次展开“对象资源管理器”树型目录“SQL Server 实例名”→“复制”→“本地订阅”。右击“本地订阅”，在右键菜单选择“新建订阅”。

2.出现“新建订阅向导”对话框窗口后，单击“下一步”，按照向导指引新建订阅。

3.出现“发布”页面后，通过“发布服务器”下拉菜单中的“查找SQL Server 发布服务器…”，查找并连接到发布服务器。

4.连接到发布服务器后，可以查看到已发布的数据库及其它对象（如存储过程），选择需要订阅的发布对象。

5.出现“分发代理位置”页面后，选择“在其订阅服务器上运行每个代理（请求订阅）”。选择请求订阅可以降低分发服务器数据处理工作的开销。请求订阅还允许订阅服务器上的用户确定同步数据更改的时间。

6.出现“订阅服务器”页面后，选择需要订阅的数据库。

7.出现“分发代理安全性”页面后，单击订阅服务器右侧的“……”按钮。

8.出现“分发代理安全性”页面后，勾选“在以下Windows 账户下运行”，然后在“进程账户”文本框中输入前面所创建SQL Server登录名，在“密码”和“确认密码”框输入与该登录名对应的密码。

在“连接到发布服务器”栏选择“通过模拟进程账户”。在“连接到订阅服务器”栏选择“通过模拟进程账户”。

9.出现“同步计划”页面后，在“代理计划”下拉列表中选择“连续运行”。

10.出现“初始化订阅”页面，在“初始化时间”下拉列表中选择“立即”。用发布订阅方式同步数据，必须使用发布数据和架构的快照对订阅数据库进行初始化。如果此步操作没有勾选“立即”，将无法实现数据库同步。如果创建订阅时没有选择立即初始化，可以等订阅创建完成后，右击此订阅，手动初始化。

11.出现“向导操作”页面后，勾选“在向导结束时”→“创建订阅”。

12.出现“完成该向导”页面后，在检查在向导中所做出的选择，如果没有问题，单击“完成”按钮，创建订阅。

13.出现“正在创建订阅”页面后，可以看到创建操作状态及其结果。

同步实验验证

1.在订阅服务器查看数据库同步状态

①打开“SQL Server Ma nagement Studio”后，依次展开“对象资源管理器”树型目录“SQL Server 实例名”→“复制”→“本地订阅”→订阅对象。右击订阅对象，在右键菜单选择“查看同步状态”。

②出现“查看同步状态”对话框窗口后，可以查看到订阅和发布的数据库，开始时间以及同步的状态，此时可以单击“停止”/“启动”按钮停止/启动同步，可以单击“监视”按钮进一步监视数据库同步。

③打开“复制监视器”窗口后，依次展开“复制监视器”→“我的发布服务器”→发布服务器名→发布对象，在右侧“所有订阅”选项卡下可以看到当前订阅对象的状态。如果成功同步数据库，此处订阅的状态应该是“正在运行”。

2.在发布服务器上查看数据库同步状态

①打开“SQL Server Ma nagement Studio”后，依次展开“对象资源管理器”树型目录“SQL Server 实例名”→“复制”→“本地发布”→发布对象→订阅对象。右击订阅对象，在右键菜单选择“启动复制监视器”。

②打开“复制监视器”窗口后，依次展开“复制监视器”→“我的发布服务器”→发布服务器名→发布对象，在右侧“所有订阅”选项卡下可以看到当前订阅对象的状态。如果成功同步数据库，此处订阅的状态应该是“正在运行”。

③如果要重新初始化订阅，可以右击需要初始化的订阅对象，在右键菜单中选择“重新初始化订阅”。

3.修改数据验证数据库同步效果

无论是在发布服务器上，还是在订阅服务器上，所查看的同步状态实际上是同步订阅的工作状态。如果在创建发布时，选择的是事务发布，就可以通过修改发布数据库的数据来验证数据库同步效果。

在发布服务器上打开所发布的数据库，编辑其中一个表的数据。

手动更新源数据库数据后，打开订阅服务器上订阅的数据库，打开同一张表，查看该数据是否已更新。

例如，在本例中，该学生的姓名已更新为源数据库的姓名，说明数据库实时同步实验取得成功。