Replicating schema changes - solved, almost -- sql server replication

fritz101
1/21/2004 7:06:32 PM

I'm using the following process to make schema changes to replicate
databases (transactional replication, local distributor, pus
subscription):

- 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 i
Enterprise Manager

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 databas
and performed all these steps successfully.
The snapshot agent creates a snapshot of the changed article (and an
articles related by a foreign key) by:
creating scripts, bulk copying data, and posting snapshot commands int
the distribution database. The distribution agent then applies th
scripts and bulk copies the data.

In my test environment, everything works great. For some reason, o
the production servers, everything works up until the changes ar
replicated. The scripts never get picked up by the distribution agen
and applied at the subscriber (and of course no bulk copying take
place), therefore my schema changes never replicate. Its as if th
distribution agent is unaware that a snapshot needs to be applied
whereas on my test servers, during snapshot generation, th
distribution agent states that its waiting for the inital snapshot o
article "articlename".

Any ideas on where to start looking for the problem on my productio
servers?

Thank

fritz10
-----------------------------------------------------------------------
Posted via http://www.mcse.m
-----------------------------------------------------------------------
View this thread: http://www.mcse.ms/message308964.htm

Paul
1/23/2004 11:33:36 AM

HI
What you are doing looks like fine and you say it is working in your test
environment ,. I use the same procedure to do schema changes. Just using
more parameters when adding the article. Perhaps check your pre creation
command ? is it set to drop the existing table on the subscription db. . Is
it generating the snapshot at all ?

----------------------------------------------------------------------------
--------
exec sp_addarticle @publication = N'Publication'
, @article = N'Table_Name' -- name of table you are adding to publication
, @source_owner = N'dbo'
, @source_object = N'Table_Name'-- name of table you are adding to
publication
, @destination_table = N'Table_Name' -- name of table you are adding to
publication
, @type = N'logbased'
, @pre_creation_cmd = N'drop'
, @schema_option = 0x00000000000000F3
, @status = 16
, @ins_cmd = N'CALL sp_MSins_Table_Name' -- Custom procedure to create for
Ins . del and update based on table name
, @del_cmd = N'CALL sp_MSdel_Table_Name' -- Custom procedure to create for
Ins . del and update based on table name
, @upd_cmd = N'MCALL sp_MSupd_Table_Name' -- Custom procedure to create for
Ins . del and update based on table name
GO

sp_refreshsubscriptions 'Publication'

Start snapshot agent

Paul

sp_refreshsubscriptions 'casino'
[quoted text, click to view]

sql server replication : Replicating schema changes - solved, almost