Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted May 2, 2012

Perform Routine Server Checks Using PowerShell

By Robert Gravelle

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 Journal\2012\Apr> 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 Journal\2012\Apr"
PS E:\Database Journal\2012\Apr> ./ping_server.ps1 localhost
Transcript started, output file is ./ping_results.txt
localhost is REACHABLE
Transcript stopped, output file is E:\Database Journal\2012\Apr\ping_results.txt

And here are the contents of the ping_results.txt file:

**********************
Windows PowerShell Transcript Start
Start time: 20120420095124
Username  : PROD\jxg768 
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 Journal\2012\Apr> ./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. 

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date