SQL Server Performance Forum – Threads Archive

problem replicating schema changes

I’m using the following process to make schema changes to replicated databases (transactional replication, local distributor, push subscription, all servers are SQL 2000 with SP3):

drop the subscription to the table

drop the article so we can make changes to it

make schema changes

recreate the article

refresh the subscription

reinitialize the subscription to the changed article

run the snapshot agent for this publication, which I do in Enterprise Manager

commands being used: 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?
Thanks!

You can monitor the process using PROFILER on Production and Test environment to assess the information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>