Automating database changes

Hi all,
In our development centerwe do have a development database, Test
Database and a release database,the development database goes frequent
changes , creating new tables,new fields, new relationship, as it is
spanned among 20 developers, we are not able to track down these
changes efeectively, so, this is affecting the testers, and every body.
Do you have any solution to automate these process, something like if
any changes made to the database , it should reflect the next 2
databases effectively
Thanks in Advance
ThomsonYou need a source control system if you don't already have one. Check DDL
scripts into source control and generate the release scripts from there.
There are also tools that will help you compare and synch database schemas.
For example:
http://www.red-gate.com/sql/summary.htm
David Portas
SQL Server MVP
--|||Is there any other tools that is open source or any scripts, that can
be put as job that will solve the problem
Regards
thomson|||Regular team meetings are basically free. Why would you want to propagate
every single change to the testing environment? (I hope you don't actually
mean the production environment as well...?)
ML|||We have a tight deadline on the project , and the development and
testing activities takes place simultaneously, when we modify we do it
only in the development database , Meanwile the application might be
updated for the testers to test, but they will get an error message
saying that the database table field is not there , i need to avoid
this part
Thomson|||I don't know of any open source, source control systems. I use
Microsoft SourceSafe, which is bundled with the Enterprise editions of
VisualStudio or can be purchased separately.
Some other tools, including scripting tools, are listed here:
http://www.aspfaq.com/show.asp?id=2442
Scripting changes from a Dev box is no substitute for source control
but it is useful aid to producing release scripts.
David Portas
SQL Server MVP
--|||I have a similar problem, when I make changes to the database schema
on the test server and its tested Ok, I want to propogate all these
changes to the live server without the test data being copied across or
the live data being lost.
How does Microsoft SourceSafe accomplish this ?|||A developer that creates or alters objects inside the development database
should at least provide appropriate DDL scripts that the db admin can simply
run in the testing database.
Another benefit of this is also in making the developer do a final aggregate
check of all the changes he submits as a result of a particular project
activity.
E.g. based on the Microsoft Solution Framework (MSF) if the program manager
decides on verifying the project results by means of "daily builds", then to
satisfy that requirement team members should provide concise parts of the
solution in order to produce a buildable solution. In SQL terms - provide
appropriate DDL.
IMHO not *just every*change to the development environment should
automatically be propagated to the testing environment. At least not before
the basic (coverage) test is done by one of the developers.
Another benefit of providing "daily" scripts - the database can be rebuilt
on demand - simply by running the scripts in order in which they were
prepared.
ML|||How do you deploy your changes to the test server? You should use the same
deployment process for Test and Live, otherwise you haven't tested.
SourceSafe doesn't directly eliminate the need to create a release script.
What it does is make give you a single repository for changes so that you
can track what has changed and who changed it.
David Portas
SQL Server MVP
--|||We have precisely the same issues - 2 Production databases then 6 developmen
t
databases, 2 that mirror the live environment to track down problems in the
current live production software, 2 test databases which are used by QA to
test the software releases about to go into production and two development
databases.
In addition to all this every single access to the database is through a
stored procedure, several programmers working on different releases and
potentially working on the same sp's.
My solution is to write a VB App that I've call SQL Source Safe (I know
there are commercial apps that do this already) but it's only taken me 10
days and it's already proving invaluable, even though its not really finishe
d.
If you are interested in it Email me msearle@.merac.co.uk and I'll send you a
copy.
Regards
Mike
"thomson" wrote:

> Hi all,
> In our development centerwe do have a development database, Test
> Database and a release database,the development database goes frequent
> changes , creating new tables,new fields, new relationship, as it is
> spanned among 20 developers, we are not able to track down these
> changes efeectively, so, this is affecting the testers, and every body.
> Do you have any solution to automate these process, something like if
> any changes made to the database , it should reflect the next 2
> databases effectively
>
> Thanks in Advance
> Thomson
>