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:
- Checking the status of the SQL Server services. For a windows cluster this would include checking all the cluster groups and individual resources.
- 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.
- 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.
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 DiskIn 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 AddressTo 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
- Read my other tip: Use PowerShell to Send Email Notification for SQL Server Cluster Failover
- Read other tips on PowerShell and SQL Cluster:
- Use the Get-Command cmdlet to find out what PowerShell commands are available and the Get-Help cmdlet to get more details on using these commands
