Transaction Replication – Altering a Published Table Using SQL 7

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.

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.