How to: Replicate Schema Changes (Replication Transact-SQL Programming)

  • Artículo

If you make the following schema changes to a published article, they are propagated, by default, to Microsoft SQL Server Subscribers:

  • ALTER TABLE
  • ALTER VIEW
  • ALTER PROCEDURE
  • ALTER FUNCTION
  • ALTER TRIGGER

You can use replication stored procedures to specify whether these schema changes are replicated. The stored procedure that you use depends on the type of publication.

[!NOTA] ALTER TABLE DROP COLUMN is always replicated to all subscriptions whose partition contains the columns being dropped, regardless of the value of the @replicate_ddl parameter.

To create a snapshot or transactional publication that does not replicate schema changes

  1. At the Publisher on the publication database, execute sp_addpublication (Transact-SQL), specifying a value of 0 for @replicate_ddl. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

To create a merge publication that does not replicate schema changes

  1. At the Publisher on the publication database, execute sp_addmergepublication (Transact-SQL), specifying a value of 0 for @replicate_ddl. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

To temporarily disable replicating schema changes for a snapshot or transactional publication

  1. For a publication with replication of schema changes, execute sp_changepublication (Transact-SQL), specifying a value of replicate_ddl for @property and a value of 0 for @value.

  2. Execute the DDL command on the published object.

  3. (Optional) Re-enable replicating schema changes by executing sp_changepublication (Transact-SQL), specifying a value of replicate_ddl for @property and a value of 1 for @value.

To temporarily disable replicating schema changes for a merge publication

  1. For a publication with replication of schema changes, execute sp_changemergepublication (Transact-SQL), specifying a value of replicate_ddl for @property and a value of 0 for @value.

  2. Execute the DDL command on the published object.

  3. (Optional) Re-enable replicating schema changes by executing sp_changemergepublication (Transact-SQL), specifying a value of replicate_ddl for @property and a value of 1 for @value.

Vea también

Otros recursos

Realizar cambios de esquema en las bases de datos de publicaciones

Ayuda e información

Obtener ayuda sobre SQL Server 2005