如何连续直接将事务日志文件数据审核到SQL Server数据库中

Business transformation requires solid tools to automate complex integration to seamless deployments. In today’s modern data-rich world, nothing is more important than data management, making it critical to know how to safeguard and meet compliance requirement is very critical and the key to the business success.

业务转型需要可靠的工具来自动完成复杂的集成以实现无缝部署。 在当今现代的数据丰富的世界中，没有什么比数据管理更重要的了，因此了解如何维护和满足合规性要求至关重要，这也是业务成功的关键。

In this article, we’ll discuss one method to utilize one of the most powerful and beneficial features in SQL Server, the transaction log. The transaction log is not meant to be read, let alone queried, and even when information is extracted, it can different to interpret. So imagine if you could actually query the SQL Server transaction log like any other table and get human-readable information. Imagine the wealth of information about the transactional history and patterns of your database, that you could glean?

在本文中，我们将讨论一种利用SQL Server中最强大，最有益的功能之一的事务日志的方法。 事务日志并非要读取，更不用说查询了，即使提取了信息，其解释也可能有所不同。 因此，想象一下您是否可以像其他任何表一样实际查询SQL Server事务日志并获取人类可读的信息。 想象一下有关数据库的交易历史和模式的大量信息，您可以收集吗？

In this article, we are going to show you how to do just that and create an example of how to gain valuable aggregate information about our database including measuring aggregate information about the number the Inserts, Updates, and Deletes operations and we’ll also implement before and after auditing, for changed values. Also, we’ll discuss how easy it is to find those affected objects against these transactions and simplify the auditing process by extracting the transaction details and storing it in the repository. The data will help you to experience and give an insight on how the workload is being captured and produced in the database

在本文中，我们将向您展示如何做到这一点，并创建一个示例，说明如何获取有关我们数据库的有价值的汇总信息，包括测量有关Inserts，Updates和Deletes操作数量的汇总信息，我们还将实现审核前后 ，用于更改值。 此外，我们还将讨论针对这些事务查找受影响的对象并通过提取事务详细信息并将其存储在存储库中来简化审核过程的容易程度。 数据将帮助您体验并提供有关如何在数据库中捕获和产生工作负载的见解

大纲 (Outline)

In the previous article, How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog, we discussed the similar setup and process of reading the transaction log and logging the details in the repository with fn_dbLog. In this article, you’ll see how to automate the process by reading online transaction log data into SQL Server tables with an execution of batch file using a 3^rd party SQL Server transaction log reader.

在上一篇文章如何使用fn_dblog和fn_dump_dblog直接在SQL Server数据库中连续读取事务日志文件数据 ，我们讨论了读取事务日志并使用fn_dbLog在存储库中记录详细信息的类似设置和过程。 在本文中，您将看到如何通过阅读网上交易日志数据到SQL Server表与使用^第三方 SQL Server事务日志读者一个批处理文件的执行过程自动化。

For this article, we’ll use ApexSQL Log, a SQL Server transaction log reader from ApexSQL. We’ll simulate the process of reading a transaction log using ApexSQL Log export option along with continuous auditing features to log the transaction meta-data. And, the job can be scheduled to run every 5 minutes (or every night etc) using the available job scheduler utilities. You can refer to the previous articles if you’re intended to create a job using PowerShell and SQL Server Agent. In this example, we are going to discuss the job scheduling tasks using the Windows Task Scheduler and a batch file.

在本文中，我们将使用ApexSQL Log（ApexSQL的SQL Server事务日志读取器） 。 我们将模拟使用ApexSQL日志导出选项以及持续审核功能来记录事务元数据的读取事务日志的过程。 而且，可以使用可用的作业计划程序实用程序将作业安排为每5分钟（或每晚等）运行一次。 如果打算使用PowerShell和SQL Server代理创建作业，则可以参考前面的文章。 在此示例中，我们将使用Windows Task Scheduler和批处理文件讨论作业调度任务。

In this article you’ll learn how:

在本文中，您将学习如何：

1. to see an aggregate level overview of SQL transaction log data
   查看SQL事务日志数据的聚合级别概述
2. to prepare SQL to capture the transaction rate
   准备SQL以捕获事务率
3. to gain hands-on experience in the creation of ApexSQL Log CLI commands
   在创建ApexSQL Log CLI命令方面获得实践经验
4. to deploy, configure and manage audit data synchronization steps using ApexSQL Log Export option
   使用ApexSQL Log Export选项部署，配置和管理审核数据同步步骤
5. to schedule a job using the Windows Task Scheduler
   使用Windows Task Scheduler计划作业
6. to ensure seamless data export from source to target with audit data
   确保使用审计数据从源到目标的无缝数据导出
7. And more ….
   和更多 …。

这个怎么运作… (How it works…)

In this section, we’ll outline the process of generating an ApexSQL Log CLI commands in a windows batch (BAT) file and then call the batch file in a scheduled window to run at every minute using Windows Task Scheduler.

在本节中，我们将概述在Windows批处理（BAT）文件中生成ApexSQL Log CLI命令的过程，然后在计划的窗口中调用该批处理文件，以使用Windows Task Scheduler在每分钟运行一次。

Let us walk through the entire process using Adventureworks 2016 database.

让我们使用Adventureworks 2016数据库逐步完成整个过程。

1. Backup the source database
   备份源数据库
2. Restore the backup file on the target instance
   在目标实例上还原备份文件
3. Build a batch file using ApexSQL Log
   1. Start ApexSQL Log

   2. Type in Server and Database details

      

   3. In the select data source window, I will leave the default values and click Next.

   4. In the Select output window, select the Export results

      

   5. In the filter setup window, choose continuous auditing feature

   使用ApexSQL Log生成批处理文件
   1. 启动ApexSQL日志

   2. 输入服务器和数据库详细信息

      

   3. 在选择数据源窗口中，我将保留默认值，然后单击下一步 。

   4. 在“选择输出”窗口中，选择“ 导出结果”

      

   5. 在过滤器设置窗口中，选择连续审核功能

Continuous auditing preserves the LSN chain sequence. This mechanism provides the session continuity so that the transactions are read is consistent and it can replay the transaction to the target by maintaining the integrity of the transactions. Continuous transaction log auditing is supported in every output option

连续审核将保留LSN链序列。 此机制提供了会话连续性，因此读取的事务是一致的，并且可以通过维护事务的完整性将事务重播到目标。 每个输出选项均支持连续事务日志审核

1. In the Export, select Export to database option.
   在“ 导出”中 ，选择“ 导出到数据库”选项。
2. Browse the Path and type in the target database instance details
   浏览路径并输入目标数据库实例的详细信息

3. Click Connect

   

   点击连接

   

4. Save the batch file to the known file location.

   

   将批处理文件保存到已知的文件位置。

The content of batch file Log-AuditExport.bat is shown below

批处理文件Log-AuditExport.bat的内容如下所示

That’s all… the configuration is completed. Click Cancel to close the window

仅此而已...配置完成。 单击取消关闭窗口

排程 (Scheduling)

In this section, we detail the steps to perform to schedule a job using the Task Scheduler.

在本节中，我们详细介绍了使用Task Scheduler计划作业的步骤。

1. Open Task Scheduler and select Create Task…

   

   打开任务计划程序，然后选择创建任务…

2. Type in the Name of Job and select “Run Whether user is logged on or not” as shown below

   

   键入“作业名称” ，然后选择“ 运行是否登录用户 ”，如下所示

3. On the Triggers tab and click New
   在“ 触发器”选项卡上，单击“ 新建”。

4. Enter the Schedule details as per the requirement. In this case, we are gathering audit data every 5 minutes.

   

   根据要求输入时间表详细信息。 在这种情况下，我们每5分钟收集一次审核数据。

5. Select Actions tab and Click New
   选择操作选项卡，然后单击新建
6. Select the path of the batch file in the Program/script setting
   在“ 程序/脚本”设置中选择批处理文件的路径

7. Click OK

   

   点击确定

8. Next, the process will prompt you to enter the credentials. Type in the required detail and Click OK. Make sure, you’re entering the administrative privileges,

   

   接下来，该过程将提示您输入凭据。 输入所需的详细信息，然后单击“确定”。 确保您要输入管理权限，

9. Configuration is complete. Now, run the job.

   

   配置完成。 现在，运行作业。

验证结果 (Verifying the results)

Use ApexSQL Log export feature is used to export meta-data of each transaction into SQL table. On executing the ApexSQL Log CLI command, the process inserts the transactions read from the transaction logs into the APEXSQL_LOG_OPERATION_DETAIL and APEXSQL_LOG_OPERATION tables.

使用ApexSQL日志导出功能用于将每个事务的元数据导出到SQL表中。 在执行ApexSQL Log CLI命令时，该过程将从事务日志中读取的事务插入APEXSQL_LOG_OPERATION_DETAIL和APEXSQL_LOG_OPERATION表中。

APEXSQL_LOG_OPERATION table contains the columns such as operation type, object, schema, user etc.

APEXSQL_LOG_OPERATION表包含诸如操作类型，对象，架构，用户等列。

And APEXSQL_LOG_OPERATION_DETAIL table contains LSN, column name and type, old and new values.

并且APEXSQL_LOG_OPERATION_DETAIL表包含LSN，列名和类型，旧值和新值。

结语 (Wrapping up)

Thus far, we have seen that how is read the transaction log file and log the transaction meta-data using ApexSQL Log. Using ApexSQL Log, the column values, old as well new values are logged in the table. This gives more flexibility for quick data mining search to see the state of the data and in some case, it is very useful when the backup is invalid.

到目前为止，我们已经看到了如何使用ApexSQL Log读取事务日志文件和记录事务元数据。 使用ApexSQL Log，列值，旧值和新值都记录在表中。 这为快速数据挖掘搜索提供了更大的灵活性，以查看数据状态，并且在某些情况下，当备份无效时，它非常有用。

Other biggest advantage is logging the data in the local or remote SQL instance. In this way, the data can be secured and use it later for reporting and even in many cases, the data is used for compliance auditing.

另一个最大的优点是将数据记录在本地或远程SQL实例中。 这样，可以保护数据并在以后将其用于报告，甚至在许多情况下，也将数据用于合规性审核。

If you’re ready for the test, give it a try by downloading the free trial of ApexSQL Log, setting up a similar scenario for continuous auditing and let me know if you have any problems in the comments below

如果您已准备好进行测试，请下载ApexSQL Log的免费试用版，进行尝试，并设置类似的方案进行连续审核，如果您在下面的评论中遇到任何问题，请告诉我。

翻译自: https://www.sqlshack.com/how-to-continuously-audit-transaction-log-file-data-directly-into-the-sql-server-database/