http://www.sqlservercentral.com/blogs/sqlrepl/2012/01/10/making-schema-changes-in-a-replication-topology/

Printed 2014/08/22 07:56PM

Making Schema Changes in a Replication Topology

2012/01/10

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:

Replicate Schema Changes True

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.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.