How to update your production codebase/database schema without causing downtime

There are a few obvious arguments against this.

1. What happens if you leave. Is all this information carefully documented, or is it mostly in your head. Automated scripts are a much better place for someone else to take over from.

2. Everyone makes mistakes. There will come a time when the person doing the deployment is tired, not paying attention whatever. Yes ideally deployments are only done in an happy calm place with lots of time. In practise they can be rushed and stressed when trying to roll out urgent fixes. This is the mostly likely time to make a mistake, and also the most costly. If the deployment is a single script then the potential for mistakes is limited.

3. Time. As deployments get more complicated the amount that needs doing increases. Scripts just require kicking off, a manual check, and then a manual switch-over (you could automate this as well, but I share some of the paranoia :).

I can tell you what's worked for me. There may be reasons why it does not work for you.

First, I wrote a schema diff tool. It was based on a physical database design reporting tool I also wrote which examines a database and prints a report of all the tables, columns, relationships, indexes, constraints, etc. The diff tool simply runs this on two database instances and reports any differences. This diff tool allows me to detect differences between things that should be the same.

The next important piece is hygiene. Changes are never made ad-hoc to the production database. They are made with an SQL file which performs the "update" once we know it is safe to apply the update. We know that because we already ran it on the pre-production instance of the database and did some testing.

How many databases you hav apart from production and pre-production is really an issue of how your project is organised, and isn't so relevant apart from to maintain the discipline of not making ad-hoc changes (apart from adding users or storage devices, for example).

The next important piece of the system is the development database. By this I mean the database used to test code during development immediately prior to release. This is typically shared by the developers (who may also have their own database for making private changes and experiments).

During development. every time a database change is made, update the build-from-scratch SQL and also make a stand-alone SQL file which applies just this change.

To make a release I would do this:

1. Create a blank database (I will call this PREV)
2. Apply the database setup script for the previous release
3. Load test data into it (perhaps data sampled from production). This is important for correctly testing things like constraints and column types.
4. Apply the SQL scripts for performing the upgrade (i.e. the stand-alone files I mentioned above)
5. Do any release testing you need on this, but in parallel:
6. Create a second blank database (I will call this NEXT)
7. Run the install-from-scratch SQL to build he database in NEXT
8. Run the comparison tool to compare PREV and NEXT. Apart from the fact that NEXT has no data, they should be identical.
9. If all is OK, export the data from PREV because you will need it next time (as step 3).

Following this proces means you always have two things handy: 1. A create-from-scratch script which can be used to build new database instances for new uses (e.g. for support reasons, for clients, etc.) 2. Upgrade scripts which are guaranteed to work to bring the production system to an exact match with the development and pre-production systems (to avoid unexpected differences in the databases).

To summarise using the terminology from your question, the key thing is to make sure that even the development database is only changed by running SchemaChange.sql scripts, and also to ensure that you have a sufficiently robust testing regime that your tests will tell you when this didn't happen for any reason.