January 16, 2019 at 4:49 pm
Hi
I am looking for a tool that can look at a profile trace file of a 2008 server's activity and perform a test to ensure no code is incompatible with 2017 server
I have the latest Upgrade advice from MS this just checks the objects in the database such as SP, views, tables, functions etc. but does not check adhoc queries from applications.
Regards,
Kevin
Kevin
January 17, 2019 at 6:55 am
I'm not aware of a tool that does this.
One thing you can do though is to capture the trace, then use Distributed Replay to play it back against an upgraded copy of the database in a test environment. That will allow you to do what you want in as automated a way as possible. Just note that Distributed Replay, while it absolutely works, is a royal pain to get set up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2019 at 7:19 pm
For our upgrades from SQL2008r2 to SQL2016, we used Microsoft's SQL Server Database Experimentation Assistant:
https://blogs.msdn.microsoft.com/datamigration/2017/07/25/dea-2-1-general-availability-release-overview-database-experimentation-assistant/
This tool does exactly what you're after, using your actual workload.
There's an installer, but setting it up isn't painless. Read the docs carefully.
Once set up, you'll use it grab a trace of your production workload and a backup of the database. Restore that database to two places, preferrably on matching hardware: a SQL2008r2 instance, and a SQL2017 instance.
DEA will then use DReplay to replay the workload against both databases separately, taking a performance and error trace of each during the replay.
After the replay against both databases is complete, it will ReadTrace those perf-and-error traces into two more databases and compare them, command to command.
You'll get a detailed visual interactive report of the calls that threw an error, including comparing which calls (full SQL text included) only had an error in one of the databases (such as all the calls that failed in SQL2017), so you can see which errors are new, which ones currently exist that still error in SQL2016, and those errors that are solved by the upgrade, if any of each.
You will also get full details on the performance increase or decrease of each call that ran from the trace, so you can spot performance issues early.
Those reports come out of a simple results database it builds during the analysis, and you can easily query the data that shows on the report.
We use this for all of our upgrades and for testing each SQL patch as it comes out.
Eddie Wuerch
MCM: SQL
January 18, 2019 at 6:59 am
Thanks for mentioning Microsoft's SQL Server Database Experimentation Assistant. Didn't know the tool existed
January 18, 2019 at 7:22 am
Yeah, same here. Not surprised that it used Distributed Replay though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 21, 2019 at 9:34 pm
Kevin
Kevin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply