locked
Upgrade SQL 2005 standard to SQL 2008 Standard RRS feed

  • Question

  • Hello ,

    We have been assigned a task for upgrading from sql 2005 to sql 2008r2 standard.

    SQL 2005 is on windows 7 with an application pointing to it for general usage by HR people.

    Now, a new OS has been built, windows 2008R2 and we have installed SQL 2008R2 standard.

    We believe , to check for any issues we should use upgrade adviser and then if Ok, can simply use the backup from SQL 2K5 and restore onto SQL2k8R2 and later changing compatibility.

    Please advise if i am doing something wrong using above approach, also i found that on SQL 2K5 there is no management studio, so i was of the view will above plan work? I tried using ssms.exe but it says windows cannot find ssms.exe.

    Please help how can i upgrade in such scenario?

    • Moved by Olaf HelperMVP Friday, June 12, 2015 10:33 AM Moved from "Database Engine" to a more specific forum
    Friday, June 12, 2015 9:33 AM

Answers

  • Hello,

    You plan for a side-by-side installation, when you need to upgrade an instance no a new version but SQL Server setup program does not support the upgrade path for some reason. Maybe because is a cross platform upgrade, or you are upgrading from Enterprise Edition to Standard Edition, for example. Those are examples of upgrade paths not supported by SQL Server setup.

    You can choose a side-by-side upgrade when you need to run the new SQL Server version in parallel with the legacy version. This makes rollback to the legacy instance very easy. You have more control on the upgrade process when you choose a side-by-side upgrade, and you can test applications as needed.

    In the other hand, the in-place upgrade replaces the current SQL Server version of a target instance with a new SQL Server version, preserving server configuration and databases. It’s a great method for small systems and it is a fast process. The disadvantage of this method is complex rollback procedure.

    If you are creating a new computer with Windows Server 2008 R2 and SQL Server 2008 R2, you have to go with a side-by-side upgrade. An in-place upgrade takes place on the same computer, only one computer is involved on an in-place upgrade.

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by MAXKA Friday, June 12, 2015 1:11 PM
    Friday, June 12, 2015 12:23 PM
  • Hi,

    When you run upgrade advisor and every thing is OK then you dont need to do backup restore you can perform inplace upgrade as long as you follow upgrade matrix .

    If you dont find SSMS then also perform upgrade and then later add SSMS via manual installation

    Plus i guess you are aware Soon SQL server 2008 will loose its charm :).


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by MAXKA Friday, June 12, 2015 1:11 PM
    Friday, June 12, 2015 9:44 AM

All replies

  • Hi,

    When you run upgrade advisor and every thing is OK then you dont need to do backup restore you can perform inplace upgrade as long as you follow upgrade matrix .

    If you dont find SSMS then also perform upgrade and then later add SSMS via manual installation

    Plus i guess you are aware Soon SQL server 2008 will loose its charm :).


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by MAXKA Friday, June 12, 2015 1:11 PM
    Friday, June 12, 2015 9:44 AM
  • Shanky@ Thanks for the inputs.

    Is inplace the suitable go here. I am not against it.

    But its on being a safer and easier way in case anything is broken and we need to roll back. Also as there is change in OS, am not sure that has something to do with this, but would Inplace go good?

    Also, as said i am running UA and will let you know for any issues or not so that we can decide what suits the best.

    Thnx again!



    • Edited by MAXKA Friday, June 12, 2015 10:47 AM
    Friday, June 12, 2015 10:46 AM
  • Hello,

    You plan for a side-by-side installation, when you need to upgrade an instance no a new version but SQL Server setup program does not support the upgrade path for some reason. Maybe because is a cross platform upgrade, or you are upgrading from Enterprise Edition to Standard Edition, for example. Those are examples of upgrade paths not supported by SQL Server setup.

    You can choose a side-by-side upgrade when you need to run the new SQL Server version in parallel with the legacy version. This makes rollback to the legacy instance very easy. You have more control on the upgrade process when you choose a side-by-side upgrade, and you can test applications as needed.

    In the other hand, the in-place upgrade replaces the current SQL Server version of a target instance with a new SQL Server version, preserving server configuration and databases. It’s a great method for small systems and it is a fast process. The disadvantage of this method is complex rollback procedure.

    If you are creating a new computer with Windows Server 2008 R2 and SQL Server 2008 R2, you have to go with a side-by-side upgrade. An in-place upgrade takes place on the same computer, only one computer is involved on an in-place upgrade.

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by MAXKA Friday, June 12, 2015 1:11 PM
    Friday, June 12, 2015 12:23 PM
  • You can use both methods Inplace and side by side its up to you. if you have complex environment including SSAS,SSRS then I woul suggest side by side else if UA does not points any breaking changes you can go with inplace.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, June 12, 2015 12:34 PM