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.

Hinweis:
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.

Siehe auch

Andere Ressourcen

Vornehmen von Schemaänderungen in Publikationsdatenbanken

Hilfe und Informationen

Informationsquellen für SQL Server 2005