Version Control for SQL Code

[CaptRespect]

We've been using CVS for our HTML, JAVA etc..

Now we would like to use it for our SQL CODE to. We have a large
database with many stored procedures.
I would like to have something using Ant like:

Download the latest version of source code.
If the SQL patch has been updated, run the patch to update the
Database.

I don't know how to deal with the SQL patch files. Do I update the
same file(over writing the anything from the previous patch, or just
write new files for each version?)

I am sure someone has done something like this.

What are the best practices for doing this? Could someone please
outline thier setup for SQL version control?

[Erland Sommarskog]

Re: Version Control for SQL Code

CaptRespect (jroberts@renew alinc.com) writes:[color=blue]
> We've been using CVS for our HTML, JAVA etc..
>
> Now we would like to use it for our SQL CODE to. We have a large
> database with many stored procedures.
> I would like to have something using Ant like:
>
> Download the latest version of source code.
> If the SQL patch has been updated, run the patch to update the
> Database.
>
> I don't know how to deal with the SQL patch files. Do I update the
> same file(over writing the anything from the previous patch, or just
> write new files for each version?)
>
> I am sure someone has done something like this.
>
> What are the best practices for doing this? Could someone please
> outline thier setup for SQL version control?[/color]

It seems that most people who use version control with SQL Server
uses Visual SourceSafe, which is probably more due to that that
VSS is conveniently at hand in the CD case, than the fantasic qualities
of VSS.

Obviously, if you are using CVS you should stick with that.

I don't know what Ant is, so I cannot really comment. Many people seem
to make integration with Visual Studio or some other tool a big issue.
Personally, I've always found that over-rated, and myself I use VSS
Explorer to check in and out stuff. That is the plain interface of the
CM tool.

Some people think that stored procedures are special, but they are really
not. They are just source code. The one thing which is devious is that
if you develop in Query Analyzer, is that the normal routine of development
is disrupted. The normal routine is 0) Check out 1) Edit. 2) Save. 3)
Compile. 4) Test. 5) On error back to 1. 6) Check in. With tool like
QA, 2) does not need to happen to 3 and 4. Therefore you run a risk of
checking in something that is not what you tested.

To avoid this I use a third party editor, Textpad (www.textpad.com). It
does not have any special SQL capabilities, but it is an overall better
editor than QA. And from Textpad I invoke a command line tool that loads
the procedure. I happen to have my own tool, but OSQL does just fine.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ray Mu]

Re: Version Control for SQL Code

Hi,

SQLSourceSafe 2.0 is the right version control solution for SQL Server
database development projects. SQLSourceSafe 2.0 Integrates with both
SQL Server and Visual SourceSafe, providing an effective and
effortless version control management system for individuals and
project teams to manage database objects.

By using SQLSourceSafe 2.0, you can manage SQL Server database objects
such as tables, stored procedures, user defined functions, views,
triggers, indexes/keys and constraints as you manage regular files and
projects in SourceSafe. SQLSourceSafe 2.0 enables your to archive and
label database object changes in SourceSafe, so that you can compare
different versions, recover from an undesired write-over of objects,
or roll back to a previous release. Advanced users can also use
SQLSourceSafe 2.0 to find out the differences between SQL Server
databases or deploy the latest or any labeled version SQL Server
database archived in SourceSafe to different computers.

For more information, please visit: http://www.bestsofttool.com.