Post reply

Upgrade to SQL2005

  • November 26, 2007 at 10:35 pm

    #119576

    Hi All,

    I preparing to upgrade from SQL2000(SP4) to SQL2005(SP2). For testing before actual migration, I have chosen to use Side-by-Side method and upgraded using backup/resotre step. I have created and setup new SQL2005 instance and did a backup of the database on SQL2000. Copied over the *.bak files to the new server where I have SQL2005 instance. I restored the database with the same name in new server without any issue.

    Now I would like to confirm, is there any post upgrade step to be taken care when i use backup and restore method? I run through the upgrade manual, its all talks about application related details after upgrade.

    Would be much apreaciated if any one can guide me, If I missout any steps in the upgrade task.

    Rgds

    M

  • November 26, 2007 at 10:57 pm

    #754383

    Best information you can see SQL BOL. (http://msdn2.microsoft.com/en-us/library/ms130214.aspx)

  • November 27, 2007 at 12:19 am

    #754403

    Hi Mahen,

    I want just add thoses links which seems interesting to learn before migration

    http://www.sqlservercentral.com/articles/Administration/sqlserver2005bestpractices/2183/

    http://www.dell.com/downloads/global/power/ps1q06-20060126-Microsoft.pdf

    http://articles.techrepublic.com.com/5100-9592-6036309.html

    http://searchsystemschannel.techtarget.com/tip/0,289483,sid99_gci1229039,00.html

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

    Regards,

    Ahmed

  • November 27, 2007 at 4:00 am

    #754457

    You are on the right path ...also change the compatibility to 90.

  • November 27, 2007 at 7:40 am

    #754554

    make sure all logins are created and run sp_change_users_login

  • November 28, 2007 at 8:52 am

    #755014

    Check out Microsoft's site on upgrading to SQL 2K5. Also, download and run the SQL Server 2005 Upgrade adviser.

    http://www.microsoft.com/sql/solutions/upgrade/default.mspx

    Hope all goes well with the upgrade...

    David

  • November 29, 2007 at 8:39 pm

    #755779

    Thanks a lot to you all buddy.

    I have run through the document which david said, which I followed for upgrade too. But this one is not talks about any specific mandatory action to be done after upgrade. Its all generall like collect stats, check DB etc... Does this means, nothing specific?

    rgds,

  • November 30, 2007 at 3:48 am

    #755868

    In 2005 the schema is sepearted from users so might need to run sp_change_users_login other thats the only thing i can think off

    Jayanth Kurup[/url]

  • November 30, 2007 at 4:40 am

    #755881

    run sp_updatestats as well

    ---------------------------------------------------------------------

  • November 30, 2007 at 8:33 am

    #755989

    The Upgrade Adviser will tell you if there are any mandatory changes needed prior to the migration. Other then that, database from SQL2K migrate over to SQL2K5 really well. Generally after I use the backup/restore method and you could simply restore the database from SQL2K and run with it. I would not recommend this because you should at least check a few things out.

    To make the database work as a SQL2K5 db you need to at least change the compatibility level to 90.

    The other items I would recommend you do are (not necessarily in this order):

    - Fix db users (sp_change_users_login or delete db user and re-add). I prefer to delete and re-add because deleting the db user will also give option to delete the schema created with the user name. I don't like to have extra schema's configured...

    - Delete any unused schema's... Base schema's you need are dbo, sys, guest and information_schema, you can delete all others unless, of course, they are being used.

    - Change PageVerify option to CHECKSUM. SQL2K used TornPageDetection and this has been updated in SQL2K5. See BOL for more info on this.

    - Rebuild any full text catalogs

    - Update the db stats

    - Rebuild all the indexes... I've read where these could have issues after an upgrade. I have not see that happen yet but better to be safe :hehe:

    - Verify db owner

    - Verify db growth settings. I've seen these get messed up to something like 64000% or set to default of 1MB.

    - Run db integrity check

    - Manually backup the database and add it to you existing backup processes.

    David

  • December 1, 2007 at 3:08 pm

    #756255

    Thanks a lot for all your responses and tips from David.

    I got it now.

    Thanks to all buddys once again.

    Rgds,

    M

Viewing 11 posts - 1 through 10 (of 10 total)

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