4

I'm migrating from SQL Server 2008 R2 to SQL Server 2014. Can someone tell me if my approach will work?

Current Environment:

  • SQL Server 2008 R2
  • Windows 2008 R2

Steps to Migrate from SQL Server 2008 R2 to SQL Server 2014:

  1. Get clean install of Windows Server 2012
  2. Install SQL Server 2014 Standard x64
  3. Backup and restore SQL Server 2008 R2 databases to the 2014 instance
  4. Import SSIS packages into SQL Server 2014
  5. Do conversion of DTS packages to SSIS packages in SQL Server 2014

Done.

In theory will this work? I don't want to do an in-place upgrade. I can't because I'm going from a physical server to a virtual server as well as doing an OS upgrade.

Improve this question
6
  • 1
    I don't see a reason why it wouldn't work. It is a pretty safe bet that you will be able to restore your database(s) and have them up and running. It is more a question of migrating any applications that use the SQL database(s) than the server itself.
    – SunKnight0
    Commented May 27, 2016 at 14:00
  • 1
    In theory yes. The beauty of it is if it doesn't you still have your physical box. I'd test it before and make sure everything goes correctly so you don't get caught off guard by any "gotchas".
    – SQLChao
    Commented May 27, 2016 at 14:11
  • 2
    If you need any of your server level objects they will not cross over with the steps you have listed. Security, agent jobs, linked servers, are a couple examples. You should also test your queries out with the 2014 cardinality estimator.
    – Chris Albert
    Commented May 27, 2016 at 15:02
  • @marc_s - oops sorry. I didn't realize that until you said something.
    – Kyle Johnson
    Commented May 27, 2016 at 15:03
  • @Chris - I understand. Testing will definite have to be done.
    – Kyle Johnson
    Commented May 27, 2016 at 15:05

1 Answer 1

Reset to default
2

Your plan sounds OK to me. I have written a detailed answer describing each step at upgrading from lower version of sql server to higher version.

To help automate most of the process use PowerShell dbatool written by Chrissy LeMaire [migrates over 25 components including databases, jobs , logins, etc]. Here is a small video as well to get you started and how it works.

make sure you do full regression testing as moving to sql server 2014, we got hit by the new cardinality estimation (CE) bug and had to use TF 9481 to use the old CE.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.