SQL data migration

[SQLScholar]

Hey all,

Due to a system upgrade that will take place soon on a system at my work, we are doing a heavy data migration....

Not many rows (20000 over 2 dbs, and mulitple tables) but 15000 ish of the rows, need to be changed into a different format (moved to different table etc....)

Now to minimise downtime, and time outside work - i was wondering...

Is there any feature in SQL server that means we can log all changes to the database. This means that we could take a copy the day before the change, switch this feature on. Then come 5.30 we can have all the data migrated, and will just have to update with the days information.

Any help/links/inforamtion/tips/storys (all to do with topic) greatly received.

TIA

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Kalin]

Hi Plank,

isn't DTS an option for you.
In it you can transform your data while the db remains online...

Grtz,

Kalin

 

[Kalin]

Hi,

isn't DTS an option for you....
In it you can create an copy-action while the db is online....

Kalin

Grtz,

Kalin

 

[SQLSister]

My personal opinion is that for that number of rows, you are best off doing the whole job by locking out users for the period of time you are migrating the data. This is especially true since you will be transforing information.

TO minimize problems, I would do a test migration first. Note each and every step you take and every problem you run into in transaforming the data. If possible make everything you do into a script. Since this is just a test, it doesn't matter that the data may change. Then thoroughly check out the user interface with the new database. Run every feature and makes sure that the change of structure didn't break anything.

Once I knew I had a good solid tested set of steps to transfer the data, I would truncate the tables in the new database and then close down the existing one for as long as it takes to perform the steps again. This second step I would do after hours or in a low usage period. I would let the users know that this was happening then and make sure that they were aware they couldn't access the system during the move. Make sure you don't schedule the move for a time period when the users have a critical need to be on the system. Especially if it is something like year end closeouts where people might be working overtime to get a task done. Users with a tight deadline are extremely cranky when you close their access to the database unexpectedly.

Generally I would do this sort of thing after work or if it will be a complex move, on a weekend. If you run 24/7 then you wll just have to let people know that you are doing database maintenance and the database will be unavailable for a period of time.

Questions about posting. See faq183-874