is there a way to "detect" schema like changes on a server?
-
Hi, we have a few people who like to experiment on our prod sql server with new tables, schema changes, dependency chain breaks, maybe ssis, even ssrs etc etc without telling anyone till its too late. They hold high ranking positions so forget your first thought. I often end up with technical debt or sheer embarrassment simply being the last person to know. Is there a way to detect db, ddl, sql agent , proc, view, pkg, rdl etc etc changes automatically in sql server? This could also compliment our foray into devops which will include db object. but im not necessarily high on the idea of waiting that long.
i guess i can see how to detect new rdl's easily. Maybe deleted rdls as well. and maybe the modified via the catalog's mod date.
-
I know you say to forget my first thought, but restrict access. Give them access to a test environment with close to live data so they can play around. My opinion - doesn't matter how high their ranking position is, if they are impacting production and you are taking the fall for it, restrict access.
As for detecting db, ddl, sql agent, proc, view, pkg, rdl, etc changes, there is always a way, but do you want to be bothered with it? SOMETHING needs to be scheduled to monitor various tables to watch for those changes. It may be a SQL monitoring solution, it may be an XE session, you could have a SQL job that does a count on sys.objects and emails you every 15 minutes to tell you how many objects exist in each DB. That last one is a bit of overkill; if you are going that route, I would recommend a stored procedure that writes to a table to say how many objects exist and the date/time it was run and returns the difference between the current and previous run and if it is big enough, sends out an email. Then set up a job to alert you on that. ALTERNATELY, almost ANY SQL Monitoring tool (Redgate Monitor for example; I don't work for them, just use their tool) can send out custom alerts, so you could have it count the number of objects in sys.objects and send an alert when the number increases.
You would need an XE session or something to monitor for changes vs create/drop operations mind you, and I expect anything you put in place would have an impact on system performance, so I'd be hesitant about that.
What it sounds to me like you need is a change management process. Work to implement that and get buy in from management and then those high ranking people will have no choice but to follow company policy. Our company has a policy that ALL code must undergo code review before it hits production. So nobody messes around randomly with production stuff or they risk getting written up or potentially terminated. Some departments even take it a step further and require proof of testing by someone other than the developer prior to go live. Policies and fear of termination usually get people to fall in line. Plus, for a lot of the different compliance certs (SOX, ISO, etc), change management may be required along with segregation of duties and often high ranking positions shouldn't be messing around in production databases...
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. -
Depending on the load of your instance/db, you could try to save the day creating a daily snapshot database and keeping that for a couple of days.
This may not cover it all !
Of course, having the needed backup regime in place is a minimum, but ...
Even having all the action data using XE, traces, ..., recovering the data at multiple points in time may be a huge task.
Best is to cover it all using decent SLA !
If an SLA be agreed upon, granted stuff must be removed.
Hand them an end use computing instance for them to mess around with, but keep your production safe !
I would suggest e.g. a daily refresh of the prod db to this EUC instance and have an extra db for them to mess with, having to use remote db queries to accomplish their needs.
You just cannot guarantee stability on a db where people just fool around with, without those people being responsible for their actions and suffer the consequences!
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
This can be done by creating database level triggers and can seriously be enhanced in capturing not only what changed when but by who from where.
I can't offer the code because the code from our company is proprietary but it does work. You just need to hit the books on such things.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Depending what you need you could look at the default trace.
It captures object:created object:altered object:deleted events, but it doesn't give you the T-SQL used to do the creation or alter.
You'd need something polling this data pretty frequently too as you only have 20/25MB of trace files allowed so you could roll over the data pretty quick.
It's not for everyone as it doesn't capture the textdata, which tells you what actually changed on the object, but if all you want to know is who is altering or creating or dropping things, then it's already built in to the default trace, just needs work to extract it.
It's a starter for 10 for most things, but obviously it's only going to capture things at the engine level, any IS/AS/RS based things or job changes, but with various triggers or jobs polling for tables wouldn't be to hard to knock something up. But for sure getting things into a repo is going to be one of the better things to be doing.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply