January 15, 2003 at 6:57 am
I'm new to replication. Until I get through the 200+ pages of BOL can someone tell me the steps to take to add a column to a replicated table. The column is defined as NOT NULL and cannot have a default value. The only way I have been able to successfully alter the table is as follows:
Delete Subscription
Delete Publication
Add the new column
Create Publication
Create Push Subscription
Run SnapShot
I know I am missing something. I can't believe I need to run SnapShot and resend the entire database for one column change. If we were a 24x7 shop replicating a 100+ GB database, the downtime during the snapshot process would be unacceptable.
Thanks, Dave
January 15, 2003 at 9:41 am
BOL states that the column must either allow NULLS or have a default value. It is just enforcing the implicit contraint for existing rows (either has to have a value or allow nulls). I know you can do it in EM on non-replicated tables, but I don't know how or why it does.
One workaround would be to allow nulls, then add a check contraint that disallowed nulls.
Jay
Jay Madren
January 15, 2003 at 12:55 pm
You can do it, just takes work. I've got an article up that goes into more detail, not 100% match but close. Basically you need to get the logreader to post all open transactions (for repl) and stop any changes from being made. Then you can remove the subscription, alter the pub to include the pub, alter the table on the subscriber (and optionally go ahead and populate), add the subscriber back with no snapshot, reallow db access.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply