Monitoring SQL Server database status changes using T-SQL and PowerShell scripts

Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays.

It’s also equally important to get alerted or notified when a database is inaccessible, due to any of the myriad of reasons we can think of. This article deals with one of the solutions to monitor the status of the SQL Server database using T-SQL or PowerShell with the help of the native programming and alerting techniques.

Some of us may have many questions regarding this: How do we decide on the monitoring strategies for different environments? How do we automate and manage SQL Server databases? Can the monitoring task be automated using T-SQL or PowerShell or any other tools or techniques? Does the SQL engine provide the required capabilities to schedule a job and run it across multiple servers? What are the customization options available? Do we have a robust method to perform the backup activity?

The monitoring techniques using T-SQL, XML, and PowerShell are used to monitor a SQL Server database using native methods are discussed in this article.

Topics covered in this resource include:

Discuss the implementation architecture
XML string manipulation
Prepare the SQL and PowerShell scripts
Create SQL Jobs
And more…

Data Flow Diagram

Let’s look at the high-level view of this implementation:

A dedicated SQL Instance has the SQL Server DB Mail configured
In the dedicated instance, a SQL Server agent job is used to schedule SQL Jobs
It contains the stored procedure to perform XML data transformation. Also, it takes care of sending the email to intended recipients, using SMTP
The SQL file that contains the code to be executed on the listed SQL instance is placed in a shared path. The path should be accessible from all the SQL instances.

The instance details are the source for this implementation. The server details are stored in a text file. On the dedicated instance, the SQL job is scheduled to traverse across the entire list of servers. The SQL file is copied to a shared path and is executed on each server to generate the XML data for only the offline databases. The generated XML data is then fed as an input parameter to the stored procedure for further data manipulation. In the Stored Procedure, using dynamic SQL, an HTML is prepared and is sent as an email to the DBA’s.

The implementation can also be done using JSON transformation. JSON is supported starting from SQL 2016. Since JSON penetration is not good enough at the time, XML is the preferred choice for data interchange format for now—until JSON takes over as the de-facto format.

Prepare the script

Let’s now go through the steps to create the script. It’s good to have an understanding of the database states, to understand the script.

select

a.name,

a.state_desc,

case

When a.state = 0 then 'ONLINE'

when a.state = 1 then 'RESTORING'

when a.state = 2 then 'RECOVERING'

when a.state = 3 then 'RECOVERY_PENDING'

when a.state = 4 then 'SUSPECT'

when a.state = 5 then 'EMERGENCY'

when a.state = 6 then 'OFFLINE'

when a.state = 7 then 'COPYING - SQL AZURE'

when a.state = 10 then 'OFLINE_SECONDARY - SQL AZURE'

end

from sys.databases a

Step 1: Prepare XML Data

The XML data is generated by querying the sys.databases system object for offline databases. The query results are then transformed as nested XML elements. The simple AUTO option is very useful in generating the raw data.

The below T-SQL statements are placed in an SQL file. This file will be executed across all the SQL instances and the output is then fed to the stored procedure.

--declare table variable to hold multiple resultset

DECLARE @T table (X nvarchar(MAX));

--Variable declaration to hold XML data

DECLARE @XML nvarchar(MAX)

DECLARE @XML_content xml

--Declare table variable

DECLARE @DatabaseState table (DBName varchar(100),DBState varchar(50))

INSERT INTO @DatabaseState

select name, state_desc

from sys.databases

IF (SELECT COUNT(*) FROM @DatabaseState WHERE DBState <> 'Online') > 0

BEGIN

WITH SQLShackDemoCTE AS

(

select @@servername ServerName,DBName, DBState

FROM @DatabaseState status

where DBState <> 'Online'

)

insert into @T(X)

select (

select * from SQLShackDemoCTE

FOR XML AUTO

);

select @xml=X from @T

END

SET @xml = N'<root>'+@XML+'</root>'

--print @xml

SET @XML_content=@XML

SELECT @XML_content

When you click the above XML tag, the data shown like below

Step 2: Create Stored Procedure USP_InsertDBStatus

The XML raw output from the SQL File is fed as an input parameter to the stored procedure.

This stored procedure has two sections:

Preparing the XML

The XML raw-data is processed using the OPENXML clause. The OPENXML clause uses xml-document ID and XML text as inputs and uses the MSXL parser to parse the text and return each element for further transformation or data manipulation.
Preparing the Email

The FOR XML PATH clause is used to generate an HTML table of data. The XML tags are then transformed as SQL columns. The data is then parsed as valid HTML tags. Using Database Mail, an email is sent to intended recipients with the data.

Note: The same output can also derived by using the concept of XML nodes for efficient data processing using the OPENXML clause.

CREATE PROCEDURE [dbo].[USP_InsertDBStatus]

(

@XmlData XML

)

BEGIN

SET NOCOUNT ON;

DECLARE @hdoc AS INT;

declare @State varchar(10)

declare @Database_Name varchar(100)

declare @Server_Name varchar(100)

declare @EmailBody varchar(100)

declare @DBName varchar(max)

declare @Html nvarchar(max)

set @State = ''

declare @DBStatusCheck table

(

[Server] varchar(25) NOT NULL,

[DBStatusID] INT IDENTITY(1, 1) NOT NULL

,[StatusCheckDateTime] DATETIME NOT NULL

,[DBName] VARCHAR(128) NULL

,[DBState] VARCHAR(128) NULL

)

--Create an internal representation of the XML document.

EXECUTE [dbo].[sp_xml_preparedocument] @hdoc OUTPUT, @XmlData;

INSERT INTO @DBStatusCheck

(

[Server]

,[StatusCheckDateTime]

,[DBName]

,[DBState]

)

SELECT [ServerName]=DBstatus.[Servername]

,[CheckDate] = CURRENT_TIMESTAMP

,[DBName] = DBstatus.[DBName]

,[DBState] = DBstatus.[DBState]

FROM OPENXML(@hdoc,'//SQLShackDemoCTE')

WITH (ServerName varchar(100), DBName VARCHAR(130), DBState VARCHAR(130))

AS DBstatus;

EXECUTE [dbo].[sp_xml_removedocument] @hdoc;

select @Server_Name= server from @DBStatusCheck where [DBStatusID]=1

if (select COUNT(*) from @DBStatusCheck) > 0

begin

SET @HTML =

N'<H1>Databases not online on '+@Server_Name+'</H1>' +

N'<table border="1">' +

N'<tr>

<th>Server Name</th>

<th>Database Name</th>

<th>State</th>

<th>StatusCheck DateTime</th>' +

CAST ( ( SELECT

td= Server, '',

td = DBName, '',

td = DBState, '',

td=StatusCheckDateTime, ''

FROM @DBStatusCheck

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'PowerSQL',

@recipients = 'pjayaram@appvion.com',

@body = @Html,

@body_format = 'HTML',

@subject = 'HIGH Alert : Database Not Online';

End

END;

Define the SQL Server Agent Job

Here are some of the points we need to keep in mind:

The input SQL file is placed on the shared path so that Invoke-SQLcmd can be executed to generate XML raw-data
The PowerShell script is used to call the stored procedures which have already been created in a dedicated SQL instance.

Save the below PowerShell script as DBstatus.PS1

Import-csv \\hq6021\C$\InputServer.csv|%{

If ((Test-Connection $_.ServerName -count 1 -quiet))

{

write-host $_.Servername

$sqlcmd=Invoke-Sqlcmd -InputFile \\hqdbt01\F$\PowerSQL\DBStatusSQLQuery.sql -ServerInstance $_.Servername -Database master

$xmldata=$sqlcmd.Column1

if($xmldata -ne '')

{

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;

$SqlConnection.ConnectionString = "Server=HQDBT01;Database=SQLShackDemo;Integrated Security=TRUE;";

$SqlConnection.Open();

$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;

$SqlCommand.CommandTimeout = 120;

$SqlCommand.Connection = $SqlConnection;

$SqlCommand.CommandText = "EXECUTE [dbo].[USP_InsertDBStatus] @XmlData = N'$XmlData';";

$Result = $SqlCommand.ExecuteNonQuery();

$SqlConnection.Close();

}

Create the SQL Job

Follow the job creation process steps to create the SQL job.

Enter the Step name and choose Transact-SQL Script (T-SQL) from the Type dropdown
In the Command, call the PowerShell function
Click OK

The aforementioned steps result in a Multi-Server-DBStatus-Check-Alert job.

Right-click and run the job. You can also schedule it to run across all the instances, at a ten-minute frequency.

The output reveals shows the statuses of the databases.

Wrap Up

In an environment that relies on SQL-managed native methodologies, we could use PowerShell scripts using SMO or T-SQL. Using native methods, sometimes it’s tedious to measure every aspect of the SQL Server database status monitoring. The native methods have a few limitations, which would have to be kept in mind when proceeding. In some cases, this leads to substantial reliance on scripting, which may not be every administrator’s cup of tea. However, there are several third party monitoring solutions available in today’s market, which integrate alerts to ticketing system or send email notifications when the state of a database is not online. It really is about what suits our requirements. We must judge the environment, and work through it. I hope the script in the article helps towards getting an availability report about the managed databases.

Author
Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

Stairway to SQL essentials - April 7, 2021
A quick overview of database audit in SQL - January 28, 2021
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021

Data Flow Diagram

Prepare the script

Define the SQL Server Agent Job

Wrap Up

Related posts: