MSSQL订阅与发布：推动数据同步的最佳解决办法-猿码集

1. MSSQL数据库订阅与发布介绍

SQL Server 以“订阅和发布”技术为基础提供了一种数据同步的解决方案，它允许数据的发生端利用某个发布者将数据传送给流式接收端。这有助于构建复杂的数据共享环境，以提高应用程序的性能和可靠性。

1.1 发布者和订阅者

MSSQL数据库中，发布者提供了一种分发更改的机制，订阅者则接收并应用这些更改。发布者/订阅者架构中两个最重要的概念就是发布者和订阅者。声明自己为发布者的SQL Server实例可以发布一系列表、视图、存储过程和函数，以及相关的更改通知。订阅者是指将这些更改应用到自己的数据表中，以便其数据与发布方始终同步。

1.2 发布/订阅模式

在 SQL Server 数据库中，可以选择多种发布/订阅模式——

单项发布/订阅：订阅者只向发布者订阅数据更改，而发布者不会接收来自订阅者的任何更改。

双向发布/订阅：订阅者可以接收发布者的更改并将自己的更改上传到发布者。

匿名发布/订阅：在匿名发布者和订阅者中，使用共享的预设证书或 Windows 凭据，以启用对订阅者的远程匿名访问。

2. MSSQL数据库订阅与发布的好处

订阅和发布技术的主要优点是提供了数据库之间的分布式数据共享，使组织能够跨地理位置更好地管理其业务和信息需求。

2.1 更好的性能和扩展性

通过将数据发布到不同的实例和主机上，订阅和发布模型提高了可扩展性和性能，特别是在复杂性更高的企业级应用程序环境中。发布/订阅技术可以让数据的流量更为均衡，减轻了数据库服务器的压力，使得系统性能更加稳定。

2.2 提高数据的可用性和可靠性

订阅和发布技术通过增加冗余以及可能的故障切换，可以提高数据的可用性和可靠性。在出现故障的情况下，这种数据复制机制可以自动切换到备用发布者或订阅者，重新建立工作数据流，极大地减少了数据丢失的风险。

3. 使用MSSQL数据库订阅与发布实现数据同步

3.1 创建发布者

要创建发布者，需要执行以下步骤：

在SQL Server Management Studio中，打开 “对象资源管理器”，右键点击 “SQL Server 实例”并选择“新建发布”

在“新建发布向导”中，选择适当的发布类型，例如“创建新的发布”或“使用现有的发布”，然后单击“下一步”。（本例中选择“创建新的发布”）

选择发布数据库和发布类型，然后单击“下一步”

选择要发布的对象以及发布设置，单击“下一步”。

定义“订阅”和“分发代理”，然后单击“下一步”（注意，可以为每个订阅者定义独立的分发代理）

预览发布的设置，然后按“完成”按钮完成发布设置。

下面是SQL Server Management Studio中创建发布者的样例代码：


-- 创建一个新的发布，需要连接登录名及密码，发布名称&服务器以及发布的数据库名称。 
exec sp_addpublication @publication = N'ProductPublication', 
    @description = N'MSSQL 订阅和发布技术的样例', 
    @sync_method = N'native', 
    @retention = 0, 
    @allow_push = N'true', 
    @allow_pull = N'true', 
    @allow_anonymous = N'false', 
    @enabled_for_internet = N'false', 
    @snapshot_in_defaultfolder = N'true', 
    @compress_snapshot = N'false', 
    @ftp_port = 21, 
    @allow_subscription_copy = N'false', 
    @add_to_active_directory = N'false', 
    @publication_type = 0;
 
exec sp_addpublication_snapshot @publication = N'ProductPublication', 
     @frequency_type = 1, 
     @frequency_interval = 1, 
     @frequency_relative_interval=1, 
     @frequency_recurrence_factor=0, 
     @frequency_subday = 1, 
     @frequency_subday_interval=5, 
     @active_start_time_of_day = 50000, 
     @active_end_time_of_day = 235959;

3.2 创建订阅者

要创建订阅者，需要执行以下步骤：

在SQL Server Management Studio中，打开 “对象资源管理器”，右键击 “SQL Server 实例”并选择“新建订阅”

在“新建订阅向导”中，选择适当的订阅类型，然后单击“下一步”。（本例中选择了“从共享位置推送快照和交付订阅”）

定义订阅关系的详细信息，例如发布服务器、发布数据库、订阅名称，然后单击“下一步”

定义要复制的对象、存储设置，以及订阅的设置，然后单击“下一步”。

即可预览订阅设置，然后单击“完成”按钮来完成订阅的创建过程。

下面是SQL Server Management Studio中创建订阅者的样例代码：


-- 创建新的订阅者
exec sp_addsubscription @publication = N'ProductPublication',
    @subscriber = $(SubscriberServer),
    @destination_db = N'ProductDatabase', 
    @subscription_type = N'Push', 
    @sync_type = N'automatic', 
    @subscriber_type = 0, 
    @subscription_priority = 0, 
    @backupdevicename = N'', 
    @add_to_active_directory = N'false', 
    @status = N'active', 
    @independent_agent = N'true', 
    @immediate_sync = N'true', 
    @allow_sync_tran = N'false', 
    @autogen_sync_procs = N'false', 
    @allow_queued_tran = N'false', 
    @allow_dts = N'false', 
    @replicate_ddl = 1, 
    @allow_subscription_copy = N'false', 
    @ip_resolution = N'DnsOrFail', 
    @ftp_port = 21, 
    @anonymous_agent = N'false', 
    @job_login = null, 
    @job_password = null,
    @use_interactive_resolver = N'false';
--启用订阅器
exec sp_startpublication_snapshot @publication = N'ProductPublication'
go

3.3 数据同步

当发布者中的数据发生变化时，它就会将这些更改发送到所有相关的订阅者。订阅者可以使用订阅数据库中的“订阅器代理”将接收到的更改应用到本地数据库。可以通过以下方式来实现数据同步：

在发布者机器上打开SQL Server Management Studio，找到发布者在“对象资源管理器”中的位置，单击“启动同步”以启动发布同步代理。

在订阅者机器上打开SQL Server Management Studio，找到订阅者在“对象资源管理器”中的位置，单击“启动同步”以启动订阅同步代理。

等待直到同步代理完成同步操作。

下面是SQL Server Management Studio中启动同步代理的样例代码：


-- 启动发布者同步任务
exec sp_addpushsubscription_agent @publication = N'ProductPublication', 
    @subscriber = $(SubscriberServer), 
    @subscriber_db = N'ProductDatabase', 
    @job_login = null, 
    @job_password = null, 
    @subscriber_security_mode = 1, 
    @frequency_type = 64, 
    @frequency_interval = 0, 
    @frequency_relative_interval = 0, 
    @frequency_recurrence_factor = 0, 
    @frequency_subday = 0, 
    @frequency_subday_interval = 0, 
    @active_start_time_of_day = 0, 
    @active_end_time_of_day = 235959, 
    @active_start_date = 0, 
    @active_end_date = 0, 
    @dts_package_location = N'Distributor', 
    @publisher_security_mode = 1;
-- 启动订阅者同步任务
exec sp_startjob @job_name=N'SnapshotAgent-SubscriberServer-ProductPublication-ProductDatabase-ProductDatabaseSubscription'
go

4. 结论

使用 MSSQL 数据库的订阅和发布功能，可以轻松实现分布式数据同步，提高数据的可靠性、可用性和应用程序性能。通过以上步骤，您可以方便地创建发布者和订阅者，并实现数据同步操作。这种解决方案在处理大量关键信息和数据网络应用的过程中发挥着重要的作用。因此，如果有需要对数据库进行数据同步的场景，可以考虑使用 MSSQL 数据库的订阅和发布功能。

MSSQL订阅与发布：推动数据同步的最佳解决办法