Greg, An approach that worked well in the past for me was:
* No permission to execute DDL statements on staging / dev databases given to the developers. They still have full access to their local copies. * All database updates (tables, SPs, population scripts etc) are to be checked into source control system * All above scripts are to be re-runnable (for example a new field is to be added to table, the ALTER statement is condition with an IF to check if the field is not already in there) * The continuos integration server periodically (or on request) executes all current SQL scripts as found in repository however this is defined (could be a TXT file, a DB table, version numbering system etc) recreating the stored procedures and views and tables etc This way all updates are kept in repository and are 'scripted' in a way so the DB deployment is testable and manageable. j. On 31 May 2010 12:56, Alan Heywood <[email protected]> wrote: > Hi Greg, > > I use MigratorDotNet <http://code.google.com/p/migratordotnet/> to address > this issue. Migrator allows me to define incremental changes to the > database using code, which is then checked into source control along with > everything else. We have a Continuous Integration setup, and when a build > is triggered the following occurs: > > 1. Code is compiled > 2. MigratorDotNet is called, passing in a reference to the xxx.data.dll > from our project. > 3. Migrator uses reflection to find a list of 'migrations'. Version > number is compared to the current one stored in a table in the database. > If > there are new migrations to be run then they are executed on the database. > > Using this approach you can create tables, define foreign key constraints, > add new colums etc. You can also execute arbitrary SQL statements. This > means that you can also add or modify data as part of a migration. > > Works really well for me, particularly since I would otherwise be manually > attempting to keep > 10 databases in sync. > > Alan > > On 31 May 2010 11:14, Greg Keogh <[email protected]> wrote: > >> Folks, I’m sure we’ve all had problems where multiple developers change >> SQL Server scripts and they get out of whack and waste time with stupid >> errors. >> >> >> >> I’m starting a fresh app and I thought I’d experiment with keeping scripts >> in SVN. It just means that we have to remember to always save a script to >> the source controlled file whenever it’s changed. >> >> >> >> Because scripts aren’t compiled source code, there is still the risk of >> human error in not pushing any updated script files into the DB. I was >> thinking of concocting a utility which automatically pushed changed scripts >> into the DB, but before I start fiddling I thought I’d ask about this >> subject in general first. Are there others out there who source control >> their DB scripts and have techniques for reducing human error? Or perhaps >> there are better techniques that I’ve completely overlooked. >> >> >> >> Greg >> > >
