July 10, 2018 at 2:40 pm
Hello Everyone,
I am looking for some help with SQL Server AG scripts. I hope to find some help here.
Let's assume I have 3 SQL Server 2102 nodes in my Always on group. Node01 is my primary node. Node02 is the secondary node which is setup as sync replica with auto failover. Both nodes are in the same physical location. Node03 is in a different geo-location and is setup as as async replica with manual failover.
AG Name: AGSuperNova
Node01: CNCTSQLOrion (Primary, Synchronous Commit, Auto failover mode, Readable Secondary)
Node02: CNCTSQLCygna (Secondary, Synchronous Commit, Auto failover mode, Readable Secondary)
Node03: CNCTSQLDraco (Secondary, Asynchronous Commit, Manual failover mode, Readable Secondary)
In a real disaster situation when both Node01 and Node02 become unavailable, what should my script be to force my Node03 to become primary?
I would appreciate if someone could give me a sample script to use for this situation.
SQLCurious
July 10, 2018 at 10:40 pm
You will need to perform a manual forced failover on the 3rd DR replica.
Have a read here , but basically you will need to failover choosing with or without data loss on this replica.
ALTER AVAILABILITY GROUP 'youragnamehere' FAILOVERor with data loss, and once your primary and secondary replicas are available again, resume data movement to sync:
ALTER AVAILABILITY GROUP 'youragnamehere' FORCE_FAILOVER_ALLOW_DATA_LOSS
ALTER DATABASE 'yourdatabasenamehere' SET HADR RESUME
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 30, 2018 at 5:13 pm
Henrico,
Thank you very much for the scripts. I appreciate that very much.
SQLCurious
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply