So you’re in a shiny new position and you’re building plans for your migration from SQL 2005 to 2008 when the question hits you.
What features are we currently using in our code that will break when we move to the new server?
Ohh, tough one right? Not really. Hopefully you’ve used the profiler before. With it, you can set up a trace and actually have it look for code executing against your server that uses code that’s been marked as deprecated. That’s what I’m going to walk through today.
First up, start SQL Server Profiler.
Then click File -> New Trace. You’ll be prompted to connect to your server. I’m going to connect to my production server, because I want to see actual code that’s being used that we need to fix.
Don’t worry, I’m not actually going to use the GUI to run the profile, I’m going to send it to the server to run. Sneaky, huh?
On the properties Screen, fill in a trace name. I used “DeprecatedFeatures.”
Leave the rest with their defaults, and hit the Events Selection tab.
In order to get all the possible events that you could run a profiler on Hit the “Show all events” check box. That should give you a complete list of profile events.
Once you have them all listed, scroll down the list until you see “Deprecation” I usually run this with all deprecation options turned on. I’ll narrow the events later when I’m analyzing the results of the profiler.
Now that you’ve selected your elements, hit the Run button.
As soon as the profiler starts, hit the stop button. We don’t want to run this trace from the client. At least I don’t wan’t to run this from the client.
Hit File -> Export -> Trace Definition. Choose a location to save the SQL file and save it. That’s all we needed profiler for. You can close it now.
For some reason you can’t automate a trace to write to a file. So, open the sql script you just created. You’ll need to update “InsertFileNameHere” to a path available from your server. In my case my scratch drive on the server is L: So I changed mine to “L:MSSQLtracesdeprecationTrace”.
If you were to run the script as is, it would run until it hits 5MB, then stop. If you want it to give you multiple files (or roll over to new files), you’ll have to change the second parameter of the sp_trace_create from a 0 to a 2. If you’re going to run this trace for a while, this might be a good idea.
The next option you might want to change before running this script is decide how long you want to run this trace. I’m going to run it for one hour. Before summarizing my findings, I might let this run for one day to get a more complete picture. But this is just a demo.
To set an hour runtime, declare a DATETIME variable just after the other variables are defined.
DECLARE @endtime AS DATETIME SET @endtime = DATEADD(H, 1, GETDATE())
Then, change the last parameter of the so_trace_create from NULL to @endtime. That tells the procedure to stop in one hour.
After you’ve run your trace, you’ll want to analyze the results. The easiest way to analyze these results is to import the trc file into a table, then query away.
Luckily Microsoft built a very easy way to read that file in.
SELECT *
INTO deprecationTrace
FROM fn_trace_gettable('E:MSSQL10_50.TWOK8R2MSSQLtest.trc', default)
Easy, but if you could write directly to the table, It’d be a bit easier!
Now that you’ve collected the events, Look in your table for items with EventClass in 125 or 126. Once you identify those, you’ll see what you’re using that will be deprecated. You can then look around that time stamp to see the exact queries called that are using the deprecated features. Armed with that knowledge you can build a list of techniques you need to change before moving your system over to 2008.
So. What’s going to break when you upgrade?