The second Tuesday of January 2016 is now upon us and you know what that means. Well, I hope you know what that means.
It is time for TSQL Tuesday. It is now the 74th edition of this monthly blog party. This month the host is Robert Davis (blog | twitter) and he has asked us to “Be the change”. Whether the inspiration for this topic is the new year and resolutions, or Ghandi (you must be the change), or CaddyShack (be the ball), we will be discussing “Change.”
Specifically, Robert requested that we discuss data changes and anything relating to data changes. Well, I am going to take that “anything” literally and stretch the definition of changing data just a bit. It will all make sense by the end (I hope).
Ch-ch-changes
Changes happen on a constant basis within a database. Data will more than likely be 
changing. Yes, there are some exceptions to that, but the expectation that data is changing is not an unreal expectation.
Where that expectation becomes unwanted is when we start talking about the data that helps drive the configuration of the server. Ok, technically that is a setting or configuration option or a button, knob, whirlygig or thingamajig. Seldom do we really think about these settings as data. Think about it for a moment though. We can certainly derive some data about these changes (if these settings themselves are not actually data).
So, while you may call it settings changes, I will still be capturing data about the changes. Good? Good! Another term for this is auditing. And auditing applies to all levels including ETL processes and data changes etc. By that fortune, I just covered the topic again – tangentially.
How does one audit configuration changes? Well, there are a few different methods to do this. One could use a server side trace, SQL audit, Extended Events or (if somebody wants to) a custom solution not involving any of those using some sort of variation of tsql and error log monitoring. The point is, there are options. I have discussed a few options for the custom solution path as well as (recently published article using…) the default trace path. Today I will dive into what it looks like via SQL Audit.
When creating an audit to figure out what changes are occurring within the instance, one would need to utilize the SERVER_OPERATION_GROUP action audit group. This action group provides auditing of the following types of events:
- Administer Bulk Operations
- Alter Settings
- Alter Resources
- Authenticate
- External Access
- Alter Server State
- Unsafe Assembly
- Alter Connection
- Alter Resource Governor
- Use Any Workload Group
- View Server State
From this group of events, we can guess at the types of actions that might trigger one of these events to fire for the audit. Some of the possible actions would be:
| Action | Example |
| Issue a bulk administration command | BULK INSERT TestDB.dbo.Test1 FROM ‘c:\database\test1.txt’; |
| Issue an alter connection command | KILL 66 |
| Issue an alter resources command | CREATE RESOURCE POOL PrimaryServerPool WITH {} |
| Issue an alter server state command | DBCC FREEPROCCACHE |
| Issue an alter server settings command | Perform sp_configure with reconfigure |
| Issue a view server state command | SELECT * FROM sys.dm_xe_session_targets |
| Issue an external access assembly command | CREATE ASSEMBLY SQLCLRTest FROM ‘C:\MyDBApp\SQLCLRTest.dll’ WITH PERMISSION_SET = EXTERNAL_ACCESS; |
| Issue an unsafe assembly command | CREATE ASSEMBLY SQLCLRTest FROM ‘C:\MyDBApp\SQLCLRTest.dll’ WITH PERMISSION_SET = UNSAFE; |
| Issue an alter resource governor command | ALTER RESOURCE GOVERNOR DISABLE |
| Authenticate | see view server state vsst type occurs for auth events |
| Use any workload group | See Resource Governor |
This is quite a bit of interesting information. All of these events can be audited from the same audit group. The interesting ones of this bunch are the ones that indicate some sort of change has occurred. These happen to be all but the “Authenticate”, “View Server State” and “Use any workload Group” events even though these events may be stretched to say something has changed with them as well.
With all of that in mind, I find the the “alter server settings” event to be the most problematic. While it does truly capture that something changed, it does not completely reveal to me what was changed – just that a reconfigure occurred. If a server configuration has changed, I can capture the spid and that reconfigure statement – sure. Once that is captured, I now have to do something more to figure out what configuration was “reconfigured”. This is highly frustrating.
Here’s an example from the audit I created:
This is only a small snippit of the results. I can see who made the configuration change, the time, the spid, the source machine etc. I just miss that nugget that tells me the exact change that was made. At least that is the case with the changes made via sp_configure. There are fixes for that – as previously mentioned.
Here is another bit of a downside. If you have the default trace still running, a lot of this information will be trapped in that trace. Furthermore, some of the events may be duplicated via the object_altered event session (e.g. the resource governor events). What does this really mean? Extra tracing and a bit of extra overhead. It is something to consider. As for the extended events related events and how to do this sort of thing via XE, I will be exploring that further in a future post.
Suffice it to say that, while not a complete solution, the use of SQL Audit can be viable to track the changes that may be occurring within your SQL Server – from a settings point of view.
