Working as a DBA, sometimes you find yourself on an island by yourself or as a “one person” department. If that is the case, then hopefully you would not run into this situation. However, some companies have lax security and allow non-DBAs to touch the database….eeekkkk. This has been the case at many of the places I’ve worked, and that always scares me.
In other cases, there may be several DBAs working together but not necessarily communicating all the changes they are making. I have found it reasonable in many instances to make database property changes without consulting anyone. I will admit it. I may have forgotten to tell someone of a change here and there, so I am not above needing a process like this. In addition to this, I have approximately 20+ servers on laptops and a few with SQL Server Management Studio installed, so I never know if a user is going to become dangerous and accidently change settings.
That being said, I have created a simple process that will capture database properties and store them into a table to compare to a “changes table” that is populated on a daily basis. Basically it’s a simple SSIS package that collects the initial data, and each subsequent day collects new data. This compares the before and after tables, and if there are changes, it sends out an HTML report via email.
As in most of my articles, I mention that I have created a database that I use for my DBA activities that I call DBA_Reports, and this article is no different. I have been able to build a powerful DBA database to house all my reporting tools. Please make sure that you have the following before you begin:
- Database Mail enabled on your server – this is absolutely key to this process
- Enable xp_cmdShell on each remote server so you will be able to see what servers are on line and load them into a table (this will be discussed and used in later steps). This is also important to this working properly.
- Depending how you are set up, and which servers you are watching, you may need to create a linked server for each server. You will need to be able to write to tempdb on each remote server from a job/stored procedure running from your DBA_Reports database.
- Create yourself as an operator so you can receive notifications if your job fails. Personally, I like to add a notification on every job I create in case it fails. This is optional.
I’m not going to go into details on how to set these up as they are readily available from other sources, however, just consider them to be prerequisites.
Step One: Create the prerequisite Tables
Once you set up the prerequisites, it is time to create the tables we will use. If you have read and implemented any of my other articles, then this step is the same and you can skip it and move to step two.
The “ServerList” table below will house the server names and email address associated to it. This table will be used later for email notifications and for verifying what servers are currently online ( I need this because I have 20 laptop servers). The server names will be populated by a job, however the email addresses will need to be manually added. Although this takes some manual setup initially, once setup, only minimal maintenance will be required if new servers come online. I have a job that runs 3 times a day that will populate this table.
If a new server is inserted, I receive an email notification so I know to go and add the email address for that server. The email address in my case belongs to the owner of the server; however, it could be anyone supporting a server such as a dba. This is the only manual intervention I have after initial setup. You will see this job later in this article. Copy/paste/run the script below to create the ServerList table.
use DBA_Reports go CREATE TABLE [dbo].[ServerList]( [Server] [varchar](128) NOT NULL, [Server_email] [varchar](50) NULL, CONSTRAINT [PK_ServerList] PRIMARY KEY CLUSTERED ( [Server] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY] ) ON [PRIMARY]
The “DBA_Reports - Get New Servers” job will populate the ServerList_Stage and ServerList tables. This job uses xp_cmdshell to call the ''sqlcmd /Lc'' command. This command searches the network looking for servers where SQL Server is installed. Once it finds a server, it will insert the server name into a temporary table called #Server, then as long as the server name is not null, it will create and insert into the ServerList_Stage table.
The ServerList_Stage is just a staging table where we will use it to compare to our ServerList table later, so it is, as it says, just a “staging” table. Lastly, the job will compare the server name from the ServerList_Stage table to the ServerList table. If the record does not already exist, then it will insert it. If a new record is inserted, it will send an email listing out the newly added records with the subject of ''SQL Server - New Servers Found Today”.
After you create this job, schedule it to run every day. I have mine running 3 times a day. Once this job is setup and an initial run has occurred, edit the ServerList table and enter an email address for each server. Make sure you change <ADD YOUR INFO HERE> to your information.
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA Monitoring' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA Monitoring'
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_Reports - Get New Servers',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Gets the new servers that are not in the table and sends out an email for new servers',
@category_name=N'DBA Monitoring',
@owner_login_name=N'sa',
@notify_email_operator_name=N'<ADD YOUR INFO HERE>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
@step_name=N'step1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=N'USE [DBA_Reports]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[ServerList_Stage]'') AND type in (N''U''))
DROP TABLE [dbo].[ServerList_Stage]
GO
USE [DBA_Reports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerList_Stage](
[Server] [varchar](128) NOT NULL,
CONSTRAINT [PK_ServerList_Stage] PRIMARY KEY CLUSTERED
(
[Server] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-- Load table with server names
-- This will only work if XP_CMDSHELL is Enabled
USE DBA_Reports
GO
Create table #Server ( [Server] [varchar](128) )
Insert Into #Server
Exec xp_cmdshell ''sqlcmd /Lc''
Insert Into ServerList_Stage ([Server])
select [Server] from #Server where [Server] is not null
DROP Table #Server
GO
--Send email to with new servers found
SET nocount ON
--
DECLARE @Subject VARCHAR (100)
SET @Subject=''SQL Server - New Servers Found Today''
DECLARE @Count AS INT
SELECT @Count=COUNT(*) FROM DBA_Reports.dbo.ServerList_Stage where [Server] not in (select [Server] from [ServerList])
PRINT @Count
IF @Count > 0
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N''<table border="1">'' +
N''<tr>'' +
N''<th>Server</th>'' +
N''</tr>'' +
CAST ( ( SELECT td=[server],''''
FROM [ServerList_Stage]
where [Server] not in (select [Server] from [ServerList])
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''<ADD YOUR INFO HERE>'',
@recipients = ''<ADD YOUR INFO HERE>'',
@subject = @Subject,
@body = @tableHTML,
@body_format = ''HTML'' ;
END
INSERT INTO [DBA_Reports].[dbo].[ServerList]
([Server])
SELECT [Server]
FROM [DBA_Reports].[dbo].[ServerList_Stage]
where [Server] not in (select [Server] from [DBA_Reports].[dbo].[ServerList])
GO
',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 3 times',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=3,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110512,
@active_end_date=99991231,
@active_start_time=63000,
@active_end_time=220000
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GOStep Two: Data Tables
The Database_Setting_Previous and Database_Setting_Current tables are used by the SSIS package to compare current and newly changed properties. Data is initially captured, from each server and inserted in Tempdb into the Database_Settings table on each server. Then it is copied into the Database_Settings_Current table in the DBA_Reports database on the central server. Once data is on the central server, it is copied to the Database_Settings_Previous so it can be used on the next run of the job. Copy and paste the next two scripts to create the Database_Settings_Current and Database_Settings_Previous tables.
CREATE TABLE [dbo].[Database_Settings_Current](
[Database_Name] [sysname] NOT NULL,
[Allow Snapshot Isolation] [tinyint] NULL,
[ANSI NULL Default] [bit] NULL,
[ANSI NULLS Enabled] [bit] NULL,
[ANSI Paddings Enabled] [bit] NULL,
[ANSI Warnings Enabled] [bit] NULL,
[Arithmetic Abort Enabled] [bit] NULL,
[Auto CLOSE] [bit] NOT NULL,
[Auto Create Statistics] [bit] NULL,
[Auto Shrink] [bit] NULL,
[Auto Update Statistics Asynchronously] [bit] NULL,
[Auto Update Statistics] [bit] NULL,
[Close Cursor on Commit Enabled] [bit] NULL,
[Concatenate Null Yields Null] [bit] NULL,
[Cross-Database Ownership Chaining Enabled] [bit] NULL,
[Data Correlation Optimization Enabled] [bit] NOT NULL,
[Database Read-Only] [bit] NULL,
[Default Cursor] [bit] NULL,
[Encryption Enabled] [bit] NULL,
[Numeric Round-Abort] [bit] NULL,
[Page Verify] [nvarchar](60) NULL,
[Parameterization] [bit] NULL,
[Quoted Identifiers Enabled] [bit] NULL,
[Read Committed Snapshot] [bit] NULL,
[Recursive Triggers Enabled] [bit] NULL,
[Restrict Access] [nvarchar](60) NULL,
[Service Broker Enabled] [bit] NOT NULL,
[Trustworthy] [bit] NULL,
[ServerName] [nvarchar](128) NULL,
[DateChecked] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Database_Settings_Previous](
[Database_Name] [sysname] NOT NULL,
[Allow Snapshot Isolation] [tinyint] NULL,
[ANSI NULL Default] [bit] NULL,
[ANSI NULLS Enabled] [bit] NULL,
[ANSI Paddings Enabled] [bit] NULL,
[ANSI Warnings Enabled] [bit] NULL,
[Arithmetic Abort Enabled] [bit] NULL,
[Auto CLOSE] [bit] NOT NULL,
[Auto Create Statistics] [bit] NULL,
[Auto Shrink] [bit] NULL,
[Auto Update Statistics Asynchronously] [bit] NULL,
[Auto Update Statistics] [bit] NULL,
[Close Cursor on Commit Enabled] [bit] NULL,
[Concatenate Null Yields Null] [bit] NULL,
[Cross-Database Ownership Chaining Enabled] [bit] NULL,
[Data Correlation Optimization Enabled] [bit] NOT NULL,
[Database Read-Only] [bit] NULL,
[Default Cursor] [bit] NULL,
[Encryption Enabled] [bit] NULL,
[Numeric Round-Abort] [bit] NULL,
[Page Verify] [nvarchar](60) NULL,
[Parameterization] [bit] NULL,
[Quoted Identifiers Enabled] [bit] NULL,
[Read Committed Snapshot] [bit] NULL,
[Recursive Triggers Enabled] [bit] NULL,
[Restrict Access] [nvarchar](60) NULL,
[Service Broker Enabled] [bit] NOT NULL,
[Trustworthy] [bit] NULL,
[ServerName] [nvarchar](128) NULL,
[DateChecked] [datetime] NOT NULL
) ON [PRIMARY]
GOStep Three: The Job and Report
The report uses the tables setup in step two and compares the data. When differences are found, changes are captured and sent in an HTML email. Temporary tables called Change and Original are used to query against the previously obtained data.
Copy and paste the script below to create the job called “DBA_Reports - Report Get Database Property Changes” that calls the report “SQL Server - Changes made to DB Properties”.
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_Reports - Report Get Database Property Changes',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This report will collect the database property changes and also show the original settings. This is good in a multiple dba environment with many servers.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get Changes',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'drop table Changes
drop table Original
select *
into Changes
from dbo.Database_Settings_Current
EXCEPT
select *
from dbo.Database_Settings_Previous
select ServerName, Database_Name, *
from Changes
select *
into Original
from dbo.Database_Settings_Previous
EXCEPT
select *
from dbo.Database_Settings_Current
select ServerName, Database_Name, *
from Original
SET nocount ON
--
DECLARE @Subject VARCHAR (100)
SET @Subject=''SQL Server - Changes made to DB Properties''
DECLARE @Count_Changes AS INT
SELECT @Count_Changes=COUNT(*) from Changes
PRINT @Count_Changes
IF @Count_Changes >= 1
BEGIN
DECLARE @tableHTML_C NVARCHAR(MAX) ;
SET @tableHTML_C =
''<strong><font color="RED"> Changes to Database Properties </font></strong><br>'' +
N''<table border="1" CELLPADDING="0" CELLSPACING="10">'' +
N''<tr> '' +
N''<th> <font size="1"> <font size="1"> Server </font></th>'' +
N''<th> <font size="1"> Database </font></th>'' +
N''<th> <font size="1"> Allow Snapshot Isolation </font></th>'' +
N''<th> <font size="1"> ANSI NULL Default </font></th>'' +
N''<th> <font size="1"> ANSI NULLS Enabled </font></th>'' +
N''<th> <font size="1"> ANSI Paddings Enabled </font></th>'' +
N''<th> <font size="1"> ANSI Warnings Enabled </font></th>'' +
N''<th> <font size="1"> Arithmetic Abort Enabled </font></th>'' +
N''<th> <font size="1"> Auto CLOSE </font></th>'' +
N''<th> <font size="1"> Auto Create Statistics </font></th>'' +
N''<th> <font size="1"> Auto Shrink </font></th>'' +
N''<th> <font size="1"> Auto Update Statistics Asynchronously </font></th>'' +
N''<th> <font size="1"> Auto Update Statistics </font></th>'' +
N''<th> <font size="1"> Close Cursor on Commit Enabled </font></th>'' +
N''<th> <font size="1"> Concatenate Null Yields Null </font></th>'' +
N''<th> <font size="1"> Cross-Database Ownership Chaining Enabled </font></th>'' +
N''<th> <font size="1"> Data Correlation Optimization Enabled </font></th>'' +
N''<th> <font size="1"> Database Read-Only </font></th>'' +
N''<th> <font size="1"> Default Cursor </font></th>'' +
N''<th> <font size="1"> Encryption Enabled </font></th>'' +
N''<th> <font size="1"> Numeric Round-Abort </font></th>'' +
N''<th> <font size="1"> Page Verify </font></th>'' +
N''<th> <font size="1"> Parameterization </font></th>'' +
N''<th> <font size="1"> Quoted Identifiers Enabled </font></th>'' +
N''<th> <font size="1"> Read Committed Snapshot </font></th>'' +
N''<th> <font size="1"> Recursive Triggers Enabled </font></th>'' +
N''<th> <font size="1"> Restrict Access </font></th>'' +
N''<th> <font size="1"> Service Broker Enabled </font></th>'' +
N''<th> <font size="1"> Trustworthy </font></th>'' +
N''<th> <font size="1"> ServerName </font></th>'' +
N''<th> <font size="1"> DateChecked </font></th>'' +
N''</tr>'' +
CAST ( ( SELECT
td= [ServerName] ,''''
,td=[Database_Name] ,''''
,td=[Allow Snapshot Isolation] ,''''
,td=[ANSI NULL Default] ,''''
,td=[ANSI NULLS Enabled] ,''''
,td=[ANSI Paddings Enabled] ,''''
,td=[ANSI Warnings Enabled] ,''''
,td=[Arithmetic Abort Enabled] ,''''
,td=[Auto CLOSE] ,''''
,td=[Auto Create Statistics] ,''''
,td=[Auto Shrink] ,''''
,td=[Auto Update Statistics Asynchronously] ,''''
,td=[Auto Update Statistics] ,''''
,td=[Close Cursor on Commit Enabled] ,''''
,td=[Concatenate Null Yields Null] ,''''
,td=[Cross-Database Ownership Chaining Enabled] ,''''
,td=[Data Correlation Optimization Enabled] ,''''
,td=[Database Read-Only] ,''''
,td=[Default Cursor] ,''''
,td=[Encryption Enabled] ,''''
,td=[Numeric Round-Abort] ,''''
,td=[Page Verify] ,''''
,td=[Parameterization] ,''''
,td=[Quoted Identifiers Enabled] ,''''
,td=[Read Committed Snapshot] ,''''
,td=[Recursive Triggers Enabled] ,''''
,td=[Restrict Access] ,''''
,td=[Service Broker Enabled] ,''''
,td=[Trustworthy] ,''''
,td=[ServerName] ,''''
,td=[DateChecked] ,''''
FROM [Changes]
ORDER BY ServerName, Database_Name
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;
DECLARE @tableHTML_P NVARCHAR(MAX) ;
SET @tableHTML_P =
''<strong>Original Database Properties </strong><br>'' +
N''<table border="1" CELLPADDING="0" CELLSPACING="10">'' +
N''<tr> '' +
N''<th> <font size="1"> <font size="1"> Server </font></th>'' +
N''<th> <font size="1"> Database </font></th>'' +
N''<th> <font size="1"> Allow Snapshot Isolation </font></th>'' +
N''<th> <font size="1"> ANSI NULL Default </font></th>'' +
N''<th> <font size="1"> ANSI NULLS Enabled </font></th>'' +
N''<th> <font size="1"> ANSI Paddings Enabled </font></th>'' +
N''<th> <font size="1"> ANSI Warnings Enabled </font></th>'' +
N''<th> <font size="1"> Arithmetic Abort Enabled </font></th>'' +
N''<th> <font size="1"> Auto CLOSE </font></th>'' +
N''<th> <font size="1"> Auto Create Statistics </font></th>'' +
N''<th> <font size="1"> Auto Shrink </font></th>'' +
N''<th> <font size="1"> Auto Update Statistics Asynchronously </font></th>'' +
N''<th> <font size="1"> Auto Update Statistics </font></th>'' +
N''<th> <font size="1"> Close Cursor on Commit Enabled </font></th>'' +
N''<th> <font size="1"> Concatenate Null Yields Null </font></th>'' +
N''<th> <font size="1"> Cross-Database Ownership Chaining Enabled </font></th>'' +
N''<th> <font size="1"> Data Correlation Optimization Enabled </font></th>'' +
N''<th> <font size="1"> Database Read-Only </font></th>'' +
N''<th> <font size="1"> Default Cursor </font></th>'' +
N''<th> <font size="1"> Encryption Enabled </font></th>'' +
N''<th> <font size="1"> Numeric Round-Abort </font></th>'' +
N''<th> <font size="1"> Page Verify </font></th>'' +
N''<th> <font size="1"> Parameterization </font></th>'' +
N''<th> <font size="1"> Quoted Identifiers Enabled </font></th>'' +
N''<th> <font size="1"> Read Committed Snapshot </font></th>'' +
N''<th> <font size="1"> Recursive Triggers Enabled </font></th>'' +
N''<th> <font size="1"> Restrict Access </font></th>'' +
N''<th> <font size="1"> Service Broker Enabled </font></th>'' +
N''<th> <font size="1"> Trustworthy </font></th>'' +
N''<th> <font size="1"> ServerName </font></th>'' +
N''<th> <font size="1"> DateChecked </font></th>'' +
N''</tr>'' +
CAST ( ( SELECT
td= [ServerName] ,''''
,td=[Database_Name] ,''''
,td=[Allow Snapshot Isolation] ,''''
,td=[ANSI NULL Default] ,''''
,td=[ANSI NULLS Enabled] ,''''
,td=[ANSI Paddings Enabled] ,''''
,td=[ANSI Warnings Enabled] ,''''
,td=[Arithmetic Abort Enabled] ,''''
,td=[Auto CLOSE] ,''''
,td=[Auto Create Statistics] ,''''
,td=[Auto Shrink] ,''''
,td=[Auto Update Statistics Asynchronously] ,''''
,td=[Auto Update Statistics] ,''''
,td=[Close Cursor on Commit Enabled] ,''''
,td=[Concatenate Null Yields Null] ,''''
,td=[Cross-Database Ownership Chaining Enabled] ,''''
,td=[Data Correlation Optimization Enabled] ,''''
,td=[Database Read-Only] ,''''
,td=[Default Cursor] ,''''
,td=[Encryption Enabled] ,''''
,td=[Numeric Round-Abort] ,''''
,td=[Page Verify] ,''''
,td=[Parameterization] ,''''
,td=[Quoted Identifiers Enabled] ,''''
,td=[Read Committed Snapshot] ,''''
,td=[Recursive Triggers Enabled] ,''''
,td=[Restrict Access] ,''''
,td=[Service Broker Enabled] ,''''
,td=[Trustworthy] ,''''
,td=[ServerName] ,''''
,td=[DateChecked] ,''''
FROM [Original]
ORDER BY ServerName, Database_Name
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;
DECLARE @tableHTML NVARCHAR(MAX) ;
Set @tableHTML = @tableHTML_C + @tableHTML_P
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''<add your info here>'',
@recipients = ''<add your info here>'',
@subject = @Subject,
@body = @tableHTML,
@body_format = ''HTML'' ;
END',
@database_name=N'DBA_Reports',
@output_file_name=N'E:\Morning Reports\DBChangesErrors.txt',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GOStep Four: The SSIS Package – DatabaseProperties
The DatabaseProperties SSIS Package is the heart of this process. It will handle all of the data movement from the temporary tables to the central database, this is the final data that the report is generated from. Once the SSIS package is created, put it into a job separate from the report above so if in the event you need to rerun the report, you do not lose the current data as data is truncated daily.
First create 3 connections
- ADONET.DBA_Reports - To your central database
- Central_Server - To your central server
- Multiple_Servers - To serve as connection to multiple servers
Create a truncate script within a sequence container as shown below.
Next create a SQL task that will collect the server data for each server as shown in the image.
Finally create a foreach loop container that will collect the data from each server and copy it to the central database.
Run this script to get the database properties:
USE [tempdb];
if exists (select 1 from tempdb.dbo.sysobjects
where type = 'U' AND NAME = 'DatabaseSettings ')
drop table tempdb.dbo.DatabaseSettings;
CREATE TABLE [dbo].[DatabaseSettings](
[Database_Name] [sysname] NOT NULL,
[Allow Snapshot Isolation] [tinyint] NULL,
[ANSI NULL Default] [bit] NULL,
[ANSI NULLS Enabled] [bit] NULL,
[ANSI Paddings Enabled] [bit] NULL,
[ANSI Warnings Enabled] [bit] NULL,
[Arithmetic Abort Enabled] [bit] NULL,
[Auto CLOSE] [bit] NOT NULL,
[Auto Create Statistics] [bit] NULL,
[Auto Shrink] [bit] NULL,
[Auto Update Statistics Asynchronously] [bit] NULL,
[Auto Update Statistics] [bit] NULL,
[Close Cursor on Commit Enabled] [bit] NULL,
[Concatenate Null Yields Null] [bit] NULL,
[Cross-Database Ownership Chaining Enabled] [bit] NULL,
[Data Correlation Optimization Enabled] [bit] NOT NULL,
[Database Read-Only] [bit] NULL,
[Default Cursor] [bit] NULL,
[Encryption Enabled] [bit] NULL,
[Numeric Round-Abort] [bit] NULL,
[Page Verify] [nvarchar](60) NULL,
[Parameterization] [bit] NULL,
[Quoted Identifiers Enabled] [bit] NULL,
[Read Committed Snapshot] [bit] NULL,
[Recursive Triggers Enabled] [bit] NULL,
[Restrict Access] [nvarchar](60) NULL,
[Service Broker Enabled] [bit] NOT NULL,
[Trustworthy] [bit] NULL,
[ServerName] [nvarchar](128) NULL,
[DateChecked] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE master;
INSERT INTO [tempdb].[dbo].[DatabaseSettings]
([Database_Name]
,[Allow Snapshot Isolation]
,[ANSI NULL Default]
,[ANSI NULLS Enabled]
,[ANSI Paddings Enabled]
,[ANSI Warnings Enabled]
,[Arithmetic Abort Enabled]
,[Auto CLOSE]
,[Auto Create Statistics]
,[Auto Shrink]
,[Auto Update Statistics Asynchronously]
,[Auto Update Statistics]
,[Close Cursor on Commit Enabled]
,[Concatenate Null Yields Null]
,[Cross-Database Ownership Chaining Enabled]
,[Data Correlation Optimization Enabled]
,[Database Read-Only]
,[Default Cursor]
,[Encryption Enabled]
,[Numeric Round-Abort]
,[Page Verify]
,[Parameterization]
,[Quoted Identifiers Enabled]
,[Read Committed Snapshot]
,[Recursive Triggers Enabled]
,[Restrict Access]
,[Service Broker Enabled]
,[Trustworthy]
,[ServerName]
,[DateChecked]
)
SELECT name AS 'Database_Name' ,
snapshot_isolation_state AS 'Allow Snapshot Isolation' ,
is_ansi_null_default_on AS 'ANSI NULL Default' ,
is_ansi_nulls_on AS 'ANSI NULLS Enabled' ,
is_ansi_padding_on AS 'ANSI Paddings Enabled' ,
is_ansi_warnings_on AS 'ANSI Warnings Enabled' ,
is_arithabort_on AS 'Arithmetic Abort Enabled' ,
is_auto_close_on AS 'Auto CLOSE' ,
is_auto_create_stats_on AS 'Auto Create Statistics' ,
is_auto_shrink_on AS 'Auto Shrink' ,
is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
is_auto_update_stats_on AS 'Auto Update Statistics' ,
is_cursor_close_on_commit_on AS 'Close Cursor on Commit Enabled' ,
is_concat_null_yields_null_on AS 'Concatenate Null Yields Null' ,
is_db_chaining_on AS 'Cross-Database Ownership Chaining Enabled' ,
is_date_correlation_on AS 'Data Correlation Optimization Enabled' ,
is_read_only AS 'Database Read-Only' ,
is_local_cursor_default AS 'Default Cursor' ,
is_encrypted AS 'Encryption Enabled' ,
is_arithabort_on AS 'Numeric Round-Abort' ,
page_verify_option_desc AS 'Page Verify' ,
is_parameterization_forced AS 'Parameterization' ,
is_quoted_identifier_on AS 'Quoted Identifiers Enabled' ,
is_read_committed_snapshot_on AS 'Read Committed Snapshot' ,
is_recursive_triggers_on AS 'Recursive Triggers Enabled' ,
user_access_desc AS 'Restrict Access' ,
is_broker_enabled AS 'Service Broker Enabled' ,
is_trustworthy_on AS 'Trustworthy',
@@SERVERNAME as ServerName,
getdate() as DateChecked
FROM sys.databases ;The final package should look similar to this:
Conclusion
This is a very simple process of collecting data daily and comparing it to yesterday and reporting out the differences. Included in this package are the following:
Included in this package, the following will be created:
Tables:
- ServerList
- Database_Settings_Current
- Database_Settings_Previous
- Database_Changes
- Database_Original
Jobs:
- DBA_Reports - Report Get Database Property Changes
SSIS
- DBProperties.dtx
