Changing column constraint null/not null = rowguid replication error

Question

I have a database running under Sql server 2005 with merge replication. I want to change some of the FK columns to be 'not null' as they should always have a value. SQL server won't let me do that though, this is what it says:

• Unable to modify table. It is invalid to drop the default constraint on the rowguid column that is used by merge replication. The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message. The transaction ended in the trigger. The batch has been aborted.

I am not trying to change the constraints on the rowguid column at all, only on another column that is acting as a FK. Other columns I want to set to be not null because the record doesn't make any sense without that information (i.e. on a customer, the customer name).

Questions: Is there a way to update columns to be 'not null' without turning off replication then turning it back on again? Is this even the best way to do this - should I be using a constraint instead?

Answer 1

Apparently SSMS makes changes to tables by dropping them and recreating them. So just needed to make the changes using T-SQL statement.

ALTER TABLE dbo.MyTable ALTER COLUMN MyColumn nvarchar(50) NOT NULL

Answer 2

You need to script out your change in T-SQL statements as SQL Server Management Studio will look to drop and re-create the table, as opposed to simply adding the additional column.

You will also need to add the new column to your Publications.

Please note that changing a column in this manner can be detrimental to the performance of Replication. Dependent on the size of the table you are altering, can lead to a lot of data being replicated. Consider that although your table modification can be performed in a single statement, if 1 million rows are affected then 1 million updates will be generated at the Subscriber, NOT a single update statement as is commonly thought.

The hands on, improved performance approach.......

To perform this exercise you need to:

1. Backup your Replication environment by scripting out your entire configuration.
2. Remove the table from Replication at both Publishers/Subscribers
3. Add the column at each Publisher/Subscriber.
4. Apply the Update locally at each Publisher/Subscriber.
5. Add the table back into Replication.
6. Validate that transactions are being Replicated.