April 6, 2017 at 1:57 am
Hi guys,
I am planning to upgrade my SQL Server 2008r2 to SQL 2014. As a developer I want to know how, if any, will it impact any of the code base. As per my little research, Upgrade Advisor report will help me find out any such code which may break in new version. Is there any other way to check or any precautionary measures I should take as a DB developer ?
April 6, 2017 at 5:48 am
sqlenthu 89358 - Thursday, April 6, 2017 1:57 AMHi guys,
I am planning to upgrade my SQL Server 2008r2 to SQL 2014. As a developer I want to know how, if any, will it impact any of the code base. As per my little research, Upgrade Advisor report will help me find out any such code which may break in new version. Is there any other way to check or any precautionary measures I should take as a DB developer ?
Another thing that could help is to query sys.dm_os_performance_counters on the server you are upgrading to see if any deprecated features show up there. It's cumulative since server restart so that can limit things. SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name like 'SQLServer:deprecated%'
and cntr_type > 0
Sue
April 10, 2017 at 8:11 am
Thanks Sue. However I was looking for something to see if there can be any code breaks while going from SQL 2008 r2 to 2014. Any code which will need changes and are not supported going forward.
April 10, 2017 at 8:22 am
Why would you not want to use Upgrade Advisor? It does precisely what you asked for. Tell it what database, SSIS packages etc to look in, and it will look for things that may break. And if some or all of your workload is ad hoc, just run a trace on your live server (at a time that's busy enough to be representative but not so busy that everything grinds to a halt under the overhead) and have the Upgrade Advisor analyse that as well.
John
April 10, 2017 at 8:30 am
sqlenthu 89358 - Monday, April 10, 2017 8:11 AMThanks Sue. However I was looking for something to see if there can be any code breaks while going from SQL 2008 r2 to 2014. Any code which will need changes and are not supported going forward.
Yes, that is what the counters do with Deprecated events. If you are looking for more details, you could run a server side trace and capture the Deprecation Events. I would capture the Announcement as well due to the jump in versions.
Sue
April 10, 2017 at 10:42 am
sqlenthu 89358 - Thursday, April 6, 2017 1:57 AMHi guys,
I am planning to upgrade my SQL Server 2008r2 to SQL 2014. As a developer I want to know how, if any, will it impact any of the code base. As per my little research, Upgrade Advisor report will help me find out any such code which may break in new version. Is there any other way to check or any precautionary measures I should take as a DB developer ?
I went through such an upgrade last year, and for me the biggest problem was the new cardinality estimator in SQL Server 2014. Make sure your database queries and code is well tested on 2014 before you do the upgrade of the production environment, to make sure you don't have performance issues.
https://sqlperformance.com/2013/12/t-sql-queries/a-first-look-at-the-new-sql-server-cardinality-estimator
Here's some other general advice on the upgrade to 2014:
https://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
April 11, 2017 at 8:00 am
Chris Harshman - Monday, April 10, 2017 10:42 AMsqlenthu 89358 - Thursday, April 6, 2017 1:57 AMHi guys,
I am planning to upgrade my SQL Server 2008r2 to SQL 2014. As a developer I want to know how, if any, will it impact any of the code base. As per my little research, Upgrade Advisor report will help me find out any such code which may break in new version. Is there any other way to check or any precautionary measures I should take as a DB developer ?I went through such an upgrade last year, and for me the biggest problem was the new cardinality estimator in SQL Server 2014. Make sure your database queries and code is well tested on 2014 before you do the upgrade of the production environment, to make sure you don't have performance issues.
https://sqlperformance.com/2013/12/t-sql-queries/a-first-look-at-the-new-sql-server-cardinality-estimatorHere's some other general advice on the upgrade to 2014:
https://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
Thank you for this info. Does this apply to a SQL 2012 to SQL 2014 upgrade, where some of the dbs are in SQL 2008 R2 compatibility level?
Thanks again.
- webunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 11, 2017 at 10:08 am
webrunner - Tuesday, April 11, 2017 8:00 AMThank you for this info. Does this apply to a SQL 2012 to SQL 2014 upgrade, where some of the dbs are in SQL 2008 R2 compatibility level?Thanks again.
- webunner
If you keep the databases in a lower compatibility level, then it effectively turns off the new cardinality estimator for the entire database. It is possible using TRACE flags or a query hint to control per query if it's using the new estimator or not:
https://www.brentozar.com/archive/2014/04/sql-2014-cardinality-estimator-eats-bad-tsql-breakfast/
April 18, 2017 at 2:29 pm
Chris Harshman - Monday, April 10, 2017 10:42 AMsqlenthu 89358 - Thursday, April 6, 2017 1:57 AMHi guys,
I am planning to upgrade my SQL Server 2008r2 to SQL 2014. As a developer I want to know how, if any, will it impact any of the code base. As per my little research, Upgrade Advisor report will help me find out any such code which may break in new version. Is there any other way to check or any precautionary measures I should take as a DB developer ?I went through such an upgrade last year, and for me the biggest problem was the new cardinality estimator in SQL Server 2014. Make sure your database queries and code is well tested on 2014 before you do the upgrade of the production environment, to make sure you don't have performance issues.
https://sqlperformance.com/2013/12/t-sql-queries/a-first-look-at-the-new-sql-server-cardinality-estimatorHere's some other general advice on the upgrade to 2014:
https://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
Hi Chris, how you handled the performance issues after moving to new SS 2014 servers ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply