Alter huge tables in transactional replicaiton

Question

Alter huge tables in transactional replicaiton

Rajesh kasturi

SSCarpal Tunnel

Points: 4007
More actions
September 17, 2009 at 9:43 am

#138206

We have Implementation on production box
We have to alter the below two tables, The above two tables are replicated using Transactional Replication
TblProduct --> Increase column length from nvarchar(10) to nvarchar(11)
TblSales --> int to bigint
TblProduct size is 8 GB
TblSales size is 5 GB.
Since the tables are used for replication I can't alter straight
I have two Options
Option1:
exec sp_dropsubscription
exec sp_droparticle
alter table
exec sp_addarticle
exec sp_addsubscription
After adding the subscription I believe we need to run the snapshot agent and distribution and so..
Option2:
Add temp column using sp_repladdcolumn
update tblProduct set tempcol = originalcolumn
Drop the original column sp_repldropcolumn
Add the new column newcol with desired size sp_repladdcolumn
update tblproduct set newcol = tempcol
drop the tempcol sp_repldropcolumn
Which will you prefer for huge tables.
Which is the best option
Rajesh Kasturi

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

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

I would choose Option 1 ... or something like it. I would arrange a time when the table is not being updated, drop the article/subscription, do the ALTER TABLE to publisher and subscriber, create the article and then create the subscription specifying that the subscriber already has the schema and data (which appropriate backups etc, of course)

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 2

thanks for the advice.

Your advice looks good

can you tell me what is the dis advantage of option2

Rajesh Kasturi

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

The only certain way to find out which is best, is to test both ways.

In his book on SQL Server 200 Replication, Hilary Cotter (SQL Server MVP) actually suggests method 2.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

When you are using Option 2, How many times are you forced to do a bulk update? 2 times? Replication is very chatty. So you are going to generate quite a bit of noise for the columns to be updated. Your distributor is going to be very very busy.

In option 1, you dont even have to renapshot the tables. You are specifying that the table exists with the Data.

-Roy

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 5

All,

Thanks for your advice...

I did the alter with Option 1, in my case sync up did not take much time because both servers are in same data center.

I need to test with option 2 also

Rajesh Kasturi