Prashanth Jayaram
ServiceDetails_JSON1.jpg

Illustration of an example availability monitoring service Using PowerShell and SQL

March 23, 2017 by

This article discusses a simple solution of how to monitor SQL service availability across multiple servers and reporting. To build this I’ll use SQL Server with simple PowerShell script and cmdlets which generate JSON data and displays results in HTML

Article highlights

  • Defines the objective of JSON
  • Explains the PoSH JSON cmdllets internals and other details on its usage
  • Proactive Monitoring of Service(s) over multiple servers – Covers fundamentals of how to Monitor SQL service(s). This sample code can be implemented to monitor any services on a Windows environment
  • Automated Process – Easy to implement If you don’t have a monitoring tool
  • Easier customization of services list and input servers list
  • Seamless Integration of PowerShell and SQL using SQL 2016 JSON constructs
  • HTML Report – shows how to do effective reporting using XML parsing and Database Mail
  • Summary of data

Technologies used

  • SQL 2016 JSON Constructs – OPENROWSET and OPENJSON for JSON data manipulation and transformation into relational data
  • PowerShell Cmdlets – ConvertTo-JSON for Data export, ConvertFrom-JSON for Data import
  • XML Parsing for Prepare HTML tags
  • SQL Database Mail for the Email exchange

An integration of JSON in the relational world provides a robust platform for various automations using PowerShell automation framework. There are several JSON constructs available in SQL 2016 to manipulate and transform JSON data into relation data. The relational architecture in support of JSON provides several benefits in the non-relational world from the point of Migration and deployment. It is flexible because of simple syntax and lesser overhead to maintain and manage the JSON data. Parsing of relational data using XML gives the flexibility to manipulate huge data which will ease out the functioning of reporting

Technical overview

This article will provide an overview of the following technical implementations

  • Various methods and techniques implemented to find the windows service 
  • Details about the purpose and benefits of using JSON
  • Pre-requisites and installation of related Powershelll components
  • cmdlets under the respective library are listed
  • The convertTo-JSON and convertFROM-JSON will explained with an example along with
  • JSON data representation in various tabular formats
  • Finally, the constructs used to manipulate the JSON string will be illustrated with an example
  • Transformation of JSON data using SQL constructs

Let’s discuss the internals of JSON and cmdlets which are used to build the PowerShell script for generating the JSON data

JSON (JavaScript Object Notation)

JSON is an Open standard, lightweight data exchange based on a subset of the JavaScript Programming Language. It is easy for humans to parse and generate value out of each attribute. XML used to be a preferred choice for data exchange over the internet and it enjoyed the top spot for a long time but the gradual tendency of people to shift towards JSON due to its simple structure.

JSON in PowerShell

Microsoft provides a framework and useful cmdlets to work with JSON. The ConvertTo-Json and ConvertFrom-Json cmdlets play a vital role in data transformation. These cmdlets allow working with APIs or classes and returns or accept JSON as an input.

ConvertTo-Json 

The ConvertTo-Json cmdlet converts any object to a string in JavaScript Object Notation (JSON) format. The properties are converted to field names, the field values are converted to property values, and the methods are removed.

You can then use the ConvertFrom-Json cmdlet to convert a JSON-formatted string to a JSON object, which is easily managed in Windows PowerShell.

ConvertFrom-JSON

The ConvertFrom-Json cmdlet converts a JavaScript Object Notation (JSON) formatted string to a custom PSCustomObjectobject that has a property for each field in the JSON string.  JSON is commonly used by websites to provide a textual representation of objects.

Pre-requisite

  • Minimum shell version -> PowerShell 3.0
  • SQL 2016 for Data transformation

Powershell 3.0 is integrated with Windows 8 released on 9/12/2011. This bundle in houses two new functions ConvertFrom-Json and ConvertTo-Json to support JSON.The ConvertFrom-Json is included in the module Microsoft.PowerShell.Utility, make sure that the module is loaded by doing Import-Module Microsoft.PowerShell.Utility before you use ConvertTo-Json. These are the default modules will be automatically loaded with your profile.

How to List and verify the Modules monitor

The next question would be how do I retrieve the available commands from a module?. There are instances that modules are not loaded due to the profile problem. The below commands ensures that the cmdlets are loaded and it’s available for use.

The below sample code retrieves the details of listed services from the remote server(s).

Reading the content of a file in PowerShell is very easy. Use the Get-Content cmdlet with the raw parameter to read the content of JSON file.

Relational data presentation for JSON

Let’s discuss the JSON data representation in a tabular format by importing the JSON file in SQL Server. The OPENROWSET(BULK) is Table Valued function to read data from JSON file. The SQL 2016 contains various JSON constructs for data manipulation and relational data transformation.

  • OPENJSON(BULK) – The TVF(Table Valued Function) reads and content of JSON file and return the value via BulkColumn. The return value is either stored in a variable or table.

This section details the use of OPENROWSET(BULK) and OPENJSON(Bulkcolumn).The OPENROWSET read text value from a file and return it as Bulkcolumn. The Bulkcolumn is then passed to OPENJSON TVF. The function will iterate over the array values to return required values of each attribute.

Let’s execute the SQL to import the JSON data. The JSON file is saved in Unicode format hence have used SINGLE_NCLOB in the SQL. You can also make use of other bulk loading options such SINGLE_CLOB, SINGLE_BLOB depending on the type of the data stored in a file. The below SQL is an effort to define the use of inline JSON data processing.

In-Built JSON constructs for data processing

Processing is made much easier by using the available JSON constructs on referring JSON paths. In this case, the dollar sign ($) references entire JSON object in the input text for processing. The OPENJSON is a table-value function that parses JSON text and returns objects and properties in JSON as rows and columns. The OPENJSON function and with clause lets to define an explicit schema.

This is another way of processing the JSON text using JSON path references.

Reporting

Using the FOR XML PATH the casting is done to the string in the result set but not on each column attributes this itself is a great improvement when you deal with huge data. The use of RAW clause which adds the new table row string at the beginning of each record and the ELEMENTS and the [td] column name help to set the cells tags in the HTML table otherwise, each tag would contain the column name. As you can see, using FOR XML PATH can improve the way we write our reports.

Conclusion

One of the important system parameters to be measured to increase the availability of the application is discussed in this article. The process, described in this article can be implemented by anyone who monitors large and complex environments across multiple servers as the script has the provision of making customization to the services list and server list.

References

 
Prashanth Jayaram

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
Prashanth Jayaram
SQL Database development

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

380 Views