SQL 2012 to 2016 - Possible Application Issues

Question

SQL 2012 to 2016 - Possible Application Issues

Kenneth Igiri

SSCarpal Tunnel

Points: 4998
More actions
June 27, 2016 at 4:37 am

#317379

In upgrading from SQL Server 2012 to 2016, based on your experience, what are possible impacts the upgrade could have on an application that was built for SQL 2012? Please share any reference to documentation. Thanks so much.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 1

Just like any SQL Server version upgrade there could be many and there could be none. Run the Upgrade Advisor in a test environment to see if there are any gotchas.

Then, test, test, test the applications.

You are on the bleeding edge wanting to know if there are any gotchas in general. Remember, SQL 2016 was just released 30 days ago. Very few mid to large systems are going to be ready to upgrade right out of the shoot.

BLOB EATER SSChampion Points: 14609 More actions · Answer 2

Its probably even more important to test your queries as 2014 onwards they introduced a new QP so you might even see some regressions, I know I did.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

Br. Kenneth Igiri (6/27/2016)
In upgrading from SQL Server 2012 to 2016, based on your experience, what are possible impacts the upgrade could have on an application that was built for SQL 2012? Please share any reference to documentation. Thanks so much.

First, download and use the "Upgrade Advisor". It's not a panacea but it finds a bunch of things... even some that existed that should have been upgraded due to deprecation that may still be in effect.

The second thing is that if you're doing an "in place" upgrade, I recommend you start with your staging box. You can't live without your development box and you can't live without your production box but you can live without your UAT box for a bit if something goes wrong.

I recommend the same thing if you're doing a migration to new hardware but there's a fair bit more work in doing that especially if you have any of the SQL Server 4 letter words (SSIS, SSRS, SSAS, SSDT, etc) that must also be migrated. Full text migrations can be a bit testy, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Igiri SSCarpal Tunnel Points: 4998 More actions · Answer 4

Thanks a lot folks. Will heck out what Upgrade Advisor can tell me. And definitely we are testing but wanted some experience too since I believe some folk out there have been testing 2016 since the CTP versions cam out.

Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

Taranjit Singh-373811 SSC Veteran Points: 222 More actions · Answer 5

From SQL Server 2014 onwards , MS have changed Cardinality Estimator. This has improved some of the application, while degraded some of the queries drastically leading to roll back or changing the database compatibility to lower level. This may not get highlighted in Upgrade Advisor. So a thorough testing of application\Query performance is must.

http://sqlperformance.com/2013/12/t-sql-queries/a-first-look-at-the-new-sql-server-cardinality-estimator

Kenneth Igiri SSCarpal Tunnel Points: 4998 More actions · Answer 6

Taranjit Singh-373811 (7/17/2016)

From SQL Server 2014 onwards , MS have changed Cardinality Estimator. This has improved some of the application, while degraded some of the queries drastically leading to roll back or changing the database compatibility to lower level. This may not get highlighted in Upgrade Advisor. So a thorough testing of application\Query performance is must.

http://sqlperformance.com/2013/12/t-sql-queries/a-first-look-at-the-new-sql-server-cardinality-estimator%5B/quote%5D

Thanks so much. Will keep in mind.

Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising