0

I am planning to create additional tempdb files for 2 nodes in a SQL 2012 Availability Group. Unfortunately we do not have Instant File Initialisation turned on so the growth of the tempdb data files will be subject to PREEMPTIVE_OS_WRITEFILEGATHER waits for up to 30 minutes (based on testing).

I am concerned that these waits will affect the application/user experience so here is my proposal.

  • Make the change on Node B
  • Disable synchronisation
  • Failover to Node B on successful completion
  • Make the change on Node A
  • Fail back to Node A on successful completion
  • Enable synchronisation.

Does anyone see any issues with this ?

Thanks

Improve this question
5
  • 2
    Why can't you turn instant file init on?
    – LowlyDBA - John M
    Commented Apr 29, 2018 at 15:52
  • it will require an instance restart to take effect and downtime is a concern. So we'll have to fail over to the other node to do that anyway.
    – zapcon
    Commented Apr 30, 2018 at 9:43
  • there may also be a security concern about having IFI on - I will raise this with them.
    – zapcon
    Commented Apr 30, 2018 at 9:44
  • 3
    "fail over ... anyway" Why can't you fail over twice? Once to make the IFI change, and once to change tempdb? Or probably just once to make the IFI change instead of once to expand tempdb and leave IFI alone? Why do you have to disable synchronization? The whole point of an AG is to have failovers where you don't need to manually intervene.
    – Aaron Bertrand
    Commented Apr 30, 2018 at 11:24
  • Thanks Aaron. I was just concerned about any affects on the HA sync whilst the tempdb files were expanding/being created.
    – zapcon
    Commented Apr 30, 2018 at 17:02

1 Answer 1

Reset to default
0

The steps you provide are correct except for the "disable synchronization" and "enable synchronization," which are not needed. If you pause synchronization prior to failing over, you may have some data loss.

The stopping/starting of synchronization occurs automatically when failover is performed, regardless of whether it is automatic or manual. So the process would be:

  1. Assuming node B is the secondary, make the tempdb modifications on node B
  2. Fail over to node B
  3. Make the tempdb modifications on node A
  4. Fail back to node A

If you are concerned that the tempdb changes might cause issues with replication to the secondary, and thereby affect commit times on the primary, you could change the sync mode to asynchronous to accommodate:

  1. Assuming node B is the secondary, configure node B as an asynchronous replica
  2. Make the tempdb modifications on node B
  3. Configure node B as a synchronous replica and ensure that replication is caught up
  4. Fail over to node B
  5. Configure node A as an asynchronous replica
  6. Make the tempdb modifications on node A
  7. Configure node A as an synchronous replica and ensure that replication is caught up
  8. Fail back to node A

I think this is overkill as I don't believe tempdb is used in the replication process, but I can't find an authoritative answer that states that explicitly.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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