Prashanth Jayaram

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

October 17, 2017 by

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:

  1. Discuss the implementation architecture
  2. XML string manipulation
  3. Prepare the SQL and PowerShell scripts
  4. Create SQL Jobs
  5. And more…

Data Flow Diagram

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

  1. A dedicated SQL Instance has the SQL Server DB Mail configured
  2. In the dedicated instance, a SQL Server agent job is used to schedule SQL Jobs
  3. It contains the stored procedure to perform XML data transformation. Also, it takes care of sending the email to intended recipients, using SMTP
  4. 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.

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.

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:

  1. 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.

  2. 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.

Define the SQL Server Agent Job

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

  1. The input SQL file is placed on the shared path so that Invoke-SQLcmd can be executed to generate XML raw-data
  2. The PowerShell script is used to call the stored procedures which have already been created in a dedicated SQL instance.
  3. Save the below PowerShell script as DBstatus.PS1
  4. 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.

Prashanth Jayaram
Monitoring, PowerShell, T-SQL

About 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