sql dml ddl
In the article How to setup SQL Server database replication for reporting Server we discussed all about setting up a simple SQL Server transactional replication environment. In this article, we’ll simulate a more robust system that replicates faster and includes both DDL and DML statements. We’ll walk-through the steps to setup a dynamic transactional replication.
在如何为报表服务器设置SQL Server数据库复制一文中，我们讨论了有关设置简单SQL Server事务复制环境的所有内容。 在本文中，我们将模拟一个更健壮的系统，该系统可以更快地复制并同时包含DDL和DML语句。 我们将逐步介绍设置动态事务复制的步骤。
As we know, transactional replication propagates changes according to transaction boundaries and transactions from the publisher database can be replicated to the one or more subscription databases. There are many reasons why you want to look for alternatives from so-called “native” replication in SQL Server including:
众所周知，事务复制根据事务边界传播更改，并且可以将发布者数据库中的事务复制到一个或多个订阅数据库中。 您想从SQL Server中所谓的“本机”复制中寻找替代方法的原因很多，其中包括：
- Transactional replication requires a primary key which is mandatory to publish an article 事务复制需要主键，该主键是发布文章所必需的
- The snapshot agent acquires locks on articles during the initial stage of snapshot generation 快照代理在快照生成的初始阶段获取文章的锁
- In some cases, the size of the transaction-log affects the performance of the replication 在某些情况下，事务日志的大小会影响复制的性能
- Performing maintenance tasks can be “daunting” 执行维护任务可能“令人生畏”
- To add or drop an article from the publisher or subscriber is simple if the setup and configuration are simple. When you’ve got multiple publishers and/or subscribers, adding and dropping of articles becomes more difficult 如果设置和配置很简单，那么从发布者或订阅者添加或删除文章很简单。 当您有多个发布者和/或订阅者时，添加和删除文章变得更加困难
- Consider a scenario where the replication is setup using a backup, adding an article to replication is not a straight-forward approach. 考虑使用备份设置复制的情况，将文章添加到复制不是直接的方法。
备择方案 (Alternatives)
In this example, we are going to use some 3rd party tools for DDL and DML replication as an alternative to traditional replication. After creating the jobs to replicate the data and schema changes we’ll automate them with a combination of a batch file and PowerShell scripting and then schedule a job to run at every 30 seconds using a SQL Server Agent job. This can be done in two ways which we’ll cover in two parts of this article.
在这个例子中，我们将使用DDL和DML复制一些第三方工具，以替代传统的复制。 创建作业以复制数据和架构更改后，我们将结合使用批处理文件和PowerShell脚本将其自动化，然后使用SQL Server代理作业安排作业每30秒运行一次。 这可以通过两种方式完成，我们将在本文的两部分中介绍。
- Use a SQL Server transaction log reader to replicate both Schema (DDL) and Data (DML) changes 使用SQL Server事务日志读取器复制架构（DDL）和数据（DML）更改
- Use a SQL Server schema compare tool for Schema (DDL) changes and a SQL Server transaction log reader for Data (DML) changes 使用SQL Server模式比较工具进行模式（DDL）更改，并使用SQL Server事务日志读取器进行数据（DML）更改
使用SQL Server事务日志读取器 (Using a SQL Server transaction log reader)
In this section, we will walk-through the steps required to setup dynamic transactional replication using just a SQL Server transaction log reader for both DDL and DML transactions.
在本节中，我们将逐步介绍仅使用SQL Server事务日志读取器为DDL和DML事务设置动态事务复制所需的步骤。
In general, when adding new and/or changing existing table(s) in the source database in any replicated system, the changes must be synchronized to the Subscriber database.
通常，在任何复制系统中的源数据库中添加新表和/或更改现有表时，必须将更改同步到订户数据库。
For this task we’ll use ApexSQL Log, a SQL Server transaction log reader, to act as our DDL and DML replication agent. We’ll create a job to read the transaction log and write it to a script, then a batch file and a PowerShell script to automate the job. We will simulate synchronous data transfers between publisher and subscriber with latency as close to 30 seconds.
对于此任务，我们将使用SQL Server事务日志读取器 ApexSQL Log充当DDL和DML复制代理。 我们将创建一个作业来读取事务日志并将其写入脚本，然后创建一个批处理文件和一个PowerShell脚本以使作业自动化。 我们将模拟发布者和订阅者之间的同步数据传输，延迟接近30秒。
Here is a schematic view of our setup in which we’ll use a batch file and a PowerShell script, in combination, to poll a SQL Server, on a schedule and create a script to replay both DDL (schema) and DML (data) scripts that we’ll replay on the publisher database
这是设置的示意图，在该视图中，我们将结合使用批处理文件和PowerShell脚本，按计划轮询SQL Server，并创建脚本以重播DDL（模式）和DML（数据）我们将在发布者数据库上重播的脚本
读取发布者事务日志 (Reading the Publisher transaction log)
To begin, let’s set up ApexSQL Log to read the publisher database with the following steps.
首先，让我们通过以下步骤设置ApexSQL Log来读取发布者数据库。
- Open ApexSQL Log 打开ApexSQL日志
Click New and connect to the source database, WideWorldImporters
单击新建，然后连接到源数据库WideWorldImporters
Use the default configuration, ApexSQL Log to use the Online log
使用默认配置ApexSQL Log使用Online日志
Now, create Undo/Redo script
现在，创建撤消/重做脚本
Next, in the filter setup window, click Time range, select the Continuous auditing option; this will automatically manage the LSN sequences
接下来，在过滤器设置窗口中，点击时间范围 ，选择连续审核选项； 这将自动管理LSN序列
In the operations tab, on your right, in the DDL pane, Click Alter Table and Create Table and then click Next
在操作选项卡，在你的右边，在DDL窗格中，单击ALTER TABLE和CREATE TABLE以及c舔下一页
In this option, we are going configure filters such as Alter Table and Create Table. These two are the exact operations will be included to capture DDL changes in the auditing task
在此选项中，我们将配置过滤器，例如Alter Table和Create Table 。 这两个确切的操作将被包含以捕获审计任务中的DDL更改
自动化 (Automation)
Now that we’ve created a project to read the transaction log as per our specifications, let’s create a batch file to run this task unattended via the ApexSQL Log console application and command line interface (CLI).
现在，我们已经创建了一个项目来按照我们的规范读取事务日志，让我们创建一个批处理文件，以通过ApexSQL Log控制台应用程序和命令行界面（CLI）在无人看管的情况下运行此任务。
Let’s save the content into a batch (.BAT) file
让我们将内容保存到批处理（.BAT）文件中
- replication.bat Replication.bat
@echo off
SET “filename=%1”
“E:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com”
/server:HQDBT01 /database:WideWorldImporters /trusted
/redo:%filename%
/operations:DMLALL CREATETABLE ALTERTABLE /transactions:COMMIT BEGIN UNKNOWN
/continuous:G:\Replication\WideWorldImporters.axtr@回声关闭
设置“文件名=％1”
“ E：\ Program Files \ ApexSQL \ ApexSQL Log \ ApexSQLLog.com”
/ server：HQDBT01 / database：WideWorldImporters /受信任
/ redo ：％文件名％
/ operations：DMLALL CREATETABLE ALTERTABLE / transactions：COMMIT BEGIN UNKNOWN
/continuous:G:\Replication\WideWorldImporters.axtr
That’s the entire configuration we need to setup a highly enhanced transactional replication model for both DDL and DML changes/additions.
这就是我们为DDL和DML更改/添加设置高度增强的事务复制模型所需的整个配置。
As we plan to automate the schema and data export task, a PowerShell script is prepared. It helps to gather all the command in one place and it is possible to manipulate the way we want and use it more than once in its entirety.
当我们计划自动化架构和数据导出任务时，将准备一个PowerShell脚本。 它有助于将所有命令集中在一个地方，并且可以操纵我们想要的方式，并且可以多次使用它。
Now, save the content in a replication.PS1. The script tasks include the following four steps:
现在，将内容保存在Replication.PS1中 。 脚本任务包括以下四个步骤：
- Load SQL Server 加载SQL Server
- Invoke batch file replication.bat 调用批处理文件Replication.bat
- Generate a SQL file, replication_<*datetimestamp*>.sql 生成一个SQL文件复制_ <* datetimestamp *>。sql
- Replay the generated SQL file into the subscriber database 将生成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
$redofilepath = "G:\replication\replication_$datetime.sql"
#Invoke Replication.bat
cmd /c "G:\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 "WideWorldImporters"
}
排程 (Scheduling)
Now let us review the steps to create SQL Server Agent job that runs every 30 seconds to generate the data from the source and insert into the target using our PowerShell script. To do this, create the job steps through SSMS. Script the job and edit parameters.
现在，让我们回顾一下创建SQL Server代理作业的步骤，该作业每30秒运行一次，以从源生成数据并使用PowerShell脚本插入目标中。 为此，请通过SSMS创建作业步骤。 编写作业脚本并编辑参数。
Let us see the steps on how to schedule a job to run at every 30 seconds.
让我们看看如何安排作业每30秒运行一次的步骤。
- Jobs folder Jobs文件夹
- New 新建
- Name 名称
Uncheck the Enabled option
取消选中已启用选项
-
- Type in the Step name 输入步骤名称
- Select the PowerShell 选择PowerShell
In the command, enter the full path. If the path to the script contains a space, enclose the full path in quotation marks and prefix the entire thing with an ampersand (for example: &”G:\DBA\ApexSQL Log\Replication.ps1″)
在命令中，输入完整路径。 如果脚本的路径包含空格，则将完整路径括在引号中，并在整个内容前加上一个＆前缀（例如：＆“ G：\ DBA \ ApexSQL Log \ Replication.ps1”）
-
- Daily 每日
- the Daily frequency section, choose to run every 1 minutes( depends on your requirement) “每日频率”部分中，选择每1分钟运行一次（取决于您的要求）
- Ok 确定
Job is created
作业已创建
Right-click and generate the “Drop And Create To” script
右键单击并生成“ Drop and Create To ”脚本
In the generated script, search for schedule section, change the value of @freq_subday_type from ‘4’ to ‘2’ and @freq_subday_interval from ‘1’ to ‘30’ as shown below
在生成的脚本中，搜索“计划”部分，将@freq_subday_type的值从“ 4”更改为“ 2”，将@freq_subday_interval的值从“ 1”更改为“ 30”，如下所示
- Execute the script 执行脚本
Now, we can see that Daily frequency is scheduled to run at every 30 seconds
现在，我们可以看到“每日频率”计划每30秒运行一次
- Enable the job 启用工作
The replication job is all set and we see that the job ran at every 30 seconds
复制作业已全部设置，我们看到该作业每30秒运行一次
验证结果 (Verifying results)
Create a sample table at the source database and run the aforementioned SQL to validate the table and number of rows.
在源数据库中创建一个示例表，然后运行上述SQL来验证表和行数。
USE [WideWorldImporters]
GO
CREATE TABLE CitiesDemo(
C_ID int NOT NULL CONSTRAINT PK_CitiesDemo_ID PRIMARY KEY,
C_Name varchar(100) NULL,
C_StateProvinceID int NOT NULL,
C_Location geography NULL,
C_LatestRecordedPopulation bigint NULL,
C_LastEditedBy int NOT NULL)
Next, insert some sample data into the ExportCitiesDemo table and measure the output. The output errors out due to the absence of the table at the target.
接下来，将一些样本数据插入ExportCitiesDemo表中并测量输出。 由于目标表不存在，导致输出错误。
INSERT INTO Cities
SELECT top 10 CityID
,cityName
,StateProvinceID
,Location
,LatestRecordedPopulation
,LastEditedBy
FROM WideWorldImporters.Application.Cities
ORDER BY NEWID()
Test the data. You can see that the tables, as well as rows, are populated at the target.
测试数据。 您可以看到表和行都已填充到目标位置。
As you can see both the new table and all of the new data have been successfully replicated.
如您所见，新表和所有新数据均已成功复制。
结语 (Wrapping Up)
This article is an effort to show how easy it is to set up a fully enhanced version of SQL Server database transactional Replication using ApexSQL Log. The replication job is scheduled to run at every 30 seconds. The data propagation is tested between Publisher and Subscriber.
本文旨在说明使用ApexSQL Log设置完全增强版本SQL Server数据库事务复制的难易程度。 复制作业计划每30秒运行一次。 在发布者和订阅者之间测试了数据传播。
After working with SQL Server for such a long time, I feel it is very difficult to simulate a near-production database with the available SQL Server native techniques. If you’re thinking of out-of-the-box solution with no coding, except for a couple lines of PowerShell, you should consider this automation to create effective replication model.
在使用SQL Server这么长时间之后，我觉得很难使用可用SQL Server本机技术来模拟接近生产的数据库。 如果您考虑使用除几行PowerShell外无需编码的即用型解决方案，则应考虑这种自动化以创建有效的复制模型。
使用模式比较工具进行DDL复制 (Using a schema comparison tool for DDL replication)
Now that we’ve shown how to do this using just ApexSQL Log, in this section, we will walk-through the steps required to setup a dynamic transactional replication using ApexSQL Diff in combination with ApexSQL Log for schema and data replication respectively.
现在，我们已经展示了如何仅使用ApexSQL Log来执行此操作，在本节中，我们将逐步完成使用ApexSQL Diff结合ApexSQL Log分别进行模式和数据复制来设置动态事务复制所需的步骤。
Add new table(s), alter the existing objects in the source database in any replicated system, these changes must be synchronized to the Subscriber database. To do that you need to perform a schema comparison.
添加新表，更改任何复制系统中源数据库中的现有对象，这些更改必须同步到订户数据库。 为此，您需要执行架构比较。
For this, we’ll use ApexSQL Diff, a tool used to compare and synchronize schema differences between source and target databases or in our case, Publisher and Subscriber databases. Using ApexSQL Diff, schema changes that are made in the source database are captured and compared with the target. It is very easy to generate CLI commands and integrate the resulting batch (.bat) file with PowerShell to synchronize the schemas.
为此，我们将使用ApexSQL Diff，该工具用于比较和同步源数据库和目标数据库之间的模式差异 ，在本例中为发布者数据库和订阅者数据库。 使用ApexSQL Diff，可以捕获源数据库中进行的架构更改并将其与目标进行比较。 生成CLI命令并将生成的批处理（.bat）文件与PowerShell进行同步非常容易。
We’ll still use ApexSQL Log, a SQL Server transaction log reader, to act as our DML replication agent. We’ll create a job to read the transaction log and write it to a script, then a batch file and a PowerShell script to automate the job.
我们仍将使用ApexSQL Log（ SQL Server事务日志读取器 ）充当DML复制代理。 我们将创建一个作业来读取事务日志并将其写入脚本，然后创建一个批处理文件和一个PowerShell脚本以使作业自动化。
Here is a schematic view of our setup in which we’ll use a batch file and a PowerShell script, in combination, to poll a SQL Server, on a schedule and create a script to replay both DDL (schema) and DML (data) scripts that we’ll replay on the publisher database
这是设置的示意图，在该视图中，我们将结合使用批处理文件和PowerShell脚本，按计划轮询SQL Server，并创建脚本以重播DDL（模式）和DML（数据）我们将在发布者数据库上重播的脚本
模式变更复制 (Schema change replication)
To replicate schema changes with ApexSQL Diff, follow the steps:
要使用ApexSQL Diff复制架构更改，请按照下列步骤操作：
- Open ApexSQL Diff 打开ApexSQL差异
Click New
点击新建
In the Data sources tab, type in the data source and destination details
在“ 数据源”选项卡中，键入数据源和目标详细信息
In the object filters, by default, all objects will be checked. By using filters, we have an option to narrow down comparison. Just check the filters on Difference, Missing and Additional options to list only the affected objects
默认情况下，在对象过滤器中，将检查所有对象。 通过使用过滤器，我们可以选择缩小比较范围。 只需检查“差异”，“缺失”和“其他”选项上的过滤器，以仅列出受影响的对象
- save dropdown option and click “保存下拉菜单 ，然后点击“ Save as batch” 另存为批处理 ”
Copy or save the CLI command into a batch file
将CLI命令复制或保存到批处理文件中
@echo off
SET “filename=%1”
“E:\Program Files\ApexSQL\ApexSQL Diff\ApexSQLDiff.com”
/server1:”HQDBT01″ /database1:”WideWorldImporters” /server2:”HQDBT01\SQL2017″ /database2:”WideWorldImporters”
/ignore_general:iws ic iso idep iio /ignore_table_attributes:ico ifc /struct_sync_options:iddo ith sud /encoding:Unicode
/output_elements:cmt ps /output_type:sql /output_name:%filename%
/force /verbose
@echo ApexSQL Diff return code: %errorlevel%@回声关闭
设置“文件名=％1”
“ E：\ Program Files \ ApexSQL \ ApexSQL Diff \ ApexSQLDiff.com”
/ server1：“ HQDBT01” / database1：“ WideWorldImporters” / server2：“ HQDBT01 \ SQL2017” / database2：“ WideWorldImporters”
/ ignore_general：iws ic iso idep iio / ignore_table_attributes：ico ifc / struct_sync_options：iddo ith sud / encoding：Unicode
/ output_elements：cmt ps / output_type：sql / output_name：％filename％
/ force /详细
@echo ApexSQL Diff返回代码：％errorlevel％
资料复制 (Data replication)
The part of our task is now successfully created. Next, generate the job to replicate the data changes. The only difference is that this time we’ll exclude DDL auditing by having only DMLALL as a variable in the /operations switch.
现在，我们的任务已成功创建。 接下来，生成作业以复制数据更改。 唯一的区别是，这一次我们将通过仅将DMLALL作为/ operations开关中的变量来排除DDL审核。
@echo off
SET “filename=%1”
“E:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com”
/server:HQDBT01 /database:WideWorldImporters /trusted
/redo:%filename%
/operations:DMLALL
/transactions:COMMIT BEGIN UNKNOWN /continuous:G:\DBA\ApexSQLLog\WideWorldImporters.axtr
@回声关闭
设置“文件名=％1”
“ E：\ Program Files \ ApexSQL \ ApexSQL Log \ ApexSQLLog.com”
/ server：HQDBT01 / database：WideWorldImporters /受信任
/重做：％文件名％
/操作：DMLALL
/ transactions：COMMIT BEGIN UNKNOWN /continuous:G:\DBA\ApexSQLLog\WideWorldImporters.axtr
That’s the entire configuration we need to setup a highly enhanced transactional replication model for both DDL and DML changes/additions
这就是我们需要为DDL和DML更改/添加设置高度增强的事务复制模型的整个配置
自动化 (Automation)
As we plan to automate the synchronization and data export task, PowerShell script is prepared. It helps to gather all the command in one place and it is possible to manipulate the way we want and use it more than once in its entirety.
当我们计划自动化同步和数据导出任务时，将准备PowerShell脚本。 它有助于将所有命令集中在一个地方，并且可以操纵我们想要的方式，并且可以多次使用它。
Next, save the below content in a replication.PS1 file. The script has following tasks:
接下来，将以下内容保存在Replication.PS1文件中。 该脚本具有以下任务：
- Load the SQL Server module 加载SQL Server模块
- ApexSQL Diff batch file is invoked to generate a DDLreplication_<*datetimestamp*>.sql file 调用ApexSQL Diff批处理文件以生成DDLreplication _ <* datetimestamp *>。sql文件
- ApexSQL Log batch file is invoked to generate a DMLreplication_<*datetimestamp*>.sql 调用ApexSQL日志批处理文件以生成DMLreplication _ <* datetimestamp *>。sql
- Now, we’ve two sets of the input file to execute based on the input flag 现在，我们有两组输入文件要根据输入标志执行
- Invoke SQL files to run at the subscriber database using the Invoke-SQLCMD cmdlet 使用Invoke-SQLCMD cmdlet调用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
$redofilepath = "G:\DBA\datasync\DMLreplication_$datetime.sql"
$filepath = "G:\DBA\datasync\DDLreplication_$datetime.sql"
cmd /c "G:\DBA\datasync\DDLReplication.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 "WideWorldImporters"
}
cmd /c "G:\DBA\datasync\DMLreplication.bat" $redofilepath
#Check the file path
If(Test-Path -Path $redofilepath)
{
Invoke-SQLcmd -inputfile $redofilepath -serverinstance "hqdbt01\sql2017" -database "WideWorldImporters"
}
Then this script can be scheduled the same way as the previous use case.
然后，可以使用与上一个用例相同的方式计划此脚本。
目录 (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复制监视和设置警报 |
sql dml ddl
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
