Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Monitor a SQL Server Cluster using PowerShell

MSSQLTips author Ben Snaidero By:   |   Read Comments (2)   |   Related Tips: More > PowerShell
Problem

Since it was first released with Windows 2003 back in 2006, PowerShell has become more and more widely used for different administrative tasks in a Windows environment. In this tip we will take a look at how PowerShell can be used to monitor a SQL Server cluster.

Solution

When monitoring any SQL Server instance, whether it's clustered or stand-alone, there are a few areas that we can check to see if our system is experiencing any high level issues. These would include:

  1. Checking the status of the SQL Server services. For a windows cluster this would include checking all the cluster groups and individual resources.
  2. Checking the Windows event log. For a windows cluster this entails checking all the nodes that make up the cluster whether they have an active SQL Server instance running on them or not.
  3. Checking the SQL Server error log file. This would be similar to the process we would follow for checking a stand-alone SQL Server instance.

There are a couple things I'd like to note before getting into the details of this tip. First, although all the commands should work on other versions, this tip was tested with PowerShell 2.0 on a machine running Windows Server 2008 R2 with SQL Server 2012 installed. Second, since most of the commands we are running are administrative commands you will need to launch PowerShell "as administrator" otherwise you will get the following error. You can read here for details on how to run PowerShell as administrator.

Powershell Error

Check Windows SQL Server Cluster Status with PowerShell

Checking that the SQL services are online with a windows cluster is a little different than checking SQL services on a stand-alone instance due to the fact that the services are stopped on the inactive node(s). Before we can get started using the cmdlets in PowerShell we need to import the FailoverClusters module which we can do with the following code.

PS C:\> Import-Module FailoverClusters

In order to get the status of all the cluster groups in our cluster we can use the Get-ClusterGroup cmdlet passing in the cluster name parameter. The example below shows the result of running this command. In this example we see that we have two SQL Server instances installed on this cluster.

PS C:\> Get-ClusterGroup -Cluster SQLTESTCLUST 

Name                          OwnerNode        State
----                          ---------        -----
SQL Server (INST1)            SQLTEST01        Online
SQL Server (INST2)            SQLTEST01        Online
Cluster Group                 SQLTEST01        Online
Available Storage             SQLTEST01        Online

Next we can drill down into the resources within the SQL Server related cluster groups. To do this we first use the Get-ClusterResource cmdlet again passing in the cluster name parameter and then pipe that output through the Where-Object cmdlet to give us only the SQL Server group resources. Below is an example of this command and it's output. Note: We also added the Sort-Object cmdlet so each instances resources are grouped together.

PS C:\> Get-ClusterResource -Cluster SQLTESTCLUST | 
                        Where-Object {$_.OwnerGroup -like "SQL Server*"} |
                        Sort-Object -Property OwnerGroup

Name                          State          Group                         ResourceType
----                          -----          -----                         ------------
SQL Network Name (SQLTEST1)   Online         SQL Server (INST1)            Network Name
SQL Server (INST1)            Online         SQL Server (INST1)            SQL Server
SQL Server Agent (INST1)      Online         SQL Server (INST1)            SQL Server Agent
Cluster Disk 1                Online         SQL Server (INST1)            Physical Disk
SQL IP Address 1 (SQLTEST1)   Online         SQL Server (INST1)            IP Address
SQL Server (INST2)            Online         SQL Server (INST2)            SQL Server
SQL Server Agent (INST2)      Online         SQL Server (INST2)            SQL Server Agent
SQL IP Address 1 (SQLTEST2)   Online         SQL Server (INST2)            IP Address
SQL Network Name (SQLTEST2)   Online         SQL Server (INST2)            Network Name
Cluster Disk 3                Online         SQL Server (INST2)            Physical Disk

In the example above we see that everything is up and running but what if one of these resources was offline. Let's stop one of them and see how we would start it back up. We'll use the Stop-ClusterResource cmdlet and pass in the cluster and resource name parameters. Below is an example which stops the INST1 SQL instance.

PS C:\> Stop-ClusterResource -Cluster SQLTESTCLUST -Name "SQL Server (INST1)"

Name                          State          Group                         ResourceType
----                          -----          -----                         ------------
SQL Server (INST1)            Offline        SQL Server (INST1)            SQL Server

We should also note that because it's dependent on the SQL service the SQL agent also stopped. We can check this using the Get-ClusterResource cmdlet as in the example below.

PS C:\> Get-ClusterResource -Cluster SQLTESTCLUST |
                        Where-Object {$_.OwnerGroup -like "SQL Server*INST1*"} | 
                        Sort-Object -Property OwnerGroup

Name                          State          Group                         ResourceType
----                          -----          -----                         ------------
SQL Server (INST1)            Offline        SQL Server (INST1)            SQL Server
SQL Server Agent (INST1)      Offline        SQL Server (INST1)            SQL Server Agent
SQL Network Name (SQLTEST1)   Online         SQL Server (INST1)            Network Name
Cluster Disk 1                Online         SQL Server (INST1)            Physical Disk
SQL IP Address 1 (SQLTEST1)   Online         SQL Server (INST1)            IP Address

To start both of these resources back up we use the Start-ClusterResource cmdlet again passing in the cluster and resource name parameters as in the example below.

PS C:\> Start-ClusterResource -Cluster SQLTESTCLUST -Name "SQL Server (INST1)"

Name                          State          Group                         ResourceType
----                          -----          -----                         ------------
SQL Server (INST1)            Online         SQL Server (INST1)            SQL Server

PS C:\> Start-ClusterResource -Cluster SQLTESTCLUST -Name "SQL Server Agent (INST1)"

Name                          State          Group                         ResourceType
----                          -----          -----                         ------------
SQL Server Agent (INST1)      Online         SQL Server (INST1)            SQL Server Agent

If there were an issue with the node these services were running and they would not come online for some reason, we could use the Move-ClusterGroup cmdlet to put the entire cluster group on another node in the cluster. The following example illustrates this command. As with the other cmdlets we pass in the cluster and group name parameters, but for this one we also need to tell it what node in the cluster to move the group to with the node parameter.

PS C:\> Move-ClusterGroup "SQL Server (INST1)" -Cluster SQLTESTCLUST -Node SQLTEST02

Name                       OwnerNode                 State
----                       ---------                 -----
SQL Server (INST1)         SQLTEST02                 Online

Finally let's check again using the Get-ClusterGroup cmdlet and make sure all our cluster groups are online and that the instance we moved is on another node.

PS C:\> Get-ClusterGroup -Cluster SQLTESTCLUST

Name                       OwnerNode                 State
----                       ---------                 -----
SQL Server (INST1)         SQLTEST02                 Online
SQL Server (INST2)         SQLTEST01                 Online
Cluster Group              SQLTEST01                 Online
Available Storage          SQLTEST01                 Online

Check Windows Event Log with PowerShell

To check the Windows Event Log we can use the standard Get-EventLog cmdlet to gather any errors just as we would for a stand-alone Windows server. Passing this cmdlet the computer name and limiting the output to the last 24 hours using the after parameter we can then pipe this output through the Where-Object cmdlets to filter out any information and warning messages and show only the errors. We can call this cmdlet 3 times, once for each of the System, Security and Application event logs and select whichever properties from the log are of interest to us. The one addition we will make since this is a cluster is we will also loop through each node in the cluster using the ForEach-Object and Get-ClusterNode cmdlets to get a list of nodes in the cluster. The below example puts all of this together and displays a sample result.

PS C:\> ForEach-Object ($i in (Get-ClusterNode -Cluster SQLTESTCLUST | 
                     Select-Object -ExpandProperty Name))
     {Get-EventLog System -After (Get-Date).addDays(-1) -Computer $i | 
                      Where-Object {$_.EntryType -match "Error"} | 
                      Select-Object -Property TimeGenerated,MachineName,Message
      Get-EventLog Security -After (Get-Date).addDays(-1) -Computer $i | 
                      Where-Object {$_.EntryType -match "Error"} | 
                      Select-Object -Property TimeGenerated,MachineName,Message
      Get-EventLog Application -After (Get-Date).addDays(-1) -Computer $i | 
                      Where-Object {$_.EntryType -match "Error"} | 
                      Select-Object -Property TimeGenerated,MachineName,Message}

TimeGenerated            MachineName              Message
-------------            -----------              -------
1/7/2014 12:31:18 PM     SQLTEST01.domain.com     Faulting application name: wmiprvse....
1/7/2014 12:25:18 PM     SQLTEST01.domain.com     Faulting application name: wmiprvse....
1/8/2014 11:28:25 AM     SQLTEST02.domain.com     The Cluster Service service terminat...
1/8/2014 5:22:23 AM      SQLTEST02.domain.com     services (508) The database engine l...

Check SQL Server Error Log with PowerShell

Although we could check the SQL Server error by scanning the actual file, since PowerShell provides a way to run queries against a database let's use that method instead as it will save us the hassle of checking the registry for the error log location. In order to run T-SQL queries in PowerShell we'll need to import the sqlps module. To do this we can use the following code. One thing to note is you will get a warning when running this command. Please read here for details on this warning.

PS C:\> Import-Module sqlps
WARNING: Some imported command names include unapproved verbs which might make them less 
discoverable.  Use the Verbose  parameter for more detail or type Get-Verb to see the list
of approved verbs.

Once this module is loaded we can use the Invoke-Sqlcmd cmdlet to call the extended stored procedure xp_readerrorlog. We just use the query parameter and provide a T-SQL query just as we would if we were querying using SQL Server Management Studio. Here is an example.

PS SQLSERVER:\> Invoke-Sqlcmd -Query "EXEC master.dbo.xp_readerrorlog 0, 1, N'error', `
null,null,null, N'desc'" -ServerInstance "SQLTEST1\INST1"

LogDate                    ProcessInfo        Text
-------                    -----------        ----
1/7/2014 10:00:17 AM       Server             The SQL Server Network Interface lib...
1/7/2014 10:00:15 AM       Server             Registry startup parameters: ...
1/7/2014 10:00:15 AM       Server             Logging SQL Server messages in file ...

Monitoring with PowerShell from a Central Computer

As you may have noticed in the parameter list for all the cmdlets above I had included the ComputerName or Cluster parameters. Using these parameters allows the same commands to be run from a different computer provided both machines are accessible from each other on the network. These commands can be run on the local machine with these parameters omitted, but I always like to have them there so that they could be run from another machine without modification. Another thing we may need to do to run these commands from another machine is to install the Remote Server Administration Tools (RSAT). If for example the central machine we are using for administration does not have windows failover cluster installed then we will have to install these tools in order to use the failover cluster cmdlets.

Next Steps


Last Update: 1/27/2014


About the author
MSSQLTips author Ben Snaidero
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, January 30, 2014 - 5:38:38 AM - Paul H Read The Tip

Great stuff, very useful. Thanks for writing the article.


Friday, June 20, 2014 - 10:25:03 AM - sudhir Read The Tip

Good Info. I have Windows Server 2008 and have 4 node cluster. It looks like Import-Module FailoverClusters works only on Windows Server 2008R2. Do you have suggestions on how to use powershell for Cluster on Windows Server 2008. 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.