Increasing tempdb data files on 2 node Availability Group

Question

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

• LowlyDBA - John M: Why can't you turn instant file init on?
• zapcon: 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. there may also be a security concern about having IFI on - I will raise this with them.

Answer 1

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.