1

We are planning to make the DR as primary and do maintenance on the secondary site(Formerly Primary Site) and hence pausing both the secondaries in always on availability group for a day. I am aware that if both the secondaries are paused then there wont be any failover, but will there be any other problems of pausing the secondaries for a day ...Am i missing something ? Any help will be greatly appreciated !

Can i safely go to the Failover cluster manager and paused both the secondary nodes ?

2

...but will there be any other problems of pausing the secondaries for a day ...

"It depends" but in general, yes. the non-complete list of items:

  • No transaction log truncation on the primary
  • Primary send queue will be large
  • Log caches will become large and may end up consuming more memory than you'd like...
  • Which could cause memory pressure, buffer pool, etc. issues
  • When the connection reconnects it'll need to send all the outstanding data, which it may not ever catch up

Now, this seems pretty grim, however, if the amount of traffic is normally pretty low (which you can guesstimate by looking at your transaction log backup size history) then you might be able to get away with it.

If, however, the amount is very large then you might just want to remove it from the Availability Group, do your maintenance, replay the logs when you bring it back up, and join it back in.

Can i safely go to the Failover cluster manager and paused both the secondary nodes ?

You could but that won't affect SQL Server. You'll need to work with SQL Server through T-SQL or SMO. Pausing the node will only cause you more problems on the primary, but pausing the node on the secondary will tell the WSFC to not move any resources over (which won't happen anyway but this won't hurt anything).

Edit: Moving comments and addressing

On average the total T log Size are 1.5 gb per day

If it'll be down for a week, that's 7 days * 1.5 GB/Day = 7 * 1.5 = 10.5 GB of outstanding data to send and the log file will roughly need to be or grow by this amount - on average. This may or may not be a "big deal" in your environment.

Cant I just switch servers and pause both the secondaries or from the always on dashboard ssms just disable automatic failovers between the primary and the secondary?

So you could do that, again my concerns with it are listed above. "Pausing" the data movement does just that, pauses the data movement. The nodes will still, mostly likely, lose connection and the WSFC will boot some of the nodes out of the cluster - in fact, depending on the overall design and configuration of the WSFC, the entire cluster may go down (worst case).

Since this is the first time, you may want to find someone to work with to go over the design and give you some recommendations. This should be used to create your DR or runbook/playbook/whatever documentation as David suggested.

5
  • One average the total T log Size are 1.5 gb per day Sep 7 '18 at 15:33
  • This will be the first time I will be working on avaialbility group and i am nervous about it . I will greatly appreciate if you can advice what steps i can take once i make the DR as primary and the rest secondary ? Cant I just switch servers and pause both the secondaries or from the always on dashboard ssms just disable automatic failovers between the primary and the secondary Sep 7 '18 at 15:35
  • 2
    Note that this is a pretty good way to test your DR procedures. Sep 7 '18 at 16:06
  • @user9394033 updated. Sep 7 '18 at 16:29
  • Thank you all ! ..SInce the secondaries wont be down more then a day i would like to proceed the following way 1) Change the Availability Mode to Syn Comit on the DR then make sure its synchronised and then failover 2) Change the other two secondaries to asynchronous commit and the failover mode to manual...3) Do the maintenance on the secondaries 4) Change the Availability Mode to Synchrous Commit , make sure its synchornized and then failover ...Please correct me if there is any problem to this approach, as I dont have to touch the cluster or remvoe the secondaries Sep 7 '18 at 16:50

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.