locked
Migration of SQL Server 2005 to 2012 RRS feed

  • Question

  • HI,

    We are trying to migrate our sql server fgrom 2005 to 2012. We have multiple ASP.NET application like desktop, web, window service, WCF etc..

    ANy idea, what are the usual issues that were faced? Assuming some one has worked on similar migration, it would begreat if you all share your issues faced and steps followed?

    Thanks in advance

    Monday, June 15, 2015 6:23 AM

Answers

  • Hi,

    First you need to decide on whether you need to go with in-place or side-by-side upgrade.

    I would suggest you to go with in-place upgrade only if the applications are not critical, down time is not an issue and you can easily bring a new SQL 2005 server with old copy of the database. The reason is that if due to any reason the upgrade failed and you cant fix it easily then there is no easy return to old version.

    I follow side-by-side migrations (most of the time)

    First check the breaking changes in SQL 2012.

    Then collect a proper trace which represents every actions that happens from the website, this trace is to be used with the upgrade advisor wizard. Once this is done, you will have to implement all the critical fixes.

    Get your new SQL Server ready with SQL 2012. Take a backup copy of the SQL 2005 databases restore in SQL 2012 and change compatibility mode.Move all jobs, logins, linked servers etc

    Then (if you already dont have test application servers) Create test VM's with all the applications and then make it connect to the SQL 2012 database. Now implement the changes as suggested by the upgrade advisor if any.

    Do proper functional testing of all the applications and also do performance testing. Once you are happy with these, all you have to do is get a downtime for each application and then you can schedule migration.

    These are the broader steps for actual cut-over

    1) Stop applications

    2) Backup sql 2005 database, restore it in 2012 and change compatibility mode. (You would only need to worry about jobs, linked servers, logins etc if there is a change from the last time you moved those.)

    3) Change the connection string to point to sql 2012, start applications

    4) Do proper testing and your upgrade is complete.

    With this way you can if required move applications one by one (which are not interconnected) and the risks are very low.


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

    Monday, June 15, 2015 11:11 AM

All replies

  • HI,

    We are trying to migrate our sql server fgrom 2005 to 2012. We have multiple ASP.NET application like desktop, web, window service, WCF etc..

    ANy idea, what are the usual issues that were faced? Assuming some one has worked on similar migration, it would begreat if you all share your issues faced and steps followed?

    Thanks in advance

    You should use In place migration of SQl Server 2005 to 2012. You must run upgrade advisor and see for breaking changes if any

    Breaking changes in SQl Server 2012

    After you migrate database make sure to change compatibility level to 110

    You need to first perform inplace upgrade on UAT environemnt and test you application. Without testing dont move ahead


    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, June 15, 2015 6:54 AM
  • Few thoughts for you:

    Upgrade to SQL Server 2012 is only possible between SQL Server 2005 SP4 or SQL Server 2008 or SQL Server 2008 R2. You can perform in-place upgrade or side-by-side upgrade. In either method you must run upgrade adviser with Trace data (Workload) to ensure that there are no breaking changes that might cause issues later

    Considerations for In-place Upgrade:

    • It is all or nothing which means if upgrade is unsuccessful then you will have nothing so you might have to install everything again
    • If upgrade is successful then you would not to have worry about rest of things such as Linked Servers, SSIS packages, jobs, Logins,  users etc

    Considerations for Side-by-Side Upgrade:

    • It is a manual process, essentially you provision one more machine, install SQL Server 2012 over it then
    • Take SQL Server 2004 back-ups and restore them here
    • Also manually move your SSIS packages, Logins & users
    • Change compatibility of your databases to 110

    In either case, you must fully test your applications if they fully support SQL Server 2012 version

    Hope this helps


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Monday, June 15, 2015 7:20 AM
  • Hi,

    First you need to decide on whether you need to go with in-place or side-by-side upgrade.

    I would suggest you to go with in-place upgrade only if the applications are not critical, down time is not an issue and you can easily bring a new SQL 2005 server with old copy of the database. The reason is that if due to any reason the upgrade failed and you cant fix it easily then there is no easy return to old version.

    I follow side-by-side migrations (most of the time)

    First check the breaking changes in SQL 2012.

    Then collect a proper trace which represents every actions that happens from the website, this trace is to be used with the upgrade advisor wizard. Once this is done, you will have to implement all the critical fixes.

    Get your new SQL Server ready with SQL 2012. Take a backup copy of the SQL 2005 databases restore in SQL 2012 and change compatibility mode.Move all jobs, logins, linked servers etc

    Then (if you already dont have test application servers) Create test VM's with all the applications and then make it connect to the SQL 2012 database. Now implement the changes as suggested by the upgrade advisor if any.

    Do proper functional testing of all the applications and also do performance testing. Once you are happy with these, all you have to do is get a downtime for each application and then you can schedule migration.

    These are the broader steps for actual cut-over

    1) Stop applications

    2) Backup sql 2005 database, restore it in 2012 and change compatibility mode. (You would only need to worry about jobs, linked servers, logins etc if there is a change from the last time you moved those.)

    3) Change the connection string to point to sql 2012, start applications

    4) Do proper testing and your upgrade is complete.

    With this way you can if required move applications one by one (which are not interconnected) and the risks are very low.


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

    Monday, June 15, 2015 11:11 AM