Development and Production Database?

Question

I'm working with PHP & mySQL. I've finally got my head around source control and am quite happy with the whole development (testing) v production v repository thing for the PHP part.

My new quandary is what to do with the database. Do I create one for the test environment and one for the production environment? I currently have just the one which both environments use, leaving my test data sitting there. I kind of feel that I should have two, but I'm nervous in terms of making sure that my production database looks and feels exactly the same as my test one.

Any thoughts on which way to go? And, if you think the latter, what the best way is to keep the two databases the same (apart from the data, of course...)?

Answer 1

I was having the same dilemmas. I got stuck thinking that there was a clear dichotomy between production db versus development db. I.e they were two sides of a coin and never the twain shall meet.

A lot of problems disappeared when I stopped making my application 'think' in terms of "Either production db OR development db". Instead my application uses a local db.

When its running on my virtual (dev) machine, that local db happens to be a dev db. My application doesn't really 'know' that though.

So, for the main part, the problem disappears.

But sometimes I want to run tests using live data, or move data from the code into the live production db and see the results quickly.

This is when I added the concept of a live-read-only db connection. The application treats this differently. Its a bit like how your application might treat a web service like Google Apps. Its 'some external resource that your app uses'.

By default my app uses the local db and in some very special conditions (in the test suite) it also uses the live-readonly db. (Because its a read-only connection I don't fear making a mess of the live data during tests).

So rather than asking the question "dev db OR production db?", my app asks "local db OR live-read-only db".

Obviously my situation could be different to yours, but I found this 'breakthrough in understanding' to be most helpful for me.

Answer 2

See also

How do you version your database schema?

It's a common question and has been asked and answered many times.

Thomas Owens: Replication is not usable for versioning schemas - it is for duplicating data. You never want to replicate from dev to production or vice versa.

Answer 3

One thing I've been working with is creating a VM with the database installed. you can save the VM as a playfile, including its data. What you can do then is take a snapshot of the playfile, and start up as many different VM's as you want. They can all be identical, or you can modify one or another. Here's the good thing: assuming you have a dev version of the database that you want to go out, you can simply start that VM on your production server instead of the current server.

It's another problem altogether if you have production data that is not on your dev machines. In that case though, one thing you can do is set up a tracking VM. Run replication from your main DB to the tracking VM. When you get to a point where you need to run some alters on the production database, first stop the slave and save a snapshot.

Start an instance of that snapshot, take it out of slave mode entirely, apply your changes, and point your QA box at that database. If it works as intended, you can run the patches against your main production database. If not, bring up the snapshot, and get it replicating off the master again until you are ready to repeat the update test.

Answer 4

As a minimum one database for each development workstation and one for production. Besides that you should have one for the test environment unless you are only one developer and have a similar setup as the production environment.

Answer 5

Once I've deployed my database, any changes made to my development database(s), are done in an SQL script (not a tool), and the script is saved, and numbered.

deploy.001.description.sql
deploy.002.description.sql
deploy.003.description.sql
... etc..

Then I run each of those scripts in order when I deploy.

Then I archive them into a directory called something like

\deploy.YYMMDD\

And start all over.

If I make a mistake, I never go back to the previous deploy script, I'll create a new script and put my fix in there.

Good luck

Answer 6

Each environment should have a separate database. Script all of the database objects (tables, views, procedures, etc) and store the scripts in source control. The scripts are applied first to the development database, then promoted to test (QA, UAT, etc), then production. By applying the same scripts to each database, they should all be the same in the end.

If you have data that needs to be loaded (code tables, lookup values, etc), script that data load as part of the database creation process.

By scripting everything and keeping it in source control, a database structure can be recreated at any time for any given build level.

Answer 7

You should definitely have two. As far as keeping them in sync, you should always create DDL for creating your database objects. Treat these scripts as you do you PHP code - keep them in version control. Anytime you have to modify the test database, make a script to do so, and check it in. Then you can propogate those changes to the production system once you are ready.