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

最新推荐文章于 2024-11-01 12:20:35 发布

culuo4781

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量179

点赞数

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

原文链接：https://www.sqlshack.com/how-to-setup-cross-platform-transactional-sql-server-replication-for-database-reporting-on-linux/

版权

本文详细介绍了一种使用ApexSQL Log工具实现跨平台SQL Server事务复制的方法，包括在Windows和Linux机器间设置复制流程的步骤。文章涵盖了从定义复制概念到实际操作的全过程，包括事务日志读取、自动化脚本、工作调度以及数据验证。

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

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 SQL Server 2017 on Linux. 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 2017中不受支持的功能和服务。因此，目前，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

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

到目前为止，我们已经讨论了很多有关复制及其内部的内容。有许多因素会影响创建和管理数据库复制的整体配置。您可以参考上一篇文章 How to setup SQL Server database replication for a reporting server to know more about replication and its internals. 如何为报表服务器设置SQL Server数据库复制，以了解有关复制及其内部的更多信息。

实作 (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 backup and restores method or using sqlpackage.exe and then periodic updates as the data changes at the source.

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

技术 (Technology)

For this configuration, we’ll use ApexSQL Log as our SQL Server transaction log reader of choice, as the lynchpin of our custom replication system

对于此配置，我们将使用ApexSQL Log作为我们选择的SQL Server事务日志读取器，作为我们自定义复制系统的关键

建立 (Setup)

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

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

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

点击下一步
In the Select data sources, by default, the online transaction log is enabled. Leave the default setting and Click Next.

默认情况下，在“ 选择数据源”中 ，启用了在线事务日志。保留默认设置，然后单击下一步 。
Next, create redo script by selecting Undo/Redo option.

接下来，通过选择撤消/重做选项来创建重做脚本。
Now, enable continuous auditing feature. This option maintains transactional integrity by internally remembering the LSN values.

现在，启用连续审核功能。此选项通过内部记忆LSN值来维护事务完整性。
We are at the final step. We’ve everything setup and configured. Click Save

我们处于最后一步。我们已完成所有设置和配置。点击保存
We’ll save as replication.ps1

我们将另存为Replication.ps1
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
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
```

replication.ps1Replication.ps1

This step has load SQL Server module
此步骤已加载SQL Server模块
Execute the ApexSQL CLI commands using Invoke-Expression (Invocation operator)
使用Invoke-Expression（调用运算符）执行ApexSQL CLI命令
Invoke-SQLCMDInvoke-SQLCMD

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的批处理命令如下所示

Execute the PowerShell script

执行PowerShell脚本
We can see that the redo_<Datetimestamp>.SQL files are generated as we change the data at the source.

我们可以看到在更改源数据时会生成redo_ <Datetimestamp> .SQL文件。
How to setup SQL Server database replication for a reporting server 如何为报告服务器设置SQL Server数据库复制

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 <source>
GO
USE  AdventureWorks2016
GO
SELECT @@SERVERNAME ServerName
SELECT GETDATE() [Datetime]
SELECT TOP (1000) [C_ID]
      ,[C_Name]
      ,[C_StateProvinceID]
      ,[C_Location]
      ,[C_LatestRecordedPopulation]
      ,[C_LastEditedBy]
FROM dbo.Cities
 
 
GO
 
:CONNECT <LinuxTarget>
GO
USE  AdventureWorks2016Report
GO
SELECT @@SERVERNAME ServerName
SELECT 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是跨平台的复制一个非常好的选择。尝试一下，让我知道您在评论中的想法…