SQL Server Connector¶

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

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 Azure SQL Managed Instance.

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

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.

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

Note

For reference on extended events, see Quickstart: Extended events in SQL Server in SQL Server documentation.

Configuration¶

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>;
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>;
Create and turn on an extended events session that logs queries. To configure, see Appendix A.1a SQL Server 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.

Note

When you turn on the extended events session, the files are created in the <prefix>_0_<creation_timestamp>.xel format. If you alter the file format, Alation skips those files during query log ingestion.
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.
Configure the data source settings in the Alation user interface to point to the files that are being logged.

For connector version prior to 1.5.0, see Extended Events Method

For connector version 1.5.0 or newer on Alation version 2024.1 or newer, see Configure QLI for Version 1.5.0 or Newer.

SQL Profiler¶

Note

SQL Profiler is deprecated. However, Alation will continue to support SQL Profiler. For more information, see SQL Server 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.

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

For more information, see Run SQL Server Profiler - SQL Server Profiler and Create Template.
Click the Events Selection tab and pick the following events:
- Stored Procedures -> SP:StmtCompleted
- TSQL -> SQL:StmtCompleted

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

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:

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

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

6. Save the template.

7. Start a trace using the AlationQueryLog template.

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.

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

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.

SQL Server Audit¶

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

Note

Auditing only stores the first 4000 characters of the query in a row. So, a truncated query will be shown in the query history after QLI if the query has more than 4000 characters.

Note

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

SQL Server Audit (Database Engine)

CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)

SQL Server Audit Action Groups and Actions

Configuration¶

To use SQL Server Audit for QLI in Alation:

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

USE master;
CREATE SERVER AUDIT alation_qli_audit
    TO FILE (
        FILEPATH ='C:\Users\Public\Documents',
        MAXSIZE = 100 MB,
        MAX_ROLLOVER_FILES = 100,
        RESERVE_DISK_SPACE = OFF
    )
    WITH (
        -- Max time  in milliseconds before writing audits to file.
        QUEUE_DELAY = 15000,
        ON_FAILURE = CONTINUE
    )
    WHERE (
        [schema_name] <> 'sys'      AND
        [schema_name] <> ''                     AND
        [schema_name] <> 'public'       AND
        [database_name] <> ''           AND
        [application_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' AND
        [application_name] <> 'Microsoft SQL Server Management Studio' AND
        -- schema and database predicates should suffice
        -- server principal names can also be added to further filter (telemetry, etc)
        -- [server_principal_name] <> '<insert_name>' AND
        (
            [statement] like 'ALTER%' OR
            [statement] like 'CREATE%' OR
            [statement] like 'DROP%' OR
            [statement] like 'TRUNCATE%' OR
            [statement] like 'MERGE%' OR
            [statement] like 'SELECT%INTO%' OR
            [statement] like 'INSERT%INTO%FROM %' OR
            [statement] like 'UPDATE%FROM%' OR
            [statement] like 'USE%' OR
            [statement] LIKE 'SELECT%FROM%' OR
            [statement] like '%ALTER%' OR
            [statement] like '%CREATE%' OR
            [statement] like '%DROP%' OR
            [statement] like '%TRUNCATE%' OR
            [statement] like '%MERGE%' OR
        -- INSERT statements can be of type "SELECT INTO ..." or "INSERT INTO ..."
            [statement] like '%SELECT%INTO%' OR
            [statement] like '%INSERT%INTO%FROM%' OR
            [statement] like '%UPDATE%FROM %' OR
            [statement] like '%USE%' OR
            [statement] LIKE '%SELECT%FROM%' OR
            [statement] like '%FROM%'
        )
    )
;

Note

MAXSIZE - Specify the maximum file size. Once the file size exceeds the limit, the SQL Server creates a new file. Alation recommends a maximum file size of 100 MB before rolling over.
MAX_ROLLOVER_FILES - Specify the maximum number of rollover xel files to create when file size exceeds the maximum file size specified (max_file_size). Once the limit exceeds, the SQL Server removes the oldest file and creates a new one. Alation recommends a maximum of 100 rollover files.
QUEUE_DELAY - Specify the maximum time in milliseconds to wait before writing audits to file. Alation recommends a maximum of 15000 milliseconds.

Create a Database Audit Specification that defines which events to audit for Alation. Audit groups specify the type of queries to be logged:

USE <database_name>;
GO
CREATE DATABASE AUDIT SPECIFICATION [test_audit_spec]
FOR SERVER AUDIT [alation_qli_audit]
    ADD (DELETE ON DATABASE::[<database_name>] BY [<role>]),
    ADD (INSERT ON DATABASE::[<database_name>] BY [<role>]),
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP),
    ADD (SELECT ON DATABASE::[<database_name>] BY [<role>]),
    ADD (UPDATE ON DATABASE::[<database_name>] BY [<role>])
WITH (STATE = ON)
GO
-- enable server audit
USE master;
ALTER SERVER AUDIT alation_qli_audit
    WITH (STATE = ON);
GO

Allow some time for users to query the database before performing QLI in Alation. Alation recommends to retain 2-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.

Create a QLI View for Alation with the data from the audit files:

CREATE VIEW Alation_QLI_View AS (
SELECT
  server_principal_name as userName,
  (server_principal_name + '/' +  CONVERT(varchar(16), server_principal_sid)) as SessionId,
  statement as queryString,
  database_name as defaultDatabases,
  duration_milliseconds as seconds,
  event_time as sessionStartTime,
  event_time as StartTime,
  'N' as cancelled
FROM fn_get_audit_file('C:\Users\Public\Documents\*',default,default)
);

Grant the SELECT privileges on the created QLI view to the Alation service account.
Grant the CONTROL SERVER privilege at the server-level to read audits using the fn_get_audit_file function.

Other Methods¶

Alation can also support ingestion from a custom table (not recommended).

Configure QLI in Alation (Version 1.5.0 and Before)¶

Table-Based QLI¶

Provide the view name created for QLI in SQL Server Audit section in the Table Name field.

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 seconds,
FROM <object_name>
    WHERE startTime BETWEEN 'STARTTIME' and 'ENDTIME'
    ORDER BY sessionId, startTime;

Note

<object_name> can be a view, table or procedure.

Extended Events Method¶

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

Select the Use XEvents checkbox.

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.

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.

Important

When you turn on the extended events session, the files are created in the <prefix>_0_<creation_timestamp>.xel format. If you alter the file format, Alation skips those files during query log ingestion.

Ensure that you provide a unique prefix. If you use a common prefix, the connector will ingest all the files that match the prefix. This may affect the QLI performance.

Note

Use the absolute file path and prefix that you specified during the extended events session creation (filename).
If you run XEvents for SQL Server, enter the file path in the format C:\Users\Public\Documents\.

Automated and Manual QLI¶

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

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.
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.

Configure QLI in Alation (Version 1.5.0 and Newer)¶

See Configure QLI for Version 1.5.0 or Newer.

SQL Server Connector¶

Extended Events¶

Configuration¶

SQL Profiler¶

SQL Server Audit¶

Configuration¶

Other Methods¶

Configure QLI in Alation (Version 1.5.0 and Before)¶

Table-Based QLI¶

Custom Query-Based QLI¶

Extended Events Method¶

Automated and Manual QLI¶

Configure QLI in Alation (Version 1.5.0 and Newer)¶

Alation Documentation PDF