Hi All,
I would like to know the downsides of giving permissions to run traces in lower environments (DEV/QA/UAT). Our DB team has only two members: one supports PST hours, and the other supports IST hours. The PST resource is on leave for a month, so I have to cover both time zones as needed.
The issue is that the Dev team used to come to us when they needed to capture traces for slow-running queries or to capture workload data to send to the vendor for recommendations. Since this is a non-prod environment and they are doing it regularly, they are requesting permission to run traces themselves.
Should we grant this permission? If yes, should we limit it to 1 or 2 days and then revoke it?
Regards,
Sam
My opinion - a developer shouldn't need to run a trace. That's a thing a DBA should be doing. If the developer needs a trace run, the DBA should initiate the trace capturing the data the developer needs and then send it to the developer once it is complete.
My reasoning being that a trace lets you see everything being run on the database. So you can capture any and all queries being sent to the database. On top of this, there are performance impacts to running a trace and if you have multiple developers running traces simultaneously and they forget to stop them when they are done, your DEV/QA/UAT environments may start to have large performance impacts.
Now, since your team is tiny, what I would recommend is that IF the database is small, you send a backup with all PII removed to the developer and they can host it on their own machine and do whatever traces they need to do.
If the database is large, then your options are a lot more limited, but if you have the disk space, I'd look at granting each developer their own instance of the database so they can run traces on their own instance with minimal impact to other developers/QA/UAT. Well, unless you have a lot of developers.
BUT granting them the permission is really something you need to decide. Do you have time to start the traces for everyone who asks? If yes, then I'd keep doing things as you do now. If not, and you have no other options, then grant them access but monitor them to make sure they are not running traces for days on end. May not hurt to review any policies you may have in place on it too. My opinion - running a trace is a DBA duty and is not something I hand out to just anybody. But your situation and your company may require developers to be able to run traces in which case I'd say grant it to them. If there is no policy in place and you are comfortable with them lagging the system down and viewing ALL queries run against the database, then it is your call...
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.
July 5, 2024 at 6:17 am
Thank you. It makes a lot of sense. its a 7TB db and I asked them to reach out to DB Team.
Here, the dev team size is 48 members and DB team size is 2.
Many thanks again.
July 5, 2024 at 3:01 pm
I don't have any issues with allowing traces to be run in the DEV environment. If I had to wait for a DBA to do that for me, it would take 3 or 4 days and the DBA would get it wrong. Or, it would take a week to find a time to meet with the DBA and have him run the trace while I run the application that's having an issue. Our DBAs don't write any code, create tables create indexes, or tune queries.
July 5, 2024 at 3:42 pm
I don't have any issues with allowing traces to be run in the DEV environment. If I had to wait for a DBA to do that for me, it would take 3 or 4 days and the DBA would get it wrong. Or, it would take a week to find a time to meet with the DBA and have him run the trace while I run the application that's having an issue. Our DBAs don't write any code, create tables create indexes, or tune queries.
It depends on the data. If, for example, the database contains PII in a table called "personaddress" (not a good name, but just as an example) and you have no access to that. Then you see me (who does have access) run a query against that table where I am looking up the address by person name and by person address, you can now see the PII and MAY be able to put 2 and 2 together and figure out that I was testing if table/query contained the person and the address. I mean it is a bit of a stretch as it is a very specific scenario, but IF there is a chance that something could be PII AND that someone running the trace could see the PII (queries being run AND output parameters to stored procedures are captured in a trace, but query results are not as far as I know), I would want to make sure only the appropriate people have access to do a trace.
Now, with that being said, I wear a bunch of hats at my job. I'm an application administrator, application developer, database developer, and DBA (plus some other fun stuff), so I have access to do it all. But if the company had 2 DBA's and 48 developers asking for traces, I'd be handing it over to the developers. That's a lot for 2 people to manage. PLUS if I had to wait weeks for a DBA to run a trace for me, I'd be getting approval to get trace access myself. That's not a reasonable timeframe.
On the flip side of things - I rarely actually need to run traces on my systems as a developer. In the application I have access to all of the code I am working with, so I know the query I am running. I can load up SSMS or ADS and just run my query against the test database to see the results and timing and such. Really, the only reason I run a trace is if I can't reproduce the problem on my machine and I need the end user to run the thing against prod with the trace (need a DBA to do that trace as it is prod) OR someone is telling me that the application connection to the database isn't working and I can load a trace to see if the connection is valid or not. Otherwise all of my query tuning and query work is done in SSMS. Mind you that goes out the window if you are using a SQL helper like Entity Framework as then you don't have as much visibility to the underlying SQL being run on the system and I can see how profiler would help. But my team doesn't use any "SQL helpers" - we use the SQL objects in our code and make stored procedure requests to the database so we can test our SQL code in SSMS/ADS and know it is good.
I do wonder what keeps your DBA's busy all day if they don't write any code and don't create tables or indexes and don't tune queries... I mean, all that's left is backup/restore (which should be automated) and new instance installs (which should not be required that frequently) and patches (which isn't really that much work either)... How does a trace take 3-4 days to get done and are you hiring any more DBA's as that sounds like a pretty relaxing job at your company 😛
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply