I have a new server and want to install SQL Server 2014 Standard and migrate my database across from my old server which runs SQL Server 2008 R2 (10.50.1600.1).
I have upgraded locally, but having to move servers on my production environment complicates matters, so I thought I'd ask about the best way to progress giving my situation. I can think of different options to do the upgrade, but I am not sure which is the best choice:
Would it be best to upgrade my current SQL Server 2008 R2 box with service pack 2 before thinking about the upgrade, or will this not make a difference if I'm upgrading? If I do, which is the best option below?
1) Install SQL Server 2008 R2 service pack 2 on the current server, upgrade in-situ with SQL Server 2014, perform backup. Restore SQL Server 2014 backup on new server.
2) Install SQL Server 2008 R2 service pack 2 on the current server, perform backup. Restore SQL Server 2008 R2 backup on new server on SQL Server 2014.
I understand that SQL Server 2014 includes new full text search features that are not available in 2008 which you are prompted for as part of the installation process, so I'm thinking that if I just restore a 2008 backup on 2014 I will not be 'upgrading' in full. Would it therefore be better to take option 1 (above) over option 2?
-> Restoring backup will not upgrade your database. So, 1)Microsoft SQL Server 2014 installation setup provide options to upgrade your database either from 2008 R2 or from 2012. This will preform all the process and provide information if your database is upgrade able or you required to make some changes to your old database. After that you can proceed to upgrade database from 2008 R2 to 2014.
eg. Click Start, point to All Programs, point to Microsoft SQL Server 2014 , and then click SQL Server 2014 Upgrade Advisor. For further details http://msdn.microsoft.com/en-us/library/ms144256.aspx
My recommendation would be to install SQL Server 2014 instance and then backup and migrate database from SQL Server 2008 R2 to SQL Server 2014
2) There are other third party tools that will preform all the necessary processes and messaging before upgrade.