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.