Post reply

Software Upgrade...What to do??

  • May 29, 2007 at 11:26 am

    #93091

    My company is in the slot machine industry and when we perform an upgrade to our software that includes a modification to our database schema we take our old database called (for example) Bingo and rename it OldBingo05292007 to show that it was upgraded on 05/29/2007. These databases can be anywhere from 2GB to 10GB and even though the data is in OldBingo we have to repopulate the data back into the new Bingo DB for accounting purposes. Some of the changes are on tables that have millions of rows in them so we usually insert with either the default values or NULL values for the new columns. When we perform the upgrade it can take up to 12 hours just to get the old data from OldBingo into the new Bingo DB because of a table change or just because there is so much data to be moved. My question is what strategy or procedure do you think would be best to perform an upgrade as described above? If you need anymore specific info I can provide it as needed. Thanks in advance for your help.

  • May 29, 2007 at 3:08 pm

    #709489

    There are a couple of general tips I can share with you.

    • Before beginning the data transfer from the old database to the new database, drop all indexes in the new database and even the constraints if you can ensure integrity.
    • If you have a second physical disk subsystem, put the old database on the secondary disk subsystem and the new database on your primary disk subsystem.  When you do the data transfer this will lead to less contention for the bottleneck, the disk subsystem.
    • Set the recovery mode to simple for the new database and expand the new database to the largest size you expect it to be once the transfer is complete.  Having the size of the files grow as you insert data kills performance.  Also, make sure you expand the log file as well so that it doesn't have to be expanded by SQL Server.  Remember, you can always shrink the files later if you make them too big.

    Your most significant performance boost will come from dropping the indexes and constraints if you aren't already doing that.  Rebuilding the indexes once the data transfer is complete will take no time at all in comparison to what you save.

  • October 1, 2007 at 9:49 pm

    #738000

    I think Ed has some great advice and it's what I'd recommend.

Viewing 3 posts - 1 through 2 (of 2 total)

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