locked
Upgrade sqlserver 2008R2 to 2014 with logshipping RRS feed

  • Question

  • Some of the servers are still using 2008r2 and there are more than 30 databases per server and about 30+ logins and 20+ sqlserver integration packages as well as several applications. Will log shipping be the most accurate process to move database on the new server. We also use VMware and can copy data from all drives from one server to another.

    Since there are lot of unknown in the servers do you suggest in place migration? We are using Sql Server 2008R2 Standard edition


    • Edited by Avyayah Monday, April 29, 2019 6:54 PM
    Monday, April 29, 2019 6:53 PM

Answers

  • Hi Shashank,

    We have to build 2012R2 server and then if I copy all the drives C,D,E, F, G, I, J drive (since we use VM's) do I again have to install sqlserver 2008r2 in the new server or can I install 2012 r2 and complete the in-place upgrade.

    Thanks,

    Chitra

    Just copying would not work for SQl Server it would not come online on new machine as SQL Server respective registry keys would not be found. As I suggested 

    1. Download DMA

    2 Install and Run it for analysis. You have option to create project and in that select destination as SQL Server 2014. Run Assessment and see the report.

    3. if the report has no blockers you may migrate.

    4 Look also for all breaking changes

    5. Now do an inplace upgrade 

    Please also involve SSIS guys to check how packages are working post migration

    OR

    You can build new VM with Windows server 2012 R2, install SQl Server 2014. Configure log shipping to between 2008 r2 DB and 2014. Migrate SSIS packages to 2014 and do a cutover

    PS: Just moving files will not work for SQl Server


    Cheers,

    Shashank

    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 Articles

    MVP

    • Marked as answer by Avyayah Thursday, May 2, 2019 6:53 PM
    Thursday, May 2, 2019 7:09 AM

All replies

  • Hi Avyayah,

    From your description, you can configure the log shipping between lower version to higher version, but the database should in No recovery mode only, Standby is not supported for cross versions of SQL Log shipping. Please refer to Configure Log Shipping (SQL Server). By the way, you can use Data Migration Assistant (DMA)  to detect compatibility issues that can impact database functionality in your new version of SQL Server before you upgrade SQL server. Please refer to Overview of Data Migration Assistant.
    Hope this could  help you.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, April 30, 2019 10:00 AM
  • Some of the servers are still using 2008r2 and there are more than 30 databases per server and about 30+ logins and 20+ sqlserver integration packages as well as several applications. Will log shipping be the most accurate process to move database on the new server. We also use VMware and can copy data from all drives from one server to another.

    Since there are lot of unknown in the servers do you suggest in place migration? We are using Sql Server 2008R2 Standard edition


    Its easy to migrate database via logshipping and you can use that, but note you also have SSIS packages which needs to be upgraded before migration have you considered that.

    You should go for in-place upgrade and before doing this run Data Migration Assistant to know breaking changes if any. This will also help you in migration.


    Cheers,

    Shashank

    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 Articles

    MVP

    Tuesday, April 30, 2019 11:11 AM
  • Hi Shashank,

    We have to build 2012R2 server and then if I copy all the drives C,D,E, F, G, I, J drive (since we use VM's) do I again have to install sqlserver 2008r2 in the new server or can I install 2012 r2 and complete the in-place upgrade.

    Thanks,

    Chitra

    Tuesday, April 30, 2019 9:24 PM
  • Hi Shashank,

    We have to build 2012R2 server and then if I copy all the drives C,D,E, F, G, I, J drive (since we use VM's) do I again have to install sqlserver 2008r2 in the new server or can I install 2012 r2 and complete the in-place upgrade.

    Thanks,

    Chitra

    Just copying would not work for SQl Server it would not come online on new machine as SQL Server respective registry keys would not be found. As I suggested 

    1. Download DMA

    2 Install and Run it for analysis. You have option to create project and in that select destination as SQL Server 2014. Run Assessment and see the report.

    3. if the report has no blockers you may migrate.

    4 Look also for all breaking changes

    5. Now do an inplace upgrade 

    Please also involve SSIS guys to check how packages are working post migration

    OR

    You can build new VM with Windows server 2012 R2, install SQl Server 2014. Configure log shipping to between 2008 r2 DB and 2014. Migrate SSIS packages to 2014 and do a cutover

    PS: Just moving files will not work for SQl Server


    Cheers,

    Shashank

    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 Articles

    MVP

    • Marked as answer by Avyayah Thursday, May 2, 2019 6:53 PM
    Thursday, May 2, 2019 7:09 AM