Make schema changes on publication databases

Applies to: SQL Server Azure SQL Managed Instance

Replication supports a wide range of schema changes to published objects. When you make any of the following schema changes on the appropriate published object at a Microsoft SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers:

• ALTER TABLE

• ALTER TABLE SET LOCK ESCALATION shouldn't be used if schema change replication is enabled and a topology includes SQL Server 2005 (9.x) or SQL Server Compact 3.5 Subscribers.

• ALTER VIEW

• ALTER PROCEDURE

• ALTER FUNCTION

• ALTER TRIGGER

  ALTER TRIGGER can be used only for data manipulation language (DML) triggers because data definition language (DDL) triggers can't be replicated.

Important

You must make schema changes to tables by using Transact-SQL or SQL Server Management Objects (SMO). When you make schema changes in SQL Server Management Studio, Management Studio attempts to drop and re-create the table. You can't drop published objects, so the schema change fails.

For transactional replication and merge replication, schema changes are propagated incrementally when the Distribution Agent or Merge Agent runs. For snapshot replication, schema changes are propagated when a new snapshot is applied at the Subscriber. In snapshot replication, a new copy of the schema is sent to the Subscriber each time synchronization occurs. Therefore, all schema changes (not just those listed earlier) to previously published objects are automatically propagated with each synchronization.

For information about adding and removing articles from publications, see Add Articles to and Drop Articles from Existing Publications.

To replicate schema changes

The schema changes listed earlier are replicated by default. For information about disabling the replication of schema changes, see Replicate Schema Changes.

Considerations for schema changes

Keep the following considerations in mind when replicating schema changes.

General considerations

• Schema changes are subject to any restrictions imposed by Transact-SQL. For example, ALTER TABLE doesn't allow you to alter primary key columns.

• Data type mapping happens only for the initial snapshot. Schema changes don't map to previous versions of data types. For example, if you use ALTER TABLE ADD datetime2 column in SQL Server 2012 (11.x), the data type doesn't translate to nvarchar for SQL Server 2005 (9.x) Subscribers. In some cases, schema changes are blocked on the Publisher.

• If you set a publication to allow the propagation of schema changes, it propagates schema changes regardless of how you set the related schema option for an article in the publication. For example, if you select not to replicate foreign key constraints for a table article, but then issue an ALTER TABLE command that adds a foreign key to the table at the Publisher, the foreign key is added to the table at the Subscriber. To prevent this behavior, disable the propagation of schema changes before issuing the ALTER TABLE command.

• Make schema changes only at the Publisher, not at Subscribers (including republishing Subscribers). Merge replication prevents schema changes at the Subscriber. Transactional replication doesn't prevent the changes, but the changes can cause replication to fail.

• Changes propagated to a republishing Subscriber are by default propagated to its Subscribers.

• If the schema change references objects or constraints existing on the Publisher but not on the Subscriber, the schema change succeeds on the Publisher but fails on the Subscriber.

• All objects on the Subscriber that you reference when adding a foreign key must have the same name and owner as the corresponding object on the Publisher.

• Explicit adding, dropping, or altering indexes isn't replicated. You need to run any change involving an explicit index on each replica set individually. Indexes created implicitly for constraints (such as a primary key constraint) are supported.

• Altering or dropping identity columns that replication manages isn't supported. For more information about automatic management of identity columns, see Replicate Identity Columns.

• Schema changes that include nondeterministic functions aren't supported because they can result in data at the Publisher and Subscriber being different (referred to as non-convergence). For example, if you issue the following command at the Publisher: ALTER TABLE SalesOrderDetail ADD OrderDate DATETIME DEFAULT GETDATE(), the values are different when the command is replicated to the Subscriber and executed. For more information about nondeterministic functions, see Deterministic and Nondeterministic Functions.

• Name constraints explicitly. If you don't explicitly name a constraint, SQL Server generates a name for the constraint, and these names are different on the Publisher and each Subscriber. This difference can cause issues during the replication of schema changes. For example, if you drop a column at the Publisher and a dependent constraint is dropped, replication attempts to drop the constraint at the Subscriber. The drop at the Subscriber fails because the name of the constraint is different. If synchronization fails because of a constraint naming issue, manually drop the constraint at the Subscriber and then rerun the Merge Agent.

• If you publish a table for replication, you can't alter a column in that table to a data type of XML if you already generated a publication snapshot. To alter the column, you must first remove replication.

• Read uncommitted isn't a supported isolation level when doing DDL on a published table.

• Don't use SET CONTEXT_INFO to modify the context of transactions where schema changes are performed against published objects.

Adding columns

• To add a new column to a table and include that column in an existing publication, execute ALTER TABLE <Table> ADD <Column>. By default, the column is then replicated to all Subscribers. The column must allow NULL values or include a default constraint. For more information about adding columns, see the "Merge Replication" section in this article.

• To add a new column to a table and not include that column in an existing publication, disable the replication of schema changes, and then execute ALTER TABLE <Table> ADD <Column>.

• To include an existing column in an existing publication, use sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or the Publication Properties - <Publication> dialog box.

  For more information, see Define and Modify a Column Filter. This action requires subscriptions to be reinitialized.

• Adding an identity column to a published table isn't supported, because it can result in non-convergence when the column is replicated to the Subscriber. The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows.

Dropping columns

• To drop a column from an existing publication and drop the column from the table at the Publisher, execute ALTER TABLE <Table> DROP <Column>. By default, the column is then dropped from the table at all Subscribers.

• To drop a column from an existing publication but retain the column in the table at the Publisher, use sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or the Publication Properties - <Publication> dialog box.

  For more information, see Define and Modify a Column Filter. This action requires generating a new snapshot.

• You can't use the column to drop in the filter clauses of any article of any publication in the database.

• When dropping a column from a published article, consider any constraints, indexes, or properties of the column that could affect the database. For example:

  • You can't drop columns used in a primary key from articles in transactional publications, because replication uses them.

  • You can't drop the rowguid column from articles in merge publications or the mstran_repl_version column from articles in transactional publications that support updating subscriptions, because replication uses them.

  • Index changes aren't propagated to Subscribers. If you drop a column at the Publisher and a dependent index is dropped, the index drop isn't replicated. You should drop the index at the Subscriber before dropping the column at the Publisher, so that the column drop succeeds when it is replicated from the Publisher to the Subscriber. If synchronization fails because of an index at the Subscriber, manually drop the index and then rerun the Merge Agent.

  • Name constraints explicitly so you can drop them. For more information, see the "General Considerations" section earlier in this article.

Transactional replication

• Schema changes are propagated to Subscribers running previous versions of SQL Server, but the DDL statement should only include syntax supported by the version at the Subscriber.

  If the Subscriber republishes data, the only supported schema changes are adding and dropping a column. Make these changes on the Publisher by using sp_repladdcolumn (Transact-SQL) and sp_repldropcolumn (Transact-SQL) rather than ALTER TABLE DDL syntax.

• Schema changes aren't replicated to non-SQL Server Subscribers.

• Schema changes aren't propagated from non-SQL Server Publishers.

• You can't alter indexed views that are replicated as tables. You can alter indexed views that are replicated as indexed views, but altering them causes them to become regular views rather than indexed views.

• If the publication supports immediate updating or queued updating subscriptions, quiesce the system before making schema changes: stop all activity on the published table at the Publisher and Subscribers, and propagate pending data changes to all nodes. After the schema changes propagate to all nodes, activity can resume on the published tables.

• If the publication is in a peer-to-peer topology, quiesce the system before making schema changes. For more information, see Quiesce a Replication Topology (Replication Transact-SQL Programming).

• Adding a timestamp column to a table and mapping the timestamp to binary(8) causes the article to be reinitialized for all active subscriptions.

Merge Replication

• How merge replication handles schema changes depends on the publication compatibility level, and whether the snapshot is set to native mode (default) or character mode:

  • To replicate schema changes, set the publication compatibility level to at least 90RTM. If Subscribers use previous versions of SQL Server or the compatibility level is less than 90RTM, use sp_repladdcolumn (Transact-SQL) and sp_repldropcolumn (Transact-SQL) to add and drop columns. However, these procedures are deprecated.

  • If you try to add to an existing article a column with a data type that was introduced in SQL Server 2008 (10.0.x), SQL Server has the following behavior:

    	100RTM, native snapshot	100RTM, character snapshot	All other compatibility levels
    hierarchyid	Allow change	Block change	Block change
    geography and geometry	Allow change	Allow change*	Block change
    filestream	Allow change	Block change	Block change
    date, time, datetime2, and datetimeoffset	Allow change	Allow change*	Block change

    *SQL Server Compact Subscribers convert these data types at the Subscriber.

• If an error occurs when applying a schema change (such as an error resulting from adding a foreign key that references a table not available at the Subscriber), synchronization fails and the subscription must be reinitialized.

• If a schema change is made on a column involved in a join filter or parameterized filter, you must reinitialize all subscriptions and regenerate the snapshot.

• Merge replication provides stored procedures to skip schema changes during troubleshooting. For more information, see sp_markpendingschemachange (Transact-SQL) and sp_enumeratependingschemachanges (Transact-SQL).

Related content

• ALTER TABLE (Transact-SQL)
• ALTER VIEW (Transact-SQL)
• ALTER PROCEDURE (Transact-SQL)
• ALTER FUNCTION (Transact-SQL)
• ALTER TRIGGER (Transact-SQL)
• Publish Data and Database Objects
• Regenerate Custom Transactional Procedures to Reflect Schema Changes