Windows Server Failover Clustering Quorum Behavior Guide

“A Republic Quorum, if you can keep it.”  -  Ben Franklin.

Your WSFC Quorum is like a Republic, or more accurately, a Democracy. There are many articles out there regarding Quorum voting logic but most are somewhat lengthy. I decided to set out to see how few words I could effectively explain Quorum rules in, so here we go. Don't count this part. Or this. Wait... er... start counting.... NOW.

Windows 2008 and higher:

• A Quorum is the act of n nodes agreeing on a majority. 
• A node is a cluster member, shared disk, or fileshare witness. 
• A cluster can have a shared disk or fileshare, but not both. 
• A majority is defined by greater than 50% consensus. (a tie is not majority)
• Fractions (only) are rounded up to the nearest integer. (2.5=3)
• There is a legacy quorum method called "disk only" wherein one (defined quorum) disk is the only vote. This is considered obsolete because it creates a single point of failure. 

Windows 2008/r2 with Hotfix 2494036 or Higher:

"Nodeweight" was added to revoke a node of its voting privileges (NodeWeight=0). You can use this for nodes in a different site or to ensure that shared disk/fileshare casts the deciding vote. This is generally used in cross-site clusters.

Windows 2012 or Higher:

Dynamic Clustering

"Dynamic Clustering" changes the nodeweight of downed cluster member and effectively reduces the number of participating nodes by one. This works under the following circumstances:

• Prior to the outage, the cluster has achieved quorum (normal under most circumstances)
• Nodes must go down one at a time so the remaining nodes can agree to removed the downed member. If multiple nodes go down simultaneously the dynamic removal will not take place. 

Examples/Illustration:

Closing

Windows Server Failover Clustering is an excellent option for SQL Server, Hyper-V, and other services. Hopefully this understanding of cluster failover behavior enables you to design solutions that better meet the needs of your clients.

Note: It is important to consider how a Quorum is formed when considering patching strategy. 

References

TechNet: Understanding Quorum Configurations in a Failover Cluster
Aeval Shah's Blog: Windows Server 2012 Failover Clustering Dynamic Quorum
Configure Cluster Quorum NodeWeight Settings
Microsoft Support: Cluster NodeWeight hotfix for 2008/r2

HOWTO Connect Powershell to SQL Server

In this article I'll be walking you through using Powershell to connect to SQL database. The objective is to be as straightforward as possible while providing context the whole way. Remember that lines marked with # are comments. Some of these will get long because we're trying to provide in-line context.

Assumptions:

• Powershell 2.0 or higher
• Connecting to a MS SQL server; for other connection strings see the excellent connectionstrings.com
• Appropriate access to the SQL DB in question with your currently logged on account
• Guts. 

Forward (or, WTH are we doing?!)

We'll be using two different objects for this operation: System.Data.SQLClient.SQLConnection and System.Data.SQLClient.SQLCommand. The first is a connection object that allows you to attach to the DB, and the second is a command object that allows you to specify and submit your command. Both these objects are part of the .NET framework, so they should be present on any machine that can run Powershell. Avoid using Microsoft.SqlServer.Management.Smo.Server unless you need to (and you'll know it) because it requires the SQL tools to be installed. 

In this first example, we'll be doing a query and returning the results. I won't be covering specifying UserID and PW in this because that relies on 1> SQL authentication and 2> Stores UserID and PW in the script. Bad idea. 

Setting the Stage

Let's first declare some of our variables to keep things clean down the line.

 #Declare our SQL server name; Make sure to use Server\Instance for instances, or Server:Port if you're on an instance and don't have the access to the SQL Browser service (UDP 1434). Also, try to use the FQDN because it's good practice and will insure NETBIOS issues won't trip you up.  
 $SQLServer="my.server.here"  
 #Provide the database name, i.e. "Northwind"  
 $SQLDBName="DATABASE"  

Now let's get to the meat; create objects, define properties, and open the connection. Here are reference links for the concepts in this section, followed by the comments and code:

System.Data.SQLClient.SQLConnection
System.Data.SQLClient.SQLCommand
ConnectionString Property

 #Create the SQL Connection Object  
 $SQLConn=New-Object System.Data.SQLClient.SQLConnection  
 #Create the SQL Command Ojbect (otherwise all we can do is admire our connection)  
 $SQLCmd=New-Object System.Data.SQLClient.SQLCommand  
 #Set our connection string property on the SQL Connection Object and tell it to use integrated auth, hopefully kerberos  
 $SQLConn.ConnectionString="Server=$SQLServer;Database=$SQLDBName;Integrated Security=SSPI"  
 #Open the connection  
 $SQLConn.Open()  

Now we'll define the query and execute. 

Devguru t-sql reference

 #Define our Command with a parameter (we will cover this below)  
 $SQLCmd.CommandText="SELECT [FOO],[BAR] FROM [dbo].[table] WHERE [COLUMN] = @smalls"  
 #Provide the open connection to the Command Object as a property  
 $SQLCmd.Connection=$SQLConn  
 #Set the WHERE clause in a variable to be referenced in the parameter (See section below)
 $WhereClause="smalls"
 #Prepare parameters  
 $SQLCmd.Parameters.Clear()
 $SQLcmd.Parameters.Add("@smalls",$WhereClause)  
 #Execute this thing  
 $SQLReturn=$SQLcmd.ExecuteReader()  
 #Init arrays to handle multiple returns
 $TheFooReturn=@()
 $TheBarReturn=@()
 #Parse it out  
 while ($SQLReturn.Read())  
 {  
      $TheFooReturn+=$SQLReturn["FOO"]  
      $TheBarReturn+=$SQLReturn["BAR"]  
 }  
 #Clean it up  
 $SQLReturn.Close()  
 $SQLConn.Close()  

Bonus Section: Avoiding SQL Injection

Note the lines $SQLCmd.CommandText="SELECT [FOO],[BAR] FROM [dbo].[table] WHERE [COLUMN] = @smalls", $WhereClause="smalls" & $SQLcmd.Parameters.Add("@smalls",$WhereClause). By using @ to set that value to a parameter we can later associate the parameter to the variable which will sanitize the input if it is provided by an external data source. (Prompt, file, etc.) This will protect from SQL injections; if we had used standard variable in the first place you could inject an inline SQL statement. By adding it as a parameter SQL will essentially treat it as a string. Thanks to my buddy Austin Peters for the education on this. :)

Closing Thoughts:

• You can have multiple SQL connections open at once. When you do so, name your object variables in a way that you can keep track of the DB that variable represents
• To do something other than a query (INSERT, etc.) set the SQLCmd.CommandText appropriately and change the statement SQLcmd.ExecuteReader to SQLcmd.ExecuteNonQuery. 

For more information, see this post by Don Jones. 

No muss and/or fuss SQL DB Mirroring

So I've never really known what "muss" is, but this doesn't have any of that. It's sans muss. (without muss)

I must say that the SQL DB Mirroring error messages (at least as of 2008R2) leave something to be desired. Because of this, I figured I'd write a straightforward HOWTO on the topic. I've performed these steps on SQL 2008R2, but I suspect it will work on other versions as well.

This guide assumes the following:

• You have a DB on the Principal server that you wish to mirror
• That DB is NOT on the mirror target server (drop/delete it if so) 
• You do NOT intend on using a witness server.... using one however won't invalidate this guide, but we don't address the extra couple steps required to do so. 
• Your database is using a "Full" recovery model. (not possible otherwise) 
• You have sa access to both the Principal and Mirror server

Here are the steps, in order:

1. Log onto the Principal server
2. Perform a full backup to disk of the database in question. Ensure this is a new backup that doesn't include any previous backups.
3. Perform a tranlog only backup on the same database. While this step shouldn't be required, I've found that restoring a tranlog backup after the full results in a successful mirror pairing substantially more often. 
4. Copy the backup files to the Mirror server
5. On the Mirror server, perform a full restore of the desired database:
1. On the "General" page, ensure the "To database" is set to the exact same database name
2. On the "Options" page, select "Leave the database non-operational..." "RESTORE WITH NORECOVERY"
6. Right click the newly restored DB and select "Tasks"->"Restore"->"Transaction Log"
1. On the "General" page, select the appropriate file
2. On the "Options" page, select "Leave the database non-operational..." "RESTORE WITH NORECOVERY"
7. On the Principal server, select "Tasks"->"Mirror"
8. Click "Configure Security"
9. Select "No" for "Include Witness Server" and click "Next>"
10. Leave the ports default unless there is a reason to do otherwise. Make sure this port is properly opened on all firewalls (including the local firewall) between the to servers!
11. Select the target server, set the settings per your requirements (default are fine) and click "Next>". Double check that all firewalls are open on and between both servers. 
12. Enter the appropriate service account for this replication. While it is a lesser secure configuration, using the SQL instance domain service account (if you have one) works without issue. NOTE: If your SQL service instance is running as a local user, (including system) you must use certificate authentication which is NOT covered here.
13. Click "Next","Finish" then "Start Mirroring". All should work at this point. Note it is normal for the state on the Mirror server to be "Synchronized/Restoring".

Troubleshooting:
Explicitly grant connection rights to the "Mirroring" endpoint (note that's a name not a concept) to a given service account:
GRANT CONNECT ON ENDPOINT::Mirroring TO "Domain\Username"


Note that if you re-create the mirror it does not delete the endpoint. To delete the endpoint on each server: (assuming you took the default name of "Mirroring")
DROP ENDPOINT MIRRORING



References:
http://msdn.microsoft.com/en-us/library/ms189047(SQL.105).aspx
http://msdn.microsoft.com/en-us/library/ms189127.aspx

Good Luck!