...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.
Best Answer
I am wondering how should I go about adding a new table, or modifying the schema of an existing table in an Always On Availability group without incurring downtime?
If you Always On availability group is set up correctly and sync is working no downtime is required. Connect to the primary (preferably using listener) and do your change in the same way you will be doing in a stand-alone server.
Is this fully automatic?
Yes, it is fully automated.
I simply go ahead and create my table on the primary in an availability database and it will then get replicated to the secondary?
Yes, it will get replicated to all secondary nodes (assuming your sync is working correctly).
Few more resources to read on this: