I have encountered this problem multiple times and it seems that a lot of people think that you have to recreate the publication / subscription in order to add articles. This is *NOT* the case.
It is actually a very simple task!
Right-click on your publication and click ‘Properties’
Go to the articles listing and uncheck the ‘Show only checked articles in the list’ box.
Now you can add a new table to your replication.
Next: The key step — go to the Agents tab inside of replication monitor for the publication in question. Right click on the Snapshot agent and select ‘Start Agent’. This will snapshot the table and send it across!
Questions? Comments? Leave a note!
One thing that was strange to me is that with the advent of Availability Groups in SQL Server 2012 is that there isn’t a built in way to have SQL Agent Jobs run specifically for the active AG.
The workaround I have gone with is just adding a statement around the Job Step and then deploying the new version to both [all] nodes.
The script is very simple:
IF( select CASE
WHEN primary_replica = @@servername Then 1
from sys.dm_hadr_availability_group_states) = 1
–Here is where you would put the code for your job step
And that is it!
Deploy to all of your nodes and you are set moving forward no matter which node becomes the primary.
Questions? Comments? Please post!