Should I stop the sql server agent?

Question

Should I stop the sql server agent?

forsqlserver

SSCoach

Points: 18902
More actions
April 9, 2012 at 1:32 am

#271600

I have a transactional replication.Now apps team wants 1 column need to be added in one published table.
So How should I Stop/Start the replication.
Should I stop the sql server agent?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 1

forsqlserver (4/9/2012)
I have a transactional replication.Now apps team wants 1 column need to be added in one published table.
So How should I Stop/Start the replication.
Should I stop the sql server agent?

Why?

If you need it in subscriber, it will be replicated to there automatically.

If you do not need it in subscriber, there is an option to disable the automatically replicate the newly added column.

andersg98 SSCertifiable Points: 5180 More actions · Answer 2

It is possible that since you are just adding a column that just running the reinitialize with new snapshots after modifying the table will be fine. If that doesn't work;

You might be able to remove the Article from the publication, reinitialize the subscription (thus removing it from replication) then alter the table, add the Article back into the publication and then reinitialize again.

However, in my experience it is easier to just script out the publication then drop it, make the table modification and then use the script to rebuild the replication.

Jared SSC Guru Points: 61793 More actions · Answer 3

andersg98 (4/9/2012)
You might be able to remove the Article from the publication, reinitialize the subscription (thus removing it from replication) then alter the table, add the Article back into the publication and then reinitialize again.
However, in my experience it is easier to just script out the publication then drop it, make the table modification and then use the script to rebuild the replication.

Why? By default it will replicate schema changes. If that was changed, just change it back.

Jared
CE - Microsoft

andersg98 SSCertifiable Points: 5180 More actions · Answer 4

When it comes to replication I have stopped asking "Why?" or even "WTH? Sometimes when you try a simple schema change it works and sometimes you get errors or other issues that don't seem to make sense but force you to drop the replication and rebuild it. This happens often enough that most of the time we just drop and recreate as standard practice.

Jared SSC Guru Points: 61793 More actions · Answer 5

andersg98 (4/9/2012)
When it comes to replication I have stopped asking "Why?" or even "WTH? Sometimes when you try a simple schema change it works and sometimes you get errors or other issues that don't seem to make sense but force you to drop the replication and rebuild it. This happens often enough that most of the time we just drop and recreate as standard practice.

Hmm... To each his own, but I have never experienced this problem. We have only 10 servers, but all databases on all servers are involved in some form of replication (transactional or transactional with updateable subscriptions).

Jared
CE - Microsoft

forsqlserver SSCoach Points: 18902 More actions · Answer 6

I have stop the sql server agent and then remove the table from articles.

Do the changes and add the table into articles and reinitialize all subscriptions Successfully..

Thanks