[SQL Server] 使用事务发布与订阅实现数据同步

Dear 丶 2022-07-12 15:21 190阅读 0赞

事务的功能在sqlserver中由来已久，因为最近在做一个数据同步方案，所以有机会再次研究一下它以及快照等，发现还是有很多不错的功能和改进的。这里以sqlserver2008的事务发布功能为例，对发布订阅的方式简要介绍一下操作流程，一方面做个总结备份，一方面与大家进行一下分享和交流。费话就不多说了，进入一下今天的正题：）

这里要说明一下环境：首先我在本地局域网内有两台安装有sqlserver2008的机器（注意：已发布的快照版本无法向老版本数据库兼容，意味着2008下创建的事务或快照发布，无法被sqlserver2005订阅）。当然这两台机器要放在同一个网段，一方面出现性能和安全性考虑，另一方面是我目前还没有找到跨不同网段的快照应用方案（可行性的那种）。

好了，这里我以我们产品数据库为准来介绍一下如何通过发布订阅的方式来进行数据同步。

首先，在要发布的数据库上创建一个我们的产品数据库（这里叫做dnt\_new）,然后在该数据库实例的左侧导航的“复制”--“本地发布”上击右键，然后选择“新建发布”，如下：

[![sqlserver\_snap\_1][sqlserver_snap_1]][sqlserver_snap_1_sqlserver_snap_1]

这样，系统就会启动‘发布向导’来引导大家，这里我们在欢迎界面下点击"下一步”，然后在当前窗口中选择要发布的数据库，如下：

[![sqlserver\_snap\_2][sqlserver_snap_2]][sqlserver_snap_2_sqlserver_snap_2]

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

[![sqlserver\_snap\_3][sqlserver_snap_3]][sqlserver_snap_3_sqlserver_snap_3]

然后点击下一步，选择要同步的数据对象（数据表，存储过程，自定义函数等），如下：

[![sqlserver\_snap\_4][sqlserver_snap_4]][sqlserver_snap_4_sqlserver_snap_4]

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

[![sqlserver\_snap\_5][sqlserver_snap_5]][sqlserver_snap_5_sqlserver_snap_5]

这里可以通过“添加”方式来过滤要同步的数据信息，因为要做全表数据同步，所以这里就不做设置了，感兴趣的朋友可以自己研究一下，这里接着点击下一步，如下：

[![sqlserver\_snap\_6][sqlserver_snap_6]][sqlserver_snap_6_sqlserver_snap_6]

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

[![sqlserver\_snap\_7][sqlserver_snap_7]][sqlserver_snap_7_sqlserver_snap_7]

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

[![sqlserver\_snap\_8][sqlserver_snap_8]][sqlserver_snap_8_sqlserver_snap_8]

然后点击下一步按钮：

[![sqlserver\_snap\_9][sqlserver_snap_9]][sqlserver_snap_9_sqlserver_snap_9]

选择“创建发布”复选框，然后点击下一步，这时向导会让您输入“发布名称”，这里命名为“dnt\_new\_snap”：

[![sqlserver\_snap\_10][sqlserver_snap_10]][sqlserver_snap_10_sqlserver_snap_10]

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

[![sqlserver\_snap\_11][sqlserver_snap_11]][sqlserver_snap_11_sqlserver_snap_11]

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

[![sqlserver\_snap\_12][sqlserver_snap_12]][sqlserver_snap_12_sqlserver_snap_12]

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

[![sqlserver\_snap\_13][sqlserver_snap_13]][sqlserver_snap_13_sqlserver_snap_13]

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

[![sqlserver\_snap\_14][sqlserver_snap_14]][sqlserver_snap_14_sqlserver_snap_14]

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

[![sqlserver\_snap\_15][sqlserver_snap_15]][sqlserver_snap_15_sqlserver_snap_15]

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

[![sqlserver\_snap\_16][sqlserver_snap_16]][sqlserver_snap_16_sqlserver_snap_16]

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

[![sqlserver\_snap\_18][sqlserver_snap_18]][sqlserver_snap_18_sqlserver_snap_18]

然后点击“确定”按钮，之后在向导上点击“下一步”按钮，这时系统就会显示“代理计划执行方式”窗口，选择“连续运行”：

[![sqlserver\_snap\_19][sqlserver_snap_19]][sqlserver_snap_19_sqlserver_snap_19]

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

[![sqlserver\_snap\_20][sqlserver_snap_20]][sqlserver_snap_20_sqlserver_snap_20]

完成了这一步，点击下一步按钮，然后就可以创建该订阅对象了，如果一切运行正常，sqlserver就会从‘发布服务器’那边，将之前指定的数据表和存储过程等同步到当前的‘订阅数据库’中了。

这时我们可以在源数据库（发布服务器）上的表中添加或修改指定表数据信息，在等待1-3秒之后，所做的添加和修改就会同步到‘订阅数据库’上的相应表中。看来目的是达到了，呵呵。

\-- “本地发布”创建失败解决方法。(比如MyDB数据库从A服务器上，迁移到B服务器上)

1. 需要删除B服务器上的distribute数据库（该库为同步复制时的数据缓存库，为系统库）

2. 需要在B服务器上，执行以下命令，删除一些系统配置。

Sp\_removedbreplication 'MyDB'

3. 如果在你的“本地发布”服务器上, ping不通“订阅服务器”的主机名，则必须要在“本地发布服务器”上的c:/windows/system32/drivers/etc/hosts中，配置上“订阅服务器”的IP和主机名。

4. 重新按上文步骤，创建“本地发布”。

[sqlserver_snap_1]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_1_thumb.gif
[sqlserver_snap_1_sqlserver_snap_1]: /images/20220709/dade70c2f1e44527b5f6641d2f805e05.png
[sqlserver_snap_2]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_2_thumb.gif
[sqlserver_snap_2_sqlserver_snap_2]: /images/20220709/3d2c4c8c817841b1bf6e7576466a2f8b.png
[sqlserver_snap_3]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_3_thumb.gif
[sqlserver_snap_3_sqlserver_snap_3]: /images/20220709/a3d7226cd411485c86a1b5f4ef3a5c89.png
[sqlserver_snap_4]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_4_thumb.gif
[sqlserver_snap_4_sqlserver_snap_4]: /images/20220709/5ea88a322cf8434bb93f87586e001494.png
[sqlserver_snap_5]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_5_thumb.gif
[sqlserver_snap_5_sqlserver_snap_5]: /images/20220709/cd9ed626d62b4f34a68a8871c9924e13.png
[sqlserver_snap_6]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_6_thumb.gif
[sqlserver_snap_6_sqlserver_snap_6]: /images/20220709/abb474e0ebfc4aa3a646674485ca00c7.png
[sqlserver_snap_7]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_7_thumb.gif
[sqlserver_snap_7_sqlserver_snap_7]: /images/20220709/b8734b265c7148e2ac7d01bd6ffaab78.png
[sqlserver_snap_8]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_8_thumb.gif
[sqlserver_snap_8_sqlserver_snap_8]: /images/20220709/65358e37fd2942cb9a7353510f46ba6b.png
[sqlserver_snap_9]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_9_thumb.gif
[sqlserver_snap_9_sqlserver_snap_9]: /images/20220709/778f1d5c5fd141d097157c57b6527fb2.png
[sqlserver_snap_10]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_10_thumb.gif
[sqlserver_snap_10_sqlserver_snap_10]: /images/20220709/b98a2878b06e4724a1271eefd68338e4.png
[sqlserver_snap_11]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_11_thumb.gif
[sqlserver_snap_11_sqlserver_snap_11]: /images/20220709/0125cbafa1f34b4f8477a33b5f744c7d.png
[sqlserver_snap_12]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_12_thumb.gif
[sqlserver_snap_12_sqlserver_snap_12]: /images/20220709/868580d17a6348e4a1a7b5d06bd1b869.png
[sqlserver_snap_13]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_13_thumb.gif
[sqlserver_snap_13_sqlserver_snap_13]: /images/20220709/c437f430f99545d3b7a7f7eb932d4975.png
[sqlserver_snap_14]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_14_thumb.gif
[sqlserver_snap_14_sqlserver_snap_14]: /images/20220709/39102df3f1144c6493ca7aaaad0d984c.png
[sqlserver_snap_15]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_15_thumb.gif
[sqlserver_snap_15_sqlserver_snap_15]: /images/20220709/13ef240d0d6043baac845462256006ab.png
[sqlserver_snap_16]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_16_thumb.gif
[sqlserver_snap_16_sqlserver_snap_16]: /images/20220709/1c61a6066838426281bb72ad5c9e8b6a.png
[sqlserver_snap_18]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_18_thumb.gif
[sqlserver_snap_18_sqlserver_snap_18]: /images/20220709/6d75b7f5339f467483fdb31effc80d1a.png
[sqlserver_snap_19]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_19_thumb.gif
[sqlserver_snap_19_sqlserver_snap_19]: /images/20220709/e8633db296f249e7b032acc57142755e.png
[sqlserver_snap_20]: http://images.cnblogs.com/cnblogs_com/daizhj/WindowsLiveWriter/Sqlserver_A1FC/sqlserver_snap_20_thumb.gif
[sqlserver_snap_20_sqlserver_snap_20]: /images/20220709/ca0ca98186bf4c97990b361066e18749.png