Here’s a straw poll for anyone with a dev team > 1 person:
Do you store your database changes in a source control system? (i.e. RedGate SQL Source Control tied to Subversion)
Do you use a shared development database or local copies of SQL Server on each developer machine?
We already have local copies of ColdFusion and IIS configured and have all our CF code in Subversion. We have recently started using Jenkins to automate our unit testing and deployments.
We are moving to keeping our DB changes in source control as well, but are weighing the complexity/benefits of maintaining local databases versus keeping our shared development DB server.
We keep DB changes in source control. We haven’t used a nice SQL integrated tool like RedGate, but we make incremental changes and create scripts for those changes that we have internally dubbed “scriptlets” with the data of the change included in the name of the file (ie 2012-1-5-cool-stored-proc.sql). Then we can all keep up to date by running any scripts committed since our last update. Periodically, then we dump in a new db backup and can clear out the incremental changes. Alternatively, I have worked on projects that just do a new db backup for each change. ORM has made all of this much easier though.
We have do both (shared db and local db) depending on the project, but personally, I like the local db option. To me, local development should mean local. This gives developers more freedom to explorer or try things even with the DB without worrying about mucking up everyone else.
We keep DB changes in source control. We haven’t used a nice SQL integrated tool like RedGate, but we make incremental changes and create scripts for those changes that we have internally dubbed “scriptlets” with the data of the change included in the name of the file (ie 2012-1-5-cool-stored-proc.sql). Then we can all keep up to date by running any scripts committed since our last update. Periodically, then we dump in a new db backup and can clear out the incremental changes. Alternatively, I have worked on projects that just do a new db backup for each change. ORM has made all of this much easier though.
We have do both (shared db and local db) depending on the project, but personally, I like the local db option. To me, local development should mean local. This gives developers more freedom to explorer or try things even with the DB without worrying about mucking up everyone else.
I run a very similar approach to Curt - I keep a number of files in source control that are responsible for the database changes.
For instance, I add a new persistent class called 'Foo' to the application, I create a script that generates the appropriate database tables, populates with any basic information, modifies other tables if needed etc.
This is a similar approach to what you would find in Rails, each script is named with a datetime stamp, this ensure that.
a) we know which scripts have not yet been run.
b) we know what order they need to be executed in.
I have had discussions with Luis in the past about perhaps researching and building a proper rails style migrations framework as I'm sure plenty of people would find it useful, but it's not something I've given anything more than a glancing thought.