Replication Schema Changes

Changing the schema in a SQL Server 2005 replicated database is relatively simple, although the Microsoft documentation doesn't make it clear.

My test Merge replica included these two databases:
ReplMain
ReplSecondary

Each has a table named tblTest with a field "TestDesc varchar(50) null" in it. I need to increase the size of the TestDesc field to 100 characters.

DO NOT USE SQL Server Management Studio (SSMS) to alter the table. The GUI uses a script that includes a DROP TABLE, which is not allowed if the table is included as an article in the Subscription. Use these DDL commands instead:

USE ReplMain
ALTER TABLE tblTest ALTER COLUMN TestDesc VARCHAR(100) NULL

Using SSMS go to the Subscription Options of the Publication Properties and set "Replicate Schema Changes" to TRUE.

Right-click the SSMS Replication tab and launch the Replication Monitor.

Expand the database server and right-click the replication Publisher. Select "Generate Snapshot" to create a new snapshot of your master database which now includes the altered column.

Return to SSMS, to the SQL Server Agent tab, under the Jobs tab, and right-click "Job Activity Monitor" and choose "View Job Activity"

Locate the job with the Category named "REPL_Merge" right-click on that row and choose "Start Job." (After this you may need to click on the [Refresh] link to see the results of the job.)

When the merge is finished, you should have your new structure in all of the Subscriber databases. In my ReplSecondary database, the TestDesc column now appears as varchar(100).