Version Control for Stored Procedures
-
The topic is very much under-covered and even though I don't agree that your examples are ideal strictly for version control (they are good for other scenarios), I thank you for raising the issue nonetheless.
Here goes my bit...
Database scripts form part of an application and the code should be managed in exactly the same way as VB code for example.
My preferred (and to date successful) method for versioning is to use Visual SourceSafe. If a database exists, then I'll script the objects out into separate files and check these into VSS. The project is then labelled. Developers need to understand that the only way to get code into test/production environments is via releases built from VSS. Hacking the code into the database won't work, because your changes may be overwritten by the release.
VSS gives you the ability to identify modules changed after a point in time/label. This information is used to build incremental releases. Also the VSS comments can be used to build a release note describing the changes contained in a release.
Schema changes are handled in a similar way to SP/trigger/function changes.
I have used the VSS Object library to build a program which gets my changed modules to a "build" folder, creates a release.txt and a Release.bat for applying the release.
Obviously there are alwasy exceptions and there is often some manual interaction required (esp. when it comes to schema/data changes).
I also have some suggestions for handling versioning of production hot-fixes and same-time parallel development for multiple separate releases if anyone is interested. That is the branch/merge issue in a bit more detail.
For me, the main objective is to have a record of all changes made and to be able to reproduce builds as at a given point in time/label. VSS also allows a quick visual diff of current and past versions of a module.
-
Andy,
I think we are going to have to agree to disagree on that one. I still believe that only the latest version is important, and I think that the reason you have difficulty with this is that you also need to apply this approach to the calling code. My previous comments only related to the sp code versioning, not the application code versioning.
Why are you still testing v.05 when it has been superseded by 2 generations? Why would you even have 3 concurrent versions? Does each of your developers add their own changes and give it a version number, or something like that? Concurrent versions cause difficulties! You are getting away with a lot because it is a small team working on a small project.
I see sp's as being no different to functions written within the code. They present an interface and return a result. I would always design the project so as to know exactly what return values I require from the sp's and code them first in isolation to the rest of the app using the approach to versioning mentioned earlier. Once the sp's are stable they can be given as black boxes to application developers.
I would advocate a similar versioning structure for each function or object within your app code. That way when you get multiple developers working on multiple functions it doesn't get all mixed up. To create a new app version I would take the latest version of each function(remembering that these are also tested in isolation) and build a new version of the app. Again this is the only version that matters until another supersedes it.
Based upon the premise that the only reason to replace code is to improve it, the latest version contains the most successive improvements and so makes sense that this should be closer to the goals that the application needs to fulfil.
-
Shoot, sometimes I dont even agree with me! No problem at all with disagreeing.
The only reason we had multiple versions out was that some of the features required extended testing. So we'd release v0.5, they would do testing on the smaller areas and report back minor bugs, which we would fix and roll out to them as v0.6. Meanwhile the longer testing would continue on v0.05, if they found bugs we'd fix and roll back to them for v0.7. This leveraged our time all the way around, we could continue working and fixing, testers didnt have to stop mid test and start over on new version.
Chaos? Not as bad as it looks.
I tend to agree that the latest code is the best code - thus our reliance on highest proc number being best.
I think you're absolutely right that because we had a small team (and fairly proficient and able to follow a plan) that we could get away with this. Yet for a bigger team we could have allocated more resources to managing change, either via a DBA or a buildmaster or whatever.
So, its a quick and dirty method!
Andy
-
Another two cents on the pile...
We use version control (VSS) to manage our stored procedures, as at least a dozen programmers might be working on them at any given time. However, they only have access to the Development version of the database; the "QC and up" databases are off-limits to hackers <crosses fingers>.
We have a "database type and version number" wired into our databases. When it's time to roll out more code, we get the source from VSS and wrap it in a T-SQL deployment script (along with any table changes, simple data changes, and the like). This script checks for correct database type and version, and applies the changes smoothly and simply. Well, Ok, the QC deployment is the debugging routine, but once that's past I hardly ever have deployment problems. Everyone else panics, but the DBA gets to say "It's just another Production deployment."
Also just to mention, we use Visual Interdev to manage the source control integration. It's a pain to set up with SQL 7.0, but once in place it's pretty seemless. (Getting it to work with SQL 2000 is another story, as the process is buggy. I'm about 90% of the way to getting it to work, however...)
Philip
-
We simply use VSS to store files used to make the SP. Each file has three part: (1) Delete the SP; (2) Create the SP; (3) Grant the correct permissions.
All changes are made in a text editor on checked out copies of the script file. The updated script is executed in Query Analyzer, and the script checked back into VSS.
Viewing the list of SP in the Enterprise Manager shows the last change date of each SP since each SP is recreated for every change.
Identical methods are used for Views, Triggers, Functions, etc.
-
I do something slightly different. I use Enterprise Manager for editing my stored procs. When I am ready to save my work, I script the entire DB so that each table, view, & stored proc is saved as separate files in a directory. (I also save a full script as well in case I need to rebuild the entire DB on a different server.) I then check in the directory into SourceSafe. Provided you do not turn on the "Include descriptive headers..." checkbox, SourceSafe will only check in the script files that have changed in content. This way, not only do I have a way to compare different versions of stored proces through SourceSafe, but I can also compare changes in table and view definitions.
-
Whatever comes from Andy deserves attention. Excellent article!
For some people there is nothing new, except that feature in SQL2000:
"Create proc usp_test;2 as .........", which I wouldn't use it anyway as
SQL server didn't come up with a tool such as VSS...
The way I would use Version Control is:
1. Comment out old code (-- or /*..*/) for small changes
with appropriate short comments.
2. VSS for larger changes.
3. Regular DB Scripting of all objects.
Commenting out is not new, but very useful, as DBA or Developer can
roll back by uncommenting the old code and commenting new code.
Hope this is clear....Omer.
Omer
Omer -
Commenting has out has its place, sometimes anything else is overkill. What case do you make for scripting out all the db objects?
Andy
-
I may be skirting off into insanity here, but for some time I've been using an XML file that provides abstraction to handle small and large version changes to stored procedures. It avoids having to search and replace in the code to find instances of a stored procedure, and provides a way to easily document and distribute any changes as versions change. I know it is probably overly complictaed, but it seems to hit the spot.
The data structure includes an unchanging procedure name, version code, and description. The developer can call the procedure with a hint (the code) to test new calls, or without a hint to call the base procedure. When I need to distribute, I simply compile the application parts to their latest tested standard and script the approved stored procedures to the unchaning names (with the approved versions migrating upward). It keeps the release database relatively clean, and avoids having to fiddle with anything other than a single text file. It does require the developers keep their hint (version code) list available, but other than that takes very little time.
Though, I admit the internal version approach sounds neat.
-
I think the method you mentioned in the article works well under a hectic schedule, but I wouldn't want to go through figuring which set of sprocs were included in a version.
I've found that if you use the generate script in SQL analyzer for each sproc and check that in to CVS or VSS, it takes only slightly longer than changing the name. What it lets you do, though, is force consistency and track versions and changes more easily than name changes. We've implemented this and we can tag exactly what sprocs are being tested or are moving to production without having to change any code.
It also means that if we wanted to recreate a test environment for a bug, we can just pull all the sprocs from that release and load them into SQL without having to hand pick the stored procedures or change any code. Just seemed like a very elegant solution at the time. I think I read about it on here at some point. (sqlservercentral)
-
The article said:
Then when we versioned, the next one would look like this:
Create proc usp_test1
To avoid having to rename existing uses of usp_test I would rename the "previous" version to usp_test1 and keep the name usp_test as the current version all the time.
-
A file based system with a file for each proc is easy enough to do too. To get developers to adhere to it, we only stage from CVS. If it ain't in there, it ain't pushed out.
Developers who delay a couple of releases soon learn to check in their work.
-
Personally I use Visual Sourcesafe integration, with keyword expansion. The source to each stored procedure is stored in a .sql text file, complete with the code to drop the sp, create it afresh, and then grant permissions to it, together with any environment variables, such as
set ansi_warnings on. This seems to be the approach used by glittle et al, and IMO is definitely the best.By adding a comment block like this before the sp itself, I get plenty of metadata about the sp, who created it, who last edited it, and what the changes made were:
/**************************************************Last Changed By : $Author: $
Last Checked-In : $Date: $
Last Modified : $Modtime: $
Revision : $Revision: $
History : $History: $
**************************************************/
because the keywords are expanded to give all sorts of info. And all the joys of rollback and branching/sharing.
Also by storing the files in text files, I can then write a DOS batch file to execute them all, so I can deploy all 250 SPs my current app uses to a new server with one command
-
After reading all the articles here, I think there are alot of people going through alot of pain for no good reason.
Check out what we have to say about change management http://www.innovartis.co.uk
We also do a white paper on the subject - if you would like a copy email me mark.baekdal@innovartis.co.uk
regards,
Mark Baekdal
-
Really very good stuff to know about version controlling in SQL Server and surely this way will help the developers to version their stored procedures.
Kodi
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply