In most cases, an organization can either use the existing out-of-the-box database replication features offered by their database software provider or invest in custom solutions to execute and manage database replication processes. The latter option sometimes allows greater flexibility to create data replicas across multiple types on multiple platforms.

在大多数情况下，组织可以使用其数据库软件提供商提供的现成的现成数据库复制功能，也可以投资定制解决方案来执行和管理数据库复制过程。 后者选项有时可以提供更大的灵活性，以便在多个平台上跨多种类型创建数据副本。

In this guide, we are going to walk-through the steps to the setup of transactional replication between cross-platform SQL Servers instances. You heard it right; yes, we are going to setup a cross-platform transactional replication between Windows and Linux machines using a custom solution and an integrated SQL Server transaction log reader.

在本指南中，我们将逐步介绍跨平台SQL Server实例之间的事务复制的设置步骤。 您没听错； 是的，我们将使用自定义解决方案和集成SQL Server事务日志读取器在Windows和Linux计算机之间设置跨平台事务复制。

目的 (Objective)

As you may know, at the time of writing, replication features are still an unsupported feature and services in . So, at the moment, replication is not supported on Linux. However, according to the release notes, replication support will be added in a future release. We aren’t going to wait though, as we’re going to create a Linux based replication system for SQL Server now.

如您所知，在撰写本文时，复制功能仍然是不受支持的功能和服务。 因此，目前，Linux不支持复制。 但是，根据发行说明，复制支持将在以后的发行版中添加。 我们不会等待，因为我们现在将为SQL Server创建基于Linux的复制系统。

We’ll walk-through the steps to setup a cross-platform transactional replication. Let us discuss the following topics in detail:

我们将逐步完成设置跨平台事务复制的步骤。 让我们详细讨论以下主题：

• Defining Replication
  定义复制
• High-level overview of the cross-platform transactional replication setup
  跨平台事务复制设置的高级概述
• Describe how to use a transaction log reader to generate CLI commands—generate replay SQL transaction script
  描述如何使用事务日志读取器生成CLI命令-生成重播SQL事务脚本
• Automate with Batch and PowerShell scripts
  自动化批处理和PowerShell脚本
• Discuss various job schedule options
  讨论各种工作时间表选项
• Replay the transactions at the target database (Subscriber)
  在目标数据库（订户）上重播事务
• Validate the data between the Publisher and Subscriber
  验证发布者和订阅者之间的数据
• And more…
  和更多…

复写 (Replication )

Database replication is the process of copying of data from a publisher database from one server to a subscriber database in another so that the data at the subscriber can be transformed at various levels by using the same level of information at the publisher.

数据库复制是将数据从发布者数据库从一台服务器复制到另一台服务器中的订户数据库的过程，以便可以通过使用发布者上相同级别的信息在不同级别上转换订户上的数据。

In a distributed replication setup, the data is accessed from various data sources or data is fed to various data sources without interfering with the work of one other. In all cases, data replication design is a balancing act between managing system workload, performance, consistency, and integrity. With database replication, the focus is mainly going to be on the database scale out for transactional queries.

So far, we’ve discussed a lot about replication and its internals. There are numerous factors that contribute to the overall configuration of creating and managing database replication. You can refer to the previous article

在分布式复制设置中，可以从各种数据源访问数据或将数据馈送到各种数据源，而不会互相干扰。 在所有情况下，数据复制设计都是在管理系统工作负载，性能，一致性和完整性之间取得平衡的行为。 使用数据库复制时，重点将主要放在针对事务查询的数据库扩展上。

到目前为止，我们已经讨论了很多有关复制及其内部的内容。 有许多因素会影响创建和管理数据库复制的整体配置。 您可以参考上一篇文章 to know more about replication and its internals. 以了解有关复制及其内部的更多信息。

实作 (Implementation)

In this setup, the publisher database is on the SQL Server 2016 on Windows and the subscriber is a SQL Server 2017 on Linux host. The Linux system receives the initial copy using a traditional or using and then periodic updates as the data changes at the source.

在此设置中，发布者数据库位于Windows上SQL Server 2016上，订阅者为Linux主机上SQL Server 2017。 Linux系统使用传统的或使用接收初始副本，然后随着源中数据的更改而进行定期更新。

技术 (Technology)

For this configuration, we’ll use ApexSQL Log as our of choice, as the lynchpin of our custom replication system

对于此配置，我们将使用ApexSQL Log作为我们选择的 ，作为我们自定义复制系统的关键

建立 (Setup)

Let’s not take a deep-dive into the step by step details to setup a cross-platform transactional replication.

让我们不深入研究设置跨平台事务复制的详细步骤。

1. Let us make sure that the Publisher database is in Full recovery mode
   让我们确保发布者数据库处于完全恢复模式
2. Start ApexSQL Log
   启动ApexSQL日志
3. New 新建
4. In the Select Database, type in the SQL Server instance and database details
   在“选择数据库”中，键入SQL Server实例和数据库详细信息

5. Click Next

   点击下一步

6. In the Select data sources, by default, the online transaction log is enabled. Leave the default setting and Click Next.

   默认情况下，在“ 选择数据源”中 ，启用了在线事务日志。 保留默认设置，然后单击下一步 。

7. Next, create redo script by selecting Undo/Redo option.

   接下来，通过选择撤消/重做选项来创建重做脚本。

8. Now, enable continuous auditing feature. This option maintains transactional integrity by internally remembering the LSN values.

   现在，启用连续审核功能。 此选项通过内部记忆LSN值来维护事务完整性。

9. We are at the final step. We’ve everything setup and configured. Click Save

   我们处于最后一步。 我们已完成所有设置和配置。 点击保存

10. We’ll save as replication.ps1

    我们将另存为Replication.ps1

11. Edit the replication.ps1 file

    编辑Replication.ps1文件

    “C:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com” /server:HQDBT01 /database:AdventureWorks2016 /trusted /redo:c:\replication\redo.sql /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous:C:\Replication\AdventureWorks2016.axtr

    “ C：\ Program Files \ ApexSQL \ ApexSQL Log \ ApexSQLLog.com” / server：HQDBT01 / database：AdventureWorks2016 / trusted /redo:c:\replication\redo.sql / operations：DMLALL / transactions：COMMIT BEGIN UNKNOWN / continuous： C：\ Replication \ AdventureWorks2016.axtr

12. 1. • Path of the executable file

         可执行文件的路径

         “C:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com”

         “ C：\ Program Files \ ApexSQL \ ApexSQL Log \ ApexSQLLog.com”

       • Argument details

         参数细节

         /server:HQDBT01 /database:AdventureWorks2016 /trusted /redo:c:\replication\redo.sql /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous:C:\Replication\AdventureWorks2016.axtr

         / server：HQDBT01 / database：AdventureWorks2016 / trusted /redo:c:\replication\redo.sql / operations：DMLALL / transactions：COMMIT BEGIN UNKNOWN /continuous:C:\Replication\AdventureWorks2016.axtr

    2. Let us assign the arguments to a variable named $args

       让我们将参数分配给名为$ args的变量

       $args = '/server:HQDBT01 /database:AdventureWorks2016 /trusted /redo:$filepath /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous:C:\Replication\AdventureWorks2016.axtr'

    3. As we are generating the replay SQL file with a timestamp value, the parameters $filepath is prepared to accept date and timestamp.
       当我们生成带有时间戳值的重放SQL文件时，参数$ filepath准备接受日期和时间戳。
    4. The variable $filepath is also passed as a string in the $args.
       变量$ filepath也作为字符串传递到$ args中。

    5. Call the Invoke-Expression. The call operator is also known as the “invocation operator”, this allows us to run commands that are stored in variables and represented by strings.

       调用Invoke-Expression。 调用运算符也称为“调用运算符”，它使我们能够运行存储在变量中并由字符串表示的命令。

       "&'C:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com' $args" | Invoke-Expression
13. replication.ps1Replication.ps1
    1. This step has load SQL Server module
       此步骤已加载SQL Server模块
    2. Execute the ApexSQL CLI commands using Invoke-Expression (Invocation operator)
       使用Invoke-Expression（调用运算符）执行ApexSQL CLI命令
    3. Invoke-SQLCMDInvoke-SQLCMD

    4. Execute the replay SQL file on the subscription database using Invoke-SQLCMD

       使用Invoke-SQLCMD在订阅数据库上执行重播SQL文件

       Import-Module SQLServer $datetime = (get-date).ToString("yyyyMMdd-HHmmss") $filepath = "c:\replication\redo_$datetime.sql" $args = '/server:HQDBT01 /database:AdventureWorks2016 /trusted /redo:$filepath /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous:C:\Replication\AdventureWorks2016.axtr'"&'C:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com' $args" | Invoke-Expression Invoke-Sqlcmd -ServerInstance 10.2.6.30 -Username sa -Password thanVitha@2015 -Database "AdventureWorks2016Report" -InputFile $filepath

       The fully converted batch command to replication.ps1 is shown below

       完全转换为batch.ps1的批处理命令如下所示

14. Execute the PowerShell script

    执行PowerShell脚本

15. We can see that the redo_<Datetimestamp>.SQL files are generated as we change the data at the source.

    我们可以看到在更改源数据时会生成redo_ <Datetimestamp> .SQL文件。

    

17. Validate the data between publisher and Subscriber. In the following T-SQL, replace the <Source> and < LinuxTarget> instances to validate the data.

    验证发布者和订阅者之间的数据。 在以下T-SQL中，替换<Source>和<LinuxTarget>实例以验证数据。

    :CONNECT GOUSE  AdventureWorks2016GOSELECT @@SERVERNAME ServerNameSELECT GETDATE() [Datetime]SELECT TOP (1000) [C_ID]      ,[C_Name]      ,[C_StateProvinceID]      ,[C_Location]      ,[C_LatestRecordedPopulation]      ,[C_LastEditedBy]FROM dbo.Cities  GO :CONNECT 
          
           GOUSE  AdventureWorks2016ReportGOSELECT @@SERVERNAME ServerNameSELECT GETDATE() [Datetime]SELECT TOP (1000) [C_ID]      ,[C_Name]      ,[C_StateProvinceID]      ,[C_Location]      ,[C_LatestRecordedPopulation]      ,[C_LastEditedBy]FROM dbo.Cities
          

结语 (Wrap Up)

In this article, we looked at a method to implement cross-platform transactional replication with a production database (publisher), a SQL Server instance on a Windows machine. to a target database (subscriber), a SQL Server instance on Linux machine.

在本文中，我们研究了一种使用生产数据库（发布者），Windows计算机上SQL Server实例来实现跨平台事务复制的方法。 到目标数据库（订户），即Linux计算机上SQL Server实例。

It is very simple to setup and configure transactional replication. If you’re thinking of an out-of-the-box solution using 3^rd party tools then ApexSQL Log is a very good option for cross-platform replication. Give a try and let me know what you think in comments…

设置和配置事务复制非常简单。 如果您使用^第三方工具，一个彻头彻尾的现成解决方案的思维然后ApexSQL Log是跨平台的复制一个非常好的选择。 尝试一下，让我知道您在评论中的想法…

目录 (Table of contents)

How to setup cross-platform transactional SQL Server replication for database reporting on Linux

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

如何在Linux上为数据库报告设置跨平台事务SQL Server复制

SQL Server事务复制：如何使用“仅复制支持” –TBA重新初始化订阅

使用PowerShell –TBASQL Server复制监视和设置警报

翻译自: