The database under the desk
A small delivery firm keeps its orders database on a server under a desk. One Sunday the disk fails.
The last backup is three weeks old, and nobody knows how to restore it. On Monday the drivers have no list of deliveries. A managed database is one way to stop this happening.
By the end of this lesson
Note
I can say when to use a managed database.
Key words
| Word | What it means |
|---|---|
| Managed database | a database the provider runs for you |
| Self-managed | you install and run it yourself |
| Backup | a saved copy you can restore |
| Patch | an update that fixes a fault |
| Replica | a live copy on another machine |
| Failover | switching to the replica when the main one fails |
The cells students fill in are left blank in this preview.
The provider does the routine work
A database needs care to keep running. Someone must take backups, install patches and watch the disk fill up.
With a managed database, the provider does that routine work. You choose the size and the region, and you get an address to connect to. The provider takes a backup every night. It keeps a replica ready for failover.
Worked example: who does what
| Job | Self-managed | Managed |
|---|---|---|
| Install the database | You | Provider |
| Install patches | You | Provider |
| Take nightly backups | You | Provider |
| Switch to a replica if it fails | You | Provider |
| Design the tables | You | You |
| Write fast queries | You | You |
| Decide who may connect | You | You |
The cells students fill in are left blank in this preview.
Your program still speaks the same SQL
From your side, a managed database works like any other. Your program connects to an address with a user name and a password.
Then it sends SQL, the language you use to ask a database for data. So moving to a managed database rarely means rewriting the program. The query below runs the same on a server under a desk or in the cloud.
Try it: the deliveries still to make
-- Deliveries not made yet. This SQL is the same on any database.
SELECT id, driver, town
FROM deliveries
WHERE delivered = 0;
-- Change the query to show only Tom's deliveries, then press Run.
When managed is the right choice
Choose a managed database when your team is small and nobody’s job is looking after databases. Choose it when an hour of downtime would hurt the business.
Look at self-managed when you need software the provider does not offer, or full control of the machine. A very large, steady load can also cost less on your own server. Count the staff hours, not only the price.
Worked example: counting the staff time
| Item | Managed | Self-managed |
|---|---|---|
| Monthly price | £120 | £60 |
| Staff hours each month | 1 | 6 |
| Staff cost at £30 an hour | £30 | £180 |
| Total each month | £150 | £240 |
The cells students fill in are left blank in this preview.
A common mistake
Warning
Many people think a managed database keeps their data safe whatever they do. The provider keeps it running and takes backups. It does not check your queries. A DELETE with the wrong WHERE is copied to the replica in a second. Test restoring a backup before you need it.