Sql-server – Migrating from SQL Server 2008 R2 to SQL Server 2014

sql serversql server 2014sql-server-2008-r2

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.

Best Answer

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.