Database administrators tend to perform a lot of routine checks on a daily, weekly, and monthly basis. Some of these are often referred to as sanity checks. These are checks that monitor the overall health of your databases and servers and may include elements such as connectivity, volumetrics, performance, security, processes, backups, and just about anything else that is essential to the smooth operation of the database servers.
More and more often, these checks are being automated using PowerShell, Microsoft’s latest scripting language for Windows administrators. Why? It’s substantially more powerful than Microsoft’s previous scripting offerings, including VBScript and Wsh. Moreover, many view PowerShell as being the most flexible and easiest way to automate various tedious/time consuming tasks in a fairly simple way.
In today’s article, we’re going to write some PowerShell scripts to automate a couple of fairly common sanity checks.
Verifying the Server Connection
One of the most basic tests is to make sure that each database is accessible for logging in via user applications. Even before checking the database authentication process, it can be beneficial to simply ping the database server. PowerShell supports the ping command, just as it does all DOS commands, but the way that information is returned by ping is not ideal for automated processes. What we are most interested in is whether or not the ping succeeds. For that, the win32_pingstatus object is the way to go.
The get-wmiobject cmdlet is the PowerShell gateway to the Windows Management Instrumentation (WMI). That’s the infrastructure for management data and operations on all Windows operating systems. To use it, we tell it which WMI object we want and supply whatever flags are applicable to that object. Not surprisingly, win32_pingstatus will need a hostname to ping. The Address property holds the value of the address requested. This can be either the hostname (‘wxyz1234’) or IP address (‘193.128.177.124’).
Win32_pingstatus returns several pieces of information: the Source, Destination, IPV4Address, IPV6Address, Bytes, and Time in milliseconds. Here’s the results returned for localhost:
PS E:Database Journal2012Apr> get-wmiobject win32_pingstatus -Filter "Address='localhost'"
Source Destination IPV4Address IPV6Address Bytes Time(ms)
------ ----------- ----------- ----------- ----- --------
LH24CU14296 localhost 127.0.0.1 {} 32 0
Good to know that localhost is available!
In addition to the above information, it also returns a statuscode, which we can access by piping our command to Select-Object. Here is the complete source code for pinging a database server:
param (
[string] $Hostname
)
$status=get-wmiobject win32_pingstatus -Filter "Address='$Hostname'" | Select-Object statuscode
if($status.statuscode -eq 0)
{write-host $Hostname is REACHABLE -background "WHITE" -foreground "GREEN"}
else
{write-host $Hostname is NOT reachable -background "WHITE" -foreground "RED"}
Save that text to a file called ping_server.ps1 and then navigate to the containing directory in the PowerShell window. Issue the following command to run the script:
./ping_server.ps1 [hostname]
Where [hostname] is either the server name or IP address, i.e., 127.0.0.1.
Redirecting Write-Host Output to a File
If you intend to run your scripts as scheduled tasks, you may not be in front of the console when checks are performed. For that reason, the best bet is to save the output to a file. As with most things, it’s easily done in PowerShell.
There are several ways of going about redirecting and/or piping output to a file. The most obvious is to use the > and >> DOS operators. The problem is that they won’t work for write-host statements. For those, we have to use transcription, via the start-transcript and stop-transcript cmdlets.
Start-transcript can accept a number of parameters to suit most common usage scenarios. Here’s the command with the full parameter list:
Start-Transcript [[-Path] <string>] [-Append] [-Force] [-NoClobber] [-Confirm] [-WhatIf] [<CommonParameters>]
Stop-transcript doesn’t require any parameters as it simply stops the transcript that was started by using start-transcript.
In the script, we can choose to output certain lines, or all of them. The following modifications will duplicate the write-host output in either a new file or append to a pre-existing one:
param ( [string] $Hostname ) start-transcript -path ./ping_results.txt -append #main script... stop-transcript
Here’s the resulting output in the PS console:
Windows PowerShell Copyright (C) 2009 Microsoft Corporation. All rights reserved. PS H:> e: PS E:> cd "E:Database Journal2012Apr" PS E:Database Journal2012Apr> ./ping_server.ps1 localhost Transcript started, output file is ./ping_results.txt localhost is REACHABLE Transcript stopped, output file is E:Database Journal2012Aprping_results.txt
And here are the contents of the ping_results.txt file:
********************** Windows PowerShell Transcript Start Start time: 20120420095124 Username : PRODjxg768 Machine : LH24CU14296 (Microsoft Windows NT 5.1.2600 Service Pack 3) ********************** Transcript started, output file is ./ping_results.txt localhost is REACHABLE ********************** Windows PowerShell Transcript End End time: 20120420095124 **********************
By the way, don’t try the transcription from the PowerShell ISE; it doesn’t support it.
Gathering Information on Triggers
Here’s a script that fetches information about database triggers. That requires a bit more coding than the previous example because we need to log into the database to perform this check.
MySQL has a command to retrieve information about triggers called “show triggers”. It returns a resultset, much like a query, that contains several columns about triggers, including the name, event, table, and timing, which our script reports on:
param (
[string] $hostname,
[string] $dbname,
[string] $id,
[string] $pwd
)
start-transcript -path ./trigger_info.txt -append
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
#Create a variable to hold the connection:
$myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection
#Set the connection string:
$myconnection.ConnectionString = "database="+$dbname+";server="+$hostname+";Persist Security Info=false;user id="+$id+";pwd="+$pwd
#Call the Connection object's Open() method:
$myconnection.Open()
#uncomment this to print connection properties to the console
#echo $myconnection
#The dataset must be created before it can be used in the script:
#$dataSet = New-Object System.Data.DataSet
$command = $myconnection.CreateCommand()
$command.CommandText = "show triggers"; #select * from powershell_tests
$reader = $command.ExecuteReader()
#The data reader will now contain the results from the database query.
#Processing the Contents of a Data Reader
#The contents of a data reader is processed row by row:
while ($reader.Read()) {
$reader.GetName(0) + ": " + $reader.GetValue(0).ToString() + ", " +
$reader.GetName(1) + ": " + $reader.GetValue(1).ToString() + ", " +
$reader.GetName(2) + ": " + $reader.GetValue(2).ToString() + ", " +
$reader.GetName(4) + ": " + $reader.GetValue(4).ToString()
}
$myconnection.Close()
stop-transcript
Here is some sample output:
PS E:Database Journal2012Apr> ./get_triggers.ps1 localhost test root admin123#4 Transcript started, output file is ./trigger_info.txt Trigger: ins_pstests_trig, Event: INSERT, Table: powershell_tests, Timing: BEFORE Trigger: upt_inventory_trig, Event: UPDATE, Table: inventory, Timing: AFTER Trigger: ins__cust_trig, Event: INSERT, Table: customers, Timing: BEFORE
Conclusion
Today we saw just how easy it is to perform automated database checks using PowerShell. The two examples used here are just an illustration of what’s possible and thus represent the tip of the iceberg where this topic is concerned.
