Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Capturing and Alerting on SQL Server Configuration Changes


By:   |   Last Updated: 2011-04-20   |   Comments (9)   |   Related Tips: More > SQL Server Configurations

Problem

Once a SQL Server is initially configured, we would love nothing but to "set it, and forget it". However, in the real world, especially with multiple SQL Server environments to manage, we need to keep track of any changes to the configuration, which could impact the environment. As a DBA, we need to be aware of any ad-hoc changes and ideally be alerted to address the issue.

In SQL Server 2008, Microsoft introduced the Policy Management Framework or Policy-Based Management. This powerful new framework allows DBAs to define a set of policies that can control several aspects of SQL Server. For example, the DBA could define a policy that specifies how a particular configuration option should be set on all the servers in the enterprise. Based on a particular facet, a policy can evaluate a condition as "true" or "false" then log or prevent an unauthorized action. In this tip, we are going to offer a solution where we can use the SQL Server Default Trace, which is a lightweight server-side trace running continuously in the background.

Solution

The default trace records a handful of useful events to a trace file which can be loaded and reviewed in SQL Profiler or queried using the 'fn_trace_gettable(@trcpath)' function. With this process we should be able to capture when a change is made, made the change and which option was changed . It will trace data for the eventclass 22, which is the ErrorLog event.

Here are the overall requirements:

  1. Default Trace enabled
  2. Database Mail turned on and configured with at least some profile
  3. Creation of a history/tracking table
  4. Creation of a stored procedure

By using the Default Trace, combined with fn_trace_gettable, sp_send_dbmail, a custom tracking table and stored procedure, we can capture all of the configuration changes. Let's check out how this works.


Ensure Default Trace is ON

First let's ensure the Default Trace is enabled, by running this query:

SELECT name, CASE WHEN value_in_use=1 THEN 'ENABLED'
WHEN value_in_use=0 THEN 'DISABLED'
END AS [status]
FROM sys.configurations
WHERE name='default trace enabled'

Create History Tracking Table

The purpose of this table is twofold. First, it will store and archive all configuration changes that can be reported on. Second, when dynamically retrieving and querying the trace for any configuration changes, it will only capture the latest changes. It does this by comparing the EventSequence column, which is a unique row identifier and is the order the trace event occurred. Because the trace is an on-going continuous process, it will always contain older changes as well. By comparing the result sets where 'EventSequence' does not exist in this table, we can ensure there are no duplicate occurrences.

Now, let's create the table:
/****** Object: Table [dbo].[SQLConfig_Changes] ******/
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SQLConfig_Changes](
[TextData] [varchar](500) NULL,
[HostName] [varchar](155) NULL,
[ApplicationName] [varchar](255) NULL,
[DatabaseName] [varchar](155) NULL,
[LoginName] [varchar](155) NULL,
[SPID] [int] NULL,
[StartTime] [datetime] NULL,
[EventSequence] [int] NULL
)
ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

Methodology

Now that we have our table created, let's discuss each portion of the code to capture the SQL configuration changes and provide an explanation:

1. We will create a temp table, matching the definition of the dbo.SQLConfig_Change table to capture the needed data from the trace.

CREATE TABLE #temp_cfg (
TEXTData VARCHAR(500),
HostName VARCHAR(155),
ApplicationName VARCHAR(255),
DatabaseName VARCHAR(155),
LoginName VARCHAR(155),
SPID INT,
StartTime DATETIME,
EventSequence INT
)

2. Then we must query for the physical path of the current active trace file on your SQL Server.

DECLARE @trc_path VARCHAR(500)
SELECT @trc_path=CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT)
WHERE property=2
SELECT @trc_path

3. Next, we will query the trace to capture the needed data for the fn_trace_gettable function and filter the data where TextData like '%configure%'. The event will be inserted to our SQLConfig_Changes table, only if it has not already been captured. We also order by the StartTime descending, so we can force the latest data to the top of the query results.

INSERT INTO #temp_cfg
SELECT TEXTData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence
FROM fn_trace_gettable(@trc_path,1) fn
WHERE TEXTData LIKE '%configure%'
AND SPID<>@@spid
AND fn.EventSequence NOT IN (SELECT EventSequence FROM SQLConfig_Changes)
AND
TEXTData NOT LIKE '%Insert into #temp_cfg%'
ORDER BY StartTime DESC

4. At this point, we insert the new rows from the temp table #temp_cfg into the dbo.SQLConfig_Changes table.

INSERT INTO dbo.SQLConfig_Changes
SELECT * FROM #temp_cfg

5. Finally, we will invoke the database mail feature to alert us to any new configuration changes on the server. This must be set up in advance, so refer to these tips (SQL Server 2005 Database Mail Setup and Configuration Scripts and Setting up Database Mail for SQL 2005). As #temp_cfg contains the new data, we check to see if in fact the table is populated, by issuing the command in the script: If @@ROWCOUNT > 0. So, only if there are any rows, meaning configuration changes have been made since the last time it ran, it will send an email alert. We use a cursor to interrogate the temp table's data row-by-row, and send out one email alert for each configuration change detected. We also truncate the message to only show the text message of the configuration change, and eliminate 'Run the RECONFIGURE statement to install' message.

IF @@ROWCOUNT > 0
--select @@ROWCOUNT
BEGIN
DECLARE
c CURSOR FOR
SELECT
LTRIM(REPLACE(SUBSTRING(TEXTdata,31,250), '. Run the RECONFIGURE statement to install.', ''))
FROM
#temp_cfg

OPEN
c
FETCH NEXT FROM c INTO @textdata
WHILE (@@FETCH_STATUS <> -1)
BEGIN
--FETCH c INTO @textdata

SELECT @message = @textdata + 'on server ' + @@servername + CHAR(13)

EXEC msdb.dbo.sp_send_dbmail [email protected]_name='ProfileName - otherwise will use default profile',
@recipients='[email protected]',
@subject='SQL Server Configuration Change Alert',
@body=@message

FETCH NEXT FROM c INTO @textdata

END
CLOSE
c
DEALLOCATE c

END

DROP TABLE
#temp_cfg

Create the stored procedure

Now that we understand each part of the code, we can put all of it together and compile it into a stored procedure, called usp_Capture_SQL_Config_Changes. This stored procedure requires will require only one parameter - @SendMailTo, which are the email recipient(s) for the alerts.

SET NOCOUNT ON
GO

CREATE PROCEDURE dbo.usp_Capture_SQL_Config_Changes @SendEmailTo VARCHAR(255) AS

CREATE TABLE
#temp_cfg (
TEXTData VARCHAR(500),
HostName VARCHAR(155),
ApplicationName VARCHAR(255),
DatabaseName VARCHAR(155),
LoginName VARCHAR(155),
SPID INT,
StartTime DATETIME,
EventSequence INT
)

DECLARE @trc_path VARCHAR(500),
@message VARCHAR(MAX),
@message1 VARCHAR(MAX),
@textdata VARCHAR(1000)

SELECT @trc_path=CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT)
WHERE property=2

INSERT INTO #temp_cfg
SELECT TEXTData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence
FROM fn_trace_gettable(@trc_path,1) fn
WHERE TEXTData LIKE '%configure%'
AND SPID<>@@spid
AND fn.EventSequence NOT IN (SELECT EventSequence FROM SQLConfig_Changes)
AND
TEXTData NOT LIKE '%Insert into #temp_cfg%'
ORDER BY StartTime DESC

INSERT INTO
dbo.SQLConfig_Changes
SELECT * FROM #temp_cfg

/*select TextData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence
from fn_trace_gettable(@trc_path,1) fn
where TextData like '%configure%'
and SPID<>@@spid
and fn.EventSequence not in (select EventSequence from SQLConfig_Changes)
order by StartTime desc*/

--select * from SQLConfig_Changes

IF @@ROWCOUNT > 0
--select @@ROWCOUNT

BEGIN
DECLARE
c CURSOR FOR

SELECT
LTRIM(REPLACE(SUBSTRING(TEXTdata,31,250), '. Run the RECONFIGURE statement to install.', ''))
FROM #temp_cfg

OPEN c

FETCH NEXT FROM c INTO @textdata

WHILE (@@FETCH_STATUS <> -1)
BEGIN
--FETCH c INTO @textdata

SELECT @message = @textdata + 'on server ' + @@servername + CHAR(13)

EXEC msdb.dbo.sp_send_dbmail [email protected]_name='ProfileName - otherwise will use default profile',
@recipients=@SendEmailTo,
@subject='SQL Server Configuration Change Alert',
@body=@message

FETCH NEXT FROM c INTO @textdata

END
CLOSE
c
DEALLOCATE c

END

DROP TABLE
#temp_cfg

Test the Process

Once we have created the table and stored procedure as wll as ensured that the default trace and database mail are enabled, we are ready to test our process in a test environment. The easiest way to test this logic is to start changing some of the configuration options with the sp_configure system stored procedure. Let's turn on 'Ad Hoc Distributed Queries', for example.

sp_configure 'show advanced options',1
GO
RECONFIGURE WITH override
GO

sp_configure
'Ad Hoc Distributed Queries',1
GO
RECONFIGURE WITH override
GO

You should see the query output as:

"Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install."

Now, go to any query window on the server and run our stored procedure:

exec dbo.usp_Capture_SQL_Config_Changes '[email protected]'

With everything set up properly, within seconds you should see a message in your inbox:

In addition, you can query the SQLConfig_Changes table, to return configuration change history:

SELECT *
FROM dbo.SQLConfig_Changes
ORDER BY StartTime DESC

query the sql config_changes table


Final Comments

The above process is meant to offer one solution of capturing configuration changes, using the Default Trace. Clearly there are many things that can be captured and alerted on using this method. It is very likely that this is one method the major 3rd party monitoring vendors use in their software.

To take this a step further, you can schedule the stored procedure to run as a job. Since the data is cumulative within the trace capture, you can determine the interval run the job as desired. The only caveat is that the trace file will rollover at some point, and this process only interrogates the active trace file. You most likely do not need to run it every few minutes, and probably can start with hourly or maybe even twice a day depending on your environment.

Hopefully, with the above process, you will have some peace of mind that you will be alerted on any changes made to the SQL Server configuration.

Next Steps


Last Updated: 2011-04-20


next webcast button


next tip button



About the author
MSSQLTips author Robert Pearl Robert Pearl is a SQL MVP, and President of Pearl Knowledge Solutions, Inc., offering SQLCentric monitoring and DBA services.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, February 19, 2016 - 2:44:33 PM - James Youkhanis Back To Top

 

INSERT INTO #temp_cfg

SELECT TEXTData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence

FROM fn_trace_gettable(@trc_path,1) fn

WHERE TEXTData LIKE '%configure%'

AND SPID<>@@spid

AND fn.EventSequence NOT IN (SELECT EventSequence FROM SQLConfig_Changes)

AND TEXTData NOT LIKE '%Insert into #temp_cfg%'

ORDER BY StartTime DESC

 

Msg 567, Level 16, State 5, Line 33

File '' either does not exist or is not a recognizable trace file. Or there was an error opening the fil

 


Monday, November 07, 2011 - 5:30:57 PM - Amanda Back To Top

I had to modify slightly because there seemed to be more than one row which met the property=2 qualification...it was NULL, but it was there - single instance server...  why would there be 2 rows?

 


SELECT @trc_path=CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT)
WHERE property=2
and value is not null --ajb to accommodate more than one result (??)


Thursday, April 21, 2011 - 1:12:11 PM - Jeremy Kadlec Back To Top

Robert,

We have updated the tip to fully qualify all of the object names.

Thank you,
Jeremy Kadlec


Thursday, April 21, 2011 - 12:30:27 PM - Robert Pearl Back To Top
Oawis, Assuming you DID create the above table Could be a simple object reference issue. Please reference dbo.SQLConfig_Changes in the stored proc. There are 2 places it references the table, so try with dbo. Perhaps the editor can correct it, just to avoid this possibility. HTH

Thursday, April 21, 2011 - 9:19:59 AM - Jeremy Kadlec Back To Top

Owais,

Did you create this table as outlined in the tip?

/****** Object: Table [dbo].[SQLConfig_Changes] ******/
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SQLConfig_Changes](
[TextData] [varchar](500) NULL,
[HostName] [varchar](155) NULL,
[ApplicationName] [varchar](255) NULL,
[DatabaseName] [varchar](155) NULL,
[LoginName] [varchar](155) NULL,
[SPID] [int] NULL,
[StartTime] [datetime] NULL,
[EventSequence] [int] NULL
)
ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO
 
Thank you,
Jeremy Kadlec

Thursday, April 21, 2011 - 6:16:12 AM - owais shaikh Back To Top

Thanks for a wonderful script.

But while executing the stored procedure i m getting following eror--

'

Msg 208, Level 16, State 1, Line 1

Invalid object name 'SQLConfig_Changes'.

'

will you pls give some suggestion. 


Wednesday, April 20, 2011 - 2:36:24 PM - Robert Pearl Back To Top

Thanks for reading and hope everyone finds this useful

Krishna, I'm sure there is a way - maybe an item for another tip. :-) 

If in this context, I can mention that SQLCentric monitoring and alerting, among other things, monitors & alerts on Cluster failovers.

HTH


Wednesday, April 20, 2011 - 1:20:34 PM - krishna Back To Top

Thanks for the tip

 

Its informative


Wednesday, April 20, 2011 - 1:19:18 PM - krishna Back To Top

In clustered server environment (consider having more than 30 cluster server’s)
Is there any chance to write a Tsql script to find which server is working on which node,i mean if there is any switching of nodes happened
generate an alert and report on daily basis with the script

Thanks,
Krishna


Learn more about SQL Server tools