Seamless Replication with column drop and re-add

Question

Seamless Replication with column drop and re-add

joshua bauman

Newbie

Points: 3
More actions
October 2, 2006 at 6:39 pm

#170530

I need to drop a column from the replication articles and change the datatype from bit to int. Then I need to add the column back to the replication process. Can this be done without reintializing the snapshot, or creating a whole new snapshot? I have a very small maintenance window, and the snapshot for this DB normally takes about 4 hours. Also if I can do it this way, is there anything that needs to be done to the target server during the process?

Thanks,

Joshua

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

I am assuming that you are speaking about Transactional Replication, and that you wish to alter the datatype for a column in a replicated table and that the column width after the change (including existing column) is still within 8kB.

The process will be as follows:

1. Add a 'dummy' column with the required data type of the new column:

exec

sp_repladdcolumn @source_object = 'TableA', @column = 'DummyIntColumn' , @typetext = 'INT NULL' , @publication_to_add = 'ThisPublication'

2. Transfer existing data to new column

update TableA set DummyIntColumn = OldBitColumn

3. Drop the column to be altered

exec sp_repldropcolumn @source_object = 'TableA' , @column = 'OldBitColumn'

4. Add the 'new' column with the required data type and constraints

exec sp_repladdcolumn @source_object = 'TableA', @column = 'IntColumn' , @typetext = 'INT NOT NULL CONSTRAINT [DF__TableA__IntColumn] DEFAULT (0)' , @publication_to_add = 'ThisPublication'

5. Transfer the data to this newly created column

update TableA set IntColumn = DummyIntColumn

6. Clean up

exec

sp_repldropcolumn @source_object = 'TableA' , @column = 'DummyIntColumn'

That's it. Be sure to test this out first to gain some confidence. Nothing needs to be done to the subscribing databases. For more info, follow this link:

http://www.replicationanswers.com/AddColumn.asp

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

Your approach should be based on how much data is ion the table:

For a smallish table just take the table out of replication, make the changes, add it back and run the snapshot, it will take only the changed table schema and data. might be easier than the column changes.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Paul Mu SSCertifiable Points: 5547 More actions · Answer 3

You are quite right Colin, and the referenced article does describe the alternative that you've mentioned.

The approach that has worked for me is to use the approach I've outlined above for tables with up to a couple million rows (smallish tables), depending on your current system and number of subscribers.

For larger tables, I tended to use the alternate approach and use 'no-sync' subscriptions. This will require a down-time (normally 5-10 mins) to make sure that all transactions have been replicated to the subscribers and that no access to the affected publication databases is enforced during this time.

homebrew01 SSC Guru Points: 55553 More actions · Answer 4

I did this with good results when I needed to change the length of a field in a replicated table. -- Specifically: Drop a table from a publication so it can be modified, then add the table back in. I've done this with snapshot & trans replication, but never tried it with merge. Perhaps you can test it and see if it (or a variation) will work for you:

1) I just scripted out the CREATE Publication and the DROP publication commands to text files. This creates the sp_droparticle & sp_addarticle commands for you (& other commands if applicable).

2) Ran just the portion of the DROP publication that had to do with TableA (sp_droparticle)

3) Modified FieldA from 6 char to 8 char on publication & subscription databases.

4) Ran just the portion of the CREATE Publication dealing with TableA (sp_addarticle, sp_articlefilter etc..)

5) Ran the snapshot job

6) Ran the pull subscription job

7) Checked the table in the subscription database & it has the same data as the source table.

I usually set up a test replication and try out these things ahead of time.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 5

Paul - agreed , the terms large and small are so relative to yourself and on their own are pretty meaningless!! also depends upon your pipe for the replication path of course.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/