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:

DailyDashboardReport

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')

clip_image001

 

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''')

clip_image002

 

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')

clip_image003

 

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())')

clip_image004

 

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 &gt; GETDATE () -1
AND (LogText LIKE ''%failed%'' OR LogText LIKE ''error:%'')
drop table #ErrorLog
')

clip_image005

 

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…”

clip_image006

Point the Files to import: to the 5 XML files (You can select more than 1 file at a time so highlight all 5)

clip_image007

clip_image008

Once completed it will close the window. GO back to your Policies and open it up you should now see all 5 polices.

clip_image009

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!

clip_image010

 

Summary:

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!

 

Links

Policy Based Management is check out:

http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/PastWebinars.aspx?ResourceId=68

http://www.sqlchicken.com/category/policy-based-management/

http://www.sqlservercentral.com/articles/Video/64789/

 

Enterprise Management Framework Links:

Video from SQL Bits:

http://sqlbits.com/Sessions/Event5/Put_Your_Feet_Up_Simplified_Management_using_the_Enterprise_Policy_Management_Framework

Next we will look at setting up the EPM job to run all 5 polices against any servers in the development CMS group!

Advertisements