Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Taking Control Of Your Database by the36chambers

VIEWS: 0 PAGES: 81

									Taking Control Of Your Database
Sam Newman ThoughtWorks

<annoying-introduction>

Thoughtworks: my company

Around 800 people worldwide

Custom software development, process change, organisational change

Clients include

Banks Media Companies Miscreants (Greenpeace, BP) Telcos (AOL & Sky) Retail (DSG)

</annoying-introduction>

So why are we here?

Because we all love change

Change is good

Changing code is good...

To add new features

To fix bugs

To optimize resource use

To make it easier to maintain

We have tools to help us change code

Refactoring IDE’s

Source Control Management

Continuous Integration Servers

Test Harnesses

We want to change our databases for the same reasons we change our code

Fix Bugs

New Features

Optimization

Ease Maintenance

But a database isn’t like code

Is that a problem?

Example (i):

George changes some code in file X

George checks in file X

The new version gets deployed

Example (ii):

George changes the schema for database table file X

George checks in his change

Then it gets deployed...

...but how do you deploy a schema change?

“We could blow away the database and recreate it!”

But data is important. That’s why we have a database

And that’s why changing a database is different to changing code

I love my data

My customers love their data

Blowing it away is rarely an option

“Well we could write a migration script…”

Better

But most of us have several databases

Development databases

Test databases

Production databases!

So how do we know which migration to apply?

“We could manually create migration scripts for each environment…”

But that is:

Boring

Repetitive

Error prone

Hard to test

So is there a better way?

There is – and it’s a simple

Make a big change, by making lots of little changes

It works like this

Split your changes into version scripts

Know which change scripts have been applied to each DB

During deployment, only apply those changes that need to be made

Example (iii)

George creates a change script, and calls it 005_my_small_change.sql

It gets checked in, and now exists along with scripts 001-004

The system gets deployed to the test environment

A version table shows changes 001-003 have already been applied - so changes 004-005 get applied

The system gets deployed to the development environment

A version table shows changes 001-002 have been applied - so changes 003005 get applied

We’ve created an Open Source tool to help you do this…

It’s called dbdeploy (http://dbdeploy.com/)

For .NET and Java

But it’s not the tool that is important – it’s the rules:

1.Break up your changes into little changes

2. Know which changes you’ve applied where

Love change

Love data

Free your inner DBA

Thank you!
snewman@thoughtworks.com http://www.magpiebrain.com/
(p.s. We’re always hiring…)

Questions?


								
To top