Combining AlwaysOn Groups With Failover Cluster Instances

Question

Combining AlwaysOn Groups With Failover Cluster Instances

Perry Whittle

SSC Guru

Points: 234013
More actions
September 3, 2012 at 9:18 pm

#393083

Comments posted to this topic are about the item Combining AlwaysOn Groups With Failover Cluster Instances
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 15 posts - 1 through 15 (of 44 total)

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

jheim Right there with Babe Points: 714 More actions · Answer 1

Good article. Covered the basic configurations that people are "thinking about". Thanks.

I've set up multi-node Oracle RAC that uses a private interconnect for very quick block movement needed to keep the nodes consistent. This limits the distance nodes can be from one-another. (side note: I wonder how fast the interconnect really needs to be since once we accidently had the private traffic going over the slower public network for a time and it worked ok.).

So my SQL Server questions have to do with the new feature of separated disk.

To keep the nodes consistent...is it simply relying on mirror like transaction shipping from DB to DB? Are there issues with speed if non-shared disk instances are far away?

Secondly, it sounds like the replicas with separate disk have to be read-only at best....like mirroring? is that true?

Also, the AO is described as a collection of DBs, not instances....or do I have that wrong? That would make it on a par with mirroring.....mirroring doesn't deal with the system DBs and all they contain (authentication, sql agent stuff, etc.. etc...). Does AO deal with replicating system DBs to instances using separate disks?

My biggest desire is to have something that looks like a cluster (with all its failover wonderfulness) across a long distance with separate disk. I don't mean something that does it halfway like mirroring on a database level....I want the whole instance to be replicated including system dbs. I don't want to script off pieces of the sql server instance and restore them to really get the failover up to speed....like logins and agent jobs and other stuff that changes over time.

I dont need the failover to be updatable....just to be "complete".

P.S. I have read about partial isolation for DB users and its only a halfway fix for that particular issue.

comments please, thanks.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

jheim (9/4/2012)
Good article. Covered the basic configurations that people are "thinking about". Thanks.

Please do not forget to rate the article if you found it useful

jheim (9/4/2012)
To keep the nodes consistent...is it simply relying on mirror like transaction shipping from DB to DB? Are there issues with speed if non-shared disk instances are far away?

It uses mirroring endpoints to communicate between the replicas and operates in the traditional mirroring synch or asynch modes. It's basically mirroring on steroids.

The non shared disks are irrelevant to an extent its the comms between the replicas (sql instances) that are important, same with mirroring.

jheim (9/4/2012)
Secondly, it sounds like the replicas with separate disk have to be read-only at best....like mirroring? is that true?

Yes they're standby or read only

jheim (9/4/2012)
Also, the AO is described as a collection of DBs, not instances....or do I have that wrong?

An AlwaysOn group is a collection of databases that are replicated out to a set of defined partners\replicas.

jheim (9/4/2012)
That would make it on a par with mirroring.....mirroring doesn't deal with the system DBs and all they contain (authentication, sql agent stuff, etc.. etc...). Does AO deal with replicating system DBs to instances using separate disks?

System databases are not replicated, check Books Online there are a whole host or pre reqs and restrictions around AlwaysOn.

jheim (9/4/2012)
My biggest desire is to have something that looks like a cluster (with all its failover wonderfulness) across a long distance with separate disk. I don't mean something that does it halfway like mirroring on a database level....I want the whole instance to be replicated including system dbs. I don't want to script off pieces of the sql server instance and restore them to really get the failover up to speed....like logins and agent jobs and other stuff that changes over time.
I dont need the failover to be updatable....just to be "complete".
P.S. I have read about partial isolation for DB users and its only a halfway fix for that particular issue.
comments please, thanks.

At present contained databases are possible but there are still limitations, again check Books Online for the pre reqs and restrictions

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Eric Higgins Old Hand Points: 325 More actions · Answer 3

4 *, nice explanation.

Only thing I would suggest adding is explanation on how the data from the clustered instance gets to the AO instance. Since this article takes a ground-up approach in explaining the concepts, I think that this would help your readers.

Dustin W. Jones Ten Centuries Points: 1207 More actions · Answer 4

This is a great article (I have rated it excellent), I have been looking for this exact information. One thing I'm not clear about (maybe its just the name of the instance that is throwing me off) is AONode2\CLMaint. Is this node necessary? Is this just another secondary replica?

Dustin W. Jones - Database Tech.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

Dustin W. Jones (11/19/2012)
is AONode2\CLMaint. Is this node necessary? Is this just another secondary replica?

That's exactly what it is, a secondary replica.

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Bert-701015 SSCommitted Points: 1615 More actions · Answer 6

Doesn't really matter since you can have multiple replicas, but can a replica be clustered as well?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Bert-701015 (2/26/2013)
but can a replica be clustered as well?

Yes that's what the article discusses 😉

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Michael Carullo Valued Member Points: 59 More actions · Answer 8

with the 4 node example, what are the possible synchronization/failover options? Can the AO group be synchronously replicated from SQL-CL-INST\SiteLive 192.168.1.51 to the secondaries - AONode1\DRRepl and AONode2\CLMaint? Only synchronous replicas can be automatically failed to so this becomes important.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 9

Thank you for the article.

I have a question about the instance names: do they *have* to be different? If anything, for DR purposes, they instance names should ideally be the same in prod and DR (if we are not using the AG listener).

We are building a 3-node WSFC cluster that includes a 2-node FCI (active-passive configuration) coupled with a standalone AlwaysOn-AG instance on the 3rd node. Will we run into trouble if we name the instance the same on the FCI and AG nodes?

(I was reading an article that described a similar scenario of a single WSFC comprised of 2 FCIs on 2 different subnets; in that article they mention that instance names should be different; I'm not clear why).

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

Marios Philippopoulos (3/26/2013)
I was reading an article that described a similar scenario of a single WSFC comprised of 2 FCIs

Could you provide the article link please

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 11

Perry Whittle (3/27/2013)
Marios Philippopoulos (3/26/2013)
I was reading an article that described a similar scenario of a single WSFC comprised of 2 FCIs
Could you provide the article link please

Here it is:

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

http://msdn.microsoft.com/en-us/library/jj215886

Here is the excerpt I am talking about:

"...

Instance Naming and File Path

The two FCIs must use different instance names within the same WSFC, for example, using “INST_A” as the instance name for the primary FCI and “INST_B” as the instance name for the DR FCI. (In contrast to availability groups, database mirroring permits each FCI to use the same instance name if the FCIs are on separate WSFCs. In Figure 1, both FCIs used the same the same instance name, INST_A, with the FCI+DBM solution).

..."

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

Marios Philippopoulos (3/26/2013)
Thank you for the article.

You're welcome.

Marios Philippopoulos (3/26/2013)
We are building a 3-node WSFC cluster that includes a 2-node FCI (active-passive configuration) coupled with a standalone AlwaysOn-AG instance on the 3rd node. Will we run into trouble if we name the instance the same on the FCI and AG nodes?
(I was reading an article that described a similar scenario of a single WSFC comprised of 2 FCIs on 2 different subnets; in that article they mention that instance names should be different; I'm not clear why).

Since all 3 nodes are part of the same cluster (they have to be for AO), you could potentially install the FCI to the 3rd node. Since instance names are unique within the cluster, this is why you need separate instance names. Incidentally why do you feel that having your live and DR instances named differently will cause you issues??

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 13

Perry Whittle (3/29/2013)
Marios Philippopoulos (3/26/2013)
Thank you for the article.
You're welcome.
Marios Philippopoulos (3/26/2013)
We are building a 3-node WSFC cluster that includes a 2-node FCI (active-passive configuration) coupled with a standalone AlwaysOn-AG instance on the 3rd node. Will we run into trouble if we name the instance the same on the FCI and AG nodes?
(I was reading an article that described a similar scenario of a single WSFC comprised of 2 FCIs on 2 different subnets; in that article they mention that instance names should be different; I'm not clear why).
Since all 3 nodes are part of the same cluster (they have to be for AO), you could potentially install the FCI to the 3rd node. Since instance names are unique within the cluster, this is why you need separate instance names. Incidentally why do you feel that having your live and DR instances named differently will cause you issues??

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 14

We are using a DNS alias for the virtual network name (VNN) of the sql instance on the FCI cluster to direct connectivity of our applications to the prod instance.

Say the VNN is PRODSQLVS01 and the instance name is INST01. We can then connect to PRODSQLVS01\INST01 to get to the FCI instance hosted in nodes 1 or 2. If the DNS alias of the above VNN is BATMAN, then connecting to BATMAN\INST01 accomplishes the same thing. In the event of a disaster in the primary data center hosting nodes 1 and 2, we will then simply point the DNS alias to node 3, the DR node. Our applications will not "know" which server this is, as long as using BATMAN\INST01 will get them connected. But this plan will fail if the SQL instance on node 3 is not also named INST01.

I know the AG listener is the proper way to address this DR scenario, but we will not be able to implement a listener until all our apps are upgraded to .net 4.0.

I was recently able to implement availability groups on a multi-node cluster lab environment using the same instance name for the same AG on multiple nodes with no issues. In that setup I had no FCIs, just standalone instances. I don't see how the requirement for different instance names on different nodes fits into all this.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]