January 25, 2021 at 12:32 am
My goal is to have an FCI across 2 nodes with shared storage (node 1 & node 2) in primary data center on subnet1 and then 2 nodes with shared storage(node 3 & node4) in secondary data center as subnet2. Node1 & Node2 are connected to subnet1 and Node 3 & Node 4 are connected to subnet 2. SQL server setup see this configuration as a multi subnet cluster because node1 and node2 are on the same subnet , this configuration provides additional local high availability. and Node 3 & Node 4 on same subnet this configuration provides additional high availability on secondary location. To replication data between both data center configure AOAG. Do you see any issues with this thought? Before planning to implement this solution, just wanted to get some thoughts from the experts here.
January 25, 2021 at 3:15 am
This was removed by the editor as SPAM
January 25, 2021 at 3:17 am
This was removed by the editor as SPAM
January 25, 2021 at 4:46 pm
No direct problems, but the multi subnet thing is complex. I would hire someone to review the idea. I might recommend SQLHA.com
January 25, 2021 at 5:59 pm
Thanks! Data replication mechanism between the sites would you go with AOAG or SAN replication or any other method?
January 25, 2021 at 6:11 pm
If you use SAN Repl, I don't know how failover works. If you use AOAG, this keeps things in sync. I don't like using replication as a term here, because that is a SQL Server tech. However, this is complex, so get expert help.
January 25, 2021 at 10:52 pm
Thanks. I will plan on using expert help. However, I am not sure either how failover works when SAN repl is at storage level. Would you see any benefits of SAN repl vs AOAG?
January 25, 2021 at 11:15 pm
I don't know how SAN replication will work here. Typically, when you replicate across a SAN, you can't have a secondary node up. The files are being changed by the SAN, and that will cause issues with the SQL Server instance as it seems as though blocks are changing behind the scenes.
There might be better tech here, but typically SAN repl moves blocks, and if they are consistently moved, as in a Point in time, you could start (or attach) a database on the secondary side when the replication is off.
January 25, 2021 at 11:36 pm
Not sure why I said SAN replication. My apologies, I mean to say data replication at storage level. The idea is to be able to flip between datacenters on a regular fashion.
January 26, 2021 at 2:52 pm
It sounds as if you would be better served by setting up an availability group and use Always On.
https://www.sqlservercentral.com/stairways/stairway-to-alwayson
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 28, 2021 at 12:11 am
Thanks! Probably one last question. How would you handle the backups in Availability group when there is a flip between datacenters in a regular fashion?
January 29, 2021 at 4:08 pm
Short answer, I'm not quite sure. This feels more complex than it appears.
January 29, 2021 at 5:14 pm
If you are moving SQL across DC's - then you must put in place a method to copy the local native backups to a shared location reachable from both DC's, or use a different tool/utility to perform backups. Many SAN vendors have utilities that can be purchased and utilized - but it get much more complex to setup and IBM has a tool called SPP that might work well in this situation.
Ideally - you should not be switching between DC's very often. The purpose of having this kind of configuration is really to provide a DR solution in case something happens in DC1 - not for an HA solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 4, 2021 at 5:25 pm
Seems like this solution has a lot of moving parts. From my perspective, I like to use a less complicated solution that is easier for others to support.
Maybe an AOAG with 4 nodes (2 in each DC) . you would simply need 2 IP addresses for the cluster for each subnet and for the listeners. If you were concerned about the roles failing over cross DC you could potentially set the possible owner on each AG role so that in the event of a failure it fails over locally.
Or if you want you 2 separate clusters then you could do as you proposed and set up Node 1 and 2 in DC1 and Nodes 3 and 4 in DC 2. and you could configure a Distributed Always On Availability group between the two. the only downside is you can not link a listener name to a Distributed AG so you would need a little more thought about how your application connections would work.
As for the backups in an availability group situation if you are using SQL Native backups don't reinvent the wheel. ola hallengren scripts maintenance solutions are used by many they can be found at https://ola.hallengren.com/. Set these up on your environment and they will determine if they are running on the primary and will terminate if not. Obviously, the expectation is that you are backing up to Shared storage somewhere or a DFS namespace to make it highly available. if you are utilizing a third-party product such as Veeam they also have the intelligence to assist with identifying which is the primary.
If you are configuring this IaaS in Azure then there is more work around implementing load balancing for your AG implementations but i won't go into that here.
I hope it all goes well for you whichever option you choose.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply