By: Robert Pearl | Updated: 2011-04-20 | Comments (9) | Related: > 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:
- Default Trace enabled
- Database Mail turned on and configured with at least some profile
- Creation of a history/tracking table
- 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
GOMethodology
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_path3. 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 DESC4. 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 --@profile_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_cfgCreate 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 --@profile_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_cfgTest 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
GOYou 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
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
- If you'd like to learn more about the Default trace in SQL Server 2005 & 2008, and what you can use it for, you can refer to these previously published tips:
- If you need some help in setting up Database Mail, which is needed to send alerts, please review this helpful tips, which applies to SQL Server 2008 as well:
- As we briefly mentioned SQL 2008's Policy Management Framework, here are some articles of interest:
- Download the MSDN Whitepaper on SQL Server 2008 Policy-Based Management
- Using Policy-Based Management in SQL Server 2008
- Configuring Alerts for SQL Server Policy-Based Management
- Surface Area Configuration Using Policy Based Management - capturing configuration changes using PBM.
About the author
Robert Pearl is a SQL MVP, and President of Pearl Knowledge Solutions, Inc., offering SQLCentric monitoring and DBA services.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-04-20
