February 1, 2016 at 8:38 am
Personally at this point in time, I'd be planning for 2016 and starting the testing now (on the CTP), and you MUST test. There have been too many core changes between 2008 R2 and 2016 (especially the cardinality estimator) to wing it and hope.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2016 at 8:47 am
If you are moving to 2016, it will be two phase migration, from 2008R2 - > 2014 and then from 2014 -> 2016. At most you upgrade to 2 versions.
February 1, 2016 at 9:00 am
curious_sqldba (2/1/2016)
If you are moving to 2016, it will be two phase migration, from 2008R2 - > 2014 and then from 2014 -> 2016. At most you upgrade to 2 versions.
SQL Server supports upgrade from sql 2008 to sql server 2016, more info here (sql 2005 is also covered)
https://msdn.microsoft.com/en-us/library/ms143393.aspx
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2016 at 9:01 am
If you are moving to 2016, it will be two phase migration, from 2008R2 - > 2014 and then from 2014 -> 2016. At most you upgrade to 2 versions.
Not according to Microsoft's own documentation:
SQL Server 2016 supports upgrade from the following versions of SQL Server:
SQL Server 2008 SP3 or later
SQL Server 2008 R2 SP2 or later
SQL Server 2012 SP1 or later
SQL Server 2014 or later
Supported Version and Edition Upgrades
Joie Andrew
"Since 1982"
February 1, 2016 at 9:36 am
SQL 2005 direct upgrade is not supported. You can attach SQL 2005 data files to 2016 according to the docs.
There is also a note that 32->64 is not supported, so be sure your 2008 R2 is x64. Then you can upgrade.
My vote is go with 2016.
February 1, 2016 at 9:48 am
Steve Jones - SSC Editor (2/1/2016)
SQL 2005 direct upgrade is not supported. You can attach SQL 2005 data files to 2016 according to the docs.
Yes this is detailed in the KB link I posted
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 2, 2016 at 8:03 am
Perry Whittle (2/1/2016)
curious_sqldba (2/1/2016)
If you are moving to 2016, it will be two phase migration, from 2008R2 - > 2014 and then from 2014 -> 2016. At most you upgrade to 2 versions.SQL Server supports upgrade from sql 2008 to sql server 2016, more info here (sql 2005 is also covered)
Very interesting, during PASS 2015 a very senior and well know MVP told us that you could upgrade only up to 2 editions. I couldn't find the date on the article to see if it is a recent one. Anyways, thanks for the info, good to know. I guess you wont be able to change the compatibility level though right?
February 2, 2016 at 8:27 am
I've created a VM with Windows Server 2016 and created a SQL 2016 failover cluster with another VM... I'm currently in the process of copying a full backup of our database so I'll let you guys know how that goes. Hopefully, I can upgrade directly to SQL 2016.
So once I restore the backup, I can just change the compatibility to 130 and that should be it, right?
February 2, 2016 at 9:19 am
curious_sqldba (2/2/2016)
Very interesting, during PASS 2015 a very senior and well know MVP told us that you could upgrade only up to 2 editions.
Replication has a 2-version limit. Upgrades, not so much.
Personally I don't usually do in-place upgrades. They break more often than I like.
I guess you wont be able to change the compatibility level though right?
Huh?
SQL 2016 supports, afaik, compat modes from 100 up to 130 (4 in total), so upgrading from 2008 R2 will have no problems staying in compat mode 100 if he chooses, or moving to a more recent one
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2016 at 9:20 am
dajonx (2/2/2016)
So once I restore the backup, I can just change the compatibility to 130 and that should be it, right?
Should be it to start...
and starting the testing now (on the CTP), and you MUST test. There have been too many core changes between 2008 R2 and 2016 (especially the cardinality estimator) to wing it and hope.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2016 at 9:26 am
GilaMonster (2/2/2016)
curious_sqldba (2/2/2016)
Very interesting, during PASS 2015 a very senior and well know MVP told us that you could upgrade only up to 2 editions.Replication has a 2-version limit. Upgrades, not so much.
Personally I don't usually do in-place upgrades. They break more often than I like.
I guess you wont be able to change the compatibility level though right?
Huh?
SQL 2016 supports, afaik, compat modes from 100 up to 130 (4 in total), so upgrading from 2008 R2 will have no problems staying in compat mode 100 if he chooses, or moving to a more recent one
I meant can i restore a 2008R2 back up and change the compat mode to 130.
February 3, 2016 at 7:32 am
Perry Whittle (2/1/2016)
curious_sqldba (2/1/2016)
If you are moving to 2016, it will be two phase migration, from 2008R2 - > 2014 and then from 2014 -> 2016. At most you upgrade to 2 versions.SQL Server supports upgrade from sql 2008 to sql server 2016, more info here (sql 2005 is also covered)
The link addresses the in-place upgrade of the SQL instance, but doesn't specifically say what to expect as for as the compatibility mode of any databases found during the upgrade. Two versions has always been the rule, for any released SQL versions.
I wouldn't make such plans until MS specifically states that three or more versions of compatibility mode can be leapfrogged, or until it can be tested with 2016 RTM (not a preview).
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
February 3, 2016 at 8:08 am
Mike Hinds (2/3/2016)
Perry Whittle (2/1/2016)
curious_sqldba (2/1/2016)
If you are moving to 2016, it will be two phase migration, from 2008R2 - > 2014 and then from 2014 -> 2016. At most you upgrade to 2 versions.SQL Server supports upgrade from sql 2008 to sql server 2016, more info here (sql 2005 is also covered)
The link addresses the in-place upgrade of the SQL instance, but doesn't specifically say what to expect as for as the compatibility mode of any databases found during the upgrade.
Hmm, Microsoft supports upgrades along the paths specified in the KB link, what more do you want?
Mike Hinds (2/3/2016)
Two versions has always been the rule, for any released SQL versions.
Might be your rule but it's not Microsoft's
Mike Hinds (2/3/2016)
I wouldn't make such plans until MS specifically states that three or more versions of compatibility mode can be leapfrogged, or until it can be tested with 2016 RTM (not a preview).
That is, of course, your prerogative
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 3, 2016 at 8:22 am
... and, I'm wrong.
The link does in fact have the statement I was looking for, "When a SQL Server 2005 database is upgraded to SQL Server 2016, the database compatibility level will be changed from 90 to 100. (In SQL Server 2016, valid values for the database compatibility level are 100, 110, 120, and 130.)"
So, MS is on the record supporting compatibility with 2008 ("100") and an automatic upgrade from 2005 during the upgrade-in-place. This appears to be "safe".
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply