How To for Replication

Replication is a great way to copy tables/data from one server to another so you can do reporting on the second server. Within replication tables, stored procedure, functions, views, and indexed views are called articles but in this blog tables and articles mean the same thing. SQL Server Standard edition will work with most features of replication. Refer to Microsoft editions and supported features to determine which edition is required to fit your needs. With transactional replication, you can create separate indexes on the destination table that do not exist on the primary table. Indexes that exist on the primary table do not have to be replicated to the destination.

However, there may be a time where you need to deploy new code to your environment that is running transactional or snapshot replication. You will be required to do certain steps before deploying your code/table changes. If you use the designer within SSMS, in order to perform those changes against tables that are being replicated, you need to stop replicating those tables, make your change, then add them back. If you do not do this, replication will stop working and the data in those tables will no longer be sent to the destination server. If you use the alter table statements, those changes will replicate over with no additional steps on your part. You may be wondering why would I bother taking an article out of replication if my schema changes will replicate over as well as all of my data changes. If you change millions and millions of records after making the schema change, you might think about removing that table then adding it back. All those updates will be pushed to the subscriber and replication could break due to the amount of data that is being sent. Sometimes the server that holds the distribution database or the subscriber database can’t handle the amount of records that were sent, due to millions and millions of records being updated. When you look at the replication monitor, the estimated time to apply these commands, based on past performance in the undistributed commands tab for the subscriber may never change or keep increasing as the number of commands increase. The only way to fix this is to find the table that was changed, remove it out of replication then add it back or re-do the whole publication/subscription.

These steps can be followed for both snapshot and transactional (push or pull) replication. With snapshot replication, it can be set up as a manual process or scheduled job. If it is a manual process, you will have to kick off the job that replicates the snapshot to the destination. If it is a scheduled job, you can still kick off that job in order to see those changes right away or you will have to wait until the job kicks off during its normal schedule.

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.