I presented a session titled “SQL Server AlwaysOn Quickstart” on September 10th for the 24 Hours of PASS event.  You can view the session recording HERE if you missed it.  The session was a preview of the full session I will be presenting at the PASS Summit 2014 this November.  I will be presenting the full session on the first day of the Summit, November 5th, 2014 at 10:15am PST.

The session is right before lunch which means I don’t have to hurry out to make room for the next presenter since there is a 2 hour lunch break.  You came to the Summit to learn about SQL Server and get your questions answered so I’ll stay after the session for as long as it takes to answer every question you have.  If I don’t have the answer then I’ll find it for you and I really mean that.  Ask anyone who I promised to followup with after one of my sessions.

Make sure to add my session when you’re using the Schedule Builder to plan your Summit sessions!  Now on to the questions I received during the 24HOP session.

• The FCI needs shared storage and the AG needs local storage. I’m confused on how they can be implemented together.
  • The nodes in your cluster can have both shared storage and local storage.  The AG will use the local storage (could still be SAN attached just not shared) and essentially mirror the DBs on all nodes.  The FCI will use the shared storage and might not even be installed across all nodes in the cluster.  They are absolutely supported together.  Come see my session and I’ll show you how.  Click here to download one of my presentations showing you a sample architecture.
• I noticed you did not add the system databases to your availability group, is there a reason for that?
  • Yes.  Just like mirroring system databases are not eligible and cannot be added to an AG.
• If SQL 2012 supports 1 primary and 4 secondary and SQL 2014 supports 1 primary and 8 secondary, how many are read/write?
  • Only the primary is read/write.  All secondary replicas are read only and you can disable that functionality or limit it if you want.
• Are the replicas synchronized?
  • For a replica to be considered “synchronized” it must be in Synchronous Mode.  You can only have a maximum of two replicas in Synchronous Mode.
• Is it common to use a SQL FCI as a primary replica and another SQL FCI as a secondary replica for protection and still take advantage of AGs for reporting purposes?
  • With the exception of the reporting part this used to be common prior to AGs.  You would have two 2 node FCIs in different data centers and mirror between them.  This gave you HA in both data centers and DR if an entire data center went down.  However, this is not so practical with AGs.  You would be more likely to have 2 replicas in each data center with an AG across them and no FCIs.
• How would you handle getting stored procedures and SQL Agent jobs onto the secondary replicas?
  • You don’t have to worry about stored procedures since those are stored in the database and will be replicated automatically.  SQL Agent jobs will have to be re-created on the secondary replicas and additional code added to exit gracefully if the replica is currently acting as a secondary.  You’ll want them to run automatically if the replica becomes the primary.  See THIS POST for more information on automating logins and thanks to Robert Davis (Blog|Twitter) for writing the code.
• How do you handle installing SQL Service packs after AGs have been implemented?
  • First make sure you have backups (That’s always rule #1).  You’ll want to update a secondary replica first, make sure it is in synchronous mode, and then fail over to it.  Now you can upgrade the remaining replicas and fail back to your preferred replica when done.
• Does SELECT @@SERVERNAME return the listener name or the node name?
  • It returns the node name.  However, older versions of SQL Server (for an FCI) will return the listener name.  The best way to get the node name no matter what version you are on is to SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)
• Wouldn’t it be possible to have an indexed view on a secondary replica to use with reporting off of that replica?
  • You cannot create database objects on secondary replicas separate from the primary.  You have to create them on the primary and they will sync to the secondary replicas.  Remember that secondary replicas are READ ONLY and can never be written to.
• With AGs in automatic failover mode how does the client connection timeout have to be configured?
  • Your applications need to be written to retry the connection.  You should also add MultiSubnetFailover=true to the connection string.  You should consider reducing the TTL of the listener name.  I’ll write another blog post explaining this in more detail, but that is the short answer.
• What is the advantage of having a group of databases in a single AG?
  • If you have several databases that support a single application, if any one of them fails then they all failover together.  If you were mirroring them it was possible that myDB2 could failover to the mirror while myDB1 and myDB3 continued to run on the primary.  That would break the application, but AGs mitigate that risk factor since they stay together within that logical construct.
• Can you have replicas in different domains?
  • No.  All replicas have to be part of the same Windows Cluster which requires that all nodes be in the same domain.  As a previous AD guy I’ll take this a step further and point out that in most situations things are fine to be in different domains so long as they are trusted somewhere in the forest hierarchy.  That is NOT the case here.
• All servers in our environment are in a cluster of 8 nodes. Would that be okay ,or do you still need to create another cluster inside that cluster?
  • You cannot create a cluster within a cluster.  In this case you don’t need to do anything since you already have a cluster, you’re one step ahead of the game.