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.
- 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.
masterdatabase 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.
BillingDBS 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.
AUDITLOGfolder of the root directory to which the
masterdatabase belongs and backs up the data to DBS by using the Extended Events Sessions feature.
AUDITLOGfolder 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
Step 2: Query and analyze audit data
- Log on to the DBS console.
- In the left-side navigation pane, click Backup Schedules. On the Backup Schedules page, select a region in the upper-left corner.
- 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.
- In the left-side navigation pane, choose .
- 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.
- In the Query Backup Set message, click OK.
- 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.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.
The following table describes the fields of the
|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.|