Stairway to AlwaysOn Level 7: Combining FCIs with Availability Groups

  • Perry Whittle

    SSC Guru

    Points: 233794

    Comments posted to this topic are about the item Stairway to AlwaysOn Level 7: Combining FCIs with Availability Groups

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Mike Good

    SSCertifiable

    Points: 7376

    Interesting and well written, but I disagree with your slant.

    Much of this article is based on assumption that your DR instance is providing HA. In my experience, with a moderately busy environment this is not possible...you will not be able to tolerate a synchronous AG secondary across WAN. In such an environment, your 2-node async AG-only solution then has DR but no HA.

    In such an environment, you can readily achieve HA with either an FCI solution or by adding another local synchronous AG replica. The complexity is about the same either way.

    Yes FCI requires shared storage. This may represent higher cost for some shops, or may represent disk savings in others. My company is in the latter camp.

    The drive letter problem is pretty simple to work around without removing nodes from the cluster. One way is to simply configure the shared disk drive letters in advance of installing the SQL FCI. Not a big deal.

    Finally with regard to FCI's lack of automatic failover to DR: you can only have automatic AG failover to DR if your DR replica is synchronous. This is not tolerable for my production setup, and I imagine that is fairly typical? I believe that a common scenario is for the DR replica to be asynchronous, with no automatic failover. If a disaster takes out both your primary and HA nodes, there will be an outage until you manually move bring up your DR AG (and deal with any cluster/quorum issues)...this applies equally whether you're using FCI for HA or using a synchronous AG for HA.

    Two scenarios that we currently support on our production servers include: a) an FCI for HA and an async AG for DR; and b) a sync AG for HA and an async AG for DR. For a number of reasons beyond scope of your article, I am coming to prefer the former more and more over time.

  • Perry Whittle

    SSC Guru

    Points: 233794

    Mike Good (7/1/2015)


    Interesting and well written, but I disagree with your slant.

    Thanks for the feedback

    Mike Good (7/1/2015)


    Much of this article is based on assumption that your DR instance is providing HA. In my experience, with a moderately busy environment this is not possible...you will not be able to tolerate a synchronous AG secondary across WAN. In such an environment, your 2-node async AG-only solution then has DR but no HA.

    This does depend on the organisations ability to maintain a stable site link, again it's cost associated.

    I wouldn't say its totally based on DR providing HA but does sort of single it out a bit

    Mike Good (7/1/2015)


    In such an environment, you can readily achieve HA with either an FCI solution or by adding another local synchronous AG replica. The complexity is about the same either way.

    I wouldn't agree here, but then that's just my opinion

    Mike Good (7/1/2015)


    The drive letter problem is pretty simple to work around without removing nodes from the cluster. One way is to simply configure the shared disk drive letters in advance of installing the SQL FCI. Not a big deal.

    Actually never tried that way round but I would expect that to fail too

    Mike Good (7/1/2015)


    This is not tolerable for my production setup, and I imagine that is fairly typical?

    Is it though?

    I have worked in many organisations who have dedicated cross site links to achieve all this and more.

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • john.richter

    SSC Veteran

    Points: 222

    I admit confusion over the point in the article: "This prevents more than one Failover Cluster Instance from using the same drive assignments."

    My reference here is: "AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups " @ https://msdn.microsoft.com/en-us/library/jj215886.aspx

    On page 7, in the "Asymmetric Storage" section: "By enabling this functionality, you can combine the shared storage solution (FCI) with the non-shared storage solution (availability groups), in a single HA + DR solution. Consequently, this enhancement also enables you to use identical drive letters for shared disk resources across data centers."

    In the following section, "Instance Naming and File Path": "Each FCI has its own shared storage, which is not accessible by nodes in the other data center, and which should use identical drive letters for the disks, as well as identical file paths for database files and transaction log files in both of the FCIs. Identical file path and drive letters are not an absolute requirement, but if file paths differ, you will need to do a manual RESTORE WITH MOVE when you restore the replica databases on the secondary."

    Am I missing the point in some other fashion? Also, the MSDN article is not so clear that the asymmetric enhancement also works for an FCI + Standalone solution, though other notes on the web do say as much.

  • quackhandle1975

    SSChampion

    Points: 10963

    Nice article Perry and I have enjoyed reading the overall stairway. Quick question I have would like to gauge your thoughts on (and anyone else reading this):

    Current client has two standalone db servers (SQL-A / SQL-B for argument sake). For Reporting we use a further standalone SQL instance (SQL-C). We a have pull replication setup configured where a single database from both SQL-A & SQL-B are replicated to SQL-C. The current version/edition is SQL 2008 R2 Standard but bosses have mooted the possibility of SQL 2012 but I would like Enterprise edition for (amongst other things) Availability Groups, so we could use a read only replica for reporting and stop using replication (which can be pain to administer).

    Would it be possible to perform our existing set-up with AG's? My thinking is that I would need a FCI between SQL-A & SQL-C but also one between SQL-B & SQL-C? Would I need two instances on SQL-C?

    TIA

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • Mike Good

    SSCertifiable

    Points: 7376

    Over 3 yrs ago we replaced most of our replication with a readable AG secondary replica. We have never regretted this. Replication worked ok, but was much more problematic.

    To do this you don't need any FCIs at all, just need to have a "cluster" (WSFC) that encompasses all three nodes A, B and C.

    If you need to have high availability then easy ways to address this include FCIs or synchronous AGs. You did not mention HA, so seems to me maybe this is not a requirement? If you decide it is, I think you could use nodes A and B to provide HA for each other, and leave C as a reporting-only async readable AG replica. Absolutely cannot overlap an FCI with your readable replica containing same data, so no way you can have either FCI extended to node C.

    PS -I think getting from where you are now to where you want to be with minimal downtime will require some thought/planning. This is not hard at all if you have 3 more boxes/hosts that you can just migrate into. Otherwise the AG "no overlap" rule makes the resulting shell game somewhat challenging.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply