Using triggers to track Stored Procedure Changes or Options?

Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
32,831
Reaction score
3,034
Location
On the toilet
Hi all,

As the title states. We need to implement some sort of source control on our SQL environments and as it's end of the year and a bunch of other operational issue we need a interim solution.
Research seems that the interwebs are split between using triggers for anything other than tables changes while the other half says it's decent for a start.

Your thoughts and things I should be wary of?
Needs to be implemented in the next 2-3 weeks and will be tested till next years production cycle begins in early Jan.

Because some people are going to mention buying something, May/June 2015 is the only realistic date this would ever happen and with 9 devs working on the DB at the same time.. You can understand my problem I hope :)

Help me please :)

EDIT : See VS2012 server tools allows you to make a backup of the schema and objects into TFS. Lemme go down this rabbit hole.
EDIT 2 : Server is SQL 2008 R2.
 
Ok. is this a production server?
If so - get your 9 devs the hell off a live server! That is a recipe for disaster on an biblical scale. I'm speaking from experience.
Start cutting entry to production - I know it s hassle, but it is the only way to be sure.

If it is a dev - then use a tool like Beyond Comapre or SQL Delta to compare the stored procedures with a benchmark, like production.
Its not optimal - but fit for purpose in your scenario.
 
Compton_effect said:
Ok. is this a production server?
If so - get your 9 devs the hell off a live server! That is a recipe for disaster on an biblical scale. I'm speaking from experience.
Start cutting entry to production - I know it s hassle, but it is the only way to be sure.

If it is a dev - then use a tool like Beyond Comapre or SQL Delta to compare the stored procedures with a benchmark, like production.
Its not optimal - but fit for purpose in your scenario.
Click to expand...

What about dev ops ?

Its my job to change the SP's ;P


I think we use 2012 and triggers. Its worth the jump to 2012.

We get a report with the changes if anything happens. I think you might be able to do the same thing with an audit ?
 
Yeah.. you can use DDL triggers...

But rather implement proper versioning systems and source control at the dev level and limit access to the production systems, its a much safer solution.
 
ToxicBunny said:
Yeah.. you can use DDL triggers...

But rather implement proper versioning systems and source control at the dev level and limit access to the production systems, its a much safer solution.
Click to expand...

Why do you think I've never used it? ;) A tracking process is a quick fix for something that should be handled at procedural level. Limited access to Production, code review and source control. Its a schlep, but the first time a junior dev deletes a database the day before UAT ends...
(True story - happened 2 weeks after I had resigned. They actually phoned me to ask if I have a copy of the DB)
 
Last edited:
Nerfherder said:
What about dev ops ?

Its my job to change the SP's ;P

I think we use 2012 and triggers. Its worth the jump to 2012.

We get a report with the changes if anything happens. I think you might be able to do the same thing with an audit ?
Click to expand...

Compton_effect said:
Same here with 2012.
Here's one other solution I know of, but have never used. http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
Click to expand...

Not possible to change to 2012 any time soon :(
 
Compton_effect said:
Why do you think I've never used it? ;) A tracking process is a quick fix for something that should be handled at procedural level. Limited access to Production, code review and source control. Its a schlep, but the first time a junior dev deletes a database the day before UAT ends...
(True story - happened 2 weeks after I had resigned. They actually phoned me to ask if I have a copy of the DB)
Click to expand...

This is why I've never let dev's anywhere NEAR my production servers :)
 
Compton_effect said:
Ok. is this a production server?
If so - get your 9 devs the hell off a live server! That is a recipe for disaster on an biblical scale. I'm speaking from experience.
Start cutting entry to production - I know it s hassle, but it is the only way to be sure.

If it is a dev - then use a tool like Beyond Comapre or SQL Delta to compare the stored procedures with a benchmark, like production.
Its not optimal - but fit for purpose in your scenario.
Click to expand...

ToxicBunny said:
Yeah.. you can use DDL triggers...

But rather implement proper versioning systems and source control at the dev level and limit access to the production systems, its a much safer solution.
Click to expand...

Ok some deeper explanation is needed.

We use 4 enviroments. Dev -> DevQA -> ProdQA -> Prod
Now our ProdQA box was given away for a month a bit and now we have different versions floating about version wise across 4 servers.
Only 2 devs (me and a senior are allowed to deploy to Prod).

The other devs use Dev and DevQA(even though this is banned in the process).
No one is jotting down there code changes so when we need to deploy items to Prod or ProdQA we have a major problem and a 10 minute deployment becomes a 4 hour process.

Dev process is still being worked on and while the other devs have agreed to follow the processes we do have in place, management isn't attending to the problem children that want to be gung-ho.

A trigger or some sort of auditing tracker will assist me greatly when it comes to deployments.

Hope this clears up a couple of things.
 
Compton_effect said:
Why do you think I've never used it? ;) A tracking process is a quick fix for something that should be handled at procedural level. Limited access to Production, code review and source control. Its a schlep, but the first time a junior dev deletes a database the day before UAT ends...
(True story - happened 2 weeks after I had resigned. They actually phoned me to ask if I have a copy of the DB)
Click to expand...

Lolz, I was once that junior :o
This is a new dev team and while we are trying to get processes in place, we also have products to pump out so it is a slow and mildly painful process.
 
Compton_effect said:
Why do you think I've never used it? ;) A tracking process is a quick fix for something that should be handled at procedural level. Limited access to Production, code review and source control. Its a schlep, but the first time a junior dev deletes a database the day before UAT ends...
(True story - happened 2 weeks after I had resigned. They actually phoned me to ask if I have a copy of the DB)
Click to expand...
Your database didn't do hourly backups? :confused:
 
Compton_effect said:
Its a schlep, but the first time a junior dev deletes a database the day before UAT ends...
(True story - happened 2 weeks after I had resigned. They actually phoned me to ask if I have a copy of the DB)
Click to expand...

I've had DBAs do similar things, twice! One where I even put into a stupid change request you have to painstakingly fill in, back up db1, back up db2, restore backup of db1 over db2. Then they go back up db2 and restore over db1... Ffs, "OK just restore latest nightly backup of both then do it again the right way around" "sorry we don't take backups on dev/uat server".
 
rorz0r said:
I've had DBAs do similar things, twice! One where I even put into a stupid change request you have to painstakingly fill in, back up db1, back up db2, restore backup of db1 over db2. Then they go back up db2 and restore over db1... Ffs, "OK just restore latest nightly backup of both then do it again the right way around" "sorry we don't take backups on dev/uat server".
Click to expand...

Tell them the impact of the change is over 200 users. Only way to get around that bureaucratic rubbish.
 
Pho3nix said:
Our production boxes are done daily.
Click to expand...

I wasnt asking you ;) . Only daily? Ouch. Ours are done every 15mins even that is to long. But backing up 500gb a shot is a bit space consuming, we also have the database replicating to 3 different locations.
 
semaphore said:
I wasnt asking you ;) . Only daily? Ouch. Ours are done every 15mins even that is to long. But backing up 500gb a shot is a bit space consuming, we also have the database replicating to 3 different locations.
Click to expand...

:p Only a few systems are done that frequently were I am at. No replication though if memory serves
Still need to pop you that PM ;)
 
Pho3nix said:
:p Only a few systems are done that frequently were I am at. No replication though if memory serves
Still need to pop you that PM ;)
Click to expand...

We snapshot a lot into a redis cache for application access, our client facing apps only ever get snapshot data (refreshed every 5mins based on frequency of access). Our core crunchers are what molest the database to hell and back.

Fire away.
 
Compton_effect said:
Ok. is this a production server?
If so - get your 9 devs the hell off a live server! That is a recipe for disaster on an biblical scale. I'm speaking from experience.
Start cutting entry to production - I know it s hassle, but it is the only way to be sure.

If it is a dev - then use a tool like Beyond Comapre or SQL Delta to compare the stored procedures with a benchmark, like production.
Its not optimal - but fit for purpose in your scenario.
Click to expand...

Any developer with half a brain will avoid even knowing the password for production. When things go tits up the first question is "Who had access?"

We have a release process where we can only release changes through a specific user account and that account only allows access to run execute scripts in the staging area. We cannot place anything in the staging area. Our release software moves the code to the staging area and only code signed off after mirror testing is moved to the staging area. In situations where a developer needs to log into the database directly, they need to sign for their account to be unlocked and privileges are granted by the DBA. Once the job is done, privileges are reset and the account locked. I think I have needed to do this twice in the last 4 years.

It might sound like a painful process, but we have been able to do urgent fixes within 15mins by following the strict release process. The issues are usually getting sign-off. But at least its all documented and audited. A side benefit of this that everything we release though the process, also gets released to our clone environment. Our devs and uat can reclone their environments in about 15mins bring it in sync with production at anytime. Without all the data obviously. We have 5 mirrors for that. We have about 50 dev and 85 uat environments.

There is absolutely no reason any dev should ever be poking about in production. That is pure amateur hour practice.
 
Pho3nix said:
Ok some deeper explanation is needed.

We use 4 enviroments. Dev -> DevQA -> ProdQA -> Prod
Now our ProdQA box was given away for a month a bit and now we have different versions floating about version wise across 4 servers.
Only 2 devs (me and a senior are allowed to deploy to Prod).

The other devs use Dev and DevQA(even though this is banned in the process).
No one is jotting down there code changes so when we need to deploy items to Prod or ProdQA we have a major problem and a 10 minute deployment becomes a 4 hour process.

Dev process is still being worked on and while the other devs have agreed to follow the processes we do have in place, management isn't attending to the problem children that want to be gung-ho.

A trigger or some sort of auditing tracker will assist me greatly when it comes to deployments.

Hope this clears up a couple of things.
Click to expand...

There is tool called DB compare that might help you.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X