When you use Database Backup to back up an SQL Server database, you can back up the audit data of the database to the cloud. The audit data of a database includes all SQL logs that record user operations such as queries, data manipulation language (DML) operations, and data description language (DDL) operations on the database.

Prerequisites

  • The version of the database engine is SQL Server 2012 or later.
  • The physical backup for the database is complete. The incremental backup and audit backup features are enabled.
  • The master database of the SQL Server system is stored on an on-premises disk. The capacity of the disk used to store audit data is larger than 1 GB.

Billing

DBS can call the Data Lake Analytics (DLA) API to query data in backup sets. You are not charged for using DBS to query data in backup sets. However, DLA charges you based on the volume of scanned data. For more information, see Billing methods.

Features

If you enable the Audit Backup feature in the DBS console, DBS saves the audit data to the AUDITLOG folder of the root directory to which the master database belongs and backs up the data to DBS by using the Extended Events Sessions feature.
Note The maximum size of the AUDITLOG folder is 1 GB. We recommend that you do not move or delete the AUDITLOG folder.

DBS creates the mssql_audit table in the cloud by using the audit data. The mssql_audit table consists of the audit information including the points in time at which SQL statements are executed, SQL statements, and execution duration. For more information about mssql_audit, see mssql_audit table. You can query and analyze audit data in the cloud. For more information, see Step 2: Query and analyze audit data.

Step 1: Back up audit data

For more information, see Back up an SQL Server database.
Note When you configure a backup schedule, you must enable the incremental backup and Audit Backup features.

Step 2: Query and analyze audit data

  1. Log on to the DBS console.
  2. In the left-side navigation pane, click Backup Schedules. On the Backup Schedules page, select a region in the upper-left corner.
    Select a region
  3. On the Backup Schedules page, find the backup schedule where the backup set that you want to query is generated. Then, click Manage in the Actions column.
  4. In the left-side navigation pane, choose Backup Tasks > Audit data backup.
  5. Find the audit file that you want to query in the Audit file name column, and then click Query audit in the Actions column.
    Note Each audit file contains the audit data over the displayed time range in the Audit file start/end time column. You can find the audit file based on the displayed time range.
  6. In the Query Backup Set message, click OK.
  7. Log on to the DLA console. In the left-side navigation pane, choose Serverless SQL > Execute. DLA creates a database based on the schema of the selected backup sets and displays the database in the navigation tree of the Execute page. Double-click the database name, enter an SQL statement in the code editor, and then click Sync Execute.
    Query audit data in the DLA console
    Note
    • The SQL syntax of DLA is based on Presto. For more information, click Syntax Manuals in the upper-right corner of the Execute page or see Common SQL statements.
    • The following SQL statements are commonly used to analyze audit data:
      • SELECT timestamp,sql_text,duration FROM mssql_audit LIMIT 500;

        Description: Query the points in time at which SQL statements are executed, the SQL statement, and the execution duration of the SQL statement.

      • SELECT * FROM (SELECT query_hash,avg(duration) as avg,count(1),sql_text FROM mssql_audit group by query_hash ) order by avg desc;

        Description: Query the type of SQL statements that consume the longest average execution time and sort the query results by execution duration in descending order.

mssql_audit table

The following table describes the fields of the mssql_audit table.

Field Description
name The type of the event.
timestamp The time when the event occurred.
duration The duration of the event. Unit: microseconds.
query_hash The ID of the SQL template.
Note You can use the SQL template to aggregate SQL statements that consume the longest execution time.
sql_text The content of the SQL statement.
database_name The name of the database.
logical_read The number of logical reads.
physical_read The number of physical reads.