| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2012-07-11 : 13:23:56
|
| I am letting everyone know in advance, this is not a very technical question.We have in our Databases a lot of Stored Procedures for temporary or testing purposes. For example, the App calls a procedure ClaimFetchList. Then a Developer will need to make a change so they will rename the original one ClaimFetchListOld. Then another Developer comes along and makes a Copy of it calling it ClaimFetchListTemp. Then another Developer comes along and we get a 4th version: ClaimFetchListJan27. etc, etc, etc...Then we end up with a lot of junk and it makes comparisons difficult and it can sometimes be hard to tell if the Stored Procedure is a 'good' one or not.Has anyone else experienced this same situation? How is it best handled? Or am I complaining over nothing?We came up with a naming scheme: any Stored Procedures of a testing or temporary nature (i.e. not used) be named with a preceding 'tmp'. For example: tmpClaimFetchList, tmpClaimFetchListOld... But now, sometimes the developers don't follow it. Any ideas?Also, please note that we are using Source Control for Stored Procedures. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-11 : 13:32:00
|
| Have you considered using different schemas instead of prefixes? It's cleaner, you can have 2 of the same name in different schemas, and apply a more regular naming scheme. Anything that doesn't conform can be safely dropped. And if not, you can beat up the developer for violating it. It also promotes using 2-part names in all object references. |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-07-11 : 13:35:07
|
quote:
... Have you considered using different schemas instead of prefixes?...
We haven't been but something for us to consider...quote:
... And if not, you can beat up the developer for violating it...
Ha, good one. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-11 : 13:47:57
|
Well I'm only half-joking. If you drop a procedure and they say "Hey! I was working on that!" tell them to name it correctly. I can't remember who it was but a long-time-ago SQLTeamer used to do hourly code deployments from source control to his dev environment. If you didn't check it in, it didn't stay in the database for very long. Zero maintenance of the dev databases ensued.  |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-07-11 : 14:11:51
|
| LOL.. I have to agree with Rob. This is just asksing for a solution to a problem that shouldn't exist. If you can't control the database/developers then you need to get management involved or get outta that palce! :) |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-07-11 : 14:17:16
|
quote:
Originally posted by Lamprey
If you can't control the database/developers then you need to get management involved or get outta that palce! :)
The Developers always keep telling me that they forgot to follow the naming scheme... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-11 : 14:31:43
|
quote:
The Developers always keep telling me that they forgot to follow the naming scheme...
That's OK, they're human. They also used to forget how important it was to keep their balance when riding a bike. Enough falls and injuries compelled them to remember. If you want you can always apply Policy Management that utterly prevents objects that violate a naming scheme. You could also use DDL triggers to similar effect.I know you're trying to be reasonable but the fact is that the current "scheme" is a mess, and you're stuck doing the cleanup. The choices are you enforce your own scheme (that they won't like), invite them to contribute their naming scheme (they DO like), or require them to clean up the database on a regular basis. I'm assuming (hoping?) this is for development only and not production. If it's in production you need to start shooting people. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-07-11 : 15:05:54
|
| Yes, I think you are right. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-11 : 15:49:34
|
Might be time for a separate development environment  . As a DBA you won't care what those developers have over there in dev land but you won't put anything into production that doesn't satisfy testing and conform to standards. Oh yeah, And they don't have access to production servers - that's a biggie!)Be One with the OptimizerTG |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-07-12 : 12:07:47
|
quote:
Originally posted by TG
Might be time for a separate development environment .
Well this actually is a Development environment. The thing is I need to promote Stored Procedures from Development Databases to other Databases. Usually I'm requested to promote 'everything' rather than given a list. So that is when I start encountering all these Stored Procedures with testing names. I think of these like someone leaving around litter rather than cleaning up after themselves. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-12 : 12:17:23
|
| Ultimately you're better off only deploying from source control. I know Redgate makes great tools that simplify comparison deployment but if you deploy from source control you are far less likely to make a mistake, use the wrong setting, deploy the wrong version or even the wrong objects. Redgate has a very good source control tool now that makes this even easier.And yes, it's a royal pain in the ass to convert to this style of deployment, but having a consistent (automated or continuous) deployment of (tested, error-free) code is highly addictive and satisfying. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-07-18 : 11:07:20
|
quote:
Originally posted by robvolk
...Redgate has a very good source control tool now that makes this even easier....
Thankyou for mentiong this.Does this allow you to label all Stored Procedures with a version# and then apply all Stored Procedures with what the code was at that version# and apply it to another Database?Anyone have any links on this? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-18 : 11:45:30
|
| Link: http://www.red-gate.com/products/sql-development/sqlsourcecontrol/I've seen a brief demo of it but can't remember the details re: labeling procedures and such. It works with existing source control rather than supplementing or enhancing it, so if your source control does labels it should pick them up. In TFS you'd typically manage this using branches and merging. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-07-18 : 13:13:28
|
| If you are using SQL Server do you also use other Microsoft products? If you don't have TFS, you might look into using Visual Studio database projects and TFS (source control). Then it puts the ownership/resposibility of having the correct "code" checked into TFS as a project on the developers and the DBA/Deployment person can use the built-in DIFF/Comparison tool to create deployment scripts. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-07-18 : 15:16:56
|
quote:
Originally posted by denis_the_thief
quote:
Originally posted by TG
Might be time for a separate development environment .
Well this actually is a Development environment. The thing is I need to promote Stored Procedures from Development Databases to other Databases. Usually I'm requested to promote 'everything' rather than given a list. So that is when I start encountering all these Stored Procedures with testing names. I think of these like someone leaving around litter rather than cleaning up after themselves.
Only deploy from your source control system - don't deploy from the development environment. If they need something deployed - it better be in the source control system, or they can recreate it after the system is deployed.Isn't that what a source control system is supposed to do? |
|
|
|
|
Naming Scheme for temporary Stored Procedures