Stored Procedure Management

Question

Stored Procedure Management

dale_keller

Say Hey Kid

Points: 713
More actions
February 19, 2009 at 2:20 pm

#201466

Hi,
I was wondering what are the best practices for maintaining stored procedures? Is there source code management for stored procedures in SQL Server 2005? I know if you keep regular backups the stored procedure will be safe but if someone accidentally drops the database your code is gone. I always keep my own copy in a file and always back up my stored procedures and functions. I know with any .net or C# program they would use sourcesafe or PVCS to protect their code - just wondering if there is anything for SQL.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

maechismo_8514 SSCoach Points: 17510 More actions · Answer 1

dale_keller (2/19/2009)
Hi,
I was wondering what are the best practices for maintaining stored procedures? Is there source code management for stored procedures in SQL Server 2005? I know if you keep regular backups the stored procedure will be safe but if someone accidentally drops the database your code is gone. I always keep my own copy in a file and always back up my stored procedures and functions. I know with any .net or C# program they would use sourcesafe or PVCS to protect their code - just wondering if there is anything for SQL.

If you maintain proper security permissions it should satisfy your needs. Backuing up databases is for DR strategy, you dont have to backup databases just to protect stored procedures.

If you give users only to execute the them then they would not be able to drop them.

andrew gothard SSChampion Points: 12313 More actions · Answer 2

Even with DataDude (VS Database Pro) there's no real integration with VSS as with .NET languages. What you could do is use VS with the db powertools to generate your scripts used to move from dev -> QA -> Live and put these in VSS. Klunky and a pain, but like I said, there's no slick integration as with .NET etc, so you kind of have to roll your own and find something that works in your environment

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

You can integrate VSS to SQL Server (SSMS). Thats how i do it.

"Keep Trying"

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

dale_keller (2/19/2009)
... but if someone accidentally drops the database your code is gone.

:w00t: if this is the case your problems run deeper than code version control my friend.

I would use a version control software and also have a three layers environment as follows:

1- Development

..... Developers have full privs on code.

..... Business users have no privs on code.

..... Version control software takes care of check-in, check-out individual procs.

2- UAT

..... Developers have execute only privs on code

..... Selected Business users have execute only privs on code

..... DBA's are in charge of promoting code from Dev to UAT

3- Production

..... Developers have no privs on code

..... Business users have execute privs on code

..... DBA's are in charge of promoting code from UAT to Production

(*) Code promotion implies to save a dated backup copy of the code to be overwritten then, compile the new code.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jim C-203340 SSCommitted Points: 1716 More actions · Answer 5

We have an in house data utility that allows us to script out every object in our database. We go all the way down to the smallest detail; storing indexes, constraints, defaults, triggers, etc. all as seperate objects. These all get stored in source control.

If you script everything out like this, then the only thing you lose if your database gets wiped out is the data.

I would suggest some form of source control as a solution to your problem.

Regards, Jim C

noeld SSC Guru Points: 96590 More actions · Answer 6

... but if someone accidentally drops the database your code is gone.

If anyone can "accidentally" DROP a database you have much bigger problems!!!!

You can prevent "accidental" DROP DATABASE using:

1. The Correct permissions for the developers

2. SQL Server DDL Triggers

3. Publishing it without adding subscriptions

... etc

* Noel

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

noeld (2/20/2009)
... but if someone accidentally drops the database your code is gone.
If anyone can "accidentally" DROP a database you have much bigger problems!!!!
You can prevent "accidental" DROP DATABASE using:
1. The Correct permissions for the developers
2. SQL Server DDL Triggers
3. Publishing it without adding subscriptions
... etc

I would say corect permissions, if you manage the security properly you dont have to get into hassle of creating Triggers. Start from very minimal permissions and then gradually start adding permissions as needed.