Schema Changes in Replication

In the past, one of the hassles with replication was trying to change the schema of one of the articles. If you had to change the schema, the publication would have to be dropped and the subscribers would have to resubscribe after the publication was rebuilt.

Now, in SQL Server 2000, some schema changes can be replicated to each of the subscribers through the use of system stored procedures.

Note 

The only issue that is not addressed by the stored procedures is when you use DTS packages to transform the replicated data. If you've attached a DTS package to a publication, you will have to adjust it for the new schema.

If you try to change the schema of a replicated table, using the code shown here, you will receive an error:

ALTER TABLE dbo.Customers ADD       WebSite char(10) NULL

Here is the error that is returned:

Server: Msg 4931, Level 16, State 1, Line 1 Cannot add columns to table 'Customers' because it is being published for merge replication.

The changes can only be made using the two system stored procedures on the publisher. If you try to change it elsewhere, you will receive the following error:

Server: Msg 21260, Level 16, State 1, Procedure sp_repladdcolumn,  Line 133 Schema replication failed because database 'Northwind2' on server 'XANADU\UTOPIA' is not the original Publisher of table 'customers'.

The two stored procedures on the publisher are described in the following sections.

sp_repladdcolumn

The sp_repladdcolumn stored procedure allows you to add a new column to a table, and have the new column replicated to each of your subscribers. To run the stored procedure, use the following syntax:

sp_repladdcolumn [ @source_object = ] '<source object>'     , [ @column = ] '<column>' ]     [ , [ @typetext = ] <'typetext'> ]     [ , [ @publication_to_add = ] <'publication to add'> ]     [ , [ @schema_change_script = ] <'schema change script'> ]     [ , [ @force_invalidate_snapshot = ] <force invalidate snapshot> ]     [ , [ @force_reinit_subscription = ] <force reinit subscription> ]

The parameters serve the following functions:

  • @source_object Name of the table to add the column to.

  • @column Name of the column to add to the @source_object.

  • @typetext Data type definition as well as any extra descriptions, including the NULL clause.

  • @publication_to_add Name of the affected publication. By default, this parameter is set to all, which will send the change to each of your publications.

  • @schema_change_script Path and name of the SQL Script to execute. This is NULL by default.

  • @force_invalidate_snapshot Can be set to 1 or 0. If set to 1, the snapshot schema is invalidated and a new snapshot is generated. Otherwise, the snapshot schema will be generated at the next scheduled interval or manually.

  • @force_reinit_subscription If set to 1, the subscriptions are forced to be reinitialized. If set to the value of 0 (default), the subscriptions will not reinitialize.

For example, if you want to add a column called WebSite to the Customers table we replicated earlier, you can use the following syntax:

sp_repladdcolumn @source_object = 'customers',              @column = 'WebSite',              @typetext = 'varchar(40) NULL',              @force_reinit_subscription = 1

This returns the following warning if any of your subscribers are not SQL Server 2000 servers:

Warning: only Subscribers running SQL Server 2000 can synchronize with publication 'NorthwindMerge' because schema replication is performed.

The next time your subscribers are synchronized, they will receive the modified schema.

sp_repldropcolumn

The sp_repldropcolumn stored procedure operates the same way as the sp_repladdcolumn stored procedure. The only difference in the parameters is that sp_repldropcolumn does not have the @typetext parameter. To drop the column we created earlier, you can use the following syntax:

sp_repldropcolumn @source_object = 'customers',              @column = 'WebSite',              @force_reinit_subscription = 1

Some columns can't be dropped. For example, you can't drop the columns that meet the following conditions:

  • Columns that are a part of the publication's filter

  • The table's primary key or unique constraint

  • Columns that are uniqueidentifier columns or ROWGUID columns




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net