SQL Server Daily Monitoring Tool Part 2 - Keep Reading -Application & Backup job Status

Hi Friends,

Now we have the basic setup ready Part-1 to create our Daily monitoring packages to centralize all Health data from all SQL server.

Lets Summarize what we achieved in Part-1 

• Daily monitoring Package structure 
• Check List 
• Metadata Structure 
• Dynamic Connection In SSIS to connect to multiple server using Single Connection Manager
• Configured 1st SSIS package to create SQL server inventory 

Download package for Part-1 from here Populate_Server_inventory

Linked Server:In some package we will need this.

1st  & most prerequisite to fetch data from server is to Create linked server on All other server for our Local server using same SQL user ID.So make sure you have linked server created on all servers.

1.Application Jobs :Application jobs are those which are created by or requested by users to run on scheduled window (Via SQLAgent).In simple way User jobs

    SO what parameters we need in application Job Monitoring 

• We need to Fetch Job history form all Server MSDB to our centralize table 
• Once we have the History we can filter columns in SSRS report for visualization 

Metadata For Application Job Table ApplicationJobStatusTableScript

Now We have structure ready Lets move to create Package.

Package will look like this for Application job status

So we need  2 Data flow Tasks,2 Execute SQL task ,1 For each loop container, 1 Sequence Container  

Step 1 : 

Design SSIS Package to get Application Job details 

• Open BIDS or SQL server  Data tools
•  Open recently Created project for DM tool 
•  Now Go to Solution explorer-->Right Click --> New SSIS Package 
• Rename this package to "Application  & Backup Job Status.dtsx"  

    Step :2 

     Create All the variables  & Connection manager created in Part 1  

     Step :3 

Now In this package we have 2 different part one is Application job status  & Another is backup job status to differentiate both we will  use Sequence Container.

Sequence Container in SSIS Package : 

It makes simple to divide the control flow in a package into group of tasks and containers that you can manage as a unit.

• Go to SSIS toolbox on the right side If not you can also enable it from Menu Click anywhere in the package and then -->Click on SSIS menu and then -->Select SSIS Toolbox
• 
  
• 
• Drag and Drop the Sequence container in package 

1.Archive Job Status Data Flow Task

• Drag and drop Data flow task --> This data flow task will be to archiving daily data of current job_Status table to Archive_jobStatus table for historical purpose.

• Click On Data Flow Task --> Drag and drop Oledb Source  & Oledb Destination component from SSIS Toolbox

• Click job_status Task 
• Select your local server - Server where you created Job table 
• Data Access mode - Table & View 
• Name of the table : Job_Status 

• Go to the columns and check if all columns check-box are Ticked.
• Now Click on Archive_Job_Status task 
• Select your local server - Server where you created Archive Job table 
• Data Access mode - Table & View 
• Name of the table : Archive_Job_Status

• We are done with 1st flow task 

2.Truncate Job_Status table Execute SQL task 

Drag and drop Execute SQL task --> This Execute SQL task purpose to truncate all data from Main Job_Status table before putting any new data for the day.

Click--> Execute SQL task

Select SQL Source type : Direct input 

Click .. box on SQL Statement 

Write a query to truncate table 

Query "Truncate Table Jobs_Status"

3.Populate Server List of DMZ Server :-Execute SQL task 

Drag and drop Execute SQL task --> This Execute SQL task purpose to select all server names from ServerInfo_SSIS table to connection manager for dynamic connection 

Click--> Execute SQL task

Select SQL Source type : Direct input 

Click .. box on SQL Statement 

Write a query to Select Servers from table 

Query : 

"SELECT     LTRIM(RTRIM(Server)) AS servername

FROM         ServerInfo_SSIS

WHERE     (Connect = 1) AND (DMZ = 1) AND (Version >= 10)

order by LTRIM(RTRIM(Server)) Desc"

I have put filters for the version as some of DMV's for job history dont work with version  <10

• Then Go to Result Set --> Select SQL_RS variable to store Server names as shown in screen clip

• Click OK 
• Now Drag and Drop For each Loop container and then Data flow task as shown below in SQL 2005,200,2012 job Info table

 4. For Each loop container

• Click on For each loop container 
• Select "Foreach ADO Enumerator"
• Go To collection --> Select SQL_RS variable 

• GO to Variable mapping and select "SRV_Conn" variable for mapping.

• Now Click on Data flow task you just added into For each loop container 
• Drag  & drop Oledb Source   &  destination component 

Query Download File from here : JobStatusQuery

• Check mappings and click OK.

Finally ....Package is ready to fetch data for application job status :-). 

Now to Filter this table for SSRS report We need below Query which will only select required column data from table.


Design SSRS Dashboard : 

Will come soon with this part As of now below is the query which will filter the required columns from the table and display in Dashboard.(you can alter the query depend upon your requirement) 



Query : 

  SELECT SERVER,NAME,last_run_outcome,last_run_date FROM DBO.JOBS WHERE last_run_outcome = 0 AND Cast( convert(Date,convert(varchar,last_run_date)) as date) >= convert(DATE,GETDATE()-1) AND convert(Date,Package_run_date) >= convert(DATE,GETDATE()-1) 


In SSRS Dashboard it will look like this 


1 . Without expanding 

2. After Expanding individual Servers 

We will discuss this in later parts about how to Design SSRS to look like dash board :-)

Now we will develop same package for 
2.DBA Jobs : Maintenance Jobs,Re indexing Jobs,DB Alerts Jobs etc.. ..

Coming Soon ...............

SQL Server Daily Monitoring Tool Part 1 - Keep Reading -Dynamic Connection In SSIS

 Hi friends

Let’s start with basic and hectic task of Health Monitoring of XX no of SQL servers.and how to achieve this in simple way:-) 

We have lot of System DMV’s to achieve our goal but what if I have XX no of servers then it will take day to monitor all servers’ parameters.Such as

• How many servers do we have for SQL?
• How many databases were on each server?
• How big were these databases?
•  Were all the databases getting regular backups?
• Were all Application jobs are running ok?  blah blah blah and may more ………….

The monitoring solution must gather the metrics you need to diagnose any CPU, memory or I/O issues on your SQL Servers.

As we have many third party vendors\Tools available to o the task but why to pay extra if we can do the same with little extra efforts.

So little extra efforts will require

How should we centralize all Daily Monitoring  data from multiple servers to central repository?

- SSIS packages will help us to achieve this task

How should we view the reports using centralized data?

-SSRS package will help us to achieve his task

Before going ahead let’s see how it looks like

SO Let’s Divide and Conquer!!!....

Works by recursively breaking down a problem into two or more sub-problems of the same (or related) type (divide), until these become simple enough to be solved directly (conquer)-Courtesy by Google.

We will divide the Solution in 5 separate tasks then will merge all parts together to build complete solution.

v  Checklist to monitor databases-Gather information what we want to monitor 

v  Metadata Of Database  -Define structure of table to store data 

v  Queries To Fetch Data From Server - To Fetch data from multiple servers 

v  Centralization Of all data into Central repository - Store data in centralized DB 

v  Design SSRS reports - Fetch data in meaningful format to analyse 

TadaahhhJ deploy the reports ……

SO Lets get roll

CheckList:

Checklist	Description
SQL Service Status	Status for the SQ services including Agent/Analysis/reporting/Browser –Shows only Services which is in stopped state –Can change according to your requirement
Database status	Shows status of databases which are <> ‘ONLINE’
Disk Space Status	Shows Total  & Free disk space which goes below threshold – Threshold is depend upon your requirement
Backup Status	Status of backups which are failed for the GETDATE ()-1
Application Jobs Status	Status of application jobs which are failed GETDATE()-1
Log Size Status	Shows log files with Size info exceeding threshold value.
High Resource utilization	Shows servers having high CPU  & Memory utilization
Error Logs	Different parameters- will discuss in detail.
Running Jobs	Currently Running Jobs on server including Application as well DBA jobs.
Long Running Queries	Currently running queries more than threshold value.

 Milestone 1 Achieved 😊...

Metadata:

Database Name	Daily_monitoring_DBA
Table Details
Description	Names
Server Information	ServerInfo_SSIS
	SQL_Servers
Database status	DB_status
Log file Size information	LogSizeStats
	Archive_LogSizeStats
Database Size information	DBSizeStats
	Archive_DBSizeStats
Disk Space information	DiskSpace
	Archive_DiskSpace
Backup Status Information	BackupStatus
	Archive_BackupStatus
Application Job Status Information	JobStatus
	Archive_JobStatus
Error Logs Information	StageErrorLog
	Sqlerrorlog
Running Jobs information	RunnigJobs
Long Running Queries Information	longrunningqueries

Metadata scripts are attached here 

DailyMonitoringTableMetadata

Milestone 2 Achieved 😊.
Now we can start designing 1st SSIS package.

We will Merge Task 3  & 4 together to centralize data into repository 

·Design SSIS Package - Create new project (Blank Solution)

·Populate server Inventory  - Finalize servers which need to be add for Daily Monitoring

1.Design SSIS Package to get SQL server details.

       ·  Open BIDS or SQL server  Data tools

       · Create New Integration Services Project

       · Once the project is created, it will also create an empty package by default with the name      “Package.dtsx” Rename this package to "Populate Server Inventory.dtsx"  

Why we need this package??

-      We need to centralize all data from multiple domain SQL servers to central repository (SQL server). To    achieve this we need to create a package to identify SQL servers we want to connect for Daily monitoring.

-      So let say you have 20 SQL servers in your domain (you may be having multiple domain and trust  relationship between them)  we need all names into centralized table with their version .We will use this server name & Versions to connect to SQL server across domain.

First create 2 Table in your database

Script Attached here ServerInfo_SSISTables 

Description	Names	Description
Server Information	ServerInfo_SSIS	You need to enter a valid server name in column ServerName
	SQL_Servers	This table will populate automatically

Manually populate parts of the "ServerInfo_SSIS" table with your specific server information. For each server, you need to fill in the following fields:

• Server. Enter the server's name.
• Connect. If you want to include the server in the connection list,
   enter a value of 1. If you don't want to include the server in the connection list,
   enter a value of 0.

E.g.

Server	Connect	Version	Online
Bom-mssqlserver	1	Leave it blank	Leave it Blank

The remaining two fields—Version and Online—are updated automatically when the SSIS package executes.

Next, follow the steps below to configure this SSIS package.

·         Right-click anywhere on the empty panel on the Control Flow tab,

·         Then select Variables and then choose the Add Variables icon. Add the following variables to the SSIS package:

Create Dynamic Connection in SSIS Package to Fetch data from All SQL server Across Domain.

We need above variables to create Dynamic Connection.

1. SQL_RS - It will hold all Server names from "ServerInfo_SSIS" table

2. SRV_Conn - It will hold Single server name in loop with connection manager, it will fetch server names from SQL_RS variable one by one in loop.

3. SQLServer - it will use that single server name from SRV_Conn into OLEDb connection manager

4. SQLUsername, SQLpassword & SQLdatabase - Username to connect to all SQL instances across domain e.g. Dba which we created earlier, It will be hardcoded into variable.

5. Create new Oledb Connection

1. For Local Centralized server

2 Dynamic connections to fetch server names directly into connection string

§  Before above steps first we will set values to 2 variables.

1. SQL_Server.

2. SQL_Connection_string.

§  Go to variable window àGo to “SQL_Server” variable created in earlier step àon the Right side click on box with 3 dots ....

§  New expression window will open.

§  Drag and drop @SRV_connection variable to expression box and evaluate expression.

§  Now you are ready to create SQL Connection string through variable SQL_Connection_string

§  Go to SQL_Connection_string variable click  à click on box with 3 dots....

§  New expression window will open.

§  Create Connection string with help of all variable in expression box write below query into expression box

Query:

"Data Source= " + @[User::SQLServer] +";User ID=" + @[User::SQLUsername] +";Provider=SQLNCLI10.1;Initial Catalog=" + @[User::SQLDatabase] + ";Password=" + @[User::SQLPassword]

Click on evaluates expression and then OK….

Now you are ready to create Dynamic connection.

§  Create New Oledb Connection  à New

§  Initially put local server name into Server name text box of Connection manager and test the connection .Select Default DB as MSDB and press ok

§   Then highlight  newly created Multiserver connection and press f4 à Connection Property window

§   Go to expression à connection String à Click on  box with …  New window will open for expression 

After clicking … box below window will come drag and drop “SQL_Connection_string” variable into expression box and click on Evaluate Expression.

Click on evaluates expression and then OK….

Diagram & SSIS Flow 

Screen Clips:

1. Execute SQL task

 2. For Each loop container

3. Data Flow task in for each loop Container 

 Query Attached here : Query

J Tadhhhhhh… your first package is ready to populate server inventory into SQL_Server table.

Now execute the package and check SQl_server table for server inventory.

Conclusion :

That’s it for part 1. We’ll develop Daily monitoring packages in part 2.