locked
SQLServer Upgradation process steps RRS feed

  • Question

  • Hi All,

    We are planning upgrading the server. Present we have windows server2003R2 ,sqlserver2005                                            We need to upgrade both OS and MSSQL to the latest version, i.e., 2008.

    How could we achieve this?

    Please provide us with a high level action list of the steps focussing only MSSQL server and OS.

    Thanks in advance....

    • Moved by Olaf HelperMVP Wednesday, October 29, 2014 7:07 AM Moved from "Database Engine" to a more specific forum for a better response.
    Wednesday, October 29, 2014 6:58 AM

Answers

  • Why do you want to migrate to Windows 2008/SQL2008 ?

    They are not the latest versions. Latest would be Windows 2012 R2 and SQL 2014.

    I would not recommend doing a inplace upgrade. First decide which versions you want to go to. Then Run Upgrade Advisor SQL for that version and find what all are the recommendations/issues.

    Check for breaking changes/ discontinued features in the latest version.

    Now get the new server ready with the latest versions.

    Take backup from old server and then restore to new server.

    Change compatibility mode to latest.

    Move logins to new server - http://support2.microsoft.com/kb/246133

    Change the configuration settings (sp_configure)

    Set up linked servers/jobs etc.

    At this point you new server would be almost ready. I would now recommend to do a performance test of your application against this server to confirm that all your KPI's are met.

    At this point if everything is satisfactory, then you could get a downtime from stakeholders and restore the latest copy of the databases to new server and offline old server. Change applications to point to new server.

    Check if any new logins/linked servers/jobs have created from the time you copied them and move them over.

    Note: SQL Server 2005 needs to be SP4 if you want to move to SQL 2014/SQL 2012


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, October 29, 2014 9:04 AM
  • Unless its is hardcore requirement and you are sure you can get extended support easily don't go for SQL Server 2008. It does not falis under normal support category now plus SQL Server 2012 has lot of good features You must have a look at below doc

    Considerations while upgrading DB engine

    Upgrade to SQL Server 2012


    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

    Wednesday, October 29, 2014 9:38 AM
  • Hello,


    Assuming your current Windows Server 2003 R2 is 64-bit, please follow this plan:

    1. Please apply the service pack for SQL Server 2005 SP3 or later.
    2. Upgrade the operating system to Windows Server 2008 or Windows Server 2008 R2.
    3. Apply SP2 for Windows Server 2008 or SP1 for Windows Server 2008 R2.
    4. Upgrade SQL Server 2005 to SQL Server 2012.
    5. If you want to upgrade to Windows Server 2012, it is possible at this point.

    If Windows Server 2003 R2 is a 32-bit operating system, then you will have to perform a side-by-side upgrade, since cross-platform upgrades are not allowed (Windows Server 2008 R2 and later are 64-bit operating systems).

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, October 29, 2014 2:28 PM

All replies

  • If you have a new server just install windows 2012R2 and SQL 2014 SP1 onto it, backup your databases on the 2005 box and copy the backups to the new server. Look into synchronising your logins from the old server to the new (this should help)

    Then restore your DBs on the new server and alter their compatibility level to 2014. You should test all your stored procedures still work after this.

    If you don't have a new server you will need to store the backups of server and copy them back once you have installed the new OS and SQL 2014. You will also need to script the logins out and script them back in. The li nk above should still help with that.

    Wednesday, October 29, 2014 8:50 AM
  • Why do you want to migrate to Windows 2008/SQL2008 ?

    They are not the latest versions. Latest would be Windows 2012 R2 and SQL 2014.

    I would not recommend doing a inplace upgrade. First decide which versions you want to go to. Then Run Upgrade Advisor SQL for that version and find what all are the recommendations/issues.

    Check for breaking changes/ discontinued features in the latest version.

    Now get the new server ready with the latest versions.

    Take backup from old server and then restore to new server.

    Change compatibility mode to latest.

    Move logins to new server - http://support2.microsoft.com/kb/246133

    Change the configuration settings (sp_configure)

    Set up linked servers/jobs etc.

    At this point you new server would be almost ready. I would now recommend to do a performance test of your application against this server to confirm that all your KPI's are met.

    At this point if everything is satisfactory, then you could get a downtime from stakeholders and restore the latest copy of the databases to new server and offline old server. Change applications to point to new server.

    Check if any new logins/linked servers/jobs have created from the time you copied them and move them over.

    Note: SQL Server 2005 needs to be SP4 if you want to move to SQL 2014/SQL 2012


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, October 29, 2014 9:04 AM
  • Unless its is hardcore requirement and you are sure you can get extended support easily don't go for SQL Server 2008. It does not falis under normal support category now plus SQL Server 2012 has lot of good features You must have a look at below doc

    Considerations while upgrading DB engine

    Upgrade to SQL Server 2012


    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

    Wednesday, October 29, 2014 9:38 AM
  • Hello,


    Assuming your current Windows Server 2003 R2 is 64-bit, please follow this plan:

    1. Please apply the service pack for SQL Server 2005 SP3 or later.
    2. Upgrade the operating system to Windows Server 2008 or Windows Server 2008 R2.
    3. Apply SP2 for Windows Server 2008 or SP1 for Windows Server 2008 R2.
    4. Upgrade SQL Server 2005 to SQL Server 2012.
    5. If you want to upgrade to Windows Server 2012, it is possible at this point.

    If Windows Server 2003 R2 is a 32-bit operating system, then you will have to perform a side-by-side upgrade, since cross-platform upgrades are not allowed (Windows Server 2008 R2 and later are 64-bit operating systems).

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, October 29, 2014 2:28 PM