Hello, the company is moving our production database to new server and I was given the task of looking at SQL 2016 as a possible upgrade path from SQL 2008R2.
We have some folks here in the do not upgrade until SP1 is out. So my question is has anyone seen problems with SQL 2016 that would need watching?
Is there a way to check old code to see if there is a problem. Yes we would test our code on a dev system but you always miss one. I was thinking of something like SQLCop but for the upgrade.
If companies are going to wait for SP1, they are going to be waiting for a long time. Microsoft is moving away from service packs.
I would recommend SQL Server 2016. Many companies are running it for their extremely mission critical databases.
Be on the lookout for the Best Practices Analyzer for 2016. It wasn't ready when 2016 RTM'd but should be ready soon, if not already (haven't checked).
Pretty sure for 2008 R2, you'll need to upgrade to 2012 or 2014 first and then to 2016. You can do it with detach/attach directly, but that would mean a lot of downtime for the file copy. The best method is backup/restore, but you need a version in between to help you get to 2016.
So what are the problems with backup/restore directly from 2008R2 to 2016? I did that with my test system and things seem to be working. (Notice this is not a full test but a "pre" test system).
If the restore worked, then there is no issue. I didn't think you could restore 3 versions back. I know the compatibility level had to be raised as 2008 isn't available on 2016, so yours is likely 110 (2012) now.
If I recall the 2016 discontinued list correctly, if you are using replication in any way, shape, or form, then that's the deal breaker. Replication ONLY jumps by two revs at a time. As Tara points out, everything else works pretty well. I don't know much about SSIS, SSRS, or any of the other 4 letter words in SQL Server. I finally got rid of the last SSIS package just before we started migration from 2005 to 2012.
Tara, the compatibility level was listed as 100 in "Object Explorer Details" but I have now changed to 130 for my testing.
Jeff, the only "four letter words" I use is SSMS. Unless you count the SSIS import wizard. I believe there are a couple of packages (max of two) lurking in the shadows that now that you reminded me I shall go looking for.
I was thinking of something like SQLCop but for the upgrade.