3

I'm actually finding a way to provide a good High Availability and Failover solution while I have a way to manage some sort of horizontal scalability and Load-Balancing...

I actually have a SQLServer Failover Cluster with 2 nodes (Active-Passive) but I wonder if could be possible to add more nodes to this cluster and configure some Database from the actual FailOver Cluster in AlwaysON Availability groups as read-only secondary copies.

And in the top of everything some sort of IP Load Balancer... I don't know if something like this could be possible.

3

You can have AGs within an FCI but I don't see the point (I mean when you do this you usually do away with the FCIs unless there was some specific requirement).

In an AG the listener acts similar to a load balancer - kind of. If applications support using application intent read only in the connection string it can offload reads to the "passive" server. It's not load balancing though as it only goes to one server. In 2016 those reads are round robin and so it works better.

If your apps don't support application intent and splitting reads and writes (very few do) though then you're largely out of luck. And you're not going to be able to load balance writes.

In your comment to me you mentioned you'd want to keep the FCI in order to balance load. That isn't really going to work that way with an AG and it would be wasteful of resources - which are one of the best parts of having an AG.

Let's say you have two nodes in an AG and ten databases. You can (if queries don't cross the databases, or only groups of them), create ten groups with a different name each. Then to do balancing you can failover just groups within the node to other nodes until you're happy with it. (Note though that you'll need to do scripting to do this; AGs don't have a notion of preferred nodes to go to automatically unlike FCIs).

As the nodes are "as" available as an FCI there's not many reasons to leave the FCI with its shared storage in place. Those resources are kind of wasted.

  • Well. my point on having FCI and AGs is to have all the databases in the Instance under a FCI and then be able to split load on specific databases that are "resource hungry" all the time. Well, almost all the applications I will host are being queried by BI tools like Microstrategy / Tableau / Qlikview, I don't really know if they support application intent connection... – J1mmy May 13 '16 at 11:47
  • I've tried to expand my answer to explain why the FCI would not be so important to retain and how you'd do the load balancing you mentioned in the way you meant it. – Cody Konior May 13 '16 at 11:54
  • I saw people had asked Tableau and Qlikview to support application intent. I couldn't find it they ever did it. You'd have to ask their sales/support teams. – Cody Konior May 13 '16 at 11:57
  • I see your point on not feeling the FCI usefull in this kind of setup. You could have 2 good servers on FCI with shared storage sharing the dbs and then automatic Failover and then configure the AGs for some of the databases to be replicated on a third sercer with not so good specs just to work as a read only replica and also allowing to balance some resources. I dont know if my proposal is just dumb or useless haha. – J1mmy May 13 '16 at 16:17
  • Maybe my proposal can also be so difficult to develop as you once you failover to the secondary it should be nice that the AGs keep working and replicating. – J1mmy May 13 '16 at 16:21
2

I see your point on not feeling the FCI usefull in this kind of setup.

You could have 2 good servers on FCI with shared storage sharing the dbs and then automatic Failover and then configure the AGs for some of the databases to be replicated on a third sercer with not so good specs just to work as a read only replica and also allowing to balance some resources.

I dont know if my proposal is just dumb or useless haha.

  • FCIs can be useful in mixed topologies like this. It provides local HA, halves the cost of storage, and is generally something people are used to working with. Why Cody Konior decided it wasn't useful I'm not sure of, it's absolutely useful. – Sean Gallardy May 14 '16 at 16:30
  • This works, used in production for at least one year. BI people are happy to have the DBA team off their backs, as they can hammer away at that third node with no impact to the Primary. Async commit, but it's never behind for more than 10s, good enough for us. – Razvan Zoitanu May 17 '16 at 13:10
  • Yeah, thats what I'm planning to do, so then I don't have to "Replicate" all the Databases within my Instance, only the "Critical" ones in order to provide some kind of Load Balancing and Scalability to another servers. – J1mmy May 18 '16 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.