PowerShell with SQL Server
Over the years I have used so many different tools to support managing SQL Server on a Windows Server. We had command shell tools as old as ISQL along with the more recent SQLCMD. We had tools more closely related to SQL Server in the form of DTS and later SSIS. Microsoft also released management APIs in the form of SQLSMO and SQLDMO.
PowerShell is one tool that is somewhat different from all predecessors. It is not a SQL Server specific toolset. It is not a DOS only toolset. It can utilize SQLDMO, but has PowerShell SQL Server specific commandlets allowing you to manage and manipulate SQL Server. In this place, PowerShell has no competitor.
Recently I wanted to monitor that the SQL Server Agent service was active in a SQL Server cluster. If the SQL Server Agent service wasn’t running, I wanted to receive an Email. This turned out to be a very simple task in PowerShell that would be more difficult to do in many other tools. Using PowerShell I had access to the cluster information, the name of the server being tested, access to the Services that were running, and the ability to send Email over SMTP without much work.
Certainly many of the Server Management tool suites provide this kind of functionality and much more. However, there are many instances where the cost isn’t justified, and a simple PowerShell script is a great solution.
I’m curious how many of our readers have become proficient in PowerShell. Are you finding it a tool you keep sharpened or use from time to time? Share your experience utilizing shell scripts when managing your servers by writing in to btaylor@sswug.org.
Cheers,
Ben
PowerShell Nugget
The following powershell script can be run from a windows task scheduler. It tests if the current node is hosting the SQL Server Service. If it is the host, it tests running services for the default instance of SQL Server and SQL Server Agent. If either of those services are not running, it sends an email through SMTP to notify the recipient of the current state of both services.
Script…
Import-Module FailoverClusters;
$EmailFrom = "SqlServiceAlert@Mydomain.com";
$EmailTo = SomeUser@Mydomain.com;
$SMTPServer = "smtp.emailsrvr.com";
$ServerName = get-content env:computername;
$SQLOwnerNode = (get-clustergroup -Name "SQL Server (MSSQLSERVER)").OwnerNode.Name
If ($ServerName -eq $SQLOwnerNode)
{
$ServiceName = "SQL Server (MSSQLSERVER)";
$ServiceStatus = (get-service "$ServiceName").Status;
$ServiceAgentName = "SQL Server Agent (MSSQLSERVER)";
$ServiceAgentStatus = (get-service "$ServiceAgentName").Status;
if ($ServiceStatus -ne "Running" -or $ServiceAgentStatus -ne "Running")
{
$Subject = "$ServerName Production SQL Server Service Account Alert"
$Body = "The $ServerName Production SQL Service Status is _
$ServiceStatus...The $ServerName SQL Server Agent Service _
Status is $ServiceAgentStatus.";
$SMTPClient = New-Object Net.Mail.SmtpClient($SMTPServer, 25);
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential( _
"mySmtpClientLoginName@Mydomain.com", "MyClientPassword")
$SMTPClient.Send("SQLAlert@Mydomain.com", $EmailTo, $Subject, $Body)
}
}
if ($SQLOwnerNode = "")
{
$Subject = "!!!! $ServerName Production SQL Server Service Account Alert !!!!"
$Body = "The SQL Server Service is not currently hosted by one of the Nodes _
in the Cluster.";
$SMTPClient = New-Object Net.Mail.SmtpClient($SMTPServer, 25);
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential( _
"mySmtpClientLoginName@Mydomain.com", "MyClientPassword")
$SMTPClient.Send("SQLAlert@Mydomain.com", $EmailTo, $Subject, $Body)
}
$$SWYNK$$
Featured Article(s)
Troubleshooting problems with SQL Server 2008 R2 Database Mail
In this article, Alexander Chigrik explains some problems that you can have with SQL Server 2008 R2 Database Mail.
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Simplify encryption and key management on your SQL Server. Data thieves are targeting SMB companies because of their inadequa… (read more)