MS SQL Server Replication - ADD column on Publisher, then DROP Default constraint on Subscriber

Author

ADD column on Publisher, then DROP Default constraint on Subscriber

Frank

2006-10-25, 6:03 am

Hi folks,

Setup: Transactional Replication on SQL2000 (SP4), PUSH Publication to
multiple Subscribers

Everything works fine in the current setup

I've learned that I can easily ADD a new column to a table on the
Publication with the sp_repladdcolumn, or via EM's Publication Props->Filter
Columns. Both methods appear to require that the column either allow NULLS,
or specify a DEFAULT value. Currently on my Subscribers, all columns have NO
NULLS, and NO DEFAULTS as a result of being initialized via snapshot
application. All columns on the Subscribers have DEFAULTS for all columns.

So when I ADD a column to a table on my Publication, I specify a DEFAULT and
it replicates on over to the Subscribers. So far so good

Now on the Subscribers, the newly added column has a DEFAULT value I
specified in sp_repladdcolumn

My question is: Is there any known issues with issuing the following (at
the Subscribers) to remove the DEFAULT from that column ?

ALTER TABLE <tablename>
DROP CONSTRAINT <constraint_name>

In my testing so far, it appears there is no ill effects, the
sp_repladdcolumn takes care of recreating the sp_MSdel_<tablename>,
sp_MSins_<tablename> and sp_MSupd_<tablename> stored procedures to add the
new @param for the column. Insert, Update and Deletes at the Publisher are
replicated to Subscribers as well.

I'm merely concerned that this sort of manual dropping of an constraint on
the Subscriber may effect some other aspect of replication that I may not be
aware of and I would hate to find out when I ADD the column(s) in my PROD
environment.

Thanks in advance for your feedback.

Frank

Paul Ibison

2006-10-25, 6:03 am

Frank,
this should be fine, obviously provided the column is populated prior to the
change. Any insert which doesn't include the new column will fail on the
publisher and so will not arrive at the subscriber where it would fail
there. The insert stored procedure will proceed as before.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Frank

2006-10-25, 6:03 am

Hi Paul,

Thanks for the quick reply. I think I understand what you're saying.

No applications will use the newly added column until it's been added and
replicated to the subscribers. In other words, I need to add the column in
advance of any application code changes that reference it.

The table I'm going to be adding columns to is lightly used, and very small
(2000 rows) but would you recommend a different approach if it were a table
with 5 million rows on the publisher and each subscriber ?

I'll test it in the a dev environment of course, but was curious if you have
any additional advice.

Again, thanks for the info !

Regards,

Frank

"Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message
news:ejs%23DVQ8GHA.4304@TK2MSFTNGP03.phx.gbl...
> Frank,
> this should be fine, obviously provided the column is populated prior to
> the change. Any insert which doesn't include the new column will fail on
> the publisher and so will not arrive at the subscriber where it would fail
> there. The insert stored procedure will proceed as before.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>

Paul Ibison

2006-10-25, 6:03 am

No extra advice really. I'd make sure it is done first on the publisher to
prevent any potential issues, as that is really the only bad scenario - an
insert takes place which relies on the default and passes the publisher and
meanwhile the subscriber has the default removed. If you're dropping the
default I'm assuming the column is not nullable and is therefore already
populated when the default was added.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Frank

2006-10-25, 6:03 am

> If you're dropping the default I'm assuming the column is not nullable and
> is therefore already populated when the default was added.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Correct-o-mundo !

Many thanks,

Frank

Sponsored Links