Using triggers to track Stored Procedure Changes or Options?

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...

I would suggest that you have separate dev environment for each developer. Or at least each team or each project.

You then also a need a clone environment. Everything that is released to production is released to the clone.

When you start a project, you allocate a dev environment to it. That dev environment is refreshed/rebuilt from the clone. This means that is in sync with production at the start. You then do your stuff and write some code. When done, you put your code back into your source control and you create an installation script. You then refresh your dev environment to get back in sync with production and then you merge your installation and update it so that it still works with production version. Then its ready for user testing. UAT start their testing.

When it gets signed off, you need to do another merge with live and further regression testing. Then you release to ProdQA, and test against live-like data. Ideally, you need to do this in one day. Its the last sanity check. If all is well, you schedule for release.

At release you do your back up of production. Release changes. Do some further tests to make sure you haven't broken production. If all is well. Job done. If not. Restore back up. Cry. :)

You need more environments :)
 
Put all your views, stored procs and triggers as create scripts in source control. Changes can be done in source control and thus tracked.Recreate scripts generate and run on db dropping those and recreating them. If you want to be cowboy and change directly in db skipping source control then be prepared to lose your changes.

From my experience this is how development companies do it. Use it or don't
 
Last edited:
zaozzie said:
Put all your views, stored procs and triggers as create scripts in source control. Changes can be done in source control and thus tracked.Recreate scripts generate and run on db dropping those and recreating them. If you want to be cowboy and change directly in db skipping source control then be prepared to lose your changes.

From my experience this is how development companies do it. Use it or don't
Click to expand...

This. I only do db metadata and data changes using scripts. NEVER clicky-clicky in your DB tool.

I have a stored proc that returns DB metadata version no. Every release it bumps the db metadata version no. Having all updates in scripts means that you can version control it and maintain multiple prod environments running diff release cycles. And recreate and empty DB.

Re triggers, they are from Satan himself. I only use triggers where they do not affect anything else except bumping a row version number on updates. NEVER for any logic whatsoever. Have been burnt too many times by code running in triggers and you always forget about that and cannot figure out why this process is taking so long. Turns out some idiot put some inefficient code in a trigger. Logic should be in one place.
 
I'd recommend a database project in vs.net 2012/13 into TFS. You can make a "backup" of the schema and all objects in there, work on it in a nice source controlled environment, and use it to deploy code changes to live (it compares live with TFS and shows you what changed). Also have the option to roll back changes to tables etc which is pretty nifty. plus if you already have the tools, nothing extra to buy :) just a mindset change from the dev's
 
Nerfherder said:
There is tool called DB compare that might help you.
Click to expand...
Used it and it's a great tool :)
zippy said:
I would suggest that you have separate dev environment for each developer. Or at least each team or each project.

You then also a need a clone environment. Everything that is released to production is released to the clone.

When you start a project, you allocate a dev environment to it. That dev environment is refreshed/rebuilt from the clone. This means that is in sync with production at the start. You then do your stuff and write some code. When done, you put your code back into your source control and you create an installation script. You then refresh your dev environment to get back in sync with production and then you merge your installation and update it so that it still works with production version. Then its ready for user testing. UAT start their testing.

When it gets signed off, you need to do another merge with live and further regression testing. Then you release to ProdQA, and test against live-like data. Ideally, you need to do this in one day. Its the last sanity check. If all is well, you schedule for release.

At release you do your back up of production. Release changes. Do some further tests to make sure you haven't broken production. If all is well. Job done. If not. Restore back up. Cry. :)

You need more environments :)
Click to expand...

You were right. New laptops(beefier) arrived for 4 of the dev's so they can run most of the code on their VM's moving forward.
Trying to arrange a user account with just read access for everyone but not sure how much this would impact their ability to work on scripts on the system.

envo said:
I'd recommend a database project in vs.net 2012/13 into TFS. You can make a "backup" of the schema and all objects in there, work on it in a nice source controlled environment, and use it to deploy code changes to live (it compares live with TFS and shows you what changed). Also have the option to roll back changes to tables etc which is pretty nifty. plus if you already have the tools, nothing extra to buy :) just a mindset change from the dev's
Click to expand...

This is the method I'm almost 100% happy with.
Just playing with it a little more but almost certain it will do what we have to do.

Just currently investigating how to deploy from Dev -> QA -> ProdQA -> Prod from the tool but I am sold :)
 
Thanks to everyone with their suggestions. Will remember you (hopefully) when I get my bonus :p
 
It's easy to see people who work for mom and pop shops and those who work for companies with proper procedures in place.

No offense, but some of the ideas here are crazy.
 
whatwhat said:
It's easy to see people who work for mom and pop shops and those who work for companies with proper procedures in place.

No offense, but some of the ideas here are crazy.
Click to expand...

Instead of insulting some members why not put forth a suggestion?
 
Because most companies think their procedures are correct. I mean you're using stored procedures, that says a lot in it self. ;)
 
whatwhat said:
It's easy to see people who work for mom and pop shops and those who work for companies with proper procedures in place.

No offense, but some of the ideas here are crazy.
Click to expand...
Would be interesting to see which guys you think work for mom and pop shops and which ideas are crazy...
 
Pho3nix said:
Instead of insulting some members why not put forth a suggestion?
Click to expand...

It's simple, use a source control system to store the code. SVN or GIT. Store your objects in there. Back it up.
Use proper branching strategies to allow different people to work on the same object while still maintaining dependencies.
Version objects automatically, either the check in number or something auto-generated when you commit.
Never trust people to version.
Use alters, don't drop and create. You lose permissions that might have been adhocly added that wasn't put in source.
etc.
Write unit tests. tSQLt is piss easy.

Do not use SSDT or the like to create a patch based on what is on production and your dev environment. That will fall apart quickly once you have more than one person working on it.

The problem with database code is that people keep writing their scripts in notepad or something similar to a napkin, that gets lost or thrown away after deployment. Database code is just as important as application code, and one should follow the same solid principles that already exist for it.
 
That should have been your first post, well said.

Speaking of versioning and tracking database alterations, Laravels Eloquent ORM implementation uses an interesting method they refer to as migrations. While it may not fit into your current environment it is an interesting way of dealing with the problem, perhaps looking into this could give you some ideas.
 
patrict said:
That should have been your first post, well said.

Speaking of versioning and tracking database alterations, Laravels Eloquent ORM implementation uses an interesting method they refer to as migrations. While it may not fit into your current environment it is an interesting way of dealing with the problem, perhaps looking into this could give you some ideas.
Click to expand...

Migrations has been around for ages. Its nothing new.
 
Ah ok, I stand corrected then, thanks. First time I have come across it was when playing with Laravel
 
whatwhat said:
It's simple, use a source control system to store the code. SVN or GIT. Store your objects in there. Back it up.
Use proper branching strategies to allow different people to work on the same object while still maintaining dependencies.
Version objects automatically, either the check in number or something auto-generated when you commit.
Never trust people to version.
Use alters, don't drop and create. You lose permissions that might have been adhocly added that wasn't put in source.
etc.
Write unit tests. tSQLt is piss easy.

Do not use SSDT or the like to create a patch based on what is on production and your dev environment. That will fall apart quickly once you have more than one person working on it.

The problem with database code is that people keep writing their scripts in notepad or something similar to a napkin, that gets lost or thrown away after deployment. Database code is just as important as application code, and one should follow the same solid principles that already exist for it.
Click to expand...

Thanks for the post. Didn't know about tSQLt (see what actually posting and not insulting members brings :p).
If you could please elaborate on the why SSDT will fall apart? The database design is handled by 2 devs while the other dev's use the stored procs.

patrict said:
That should have been your first post, well said.

Speaking of versioning and tracking database alterations, Laravels Eloquent ORM implementation uses an interesting method they refer to as migrations. While it may not fit into your current environment it is an interesting way of dealing with the problem, perhaps looking into this could give you some ideas.
Click to expand...

I'll look into this. Thanks for bringing it up.
 
semaphore said:
Because most companies think their procedures are correct. I mean you're using stored procedures, that says a lot in it self. ;)
Click to expand...

I'm intrigued. The Architect of this solution uses the stored procedures to pull data (transforms it in the stored proc) for the SSIS packages.

Might you suggest a better way around this?

BI Devs are using mostly using views for their reports; except for the problem children.

Always keen to learn something new and better :)
 
The other thing I've seen done very effectively as an additional check to versioning is to hash the source code and use that to check that the version applied is correct.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X