Your web application or mobile API needs a database, so you'll likely be using apt-get install postgres or brew install postgres to get up-and-running on your laptop. When you deploy to the cloud, you'll decide to run the same command used previously. Everything should be production-ready, right? Well not quite.
There's much more to running and managing a database in production than meets the eye. This article outlines tips for managing a production database, giving you some helpful resources on how to get started.
Tip #1: Secure Your Database Server
How secure is your production database? This should be the first question you look to answer. From network access to securing your data over the wire using TLS connections, your database must protect the data it stores.
You should install and configure a firewall to protect your database servers. To limit access to a specific IP or IP range via SSH for external connections, apply firewall rules to restrict the accessible port(s) to your database (e.g. 5432 for Postgres and 3306 for MySQL) so they're accessible only from your app servers. This restriction will require you to SSH to the database server, in order to connect to your database, while ensuring your database process is not publicly accessible to the world.
We also recommend using SSL to secure the connections between your application and database, to prevent snooping on authentication details and sensitive data over the wire. Be sure to verify that the distribution of your installed database supports SSL. Some distributions don't include SSL support by default, requiring the installation of an add-on package or custom distribution.
Tip #2: Tune Your Database For Production
When databases are installed from a binary distribution or package manager, a default configuration file is used to ensure the server uses a limited footprint to be the most compatible for just about any environment. This means, however, that tuning your database is required to get the most of your server in production.
An investment of time for research and testing is required to optimize server performance. There are a variety of variables associated with tuning a database, including a server's available memory, number of CPUs, and the types of queries you will likely execute. Skipping this step will cause your application performance to degrade much earlier than expected, limiting scalability.
Below are some resources to help get you started tuning your database:
- PostgreSQL tuning guide
- Ten MySQL performance tuning settings after installation
- MongoDB optimization strategies
- Redis latency problems troubleshooting and memory optimization
- Elasticsearch index performance tips
Tip #3: Replicate Your Database For High Availability
It's common for applications to span multiple web servers, with a load balancer in front to distribute incoming requests amongst the servers. Techniques such as server groups can be used to auto-scale servers up and down based on incoming traffic, as we outlined in "Cloud Server Scaling Strategies".
No matter how many app servers you may have configured, if you only have one server for your database, you are at risk of the entire application becoming unavailable if your database server fails. Instead, we need to design for high availability.
Database replication allows your data to be duplicated across multiple database servers, preventing a single point of failure. Database replication takes time to setup and configure, so allocate enough time and resources to properly setup your replica strategy.
Below are some resources to help get you started with high availability database replication:
- PostgreSQL load balancing and replication guide
- MySQL replication guide
- MongoDB replica sets
- Redis replication
- Elasticsearch replica shards
Tip #4: Select a Scaling Strategy For Your Database
As your application starts to experience more traffic, you'll begin to see your database queries slow down. The most common use of replication is to create read-replicas, allowing data for incoming requests to be served from multiple servers while retaining a master server for writes. Here is a diagram that illustrates how this works:
Read my article titled "Patterns for Scaling Databases" section of our scaling databases article to learn more.
We encourage you to develop a solid monitoring solution, as well as performing pro-active load testing to better estimate your database and application scalability. This will prevent the setup from occuring under the pressures of a down website.
Tip #5: Establish a Reliable Database Back-Up and Recovery Plan
A database back-up ensures your data can be recovered in the case of corrupted data or server loss. A good database backup should include:
- All data stored within your database
- Server configuration files
- Database user accounts
- Certificates used for secure database communications
- Any external files that may be referenced from within the database via an absolute or relative path
Be sure to store your backups on a third-party system or an object storage solution such as AWS S3, rather than on your database server where they could be lost due to a hardware or filesystem failure.
There are many scripts that make it easy to create a backup solution. I've had great success using the Backup Rubygem, as it's flexible and easy to use.
Perhaps you're using one already, or one that you crafted yourself. That's a great first step. But, can you be certain that your backups are current, reliable, and accessible to the team? Do you know that your backup scripts are working properly, contain a complete data set, and you can restore the data quickly and reliably?
Creating a backup script can be done quickly. However, your backups are only as good as your ability to restore them in a time of need. If you are not testing your backup files to ensure they can be restored, you may be devastated when you realize they don't work.
Verify your database backups as part of your disaster recovery strategy. Perform a dry-run of the restoration process once per month to ensure you understand how to conduct a restoration process quickly and easily. A critical situation is not the best time to look up the commands to execute a backup restoration.
Managed Databases: When a DIY Solution isn't the Right Choice
If your team doesn't have the experience to properly setup and configure a secure database server with replica support, you may want to opt into a managed database service. Managed database services may vary based on the service provider. Cloud 66 offers a managed database service for teams that includes:
- Single-click database replication support, including MongoDB replica sets
- Database backup and verification
- Tuned database server configurations based on your cloud provider and VM size
- Multiple vendor support, including PostgreSQL, MySQL, MongoDB, Redis, ElasticSearch, and RabbitMQ for messaging
- Multiple cloud support, including AWS, Google, MS Azure, DigitalOcean, and Rackspace
View the complete list of features and sign up for an account to get started.