November 14, 2018 at 1:14 am
Hi People
My Scenario is as follows:
We want to setup SQL Server 2016 with AlwaysON features from head office to DR.
If primary site is down, DR must takeover and become the new primary.
When DR is the new primary, database transactions such as unpdate,delete, insert should be allowed.
When the Primary site is back on line, an autofailback from DR needs to happen.
All databases updated at DR must resync to the primary site and Transactions must start happening at the original primary site.
I need to know whether this is possible without the need to manually perform backup/restore operations back to primary site in order to resync.
November 14, 2018 at 1:55 am
No backup and restore is necessary - Availability Groups are a high availability technology. When your primary site is no longer available, your databases will automatically fail over to the secondary. Users will not notice any difference, except (a) there'll be an interruption to the service of a few seconds and (b) if the secondary is at a remote site, for instance, network latency will increase. As for failing back, I don't think I'd want that to happen automatically. Wait for a quite time and do it manually.
All of the above assumes you have Enterprise Edition: AGs are available in Standard, but there are limitations.
John
November 14, 2018 at 6:13 am
Thanks for the response,
I have been preaching this gospel that this failback is manual and is recommended practise for it to be manual but i SQL is being compared to Exchange AlwaysOn Architecture.
If there were a possibility for automatic failback, what should be done to make this a reality?
November 14, 2018 at 7:09 am
I don't know whether automatic failback is even a thing. But even if it were, how would you want it to work - for failback to occur immediately the primary comes back online? What if you're in the middle of some important process when that happens? What if the primary has to be patched before it's usable again, so it goes online and offline several times in the process - would you want it to fail backwards and forwards between the two nodes until it settles down? Maybe you want failback after the primary has been up for a certain period of time. If that's the case, write a PowerShell script and have it poll the primary to see how long it's been up, and trigger the failback after the specified period of time.
John
November 14, 2018 at 9:47 am
Let's make this easier. Node1 is the original primary and Node2 is the original secondary.
When Node1 comes back, it's not going to failback immediately. It comes back as a secondary and starts to sync with Node2. All transactions that occurred on Node2 need to be transferred back to Node1 to allow it to sync. This isn't necessarily quick and easy, as the database comes back up in a state that might require reseeding. In any case, when things sync, if you need this to fail back, I know you can do this manually. If you want automatic, you'd need a script to logically check and perform the failover.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply