Core component of SQL Server for storing, processing, and securing data
You can run the ALTER without removing the table from replication, unless one of the two documented blockers below applies.
Step 1: Check whether the column is in the primary key
SELECT c.name AS pk_column
FROM sys.index_columns ic
JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
AND ic.object_id = OBJECT_ID('dbo.YourTable');
Step 2: Choose the path
A. The column is not in the PK, and you are not changing it to XML. Run the ALTER on the publisher while the table stays published. When replicate_ddl = 1 (the default), the change is sent to subscribers automatically.
-- Check the setting
SELECT name, replicate_ddl FROM dbo.syspublications;
-- Run the change on the publisher only
ALTER TABLE dbo.YourTable ALTER COLUMN YourColumn VARCHAR(200) NULL;
Make sure the Log Reader and Distribution agents are running. Never make schema changes directly on the subscriber.
B. The column is in the PK, or you are changing it to XML. SQL Server won’t allow this while the table is published. You have two options:
Keep the table published. Add a new column, copy the data into it, switch the application over to it, then drop the old column. Each of those steps is supported and replicates.
Re-publish only this table. Remove just this article, run the ALTER, add the article back, and generate a snapshot:
EXEC sp_dropsubscription @publication = N'YourPub', @article = N'YourTable', @subscriber = N'all';
EXEC sp_droparticle @publication = N'YourPub', @article = N'YourTable';
ALTER TABLE dbo.YourTable ALTER COLUMN YourColumn ...;
EXEC sp_addarticle @publication = N'YourPub', @article = N'YourTable', @source_object = N'YourTable';
EXEC sp_addsubscription @publication = N'YourPub', @article = N'YourTable',
@subscriber = N'SubServer', @destination_db = N'SubDB';
EXEC sp_startpublication_snapshot @publication = N'YourPub';
If the publication has immediate_sync = 0 and allow_anonymous = 0, the snapshot covers only this table, not all three.
If the column isn’t in the PK and you aren’t changing it to XML, but the ALTER still fails, send the exact error number and your ALTER statement.