Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Minmize Down Time During Data Type Conversion Expand / Collapse
Author
Message
Posted Thursday, August 16, 2012 12:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 17, 2012 2:17 PM
Points: 3, Visits: 8
Hi Folks,

We need to update a data type in an existing database from Int-->BigInt. Overall there are about 10 billion records distributed over 90 tables that will need to be updated. Unfortunately we can't incur downtown of more than 5-10 hours. DBA's have tested, and maximum throughput they can get using standard BCP technque is around 5 million rows converted per minute, which blows right by the allowed downtown window.

Was wondering if anybody had ran up against similar type problems, and if there was a way to use a backup and replication or log shipping as a solution (ie, update all the records in an offline backup while production is still running, bring the backup up-to-date using replication or log shipping, and re-point production over to the backup), or any hardware solutions that might work.

Thanks in advance for any help that you can provide

Sam
Post #1346189
Posted Friday, August 17, 2012 5:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 2,873, Visits: 5,190
Have I lost something? Do you need to change datatype of the table column or you need to change the datatype and values as well?
If only column change, than the maximum available speed can be achieved by simple altering the column:

alter table [yourtable] alter column [yourcolumnname] bigint

If you have indexes/relationships on this column, they must be dropped and recreated in at additional cost.

If you are talking about value change as well, then I guess the any type of the bulk load is about the fastest options you have: BCP, BULK INSERT, SELECT INTO.




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1346482
Posted Friday, August 17, 2012 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 17, 2012 2:17 PM
Points: 3, Visits: 8
Thanks for the reply. It's strictly a data type change. No data will be changed. As I understand it, BCP was being used as an alternative to Alter Table. Basically they created the modified table and then BCP'ed into it, as it was faster than submitting the Alter Table command for large tables.

Post #1346704
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse