This article discusses the challenges of meeting the availability, and performance requirements of high ended transactional replication environments. In addition, you’ll learn a new innovative approach that can be used to add/drop articles from an existing replication environment while maintaining replication system up and running.
本文讨论了满足高端事务复制环境的可用性和性能要求的挑战。 此外，您还将学习一种新的创新方法，该方法可用于在现有复制环境中添加/删除文章，同时保持复制系统的正常运行。
After reading this article, you should be able to perform the following tasks:
阅读本文之后，您应该能够执行以下任务：
- Adding/dropping article(s) with a snapshot 添加/删除带有快照的文章
- Adding/dropping article(s) without generating a snapshot using T-SQL 在不使用T-SQL生成快照的情况下添加/删除文章
- Adding an article to a subscription initialized through the backup using SSMS 使用SSMS将文章添加到通过备份初始化的订阅中
- And more… 和更多…
先决条件 (Pre-requisites)
- Requires understanding of replication terminologies such as articles, publisher, subscriber, and snapshot agent, log reader agent, and distribution agent 需要了解复制术语，例如文章，发布者，订户和快照代理，日志读取器代理和分发代理
- Good understanding of transactional replication topology 很好地了解事务复制拓扑
- Replication monitor 复制监视器
- Need to have an account with syadmin and db_owner role 需要具有syadmin和db_owner角色的帐户
- he他 r- [R e é
Let us deep-dive into the concepts of adding article(s) to the existing transactional replication.
让我们深入研究将文章添加到现有事务复制中的概念。
通过重新生成快照从现有出版物中添加或删除文章 (Adding or dropping articles from existing publication by regenerating a snapshot)
This is simple, straight-forward and Microsoft recommended practice. The walk-through of the above steps is common where the environment is small and relatively has less workload on the replication database and getting outage windows is relatively much easier.
这是简单，直接的方法，是Microsoft建议的做法。 在环境较小且复制数据库上的工作量相对较小且中断窗口相对容易得多的情况下，通常需要执行上述步骤。
Adding an article involves the following tasks
添加文章 涉及以下任务
- Adding the article to the publication 将文章添加到出版物
- Creating a new snapshot for the publication 为发布创建新快照
- Using the distribution agent, synchronize the subscription database by applying the schema and data for the new article 使用分发代理，通过应用新文章的架构和数据来同步订阅数据库
通过使快照代理无效来从现有发布中添加/删除文章 (Adding/dropping article(s) from an existing publication by invalidating the snapshot agent )
As I mentioned above, adding articles to and dropping articles from an existing publication, you must create a new snapshot for the publication. This is the recommended practice. To avoid generating a snapshot for all articles when adding a new article, publication property immediate_sync must be set to 0 and then call sp_addarticle, followed by sp_addsubscription. If it is pull subscription, you must call sp_refreshsubscriptions. Then generate a snapshot and this process will yield to generate snapshot only for the newly added articles.
如前所述，在现有出版物中添加文章或从现有出版物中删除文章，必须为该出版物创建一个新的快照。 这是推荐的做法。 为了避免增加新的文章的时候生成快照的所有文章，公布财产immediate_sync必须设置为0，然后调用sp_addarticle的，其次是sp_addsubscription。 如果是请求订阅，则必须调用sp_refreshsubscriptions 。 然后生成快照，此过程将产生仅为新添加的文章生成快照的信息。
As invalidating the snapshot is not a recommended option, but still, this is more important when we deal with large replication database and we don’t want to reinitialize snapshot agent because of the database size and other considerations in mind. In order to do this; we need to set the publisher properties allow_anonymous and Immediate_sync to False. Let us do this using the following T-SQL.
由于不建议使快照无效，但是仍然如此，当我们处理大型复制数据库并且由于数据库大小和其他考虑因素而不想重新初始化快照代理时，这更为重要。 为此; 我们需要将发布者属性allow_anonymous和Instant_sync设置为False。 让我们使用以下T-SQL进行此操作。
First, change the allow_anonymous property of the publication to FALSE
首先，将发布的allow_anonymous属性更改为FALSE
EXEC sp_changepublication @publication = N'Adventureworks2016-Pub', @property = N'allow_anonymous', @value = 'FALSE' GONext, disable Change immediate_sync
接下来，禁用Change Instant_sync
EXEC sp_changepublication @publication = N'Adventureworks2016-Pub', @property = N'immediate_sync', @value = 'FALSE' GOInvalidate the snapshot
使快照无效
EXEC sp_addarticle @publication = N'Adventureworks2016-Pub', @article =N'Cities', @source_object =N'Cities', @force_invalidate_snapshot=1Refresh subscriptions
刷新订阅
EXEC sp_refreshsubscriptions @publication = N'Adventureworks2016-Pub'Now, start Snapshot Agent using Replication monitor
现在，使用“复制”监视器启动Snapshot Agent
You should notice that bulk-insert statements are created only for the specific article instead of all articles,
您应注意，仅为特定文章而不是所有文章创建批量插入语句，
- Next, start log reader agent 接下来，启动日志读取器代理
Re-enable the disabled properties, first, immediate_sync and then Allow_anonymous options
重新启用禁用的属性，首先是Instant_sync，然后是Allow_anonymous选项
EXEC sp_changepublication @publication = N'Adventureworks2016-Pub', @property = N'immediate_sync', @value = 'TRUE' EXEC sp_changepublication @publication = N'Adventureworks2016-Pub', @property = N'allow_anonymous', @value = 'TRUE'- Now you can verify the article on all your subscribers 现在，您可以在所有订阅者上验证该文章
Let us talk about the other scenario as well. In the real world, there are situations where articles are out of sync. In a real-time system, getting an outage window is a daunting task. And effectively using that window is another challenge. By default, transactional replication, locks will be applied on the related transactional articles only during the initial phase of snapshot generation. If the publisher database is huge in size, then there is an impact on the user actions as locks prevents the users from insert/update operations. However, if the table is HUGE and out of sync and it is required to re-sync and you don’t have any other option—drop the article in LIFO (Last-In-First-Out) order and re-add the article or Use data export utilities to sync the data or use BCP or SSIS to sync the subscription.
让我们也讨论其他情况。 在现实世界中，有些情况下文章不同步。 在实时系统中，获取中断窗口是一项艰巨的任务。 有效使用该窗口是另一个挑战。 默认情况下，事务复制锁定仅在快照生成的初始阶段才会应用于相关的事务项目。 如果发布者数据库规模巨大，则会对用户操作产生影响，因为锁会阻止用户进行插入/更新操作。 但是，如果表太大且不同步，并且需要重新同步，并且您没有其他选择，请以LIFO（先进先出）顺序放置文章，然后重新添加文章或使用数据导出实用程序同步数据，或使用BCP或SSIS同步订阅。
Here are the steps to follow:
以下是要遵循的步骤：
- To drop the article from the subscriber, run sp_dropsubscription 要从订阅者中删除文章，请运行sp_dropsubscription
- To drop the article from the publisher, run sp_droparticle 要从发布者处删除文章，请运行sp_droparticle
- To re-add the article to publication with the Immediate_sync option set to false 要将文章重新添加到发布中，且Instant_sync选项设置为false
将文章添加到通过备份初始化的订阅中 (Adding an article to a subscription initialized through the backup )
Let us talk about another scenario where we set up transactional replication using a database backup. Typically, transactional replication is initialized with a snapshot. In this case, the subscription is initialized from a backup. When you setup a subscription with sync_type=’replication support only’, it does not allow to add any more articles.
让我们谈谈另一种使用数据库备份设置事务复制的方案。 通常，事务复制使用快照初始化。 在这种情况下，将从备份中初始化订阅。 当您使用sync_type ='仅复制支持'设置订阅时，不允许添加更多文章。
Let us see the steps on how to add an article to an existing Transactional Subscription initialized through backup
让我们看看有关如何将文章添加到通过备份初始化的现有事务订阅中的步骤
Adding new articles to a publisher and a subscriber can be addressed in two ways
向发布者和订阅者添加新文章可以通过两种方式解决
- Create a new publication for just that article and follow the same steps that we followed to add an article by generating the snapshot 为该文章创建一个新的出版物，并通过生成快照按照与添加文章相同的步骤进行操作
- Add the article to an existing publication and manually synchronizing the data between publisher and subscriber 将文章添加到现有出版物中，并在发布者和订阅者之间手动同步数据
The setup instruction is not discussed in this article and it is out of scope for the discussion. You’ll see the demo of adding article to existing publisher and subscriber that has been configured with “replication support only“ feature. Unlike initializing with a backup, you or your application must ensure the data and schema are properly synchronized at the time you add the subscription. If, for example, there is activity on the Publisher between the time data and schema are copied to the Subscriber and the time at which the subscription is added, changes resulting from this activity might not be replicated to the Subscriber.
本文中没有讨论设置指令，因此超出了讨论范围。 您将看到将文章添加到已配置有“ 仅复制支持 ”功能的现有发布者和订阅者的演示。 与使用备份初始化不同，您或您的应用程序必须在添加订阅时确保数据和架构正确同步。 例如，如果在将数据和架构复制到订阅服务器与添加订阅的时间之间在发布服务器上存在活动，则可能无法将由此活动产生的更改复制到订阅服务器。
Let us follow the steps to add an article to publisher
让我们按照以下步骤向发布者添加文章
- Copy the newly created tables from Publisher to Subscriber using any known methods 使用任何已知方法将新创建的表从Publisher复制到订阅服务器
First, stop the log reader agent. It is the recommended practice to stop the log-reader agent before doing any operations with replication articles
首先，停止日志读取器代理。 建议您对复制文章进行任何操作之前，先停止日志读取器代理
- Now, quiesce the table. In this way you can ensure the data consistency between publisher and subscriber 现在，停顿桌子。 这样可以确保发布者和订阅者之间的数据一致性
-
- Replication folder and select the 复制文件夹，然后在AdventureWorks-Pub under 本地出版物下选择Local Publications AdventureWorks-Pub
- Right-click and select 右键单击并选择Properties 属性
- Select a page, Choose选择页面”中，选择“ Articles 文章”
- Show only checked articles in the list 仅显示列表中已选中的文章
Select the Articles under Object to Publish and then Click Ok. In this case, Cities article is added the AdventureWorks -Pub
选择要发布的对象下的文章，然后单击确定。 在这种情况下， 城市文章添加了AdventureWorks -Pub
- The configuration is complete 配置完成
Add few more rows to table in the Publisher database. In this case, 10 rows are added. The output has two data sets. The first referencing publisher article and the second is subscriber
在发布服务器数据库中的表中再添加几行。 在这种情况下，将添加10行。 输出具有两个数据集。 第一篇引用发布者文章，第二篇是订阅者文章
- Next, start log reader agent. In this way, the article is ready to accept any change and the log reader will be able to start tracking the changes from this point 接下来，启动日志读取器代理。 这样，文章就可以接受任何更改，并且日志阅读器将能够从此开始跟踪更改
Launch Replication monitor to monitor the transactions.
启动复制监视器以监视事务。
Let’s do a QAD data Comparison to measure difference. Use the following T-SQL to query the articles. In this case, T-SQL is executed under SQLCMD mode. You can change the source and target server instance names as per your environment
让我们进行QAD数据比较以测量差异。 使用以下T-SQL查询文章。 在这种情况下，T-SQL在SQLCMD模式下执行。 您可以根据您的环境更改源服务器实例名称和目标服务器实例名称
:CONNECT <Source> GO select @@servername select * from AdventureWorks2016.dbo.cities GO :CONNECT <target> GO select @@servername select * from AdventureWorks2016_Report.dbo.citiesNow, you can see the rows are in sync in the newly created tables
现在，您可以看到新创建的表中的行是同步的
结语 (Wrap Up)
In this article, we looked at core replication features about how to add articles to transactional replication that are configured with a snapshot and also a scenario where replication is configured using a backup. After reading this article, you might have realized the how difficult the process is and many DBAs might not even be aware of how to set up replication using backup.
在本文中，我们研究了核心复制功能，这些功能涉及如何向使用快照配置的事务复制中添加项目，以及如何使用备份配置复制的方案。 阅读本文之后，您可能已经意识到该过程有多么困难，并且许多DBA甚至可能都不知道如何使用备份来设置复制。
In the next article, How to set up a DDL and DML SQL Server database transactional replication solution, we’ll take another approach to this use case using different technologies
在下一篇文章如何建立DDL和DML SQL Server数据库事务复制解决方案中 ，我们将使用不同的技术针对该用例采用另一种方法
目录 (Table of contents)
| SQL Server复制：组件和拓扑概述 |
| SQL复制：基本设置和配置 |
| 如何从SQL Server中的现有出版物中添加/删除文章 |
| 如何对两个大型SQL Server数据库中的数据进行快速估计比较，以查看它们是否相等 |
| SQL Server事务复制：如何使用SQL Server数据库备份重新初始化订阅 |
| 如何使用中央订阅服务器和多个发布者数据库设置自定义SQL Server事务复制模型 |
| 如何使用中央发布者和多个订阅者数据库设置自定义SQL Server事务复制 |
| 如何设置DDL和DML SQL Server数据库事务复制解决方案 |
| 如何在Linux上为数据库报告设置跨平台事务SQL Server复制 |
| SQL Server数据库迁移，数据丢失为零，停机时间为零 |
| 使用事务数据复制来重放和测试登台服务器上的生产负载 |
| 如何为报表服务器设置SQL Server数据库复制 |
| SQL Server事务复制：如何使用“仅复制支持” –TBA重新初始化订阅 |
| 使用PowerShell –TBASQL Server复制监视和设置警报 |
翻译自: https://www.sqlshack.com/how-to-add-drop-articles-from-existing-publications-in-sql-server/
2209
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
