Post reply

SQL 2000 upgrade to SQL 2005

  • August 26, 2009 at 9:34 am

    #209033

    Hi all,

    I am in the middle of planning and testing my first migration from SQL 2000 to SQL 2005 x64.

    I have ran the upgrade advisor and made the changes to unsupported syntax which is in the process of being tested.

    All other issues pertaining to the advisor were recommendations (stats, autogrow, DTS)

    I have the majority of the upgrade scripted out and planned.

    I am curious if someone can please recommend what they have done in the past to ENSURE things restored correctly and there are no issues with the databases once they are upgraded and to the highest compatibility level

    The upgrade will not be an in-place upgrade it is a backup and restore plan.

    I have been reading up on the DBCC CHECKDB with DATA_PURITY, ALL_ERROR_MESSAGES

    I also read about updating stats once the db's are on 2005.

    Would it be all the same if I created a maintenance plan for a re-index all the databases (something I planned already). Rebuilding the indexes should automatically update the stats correct?

    Any other gotchas? Just trying to cover all my bases!

    Thanks in advance,

    Lee

  • August 26, 2009 at 10:38 am

    #1045004

    1) Rebuild Index

    2) Update Statistics with Full Scan

    3) DBCC UPDATEUSAGE

    4) Fix Orphaned Users

  • August 26, 2009 at 10:40 am

    #1045005

    DBCC FREEPROCCACHE...just to flush out old query plans

  • August 26, 2009 at 11:01 am

    #1045019

    ravikanth (8/26/2009)

    1) Rebuild Index

    2) Update Statistics with Full Scan

    3) DBCC UPDATEUSAGE

    4) Fix Orphaned Users

    Thanks for the info...just one quick comment

    wouldn't 1 & 2 be taken care of if I ran a maintenance plan for all DB's that rebuilt the dB's? unless I am confused I thought the statistics were automatically updated with a full sample when a re-index occurs...if that isn't the case please correct me.

    Also thanks for mentioning DBCC UPDATEUSAGE, I will add that to my script for each db.

    As for #4

    I previously found a method for scripting out all logins to keep their same SID's

    http://support.microsoft.com/kb/246133

    Thanks for the info!

  • August 26, 2009 at 11:51 am

    #1045049

    you should do both rebuild index and update stats.

    also follow the same order.

  • August 26, 2009 at 3:13 pm

    #1045192

    ravikanth (8/26/2009)

    you should do both rebuild index and update stats.

    also follow the same order.

    I had a nice long reply going and apparently there is a session time out enabled which not only errors out but doesn't save what you had typed (GOD I hate that)

    Anyways I did some extra reading after your post decided I could probably create a script that will generate ALTER INDEX statements

    something similar to this

    ALTER INDEX ALL ON DB.schema.table REBUILD WITH (FILLFACTOR = xxx, STATISTICS_NORECOMPUTE = OFF)

    that should rebuild all indexes for the table...

    question thought: Do you need to add STATISTICS_NORECOMPUTE = OFF? it seems to me that it is OFF by default so if you don't add that syntax then it should recompute the statistics on the columns in the rebuilt indexes...

    Once the index rebuild is finished I could run something like sp_updatestatistics to update the remaining statistics in the database...

    Let me know if that sounds like I am on the right track...

    Thanks again,

    Lee

  • August 28, 2009 at 5:50 pm

    #1046437

    STATISTICS_NORECOMPUTE = { ON | OFF }

    Specifies whether distribution statistics are recomputed. The default is OFF.

    1. Backup the databases in Sql server 2000.

    2. Script the logins,jobs in sql 2000

    3. Install sql server 2005 and service packs etc.

    4. Restore the backups in newly installed sql server 2005.

    5. dbcc updateusage with count_rows

    6. sp_updatestats

    7. Rebuild all indexes

    8. DBCC for DATA_PURITY

    9. Change page verification to CheckSum (from Torn page)

    10. Switch to dblevel 90

    11. Run scripted logins and jobs in sql server 2005

    12. create a new backup

    13. Perform test by the application team(s)

    point 11, put the logins, jobs in after the databases have been successfully restored otherwise they will likely try and reference databases that dont exist

    Copied from this forum....

    MJ

Viewing 7 posts - 1 through 6 (of 6 total)

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