本文共 12072 字,大约阅读时间需要 40 分钟。
sql数据库 订阅发布
In this article, you’ll learn how to setup a simple, custom distributed database replication system.
在本文中,您将学习如何设置简单的自定义分布式数据库复制系统。
In general, a typical setup of transactional replication model of a central publisher with multiple subscribers includes the creation of a replica database(s) which may serve multiple purposes including:
通常,具有多个订户的中央发布者的事务复制模型的典型设置包括创建副本数据库,该副本数据库可以用于多种目的,包括:
In this system, it is important to understand and consider the scope of the objects that need to be replicated across databases and servers. Replication allows to you choose particular tables, or even subsets of tables, to be replicated to other databases or you can replicate entire databases
在此系统中,重要的是理解并考虑需要在数据库和服务器之间复制的对象的范围。 复制允许您选择要复制到其他数据库的特定表,甚至表的子集,也可以复制整个数据库
I have been in a process to setup a mission-critical replication setup many times. It is very difficult to setup 100% synchronous data propagation between publisher and subscriber. In this article, I’ll explore some alternate approaches and technologies to create a custom replication solution, independent of traditional SQL Server replication
我已经在多次设置关键任务复制设置的过程中。 在发布者和订阅者之间设置100%同步数据传播非常困难。 在本文中,我将探索一些独立于传统SQL Server复制的替代方法和技术,以创建自定义复制解决方案。
One Publisher can have multiple Subscribers, and in many cases a Publisher can also act as a Distributor. This replication model is referred to as central publisher-multiple subscribers. In this case, you’ve got one publisher server publishing data that is sent to several subscriber servers. The publisher server the central piece in a process that distributes data into multiple subscriber servers. The publisher server partitions the data and only sends out data that is relevant to the subscribing servers. The publishing server transmits the transactions to subscribing servers based on some set of conditions. For example, sales data at the regional level or data for specific manufacturing plants or data update for specific insurance policy etc. In any case, the users log onto the local subscriber server and retrieve the data set that is relevant to their respective job and responsibilities.
一个发布者可以具有多个订阅者,并且在许多情况下,发布者还可以充当分发者。 此复制模型称为“ 中央发布者-多个订阅者”。 在这种情况下,您需要一个发布服务器来发布发送到多个订阅服务器的数据。 在将数据分发到多个订户服务器的过程中,发布服务器是中心部分。 发布服务器对数据进行分区,仅发送与订阅服务器相关的数据。 发布服务器基于一组条件将事务传输到订阅服务器。 例如,区域级别的销售数据或特定制造工厂的数据或特定保险单的数据更新等。在任何情况下,用户登录到本地订户服务器并检索与其各自工作和职责相关的数据集。
Let’s take a brief look at the overall schematic of this system. It shows a simple layout of a single publisher database, where the transactions are read and distributed to four different subscriber databases.
让我们简要看一下该系统的整体原理图。 它显示了单个发布者数据库的简单布局,其中读取了事务并将其分发到四个不同的订户数据库。
In general, this model is used when you want to setup isolated read-only subscriber databases. Let us discuss the setup in detail:
通常,当您要设置隔离的只读订户数据库时使用此模型。 让我们详细讨论设置:
For this custom SQL Server database replication system, we are going to use a ApexSQL Log as our agent and a combination of Batch files, PowerShell scrips and SQL Agent jobs
对于此自定义SQL Server数据库复制系统,我们将使用ApexSQL日志作为代理,以及批处理文件,PowerShell脚本和SQL代理作业的组合
Let us discuss the setup further in detail to understand the data availability scenarios.
让我们进一步详细讨论设置,以了解数据可用性方案。
Let us walk through the entire process using Adventureworks 2016 database.
让我们使用Adventureworks 2016数据库逐步完成整个过程。
Query the publisher and subscriber database. You noticed that there is a difference in the number of rows
查询发布者和订阅者数据库。 您注意到行数有所不同
Type in Server and Database details to connect to the Publisher database, AdventureWorks2016
输入服务器和数据库详细信息以连接到Publisher数据库AdventureWorks2016
Configure ApexSQL Log to use the default online log setup to ensure the transaction availability for the defined interval
配置ApexSQL日志以使用默认的联机日志设置来确保在定义的间隔内事务可用性
Now, create Undo/Redo script
现在,创建撤消/重做脚本
Next, choose continuous auditing. This maintains the LSN (Log Sequence Number) value. The configuration ensures transactions integrity.
接下来,选择持续审核 。 这将保持LSN(日志序列号)值。 该配置可确保事务完整性。
Let’s setup a filter. In this case, the table dbo.cities is included. In this step, transactions are audited at the granular level. In this way, we can opt for tables to be included and/or excluded from the setup.
让我们设置一个过滤器。 在这种情况下,表dbo 。 包括城市 。 在此步骤中,将在粒度级别上审核事务。 这样,我们可以选择将表包含在设置中和/或从设置中排除。
In the Advanced options, Select the Filed Values to setup a filter on the selected table. Browse the selected table (Cities) to apply the condition clause.
在“ 高级”选项中 ,选择“ 归档值”以在所选表上设置过滤器。 浏览选定的表( 城市 )以应用条件子句。
Select the operator, Field, Comparison operator and define the value. In this case, the data segregation or subset of data is derived for “US East”.
选择运营商 , 现场 , 比较运营商和定义值。 在这种情况下,将为“美国东部”导出数据隔离或数据子集。
You can see that the condition is placed in the filter condition column which is shown below
您可以看到该条件位于过滤条件列中,如下所示
The configuration is done in the way we want it, let’s save the ApexSQL Log CLI commands in the ‘Batch file’. Save the command replication.BAT file.
按照我们想要的方式完成配置,让我们将ApexSQL Log CLI命令保存在“批处理文件”中。 保存命令replication.BAT文件。
Click finish to run the solution. You can see that 160 insert statements are generated in this process. This concludes that the setup and filter are working in the way we want it.
单击完成以运行解决方案。 您可以看到在此过程中生成了160个insert语句。 这可以得出结论,设置和过滤器可以按照我们想要的方式工作。
Run through the same steps again to generate another subset from the Cities table using a condition clause. In this case, the data is segregated based on the “US West” region.
再次执行相同的步骤,以使用条件子句从“城市”表中生成另一个子集。 在这种情况下,将根据“美国西部”区域对数据进行隔离。
Now you can see that 110 insert statements are generated in this process.
现在您可以看到在此过程中生成了110条插入语句。
Now, let’s take a look at the bath file. You can see that both the condition clauses are in the same batch file. It is very easy to add additional clauses with very few lines of code.
现在,让我们看一下浴文件。 您可以看到两个条件子句都在同一批处理文件中。 用很少的代码行添加其他子句非常容易。
@echo off
@回声关闭
SET “filename=%1”
设置“文件名=%1”
“E:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com” /server:HQDBT01 /database:AdventureWorks2016 /trusted /redo:G:\BKP\Subscriber1_%filename%.sql /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous:g:\bkp\AWPublisher.axtr /tables:[dbo].[Cities] /fields:” ([Cities].[C_Region] = ‘US East’ )”
“ E:\ Program Files \ ApexSQL \ ApexSQL Log \ ApexSQLLog.com” / server:HQDBT01 / database:AdventureWorks2016 / trusted /redo:G:\BKP\Subscriber1_%filename%.sql / operations:DMLALL / transactions:COMMIT BEGIN UNKNOWN /continuous:g:\bkp\AWPublisher.axtr /tables:[dbo].[Cities] / fields:” ([[Cities]。[C_Region] ='US East')”
“E:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com” /server:HQDBT01 /database:AdventureWorks2016 /trusted /redo:G:\BKP\Subscriber2_%filename%.sql /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous:g:\bkp\AWPublisher.axtr /tables:[dbo].[Cities] /fields:” ([Cities].[C_Region] = ‘US West’ )”
“ E:\ Program Files \ ApexSQL \ ApexSQL Log \ ApexSQLLog.com” / server:HQDBT01 / database:AdventureWorks2016 / trusted /redo:G:\BKP\Subscriber2_%filename%.sql / operations:DMLALL / transactions:COMMIT BEGIN UNKNOWN /continuous:g:\bkp\AWPublisher.axtr /tables:[dbo].[Cities] / fields:” ([[Cities]。[C_Region] ='US West')”
Prepare the PowerShell script. Save the below content in Replication.ps1 and run the script
准备PowerShell脚本。 将以下内容保存在Replication.ps1中并运行脚本
#Load the SQL Server Moduleimport-Module "sqlserver" #Assign the date time value in the format yyyyMMdd-HHmmss to $datetime variable$datetime = (get-date).ToString("yyyyMMdd-HHmmss") #Invoke Replication.bat cmd /c "G:\replication\publisher.bat" $datetime #Check the file pathIf(Test-Path -Path "G:\BKP\Subscriber1_$datetime.sql") { #replay the generated redo.sql on the subscriber database Invoke-SQLcmd -inputfile "G:\BKP\Subscriber1_$datetime.sql" -serverinstance "hqdbt01" -database "AW2016Subscriber1" } #Check the file pathIf(Test-Path -Path "G:\BKP\Subscriber2_$datetime.sql") { #replay the generated redo.sql on the subscriber database Invoke-SQLcmd -inputfile "G:\BKP\Subscriber2_$datetime.sql" -serverinstance "hqdbt01" -database "AW2016Subscriber2" }
We can see that files are generated with the unique filename corresponding to the subscriber database.
我们可以看到生成的文件具有与订户数据库相对应的唯一文件名。
Test the data
测试数据
We can see that they are now equal numbers of rows have been replicated to the Subscriber databases.
我们可以看到,现在已经将相同数量的行复制到了订户数据库。
Schedule job
安排工作
To automate, schedule the PowerShell script to run at any interval you want e.g. 5 minutes. For more information on scheduling, in this type of scenario, you can refer to the scheduling section in this article
要实现自动化,请安排PowerShell脚本以您希望的任何间隔(例如5分钟)运行。 有关调度的更多信息,在这种情况下,您可以参考本文中的调度部分
If you’re thinking of setting up scale-out, distributed replication model, evaluate the environment and make sure there is room for acceptable latency for subscriber access. In real-time, it is near impossible to replicate synchronous data propagation. In this article, we reviewed how to setup a custom transactional replication system with a central publisher and multiple subscriber model. In the article we reviewed the multiple subscriber use-case and then designed and built the system using a 3rd party SQL Server transaction log reader and common scripting technology e.g. PowerShell. If you feel like giving it a try, feel free to let me know how it went on the comments below
如果您正在考虑建立横向扩展,分布式复制模型,请评估环境并确保有足够的空间供用户访问可接受的延迟。 实时地,几乎不可能复制同步数据传播。 在本文中,我们回顾了如何设置具有中央发布者和多个订户模型的自定义事务复制系统。 在文章中,我们回顾了多个用户的使用情况,然后设计和建造使用第三方 SQL Server事务日志阅读器和通用脚本技术,如PowerShell中的系统。 如果您想尝试一下,请随时在下面的评论中告诉我如何进行
How to setup custom SQL Server transactional replication with a central publisher and multiple subscriber databases |
SQL Server transactional replication: How to reinitialize a subscription using a “Replication support only” –TBA |
SQL Server Replication Monitoring and setting alerts using PowerShell –TBA |
如何使用中央发布者和多个订阅者数据库设置自定义SQL Server事务复制 |
SQL Server事务复制:如何使用“仅复制支持” –TBA重新初始化订阅 |
使用PowerShell –TBASQL Server复制监视和设置警报 |
翻译自:
sql数据库 订阅发布
转载地址:http://gwnwd.baihongyu.com/