Deploying Databases Locally vs. In a Live Environment

Local Database Deployment

Neither developers, nor higher-tier DBAs have many worries when deploying databases locally. Perhaps they worry about configuration, but not much else. With local deployment you already know all of the parameters of your system, so the only thing that you need to do is allocate them appropriately. Literally – that‘s it! If you’re running MySQL, just open up my.cnf, set your parameters to use 60-80% of RAM, exit WAMP, start it again, and that’s it. Not that hard! 

To deploy a database, you simply use phpMyAdmin or log in through the CLI and run a couple of CREATE DATABASE queries (advanced DBAs might even have scripts that accomplish all of these tasks for them.) Select your collation, database storage engine might be set up in my.cnf (by default, that’s InnoDB or XtraDB, depending on the database version you elect to use), and that’s it – you’re up to a good start.

Unfortunately, everything gets more difficult in a live environment. The reasons of that happening are numerous – live environments usually need more care and nourishment than local ones, before adjusting your settings you must carefully consider all aspects both of your server, and before you know it, customers start pouring in – what do you do now? Adjust the functionality of the server then? No – that wouldn’t work. 

Deploying Databases in a Live Environment 

Developers and database administrators deploying databases (or software in general) in a live environment need to consider the following things: 

1. How big is the customer base of our present project? 5 customers are not too much to worry about in regards to downtime – 5,000,000 however, is a big deal. In that case, 5 minutes of downtime could cost your customers thousands. Are your databases prepared for that? 
2. What parts of our project are used the most frequently? Before deploying a database, perhaps we can deploy it in a server that’s used less frequently than others? If one server is always under 88% of load, the other – under 6%, it might be time to have a closer look at the second one. Why is that the case? Perhaps we can deploy our databases on that server to not cause hassle and load issues for other servers? 
3. Do we need to deploy one additional database? Seriously, this question is overlooked so frequently these days – developers just deploy databases here or there. Before deploying one, ask yourself: why does our team deploy a database? Is it going to be normalized? If yes, were the rest of the databases not normalized? Why? What’s the use of one additional database? Is it crucial for our project? Will it not be a burden for the underlying infrastructure? Will it help us adhere to certain regulatory requirements (e.g. GDPR and the like?) 

Consider those questions carefully. Weigh your choices on three principles: 

1. Who? Who is deploying the database? Who is it directed at? 
2.  What? What’s the function of the database? 
3. Why? Why the database is being deployed? 

The answers to these three questions will determine whether deploying a database is a good option for your use case. If the answers to all of those questions are, in your opinion, solid, deploy a database. Before deploying one though, investigate your configuration files and adjust the parameters so that they use the specifics provided by your operating system to the maximum. Look at your RAM, how much space you have in your hard drives, look at what your CPU can handle, and decide what parameters are you going to set and to what values – if you have a hard time deciding, though, automating database processes can help. 

Automating Processes in Deployment 

“Decide what parameters are you going to set and to what values” – as if it were that easy. 1, 2, 3, done? No. This step requires careful, very careful consideration. One step in the wrong direction and your database is in ruins. Thankfully, there are tools that can assist you in this realm: one of them is dbWatch. 

With dbWatch Control Center, your databases are in safe hands, because dbWatch can support pretty much all database flavors that are out there: MySQL, MariaDB, PostgreSQL, Oracle, Sybase, even SQL Server. Once you know what database flavor you’re going to use, think of the database tasks that you’re automating: dbWatch comes with a bunch of database jobs (tasks) that can be scheduled and ran at a specific time, but those database jobs that can be ran depend on the specific database management system that you find yourself using: if you’re using MySQL, they aren’t going to be very extensive simply because of the fact that MySQL is in active beta right now, but import a Microsoft SQL Server instance into dbWatch and you will be able to choose from tens of database jobs in the performance space alone ranging from performance to security to replication.  

Here’s an example of how MySQL jobs would look like so you can imagine what would happen in the space of other DBMSes: 

Choose what job you want to run, schedule it by right-clicking (click Set Schedule), and your job will be automated: 

Once your job is automated, the work doesn’t end there – you can stop here, but we don’t recommend you to, because there are other places of dbWatch you will need to keep an eye on if you want to put your database instances towards the highway of performance by using database farm management.

One of those is the Database Farm available within dbWatch (that’s the third icon from the top.) The database farm will let you gain an overview of the inventory that your databases are using (what platforms, versions, etc.), and also let you keep a close eye on your database jobs (both those that you have automated, and those that are not yet set up.) This page will let you get an overview with jobs with a status of “OK”, “Warning”, and an “Alarm”.

The jobs that you will need to keep a close eye on when you’re deploying in a live environment will be those having the statuses of a “Warning” and an “Alarm” simply because a jobs of that status imply that something is either wrong or seriously wrong in your database instance. Inspect those jobs once they turn orange (meaning a warning) or red (an alarm) – that means something goes off the rails and needs immediate attention. 

With dbWatch database monitoring, everything is simple though – you don’t need another client to run queries to fix problems here or there – click the fourth icon from the top (that’s the SQL client) and use the client existing inside of dbWatch – this SQL client will help you run queries to fix problems while switching back to the monitor will provide you with information whether the queries you ran fixed the issue or not. While the SQL client is not perfect, it can help you execute your queries both as SQL and PL, load files to be ran (think of loading SQL files with a bunch of statements, etc.) – do keep in mind that this feature can be useful both in live environments and for testing, since these features in a testing environment will help you test whether you configured settings correctly, etc.: 

dbWatch Control Center is a great fit whether you’re deploying databases locally or in a live environment – as already noted before, the main features of the dbWatch Control Center will help you run database-related jobs, while the SQL client will let you run queries to fix any issues you encounter on the way.

Most clients see an impact in their organization withing the first week of using the tool. Remember whether you’re deploying database instances in a local or in a live environment: use the guidance outlined in the documentation.

Discover the impact dbWatch can make on your organization, download a trial version of dbWatch that monitors up to 5 instances for 6 months.