October 21, 2020 at 4:34 pm
Hello
I have a real issue with the scripts that are being run on our systems - database.
Vendors pass various scripts through the Apps team so I have no problems with their legitimacy, but I have a real problem in keeping track of what was run, when, by who (which DBA). More often these scripts have no version numbers or identifiers, and we get asked about whether we ran certain script and we have no way of knowing exactly what it is being referred to.
I want to introduce a logging system - a bit like the MS SourceSafe but not quite.
Do you have similar challenges in your work and what do you do to overcome this, do you use a software product, if so what's available.
Thank you.
October 22, 2020 at 8:58 am
You need change control and your application needs a version table. Your application needs an about screen to show this info
The script should include the code to update the version table. A sql audit as an insert trigger on that table can catch the ip address and user id, record time
Do the scripts just update data or also do dml?
You could set up a job to schedule these after hours on an as needed basis.
October 24, 2020 at 9:17 pm
Hi
Thank you for the reply.
The apps all have their versions and we do have stringent change control. However, the scripts we get from suppliers don't always have identifiers, The Apps team and/or the business owners pass them to us from the suppliers. We, the DBA's have no record of what was run, on what, when, by whom, etc.
Ideally a some sort of a repository like the Visual Source Safe where we can store these scripts like a library and with notes and descriptions about them.
October 26, 2020 at 6:13 pm
To me, this is a process problem.
Management will need to support your efforts to work with the Development team, and the developers will need to agree to adopt an insert to your audit table every time they run an update. DBA can require they update and execute an insert that you've come up with as part of each release.
To make adoption easier, you may wish to create the audit table and stored procedure (or insert template) for them ahead of time and ask team leaders to help you present this new requirement.
Managers should work with you to verify that this statement is executed with every update, at least the first few times, and be willing to talk to developers who do not comply.
A single statement doesn't add a lot of overhead to any change and isn't unrealistic to require, in my opinion. DBA's have already given up a lot when vendors stop providing scripts to review ahead of time before deploying on their servers.
I'd suggest you create a separate ChangeLogs database for the organization rather than adding a table to each application's database. You can get as fancy as you want, but I'd want to keep it centralized.
The table/stored proc you create for them should be simple, flexible and allow inserts only. I'd provide a stored procedure rather than permissions directly on the audit table to developers or to service accounts, assuming they can add a call to the stored proc from deployment method is making application changes to your database.
I'd suggest you keep it as simple as possible and keep a close eye on it, at least until everyone has adopted this step into their process in some manner.
February 11, 2021 at 7:54 am
We have an SProc that we put (EXEC) at the front of all scripts. Of course that is only Scouts Honour that it will be included ...
PRINT 'Create procedure MySproc'
GO
EXEC dbo.MyLogScriptRun 'MySproc' ,'210211'
GO
-- DROP PROCEDURE dbo.MySproc
IF OBJECT_ID(N'[dbo].[MySproc]', N'P') IS NULL
EXEC ('CREATE PROC dbo.MySproc AS RAISERROR(''MySproc:stub version, full version not found'', 10, 1) WITH LOG, SETERROR RETURN -1')
GO
ALTER PROCEDURE dbo.MySproc
...
MyLogScriptRun takes parameters for Script Name (usually the same as the Object being created) and Version. We use "today's date" as the version, just to have some sort of roughly chronological encoding. Could be much more formal of course ...
If run without the version parameter eg.
EXEC dbo.MyLogScriptRun 'MySproc'
it returns information about MySProc - information from sys.objects regarding the Create / Modify date of the object, and a history from our LOG table (Date/Time, User, IP Address, Server etc.)
We also have a parameter to display information from our execution logs of when the SProc itself was run, and what parameters were passed to it and so on.
Also a "History of all recent script" ... you know ... for that never-ever-happens-event where you run some scripts on PRODUCTION instead of DEV and out of curiosity wonder what they were 🙂
MyLogScriptRun complains if the current environment is "unexpected". e.g. running in a transaction, not having the expected NOCOUNT / ARITHIGNORE, ANSI_NULLS, QUOTED_IDENTIFIER and loads of others. Particularly the ones that influence the environment that the SProc itself will run in ...
MyLogScriptRun doesn't provide a means of recording Success / Failure of attempting to create the OBJECT, but where
LOG DATE > OBJECT MODIFY DATE
then the outcome was "failure" 🙂 so we report on that, especially after running a rollout-script made up of the concatenation of many such individual scripts (each having its own MyLogScriptRun)
MyLogScriptRun also has the ability to check our Bugs Database, and list anything relevant there (handy when working on something to be able to consider other Tickets)
Separately, we also have a DLL LOG trigger
CREATE TRIGGER MyTriggerName
ON DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS
AS
...
this logs all sorts of DLL changes and has been very useful when we have had to either sort out an accident or point-a-finger at Who Dunnit
Both these need some sort of Stale Data Purge to stop the log tables growing indefinitely
January 10, 2022 at 8:59 pm
Hi all
Yes, this IS a process problem.
We don't develop in house. Our business partners from time to time provide scripts to the Apps team. Sometimes they are upgrades, other times they are bug fixes or data correction routines. We run these on our test platform first and often these scripts go back and forth several times before the supplier and the apps agree the results are as expected. Some times they refer to previous version but there is no version control. We have to look up the email and confirm by the date they were sent. There is no log or record of what they are and what the revisions are, etc.
This also creates issues within the DBA team because historically we don't know who ran which script and when.
Management thinks the solution is one that is going to cost money and allows to carry on as we do now. Heavy burden on the DBA's with little accountability.
Hirantha
January 10, 2022 at 9:38 pm
Just for your own sake - every script that you run should be saved in the file system, preferably a shared location. Include identifying information in the file name, for example - case/ticket number, username, description of what it does, etc.
In the script itself, if it changes data include the counts/results - or create a separate file with the results pane and save that with the script that was run.
Yes - it is manual and everyone needs to do the same thing, but if you have that you can answer those types of questions when they ask.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply