Hi friends
2. For
Each loop container
Query Attached here : Query
Conclusion :
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 ………….
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
1.Design SSIS Package to get SQL server details.
• 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.
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
3.
Data Flow task in for each loop Container
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.
