Eric, Pl. suggest me how to control and maintain database objects (SQL Srvr) history and their version as they keep changing frequently.

Thanks. Kothans.

-- Anonymous, October 30, 1998

Answers

Re: Control and maintain database objects..

Kothans,

For this discussion, I assume that "database objects" refers to the database schema items and the stored procedures.

It is not easy to control and maintain database objects, and most shops generally don't do it very well. The trouble is that the vast majority of configuration management tools (Visual SourceSafe, PVCS, etc.) are file based. SQL Server objects are database based. Configuration tools cannot get to the database.

Some shops have had fair success with using ERwin to make schema changes and always forward engineering them. Far more shops use ERwin to design a database schema, generate the database, and forget the database model. (Fortunately, ERwin is designed to also reverse engineer your database when you want to get in synch again.) You can even combine this with a configuation management tool (for ERwin stores the schema in files). Stored procedures also can be generated from file based scripts and can be kept under configuration management if you always run the scripts after making changes.

Other shops employ one individual to function as the "SQL Librarian". All changes must go through this person. This as you can imagine is a full time job.

Good Luck,

Eric

-- Anonymous, November 04, 1998

Re: Control and maintain database objects..

Eric,

Thank you so much for getting back to my query.

I didn't really understand a sentence that I putforth for you to make me understand as I refer your tips.

Your tips : It is not easy to control and maintain database objects, and most shops generally don't do it very well. The trouble is that the vast majority of configuration management tools (Visual SourceSafe, PVCS, etc.) are file based. SQL Server objects are database based. Configuration tools cannot get to the database.

Q. Configuration tools cannot get to the database?

Thanks. Kothans.

-- Anonymous, November 04, 1998

Re: Control and maintain database objects..

Kothans,

A configuration management tool will allow you to "check out" the latest version of a file from the "archive" and into a "working directory". When you are done editing the file you do a "check in" to put it back into the "archive" (perhaps leaving a read-only copy of it in the working directory.

For instance, with PVCS, if I have a file, hello.c. The archive of this file is hello.acv in the archive directory. When I check out hello.c, PVCS fetches the latest version from the version history archive hello.acv and puts the latest hello.c in the working directory. When I check in that file, PVCS updates the version history and marks the hello.c in the working directory as read only.

The trouble with the SQL Server / Configuration Management tool interaction is that if I have a stored procedure, hello, it exists, not as a file, but rather as multiple instances of the text column of the syscomments table of a database. The database, which by nature is constantly changing, exists in multiple files with names like DEV1.DAT and DEV2.DAT. Configuration management tools cannot directly deal with this situation.

Hope this explains it,

Eric

-- Anonymous, November 05, 1998

Eric,

Many thanks for your kind explanation, which cleared my doubts.

Thanks.

Kothans.

-- Anonymous, November 06, 1998