Replication - To init or not to init that is the question

Replication

We use replication quite heavily at work. We are just getting into availability groups now so previously replication was the only way to get readable data onto a secondary server. Last week I was primary on-call and there were two changes to be implemented on Saturday dealing with replication. One change was adding a table to replication and the other was altering an existing table and an existing view to add a column to both. I was curious if any or all of the changes would require a re-initialization or at the very least that I would have to run the snapshot agent. Like anything I do, I wanted to be sure before I had to do the actual work so I did some testing. I have two test servers with the AdventureWorks database setup to be replicated between them.

First Test: Adding an article to a publication

1. I created a new table in the publisher database 
2. I added the table as an article in the publication 
3. I checked replication status on the Subscriber and it says "The initial snapshot for article 'JohnReplTest' is not yet available." 
4. Ran the Replication Snapshot Agent for the publication on the publisher 
5. Check the subscriber and the table was there and synced. 

Second Test: Adding a view to an existing publication

1. Created a view in the published database 
2. Added the view as an article in the publication 
3. Checked the subscriber but the view was not there and the status said: The initial snapshot for article 'vw_JohnsReplView' is not yet available. (I did not realize you needed a snapshot for a view) 
4. Ran the Replication snapshot agent job for the publication 
5. Checked the Subscriber and the view is now there. 

Third Test: Adding a Column to a Table and to a View

1. Altered the table to add a column 
2. Altered the view to add the same column 
3. Checked the subscriber and the information was there. 

I did not need to do anything additional to get the data across.

Fourth Test: Adding a column with a default value

1. Altered the table to add a column with a default value 
2. Altered the view to add the same column 
3. Checked the subscriber and the column was there with the data. 

I didn't need to do anything additional to get the data across.

Sum Up

I find Replication a difficult technology to work with, there are so many facets that change based on what you are doing. It's easy to go the route of a full re-initialization when something goes wrong but if you have an oversees subscriber you have to consider the time it will take to push all the data out.

Out of my tests I was most surprised that adding a view required running the snapshot agent. I did not think that creating an object that had no data would require a snapshot. The saving grace is the fact that if you have not re-initialized any of the subscribers, the snapshot agent will only publish the new articles not the whole publication. Try these tests for yourself. The best way to learn about something is not just to read but to actually experiment.

Hasta Luego,

RutherfordTX