4

I have a requirement to get rid of our old servers, however one of them includes a database server that is currently using SQL Server 2005.

We have a new server running with 2016 so I wanted to know the best way to upgrade. From what I have discovered is that ideally we would need 2008 as an interim step. However as we do not have 2008 and this cost more I have been looking at the possibility of doing a 2005 back up and then a 2016 restore.

I have tested this out with a very simple db and it seems to work. However I have read that I should change the compatibility level from 100 (use to be 90) to 130. My question is, is the necessary? After all some of these old db's are old legacy databases which require very little change. Is it really worth it?

So what I will not be able to use the fancy new ways of doing stuff, but as these are old does that matter? After all its not like it is needed otherwise we would not have created these db. I am yet to still test with a more complex db but wanted to make sure I am on the right path.

Improve this question
1
  • The biggest behavior change involving compatibility level happened between 2000 and 2005. To see the kinds of things this affected, see this question.
    – Aaron Bertrand
    Commented Nov 19, 2018 at 12:42

2 Answers 2

Reset to default
1

Since you are migrating to a new server, you don't need to bring SQL Server 2008 into the mix.

As stated by SQL Server expert Paul Randal, You can upgrade from any version 2005+ to any other version. Paul states:

There’s a persistent myth that you cannot upgrade a database to a version more that is more then two versions newer.

It’s not true.

You can backup+restore (safest way) or detach+attach (not a safe way) from SQL Server 2005 (or higher) to any other newer version. You just can’t do an in-place upgrade to the latest version from SQL Server 2005.

As for changing the compatibility level, I'd recommend not changing that for several weeks and let the dust settle from the upgrade. After you're confident that everything is functioning properly from the upgrade, why not bump the compatibility level up to the maximum and monitor performance. You might even see an improvement in performance. However, compatibility levels from 120 (SQL Server 2014) and above bring the new cardinality estimator into play, which might present some performance problems. If you want, you can be more selective in your testing of the new cardinality estimator by using OPTION (QUERYTRACEON 2312) on selected queries. You can always reduce the level should problems arise. Do some internet searches on sql server 2014 cardinality estimator issues to familiarize yourself with possible issues.

At some point in the future when you upgrade to a later release of SQL Server, you WILL have to eventually increase the compatibility level.

Finally, check out Upgrade SQL Server for a checklist of items to consider for upgrading SQL Server.

Referencing ALTER DATABASE (Transact-SQL) Compatibility Level:

From an application perspective, the goal should still be to upgrade to the latest compatibility level at some point in time, in order to inherit some of the new features, as well as performance improvements done in the query optimizer space, but to do so in a controlled way. Use the lower compatibility level as a safer migration aid to work around version differences, in the behaviors that are controlled by the relevant compatibility level setting. For more details, including the recommended workflow for upgrading database compatibility level, see the Best Practices for upgrading Database Compatibility Level.

Improve this answer
0

You would need to change the DB compatibility level to at least 100 since on SQL server 2016, minimum DB compatibility level supported is 100.

See : https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017

A backup restore would just work.

I have detailed step by step pre and post migration steps that would help you. https://dba.stackexchange.com/a/50315/8783

Test the migration process as there will be some breaking changes.

Also, on 2014 the DB compatibility level of 90 is supported. So that is something you want to explore if you cannot change the DB compatibility level.

From PASS summit, I learned that an application should be certified based on be compatibility level and not on the version of SQL server.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.