Best practices for concurrent development of DB

Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph
Look into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas

"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph
|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph