#28.Monitoring SQL Instances – Automation

Monitoring SQL Instances – Automation

Why?

                Consider a scenario if the SQL databases are not accessible by the users (Really a disaster situation that a DBA don’t likes always). There may be number of reasons behind the scene.

 * The data access for the particular user would have lost, some connectivity lags on network

* Some LUN would have got disconnected from the server

* The server would have went out of domain

 *The windows server would have patched but not yet came up after its restart.

It’s the role of any SQL admin to check & keep updated about his Servers / SQL Instances status at any time. Bring back SQL online by understanding the issues. Then do a R&D on the issue to get a permanent fix. Here we are going to automate a process through which we will receive an alert email when the particular SQL instance is not available.

How?

                Being fond of SQL, I would trust SQL than any other toolJ. Also why we need to pay for a tool if we get everything within SQL. The idea behind this automation is to check whether we can connect to various SQL instances using SQLCMD utility & if we can’t make a connection then using the stored procedure sp_send_dbmail we are going to alert the inbox of DBA. Let’s begin

 Basics:

                We can have a look on SQLCMD utility from command prompt. I am going to use following in a command shell,

FOR LOOP à this will help us to connect multiple SQL instances one by one which we had specified on a text file.

FOR /F ["options"] %%parameter IN ('command_to_process') DO command

SQLCMD –S à to specify the SQL instance name to which current connection being made.

SQLCMD –Q à to specify the query which will run on the SQL instance & the connection will get disconnected once the query ends.

Consider this code,

SQLCMD –S SQL2012 –Q “Select Name as System_databases from sys.databases where database_id<5”

We will run this query & see the output will be similar to that of a query window.

|| (pipe symbol) à this will behave like or condition. It will execute the command present at right of this pipe if the command present at left side of this pipe fails

Sp_send_dbmail stored procedure:

                This is a stored procedure present in MSDB by default, which helps in sending mails from SQL server to recipients. We are going to use just 4 parameters of this stored procedure in our automation.

@profile_name à the mailing profile which you already created in your SQL server.

@recipients à mention the personal email IDs or the group email ID that should get notified when your SQL instance is down,

@subjectà appropriate subject for your email.

@body à the message you want to deliver to the recipients.

Code:

                I had designed a 2 line code by using above mentioned things. I had bifurcated my servers into 2 lists. Servers.txt consists of SQL servers with default instances. Servers_namedinstances.txt has all SQL servers with named instances on it.

For /F %%A IN (D:\DBATEAM\Servers.txt) DO SQLCMD -S %%A -Q "Exit" || SQLCMD -S "SQL2012-1" -Q "msdb..sp_send_dbmail @profile_name = 'Mailing Profile', @recipients='DBATEAM@mail.com', @subject='Server Connectivity Alert on %%A', @body='The SQL Connectivity is unavailable in %%A,Kindly Check the same asap'"

For /F %%A IN (D:\DBATEAM\Servers_namedinstances.txt) DO SQLCMD -S %%A -Q "Exit" || SQLCMD -S "SQL2012-1" -Q "msdb..sp_send_dbmail @profile_name = 'Mailing Profile', @recipients='DBATEAM@mail.com', @subject='Server Connectivity Alert on %%A', @body='The SQL Connectivity is unavailable in %%A,Kindly Check the same asap'"

                The FOR loop will get each server name and assign it to %%A variable. This in turn passed through SQLCMD –S, if the SQL SERVER is online, the connection will be made & simply the connection will get terminated as we had mentioned –Q “EXIT”.

                If the connection is not made then the second part of the script will be executed on which we connect to the default SQL INSTANCE of SQL2012 and the SQL stored procedure “msdb..sp_send_dbmail” will be executed in SQLCMD which will send an alert email to DBATEAM’s mailbox.

                We will save above mentioned code as a batch file SeverConnect.bat & will trigger the same from SQL SERVER Agent job of SQL2012 server. Remember the SQL server agent service account should have minimum of public rights on all our servers mentioned on those text files.

For ex:

Servers.txt consists of 2 servers SQL2012-1, SQL2012-2.

Servers_namedinstances.txt consists of SQL2012-1\DBA, SQL2012-2\DBA

                To run a batch file from SQL SERVER we can use xp_cmdshell stored procedure. We need to enable xp_cmdshell on SQL2012 before we schedule the batch file. We can enable the same using below code.

        -- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1;

GO

-- To update the currently configured value for advanced options.

RECONFIGURE;

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1;

GO

-- To update the currently configured value for this feature.

RECONFIGURE;

GO

After enabling xp_cmdshell, On running below query the batch file will be executed from SQL SERVER query window now, It will show output beneath it.

EXEC MASTER..xp_cmdshell ‘D:\DBATEAM\Serverconnect.bat’

               

Thus if any SQL instance is not available online you will receive an email in below format. I had intentionally shutdown SQL2012-1\DBA SQL instance now.

Thus by scheduling the same for a frequency of 2 minutes we ensure that we are all set for monitoring our SQL instances with help of SQLCMD.

========================================================

Thanks for coming till this point!!
VSG
SQL DBA