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

Altering Replicated Tables (SQL 2000)

By Andy Warren,

A few weeks ago I published an article about modifying replicated tables with SQL 7. If you haven't read that article, I encourage you to do so before continuing.

With SQL 2000 you can now add a column to a table (and a publication) with very little effort. The only thing to remember is that if you want the new column to be added to the subscribers, you MUST make the change via the 'Filter Columns' tab of the publication properties. SQL still provides no help if you want to modify an existing column. You can drop a column as long as it is not part of the primarykey or part of a filter (thanks to Jeff Cook for pointing this out to me).  If you don't want the new column to be part of any existing publication you can add the column via Enterprise Manager or Query Analyzer.

For the following demo, I created two databases, ReplSource and ReplDestination, both on the same machine running an instance of SQL2K Developer Edition. I then imported the Authors table from Pubs into ReplSource and created a standard transactional publication, using the default options. Here is the original schema:

To use the Filter Columns tab you can either use 'Create & Manage Publications' found on the Tools|Replication menu, or you can right click the publication itself either under Databases or under Replication Monitor.

Click on Filter Columns. You'll see the Add Column to Table button. Clicking that brings up the following dialog. My one complaint here is that instead of the nice editing tools you normally get when making changes through Enterprise Manager, you have to type everything in. If you're not sure of the syntax, make a quick copy of the table schema and use Enterprise Manager to make the change, then script the changes out so you can copy the DDL for the column you're adding. If you make a mistake here, you'll have to apply the same process you would with SQL 7! 

In this example I'm adding a column called country.

Once you add a column, it's automatically selected as part of the article. When you close the publication properties the change will be sent to each subscriber the next time the log reader & distribution agent run.

That's all there is to it. A big step up from SQL 7 and if you use do these changes often, probably worth the upgrade right there! You've probably noticed that there is also a 'Drop Selected Column' button. Let's look at what happens when you click it:

That's right, even though you're working on a publication, if you use this button it will actually drop the column from both the publisher and all the subscribers. Useful, but use with care!

Another thing you can do from Filter Columns is to remove a column from the article. You just can't do this easily in SQL 7, but with SQL 2000 you just clear the checkbox - well, almost. It does most of the work for you, but unfortunately requires you to force a snapshot to occur. Until the snapshot is done, no transactions will be distributed to subscribers of that publication.

That's all there is to it. SQL 2000 greatly reduces the time needed to perform one of the more common tasks of adding a column to a published article. Maybe in a future release we'll see enhancements that will support modifying existing columns without having to do a snapshot.

Total article views: 14189 | Views in the last 30 days: 17
 
Related Articles
FORUM

Change in Publication articles in Relication

Change in Publication articles in Relication

FORUM

Adding new article to existing publication

Adding new article to existing publication

FORUM

Dropping Article

Dropping article from existing publication

FORUM

Using Union All In Article Publication Filter

Hello my colleges, I would like to know if anyone has ever required to use a Union join within a pu...

BLOG

Replication Gotcha - Including An Article In Multiple Publications

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

Tags
replication    
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