Schema Changes on Publication Databases
Microsoft® SQL Server™ 2000 supports common schema changes to an existing publication database. You can add columns to, and drop columns from, a published table without dropping and recreating the publications and subscriptions referencing that table.
Schema changes can be replicated during snapshot replication, transactional replication, and merge replication. Column additions and deletions are implemented at the table level and propagated to all Subscribers that receive data from that table. For snapshot replication, the schema change is propagated when a new snapshot is reapplied at the Subscriber. For transactional replication and merge replication, the schema change is propagated incrementally when the Distribution Agent or Merge Agent runs.
Important Schema changes to a published table must be made only through the replication publication properties dialog box in SQL Server Enterprise Manager or through replication stored procedures. Do not make schema changes to published tables using the SQL ALTER TABLE statements in a tool such as SQL Query Analyzer or by using SQL Server Enterprise Manager visual database tools. Changes made to the schema of a published table using these tools will not be propagated to Subscribers.
It is recommended that you back up the publication database after making schema changes or using sp_mergecleanupmetadata. This will ensure that you can recover the publication database in its correct state if there is a failure of the Publisher.
Adding Columns
You can add a column:
- To an article in one or more publications.
Here, you add a column and apply the schema change immediately to one or more existing publications; the change is propagated to the Subscribers of those publications.
- To the underlying table, without including it in the published article.
You may want to make a schema change to the underlying table but not to the published article. For example, if you want to add a column that includes sensitive or proprietary data, this choice allows you to make a schema change without propagating the information to Subscribers. This option also lets you defer inclusion of a new column in a published article until a later date.
- To a published article, using a column that exists in an underlying table.
Whenever you add a column to a transactional publication, the appropriate ALTER TABLE statement (or sp_repladdcolumn or sp_repldropcolumn if the table is republished at the Subscriber) will be propagated and run at the Subscribers to complete the schema changes at the subscription databases.
Reinitialization of the subscription is necessary only when you add an existing column to a published article. When creating a new column and immediately adding it to a published article, a reinitialization is not required. This is because the Merge Agent re-executes the sp_repladdcolumn stored procedure (or sp_repldropcolumn for the dropping of a column), including all of its original syntax, at each affected Subscriber at the time of the next synchronization. The Distribution Agent re-executes the ALTER TABLE statement if the destination table is not republished at the Subscriber, otherwise, it re-executes the sp_repladdcolumn or sp_repldropcolumn, including all the original syntax, at each affected Subscriber at the time of the next synchronization.
When you add a column to the publishing table, but do not include the column in a publication, no further action is required. However, if you add the column to a publication later, subscriptions to the publication will need to be reinitialized for all types of publications. To avoid reinitializing subscriptions, add the column to the published article immediately, instead of waiting to add it to an existing article.
Additional Considerations
When defining the new column through the replication user interface or through replication stored procedures, you must do one of the following:
- Allow NULL values for the new column.
- Specify a default value for the column.
Adding Articles to a Merge Publication
When you add articles to a merge publication, a reinitialization of existing subscriptions is not required for the new article schema and data to be propagated to Subscribers. When adding an article to a merge publication for which there are active subscriptions, you must run the Snapshot Agent after adding the article before any Subscribers can synchronize. If the publication already has subscriptions, Subscribers will receive the schema and data for the new article based on this snapshot the next time they synchronize. The Merge Agent will then synchronize any data changes for the subscription.
When adding an article to a publication that has active subscriptions, you can filter the article using a subset filter clause without requiring that subscriptions be reinitialized. However, you cannot add any join filter clauses to a publication that has active subscriptions without also reinitializing all subscriptions to the publication.
When adding the article using Publication Properties in SQL Server Enterprise Manager, you will receive a message indicating that subscriptions will be prevented from synchronizing until a new snapshot has been generated for the publication. When you apply the changes, you will be advised to run the Snapshot Agent immediately.
If you are using stored procedures to add articles, you must authorize the addition of the article to a publication by setting @force_invalidate_snapshot=1 in sp_addmergearticle. You should then run the Snapshot Agent for the publication immediately.
Whether you use Publication Properties in SQL Server Enterprise Manager or stored procedures, you can defer running the Snapshot Agent, but you must run it before any existing subscriptions to the changed publication can synchronize and receive the new schema and data.
Dropping Columns
When dropping a column from a published article, take into consideration any constraints or properties of the column that could affect the database.
- You cannot drop columns with primary key or unique constraints, and you cannot drop UNIQUEIDENTIFIER (or ROWGUIDCOL) columns, which are used by the replication agents.
- The column to be dropped cannot be used in the filter clauses of any article of any publication in the database.
- Other types of constraints, such as foreign key and check constraints, will not prevent you from dropping a column. However, for most constraints, you are prompted with a warning message identifying the constraints on a column and requesting validation before you can drop the column. After you confirm the action, SQL Server 2000 drops all constraints on the column, and then drops the column.
Note Replication does not warn you of every possible dependency related to a column that is being dropped. If a column you are considering dropping is referenced by a constraint on another column, SQL Server 2000 does not inform you of the dependency and you are allowed to drop the column. Therefore, you should have a thorough understanding of the underlying database schema and use caution before dropping a published column.
How Schema Changes are Applied
After adding or dropping a column on the publishing table in merge replication, the schema change will be propagated to Subscribers the next time the subscription is synchronized. In transactional replication, the schema change will be propagated to Subscribers the next time the Log Reader Agent and the Distribution Agent run. When adding a new article or reinitializing an existing article to a transactional publication using concurrent snapshot processing, when the Snapshot Agent starts, the Distribution Agent stops to wait for the synchronization process including the time it takes for the Snapshot Agent and Log Reader Agent to run. When the synchronization is complete, the Distribution Agent will resume.
By default, in transactional replication, the custom stored procedures will be re-created at the Subscriber automatically. The current snapshot with old schema information is invalidated by default for all types of replication.
If you do not want the custom stored procedures to be re-created at the Subscriber after a schema change to a transactional publication, you should specify that when creating the publication.
Note When columns are added to or dropped from a publication that allows transformations on published data, the DTS packages will need to be regenerated.
To disable automatic creation of custom stored procedures during initial synchronization (transactional replication):
- In the Create Publication Wizard, on the Specify Articles page, select the articles you want to publish, and for a specific table article, click the properties (...) button associated with that table article.
- On the Commands tab, clear the Create the stored procedures during initial synchronization of subscriptions check box.
To change default properties for forcing reinitialization and invalidation of the current snapshot (transactional replication):
- Execute sp_repladdcolumn or sp_repldropcolumn with a value of 1 for the @force_reinit_subscription parameter. When set equal to 1, schema changes commands will not be propagated to Subscribers. All subscriptions affected by the schema change will be reinitialized except for nosync subscriptions, for which no action is taken.
- Execute sp_repladdcolumn or sp_repldropcolumn with a value of 0 for the @force_invalidate_snapshot parameter. When set equal to 0, current snapshot with previous schema information is still available in case it is needed. This parameter affects only publications created with the immediate_sync option.
Applying Schema Changes to Specific Publications
- Usually, schema changes flow to all Subscribers and republishers when included in an article. You can optionally select the publications on which to add a column, and the schema change will be propagated only to Subscribers of those publications.
- When dropping a column, all publications and Subscribers are affected; you cannot selectively implement the change on a specific publication.
To apply schema changes on publication databases