Udostępnij za pośrednictwem

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

  • Artykuł

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.

Note

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.

See Also

Other Resources

Making Schema Changes on Publication Databases

Help and Information

Getting SQL Server 2005 Assistance