复制MSSQL服务器订阅局域网内实现数据复制

1. 概述

在MSSQL数据库中，将数据复制到另一个服务器是一个常见的场景。这种情况下，数据库管理员可能需要在两个服务器之间复制数据以便更好地管理大量的数据。在本文中，我们将关注如何使用订阅和发布模型实现数据复制到局域网内的另一个MSSQL服务器。

2. 订阅与发布模型

订阅和发布模型是SQL Server提供的一种复制方式。在此模型中，源数据库使用发布器将更改写入发布数据库中的发布表中。然后，订阅器使用订阅器代理来读取发布表中的更改并将其应用于订阅数据库中的相应表。下面我们将详细介绍如何设置发布者和订阅者。

2.1 发布者设置

首先，我们需要创建一个发布数据库。在我们的案例中假设我们的发布数据库名为MyPublicationDB，该数据库中包含MyPublicationTable表。接下来，我们需要执行以下步骤设置发布者：

启用发布

  USE [MyPublicationDB]
  EXEC sp_replicationdboption @dbname = N'MyPublicationDB', @optname = N'publish', @value = N'true'
  

创建并启用发布器

  EXEC sp_addpublication @publication = N'MyPublication', @description = N'Transactional publication of database ''MyPublicationDB'' from Publisher ''MyPublisher''', @sync_method = N'concurrent_c', @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, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
  EXEC sp_addpublication_snapshot @publication = N'MyPublication', @frequency_type = 1, @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, @job_login = null, @job_password = null, @publisher_security_mode = 1;
  EXEC sp_grant_publication_access @publication = N'MyPublication', @login = N'sa';
  

创建并启用发布器。这个发布者将MyPublicationDB数据库中的更改保存到指向另一个MSSQL服务器的分发文件夹中。

  EXEC sp_addpublication @publication = N'MyPublication', @description = N'Transactional publication of database ''MyPublicationDB'' from Publisher ''MyPublisher''', @sync_method = N'concurrent_c', @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, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
  EXEC sp_addpublication_snapshot @publication = N'MyPublication', @frequency_type = 1, @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, @job_login = null, @job_password = null, @publisher_security_mode = 1;
  EXEC sp_grant_publication_access @publication = N'MyPublication', @login = N'sa';
  EXEC sp_add_distribution_db @database = N'distribution', @security_mode = 1, @version = null, @cleanup_mode = 0, @job_login = null, @job_password = null, @publisher_view_password = null, @publisher_security_mode = 1
  EXEC sp_add_linkedsrvlogin @rmtsrvname = N'MyPublisher', @useself = N'False', @locallogin = NULL, @rmtuser = N'MyPublisherUser', @rmtpassword = 'MyPublisherPassword'
  

2.2 订阅者设置

有了上面创建好的发布者，我们现在将在订阅者上创建一个newspaper类的订阅。下面我们将详细介绍如何在订阅者中设置订阅：

创建订阅者

  EXEC sp_addsubscription @publication = N'MyPublication', @subscriber = N'MySubscriber', @destination_db = N'MyDestinationDB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
  EXEC sp_addpushsubscription_agent @publication = N'MyPublication', @subscriber = N'MySubscriber', @subscriber_db = N'MyDestinationDB', @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 = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 80000, @active_end_time_of_day = 170000, @active_start_date = 20160301, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_retry_attempts = 5, @job_retry_interval = 5, @publication_type = 0, @dts_package_name = N'', @dts_package_password = null, @dts_package_location = N'', @enabled_for_syncmgr = N'False', @distribution_job_name = N'MyPublication-MySubscriber-Job', @publisher = N'MyPublisher', @local_login = NULL, @credential_id = NULL
  

3. 局域网内数据的复制

在本节中，我们将详细介绍如何使用订阅和发布模型在局域网内复制数据。

设置发布数据库

我们需要创建一个发布数据库和一个或多个发布表，如前面章节所述。

设置分布服务器

我们还需要设置一个发布数据库和分布服务器，它负责将更改信息传播到订阅服务器，如前面章节所述。

设置订阅服务器

首先，我们需要在订阅服务器上创建一个与发布数据库相同的数据库，然后创建与发布表相同的表。表可以是空的，因为它将通过发布者获得数据。接下来，我们需要创建一个订阅，如前面章节所述。

启动订阅代理

现在，我们需要在订阅服务器上启动订阅代理。订阅代理负责接收发布者的更改，将它们写入订阅服务器上的相应表中。下面是启动订阅代理的代码：

  EXEC sp_startsubscription_agent @publisher = N'MyPublisher', @publication = N'MyPublication', @subscriber = N'MySubscriber', @subscriber_db = N'MyDestinationDB', @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 = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 80000, @active_end_time_of_day = 170000, @active_start_date = 0, @active_end_date = 0, @dts_package_name = N'', @dts_package_password = null, @dts_package_location = N'', @enabled_for_syncmgr = N'False', @distribution_job_name = N'MyPublication-MySubscriber-Job', @use_distributor = N'MyPublisher', @publication_type = 0
  

测试订阅

现在我们已经设置好订阅和发布数据库，我们可以开始测试是否成功进行了数据复制。如果一切正常，我们应该能够在订阅服务器上看到来自发布者的更改。

4. 结论

本文介绍了如何使用订阅和发布模型实现MSSQL服务器之间的数据复制。从上面的步骤中，我们可以看到这个复制过程比较简单而且易于操作。通过实践，您可以更好地理解该过程，并在需要时使用它。希望您已经从本文中获得了有用的信息。