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

sql-server sql-server-2008 sql-server-2014 sql-server-administration
Andrew Kelly picture Andrew Kelly · Oct 28, 2014 · Viewed 18.4k times · Source

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?

Answer

Shanky picture Shanky · Dec 19, 2014

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

Related questions

How to restore SQL Server 2014 backup in SQL Server 2008

Were there any changes in this area with SQL Server 2014? I’ve seen this post Is it possible to restore Sql Server 2008 backup in sql server 2005 and I know that this was not possible as a scenario for 2012 -> 2008 …

Read More
SQL Server Management Studio won't let me add an index to a table

When I right click on the indexes folder in the table the "New Index" menu item is grayed out. I don't understand why. I've deleted all data in the table just in case, and refreshed and restarted SSMS, but no …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More