Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Altering Replicated Tables

By Andy Warren,

Replication is a pretty nice feature, right up until you want to make a few schema changes. SQL 7 will not let you alter the schema of any table that is part of an article in a publication. SQL 2000 offers some new features in this regard that I'll cover in a future article.

If the database is small or bandwidth to the subscriber allows, the quick and dirty way is to just drop the publication, make your changes, then rebuild your publication. If you decide to use this method take advantage of Enterprise Manager to script out your publication before dropping it.

SQL does offer a way to make your changes without quite as much work. Remember - you can't modify the table if it's published, but that doesn't mean you have to drop your entire publication! Here are the steps you need to take:

  • Stop any users from connecting. I usually put the database in DBO only mode. I normally do these procedures after hours when usage is lightest AND after doing a backup.

  • Make sure all pending replication commands are distributed. If you're running continuous replication, it should only take a few seconds for them to post to your subscriber. If there are undistributed commands remaining when you add or remove a column from the table, replication will fail when you restart it.

  • Stop replication for the database. The easiest way is to go to Replication Monitor in Enterprise Manager, locate your publication, then right click the Log Reader Agent and click Stop Agent. Then click the Distribution Agent and click Stop Synchronizing.

  • You can't modify or remove an article if there are subscribers to the publication, so you want to drop all subscriptions. Use sp_dropsubscription.

  • You can't modify the table if it's published as an article, so you need to drop the article from the publication using sp_droparticle.

  • Make your schema changes on the publisher. Keep in mind the warning in item #2 above. If you're adding or dropping a column that will be part of the article you add back to your publication, you will also need to make those same changes to ALL subscribers. You will also need to update the stored procedures on the subscribers that apply the insert/update/delete commands from the publisher.

  • Create a new article. Remember to include any vertical or horizontal filters that you were using before. Use sp_addarticle.

  • Add your subscribers back to the publication using sp_addsubscription.

  • Test! Insert a row into each table you modified, then delete that same row. Then right click your Log Reader and Distribution Agents and start both. If the two commands replicate successfully, you're in business. If not, you're not out of sync with your subscribers, so you can go back and troubleshoot.

  • Take the database out of dbo only mode.

Here is the basic script to alter a column. This assumes only one subscriber.

sp_dropsubscription 'DBNAME','TABLENAME','SUBSCRIBERNAME'

go

sp_droparticle 'DBNAME', 'TABLENAME'

go

alter table TABLENAME alter column street varchar (50)

go

sp_addarticle @publication = N'DBNAME', @article = N'TABLENAME', @source_owner = N'dbo', @source_object = N'TABLENAME', @destination_table = N'TABLENAME', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000073, @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_TABLENAME', @del_cmd = N'CALL sp_MSdel_TABLENAME', @upd_cmd = N'MCALL sp_MSupd_TABLENAME', @filter = null, @sync_object = null

go

sp_addsubscription 'DBNAME', 'TABLENAME', 'SUBSCRIBERNAME', 'DBNAME','automatic','active'

go

Getting it right the first time can be tricky. I highly recommend that set up a test database and mirror the publication you want to change, then work through the process and build your scripts until they work. If you're adding or dropping a column, the test setup pays even bigger dividends. A tip I learned from my friend Sean Burke is to make the change on the publisher, then execute a new snapshot which recreates the correct stored procedures on the subscriber. Script those out as create, change to alter (to keep the permissions intact) and add to your final script.

Once you decide to apply your changes to your production database, keep in mind that you have two fall back plans if things go south. One is to restore from backup and try again. The other is to just execute a new snapshot and resync all of the subscribers.

Total article views: 7212 | Views in the last 30 days: 6
 
Related Articles
BLOG

Replication Gotcha - Including An Article In Multiple Publications

When administering replication topologies it's common to group articles into publications based on r...

FORUM

Change in Publication articles in Relication

Change in Publication articles in Relication

FORUM

Replication Publication issue

Replication Publication issue

FORUM

Who changed the replication subscriber ??

Is there any log information on who changed (and when) the subscriber in a replication?

ARTICLE

Stairway to SQL Server Replication: Level 4 - Transactional Replication – The Subscriber

The Subscriber is the server where all the changes that are published by replication get delivered t...

Tags
replication    
sql server 6.5    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones