Learning Code & Activity: Monitoring

Showing posts with label Monitoring. Show all posts

Best Practices & Monitoring Points For SQL Database & Server

Best Practices :

1. Application and Database should be on different servers

2. Proper Backup plan should be implemented (Based on Data size and RPO and RTO)

Plan :

i. Full Backup on Sunday

ii. Differential Backup on Daily

iii. Log hourly or every fifteen minutes

iv. System database backup plan should also there

v. Restore drill

vi. Backup reports

3. Proper Indexing

i. Based on Data type, Data size, Fill factor

ii. Implement Indexs suggested by Sql server engine having high impact and monitor

iii. Remove indexes not in use

4. Remove all the objects which were never used

5. Update statististics every week or more frequently(depending upon data insertion and updation and deletion)

6. Email notification

i. Define mailer profile

ii. Create operators

7. Define Bottlenecks for CPU Utilization (Performance)

8. Create Logins and give proper roles not all

9. Proper naming convention of sql objects like procedures with prefix usp

10. For heavy data searching use Full text search

11. Proper history clean up

12. Always logoff after completion of task on prod environment

a. SET NOCOUNT ON should be in every proc

b. Proper try and catch should be there
c. Use SARGABLE rules

-> . DR plan should be ready (Disaster Recovery)

->. Apply high availability solution ( Logshiping or replication or clustering) depending upon the use of data

--------------------------------------------------------

Monitoring :

A. Backups, backups should be validated and monitored, Define retention period

B. Monitor job failed and job status

C. Monitor services

D. Monitor CPU Utilization & system performance

E. Monitor database performance

F. Monitor disk spaces

G. Monitor tables (based on iteration prepare plan for partitioning and archival)

H. Monitor security

I. Monitor users and delete orphan users

J. Use ssrs for monitoring create ssrs reports(less costly) or use third party tool (more costly)

K. Restricted Access

L. Long running query and job alert

M. Agent Job history configuration and maintenance

N. Monitor database integrity

Monitor Database Size Location through query and mailer

Hi friends! One of my friend asked me to help him creating a mailer that gives information about all databases of a server. I tried and created this-

----------------------------------------------------------------------

Use master
Go

Create Table #temp
(
    DatabaseName sysname,
    LogicalName sysname,
    physical_name varchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2),
 [DBState] varchar(500)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into #temp (DatabaseName, LogicalName, physical_name, Size, FreeSpace,[DBState])
    Select DB_NAME() AS [DatabaseName], Name as LogicalName,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
    Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace,
 state_desc as [DBState]
    From sys.database_files
'
Select ROW_NUMBER() Over(Order by DatabaseName) RowNo, * From #temp


---------mailer
Declare @MailSubject varchar(1000)='Database Monitor Info for Servers '+Isnull(Cast(@@ServerName as varchar(500)),'')
Declare @MailBodyHeader varchar(3000)='Hi,<br><br>Please find the details of databases-<br><br>
  <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" style="border-top:1px solid black; border-left:1px solid black;"><TBODY>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="3" WIDTH="100PX" Color="white"><B>Database Monitor Info</B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="2" WIDTH="100PX" Color="white"><B>Status </B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>RowNo</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>DatabaseName</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>LogicalName</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>physical_name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>size</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>FreeSpace</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>DBState</B></FONT></TD>
           </TR>'
Declare @MailBody varchar(max)
Select @MailBody= Isnull(@MailBody,'')+'<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Cast(ROW_NUMBER() Over(Order by DatabaseName) as varchar(10)),'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Cast(DatabaseName as varchar(500)),'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(LogicalName as varchar(500)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(physical_name as varchar(2000)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Center"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(size as varchar(50)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Center"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(FreeSpace as varchar(50)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(DBState as varchar(50)),'') +'</FONT></TD>
           </TR>'
     From #temp

Set  @MAILBODY= @MailBodyHeader +@MAILBODY+'</Table>'
IF((Select Count(1) from #temp)>0)
Begin
EXEC MSDB.DBO.SP_SEND_DBMAIL 
     @RECIPIENTS ='vimal.lohani@gmail.com',
     --@COPY_RECIPIENTS = 'vimal.lohani@gmail.com',                                   
     @BODY = @MailBody,
     @SUBJECT = @MailSubject,
     @BODY_FORMAT = 'HTML',
     @PROFILE_NAME ='dbamail' --Your profile name
End
---------mailer enddrop table #temp

Monitor Windows Server having SQL Services Agent Services Reporting Services

Hi guys! Yesterday my boss asked me to build a tool to monitor Windows servers and Sql server services. I searched on Google and find few codes, i modified the code changed powershell concept and made below code.

In this post I shall tell you about how you monitor your all servers. The only thing you need is SQL Server which runs with an account having admin privilege on the network, or you can monitor those systems, the account having admin rights.

Lets start-

Create a database and two tables-

----------------------------------------------------------------------

Use master
Go
Create Database DBAMonitor
GO
Use DBAMonitor
GO 

CREATE TABLE [dbo].[tbl_ServerInfo](
 [Id] [int] identity(1,1) Not NULL,
 [WindowsServerName] [varchar](100) Not NULL,
 [SQLinstanceName] [varchar](100)  NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl_ServiceInfo](
 [SQLServiceName] [varchar](500) NULL,
 [servicestatus] [varchar](100) NULL,
 [windowservername] [varchar](500) NULL,
 [startmode] [varchar](100) NULL,
 [UpdateDate] [datetime] NULL DEFAULT GETDATE(),
 [startname] [varchar](1000) NULL,
 [InstanceName] [varchar](1000) NULL,
 [ServerID] INT NULL
) ON [PRIMARY]

----------------------------------------------------------------------

Please add your server details on tbl_serverInfo you want to monitor-

----------------------------------------------------------------------

--For example

insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('Proddb','Dashboard')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.512','sql2008r2')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.500','')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.510','')

--Checking details

----------------------------------------------------------------------

Now we need to run below script, but make sure xp_cmdshell is enabled. To enable it, please run below code-

----------------------------------------------------------------------

sp_configure 'Show Advanced Options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
go   
reconfigure
go

----------------------------------------------------------------------

Now run the script and look the power-

----------------------------------------------------------------------

set NoCount on
--Truncate table DBAMonitor..[tbl_ServiceInfo]
-- Variable to store windows server name
DECLARE @server_name varchar(100)
DECLARE @SQLInstance_name varchar(100)
DECLARE @SrvrID int
-- table to store PowerShell script output
CREATE TABLE #ServiceInfoData
(line varchar(max) null)
-- Declaring cursor to fetch windows server name
DECLARE server_cursor CURSOR FOR 
select distinct LTRIM(rtrim(windowsservername)) as windowsServerName,SQLInstanceName,Id from DBAMonitor..[tbl_ServerInfo]
OPEN server_cursor
FETCH NEXT FROM server_cursor 
INTO @server_name, @SqlInstance_Name,@SrvrID
WHILE @@FETCH_STATUS = 0
BEGIN
declare @svrName varchar(255)
declare @sql varchar(800)
set @svrName = @server_name
-- Preparing PowerShell Dynamic Statement
set @sql = 'powershell.exe -c "get-wmiobject -Class win32_service -Computername ' + QUOTENAME(Isnull(@svrName,''),'''') + ' | where {$_.name -like ''MSSQLServer'' -OR $_.name -like ''MSSQL$' + Isnull(@SqlInstance_Name,'') + ''' -OR $_.name -like ''SQLSERVERAGENT'' -OR $_.name -like ''ReportServer$' + Isnull(@SqlInstance_Name,'') + ''' -OR $_.name -like ''SQLAgent$' + Isnull(@SqlInstance_Name,'') + ''' } | select-object  Name,state,systemname,startmode,startname | %{$_.name+''|''+$_.state+''%''+$_.systemname+''*''+$_.startmode+''@''+$_.startname+''!''} "'
-- Inserting PowerShell Output to temporary table
--print @sql
insert #ServiceInfoData 
EXEC xp_cmdshell @sql
-- Deleting the rows which contains error or has not sufficient data
delete from #ServiceInfoData  where len(line) < 30
update #ServiceInfoData set line = line + '!' where line not like '%!%'
IF (SELECT COUNT(*) FROM #ServiceInfoData where line like '%Get-Wmi%') = 0
begin
insert into DBAMonitor..[tbl_ServiceInfo](SQLServiceName,servicestatus,windowservername,startmode,startname,InstanceName,ServerId)
select  rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as SQLServiceName 
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))) as ServiceStatus
--,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))) as WindowsServerName 
,@server_name
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1, (CHARINDEX('@',line) -1)-CHARINDEX('*',line)) ))) as startmode
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1, (CHARINDEX('!',line) -1)-CHARINDEX('@',line)) ))) as startname
,@SQLInstance_name
,@SrvrID
from #ServiceInfoData
where line is not null and LEN(line) > 30
end
-- Clearing output table
truncate table #ServiceInfoData
-- Next windows record
FETCH NEXT FROM server_cursor 
INTO @server_name,@SQLInstance_name,@SrvrID
END 
CLOSE server_cursor;
DEALLOCATE server_cursor;
-- dropping the temporary table
drop table #ServiceInfoData
Select 
Srvr.windowsServerName
,Srvr.SQLinstanceName 
,sevi.SQLServiceName
,sevi.startname [ServiceAccount]
,sevi.servicestatus
,sevi.UpdateDate [StatusTime]
,Case when sevi.serverid is null Then 'Server is down or Access denied!!' Else 'Check status!!' End [Remarks] 
From DBAMonitor..[tbl_ServerInfo] srvr
Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid

---------------------------------------------------------

For better results, Create a job and run the above script from sql agent.

You can also create mailer for same to notify.

The below code is to notify if any service is stopped or any server is down.

----------------------------------------------------------------------

Declare @MailSubject varchar(1000)='Monitor info for Windows Servers and Sql Services'
Declare @MailBodyHeader varchar(3000)='Hi,<br><br>Please find the details of servers or services, which are not working perfectly-<br><br>
  <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" style="border-top:1px solid black; border-left:1px solid black;"><TBODY>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="3" WIDTH="100PX" Color="white"><B>Server Monitor Info</B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="2" WIDTH="100PX" Color="white"><B>Status </B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Windows Server Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Sql Instance Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Sql Service Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Service Account</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Service Status</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Status Time</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Remarks</B></FONT></TD>
           </TR>'
Declare @MailBody varchar(max)
Select @MailBody= Isnull(@MailBody,'')+'<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Srvr.windowsServerName,'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Srvr.SQLinstanceName,'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.SQLServiceName,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.startname,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.servicestatus,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(sevi.UpdateDate as varchar(30)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Case when sevi.serverid is null Then 'Server is down or Access denied!!' Else 'Check status!!' End +'</FONT></TD>
           </TR>'
     From DBAMonitor..[tbl_ServerInfo] srvr
     Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid
     Where servicestatus<>'Running' or servicestatus is null

Set  @MAILBODY= @MailBodyHeader +@MAILBODY+'</Table>'
IF((Select Count(1) from DBAMonitor..[tbl_ServerInfo] srvr Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid Where servicestatus<>'Running' or servicestatus is null)>0)
Begin
EXEC MSDB.DBO.SP_SEND_DBMAIL 
     @RECIPIENTS ='vimal.lohani@gmail.com',
     @COPY_RECIPIENTS = 'vimal.lohani@gmail.com',                                   
     @BODY = @MailBody,
     @SUBJECT = @MailSubject,
     @BODY_FORMAT = 'HTML',
     @PROFILE_NAME ='dbamail' --Your profile name
End

----------------------------------------------------------------------

You can also schedule above script through agent.

Reference

MSSqlTips