March 5, 2014 at 9:09 am
Hello experts,
I am working on using the SQL Server 2012 Upgrade Advisor to analyze databases for an upgrade.
There is a step that includes check boxes for trace files and SQL batch files. My question is, how can I go about creating appropriate traces or batch files to make use of this step? I'm comfortable creating traces in general, just not sure what to trace to make the most of what Upgrade Advisor is checking.
Thanks for any help.
- webrunner
-------------------
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
March 5, 2014 at 9:48 am
Try to run a trace during the busiest part of the day\season so that you could capture most of your business queries. That can help you in identifying any deprecated\discontinued features being used in the code.
--
SQLBuddy
March 5, 2014 at 10:22 am
I'm pretty sure it has the same requirements as distributed replay. You can see the events and columns you'll need to capture here.
"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
March 5, 2014 at 12:44 pm
Many thanks, sqlbuddy and Grant!
This is very helpful information.
- webrunner
-------------------
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
March 10, 2014 at 8:43 am
I've tested out the replay trace and have a couple of follow-up questions I hope you can help me with.
1. The trace (TQL Replay) template that has the settings generates a huge amount of data. Is there any way to reduce that or control it?
2. Is there a recommended range of time to run the trace, given that it does collect so much data, but also needs to collect the necessary data to yield meaningful results? I tried running the trace for 15 minutes and got about 600 MB of trace logs, but only a few upgrade issues. Maybe I am lucky and there isn't that much SQL code to update, but my pessimistic side doubts that.
Thanks again for any help!
- webrunner
-------------------
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
March 10, 2014 at 9:20 am
webrunner (3/10/2014)
I've tested out the replay trace and have a couple of follow-up questions I hope you can help me with.1. The trace (TQL Replay) template that has the settings generates a huge amount of data. Is there any way to reduce that or control it?
2. Is there a recommended range of time to run the trace, given that it does collect so much data, but also needs to collect the necessary data to yield meaningful results? I tried running the trace for 15 minutes and got about 600 MB of trace logs, but only a few upgrade issues. Maybe I am lucky and there isn't that much SQL code to update, but my pessimistic side doubts that.
Thanks again for any help!
- webrunner
1. Go to the Events Selection Tab and remove the events that you don't need. Also Uncheck the columns that you don't need for gathering the details.
Mostly you will be interested in the Text Data and few others, not all.
Also instead of collecting data for all the DBs and Logins, just select only the DBs that you need (Excluding system DBs) and only the Logins you need.
You can just add the Application Login in the filter or exclude DBA logins and Service accounts.
Later if you feel enough data has not been collected, you can add few more events\columns.
2. Try to find the busiest part of the day say 8 AM-12 PM and run it. It's recommended to do at least 8-5 PM. But if you follow point 1 above, this should not be a problem.
Since your goal is to find deprecated features, you can use the Tuning Template too, which will let you collect only minimal data.
Check this book on Profiler ..
https://www.simple-talk.com/books/sql-books/mastering-sql-server-profiler/
--
SQLBuddy
March 10, 2014 at 10:05 am
There's a minimal number of columns that you must have for the replay mechanism to work. And yeah, it collects a lot of data. Only way to deal with it is to be prepared to capture that much data. Filtering by database, if you can, will help, assuming your app only calls from and to the one database.
I'm in agreement on when to collect the data. During the worst parts of the day, or at least during the parts of day most representative of your normal to high operations tempo.
"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
March 10, 2014 at 10:35 am
Great, thanks again for all of your help!
- webrunner
-------------------
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
November 5, 2015 at 6:08 am
Hi,
I know this is an old thread but I am having the same question only regarding the CHECK of SQL Batch Files.
I filled/enabled the Path and Filename pointed to an old MSSQL2k0 DB creation script in Upgrade Advisor.
The Upgrade Advisor claimed after finish: All is OK.
But the SQL-Script was firstly not compatible to MSSQL2k12 and secondly I had (willful) inserted wrong lines with no SQL Statements in this script!
Thanks in advance
Thomas
November 5, 2015 at 9:16 am
Better to post this as a new question. the only people likely to see this are those who responded to the original question.
I've never tried the approach you've outlined, so I don't have useful feedback for you. Sorry.
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply