I'm using the following process to make schema changes to replicated databases (transactional replication, local distributor, push subscription):
[ul]
[li]drop the subscription to the table [/li]
[li]drop the article so we can make changes to it [/li]
[li]make schema changes[/li]
[li]recreate the article[/li]
[li]refresh the subscription[/li]
[li]reinitialize the subscription to the changed article[/li]
[li]run the snapshot agent for this publication, which I do in Enterprise Manager[/li]
[/ul]
Here are the stored procedures used:
For testing, I've even copied over a backup of a production database and performed all these steps successfully.
The snapshot agent creates a snapshot of the changed article (and any articles related by a foreign key) by creating scripts, bulk copying data, and posting snapshot commands into the distribution database. The distribution agent then applies the scripts and bulk copies the data.
In my test environment, everything works great. For some reason, on the production servers, everything works up until the changes are replicated. The scripts never get picked up by the distribution agent and applied at the subscriber (and of course no bulk copying takes place), therefore my schema changes never replicate. Its as if the distribution agent is unaware that a snapshot needs to be applied, whereas on my test servers, during snapshot generation, the distribution agent states that its waiting for the inital snapshot of article "articlename".
Any ideas on where to start looking for the problem on my production systems?
[ul]
[li]drop the subscription to the table [/li]
[li]drop the article so we can make changes to it [/li]
[li]make schema changes[/li]
[li]recreate the article[/li]
[li]refresh the subscription[/li]
[li]reinitialize the subscription to the changed article[/li]
[li]run the snapshot agent for this publication, which I do in Enterprise Manager[/li]
[/ul]
Here are the stored procedures used:
Code:
sp_dropsubscription @publication='pubname', @article='articlename', @subscriber='all'
sp_droparticle @publication='pubname', @article='articlename', @subscriber='all'
eg alter table
sp_addarticle @publication='pubname', @article='articlename', @sourcetable='tablename', @subscriber='all', @force_invalidate_snapshot = 1
sp_refreshsubscriptions @publication='pubname'
sp_reinitializesubscription @publication='pubname', @article='articlename', @for_schema_change= 1, @subscriber='all'For testing, I've even copied over a backup of a production database and performed all these steps successfully.
The snapshot agent creates a snapshot of the changed article (and any articles related by a foreign key) by creating scripts, bulk copying data, and posting snapshot commands into the distribution database. The distribution agent then applies the scripts and bulk copies the data.
In my test environment, everything works great. For some reason, on the production servers, everything works up until the changes are replicated. The scripts never get picked up by the distribution agent and applied at the subscriber (and of course no bulk copying takes place), therefore my schema changes never replicate. Its as if the distribution agent is unaware that a snapshot needs to be applied, whereas on my test servers, during snapshot generation, the distribution agent states that its waiting for the inital snapshot of article "articlename".
Any ideas on where to start looking for the problem on my production systems?
