SQL Server事务复制：如何使用SQL Server数据库备份重新初始化订阅

最新推荐文章于 2024-07-02 09:46:52 发布

culuo4781

最新推荐文章于 2024-07-02 09:46:52 发布

阅读量1k

点赞数

文章标签：数据库 java mysql 大数据 linux

原文链接：https://www.sqlshack.com/sql-server-transactional-replication-how-to-reinitialize-a-subscription-using-a-sql-server-database-backup/

版权

本文探讨了如何使用SQL Server数据库备份来高效地重新初始化事务复制订阅，避免快照生成和网络数据复制的缺点。首先在订阅服务器上还原备份，然后通过T-SQL设置订阅，实现快速数据传递。这种方法适用于处理大型数据库和初始数据集的场景。

摘要由CSDN通过智能技术生成

A workload management is considered as a critical aspect of SQL Server transactional replication. Replication is the oldest of the high availability technologies in SQL Server and it is available since the inception of SQL Server. As a very mature technology, SQL Server transactional replication is also very robust and, in most cases, very straightforward to set up and manage.

工作负载管理被视为SQL Server事务复制的关键方面。复制是SQL Server中最古老的高可用性技术，自SQL Server诞生以来就可以使用。作为一项非常成熟的技术，SQL Server事务复制也非常健壮，并且在大多数情况下，设置和管理非常简单。

In the previous article SQL Replication: Basic setup and configuration, I’ve discussed a lot about setting up a SQL Server transactional replication solution and synchronizing the subscription with a snapshot. As we all know, by default, subscriptions in a SQL Server transactional replication publication are reinitialized with a snapshot, which is generated by the Snapshot Agent and applied by the Distribution Agent. In most of the scenarios, such as those working with very large database and large initial datasets, it is preferable to reinitialize a subscription using another method.

在上一篇文章SQL复制：基本设置和配置中，我讨论了很多有关设置SQL Server事务复制解决方案以及将预订与快照同步的问题。众所周知，默认情况下，SQL Server事务复制发布中的订阅将使用快照重新初始化，该快照由快照代理生成并由分发代理应用。在大多数情况下，例如使用非常大的数据库和庞大的初始数据集的情况，最好使用另一种方法重新初始化订阅。

Other methods of initializing a Subscriber include:

初始化订户的其他方法包括：

Specifying a backup
指定备份
Copying the initial dataset
复制初始数据集

指定备份方法： (Specifying a backup method:)

This is a very simple method. First, restore the backup on the Subscriber, and then setup a subscription using T-SQL. After the setup, the Distribution Agent copies the required SQL Server transactional replication metadata and system procedures that are required for data propagation. It considered as an efficient way to reinitialize a subscriber and it is the fastest way to deliver data to the subscriber. The most recent backup can be used if it was taken after the publication was enabled for initialization with a backup.

这是一个非常简单的方法。首先，在订阅服务器上还原备份，然后使用T-SQL设置订阅。设置完成后，分发代理将复制数据传播所需的所需SQL Server事务复制元数据和系统过程。它被认为是重新初始化订户的有效方法，并且是向订户传递数据的最快方法。如果最近的备份是在启用发布以进行备份初始化之后进行的，则可以使用该备份。

In this article, we’ll discuss more about “Specifying a backup” method

在本文中，我们将讨论有关“指定备份”方法的更多信息。

复制初始数据集： (Copying an initial dataset:)

In this method, the schema-and-data are copied to the Subscriber through any of the known schema-and-data copy method. And then setup a subscription with “replication support only” option. The Distribution Agent copies any required metadata and system procedures. At the Publisher on the publication database, execute sp_addsubscription. Specify the name of the database at the Subscriber containing the published data for @destination_db, a value of push for @subscription_type, and a value of “replication support only” for @sync_type.

在此方法中，通过任何已知的架构和数据复制方法将架构和数据复制到订阅服务器。然后使用“仅复制支持”选项设置订阅。分发代理复制任何必需的元数据和系统过程。在发布服务器上的发布数据库上，执行sp_addsubscription。在订阅服务器上指定数据库名称，该数据库名称包含@destination_db的已发布数据，@ subscription_type的push值和@sync_type的“ 仅复制支持 ”值。

It will be discussing more about this method in my next article.

我的下一篇文章将讨论有关此方法的更多信息。

SQL Server transactional replication diagram:

SQL Server事务复制图：

问题陈述 (Problem statement)

As we all know, by default, subscriptions in a transactional publication are reinitialized with a snapshot, which is generated by the Snapshot Agent and applied by the Distribution Agent. A snapshot is a point-in-time picture of the related objects (articles) of the publication. By default, in SQL Server transactional replication, the data will be loaded via BCP or Bulk Insert. Let us assume a scenario where it is required to publish articles of VLDB (Very Large Database) to Subscribers. By default, for the initial synchronization, requires a snapshot generation. In some cases, space is a big constraint and also it will lock the articles. It will create concurrency issues as well. For mission-critical database this is not practical as it can take a very long time to create an initial snapshot and reinitialize the Subscribers from that generated snapshot.

众所周知，默认情况下，事务发布中的订阅会使用快照重新初始化，该快照由快照代理生成并由分发代理应用。快照是出版物的相关对象（文章）的时间点图片。默认情况下，在SQL Server事务复制中，将通过BCP或批量插入来加载数据。让我们假设需要将VLDB（大型数据库）的文章发布到订阅服务器的情况。默认情况下，对于初始同步，需要快照生成。在某些情况下，空间是一个很大的限制，并且也会锁定商品。也会产生并发问题。对于关键任务数据库，这是不切实际的，因为创建初始快照并根据生成的快照重新初始化订阅服务器可能会花费很长时间。

入门 (Getting Started)

To set up SQL Server transactional replication, you must configure the Distributor and create a Publication and a Subscription using backup option

若要设置SQL Server事务复制，必须配置分发服务器并使用备份选项创建发布和预订。

To reinitialize a subscription with a backup, you first must enable “Initialize with backup” option when you create a publication, and then specify values for @sync_type, @backupdevicetype, and @backupdevicename options when you create a subscription. Publication option can be enabled through the New Publication Wizard using SSMS or programmatically using T-SQL. However, the values required for the setting up a subscription option can only be specified programmatically using T-SQL.

要使用备份重新初始化订阅，必须首先在创建发布时启用“使用备份初始化”选项，然后在创建订阅时为@ sync_type，@ backupdevicetype和@backupdevicename选项指定值。可以使用SSMS通过“新建发布向导”来启用发布选项，也可以使用T-SQL以编程方式来启用发布选项。但是，只能使用T-SQL以编程方式指定设置订阅选项所需的值。

Let us get into the details of the setup:

让我们进入设置的细节：

How to setup a basic SQL Server Transactional Replication 如何设置基本SQL Server事务复制的配置分发服务器部分。
To configure the SQL Server transactional replication publisher, refer to the Configure Publisher section of the article How to setup a basic SQL Server Transactional Replication. In the 8^th step, On the Snapshot Agent page, the options “Create a snapshot immediately and keep the snapshot available to reinitialize subscriptions” and
“Schedule the Snapshot Agent to run the following times” are left blank and Click Next.
若要配置SQL Server事务复制发布者，请参考文章如何设置基本SQL Server事务复制的“配置发布者”部分。在^第 8步，在快照代理页中，选择“ 立即创建快照，并保留快照可重新初始化订阅 ”和
“ 计划快照代理运行以下时间 ”保留为空，然后单击“ 下一步” 。
The other steps remain the same

其他步骤保持不变
You could notice that there is no snapshot step in the Creating Publication page.

您可能会注意到“ 创建发布”页面中没有快照步骤。
After creating the publication, you need to set the ‘Allow_Initialize_From_Backup’ parameter to true at the publisher. You can either do this using T-SQL or SMSS.
创建发布后，需要在发布者处将“ Allow_Initialize_From_Backup”参数设置为true。您可以使用T-SQL或SMSS进行此操作。
```
USE [AdventureWorks2016]
GO
DECLARE @publication AS sysname
SET @publication = N'AdventureWorks2016_Publisher_BKP' 
EXEC sp_changepublication 
  @publication = @publication, 
  @property = N'allow_reinitialize_from_backup', 
  @value = 'true'
GO
```
OR

要么

Browse to the Publication Properties and select the Subscription Options, set “Allow initialization from backup files“ to “true” from the drop-down list and Click Ok to save the change.

浏览到发布属性，然后选择订阅选项 ，从下拉列表中将“ 允许从备份文件初始化 ”设置为“ true ”，然后单击“ 确定”保存更改。
Create a backup of the SQL Server transactional replication publication database using the Backup command. Let’s initiate a full backup or T-log backup if you’ve already initiated a full backup.

使用备份命令创建SQL Server事务复制发布数据库的备份。如果您已经启动了完整备份，请启动完整备份或T日志备份。
```
BACKUP DATABASE AdventureWorks2016 TO DISK = 'f:\PowerSQL\AdventureWorks2016PublisherDB.bak' WITH FORMAT
```

Next, restore the database backup on the SQL Server transactional replication Subscriber using the RESTORE database command

接下来，使用RESTORE database命令在SQL Server事务复制订阅服务器上还原数据库备份。

USE [master]
RESTORE DATABASE [AdventureWorks2016] FROM  DISK = N'F:\PowerSQL\AdventureWorks2016PublisherDB.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2016_Data' TO N'f:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\AdventureWorks2016_Data.mdf', 
 MOVE N'AdventureWorks2016_Log' TO N'g:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\AdventureWorks2016_Log.ldf',  
 NOUNLOAD,  REPLACE,  STATS = 5

On the publication database, execute the sp_addsubscription system stored procedure. Specify the following parameter

在发布数据库上，执行sp_addsubscription系统存储过程。指定以下参数
- @sync_type – “@sync_type –“ reinitialize with backup” 使用备份重新初始化 ”
- @backupdevicetype – the type of the backup device. In this case it’s Disk @backupdevicetype –备份设备的类型。在这种情况下是磁盘
- @backupdevicename – Physical or logical backup device name. In this case, its physical, so type in the full path of the backup file. If the most recent backup is transactional log then specify the transaction log file full path. @backupdevicename –物理或逻辑备份设备名称。在这种情况下，它是物理的，因此请键入备份文件的完整路径。如果最新备份是事务日志，则指定事务日志文件的完整路径。
- This script uses sqlcmd scripting variables
  
  该脚本使用sqlcmd脚本变量
:setvar publicationDB N’AdventureWorks2014′;
:setvar publication N’AdventureWorks2016_Publisher_BKP’;
:setvar job_login N’Domain\ID001′;
:setvar job_password N’thanVitha@2016′;
:setvar subscriber N’hqdbt01\SQL2017′
:setvar subscriptionDB N’AdventureWorks2016_REPL_Rpt’

：setvar publicationDB N'AdventureWorks2014';
：setvar出版物N'AdventureWorks2016_Publisher_BKP';
：setvar job_login N'Domain \ ID001';
：setvar job_password N'thanVitha @ 2016';
：setvar订户N'hqdbt01 \ SQL2017'
：setvar subscriptionDB N'AdventureWorks2016_REPL_Rpt'
```
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = $(publication);
SET @subscriber = $(subscriber);
SET @subscriptionDB =$(subscriptionDB);
```
```
--Add a push subscription to a transactional publication.
USE [AdventureWorks2014]
EXEC sp_addsubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @destination_db = @subscriptionDB,
  @sync_type= N'reinitialize with backup',
  @backupdevicetype='Disk',
  @backupdevicename='F:\PowerSQL\AdventureWorks2016PublisherDB.bak',
  @subscription_type = N'push',
  @update_mode = N'read only';
```
The easiest way to launch the SQL Replication Monitor is from SSMS, as described in the following steps:

启动SQL复制监视器的最简单方法是从SSMS启动，如以下步骤所述：
- Using SSMS, connect to the replication SQL Server instance
  使用SSMS，连接到复制SQL Server实例
- In Object Explorer, locate the Replication folder, right-click the folder, and then click Launch Replication Monitor
  在对象资源管理器中，找到“复制”文件夹，右键单击该文件夹，然后单击“启动复制监视器”。
- You can see that the performance is in an excellent state
  
  您可以看到性能处于良好状态
- If you select the Agents tab, you can see that there is no Snapshot agent created in the entire process.
  如果选择“代理”选项卡，则可以看到在整个过程中没有创建快照代理。

摘要 (Summary)

So far, we discussed the details to reinitialize a SQL Server transaction replication subscription to a publication from a backup, enable the publication to allow initialization from a backup, and then specify backup information when creating the subscription.

到目前为止，我们讨论了以下详细信息：从备份重新初始化对发布SQL Server事务复制订阅，启用发布以允许从备份进行初始化，然后在创建订阅时指定备份信息。

This method has a big advantage in terms of bypassing the snapshot generation step and a bulk copy of data over a network but the drawback is that, we have to restore the full database on the subscriber. In this case, you need to perform a deletion of those objects that are not necessary for SQL Server transactional replication.

就绕过快照生成步骤和通过网络批量复制数据而言，此方法具有很大的优势，但缺点是，我们必须在订户上还原完整的数据库。在这种情况下，您需要删除SQL Server事务复制不需要的那些对象。

In the next article, we will see how to build a SQL Server transactional replication solution using the “Schema and data copy only method”.

在下一篇文章中，我们将看到如何使用“仅架构和数据复制方法”构建SQL Server事务复制解决方案。

That’s all for now… Happy replicating!

现在就这些了……复制愉快！