March 14, 2023 at 1:28 am
We get a bunch of tasks/requests to run t-sql scripts to update views or stored procs or bad data.
Does anyone know of a server product that can automate the execution of these t-sql and do full logging and error reporting of execution and email the requesting user of the error?
Basically a server application where the developer can submit the request and the server will be delegated the ddl/dml rights and will do it for us.
Naturally we would control what access to what db the service has the rights to.
Thanks
March 14, 2023 at 7:01 am
https://ola.hallengren.com/downloads.html - these has maintenance data scripts.
Others you have to use Audit, XEvent Profiler etc based on your requirement
We get a bunch of tasks/requests to run t-sql scripts to update views or stored procs or bad data.
Does anyone know of a server product that can automate the execution of these t-sql and do full logging and error reporting of execution and email the requesting user of the error?
Sprocs, Views or bad data( i believe wrong data) is completely dependent on application and it cant be compared to other App DBs, you have take this manually.
What we done in past is, asked the dev team to create a .bat file like program which executes with few inputs but runs 100s of files/scripts in order.
Regards
Durai Nagarajan
March 14, 2023 at 12:11 pm
Sounds a lot like you might be interested in building out a DevOps pipeline. You'll want to pick a flow control tool like AWS Developer Tools, Azure DevOps, Jenkins, Octopus, Github Actions, something. Then, you can work from source control to get the deployments done. Here's one example in a presentation. Here's another couple in a blog. There are tons of more examples. It's all about getting your data structures in and out of source control, which provides the basis for automation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 14, 2023 at 1:21 pm
https://ola.hallengren.com/downloads.html - these has maintenance data scripts.
Others you have to use Audit, XEvent Profiler etc based on your requirement
Warren Peace wrote:We get a bunch of tasks/requests to run t-sql scripts to update views or stored procs or bad data.
Does anyone know of a server product that can automate the execution of these t-sql and do full logging and error reporting of execution and email the requesting user of the error?
Sprocs, Views or bad data( i believe wrong data) is completely dependent on application and it cant be compared to other App DBs, you have take this manually.
What we done in past is, asked the dev team to create a .bat file like program which executes with few inputs but runs 100s of files/scripts in order.
There is nothing in Ola's scripts that will automate deployments of changes to database objects.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 14, 2023 at 3:42 pm
We get a bunch of tasks/requests to run t-sql scripts to update views or stored procs or bad data.
Does anyone know of a server product that can automate the execution of these t-sql and do full logging and error reporting of execution and email the requesting user of the error?
Basically a server application where the developer can submit the request and the server will be delegated the ddl/dml rights and will do it for us.
Naturally we would control what access to what db the service has the rights to.
Thanks
That sounds like you're saying that changes to production code will be automated so the Developers can make a change and it'll go directly to prod without a peer review, QA, and UAT.
If that's true, lemme tell you that's a really, really bad idea if if you need to pass audits on your deployment process, like SOC2, ISO, or SEC audits, that will be reason enough to fail the company.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2023 at 3:44 pm
Warren Peace wrote:We get a bunch of tasks/requests to run t-sql scripts to update views or stored procs or bad data.
Does anyone know of a server product that can automate the execution of these t-sql and do full logging and error reporting of execution and email the requesting user of the error?
Basically a server application where the developer can submit the request and the server will be delegated the ddl/dml rights and will do it for us.
Naturally we would control what access to what db the service has the rights to.
Thanks
That sounds like you're saying that changes to production code will be automated so the Developers can make a change and it'll go directly to prod without a peer review, QA, and UAT.
If that's true, lemme tell you that a really, really bad idea if if you need to pass audits on your deployment process, like SOC2, ISO, or SEC audits, that will be reason enough to fail the company.
Jeff's right. I should call out that the links I sent, show how to factor in automated testing, deployments to pre-production environments and more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 14, 2023 at 5:42 pm
Sorry, my fault. I failed to mention this will be just in the test/qa environment and not prod.
The Devops was mentioned by the developers but from what little I read, that wants way more rights than we want to delegate out. Admittedly I have not read up on all of it though.
March 14, 2023 at 6:21 pm
Thanks, for the feedback, Warren. I was worried for ya! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply