By: Edwin Sarmiento | Updated: 2014-09-15 | Comments | Related: 1 | 2 | 3 | 4 | > Clustering
Problem
In a previous tip on Install SQL Server 2008 on a Windows Server 2008 Cluster Part 2, we've seen how to install and configure a Windows Server 2008 Failover Cluster in preparation for installing a SQL Server 2008 failover clustered instance. This time, I wanted to use Windows PowerShell to install, configure and manage a Windows Server Failover Cluster from the command-line or remotely from my workstation. How do I do it?
Solution
To continue this series on Installing, Configuring and Managing Windows Server Failover Cluster using PowerShell, let's explore more PowerShell cmdlets that we can use to perform common administration tasks. While I would be the main administrator for this cluster, I'd like to add more administrators who I can count on to manage this cluster (besides, I would want to take vacations every once in a while). Let's start by exploring who has access to the cluster by using the Get-ClusterAccess cmdlet.
PS C:\> Get-ClusterAccess
This cmdlet lists the different permissions assigned to users of the cluster. It also includes users who have been explicitly denied access to the cluster. I will use the Grant-ClusterAccess cmdlet to grant full control access to the domain account TESTDOMAIN\sarmientoDBA. We will pass the -Full parameter to the cmdlet to grant full access permission. Otherwise, the default will be read-only.
PS C:\> Grant-ClusterAccess TESTDOMAIN\sarmientoDBA -Full
Users who are granted permission to the cluster can access it via the Remote Server Administration Tools (RSAT) installed on their workstation, Failover Cluster Manager locally on one of the cluster nodes and Windows PowerShell. As a security best practice, do not use an account that is a member of the Domain Administrators group when managing servers, but rather one that has limited permissions with local administrative privileges. Also, use domain security groups to minimize overhead of managing individual users. Similar to what was discussed in this tip for SQL Server logins, it also applies to server administrators.
To remove access from the cluster, we can use the Remove-ClusterAccess cmdlet, passing the name of either the domain account or the domain security group.
PS C:\> Remove-ClusterAccess TESTDOMAIN\sarmientoDBA
Remember that while the account might have been explicitly removed from the cluster, this does not prevent it from accessing the cluster should it be a member of other domain security groups that have access to it. For example, if the domain account added is a member of the Domain Administrators security group, it will still be able to access the cluster because of the fact that Domain Administrators are members of the BUILTIN\Administrators group. If you want to explicitly prevent users from accessing the cluster, use the Block-ClusterAccess cmdlet instead.
PS C:\> Block-ClusterAccess TESTDOMAIN\sarmientoDBA
Next, let's explore the properties and parameters of the SQL Server failover clustered instance installed on this cluster. We've already used the Get-ClusterResource cmdlet to explore the different resources running on the cluster, let's use the Get-ClusterGroup cmdlet this time. The Get-ClusterResource cmdlet lists all of the cluster resources, but we might not want that. In cases where the cluster runs more than one clustered resource group that is not SQL Server (for example, a clustered file server), we may only want to see those that are related to SQL Server. Now, this is where appropriate naming conventions for cluster resources become very handy. In my cluster environment, I know that my clustered resource groups have SQL in the name. Therefore, I can use this to list all of the cluster resource groups that are running SQL Server.
PS C:\> Get-ClusterGroup -Name "SQL*"
I used the wildcard character to list all of the clustered resource groups that have the SQL prefix in their name. Since I only have one SQL Server failover clustered instance, I will use this to further explore it's properties using the Get-ClusterResource cmdlet piped to the Get-ClusterGroup cmdlet.
PS C:\> Get-ClusterGroup -Name "SQL*" | Get-ClusterResource
Looking at the result, I can see that my SQL Server failover clustered instance has one clustered drive (F_Drive,) one IP address, a network name, the SQL Server service and the SQL Server Agent service. The reason I mentioned the IP address is because in a multi-subnet/geographically-dispersed cluster, there will be more than one IP address assigned to a resource group as discussed in this tip. Now, if you noticed, the result truncates the names of the resources. We can simply display only the properties that we need to see the full name.
PS C:\> Get-ClusterGroup -Name "SQL*" | Get-ClusterResource | Select-Object Name, State, ResourceType
Let's explore the different properties of the IP address resource.
PS C:\> Get-ClusterResource -Name "SQL IP*" | Format-List *
I'm just showing how easy it is to display all of the properties of a clustered resource without going thru the hassle of opening up the Properties window of the resource in Failover Cluster Manager. But these are just the properties. What about the parameters? We can use the Get-ClusterParameter cmdlet to display all of the cluster resource parameters. For example, similar to what was discussed in this tip, let's see what the parameters are for the SQL Server virtual network name. I've only displayed the properties that I want - Name, Value and IsReadOnly - to prevent the values from getting truncated in the result.
PS C:\> Get-ClusterResource -Name "SQL Net*" | Get-ClusterParameter | Select-Object Name, Value, IsReadOnly
Let's say, we want to change the HostRecordTTL parameter from 1200 (20 minutes) to 600 (10 minutes) because we want our SQL Server virtual network name time-to-live value to be lower than the default. We will use the Set-ClusterParameter cmdlet for this purpose.
PS C:\> Get-ClusterResource "SQL Network Name (SQLCLUSTER)" | Set-ClusterParameter HostRecordTTL 600
A typical task is adding a clustered resource to an existing resource group. For example, let's say you need to provision additional storage for your databases as you are running out of space on your existing drives. The drives need to be added to the cluster first using the Add-ClusterDisk cmdlet and then added to the appropriate cluster resource group. Another option is to add them as mountpoints to your existing clustered drives as discussed in this tip. For this example, I will add the existing available storage G_Drive to my SQL Server resource group named SQL Server (MSSQLSERVER) using the Move-ClusterResource cmdlet. You might be wondering why we are using the Move-ClusteredResource cmdlet for this specific task and not the Add-ClusteredResource cmdlet. That's because the clustered drive is already added to the cluster as a resource and is in the Available Storage resource group. All we need to do is to move this from the Available Storage resource group to the SQL Server resource group named SQL Server (MSSQLSERVER)
PS C:\> Move-ClusterResource "G_Drive" "SQL Server (MSSQLSERVER)"
Once that is done, you can now use this clustered drive for your SQL Server databases and backups without even having to take the SQL Server service offline. However, the cluster resource dependency is not automatically set. Let's examine how the SQL Server clustered resource is dependent on the existing F_Drive. We'll use the Get-ClusterResourceDependency cmdlet for this purpose, passing SQL Server as the name of our clustered resource.
PS C:\> Get-ClusterResourceDependency "SQL Server"
Notice that our SQL Server clustered resource is dependent on the SQL Network Name (SQLCLUSTER) and the F_Drive clustered resources. This means that if we will be using the newly added clustered drive for our databases, we need to make the SQL Server clustered resource dependent on it. The clustered drives have to go online first before the SQL Server clustered resource can come online. Otherwise, we might end up with suspect, corrupt or inaccessible databases. Let's add the G_Drive clustered resource as a dependency to the SQL Server clustered resource using the Add-ClusterResourceDependency cmdlet.
PS C:\> Add-ClusterResourceDependency "SQL Server" "G_Drive"
Be careful with adding the dependencies as you don't want to be making the one dependent on the other. The syntax for the Add-ClusterResourceDependency cmdlet is written such that the dependency resource - in this case, G_Drive - comes after the dependent resource, SQL Server. From the clustered resource Property page, this should look something like this.
The default when adding a cluster resource dependency will be the AND condition. This means that all clustered resource dependencies should be online before the dependent cluster resource comes online. To assign an OR logic dependency as discussed in this tip, the Set-ClusterResourceDependency cmdlet should be used.
In this tip, we used Windows PowerShell to perform the following tasks:
- retrieve the different properties of objects in our cluster,
- manage user permissions,
- managed cluster resources,
- set cluster parameter values,
- move clustered resources between resource groups and
- add cluster resource dependencies.
In the next tip, we will look at performing common cluster troubleshooting tasks using Windows PowerShell. We will keep our focus on running SQL Server clustered resources like a failover clustered instance and AlwaysOn Availability Groups.
Next Steps
- Review the previous tips on Install SQL Server 2008 on a Windows Server 2008 Cluster Part 1, Part 2, Part 3 and Part 4. This will help you map the Failover Clustering PowerShell cmdlets with their corresponding actions on the Failover Cluster Management console.
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-09-15
