Monitoring SQL Server database status changes using T SQL and PowerShell scripts
Monitoring SQL Server database status changes using T-SQL and PowerShell scripts
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. 1234567891011121314151617181920212223242526272829303132333435 --declare table variable to hold multiple resultsetDECLARE @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 @TENDSET @xml = N''+@XML+' '--print @xmlSET @XML_content=@XMLSELECT @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. 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879 CREATE PROCEDURE [dbo].[USP_InsertDBStatus]( @XmlData XML )ASBEGIN 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'' + N'
' ; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'PowerSQL', @recipients = '[email protected]', @body = @Html, @body_format = 'HTML', @subject = 'HIGH Alert : Database Not Online';EndEND;
Author Recent Posts Prashanth JayaramI’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
SQLShack
SQL Server training EspañolMonitoring SQL Server database status changes using T-SQL and PowerShell scripts
October 17, 2017 by Prashanth Jayaram 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. 1234567891011121314151617 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 aStep 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. 1234567891011121314151617181920212223242526272829303132333435 --declare table variable to hold multiple resultsetDECLARE @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 @TENDSET @xml = N'
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. 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879 CREATE PROCEDURE [dbo].[USP_InsertDBStatus]( @XmlData XML )ASBEGIN 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'
Databases not online on '+@Server_Name+'
' + N'| Server Name | Database Name | State | StatusCheck DateTime | ' + CAST ( ( SELECT td= Server, '', td = DBName, '', td = DBState, '', td=StatusCheckDateTime, '' FROM @DBStatusCheck FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'
|---|
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 1234567891011121314151617181920 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 JayaramI’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