July 21, 2017 at 7:40 pm
Hi,
I have a third party app with an accompanying SQL Server database back end, and I'm trying to determine, when I perform various functions within the app, which tables are affected and how they're affected. What would be the best (easiest?) way to do that?
Is this a good case for Profile/Extended events? Should I take a backup, perform the function in the app, then use a tool like SQL Compare to see what changed? CDC? Change Tracking? Use database snapshots to some extent?
I know this is probably a dumb question, but I honestly just don't do as much database administration as I do development and query tuning. Also, I should note that this is a completely isolated database that wouldn't have any other traffic--only what I'm doing in the app at the time.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
July 24, 2017 at 6:26 am
I'm extended events are lovely, but I'm a dinosaur and would use Profiler tracing for this..specifying filters for the databaseID, and whatever else you can narrow it by. Turn off trace items you don't need, such as login audits, connections, etc.
This is what I do when I get told "the app is slow, tune it" and I had no hand in developing the app.
Plenty of places to learn more about tracing all over the web that can do a better job than I can here 🙂
Good luck!
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
July 24, 2017 at 8:43 am
Thanks, Kevin,
And would this allow me to see the net effect of the queries issued by the third-party app? So, for example, LastName in the Person table was "Smith" and now it's "Jones". Also, a record was added to a log table, etc.
Or are you suggesting that I would derive that before and after information by capturing the queries themselves with Profiler or Extended Events?
Mike Scalise, PMP
https://www.michaelscalise.com
July 24, 2017 at 9:38 am
Tracing will allow you to capture the statements that were sent to the Query Engine. If you want to see data changes you need something like SQL Server Data Tools or Red Gate SQL Data Compare if you don't want to make database changes. Logging old data post change is quite a bit more involved using something like triggers, CDC, temporal tables (2014? Maybe 16?), etc.
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply