locked
SQL 2005 9.00.3080 upgrade to SQL 2014 RRS feed

  • Question

  • Hi All,

    I'm looking to migrate from a Windows 2003 Server running SQL 2005 Server on 9.00.3080 to a Windows 2012 R2 Server running SQL 2014. 

    I just wanted to know a few things about the migration:

    1. Is this possible using the versions of SQL I'm going from and too?
    2. I've read I can use backup/restore to move from one SQL installation to the other.  How would this work?  Would I just backup each DB in management studio individually and restore them?  How does the SQL based application that was using the 2005 installation now know that the database has moved to a new server?
    3. Can I use SQL log shipping to do this?  I've read I can do this with no downtime?
    4. Are there any caveats to this or gotchas?  i.e are there any things that will change in the DBs after moving them that will potentially cause problems with the application?
    5. Has anyone done this before and what was your experience?

    Thanks for any help!

    Rob

    Thursday, July 7, 2016 7:46 PM

Answers

  • Hi Robclarke411,

    >> 1.Is this possible using the versions of SQL I'm going from and too?

    Yes it’s possible. According to BOL, you could upgrade to SQL Server 2014 from SQL Server 2005 SP4 or later.

    >> 2.I've read I can use backup/restore to move from one SQL installation to the other.  How would this work?  Would I just backup each DB in management studio individually and restore them?  How does the SQL based application that was using the 2005 installation now know that the database has moved to a new server?

    I think you are referring to Side-by-side upgrade. It’s basically a migration process so you can either use backup/restore or other methods such as SSIS packages for it. Correct me if I’m wrong, I don’t think any SQL-based application should notice the difference as after testing the new SQL Server, you could just take the old server offline and redirect the application to new server.

    >>3.Can I use SQL log shipping to do this?  I've read I can do this with no downtime?

    Yes, but it’s for user database only. As far as I know, you cannot log shipping a system database.

    >>4.Are there any caveats to this or gotchas?  i.e are there any things that will change in the DBs after moving them that will potentially cause problems with the application?

    Backup everything before upgrade in case something goes wrong and test everything after upgrade. Other than that, using SQL Server 2014 Upgrade Advisor to find out the issues need to be fixed before upgrade.

    >>5.Has anyone done this before and what was your experience?

    I would suggest you review SQL Server 2014 Upgrade Technical Guide.

    If you have any other questions, please let me know.

    Regards,
    Lin


    Friday, July 8, 2016 3:21 AM