Daily Dashboard – 03 Creating the Policies
Every DBA loves a checklist in the morning, to try and speed up my checks I decided to mix a little policy based management and a SSRS subscription, this is a really easy way to set up a daily report (or even weekly / monthly reports) that can cover pretty much anything you want it to for your servers health.
I’ve split this into sections to go through the steps needed to set it up:
Daily Dashboard – 01 Setting up a Central Management Server
Daily Dashboard – 02 Setting up a Enterprise Policy Management Framework Server
Daily Dashboard – 03 Creating the Policies
Daily Dashboard – 04 Setting up the Daily EPM Agent Job
Daily Dashboard – 05 Create the Report and set up the subscription
The report ends up looking like:
Create the Policies
I wanted a checklist to make sure that:
The Instance is online
The SQL Agent is online
No Corruption has been found
Number of Jobs that have failed in the last 24 hours
Number of errors in the errorlog in the last 24 hours
Error Log Trends (7 days worth of trending)
How its done:
I wont go into details of how to set up each policy instead ill supply the code I used for each one and the XML for the policies to download.
Instance is online:
I just wanted a quick check to make sure the instance is accepting connections. I used a dynamic sql command and just connect to sys.databases to see if I can get a result. If I can success!
ExecuteSql('numeric', 'SELECT TOP (1) database_id FROM master.sys.databases')
SQL Agent is online:
I wanted to know the Agent was up.. If its not my notifications for job fails wont matter as they wont run to start with! Again I used dynamic sql command against sysprocesses to see if the “SQLAgent – Generic Refresher” is on.
ExecuteSql('numeric', ' SELECT COUNT(*) FROM MASTER.dbo.sysprocesses WHERE program_name = N''SQLAgent - Generic Refresher''')
No Corruption has been found:
corruption is badddd SQL Server keeps a record of any suspect pages it finds it finds in MSDB. so another dynamic sql command checks for any rows in the suspect_pages table
ExecuteSql('numeric', 'SELECT COUNT(*) FROM msdb.dbo.suspect_pages')
Number of Jobs that have failed in the last 24 hours:
I wanted to keep track of agent job failures. I have failure notifications set up. but its good to see it all again the next.
ExecuteSql('numeric', 'SELECT COUNT(*) FROM msdb.dbo.sysjobhistory WHERE message LIKE ''%failed.%'' AND step_name LIKE ''%job%'' AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + '' '' + STUFF(STUFF(RIGHT(''000000'' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, '':''), 3, 0, '':''), 121) > DATEADD(DAY, -1, GETDATE())')
Number of errors in the errorlog in the last 24 hours:
Seems to be forgetton far to regularly, is reading the errorlog, I wanted a way to check for warning signs that something might not be as it seems! I look for “failed” or “error” in the log in the last 24 hours.
ExecuteSql('numeric', ' CREATE TABLE #ErrorLog ( LogID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY , LogDate DATETIME NULL , ProcessInfo NVARCHAR(100) NULL , LogTe</code>xt NVARCHAR(4000) NULL ) INSERT INTO #ErrorLog ( LogDate, ProcessInfo, LogText ) EXEC master..xp_readerrorlog 0, 1 SELECT COUNT(*) FROM #ErrorLog WHERE LogDate > GETDATE () -1 AND (LogText LIKE ''%failed%'' OR LogText LIKE ''error:%'') drop table #ErrorLog ')
Error Log Trends (7 days worth of trending):
In a dream world this is just a flat line on every server, if im seeing thick lines I know there’s a regular problem that needs fixing compared to one of spikes.. this is just a sub report sparkline which is hitting the previous errorlog for the instance.
You can download the policies here: https://1drv.ms/u/s!AsBZ-ToGRkxLlmJjb7g7dOiVXv-D
Import the policies
Extract all the XML files to a single location.
Open SSMS, connect to your CMS / EPM server and navigate through.
Management -> Policy Management
right click on Policies and select “Import Policy…”
Point the Files to import: to the 5 XML files (You can select more than 1 file at a time so highlight all 5)
Once completed it will close the window. GO back to your Policies and open it up you should now see all 5 polices.
Note that when you open the Policies in the Description they are all in the same Category “PROD_DAILY” This is very important for using Enterprise Policy Management! We group things together by CMS Folder (In our case “Development”) for which Servers we want to Query. And by Policy Category for which policies to run!
So we’ve added 5 policies to our Server and set them to the same Category for EPM to use. The only downside I’ve found for EPM is that if I want to run the policy for different purposes (think a daily report but also used in a monthly job, you will have to create the same policy again and set it to the different category since there’s no way to use multiple category for a single policy..
Make sure you edit the .sql script and the .ps1 scripts to match your environment. any mistakes will instantly break EPM!
Policy Based Management is check out:
Enterprise Management Framework Links:
Video from SQL Bits:
Next we will look at setting up the EPM job to run all 5 polices against any servers in the development CMS group!