The volume of data retained, managed, and accessed today is unprecedented. Businesses expect the IT department to keep data online and accessible indefinitely, putting intense pressure on the databases required to store and manage it. To meet today’s needs; we need to replace outdated and inefficient legacy processes with new, more agile techniques. SQL Server Replication is one of the techniques to accommodate such demands.
如今，保留，管理和访问的数据量是空前的。 企业希望IT部门能够无限期地保持数据在线和可访问，这给存储和管理数据所需的数据库带来了巨大压力。 满足当今的需求； 我们需要用更敏捷的新技术替换过时且效率低下的旧流程。 SQL Server复制是满足此类需求的技术之一。
In this article, let’s you shape your understanding of the full SQL Server replication topography including components, internals and the SQL to bind it all together. After you complete reading this article, you‘ll understand:
在本文中，让您形成对完整SQL Server复制拓扑的理解，包括组件，内部结构和将它们绑定在一起SQL。 阅读完本文后，您将了解：
- SQL Server replication, in general 一般而言，SQL Server复制
- Components of transactional SQL Server replication, in particular 事务性SQL Server复制的组件，尤其是
- How to get distributor properties 如何获得经销商财产
- How to find the publisher using the same distributor 如何使用同一发行人查找发行人
- What are the databases used for SQL Server replication 什么是用于SQL Server复制的数据库
- The general topology of a replication environment 复制环境的一般拓扑
- What are the articles that are mapped to the type of SQL Server replication model 映射到SQL Server复制模型类型的文章有哪些
- How to get publication details 如何获取出版物详细信息
- How to get subscription details 如何获取订阅详细信息
- SQL Server Replication agents SQL Server复制代理
- And more… 和更多…
复写 (Replication )
SQL Server replication is a technology for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency and integrity of the data. In most cases, replication is a process of reproducing the data at the desired targets. SQL Server replication is used for copying and synchronizing data continuously or it can also be scheduled to run at predetermined intervals. There are several different replication techniques that support a variety of data synchronization approaches; one-way; one-to-many; many-to-one; and bi-directional, and keep several datasets in sync with each other.
SQL Server复制是一种用于将数据和数据库对象从一个数据库复制和分发到另一个数据库，然后在数据库之间进行同步以维护数据的一致性和完整性的技术。 在大多数情况下，复制是在所需目标位置复制数据的过程。 SQL Server复制用于连续地复制和同步数据，也可以安排它以预定的间隔运行。 有几种不同的复制技术可支持多种数据同步方法。 单程; 一对多 多对一 和双向，并保持多个数据集彼此同步。
事务性SQL Server复制组件 (Transactional SQL Server replication components )
The following diagram depicts the components of transactional SQL Server replication.
下图描述了事务型SQL Server复制的组件。
Including the SQL Server replication …
包括SQL Server复制...
- Publisher 发行人
- Publication database 出版物数据库
- Publication 出版物
- Articles 文章
- Distributor 发行人
- Distribution database 发行数据库
- Subscriber 订户
- Subscription database 订阅数据库
- Subscription 订阅
- Replication agents 复制代理
SQL Server replication diagram
SQL Server复制图
文章 (Article)
An article is the basic unit of SQL Server Replication. An article can consist of tables, stored procedures, and views. It is possible to scale the article, horizontally and vertically using a filter option. We can also create multiple articles on the same object with some restrictions and limitations.
文章是SQL Server复制的基本单元。 文章可以包含表，存储过程和视图。 使用过滤器选项可以水平和垂直缩放商品。 我们也可以在相同的对象上创建多篇文章，但有一些限制。
Using the New Publication wizard, the Article can be navigated. It allows us to view the properties of an article and provide options to set properties for the articles. In some case, the properties can be set during the time of publication creation and it’s a read-only property.
使用“新建发布”向导， 可以浏览 “ 文章” 。 它使我们可以查看文章的属性，并提供用于设置文章属性的选项。 在某些情况下，可以在创建发布时设置属性，这是一个只读属性。
After the creation of a SQL Server replication publication, for instance, if some property requires a change, it will, in turn, require a new replication snapshot to be generated. If the publication has one or more subscriptions then the change requires all subscriptions to be reinitialized. For more information, see How to add/drop articles to/from existing publication in SQL Server article.
例如，在创建SQL Server复制发布之后，如果某些属性需要更改，那么它将依次需要生成新的复制快照。 如果发布具有一个或多个订阅，则更改需要重新初始化所有订阅。 有关更多信息，请参见如何在SQL Server中的现有出版物中添加文章或从中删除文章 。
To list all the articles that are published, run the following T-SQL
要列出所有已发布的文章，请运行以下T-SQL
SELECT
Pub.[publication] [PublicationName]
,Art.[publisher_db] [DatabaseName]
,Art.[article] [Article Name]
,Art.[source_owner] [Schema]
,Art.[source_object] [Object]
FROM
[distribution].[dbo].[MSarticles] Art
INNER JOIN [distribution].[dbo].[MSpublications] Pub
ON Art.[publication_id] = Pub.[publication_id]
ORDER BY
Pub.[publication], Art.[article]
To get the details of articles in transactional or merge SQL Server replication in a published database, run the following T-SQL.
要获取发布数据库中的事务性或合并SQL Server复制中的文章详细信息，请运行以下T-SQL。
SELECT st.name [published object], st.schema_id, st.is_published , st.is_merge_published, is_schema_published
FROM sys.tables st WHERE st.is_published = 1 or st.is_merge_published = 1 or st.is_schema_published = 1
UNION
SELECT sp.name, sp.schema_id, 0, 0, sp.is_schema_published
FROM sys.procedures sp WHERE sp.is_schema_published = 1
UNION
SELECT sv.name, sv.schema_id, 0, 0, sv.is_schema_published
FROM sys.views sv WHERE sv.is_schema_published = 1;
To get detailed information about an article in the listed publisher, run the following T-SQL
要获取有关列出的发行者中文章的详细信息，请运行以下T-SQL
DECLARE @publication AS sysname;
SET @publication = N'PROD_HIST_Pub';
USE MES_PROD_AP
EXEC sp_helparticle
@publication = @publication;
GO
To get column level details, run the following T-SQL
要获取列级别的详细信息，请运行以下T-SQL
USE MES_PROD_AP
GO
sp_helparticlecolumns @publication = N'PROD_HIST_Pub' , @article = 'tb_Branch_Plant'
To list the columns that are published in transactional replication in the publication database, run the following T-SQL
要列出发布数据库中事务复制中发布的列，请运行以下T-SQL
SELECT object_name(object_id) [published table], name [published column] FROM sys.columns sc WHERE sc.is_replicated = 1;
刊物 (Publications)
A Publication is a logical collection of articles from a database. The entity allows us to define and configure article properties at the higher level so that the properties are inherited to all the articles in that group.
发布是数据库中文章的逻辑集合。 该实体允许我们在更高级别上定义和配置商品属性，以便这些属性被继承到该组中的所有商品。
EXEC sp_helppublication;
发布者数据库 (Publisher database)
The publisher is a database that contains a list of objects that are designated as SQL Server replication articles are known as publication database. The publisher can have one or more publications. Each publisher defines a data propagation mechanism by creating several internal replication stored procedures.
发布者是一个数据库，其中包含被指定为SQL Server复制文章的对象列表，称为发布数据库 。 发布者可以拥有一个或多个出版物。 每个发布者通过创建几个内部复制存储过程来定义数据传播机制。
USE Distribution
GO
select * from MSpublications
发行人 (Publisher)
The Publisher is a database instance that makes data available to other locations through SQL Server replication. The Publisher can have one or more publications, each defining a logically related set of objects and data to replicate.
发布服务器是一个数据库实例，可通过SQL Server复制使数据可供其他位置使用。 发布者可以具有一个或多个发布，每个发布定义一组逻辑上相关的对象和要复制的数据。
发行人 (Distributor)
The Distributor is a database that acts as a storehouse for replication specific data associated with one or more Publishers. In many cases, the distributor is a single database that acts as both the Publisher and the Distributor. In the context of SQL Server replication, this is commonly known as a “local distributor”. On the other hand, if it’s configured on a separate server, then it is known as a “remote distributor”. Each Publisher is associated with a single database known as a “distribution database” aka the “Distributor”.
分发服务器是一个数据库，用作与一个或多个发布服务器关联的复制特定数据的仓库。 在许多情况下，分发服务器是充当发布服务器和分发服务器的单个数据库。 在SQL Server复制的上下文中，这通常称为“本地分发程序”。 另一方面，如果将其配置在单独的服务器上，则称为“远程分发服务器”。 每个发布者都与一个称为“发布数据库”（也称为“发布者”）的数据库相关联。
The distribution database identifies and stores SQL Server replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers.
分发数据库标识并存储SQL Server复制状态数据，有关发布的元数据，并且在某些情况下，充当数据从发布者到订阅者移动的队列。
Depending on the replication model, the Distributor might also be responsible for notifying the Subscribers that have subscribed to a publication that an article has changed. Also, the distribution database maintains the integrity of the data.
根据复制模型，分发服务器还可能负责通知已订阅发布的订阅服务器文章已更改。 而且，分发数据库维护数据的完整性。
发行数据库 (Distribution databases)
Each Distributor must have at least one distribution database. The distribution database consists of article detail, replication meta-data and data. A Distributor can hold more than one distribution database; however, all publications defined on a single Publisher must use the same distribution database.
每个分发服务器必须至少具有一个分发数据库。 分发数据库由商品详细信息，复制元数据和数据组成。 一个分发服务器可以拥有多个分发数据库； 但是，在单个发布服务器上定义的所有发布必须使用相同的分发数据库。
To find out whether …
找出是否...
a server is a distributor or not?
服务器是分销商还是不是分销商？
SELECT @@ServerName Servername, case when is_distributor=1 then 'Yes' else 'No' end status FROM sys.servers WHERE name='repl_distributor' AND data_source=@@servername;
a distribution database installed or not?
是否安装了分发数据库？
SELECT name FROM sys.databases WHERE is_distributor = 1a Publisher is using this Distributor or not?
发布者是否正在使用此分发服务器？
EXEC sp_get_distributor
or just to interrogate various Distributor and Distribution database properties?
还是仅仅询问各种Distributor和Distribution数据库属性？
EXEC sp_helpdistributor; EXEC sp_helpdistributiondb; EXEC sp_helpdistpublisher;
订户 (Subscriber)
A database instance that consumes SQL Server replication data from a publication is called a Subscriber. The subscriber can receive data from one or more publishers and publications. The subscriber can also pass data changes back to the publisher or republish the data to other subscribers depending on the type of the replication design and model.
使用发布中SQL Server复制数据的数据库实例称为订阅服务器。 订户可以从一个或多个发布者和出版物接收数据。 订阅者还可以根据复制设计和模型的类型，将数据更改传递回发布者，或将数据重新发布给其他订阅者。
EXEC sp_helpsubscriberinfo;
订阅内容 (Subscriptions)
A subscription is a request for a copy of a publication to be delivered to a Subscriber. The subscription defines what publication data will be received, where, and when.
订阅是将出版物副本交付给订阅者的请求。 订阅定义将在何时何地接收什么发布数据。
There are two types of subscriptions: push subscriptions and pull subscriptions
订阅有两种类型：推送订阅和请求订阅
- Push subscription: Distributor directly updates the data in the Subscriber database 推送订阅：分发服务器直接更新订阅服务器数据库中的数据
- Pull subscription: the Subscriber is scheduled to check at the Distributor regularly if any new changes are available, and then updates the data in the subscription database itself. 拉订阅：计划将订阅服务器定期检查分发服务器上是否有任何新更改，然后更新订阅数据库本身中的数据。
EXEC sp_helpsubscription;
订阅数据库 (Subscription databases )
A target database of a replication model is called a subscription database.
复制模型的目标数据库称为预订数据库。
复制代理 (Replication agents)
SQL Server replication uses a pre-defined set of standalone programs and events are known as agents, to carry out the tasks associated with data. By default, SQL Server replication agents run as scheduled jobs under SQL Server Agent. Replication agents can also be run from the command line and by applications that use Replication Management Objects (RMO). SQL Server replication agents can be monitored and administered using Replication Monitor and SQL Server Management Studio.
SQL Server复制使用一组预定义的独立程序和事件（称为代理）来执行与数据相关的任务。 默认情况下，SQL Server复制代理在SQL Server代理下按计划的作业运行。 复制代理也可以从命令行运行，也可以由使用复制管理对象（RMO）的应用程序运行。 可以使用Replication Monitor和SQL Server Management Studio监视和管理SQL Server复制代理。
复制快照代理 (Replication snapshot Agent)
The Replication snapshot Agent is used with all types of SQL Server replication technology as it provides the required data set to perform the initial data synchronization of the publication database with the subscription database. It prepares schema and initial data of published articles, snapshot files, and records information about the synchronization type in the distribution database.
复制快照代理与所有类型SQL Server复制技术一起使用，因为它提供了执行发布数据库与订阅数据库的初始数据同步所需的数据集。 它准备已发布文章，快照文件的架构和初始数据，并在分发数据库中记录有关同步类型的信息。
日志读取器代理 (Log Reader Agent)
The Log Reader Agent is used only with transactional replication. It moves replication transactions from the online transaction log of the publication database to the distribution database.
日志读取器代理仅与事务复制一起使用。 它将复制事务从发布数据库的联机事务日志移到分发数据库。
经销代理 (Distribution Agent)
The Distribution Agent is used only with Replication snapshot and Transactional SQL Server replication. This agent applies the initial replication snapshot to the subscription database and later, the data changes are tracked and recorded in the distribution database and applied to the subscription database.
分发代理仅与复制快照和事务性SQL Server复制一起使用。 该代理将初始复制快照应用于订阅数据库，随后，将对数据更改进行跟踪并将其记录在分发数据库中，并将其应用于订阅数据库。
合并代理 (Merge Agent)
The Merge Agent is used with the merge replication model. By default, the Merge Agent uploads changes from the Subscriber to the Publisher and then downloads changes from the Publisher to the Subscriber. Each subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions. Here, the synchronization is bi-directional. The data conflicts are handled by a set of triggers that supports the entire process
合并代理与合并复制模型一起使用。 默认情况下，合并代理将更改从订阅服务器上载到发布服务器，然后将更改从发布服务器上下载到订阅服务器。 每个订阅都有自己的合并代理，该代理同时连接到发布者和订阅者并同时更新两者。 合并代理在分发服务器上运行以进行推送订阅，在订阅服务器上运行，以进行推送订阅。 在此，同步是双向的。 数据冲突由一组支持整个过程的触发器处理
摘要 (Summary)
Thus far, we’ve seen a walk-through of some of the important concepts of SQL Server replication. Also, T-SQL scripts are shown to query system tables and replication stored procedures to answer most of the commonly asked question about SQL Server replication.
到目前为止，我们已经了解了SQL Server复制的一些重要概念。 此外，还显示了T-SQL脚本来查询系统表和复制存储过程，以回答有关SQL Server复制的大多数常见问题。
I’ll discuss more about SQL Server replication in upcoming articles. If you feel, something can be improved in this article, feel free to leave your comment below…
我将在以后的文章中讨论有关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/sql-server-replication-overview-of-components-and-topography/
扫一扫
889
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
