December 13, 2021 at 9:37 pm
Hi all,
We have an app that currently requires Admins /Power users to navigate a difficult GUI, but clearly it's sending SQL to the DB. A first look at the underlying table shows the column names map perfectly to the fields in the form, but of course the table has multiple dependencies, so I'm hoping to capture the SQL via Extended Events and replicate the process programatically, thus hopefully eliminating the GUI.
Any tips (basic to advanced) welcome, TIA.
December 14, 2021 at 2:46 pm
Do you have access to the source code behind the GUI? If so, I would tackle it on the GUI source side of things NOT on the database side of things. The reason being - you don't know what logic the application layer is handling prior to inserting any data in the database.
BUT if you need to do it on the database side, using an XE (eXtended Events) session can capture queries. I would recommend capturing rpc_completed if the only thing you care about is the query that was run from the application. But you will be missing all of the logic the application is doing. And, unless the application is really just a fancy GUI for reading and writing to the database and does absolutely no logic, you are going to be missing key parts of the equation.
For example, if the application does any sanity checking prior to trying to insert data, which is something I bake into most applications I develop, that would be lost. Or if the application does any calculations prior to inserting data, such as currency exchange rates, that logic could be lost if you ONLY looked at the database side of things. Or if the application pulls data for all potential operations on form load, it may be hard to track down where it is getting some of the values from by simply looking at an XE session result. What I mean is if you had a form that required data from 10 different tables and then when you go to save it, it is only writing to 1 of those 10, the trace is going to show the pull from the 10 at the point the application asked for the data (form load, application load, on save, some other time) and the application may be handling all of the mappings between the tables. Likely isn't handling the mappings, but I've seen it done before for performance reasons. Memory is a LOT faster than network, so if you can pull all of the data in the background, transparent to the end user, the application will feel faster. Downside to that approach is as the data grows, the form load and application startup times decrease as you need to pull a lot more data into memory. And if it gets big enough, it won't fit in memory and now the application is useless.
On top of those concerns, there is also the chance that the application has features you currently are not using that you will miss when you remove/rewrite the GUI. Or features that only kick in in certain scenarios. Like end of month, end of quarter, or end of year type scenarios.
The above is just a SMALL sample of things that you could miss by trying to reverse engineer an application entirely from the database side. My opinion - it is likely going to be better (and safer) to reach out to the software vendor (if it is off the shelf solution) and work with them to improve their GUI. If it is a home-grown application, I would encourage you to review the code to see how it is working.
Lastly, if this is a 3rd party application (off the shelf solution), you will also want to review the license agreement. Taking any steps to reverse engineer a piece of software is USUALLY against the terms of service. By attempting to bypass their application and use your own thing, you may be in violation of the license and may even get into legal troubles.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 15, 2021 at 9:34 am
Firstly, I wish everyone were as explicative as you 🙂
I think the common view at this point is we've had enough of being chiselled by this vendor for consultancy on how to circumvent relatively simple operations that are frequently repeated and cost us 30 man-minutes per iteration, so there's been a buy-in to move from GUI to code. I'm hoping the EE trace will expose ALL code being run, I've already tested the session and it at first glance seems to show everything, though I will confirm this. I can say it won't be too difficult to strip out the unrelated operations and focus on what is needed.
I'm still on the lookout for any "Gotcha!"'s, ie tracking tables that like you said are only written to periodically, and there are half a dozen ancillary tables whose function I need to unpack before going live, but we are pressing on with this.
December 15, 2021 at 4:20 pm
Sometimes it is good having a lot of detail, sometimes people are looking for a "quick and easy" answer and my detail just gets skimmed or ignored.
The XE trace will expose all of the code being run against the database. It unfortunately won't show you any application level logic that is happening.
One thing that MAY be beneficial is rather than trying to reverse engineer their tool (which, like I said, may violate the license agreement and get you into legal trouble) would be to find out end user requirements and build a new and better system that meets those needs. What I mean is if the tool has 100 functions and your company uses 15 of those 100 functions, it may be easier to build a tool (or buy a different tool) that handles those 15 functions and maybe a few extra rather than reverse engineering the 100 functions and hoping you aren't missing anything important.
A few gotchas you may want to look out for are any automation related things like triggers on tables or any jobs that do work on the tables and any stuff that MAY be happening outside of that particular database. For example, our ERP system (3rd party tool, not home grown) will sometimes write directly to tempdb and then clean up when it is done. If I was only watching user databases, I would miss all the tempdb work. The tool also creates a TON of temporary stored procedures while it does its work which makes trying to decode what it is doing complicated. And most of the stored procedures created by the ERP are "protected" and are all single-line stored procedures. We do have SQL Prompt which will take that single line stored procedure and happily break it out into the 100-4000 lines it SHOULD be in for us so we can see what it is doing and work with it. Thankfully we are not going to be using that ERP for too much longer.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 31, 2021 at 5:20 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply