August 16, 2012 at 12:48 pm
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
August 17, 2012 at 5:29 am
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.
August 17, 2012 at 12:25 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply