2

I have a 2-node Always On Availability group cluster in SQL Server 2016. 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?

Is this fully automatic? I.E. I simply go ahead and create my table on the primary in an availability database and it will then get replicated to the secondary? Or will something weird happen? I'm worried that there might be cases where it doesn't work and replication between the primary and secondary is halted. For example, what happens if SQL Server Management Studio decides to drop and recreate the table? Will this work as expected? Are there any gotchas to be aware of?

Thanks in advance, and any help is greatly appreciated. Am a long-time SQL Server user but am new to Availability Groups.

4

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:

1
  • Thank you! Great answer
    – A X
    Apr 8 '20 at 19:31
1

Always On Availability Groups are not the same as any of the replication methods. They work by sending modified pages of data from primary to secondary. The only thing you need to be concerned about is if you are in synchronous or asynchronous mode and if your change is large enough to require noticeable amounts of time to get back to a synchronized state.

For example, rebuilding an index on the primary will take time. Those modified pages must be sent to the secondary. If you are in synchronous mode, then your transaction (and any locks) won't complete until the secondary has finished. If the connection is slow enough then this could be some time. If you are asynchronous, then you could see yourself falling out of sync for a long period of time, potentially damaging your RTO/RPO if an event happens before it's fully synchronized.

So, create tables, add columns, rename objects. You can't break your Always On Availability Group that way and it's all automatic.

2
  • Thank you so much! Quick question - how can I confirm if I am in async or synchronous replication mode? Is there a query I can run to do that, or perhaps see it in SSMS UI somewhere?
    – A X
    Apr 5 '20 at 20:46
  • Actually, it does not send the modified pages. It send the log records and those log records are replayed on the secondaries. Apr 6 '20 at 12:41

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.