SQL Server 2008 R2 Upgrade / Server Move to SQL Server 2014

0
votes

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?

2
sql-serversql-server-2008sql-server-2014sql-server-administration

2 Answers

3
votes

I did not find answer given by Aashish promising because he said

Restoring backup will not upgrade your database

This looks confusing. If you restore backup taken on 2008 r2 database to 2014 it would be upgraded automatically to 2014. After restoration change compatibility level to 120 and you are good to go.

Regarding upgrade from 2008 r2 to 2014 there are two methods

  1. Inplace upgrade

  2. Side by side upgrade

In place upgrade can be use when your environment is less complex. You need to make sure you first upgrade SQl Server 2008 R2 to SP2/Sp3( I would recommend SP3) and then run Upgrade advisor. Only when upgrade advisor does not finds any issue you can go ahead and do inplace upgrade.

Side by side upgrade is much safer but is time consuming and has rollback plan. The old instance is not touched. In this method you have to install new instance of SQL Server 2014 then migrate database using backup and restore method.

Then migrate logins using this Support article

Then migrate job using script out method of you can us SSIS transfer job task to migrate jobs.

Migrate linked server and other server objects using script out method.

Migrate SSIS packages if any.

After this you are good to go. Make sure after migration you rebuild indexes and update statistics using Ola Hallengen Index rebuild and update Statistics solution

Do let me know if any further information is required

0
votes

-> 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.