locked
SQL 2012 upgrade question RRS feed

  • Question

  • Hi All,

    We are in process of upgrading to 2012 from 2008 R2, however my development team also wants to keep 2008R2 version of some databases to support small production releases to be release to 2008R2 prod server.

    I've two options:

    1. keep separate server for 2008R2 (which has additional cost).

    2. move these databases to new SQL 2012 edition and set compatibility mode to 2008R2. and development team can migrate code from these databases to 2008R2 prod.

    My question is , is there any caveat in going with option 2.

    Monday, August 17, 2015 3:30 PM

Answers

  • My question is , is there any caveat in going with option 2.

    You will not be able to restore databases in 2008 R2 that have been backed up in SQL 2012.

    See this link for more info:

    Restore database from SQL Server 2012 to SQL Server 2008 R2


    Dan Randolph - My Code Samples List

    Monday, August 17, 2015 3:42 PM
  • Hello,

    You will have to script changes to objects (stored procedures, functions, tables, views, etc) in order to apply them on SQL Server 2008 R2 production instances.


    Additionally to the fact mentioned by Dan, you won’t be able to detach a database from SQL Server 2012 and attach it to any SQL Server 2008 R2.


    SQL Server 2008 R2 Developer Edition only cost $50 dollars more or less.

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Monday, August 17, 2015 4:09 PM
  • 2. move these databases to new SQL 2012 edition and set compatibility mode to 2008R2. and development team can migrate code from these databases to 2008R2 prod.

    My question is , is there any caveat in going with option 2.

    Yes there might be some road blocks you need

    1. Run upgrade advisor to see any breaking changes as pointed by Upgrade advisor

    2. Refer closely Breaking changes in SQL Server 2012

    3. You can migrate database and keep compatibility level to 100 I cannot upfront say what would fail you need to test this. Its easy to change and change back compatibility level


    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

    Monday, August 17, 2015 4:22 PM

All replies

  • My question is , is there any caveat in going with option 2.

    You will not be able to restore databases in 2008 R2 that have been backed up in SQL 2012.

    See this link for more info:

    Restore database from SQL Server 2012 to SQL Server 2008 R2


    Dan Randolph - My Code Samples List

    Monday, August 17, 2015 3:42 PM
  • Hello,

    You will have to script changes to objects (stored procedures, functions, tables, views, etc) in order to apply them on SQL Server 2008 R2 production instances.


    Additionally to the fact mentioned by Dan, you won’t be able to detach a database from SQL Server 2012 and attach it to any SQL Server 2008 R2.


    SQL Server 2008 R2 Developer Edition only cost $50 dollars more or less.

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Monday, August 17, 2015 4:09 PM
  • 2. move these databases to new SQL 2012 edition and set compatibility mode to 2008R2. and development team can migrate code from these databases to 2008R2 prod.

    My question is , is there any caveat in going with option 2.

    Yes there might be some road blocks you need

    1. Run upgrade advisor to see any breaking changes as pointed by Upgrade advisor

    2. Refer closely Breaking changes in SQL Server 2012

    3. You can migrate database and keep compatibility level to 100 I cannot upfront say what would fail you need to test this. Its easy to change and change back compatibility level


    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

    Monday, August 17, 2015 4:22 PM