SQL Server Health Check Step 2: Analyze Results

Do you have SQL Server problems and don’t know where to turn for help? This article is about SQL Server Health Check Step 2: Analyze Results. It was designed to help you decide if a SQL Server audit is right for your organization.

SQL Server Health Check Step 2: Analyze Results

 

SQL Server Health Check Step 2 Analyze Results by UpSearch

Previously, we explained Step 1: Collect Metrics. Let’s move on to Step 2: Analyze Results.

Once metrics have been collected, UpSearch’s seasoned experts take a team approach to reviewing your environment’s data.  We leverage extensive SQL Server training, real world experiences and in-depth knowledge of Microsoft’s best practices. This combination of practical know-how allows us to evaluate your systems with a focus on your core issues.

Our analysis targets several key areas:

  • System Configuration
  • Performance Bottlenecks
  • Disaster Recovery and High Availability Risks
  • Security Gaps
  • Other Concerns

We gather all of these items into a comprehensive list of actions designed to meet your business and continuity objectives, as well as secure, unlock, and protect your database.

In summary, the SQL Server Health Check Step 2: Analyze Results helps us prepare our recommendations exclusively for your SQL Server environment.

This blog series continues with Step 3: Report Findings and webinar invitation. At the conclusion of this series, you will have a better understanding of how your organization will benefit from a SQL Server Health Check.

 

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s SQL Server Health Check is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
  1. SQL Server Health Check Introduction
  2. SQL Server Health Check Benefits
  3. SQL Server Health Check Process - Step 1
  4. SQL Server Health Check Process - Step 2
  5. SQL Server Health Check Process - Step 3
  6. SQL Server Health Check Webinar

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Health Check Step 1: Collect Metrics

Do you have SQL Server problems and don’t where to turn for help? SQL Server Health Check Step 1: Collect Metrics describes how we obtain a comprehensive view of your database environment. This blog series is intended to help you decide if a SQL Server audit is right for your organization.

SQL Server Health Check Step 1: Collect Metrics

 

Collect Configuration and Performance Metrics

SQL Server Health Check Step 1: Collect Metrics by UpSearch

Previously, we explored SQL Server Health Check Benefits. Now that the end results are clear, let’s talk about the process used to achieve these benefits. We begin with the end in mind by interviewing key stakeholders. We want to make certain business and continuity objectives are top of mind.

Once we have a clear understanding of your business goals, we use UpSearch’s proven methodology to collect metrics. We run our proprietary scripts to get configuration and performance metrics on just about anything that touches SQL Server.

 

 

We collect information about your server and infrastructure, SQL Server instance and detailed database information. We pay careful consideration to SQL Server performance metrics, including:

  • Host Statistics
  • Storage Configuration
  • SQL Instance Activity and Performance

SQL Server Health Check Step 1: Collect Metrics

And when it comes to Queries and Indexes, we pay special attention to long running and expensive queries, top missing indexes and duplicate indexes.

SQL queries can perform poorly for a variety of reasons. Some factors are how they are written, inadequate hardware, or missing indexes to support the workload. We recognize the importance of identifying queries that perform poorly based on:

  • Number of Executions
  • CPU cost
  • Disk Impact (Reads and Writes)
  • Duration

In summary, the first step of UpSearch’s SQL Server Health Check process is to provide a comprehensive view of your database environment. This process can include multiple instances of SQL Server.

This blog series continues with Step 2: Analyze Results and webinar invitation.  At the conclusion of this series, you will have a better understanding of how your organization will benefit from a SQL Server Health Check.

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s SQL Server Health Check is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
  1. SQL Server Health Check Introduction
  2. SQL Server Health Check Benefits
  3. SQL Server Health Check Process - Step 1
  4. SQL Server Health Check Process - Step 2
  5. SQL Server Health Check Process - Step 3
  6. SQL Server Health Check Webinar

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Health Check Benefits

Do you have SQL Server problems and don’t know where to turn for help? This article, SQL Server Health Check Benefits, as well as the entire blog series will help you decide if a SQL Server audit is right for your organization.

SQL Server Health Check Benefits

 

What are the benefits of a SQL Server Health Check?

SQL Server Health Check Benefits Overview by UpSearch

Previously, we shared a high-level overview in SQL Server Health Check Introduction. Let’s get right to the reason why you are reading this blog series; you want to save time, spend less, reduce risk and improve performance.

Our experience indicates the benefits of a SQL Server Health Check remain consistent regardless of the challenges. You may need a struggling SQL Server to keep up with your growing company. Maybe you recently virtualized and performance hasn’t been the same. Regardless of the problem, you want an expert to tell you what is wrong and specifically how to fix it.

Our deep understanding of SQL Server internals allows us to interpret your configuration and performance metrics. We compare your settings and metrics against our know-how and recommended best practices to produce actionable recommendations customized to your business. What you get is our team’s knowledge working for you, helping you solve your SQL Server problems.

SQL Server Health Check Benefits

  • Solve specific problems that waste time and money
  • Ensure your databases are secure & methodology compliant to reduce risk
  • Identify inefficient query patterns & hardware bottlenecks to improve performance
  • Avoid unnecessary hardware costs by pinpointing server performance issues

In summary, the benefits of a SQL Server Health Check include saving time, reducing both cost and risk, and improving performance.  We will help you get more out of Microsoft SQL Server.

This blog series continues with Step 1: Collect Metrics and and webinar invitation.

 

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s SQL Server Health Check is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
  1. SQL Server Health Check Introduction
  2. SQL Server Health Check Benefits
  3. SQL Server Health Check Process - Step 1
  4. SQL Server Health Check Process - Step 2
  5. SQL Server Health Check Process - Step 3
  6. SQL Server Health Check Webinar

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Health Check Introduction

Do you have SQL Server problems and don’t know where to turn for help? This SQL Server Health Check Introduction and blog series will help you determine if a SQL Server audit is right for your organization.

SQL Server Health Check Introduction

 

What is a SQL Server Health Check?


SQL Server Health Check Introduction by UpSearchA SQL Server Health Check is the first step toward resolving SQL Server problems. The goal is to provide insight and actionable steps to make your SQL Server environment stable, better performing, and more secure.

UpSearch’s SQL Server Health Check process is a proven methodology with three distinct steps; collect key metrics, analyze your environment, and report our findings. We interview key stakeholders, run proprietary scripts and collect configuration and performance metrics on just about anything that touches SQL Server. As we analyze results, core concerns are identified – not just symptoms. UpSearch’s SQL Server Health Check report includes actionable recommendations that enable you to strategically implement maintenance and scalability measures.

In summary, a SQL Server Health Check is the first step toward understanding and resolving SQL Server problems. UpSearch’s process will give you a clear view of where your issues are along with a plan of action to address them.

This blog series continues with a highlight of benefits, process description and webinar invitation. At the conclusion of this series, you will have a better understanding of how your organization will benefit from a SQL Server Health Check.

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s SQL Server Health Check is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
  1. SQL Server Health Check Introduction
  2. SQL Server Health Check Benefits
  3. SQL Server Health Check Process - Step 1
  4. SQL Server Health Check Process - Step 2
  5. SQL Server Health Check Process - Step 3
  6. SQL Server Health Check Webinar

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Audit Toolbox

SQL Server Audit ToolboxOriginally published on ColleenMorrow.com.

SQL Server Audit ToolboxA consolidated repository of various scripts and tools I’ve blogged about.

 

SQL Server Audit Toolbox

 

Related Posts

 

Download the SQL Server Audit Scripts

 

Download - SQL Server Audit Toolbox
First Name*
Last Name*
Email*
Twitter

UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Audit User-Defined Audit Events

SQL Server Audit – User-defined Audit EventsOriginally published on ColleenMorrow.com.

One thing I failed to touch on during my series on SQL Audit was the use of user-defined events in audits. This was brought to my attention in a comment by one of my readers. He was trying to make use of user-defined events and was having a problem getting the output to actually write to the audit file. Since I was writing some code to recreate the problem on my system anyway, I decided to post it here.

Why might you want to create a user-defined event audit in the first place? Well, we already know that we can use SQL Audit to audit access to certain objects. So let’s say we have a table with salary data. We can use the SCHEMA_OBJECT_ACCESS_GROUP to audit access to any object in that schema. We can also use SELECT, INSERT, UPDATE, and DELETE actions to audit those actions on specific objects. But let’s say even getting that granular will produce more audit output than we’d like. Suppose we only want to know when an employee’s salary is increased by more than 10%. We can’t do that with any of the canned actions. But we can do that with a custom event.

Configure the Audit

The first step is to configure the audit object. Once we’ve got that configured we create the audit specification. This can be either a server audit spec or a database audit spec, depending on your needs. Just make sure to add the USER_DEFINED_AUDIT_GROUP action. And don’t forget to enable both the server audit and the audit spec.

USE [master]
GO



CREATE SERVER AUDIT [TestingUserDefinedEvents]
TO FILE
(   FILEPATH = N'D:\SQL2012\Audits'
    ,MAXSIZE = 5 MB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)
GO
ALTER SERVER AUDIT [TestingUserDefinedEvents] WITH (STATE = ON);
GO



USE [AdventureWorks2012]
GO



CREATE DATABASE AUDIT SPECIFICATION [UserDefinedEvents]
FOR SERVER AUDIT [TestingUserDefinedEvents]
ADD (USER_DEFINED_AUDIT_GROUP)
WITH (STATE = ON)
GO

 

Writing to the Audit

To write to the audit log, we’ll use the sp_audit_write stored procedure. This built-in stored procedure accepts 3 parameters:

  • @user_defined_event_id is a smallint used to identify the event
  • @succeeded is a binary flag used to specify whether the action was successful or not
  • @user_defined_information is an nvarchar string describing the event

So, to test our audit, let’s run the following:

USE [AdventureWorks2012]
GO
EXEC sp_audit_write @user_defined_event_id =  27 ,
              @succeeded =  0
            , @user_defined_information = N'Testing a user defined event.' ;

If we check the audit log, we should see the event.

 

Putting it into Practice

Back to our original purpose, we wanted to know whenever an employee’s salary was increased more than 10%. To do this, we can create a trigger. (As I’ve mentioned many times in the past, I’m not a developer, so no fair picking on my trigger code.)

USE AdventureWorks2012
GO



CREATE TRIGGER [humanresources].[SalaryMonitor] ON [humanresources].[employeepayhistory]
AFTER UPDATE
AS
declare   @oldrate money
        , @newrate money
        , @empid integer
        , @msg nvarchar(4000)



select  @oldrate = d.rate
from deleted d



select @newrate = i.rate, @empid = i.BusinessEntityID
from inserted i



IF @oldrate*1.10 < @newrate
BEGIN
    SET @msg = 'Employee '+CAST(@empid as varchar(50))+' pay rate increased more than 10%'
    EXEC sp_audit_write @user_defined_event_id =  27 ,
              @succeeded =  1
            , @user_defined_information = @msg;
END
GO

Now if we test the trigger by virtually doubling employee 4’s rate and only increasing employee 8’s rate by a small amount.

select * from HumanResources.EmployeePayHistory where BusinessEntityID= 4



Update HumanResources.EmployeePayHistory set rate = 59.8462
where BusinessEntityID=4 and RateChangeDate = '2006-01-15 00:00:00.000'



select * from HumanResources.EmployeePayHistory where BusinessEntityID= 8



Update HumanResources.EmployeePayHistory set rate = 41.8654
where BusinessEntityID=8 and RateChangeDate = '2003-01-30 00:00:00.000'

We should see an audit record for employee 4 in the output, and we do.

 

You might be wondering if you can configure an audit to only capture specific user-defined event IDs.  Great question, and you can by filtering on the user_defined_event_id field in the server audit.

So there you have it, another way to tailor SQL Audit a bit more to your specific needs. Happy auditing!

 

UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Audit 302 Deploying Audit Solution with Powershell

Auditing doesn’t have to be scary. SQL Server Audit 302 – Deploying Audit Solution with Powershell is part of a blog series designed to help you audit database changes with SQL Server Audit. Contact us if you have any questions about how to select and implement the right auditing solution for your organization.

SQL Server Audit 302 – Deploying Audit Solution with Powershell

SQL Server Audit 302 – Deploying Audit Solution with PowershellOriginally published on ColleenMorrow.com.

This is it, ladies and gentlemen. One last post and I promise I won’t mention the word audit for at least a week.

Before we begin, let’s take a moment to recap what we’ve covered so far:

For the final installment of this series, we’re going to take everything we’ve learned and put it all together.

Premise

In case you haven’t been paying attention, I really don’t like pointing and clicking my way through a GUI to do, well, just about anything. For any task that needs to be done more that once, I’d much rather spend the time upfront automating it, in order to save time later. Besides, automating tasks is fun! So it only makes sense that I’d want to use the same approach to auditing.

In SQLAudit 201, we created a SQLAudit database to act as our central audit repository. That’s where we’re storing the info on our current audits, and importing data from our audit files into a table for permanent storage and reporting.

The other thing we’re going to use that SQLAudit database for is to hold our “master” database audit specifications. These “master” specs will be used as templates to deploy auditing to other databases/instances. How, you ask? By using PowerShell, of course!

How it Works

I’ve split the work into 2 scripts: one to deploy an audit, one to remove an audit. Both can be executed interactively or in batch mode.

DeployAudit.ps1

Parameters

  • Target Instance – where to deploy audit to
  • Target Database – database to deploy audit to; null for a server audit
  • Target Login – Login with permissions to manage audits
  • Target Password
  • Audit Specification – name of the master audit specification to be deployed
  • UpdateAudit – y/n flag to overwrite an existing audit object if found
  • UpdateSpec – y/n flag to overwrite existing audit specification if found

If executed interactively, the script will prompt the user for target instance and database, login and password. It will connect to master instance and list available audit specs in the SQLAudit database and any server audit specs starting with “master”. The user is then asked to specify an audit to deploy. The script verifies that the user specified a valid master audit specification and determines whether the selected spec is databsse or server audit specification.

The script then connects to target instance, and determines if the target audit object exists. If it doesn’t, the audit object is created and a record is inserted into the SQLAudit.dbo.AuditLocator table. If the audit does exist, the user is prompted to overwrite definition. This is useful if you’ve modified the location or file options in the master audit object and you want to propagate those changes. If the path to the audit file has been changed, the AuditLocator table will be updated accordingly.

If a database audit specification was selected, the script will connect to the target database and check for a pre-existing spec. If one is found, the user is asked if the apec should be overwritten. If yes, the specification will be dropped and recreated. (There didn’t seem to be any sense to writing a separate function to update an audit specification.) Otherwise, the new specification is created. The same general logic is used for a server audit specification.

RemoveAudit.ps1

Parameters

  • Instance Name – name of the instance you want to stop auditing
  • Database Name – name of database being audited
  • Audit Specification Name – name of audit specification to remove.
  • Target Login
  • Target Password

If run interactively, the user is prompted for the target instance and database, login and password. The script connects to target instance and lists all existing server audit specs and any database audit specs in specified database. It then prompts the user for the audit spec to remove. The script verifies the selected spec name is valid, and, if so, deletes that specification. If that was the last audit specification associated with that audit object, the audit object is also dropped and the record in AuditLocator is updated to mark the audit as inactive.

Download the Scripts

I’ve packaged up my scripts into one handy-dandy zip file for your downloading pleasure.

SQLAuditScripts.zip contains:

  • SQLAuditDatabase.sql – Script to create the SQLAudit database and its objects.
  • Library-ManageAudits.ps1 – Library of PowerShell functions to drop, create, and update audit objects and audit specs.
  • DeployAudit.ps1 – Powershell script for deploying an audit.
  • RemoveAudit.ps1 – Powershell script for removing an existing audit.

Download - SQL Server Audit 302 Deploying the Audit Solution with Powershell
First Name*
Last Name*
Email*
Twitter

Additional Tools You’ll Need

I’ve mentioned these before, in my Inventory scripts. If you haven’t already, you’ll need to download a couple of PowerShell functions.

Name: Write-DataTable.ps1

Author: Chad Miller

Loads data into from a datatable object into SQL Server tables

Name: Out-DataTable.ps1

Author: Chad Miller

Formats input into a datatable object which can then be imported into SQL Server using Write-DataTable.

Sample Deployment

Let’s say we want to audit DDL (a common theme for me). Start by creating a Master_DDLAudit audit object on whatever instance holds the SQLAudit database. Use a file output, specifying a UNC path to our central audit folder. This is where all our audits will write to, so make sure it’s accessible by all your potential target instances. We’re not actually auditing with this master audit, so leave it disabled.

Next create an audit specification, Master_Database_DDLAudit, in the SQLAudit database. Specify the SCHEMA_OBJECT_CHANGE_GROUP action, and assign the spec to the Master_DDLAudit audit. Again, leave the specification disabled.

Suppose we also want to create a security audit to monitor changes to logins and server-level permissions. These are instance-level events, so we need to create a server audit specification, rather than a database audit spec. I don’t want this output going to the same file as my DDL audit, so I’ll create a new audit object, Master_SecurityAudit. Then I’ll create a server audit spec called Master_Server_SecurityAudit.

Now if I want to deploy the DDL audit to my AdventureWorks2012 database, I simply run the DeployAudit script.

DeployAudit

If I check my AuditLocator table, I see the audit has been added as an active audit.

AuditLocator

To stop auditing DDL on AdventureWorks, I use the RemoveAudit script.

RemoveAudit

Since this was the only audit specification attached to this audit, a select on my AuditLocator shows that the audit is now marked as inactive. The audit object has also been removed from the instance.

AuditLocator2

Conclusion

That concludes this series on SQL Audit. I hope you’ve enjoyed it as much as I have and I really hope you’ve found it helpful for your own auditing needs. Please don’t hesitate to contact me with any questions or problems you run into with the scripts.

SQL Server Audit Series

This blog series was designed to help you audit database changes.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization with SQL Server Audit.
  1. SQL Server Auditing – Getting Started
  2. SQL Server Audit 101 – Creating Basic Audit
  3. SQL Server Audit 102 – Reading Audit Output 
  4. SQL Server Audit 201 – Creating Audit Solution
  5. SQL Server Audit 301 – Using PowerShell to Manage Audits
  6. SQL Server Audit 302 – Deploying Audit Solution with PowerShell
UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Audit 301 Using Powershell to Manage Audits

Auditing doesn’t have to be scary. SQL Server Audit 301 – Using Powershell to Manage Audits is part of a blog series designed to help you audit database changes with SQL Server Audit. Contact us if you have any questions about how to select and implement the right auditing solution for your organization.

SQL Server Audit 301 – Using Powershell to Manage Audits

SQL Server Audit 301 – Using Powershell to Manage AuditsOriginally published on ColleenMorrow.com.

Today we’re going to go over some very basic scripts to create, drop, and copy SQL Audit objects using Powershell and SMO. Managing SQL Audit objects via PowerShell is actually pretty simple, even for a newbie like me. And I’m proud to say that these might be the first PowerShell scripts I’ve written that were entirely my own and not based on someone else’s work. I might actually be learning something! Nah, probably not. :-)

Creating an Audit Object

The first step in implementing SQL Audit is to create the audit object, so that’s where we’ll start. Let’s look at the whole script and then break it down.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instance = 'MyServer'
$auditName = $instance+"_TestAudit"
$auditDir = '\\MyServer\D$\Audits\'

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance

$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName")
$newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
$newAudit.FilePath = $auditDir
$newAudit.MaximumRolloverFiles = 10
$newAudit.MaximumFileSize = 100
$newAudit.QueueDelay = 1000
$newAudit.Create()
$newAudit.Enable()

The first thing we’re doing is simply declaring some variables to hold our instance name, the name of the audit we want to create, and the folder where we want our audit file to be written. For re-usability, we could even make these into parameters, but I wanted to keep this simple. Next we create a new SMO connection to our instance with the command

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance

Once we’re connected to SQL Server, we can create a new audit class object and start assigning attribute values. Here, we’re setting the destination to a file, and the file path to our $auditDir variable. We set the maximum number of rollover files, the queue delay, etc. All of the available properties can be found here: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.audit.aspx, but everything I’m setting here should look very familiar if you’ve been following along in this series.

$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName")
$newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
$newAudit.FilePath = $auditDir
$newAudit.MaximumRolloverFiles = 10
$newAudit.MaximumFileSize = 100
$newAudit.QueueDelay = 1000

And once we’ve got all of our properties set, we do the PowerShell equivalent of clicking OK: we create the audit and enable it.

$newAudit.Create()
$newAudit.Enable()

Want to make sure our audit was created? We can list all of our audit objects like this:

$instance = 'MyServer'
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
foreach ($a in $srv.Audits)
{
   Write-Host $a.Name
}

Creating an Audit Specification

Once we’ve got our audit object created, we need to create the audit specification. For this example, we’ll create a database audit specification, rather than a server audit specification, though there are only slight differences between the two. In this particular audit spec, we’ll audit DDL changes to our AdventureWorks database and we’ll audit the execution of a stored procedure call usp_ChangeEmpSalary. As before, let’s see the whole script and break it down.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instance = 'MyServer'
$database = 'AdventureWorks'
$auditName = $instance+"_TestAudit"
$specName = $instance+"_"+$database+"_AuditSpec"

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item($database)

## Set audit spec properties
$AuditSpec = new-object Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification($db, $specName)
$AuditSpec.AuditName = "$auditName"

## Set audit actions
$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("SchemaObjectChangeGroup")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)

$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("EXECUTE","OBJECT","dbo","usp_ChangeEmpSalary","public")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)

## Create and enable audit spec
$AuditSpec.Create()
$AuditSpec.Enable()

Just like in the previous script we start out by defining our variables for instance, database, the audit we’re assigning this spec to, and the name of the new spec. Then we create our SMO objects for our server and database. Now we can get down to business. We start by creating a DatabaseAuditSpecification class object and setting its AuditName property to the name of the audit object we’re assigning this spec to.

## Set audit spec properties
$AuditSpec = new-object Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification($db, $specName)
$AuditSpec.AuditName = "$auditName"

Note that, if we wanted to create a server audit specification, we would use the ServerAuditSpecification class:

$AuditSpec = new-object Microsoft.SqlServer.Management.Smo.ServerAuditSpecification($srv, $specName)

Ok, so we’ve got our audit spec named and assigned, now we need to tell it what to audit. This is a two-step process in SMO, first we create an AuditSpecificationDetail object, and then we add it to the audit spec. I used these two actions to give you an idea of how to add an action group vs an individual action with more configuration options.

## Set audit actions
$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("SchemaObjectChangeGroup")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)

$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("EXECUTE","OBJECT","dbo","usp_ChangeEmpSalary","public")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)

And finally, we create the audit specification and enable it.

## Create and enable audit spec
$AuditSpec.Create()
$AuditSpec.Enable()

Dropping Audits and Audit Specifications

Dropping an audit or audit spec is even easier than creating one. You find the one that matches the name you’re looking for, you disable it and drop it. In fact, it’s so straightforward that I’m not going to step through this one. If you’ve been following along so far, you’ll see what I’m doing here.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instance = 'MyServer'
$database = 'AdventureWorks'
$auditName = $instance+"_TestAudit"
$specName = $instance+"_"+$database+"_AuditSpec"

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item($database)

## Delete the audit spec
$auditSpec = $db.DatabaseAuditSpecifications | where {$_.Name -match $specName}

if ( $auditSpec.Name -eq $null )
{
	Write-Host "Database Audit Spec $specName does not exist."
}
else
{
	$auditSpec.Disable()
	$auditSpec.Drop()
	Write-Host "Database Audit Spec $specName dropped."
}

## Delete the audit object
$delAudit = $srv.Audits | where {$_.Name -match $auditName}

if ( $delAudit.Name -eq $null )
{
	Write-Host "Audit $auditName does not exist."
}
else
{
	$delAudit.Disable()
	$delAudit.Drop()
	Write-Host "Audit $auditName dropped."
}

Copying an Audit Specification

The last activity I want to cover today is copying an audit specification from one database to another. This way we can define a “master” audit spec to use as a template for deployment to other databases/instances. Let’s imagine I have a database called SQLAudit where I’ve created such a template. And I want to copy that audit spec definition to AdventureWorks.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instance = 'MyServer'
$masterDB = 'SQLAudit'
$masterSpec = "Master_DB_AuditSpec"

$targetDB = 'AdventureWorks'
$targetSpec = $instance+"_"+$database+"_AuditSpec"

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$mdb = New-Object Microsoft.SqlServer.Management.Smo.Database
$mdb = $srv.Databases.Item($masterDB)

$mAuditSpec = $mdb.DatabaseAuditSpecifications | where {$_.Name -match $masterSpec}

if ( $mauditSpec.Name -eq $null )
{
	Write-Host "Master Database Audit Spec $masterSpec does not exist."
}
else
{

	$tdb = New-Object Microsoft.SqlServer.Management.Smo.Database
	$tdb = $srv.Databases.Item($targetDB)

	$newAuditSpec = new-object Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification($tdb, $targetSpec)
	$newAuditSpec.AuditName = $mauditSpec.AuditName
	$newAuditSpec.AddAuditSpecificationDetail($mauditSpec.EnumAuditSpecificationDetails())
	$newAuditSpec.Create()
	$newAuditSpec.Enable()

	Write-Host "Database Audit Spec $targetSpec created."

}

The only somewhat tricky part of copying an audit spec definition is that you can’t directly copy the actions. You need to use the EnumAuditSpecificationDetails function to assign the details from the original specification to the new one. Everything else is easy.

What’s Next?

Now that we’ve got the basics of managing audits using PowerShell under our belts, we’re going to use these skills to deploy the audit solution I talked about previously. That’s what we’ll do next in SQL Server Audit 302 – Deploying Audit Solution with PowerShell. Good stuff people!!

SQL Server Audit Series

This blog series was designed to help you audit database changes.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization with SQL Server Audit.
  1. SQL Server Auditing – Getting Started
  2. SQL Server Audit 101 – Creating Basic Audit
  3. SQL Server Audit 102 – Reading Audit Output 
  4. SQL Server Audit 201 – Creating Audit Solution
  5. SQL Server Audit 301 – Using PowerShell to Manage Audits
  6. SQL Server Audit 302 – Deploying Audit Solution with PowerShell
UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Audit 201 Creating Audit Solution

Auditing doesn’t have to be scary. SQL Server Audit 201 – Creating Audit Solution is part of a blog series designed to help you audit database changes with SQL Server Audit.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization.  

SQL Server Audit 201 – Creating Audit Solution

SQL Server Audit 201 – Creating Audit SolutionOriginally published on ColleenMorrow.com.

Welcome back, folks!  Ok so, now that we’ve covered the basics of creating a SQL Audit and viewing the output, let’s put the pieces together to form a complete solution. As with any implementation, the key to success lies in careful planning. Before we take off running, we need to take a moment and figure out exactly where we want to go.

My requirements

  • What do I want to audit? DDL changes, excluding specific objects and excluding index/statistics maintenance events.
  • Where do I want to run the audit? AdventureWorks, with the option to audit additional databases in the future.
  • Where do I want the output to go? All audit data should be stored in a central audit database for archiving and reporting.
  • How will the audit output be processed? I’ll be using SSRS to generate reports.

Design

Now, there are a few ways I could do this, depending on what version of SQL Server I’m using. If I’m running SQL 2008, unless I want to audit all my databases, I’m limited to creating a Database Audit Specification in AdventureWorks to track the DDL changes. I would also have no way to eliminate index maintenance events from the audit, so those would need to be processed out later. However, if I’m running SQL 2012, I have the option of creating a Server Audit Specification and using a filter on the audit to limit my auditing to AdventureWorks DDL events and exclude any index maintenance commands. Like this:

CREATE SERVER AUDIT [DDLAudit]
TO FILE
(	FILEPATH = N'D:\SQL2012\Audits\'
	,MAXSIZE = 100 MB
	,MAX_ROLLOVER_FILES = 10
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 0
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = 'd50cf1ad-2927-44c7-afd0-0c31d302ca35'
)
WHERE ([database_name]='AdventureWorks' AND NOT [statement] like 'ALTER INDEX%REBUILD%' AND NOT [statement] like 'ALTER INDEX%REORGANIZE%')
GO

In order to maintain backward compatibility to SQL 2008, I’m going to avoid using the filter for this demo.

CREATE SERVER AUDIT [DDLAudit]
TO FILE
(	FILEPATH = N'D:\SQL2012\Audits\'
	,MAXSIZE = 100 MB
	,MAX_ROLLOVER_FILES = 10
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 0
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = 'd50cf1ad-2927-44c7-afd0-0c31d302ca35'
)
GO
USE AdventureWorks
GO
CREATE DATABASE AUDIT SPECIFICATION [AdventureWorks_DB_DDLAudit]
FOR SERVER AUDIT [DDLAudit]
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

Ok, so that takes care of my first two requirements. The next item on my wish list was to have my audit records stored to a centralized audit database. Right now, that isn’t happening, all my audit info is being written to .sqlaudit files on my D drive. So my next order of business is to build a database repository to hold them.

I’ll create a centralized SQLAudit database. Ideally would be isolated from whatever instance I’m auditing, but in this case it’s located on the same instance. In this audit database I’ll create a handful of tables.

  • AuditExclude – list of all objects I want excluded from my audit output.
  • AuditStage – this is the staging table for my audit records, before filtering; The contents of my audit file(s) will be pulled directly into this table.
  • AuditRecord – final storage for filtered/processed audit output.
  • AuditLocator – stores current audit file and offset to be used as starting point for next run; By knowing where I left off, I won’t end up re-processing audit records.
  • AuditLoadLog – run log; This just keeps track of the number of records staged and retained from each run.
USE [SQLAudit]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditRecord]') AND type in (N'U'))
DROP TABLE [dbo].[AuditRecord]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditStage]') AND type in (N'U'))
DROP TABLE [dbo].[AuditStage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditLocator]') AND type in (N'U'))
DROP TABLE [dbo].[AuditLocator]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditLoadLog]') AND type in (N'U'))
DROP TABLE [dbo].[AuditLoadLog]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditExclude]') AND type in (N'U'))
DROP TABLE [dbo].[AuditExclude]
GO

CREATE TABLE [dbo].[AuditExclude](
	[InstanceName] [nvarchar](128) NULL,
	[DatabaseName] [varchar](50) NULL,
	[SchemaName] [sysname] NOT NULL,
	[ObjectName] [varchar](50) NULL,
	[ObjectType] [varchar](50) NULL,
	[Reason] [varchar](100) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuditRecord](
	[audit_name] [varchar](128) NOT NULL,
	[event_time] [datetime2](7) NOT NULL,
	[sequence_number] [int] NOT NULL,
	[action_id] [varchar](4) NULL,
	[succeeded] [bit] NOT NULL,
	[permission_bitmask] [bigint] NOT NULL,
	[is_column_permission] [bit] NOT NULL,
	[session_id] [smallint] NOT NULL,
	[server_principal_id] [int] NOT NULL,
	[database_principal_id] [int] NOT NULL,
	[target_server_principal_id] [int] NOT NULL,
	[target_database_principal_id] [int] NOT NULL,
	[object_id] [int] NOT NULL,
	[class_type] [varchar](2) NULL,
	[session_server_principal_name] [nvarchar](128) NULL,
	[server_principal_name] [nvarchar](128) NULL,
	[server_principal_sid] [varbinary](85) NULL,
	[database_principal_name] [nvarchar](128) NULL,
	[target_server_principal_name] [nvarchar](128) NULL,
	[target_server_principal_sid] [varbinary](85) NULL,
	[target_database_principal_name] [nvarchar](128) NULL,
	[server_instance_name] [nvarchar](128) NULL,
	[database_name] [nvarchar](128) NULL,
	[schema_name] [nvarchar](128) NULL,
	[object_name] [nvarchar](128) NULL,
	[statement] [nvarchar](4000) NULL,
	[additional_information] [nvarchar](4000) NULL,
	[file_name] [nvarchar](260) NOT NULL,
	[audit_file_offset] [bigint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuditStage](
	[audit_name] [varchar](128) NOT NULL,
	[event_time] [datetime2](7) NOT NULL,
	[sequence_number] [int] NOT NULL,
	[action_id] [varchar](4) NULL,
	[succeeded] [bit] NOT NULL,
	[permission_bitmask] [bigint] NOT NULL,
	[is_column_permission] [bit] NOT NULL,
	[session_id] [smallint] NOT NULL,
	[server_principal_id] [int] NOT NULL,
	[database_principal_id] [int] NOT NULL,
	[target_server_principal_id] [int] NOT NULL,
	[target_database_principal_id] [int] NOT NULL,
	[object_id] [int] NOT NULL,
	[class_type] [varchar](2) NULL,
	[session_server_principal_name] [nvarchar](128) NULL,
	[server_principal_name] [nvarchar](128) NULL,
	[server_principal_sid] [varbinary](85) NULL,
	[database_principal_name] [nvarchar](128) NULL,
	[target_server_principal_name] [nvarchar](128) NULL,
	[target_server_principal_sid] [varbinary](85) NULL,
	[target_database_principal_name] [nvarchar](128) NULL,
	[server_instance_name] [nvarchar](128) NULL,
	[database_name] [nvarchar](128) NULL,
	[schema_name] [nvarchar](128) NULL,
	[object_name] [nvarchar](128) NULL,
	[statement] [nvarchar](4000) NULL,
	[additional_information] [nvarchar](4000) NULL,
	[file_name] [nvarchar](260) NOT NULL,
	[audit_file_offset] [bigint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuditLocator](
	[audit_name] [varchar](128) NULL,
	[file_name] [nvarchar](260) NOT NULL,
	[audit_file_offset] [bigint] NOT NULL,
	[file_pattern] [nvarchar](260) NULL,
	[locator_id] int identity(1,1) not null,
	[active] char(1) default 'Y'
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuditLoadLog](
	[audit_name] [varchar](128) NULL,
	[staged_count] [int] NOT NULL,
	[saved_count] [int] NOT NULL,
	[run_date] datetime DEFAULT getdate()
) ON [PRIMARY]
GO

Now, since I already have an audit in place that I’d like to load, I’ll seed the AuditLocator table with that information.

USE master
GO
declare @initfile nvarchar(260)
select @initfile=log_file_path+ log_file_name from sys.server_file_audits where name = 'DDLAudit'
set @initfile = STUFF(@initfile,len(@initfile)-charindex('.',reverse(@initfile)), 1, '*')
Insert into SQLAudit.dbo.AuditLocator  (audit_name, file_name, audit_file_offset, file_pattern)
SELECT top 1 'DDLAudit', file_name, audit_file_offset, @initfile FROM fn_get_audit_file (@initfile, default,  default) order by event_time asc

Now that I’ve got someplace to store my audit data permanently, I need a way to get it out of external file(s) and into my database. So I’ll also be creating a stored procedure to read my audit files, massage the data, and save it into my AuditRecord table. That would be the LoadAuditData procedure. Let’s take a look.

USE SQLAudit
GO
 create procedure LoadAuditData
as
begin
	declare @audit varchar(128),
			@file nvarchar(260),
			@offset bigint,
			@pattern nvarchar(260),
			@staged int,
			@saved int

	set nocount on

	declare cAudits cursor for
		select audit_name, file_name, audit_file_offset, file_pattern
		from AuditLocator
		where active = 'Y'
	FOR UPDATE

The first thing I’m going to do is find all the active audits in my AuditLocator table and grab the audit file location, the current file and the current offset. I decided that, if I discontinue or move an audit for some reason, rather then deleting it from the AuditLocator table, I would just mark it inactive. That way I’ll have a historical record for my reference.

open cAudits
	fetch cAudits into @audit, @file, @offset, @pattern
	while @@fetch_status = 0
	begin

		set @staged = 0
		set @saved = 0

		insert into AuditStage
		SELECT @audit, * FROM fn_get_audit_file (@pattern, @file,  @offset)

		set @staged = @@rowcount

		insert into AuditRecord
		SELECT * from AuditStage a
		WHERE NOT EXISTS (SELECT 1 FROM dbo.AuditExclude ae WHERE
				a.server_instance_name = ae.InstanceName and
				a.database_name = ae.DatabaseName and
				a.schema_name = ae.SchemaName and
				a.object_name = ae.ObjectName)
		and statement not like '%STATISTICS%'
		and statement NOT LIKE 'ALTER INDEX%REBUILD%'
		and statement NOT LIKE 'ALTER INDEX%REORGANIZE%'

		set @saved = @@rowcount

For each of my active audits, I read in all audit records, starting with the file and offset I pulled out of AuditLocator. All audit records are loaded into AuditStage, then I pull only the records I care about into AuditRecord. In this case, I’m eliminating any statistics or index maintenance statements, and anything dealing with my excluded objects.

select top 1 @file=file_name, @offset=audit_file_offset from AuditStage order by event_time desc

		update AuditLocator set file_name = @file, audit_file_offset = @offset
		where current of cAudits

The next step is to grab the most current record from the AuditStage table, and that’s going to be my jumping off point for the next run. This was really the major purpose of the staging table. I could easily have loaded the audit records directly from my audit file(s) into AuditRecord, filtering at the same time. But what if none of the records met my criteria? I wouldn’t have any way to update my AuditLocator record and I’d end up re-processing the same records next time. This way, I know I processed this set of records and I know none of them met my criteria, so I can move on to the next set.

       insert into AuditLoadLog (audit_name, staged_count, saved_count) values (@audit, @staged, @saved)
		DELETE AuditStage
		fetch cAudits into @audit, @file, @offset, @pattern
	end
	close cAudits
	deallocate cAudits
end

Finally I log my record counts into the AuditLoadLog table and clear out my staging table for the next run.

Reviewing my requirements

  • What do I want to audit? DDL changes, excluding specific objects and excluding index/statistics maintenance events.
    • Using the SCHEMA_OBJECT_CHANGE_GROUP I’ll record the DDL changes I’m interested in. I’m not filtering the audit, but I’m able to filter the output before it gets loaded into the AuditRecord table, using procedure logic and the AuditExclude table.
  • Where do I want to run the audit? AdventureWorks, with the option to audit additional databases in the future.
    • I’m using a database audit specification to audit only AdventureWorks right now. In the future, I could create additional audits and simply add their information to the AuditLocator table for processing. As long as my audit files are on an accessible network share, I’m good to go.
  • Where do I want the output to go? All audit data should be stored in a central audit database for archiving and reporting.
    • Got it. Right now that database is on the same local server, but it could easily be on a dedicated remote instance, away from prying eyes.
  • How will the audit output be processed? I’ll be using SSRS to generate reports.
    • Because I’m loading my data into a single database, creating SSRS reports to view audit data for a particular database, or to track changes across audits, will be a snap.

Considerations

This audit solution has 2 things working against it: the lack of filtering in the audit and the use of rollover files. Let’s say I size my audit files really small and I set a low limit for my maximum rollover files, say 3 files. I’ve made a few DDL changes and they get logged to File 1. Then let’s say I kick off a big index maintenance or update stats job, and the audit records from that job fills up the rest of File 1, File 2, and File 3. Once File 3 is full, File 1 will be deleted and File 4 will be created. If I haven’t processed my audit files in a while, it’s possible that valid audit records in File 1 could be lost. So it’s important to size your files appropriately for the amount of audit data you expect, keep a good number of rollover files, and, most importantly, process your files often.

What’s next?

What if I want to deploy SQLAudit to a number of instances/databases? I could point and click my way through it, but that’s asking for a typo or configuration mistake. I could script it in T-SQL, but that’s so last year. So how about we use PowerShell? In SQL Server Audit 301 – Using PowerShell to Manage Audits, we’ll go over ways to create, drop, and manage SQL Audit via PowerShell and we’ll use a master audit as a template for deploying audits to the instance/database of our choosing.

SQL Server Audit Series

This blog series was designed to help you audit database changes.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization with SQL Server Audit.
  1. SQL Server Auditing – Getting Started
  2. SQL Server Audit 101 – Creating Basic Audit
  3. SQL Server Audit 102 – Reading Audit Output 
  4. SQL Server Audit 201 – Creating Audit Solution
  5. SQL Server Audit 301 – Using PowerShell to Manage Audits
  6. SQL Server Audit 302 – Deploying Audit Solution with PowerShell
UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Audit 102 Reading Audit Output

Auditing doesn’t have to be scary.  SQL Server Audit 102 – Reading Audit Output is part of a blog series designed to help you audit database changes with SQL Server Audit.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization.  

SQL Server Audit 102 – Reading Audit Output

SQL Server Audit 102 – Reading Audit OutputOriginally published on ColleenMorrow.com.

In SQL Server Audit 101 – Creating Basic Audit, we went over the basics of creating a SQL Audit. Now obviously once you’ve gotten your audit in place, you’re going to want to look at the output once in a while, right? Right. So that’s what we’re going to go over today.

If you’re using the default file output for your audit, you have two options for reading your audit output: the log viewer and the fn_get_audit_file function.

Log Viewer

We briefly touched on using the log viewer last time, but in case you missed that post, you can view the audit logs by right-clicking on the Audit object and selecting View Audit Logs. The nice thing about the log viewer is that it’s convenient for taking a quick look at your most recent audit records, without having to know the exact path and file name of your current audit file. On the downside, you’re limited to the most recent 1000 records, so if you’ve got a busy system generating a lot of audit records, you might miss something. And you really can’t run reports or archive records using the Log Viewer, now can you? So, if you’re going to use audit files and do some serious auditing, you’ll want a more powerful tool.

fn_get_audit_file

Fortunately, we have that tool in the fn_get_audit_file function. The great thing about this function is that it allows us to treat the audit output file like a table; so we can search, filter and order our audit records like any other data. We can insert it into a table for archival and reporting purposes, and we can join it with other audit files to find trends in our audit data. And, unlike the Log Viewer, we’re not limited in the number of records we can view.

Using fn_get_audit_file

The fn_get_audit_file function accepts 3 parameters:

  • file_pattern – The first parameter is the file pattern, which specifies the path and file name of the audit file(s) to be read. You have to specify a path and a file name, though the file name can be or include a wildcard. So, for example, acceptable values would be ‘d:\myAudits\MyAudit*.sqlaudit’ or ‘\\Myserver\d$\myAudits\*’. You can also specify a specific file name, if that’s the only one you want to read.
  • initial_file_name – The second parameter is the initial file name. Suppose there were multiple files in d:\myAudits that started with MyAudit*, but I didn’t want to process them all. I could use this parameter to tell SQL Server which file to start with, and it will read that file and the remaining files after it.
  • audit_record_offset – This last parameter is used in conjunction with the initial file name to tell SQL Server where in that initial file to start. This comes in handy when you’ve already processed some records in that initial file, and you just want to pick up where you left off.

Examples

Let’s look at some examples using the DDLAudit audit I created last week. We’ll start with a basic query, reading in all the records in all the files we’ve accumulated so far.

select * from fn_get_audit_file ('D:\SQL2012\Audits\DDLAudit*.sqlaudit', DEFAULT, DEFAULT)

In my case, I only get four records returned, but that’s ok for this demo. If I scroll over to the file_name and audit_file_offset columns, I can make a note of my last audit record so far.

Get-audit-offset

I’ll create and drop a table in AdventureWorks2012 to generate a couple of audit records.

Use AdventureWorks2012
GO
CREATE TABLE myAuditTest2 (col1 int);
GO
DROP TABLE myAuditTest2;
GO

Now, if I run that same basic query again, I’ll get the new audit records in addition to the old records I’ve already viewed. But, if I use the initial_file_name and audit_file_offset parameter to tell SQL Server where I left off last time, I’ll only get the new records.

select * from fn_get_audit_file
   ('D:\SQL2012\Audits\DDLAudit*.sqlaudit',
    'D:\SQL2012\Audits\DDLAudit_D50CF1AD-2927-44C7-AFD0-0C31D302CA35_0_129861627977120000.sqlaudit',
    5632)

Use-audit-offset

If we only wanted to see object creation records, and we wanted to know the owner of the database where the event took place, we could use the following:

select p.name, a.database_name, a.schema_name, a.object_name, a.statement
from fn_get_audit_file
   ('D:\SQL2012\Audits\DDLAudit*.sqlaudit',
    'D:\SQL2012\Audits\DDLAudit_D50CF1AD-2927-44C7-AFD0-0C31D302CA35_0_129861627977120000.sqlaudit',
    5632) a
join sys.databases d on a.database_name = d.name
join sys.server_principals p on p.sid = d.owner_sid
where action_id = 'CR'

What’s Next?

Now that we’ve covered the basics of creating an audit and reading its output, we can put this knowledge to use creating a solid auditing solution for our SQL Server instance. That’s what we’ll do next in SQL Server Audit 201 – Creating an Audit Solution.

SQL Server Audit Series

This blog series was designed to help you audit database changes.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization with SQL Server Audit.
  1. SQL Server Auditing – Getting Started
  2. SQL Server Audit 101 – Creating Basic Audit
  3. SQL Server Audit 102 – Reading Audit Output 
  4. SQL Server Audit 201 – Creating Audit Solution
  5. SQL Server Audit 301 – Using PowerShell to Manage Audits
  6. SQL Server Audit 302 – Deploying Audit Solution with PowerShell
UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.