Re: Best practices for migrating a development database

Julian North wrote:
> Hi,
>
> We manage a number of high-volume databases that require 24/7 uptime (pretty
>
> much) and deal with this problem a lot.
>
> The solution we employ is that once a database is in production the only
> way to alter the database is using a change script that deals with any data
> migration issues as well as altering the schema.
>
> The practice is for a developer to make changes as they see fit in dev
> using a simple change script. Once the changes are complete (and the
> application coding work has been done) the schema change is passed to a
> member of the dba team to code up properly.
>
> It is this change script (which usually includes a section that can reverse
> the change) that is then run against the qa database. The application is
> then
> tested at this stage before a production release is performed.
[snip]
> All of schema change scripts are managed in source control.

I'm operating at the other end of the scale from Julian's setup, but I
use an almost identical system. In my "db" directory, where I keep my
schema definition files there's also a "changes" file that I use to
transition schema changes. All are under the same version control.

The fact that you can (resources permitting) drop/alter/add tables and
populate all within a single transaction makes updates a stress free
experience. You do need to be disciplined about stopping "just one small
change" slip through the system, but I find the effort pays for itself
almost instantly.

--
   Richard Huxton
   Archonet Ltd