ALTER a replicated column in SQL Server 2008 R2. Merge Replication

Question

I want to alter columns in StudentPerformance table

Columns to be altered are -

StudentName varchar(50) to StudentName varchar(100)
Percentage decimal(18,0) to Percentage decimal(18,2)

It is configured for merge replication.

• Will it make my current snapshot obselete?
• Will I have to re-initialze all the subscriptions?
• Will it update all the records in the table and it will increase traffic in replication when I run the alter statement?

Answer 1

You will need to do this using ALTER TABLE ALTER COLUMN syntax at the Publisher. By default the schema changes will be propagated on the next synchronization, publication property @replicate_ddl must be set to true. No new snapshot or reinitialization required in most cases.

For example, I can change my SalesLT.Customer Title column from nvarchar(8) null to nvarchar(10) null by executing on the publication database:

ALTER TABLE SalesLT.Customer ALTER COLUMN Title NVARCHAR(10) NULL 

In most cases the schema change will replicate out on the next sync, there are some exceptions that are covered in Making Schema Changes on Publication Databases.