I've noticed in the forums there have been a number of people inquiring about making schema changes in a replication topology. I understand the concern and I'm here to assure you that yes, it is possible to do. If you think about it, DDL statements are used to create and modify the structure of database objects in a database. If SQL Server Replication wasn't able to handle something as basic as this, what good would it be?
Usually the requirement is to add a column to or drop a column from a published table. Making these types of schema changes in a replication topology is usually non-invasive and should not require a maintenance window. Replication supports the following schema changes on published objects per BOL:
- ALTER TABLE
- ALTER TABLE SET LOCK ESCALATION
- ALTER VIEW
- ALTER PROCEDURE
- ALTER FUNCTION
- ALTER TRIGGER
To add a new column to a table and include it in an existing publication, you'll need to use ALTER TABLE <Table> ADD <Column> syntax at the publisher. By default the schema change will be propagated to subscribers on the next synchronization, publication property @replicate_ddl must be set to true. It is important to realize that this must be done using T-SQL as SQL Server Management Studio attempts to drop and recreate the table when schema changes are made via the table designer GUI, which causes the schema change to fail.
USE MyPublicationDB GO ALTER TABLE SalesLT.Customer ADD TestColumn1 varchar(50) NULL
Similarly, to drop a column from a published table you'll need use ALTER TABLE <Table> DROP COLUMN <Column> syntax at the publisher and the schema change will be propagated to subscribers on the next synchronization.
USE MyPublicationDB GO ALTER TABLE SalesLT.Customer DROP COLUMN TestColumn1
In general, schema changes do not require subscriptions to be reinitialized but there are some exceptions to this rule. To be sure whether or not these exceptions apply to your scenario, please see Making Schema Changes on Publication Databases for more information.
