如何为报表服务器设置SQL Server数据库复制

最新推荐文章于 2023-12-25 00:32:20 发布

culuo4781

最新推荐文章于 2023-12-25 00:32:20 发布

阅读量183

点赞数

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

原文链接：https://www.sqlshack.com/how-to-setup-sql-server-database-replication-for-a-reporting-server/

版权

In this article, we’ll discuss the purpose of database replication and show how you can implement Replication using ApexSQL Log, a SQL Server transaction log reader.

在本文中，我们将讨论数据库复制的目的，并展示如何使用ApexSQL Log（SQL Server事务日志读取器）实现复制。

复写 (Replication)

So what is SQL Server database replication? Well, essentially, it’s a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between those databases to help maintain consistency of the data. Using replication, you can distribute data to different locations and even to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, or even the internet.

那么什么是SQL Server数据库复制？好吧，从本质上讲，它是一套技术，用于将数据和数据库对象从一个数据库复制和分发到另一个数据库，然后在这些数据库之间进行同步以帮助保持数据的一致性。使用复制，您可以将数据分布到不同的位置，甚至可以通过局域网和广域网，拨号连接，无线连接甚至Internet分布到远程或移动用户。

复制的缺点 (Replication disadvantages)

SQL Server replication as its devotees but it also can be challenging to set up and configure. There can be issues with replicating across different versions of SQL Server and other “pain points”. If you are looking for an out-of-the-box solution, that can be set up quickly but yet easily customized, SQL Server replication might not be the best choice.

SQL Server复制是其专门技术，但设置和配置也可能具有挑战性。在不同版本SQL Server和其他“痛点”之间进行复制可能会出现问题。如果您正在寻找一种现成的解决方案，该解决方案可以快速设置但可以轻松自定义，则SQL Server复制可能不是最佳选择。

替代 (An alternative)

In this article, we are going to propose an alternative technology, ApexSQL Log

在本文中，我们将提出一种替代技术ApexSQL Log

ApexSQL Log is a 3^rd party SQL Server transaction-log reader, designed to read online transaction logs; detached transaction logs; transaction log backups for auditing, recovery, production load simulations and in our case replication. We’ll be using ApexSQL Log as our replication agent/technology

ApexSQL日志是一个^第三方SQL Server事务日志阅读器，专为阅读网上交易日志; 分离的事务日志；事务日志备份，用于审计，恢复，生产负载模拟以及本例中的复制。我们将使用ApexSQL Log作为复制代理/技术

用例 (Use case)

For our use case, we are going to do one-way replication from a production “Publisher” database to a reporting “Subscriber” database. Our goal will be to offload reporting queries to another server, to reduce load on production and enhance reporting performance (via less general workload, re-indexing for selects, potentially closer to clients etc)

对于我们的用例，我们将从生产“发布者”数据库到报告“订阅者”数据库进行单向复制。我们的目标是将报告查询卸载到另一台服务器上，以减轻生产负荷并提高报告性能（通过减少一般工作量，为选择重新索引，可能更接近客户端等）

技术要求 (Technical requirements)

For this use case we can afford latency of up to 15 minutes between production and reporting systems

对于此用例，我们可以承受生产和报告系统之间长达15分钟的延迟

复制任务概述 (Replication task overview)

Once we have initially set up our subscriber environment, we’ll be creating our replication task, automating and scheduling it

最初设置订户环境后，我们将创建复制任务，并对其进行自动化和调度

Here are the steps to follow:

以下是要遵循的步骤：

Using ApexSQL Log, create a redo.sql script; the redo.sql file is a collection of replay transactions
使用ApexSQL Log创建redo.sql脚本； redo.sql文件是重播事务的集合
replication.bat. Replication.bat 。
Schedule a job to run the batch file on an a scheduled interval to create a SQL script and execute the script on the Subscriber
安排作业以预定的时间间隔运行批处理文件，以创建SQL脚本并在订阅服务器上执行脚本

复制任务 (Replication task)

Let’s begin …

让我们开始 …

Start ApexSQL Log
启动ApexSQL日志
New 新建
Select Database, enter the SQL Server instance and select the source database 选择数据库”中 ，输入SQL Server实例并选择源数据库。
Click Next

点击下一步
Select data sources step, by default, the online transaction log will be selected. 选择数据源”步骤中，将选择在线事务日志。
Click Next

点击下一步

Next, Select output, select Undo/Redo, this will create redo SQL script

接下来， 选择输出 ，选择撤消/重做 ，这将创建重做SQL脚本
In Filter setup, select Continuous auditing. This will ensure that reading the transaction log is seamless as it will remember the LSN of the last transaction enduring no gaps of missing data nor any duplication of overlapping/redundant data.

在“ 筛选器设置”中 ，选择“ 连续审核” 。这将确保读取事务日志是无缝的，因为它将记住上一个事务的LSN，从而不会丢失数据的间隙，也不会重复重叠/冗余的数据。
In the Batch script, enable Overwrite existing files and click Copy, to copy the script, and click Cancel
在“ 批处理”脚本中 ，启用“ 覆盖现有文件” ，然后单击“ 复制 ”以复制脚本，然后单击“ 取消”。
Now, paste the content in the batch file, Replication.bat.

现在，将内容粘贴到批处理文件Replication.bat中。

自动化 (Automation)

Here is the view of our batch script

这是我们的批处理脚本的视图
```
SET "redofile=%1"
"E:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com" 
/server:HQDBT01 /database:WideWorldImporters /trusted 
/redo:%redofile% /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN 
/continuous:G:\DBA\ApexSQLLog\WideWorldImporters.axtr
```
Although we could do this all in Windows Shell we will create a small wrapper PowerShell script to run our batch file and perform a few other simple tasks

尽管我们可以在Windows Shell中完成所有操作，但是我们将创建一个小的包装PowerShell脚本来运行批处理文件并执行其他一些简单任务

Prepare the PowerShell script involves the following steps:

准备PowerShell脚本涉及以下步骤：
1. Load the SQL Server module
  加载SQL Server模块
2. Build filename in the format <path>\redo_yyyyMMdd-HHmmss followed by sql extension
  构建文件名，格式为<path> \ redo_yyyyMMdd-HHmmss，后跟sql扩展名
3. Execute the batch file which in turn invokes the ApexSQL Log executable.
  执行批处理文件，该文件又调用ApexSQL Log可执行文件。
4. Run the SQL file on the subscriber database using Invoke_SQLCMD
  使用Invoke_SQLCMD在订户数据库上运行SQL文件
```
#Load the SQL Server Module
Import-module sqlserver
#Assign the date time value in the format yyyyMMdd-HHmmss to $datetime variable
$datetime = (get-date).ToString("yyyyMMdd-HHmmss")
 
#Build full filename 
 
$filepath = "g:\dba\replication\redo_$datetime.sql"
 
#run the batch file
 
cmd /c "G:\DBA\Replication\replication.bat" $filepath
 
#Check the file path
If(Test-Path -Path $filepath)
{
    #replay the generated redo.sql on the subscriber database
    Invoke-SQLcmd -inputfile $filepath -serverinstance "hqdbt01\sql2017" -database "WorldWideImporters"
}
```
Save the PowerShell script as a ApexAutomation.ps1
将PowerShell脚本另存为ApexAutomation.ps1

安排工作 (Scheduling the job)

Now that we’ve built our automation layer, it is time to schedule it

现在我们已经构建了自动化层，现在是时候对其进行调度了

To create a job in SQL Server Agent, follow the below steps:

要在SQL Server代理中创建作业，请执行以下步骤：

Navigate Jobs under SQL Server Agent. Right-click Jobs and select New. Type in the job name, Replication

在SQL Server代理下导航作业。右键单击作业，然后选择新建。输入作业名称“ 复制”
Next, Select the Steps, click New and type in the step name, Replication
接下来，选择“ 步骤” ，单击“ 新建 ” ，然后输入步骤名称“ 复制”。
PowerShell PowerShell
At the Command, type in the path(G:\DBA\Replication\ApexAutomation.ps1) of the PowerShell script

在“ 命令”处 ，键入PowerShell脚本的路径（G：\ DBA \ Replication \ ApexAutomation.ps1）
Now, go to Schedules and click New, Select Frequency “Daily” and ”Occurs every 15 minutes”

现在，转到“ 时间表” ，然后单击“新建”，然后选择“每天”和“每15分钟发生一次”
The SQL Job is created successfully.
SQL作业已成功创建。
On the source database, perform few DML operation and run the job

在源数据库上，执行一些DML操作并运行作业

评论 (Review)

You can test this on WorldWideImporters but running some sample inserts and updates. Then manually executing the batch script and comparing the data in the two tables with a tool like ApexSQL Data Diff and verifying that they are the same

您可以在WorldWideImporters上进行测试，但可以运行一些示例插入和更新。然后手动执行批处理脚本，并使用ApexSQL Data Diff之类的工具将两个表中的数据进行比较，并验证它们是否相同

结语 (Wrapping Up)

This article is an effort to show how easy it is to set up Replication using ApexSQL Log. It’s also an effective way to transfer data from a source to a destination. If you’re working on busy OLTP systems with heavy workload, then you can run the job as frequently as every 5 minutes or less.

本文旨在说明使用ApexSQL Log设置复制的难易程度。这也是将数据从源传输到目标的有效方法。如果您正在繁忙的OLTP系统上处理繁重的工作负载，则可以每5分钟或更短的时间运行一次作业。

If you are considering replication, but don’t have a version/edition of SQL Server that supports it or would prefer an easier, out-of-the-box solution with no coding, except for a couple lines of PowerShell, you should consider ApexSQL Log for replication, as well as continuous auditing, forensic auditing, database recovery and even to simulate production loads in testing.

如果您正在考虑复制，但是没有支持该复制SQL Server版本/版本，或者希望使用一种简单，无需编码的现成解决方案（除了几行PowerShell），则应考虑ApexSQL Log用于复制，以及连续审核，取证审核，数据库恢复，甚至可以模拟测试中的生产负载。