Problem adding NOT NULL column to replicated table

[DaveH]

I'm new to replication and am trying to determine the best approach to add a column (NOT NULL with no DEFAULT) to a replicated table. The only success I have had is if I do the following:

Delete entire Subscription
Delete entire Publication
Add column to table
Create new Publication
Create new Subscription
Run SnapShot

The problem with this approach is that each step affects the entire database and not just the modified table. I think it is inefficient to redo replication for a simple object change. What am I missing? Is there a way to only replicate the changes made to the one table without having to run a SnapShot for the entire publication?
Keep in mind the column must be defined as NOT NULL and cannot have a Default.

Thanks, Dave

[rmiao]

What's your sql version?

[DaveH]

2000

[Stephen]

You cannot use the GUI to do what you want.

However, you can easily use the replication stored procs to do what you want. Have a look at sp_dropsubscription, sp_droparticle, sp_addsubscription and sp_addarticle.

It is worth generating out the scripts to drop and add the publication. You will see these stored procs being used. You should note that the sp_addsubscription uses "All" for the article name. Similar for sp_dropsubscription. You should specify the name of the arcticle that contains the table you are about to change.

The process you need is similar to your original post but for the one article only not the entire publication



Cheers
Stephen

[DaveH]

I saw the stored procedures you refer to, but it still looks like I would have to run the Snapshot agent again. Wouldn't the Snapshot resend the entire contents of the replicated database and not just the table I have changed? Every time I tried dropping the article and subscription, altering the table and then re-adding the article and subscription SQL Server indicated that it was in a "pending" status waiting for Snapshot to be run. Am I missing something?

Thanks, Dave

[Stephen]

The snapshot agent may need to be run.

If you apply the changes to the table on master copy of the table and to all of the replicas, you can avoid having to run the snapshot agent again. When running sp_addsubscription, use "@sync_type ='none'". The snapshot agent is not required because you are telling replication that the data at the subscriber is the same as at the published.

If you only apply the changes to the master copy, you will need "@sync_type ='automatic'". In effect, you are telling replication that the table and its data on the subscriber DO NOT match the published. Hence, a new snapshot will need to be generated and delivered to the subscriber.

[DaveH]

If I try the first approach you mentioned and make the changes on the publisher and the subscriber, how do I make replication aware of the schema change. I thought replication stores a copy of all table schemas? Is this handled with sp_addsubscription or does the schema reside in the publisher.

Thanks, Dave

[Stephen]

When the snapshot agent runs, it generates the SQL required to create the table if it is required. This is the only time that a copy of the table definition is stored. This is stored in a file and is deleted by the replication cleanup job.

All that replication needs for its normal operation is the definition of the publication and each article which is stored in the publishing database in system tables.

When an article is created (using sp_addarticle), replication reads the relevant meta data for the table and stores enough meta data for its requirements.

The only catch will be if you told replication to use stored procedures to apply the inserts and updates. If you did, then you will need to update these as well - choose your favourite editor.

[DaveH]

Makes sense. I'll give it a try.

Thanks and have a great weekend, Dave