I have SQL 2008R2 server to migrate to SQL2014 Server.
A side by side upgrade is what we are thinking for few databases in SQL 2008R2 which are having compatibility level of 90 or 80.
Can I migrate these databases (back& restore) to SQL2014 and sql will allow me to change compatibility level to 120 ? if not how can i deal with this?
thanks
Yes you can migrate, but that totally depends upon the results of testing you have done pre-migration. That is , you can test the application on test-staging server before you make any changes in production. As per the results you may get a go-head.
Also, please refer to this MSDN article, to understand the difference between various compatibility levels in SQL server.Therefore you will need to go what has been changed since OLD Compatibility versions till now and how will it behave when you change to new.
As mentioned :
When a stored procedure executes, it uses the current compatibility level of the database in which it is defined. When the compatibility setting of a database is changed, all of its stored procedures are automatically recompiled accordingly.
Also, running upgrade advisor will be helpful during you're migration strategy which shall let you know for any issues, so that they can be looked after accordingly.
In addition you can read Bad habits : Clinging to old compatibility levels and What is the actual behavior of compatibility level 80? from Aaron.
Also, since SQL Server 2014 has a new Cardinality Estimator, it is important that you test all of your queries in order to take advantage of this new feature.
I suggest you also Read Changes In SQL Server 2014's new Cardinality Estimator
Yes, Definitely you can migrate from SQL Server 2008 R2 to SQL Server 2014.
There are two basic strategies for upgrading to a new SQL Server. 1) Full backup with Clean install 2) In-Place Upgrade
As per Michael Otey article. The In-Place Upgrade is Much faster than Full Backup with Clean Install.
If you shall go for "In-Place upgrade" then for all installations of SQL Server, the default compatibility level is set to the version of the Database Engine . As per MSDN BOL Article
Either option 1 or option 2 the SQL server always allow to change compatibality level (in your case SQL Server 2008 R2 to SQL Server 2014).
But you have to keep in mind to migrate any SQL Server . Before doing migration of sql server keep at least you have take Full Backup with full recovery model with native SQL Backup. It's always safeguard in case of production Server.
In your case Compatibility is not issue , Always think about to migration of Old DB engine to new One. If you have past vendor of DB implemention. Always consult with them.
