Sql-server – Upgrading SQL Server 2005 to 2016

sql serversql-server-2005sql-server-2016upgrade

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.

Best Answer

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.

Related Solutions

Sql-server – How to update conflict resolver when upgrading from SQL-Server 2005 to SQL-Server 2008

Well .. so nobody got an answer. But I think I (finally) got it. Guess what... it is somewhere in the metamodel (as usual)!

When adding an item to the subscription, the new conflict resolver references to be used by the stored procedure come from the [distribution].[MSmerge_articleresolver] table
But, for existing subscriptions, previous conflict resolver references are stored in the system tables of the publishing database, ie [sysmergearticles], [sysmergeextendedarticlesview], and [sysmergepartitioninfoview]

So we have on one side an item initialy published with SQLSERVER 2005, where the publication references the 2005 conflict resolver, as per the publishing database metamodel. On the other side, the machine will attempt to add the same item to a new publication, this time with a default reference to the conflict resolver available in the distibution database, which is indeed different from the 2005 one ....

To illustrate this, you can check the following

USE distribution
go
SELECT article_resolver, resolver_clsid
  FROM [MSmerge_articleresolver] WHERE article_resolver like '%Later Wins%'
  GO

Then,

USE myPublicationDatabase
go
SELECT article_resolver, resolver_clsid
  FROM [sysmergearticles] WHERE article_resolver like '%Later Wins%'
  GO
 SELECT article_resolver, resolver_clsid
  FROM [sysmergeextendedarticlesview] WHERE article_resolver like '%Later Wins%'
  GO
 SELECT article_resolver, resolver_clsid
  FROM [sysmergepartitioninfoview] WHERE article_resolver like '%Later Wins%'
  GO

So it seems that I should update either the references in the distribution database or the references in the publication database. Let's give it a try!

Sql-server – Migrating a cluster from SQL Server 2005 to SQL Server 2016

This is one of those questions that I love to see. There are a lot of things to add to do to add to migration and upgrade successes and it warms the hear to see the question.

You've already covered a lot of things to consider and I'll add a few thoughts but I am looking forward to seeing other answers with more ideas from others.

I'm not including things you've already included in the great list you have started and knowing me I'll be editing and adding to this answer as I go.

**One disagreement I may have with your approach: Regarding the cluster network name, why not make a new cluster name and just use CNAME to point connections to the old name to the new instance? This lets you make a new name, avoid messing with conflict potential, makes go live night smoother and gives you a clearer and easier rollback. I would do a CNAME approach and change the CNAME once you are sure the old can be taken offline. I may be alone here so I welcome comments supporting that idea or disagreeing.

I see a few areas of focus when it comes to migrating. If you make an "outline" of the phases and outline of the areas to focus on comes to mind: Planning, Pre-Migration Steps, Day of Migration, Post Migration. I'll cover some of those sections here and I am hoping others join in with ideas.

Planning Thoughts

Since you are starting with new hardware. This is a great chance to make sure you are considering all of the best practices that may have been overlooked before. Things like:

Split out your drives. At the very least I'd love to see separate drives for (Your Mileage May Vary here also, really depends on performance profile of your setup):
- Operating System (C drive)
- SQL Server binaries and maybe system DBs excluding TempDB
- User Data Files
- User Log Files
- TempDB data and Log Files
- Local backup and/or misc drive for file imports, staging of data, etc
When formatting your drives for Data files and TempDB specifically, consider doing the 64KB allocation unit size. (Source)
Now is your chance to maybe clean up some of those security holes and flaws that could exist. Look to build with best practices and least access.

Before Migration

Lots of things to do before the migration. Some that come to mind immediately are:

Run the upgrade advisor. SQL Server 2016 made some improvements there and it is a separate download. Look to see what issues may exist.
Do a more thorough test of your DB to see what breaks and what works. Do a test migration and put into 2016 (130) compatibility mode. And do thorough testing. If you don't have a test approach you can look at something like profiling activity and replaying to see what breaks. Test. Test. Test.
Grab the SQL Server and Windows Server 2016 documents about clustering and failover clustering. Look to the checklists and prerequisites carefully.
Ensure you have good transaction log management - why migrate an excessively large LDF file due to poor management.
Make sure you are doing maintenance properly on your databases - especially make sure you are running regular DBCC CHECKDBs - this is a good thing to do before migration or really as part of final migration, but you don't want the first time you do one to be on migration night and find surprises.
Determine success criteria and have a rollback plan (doing a migration to new is great, you can simply rollback any CNAME changes, connection strings and point back to old) in case of issues. And know what issues constitute a rollback and what issues you'll "stay and play" with post migration. Get buy in for this list.
Have a really good backup strategy. Know you can restore. Know where your backup files are, etc.
Look to all server objects. You covered a lot. But don't forget credentials, proxy accounts, alerts, maintenance jobs, SP_Configure settings, etc. A GREAT tool for you to look at that can actually help you remember things is the PowerShell DBATools scripts. These scripts can help you migrate all of these items - including SQL authenticated logins with the correct SIDs for your databases, but they also can help you remember what to migrate when you look through all of the modules. You can look into the scripts here.

Immediately After Migration

Lots more here. And a category of "On migration week" could be added and maybe someone will or I will edit and add later.

Some things here that come to mind straight away:

Have a smoke test / check to verify all came over well.
Change compatibility mode to SQL Server 2016 on all databases if testing proved that out to work. I see this getting missed a lot.
Make sure maintenance and backups are in place. Run a post migration DBCC CHECKDB on the databases.
Make sure your jobs are running.
Configure monitoring and alerting so you don't miss alerts about this server
Once you are satisfied with a limited test, turn the old off and make a CNAME change.