The best defense is a strong CHAINSAW SWORD!!!
There may come a time in your career when you are tasked with finding out how a 3rd party application functions because some 3rd parties have completely closed source applications, or better yet, APIs that perform poorly. Microsoft SQL Server Profiler can help you identify what tables are used, procedures and what data is inserted when functions are run.
I was recently given this task. We needed to import upwards of 400k records from Numara TrackIt 8 to an unnamed vendor we will call X Engine [XE]. The XE API posed 2 problems; speed is less than desirable and there is no way to import attachments. In this case, not being to import the attachments is the deal breaker. With over 5 years of historical ticket information, we needed to make sure that our clients did not receive any kind of service degradation due to not being able to look up attachments would definitely cause some client frustration. I know right now I’m on hold because Staples can’t find an attachment of a receipt that I emailed them in previously…and it’s kind of pissing me off (not to mention their extremely rude, and absolutely non personable customer service reps).
I’m not going to go into the fluff about profiler and tracing, you can read about profile here http://technet.microsoft.com/en-us/library/ms181091.aspx and you can google tracing if you need to. I’m simply presenting a great use for the tool.
So where do we start? Well, the goal is to get data from one system to another, so knowing how and what is inserted would be a great place to begin. But how can we get this information? There are probably some great 3rd party tools that will provide documentation of the database, this “could” get you close, but I like to dive right in and get dirty. The first function I wanted to look at is how records are cleanly inserted into the XE database. So I crack open management studio, hit tools and call up my homey SQL Server Profiler.
I opened up my browser and went to the ticket creation screen in XE. I loaded the default template in profiler. Assuming that no one else is testing the system at the same time I am trying to trace (if you can control this variable, then do it. Kick everyone out of the system), I begin the trace. I switch back to XE and submit a ticket, right away profiler starts filling up with all kinds of useful information. In this particular case I was presented with about 8 sequential insert statements and their values. Armed with this information I made a mapping between the values I inputted into the form, and the values being inserted into the database. In order to really understand what was going on and make sense of it, I had to look at the columns in each table and figure out what they meant, and what their function was. For example, during one of the inserts a bit column is set, the column name was something like isActive, and the table was something like WOQueue. Makes sense right?
After dissecting what the application was doing behind the scenes, the next step is to try and do the insertion manually through TSQL. Here you can basically take all the information that was given to you by profiler, change some of the values so that they are meaningful in helping you determine their function (for example, what if I set the isActive bit to false? What happens in the application?), and fire off the code. I ran through this process quite a few times in order to see what was happening behind the scenes. During this process I would check the interface and make sure everything was ok. When I had set the isActive bit to false, the work order wouldn’t appear in the queue, regardless of it’s status, the isActive bit had to be set to true in order to see it (this made no sense to me).
I’m hoping by now you can see the value of using profiler to help you navigate through uncharted territory. Using tools that should be at hand, you can get a handle and gain knowledge on how applications work behind the scenes.