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

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.

ms147331.note(fr-fr,SQL.90).gifRemarque :
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**.

Voir aussi

Autres ressources

Modification du schéma dans les bases de données de publication

Aide et Informations

Assistance sur SQL Server 2005