Uncovering ERP Secrets with SQL Profiler
The simplest starting point for reverse-engineering an ERP process is to run that process while tracing the activity with Microsoft’s SQL Profiler. This is, of course, assuming the system is based on SQL Server, as many of them are.
The profiler is part of Microsoft SQL Server Management Studio, and is accessible from the Tools menu.
If you want to go down this path, the first thing to consider is that in order to gather useful information, it is likely that you will have to collect a LOT of information you are not going to be interested in. The job of filtering out this superfluous data is difficult enough if your process is the only one being traced. If everyone else’s work is being traced as well, you could end up with thousands of records within seconds.
Therefore, if at all possible you will want to run the process on a machine that no one else is using. That could be easier said than done if the ERP relies heavily on middleware. Failing machine isolation, you can rely on the user name to identify your process…that is, unless the ERP uses a generic user name to make some global requests, as some do.
I have found it useful to save a trace template with the settings I prefer. If you don’t know how to do that…find out! I’m not your mama.
Anyway, the trick is to reduce the amount of irrelevant information SQL Profiler spews at you. You will have to decide for yourself what you consider relevant. If you’re like me, information that seems relevant at first seems less important when you are drowning in megabytes of the stuff.
So, there are two questions you must ask: What SQL events do I want to see, and what information about these events do I want to see? Both can be configured from the “Select Events” tab of the trace properties window.
Really, the only events that I find useful are those that involve processing of actual SQL commands. It may be nice to know when this user or that has logged in or when transactions are committed or what errors are raised, but these details are more useful for debugging your own code, not hacking someone else’s. Getting inside another programmer’s head is helped along by following the execution path of database interactions, which can be done by looking at statements, stored procedures, and preparations as they are processed by the DBMS. Profiler allows the user to trace both the initiation and completion of these events, resulting in quite a bit of duplication. There are more duplications caused by the fact that stored procedures are also statements, and also must be prepared, so the same statements may appear as many as six times.
Why would we want this? Well, because SQL logic works much like machine-level processing anywhere in that you’ve got both data and an execution stack. Just looking at the duplicated statements linearly isn’t particularly helpful, but if you include nesting information in your event tracing, the sequence of events begins to come alive. By looking at nesting level, it is possible to zoom ahead through a stored procedure you’re not interested in by looking for a step down to the level at which the procedure was called. If the nesting level was 1 when the procedure was called, and you know the procedure is of no interest to you, then you can follow the nesting up to 2, 3, perhaps 4 or 5, then back down to 1, at which the procedure call will appear again in relation to the “end” event.
Another useful column to include in the event information is the object name. It is here that the procedure name, if any, will appear. This, in combination with the nest level, makes it clear where the execution path is headed.
Look at the trace shown below:
SQL Profiler Trace
You can see that just before the procedure “spMainProcedure” is called, there is no nest level. When it is called, the nest level is 1. When this procedure in turn calls “spInnerProcedure”, the nest level goes to 2. When this procedure completes, control goes back to the main procedure, and nest level drops to 1. When this procedure completes, the nest level goes away again, dropping to zero.
Here are the events I selected to get these results:
Profiler Event Selection Tab
SP: Started marks when a stored procedure is called, while SP:Completed marks when it completes. These can be thought of as opening and closing parentheses that encapsulate the operations within. SP:StatementStarted and SP:StatementCompleted will mark execution of statements within a stored procedure.
The TSQL events mark the beginning and end of SQL commands that are not part of a stored procedure. They will normally have a nest level of zero or none at all.
As you can see, the TextData field displays the actual SQL commands, while the other fields help place them in their proper contexts.
This methodology will prove to be very helpful to understand the inner workings of data-centric applications, and most ERP’s were written during a time in software evolution when data was everything. Best practices today attempt to reduce coupling between business logic and data persistence, but then, the concept of a monolithic ERP system isn’t exactly the epitomy of best practices today. Most, if not all, of the logic can be gleaned from an analysis of the database traffic patterns.
Caveats
There are caveats…Lord, are there ever caveats! Most importantly, it is critical to realize that the execution path taken by one trace isn’t necessarily the only possible one. There may be branching possibilities that will never be revealed no matter how many different real-life scenarios you collect data from.
Also, keep in mind that there may be hard-coded data that appears to materialize by magic in the course of a SQL trace. In this case it may not be possible to determine exactly how the information is arrived at, short of performing a decompile on the executables. This is especially true if there is encryption involved…the encryption/decryption keys are not likely to be sitting in the database for you to find!
This should be enough to get you started. Be ready for some tedious work, but be careful. That unimportant-looking little SQL statement you overlook may turn out to be a crucial one.
