SQL Server Connector: Query Log Ingestion

To ingest query logs into the catalog, you can either create a table or view for Alation to retrieve the query logs from or create a custom query to read the logs from SQL Server.

Before running query log ingestion (QLI) in Alation, perform the preliminary QLI setup on SQL Server.

Note

For information on how to configure query log ingestion (QLI) for Azure SQL Managed Instance, see Query Log Ingestion for Azure SQL Managed Instance.

For SQL Server, Alation supports various methods of query ingestion. Select one of the options below.

Option 1: Extended Events

Using extended events is the preferred QLI method that has less performance impact on the SQL Server system than other methods. An extended events session is created that logs queries to files. Alation reads those files through SQL queries sent to SQL Server and ingests them. The volume of events may be high, so the configuration described below limits logging to only a sample of events and tries to filter out as many queries as possible that aren’t useful to Alation. We suggest starting with this setup and adjusting the sampling later, working with the sampled logs.

To use this option, make sure your Alation service account has the required permissions. If the Alation service account is a local SQL Server account, it must have the sysadmin role. If an Active Directory account is used, the sysadmin role is not required.

The SQL Server account (not the Alation service account) must have the read/write access to the directory that stores the logs.

Note

More details on extended events in SQL Server documentation: Quickstart: Extended events in SQL Server.

Configuration

  1. Grant the Alation service account the VIEW SERVER STATE permission. This is needed as Alation will run the sys.fn_xe_file_target_read_file command to read the logged .xel files that are created by extended events. The VIEW SERVER STATE permissions is required to use the command.

    USE master;
    GRANT VIEW SERVER STATE TO <service_account>;
    
  2. Grant the Alation service account permission to run the stored procedure xp_dirtree. This is needed as Alation will read the list of logged .xel files that it is going to ingest. This stored procedure lists files in a directory.

    USE master;
    GRANT EXECUTE ON xp_dirtree TO <service_account>;
    
  3. Create and turn on an extended events session that logs queries. See Appendix A.1a SQL Server 2012, 2014, 2016 Extended Events Session Creation for the SQL code. You will need to change the file path to log files and can optionally change some of the other parameters like the buffer size.

  4. When you first turn on the session, monitor it for a few days to see that it is working and assess the volume of log files. If the volume is small—only a few log files per day—you can edit the extended events SQL code to turn down or remove the sampling. See the comments in the SQL code.

  5. Configure the data source settings in the Alation user interface to point to the files that are being logged. Ensure that you include the trailing slashes at the end of the path or you will get partial success with the no logs to ingest warning.

    Note

    If you run XEvents for SQL Server, enter the file path in the format C:\\Users\\Public\\Documents\\.

    ../../../_images/SQLServerOCF_04.png

Option 2: Server Side Trace

Alation can use the server side trace to capture the query history log. By default, the server side trace can log a lot of unwanted queries like system queries, or backup and maintenance queries that can occupy a lot of disk space. To limit disk usage, it is important to limit the traced queries to only the important ones that support your use case. For example:

  • Model queries from data analysts that can be used for reference and knowledge sharing.

  • Queries on a subset of DBs to compute object popularity in a DB.

If filtering doesn’t help limit the size of the query history log, a trace script can be run for a short interval anywhere from two to four hours to capture a subset of queries.

Refer to Appendix A.1c SQL Server Trace Script for an example trace script. The script should be run directly on the server.

Option 3: SQL Profiler

SQL Server environments with light infrequent loads can use the client side profiling with SQL Profiler to capture the query history log. The following steps show how to to set up query history logging using Profiler.

  1. Launch SQL Server Profiler and create a new Template called AlationQueryLog:

    ../../../_images/DS_SQLServer04.png

../../../_images/DS_SQLServer05.png
  1. Click the Events Selection tab and pick the following events:

    • Stored Procedures -> SP:StmtCompleted

    • TSQL -> SQL:StmtCompleted

    ../../../_images/DS_SQLServer06.png

3. For the SP:StmtCompleted and SQL:StmtCompleted events, select the following columns:

  • ApplicationName

  • CPU

  • DatabaseName

  • Duration

  • EndTime

  • LoginName

  • NTUserName

  • Reads

  • RowCounts

  • SPID

  • StartTime

  • TextData

  • Writes

../../../_images/DS_SQLServer07.png

4. Set Filters to log only the important queries (for example, exclude routine maintenance queries). This step is optional. Click the Column Filters button on the Events Selection tab:

../../../_images/DS_SQLServer08.png

For example, to exclude queries from MaintenanceApp, set the ApplicationName Not Like filter to MaintenanceApp%.

  1. Set filters on other columns as you see fit to limit logging to useful queries only.

    ../../../_images/DS_SQLServer09.png

6. Save the template.

7. Start a trace using the AlationQueryLog template.

../../../_images/DS_SQLServer10.png

8. Select Save To Table. Select Set maximum rows and define a limit based on the workload in your environment and the disk space on the server.

../../../_images/DS_SQLServer11.png

9. Observe the trace and adjust the column filters as needed (after pausing the trace).

../../../_images/DS_SQLServer12.png

10. Set up a job to periodically copy rows from the trace table into another archive table where you maintain history for three or four days.

11. Grant the SELECT privileges to the Alation database account on the archive table to read query history.

Option 4: SQL Server Audit

Query history information can be captured from the SQL Server Audit database.

Note

For specific details about SQL Server Audit, refer to the SQL Server documentation for the relevant SQL Server version:

Prerequisites

To use SQL Server Audit for QLI in Alation:

  1. Create a Server Audit to log query events for Alation QLI. FILEPATH is the path to the directory to store the audit files:

    CREATE SERVER AUDIT test_audit
    TO FILE ( FILEPATH =
    'C:\Users\Public\Documents\' );
    
  2. Create a Database Audit Specification that defines which events to audit for Alation. Audit groups specify the type of queries to be logged:

    CREATE DATABASE AUDIT SPECIFICATION test_audit_spec
    FOR SERVER AUDIT test_audit
    ADD( DATABASE_OBJECT_CHANGE_GROUP),
    ADD( SELECT ON DATABASE::db_name BY <role>))
    WITH (STATE = ON);
    
  3. Allow some time for users to query the database before performing QLI in Alation. Alation recommends to have 2 to 3 weeks of query history logs in the database for QLI to render meaningful results. Logs will be stored in the file path you have set up in the .sqlaudit format.

  4. To list the files that currently exist in the file path, you can run the following command:

    EXEC xp_dirtree 'C:\Users\Public\Documents', 0, 1;
    
  5. Create a QLI View for Alation with the data from the audit files:

    CREATE VIEW Alation_QLI_View AS (
    SELECT
      application_name as ApplicationName,
      server_principal_sid as CPU,
      database_name as DatabaseName,
      duration_milliseconds as Duration,
      event_time as EndTime,
      server_principal_name as LoginName,
      server_principal_name as NTUserName,
      0 as Reads,
      affected_rows as RowCounts,
      server_principal_sid as SPID,
      event_time as StartTime,
      statement as TextData,
      session_id as Writes,
      null as EventClass
    FROM fn_get_audit_file('C:\Users\Public\Documents\*',default,default)
    );
    
  6. Grant the SELECT privileges on the created QLI view to the Alation service account.

Other Methods

Alation can also support ingestion from a custom table (not recommended). For information on the custom table set up, see Appendix A.1d SQL Server Ingestion from Custom Table Setup.

Configure QLI in Alation

Table-Based QLI

Use the query format below to create a new table and flush the query history from the SQL Server data source.

CREATE VIEW <view_name> AS (
    SELECT
      top(100)
      'username' AS userName,
      creation_time startTime,
      st.text AS queryString,
      @@SPID AS sessionId,
      creation_time AS sessionStartTime,
      convert(money, (qs.total_elapsed_time))/(execution_count*1000) AS seconds,
      NULL AS cancelled,
      db_name(sp.dbid) AS defaultDatabases
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS st
    CROSS APPLY sys.dm_exec_query_plan(qs.[plan_handle]) AS sp
    ORDER BY
      last_execution_time DESC);

In the Table Name field, specify the name of the view in which the query logs are available. Make sure that the user has access to this view. Use the following format: catalog.schema.view.

Custom Query-Based QLI

When you cannot create a table or view, you can use a custom QLI query to perform QLI. Provide the expected query structure as shown below and click Save:

SELECT
  <userNameColumn> AS userName,
  <textColumn> as queryString,
  <defaultDatabases> AS defaultDatabases,
  <sessionIdColumn> AS sessionId,
  <sessionStartTimeColumn> AS sessionStartTime,
  <StartTimeColumn> AS startTime,
  <QueryCancelledColumn> AS cancelled,
  <milliSecondsColumn> AS milliSeconds,
  <sequenceidColumn> AS sequence
FROM <view_name>
    WHERE startTime BETWEEN 'STARTTIME1' and 'STARTTIME2'
    ORDER BY sessionId, startTime;

Extended Events Method

If Extended Events method is used to ingest query logs in Alation:

  1. Select the Use XEvents checkbox.

  2. In the Absolute Path on Server field, provide the absolute path of the .xel files on the server. If this field is blank, the default path C:\Users\Public\Documents\ will be used.

  3. In the Prefix of the XEL Files field, provide the prefix of the .xel files. If this field is blank, the default prefix alation_query_log will be used.

Automated and Manual QLI

Users can either perform query log ingestion manually on demand or enable automated query log ingestion.

  1. To perform manual query log ingestion, make sure that the Enable Automated Query Log Ingestion toggle is Off. Click the Import button to start query log ingestion.

  2. To perform the automated query log ingestion, set the Enable Automated Query Log Ingestion toggle to On and set a schedule in the corresponding fields of the schedule section, specifying the values for week, day, and time. The next query log ingestion job will run automatically on ths schedule.

    Note

    Hourly schedule for automated QLI is not supported.

    ../../../_images/SQLServerOCF_05.png