February 2, 2016 at 12:55 pm
I realize the subject line is somewhat unclear, so...
We're going to be migrating our servers in a double-whammy. We're moving to new servers with Windows Server 2012R2, and we're also going to be moving from SQL Server 2008R2 to SQL Server 2014 at the same time. Needless to say, we won't actually pull the trigger until customers have tested their apps and databases on the new SQL2014, so for a while we'll have both running at once on separate servers.
To try to reduce the pain of the migration, I've got an Extended Events session running on the production servers looking for and logging any deprecation announcements (deprecation_announcement and deprecation_final_support events to be exact.)
My question is, will this catch items that might be removed in SQL2014, or does this only show one major version up (IE SQL2012?) If it only finds one major version up, then we'll definitely have some checking to do before even contemplating migrating production. If it catches anything up to the newest version (IE SQL2014,) we'll still have checking to do, but it shouldn't be as bad.
Thanks all,
Jason A
February 2, 2016 at 1:12 pm
Deprecated means that Microsoft has announced that the feature "will be removed in a future version".
(And based on my experience with some deprecated features, the actual description should probably read "might be removed in a future version").
In most cases, a deprecated feature is kept in the product for two or three major releases as a "normal" feature, and after that is available only when you set the database to a lower compatibility mode. But there have been exceptions, both up and down.
So in theory it is possible that a feature that was not deprecated in 2008 is still gone in 2014 - but it is very unlikely, and I do not know any example of this.
On the other hands, a lot of features that are marked as deprecated will probably still work in 2014. Some might need a certain compatibility level, others might just still work completely.
Good luck on the migration!
February 2, 2016 at 1:26 pm
That's what I was seeing, but on the off-chance I were to run into a feature that's actually gone in 2014, I'm hoping to catch it. So far the XE session hasn't returned much in the way of final_support announcements (that would be a major headache,) so I'm reasonably confident this won't have too many headaches.
February 2, 2016 at 1:31 pm
Good, that's anice starting point.
Now you still have to do a lot of testing. Don't forget to do thorough stress testing as well, changes in the cardinality estimator are known to sometimes cause performance regression. There are very easy workarounds for this (various ways to force SQL Server to revert to the old cardinality estimator), so you want to discover these issues during test and fix them before going live. It can be very awkward to have to go to the executive panel and explain why the expensive three-times-better server and the even more expensive per-core-licensed server are giving them worst instead of better performance.
February 2, 2016 at 4:30 pm
Heh. We've got one dev team pestering for SQL 2014 because they want the new CE.
My current plan is to either leave the compatibility level of the DBs where they are now or move them to the lowest supported on SQL2014. I'm 99.9% sure none of the apps will break due to other changes from the compatibility level, but the CE is going to be one of those long, slow, switch-overs.
According to MSDN, if I don't set a database to level 120 (110 or lower,) then the engine will still use the old estimator, so I'll avoid any problems there. Once everyone is happy with being on SQL2014, I'll work with individual dev teams to test out the new estimator and start making that switch.
Loads of fun in the coming months...
:hehe:
February 2, 2016 at 8:03 pm
Jason, I don't know if you've worked with SQL 2012 R2 much, but let me tell you it's been great for me. While the metro interface takes some getting used to, it's just different. Things are there; you just have to find them again. Under the hood is where I think the real differences are. I didn't set up the server or the failover cluster part of it, but I did setup the SQL Server and create the SQL cluster. It was so much simpler than Windows 2008 that I couldn't believe it when I supposedly had it done. It worked flawlessly and is just plain fast.
I don't know if you need any help with the failover cluster, but I learned a lot from Perry Whittle's stairway series at http://www.sqlservercentral.com/stairway/112556/.
It sounds like you already know the first three rules of migration: test, test, test. 😉 Enjoy and have fun.
February 2, 2016 at 8:11 pm
I *love* Server 2012! Got two 2012 servers (well 3, but I shut #3 down to see if it's what drove up my electric bill recently,) in the basement, one with the full install, one just Hyper-V hosting a couple 2012 VMs.
So I'm looking forward to the migration.
As for clustering, I've been throwing the idea around, but with the headaches I get trying to get a measly 250GB drive added to a VM for Filestream, I don't want to deal with trying to get double the VMs. Plus, we're not a 24x7x364 shop, so while it is a pain if a VM goes down for some reason (blown VM host, OS craps out, etc,) it's not the end of the world.
Physical machines, if I had them, would be clustered, period.
February 3, 2016 at 1:57 am
jasona.work (2/2/2016)
According to MSDN, if I don't set a database to level 120 (110 or lower,) then the engine will still use the old estimator, so I'll avoid any problems there.
Not necessarily. I have one (potential) client who upgraded from 2012 to 2014 and got performance degradations with the old CE (ok, they upgraded hardware, OS version and SQL version in one go, sooo...)
Test First!
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 3, 2016 at 5:38 am
GilaMonster (2/3/2016)
jasona.work (2/2/2016)
According to MSDN, if I don't set a database to level 120 (110 or lower,) then the engine will still use the old estimator, so I'll avoid any problems there.Not necessarily. I have one (potential) client who upgraded from 2012 to 2014 and got performance degradations with the old CE (ok, they upgraded hardware, OS version and SQL version in one go, sooo...)
Test First!
Aanndd it's a good thing I haven't sent my e-mail to the dev teams yet about this. One more item to add to it, a warning about the new CE and that it MAY impact their queries performance even if their DB compatibility level is set to 110 or lower, so they'll really, really need to test in QA.
Thanks Gail!
February 3, 2016 at 5:41 am
jasona.work (2/3/2016)
One more item to add to it, a warning about the new CE and that it MAY impact their queries performance even if their DB compatibility level is set to 110 or lower
No. The new CE only applies when compat mode is 120 or higher.
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 3, 2016 at 6:14 am
GilaMonster (2/3/2016)
jasona.work (2/3/2016)
One more item to add to it, a warning about the new CE and that it MAY impact their queries performance even if their DB compatibility level is set to 110 or lowerNo. The new CE only applies when compat mode is 120 or higher.
Ah, OK, I misunderstood. I had read it as even with the CE "off" by using a lower compat setting, there could still be impacts.
Re-reading your post, now I gotcha. They did (are doing) the same thing I am, and had problems. Possibly from the change in hardware / OS / SQL.
It's early, I'm still working on my coffee...
:hehe:
(Good thing I'm still working on my announcement e-mail)
February 9, 2016 at 5:21 am
My advice: Set compatibility mode to 120 for all DBs until you find a CE problem. You should be doing a lot of testing as part of your move to SQL2014 regardless of DB compatibility mode, so there is no reason why this testing should not be using all the features of the new SQL version.
If you have a problem during testing, then troubleshoot it as normal. If it is a CE problem, then decide how you will address the problem - either live with it, adjust SQL code or indexes, etc to overcome it, or last of all lower the compat mode. If you do lower the compat mode, then re-test everything that accesses that database to see if anything new is affected.
The most time-efficient and risk-efficient place to test out improvements to existing features is when you are upgrading. You get a budget allowance for testing at this time so make the most of it. You will struggle to get budget at a later date to spend time testing improvements like CE.
FWIW my old place were very much into automated testing so regression testing of new SQL versions was standard. We often found a small number of SQL statements that ran significantly slower in the new version of SQL, as well as a much larger number that ran faster. Sometimes we fixed the SQL before going live, more often we lived with the problem and added it to the Technical Debt queue, but we never took the view we should not fully exploit the new SQL version.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply