SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Minmize Down Time During Data Type Conversion


Minmize Down Time During Data Type Conversion

Author
Message
Sam73
Sam73
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
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
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13070 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Sam73
Sam73
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search