Replication Regular activities...

How to Stop Replication on select article(s)

If you want to stop replication on certain articles follow these steps.

1. Open SSMS, connect to the instance that is running the publication (source) that you want to remove articles from replication.
2. Expand Replication, then Local Publications. Look for the publication you want to stop replicating those articles. Right click on the publication and click Properties.

3. Click on Articles and check Show only checked articles in the list.
• Uncheck the article(s) you no longer want to replicate.

• If a warning pops up, click Yes.
–Please Note this will cause your snapshot to be invalid. So a new one will have to be created and this can cause the article to be locked until the data is BCP’ed out (copied to a file on the drive). This does not take that long. However, the re-loading of those articles into the subscription may take time since the new article(s) will have to be reloaded.

• If you see this pop-up, click Mark For Reinitialization.

-This will cause the subscriber to be reinitialized.

• Click OK.
• This does NOT delete the article from the subscriber (destination). So, any new records that are added to the source table do NOT get replicated to the subscriber.
4. Verify changes.

How to Restart Replication on articles after a schema change

If you stop replication (remove the article from the publication) for a table you want to make schema changes to and then you want to add it back into replication, follow these steps. Again if Alter table is used this is not necessary but if you are modifying millions of records it might be a better approach to do this then add it back.

1. Open SSMS, connect to the instance that is running the publication (source) and the source where you made schema changes to.
2. Add the article back into replication.
3. If the article(s) existed on the subscriber, no need to delete these article(s) first.
4. Expand Replication, then Local Publications. Look for the publication from which you want to stop replicating those articles. Right click on the publication and click Properties.

5. Click on Articles and uncheck Show only checked articles in the list.
• If you see a red circle with a line through it, that means the article does NOT have a primary key and you need to add one. Close the window and create a new primary key.
-Example: alter table Table_1 add primary key (Test)

• Go back into the properties for the publication (steps 3-4 from above)
-Check the article(s) you want to add to replication.

-Click OK.
6. Right click on the publication you want to push the new article(s) you just added to the publication. Click on View Snapshot Agent Status.

7. Click on Start to start the snapshot.
• Please Note a new snapshot will have to be created and this can cause article(s) to be locked until the data is BCP’ed out (copied to a file on the drive). This does not take that long.

8. After a short time, the new article(s) will be synced to the subscriber without initializing all previously synched articles with transactional replication. With snapshot replication, all tables will be refreshed so the create date(s) will be the same.
9. Verify changes.

How to add a new article to the existing Publication

If you create a new table and want to add it to the existing publication, follow these steps:

1. Open SSMS, connect to the instance that is running the publication (source) that you want to add article(s) to replication.
2. Verify that the table you want to add exists with a Primary Key.

3. Expand Replication, then Local Publications. Look for the publication you want to add those articles to replication. Right click on the publication and click Properties.

4. Click on Articles and uncheck Show only checked articles in the list.
• If you see a red circle with a line through it, that means the table does NOT have a primary key and you need to add one. Close the window and create a new primary key.
-Example: alter table Table_1 add primary key (Test)

• Go back into the properties for the publication (steps 3-4 from above).
-Check the new article(s) you want to add to replication.

-Click OK.
5. Right click on the publication you want to push the new article(s) you just added to the publication. Click on View Snapshot Agent Status.

6. Click on Start to start the snapshot.

7. After a short time, the new article(s) will be synced to the subscriber without initializing all previously synched articles.

8. Verify changes...