Post reply

Upgrade 2005 Standard 32bit to 2008 Enterprise 64bit

  • June 1, 2011 at 4:11 am

    #389628

    Hi we are planning to upgrade one of our servers from SQL Server 2005 Standard 32 bit on Windows 2003 Standard to SQL Server 2008 R2 Enterprise 64 bit on Windows 2008, so have determined the best course of action is to build a new server and migrate databases to the new server. The server is a standalone server with no clustering or replication involved.

    My instinct is that the best approach would be to

    - transfer logins with http://support.microsoft.com/kb/246133

    - dettach the database from the old server and re-attach to the new ones

    - Jobs will be created from scratch again as part of a review of the jobs

    First Is this the best approach and are there any gotcha's I should look out for and...

    Second we have a couple of SSIS packages stored in the MSDB package repository on the 2005 server - what is the easiest way to transfer them or do they have to be re-saved and re-published in Business Intelligence Studion 2008?

    Thanks

  • June 1, 2011 at 4:19 am

    #1332418

    - transfer logins with http://support.microsoft.com/kb/246133

    Sounds Good

    - dettach the database from the old server and re-attach to the new ones

    I will suggest to go with backup restore approach for safe side. It will make your 2005 available till your application not comfortable enough with 2008. For less downtime, you can restore the latest full backup on 2008 and let the database on norecovery and then you can do log restore when you want to make your database available on 2008.

    - Jobs will be created from scratch again as part of a review of the jobs

    If you want you can script out all your jobs and run it on 2008. Let them in disable mode.

    we have a couple of SSIS packages stored in the MSDB package repository on the 2005 server - what is the easiest way to transfer them or do they have to be re-saved and re-published in Business Intelligence Studion 2008?

    Save the packages using file system and then import them in 2008.

    Also forget to mention, post upgrade do not forget:-

    1) run DBCC checkdb with no_infomsgs, all_errormsgs

    2) Update the Stats

    3) Change the compatibility level of database

    4) Update the usage

    ----------
    Ashish

  • June 1, 2011 at 4:28 am

    #1332421

    Thanks Ashish,

    Good point on the Backup v Attach/Detach

    I just wondered whether there may be a quick way of transferring the packages, but I guess it's not that much effort to save them in the file system and publish them in the new server - and probably safer too

    Cheers

    Simon

  • June 1, 2011 at 4:55 am

    #1332435

    This script will help you to generate the server role for existing logins:-

    Declare @Prefix varchar(255)

    Declare @tmpstr varchar(MAX)

    Set @Prefix = '

    exec master.dbo.sp_addsrvrolemember @loginame='''

    Set @tmpstr=''

    Select @tmpstr = @tmpstr

    + Case When sysadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''sysadmin''' Else '' End

    + Case When securityadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''securityadmin''' Else '' End

    + Case When serveradmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''serveradmin''' Else '' End

    + Case When setupadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''setupadmin''' Else '' End

    + Case When processadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''processadmin''' Else '' End

    + Case When diskadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''diskadmin''' Else '' End

    + Case When dbcreator = 1 Then @Prefix + [LoginName] + ''', @rolename=''dbcreator''' Else '' End

    + Case When bulkadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''bulkadmin''' Else '' End

    From (select convert(varchar(100),suser_sname(sid)) as [LoginName],

    sysadmin,

    securityadmin,

    serveradmin,

    setupadmin,

    processadmin,

    diskadmin,

    dbcreator,

    bulkadmin

    from sys.syslogins

    where sysadmin<>0

    or securityadmin<>0

    or serveradmin<>0

    or setupadmin <>0

    or processadmin <>0

    or diskadmin<>0

    or dbcreator<>0

    or bulkadmin<>0) L

    Print @tmpstr

    I have found it in some blog so its not my hard work. I am just sharing as it helps a lot.

    ----------
    Ashish

  • June 1, 2011 at 5:38 am

    #1332444

    simon-220850, i agree with the procedure mencioned by crazy4sql!!!

    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

  • June 1, 2011 at 5:38 am

    #1332445

    thanks crazy4sql for share the script!!! it's very useful!

    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

  • June 2, 2011 at 2:34 am

    #1332949

    1-Take Full Backup and then restore with norecovery on another instance or Server

    2-Wait for the restore process of Step 1 till complete

    3-Take application offline and then take Differential backup and then restore with recovery

    downtime will be start on Step 3

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

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

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