So, you've decided to install an Atlassian server application, but what database should you use? Selecting the right database is an important decision, and if you change your mind, switching databases later can be costly and difficult.
Let's start with the basics; first ask what databases are supported by the application(s) you'll be using? In the case of the Atlassian Stack, your support options are limited to three main relational databases:
- PostgreSQL,
- MySQL, a
- Microsoft SQL Server...
Oracle Database is also supported by some Atlassian applications, for now, but Atlassian is phasing out support for all of their applications by the end of 2020, so we'll ignore that one.
We know what databases are supported now, but what are the differences between them?
From a quick glance they all seem pretty similar; they all support running on the major operating systems (Linux, Windows, and Mac OS), they all store data in tables, they all use indexes to sort the data & help with performance, and they all allow you to host several databases from the same server. However, when you get into the details, we can see there are some pretty noticeable differences and these can make an impact in your decision.
1. Cost
The primary difference that most will notice right away is the software cost. PostgreSQL & MySQL are both open-source, free software; while Microsoft SQL Server requires a paid license for the software.
2. Knowledge and Accessibility
Outside of cost, the next biggest difference is the knowledge & accessibility you (or your IT staff) may have with these applications. Database management likely isn't going to be a part of your everyday work, but it will be important from time to time, and having some comfort level working within your database can be highly valuable. On average, most IT professionals have had more experience working with MySQL & PostgreSQL over Microsoft SQL Server; mainly because of the free vs. cost aspect, but also because PostgreSQL & MySQL are much more widely across a variety of applications, while Microsoft SQL Server is primarily used in Windows environments. Also, the accessibility to knowledge online should be taken into account. While you will find knowledgeable help online for all of these, you're much more likely to find the answers you're looking for easier & much quicker when you're look at MySQL & PostgreSQL; again, because most IT professionals have had more experience working with these.
3. Configurations
Another big difference, especially when looking at the Atlassian Stack, is the configurations you'll need to use when setting these databases up. PostgreSQL will work with the Atlassian Stack with little to no changes needed, you mostly just need to make sure to use the right encoding & collation types for your application. Microsoft SQL Server does require you to download & install the appropriate Microsoft JDBC driver for your database version, also Express Editions of Microsoft SQL Server will not work at all; and of course, you still need to make sure to use the right encoding & collation types for your application. MySQL also requires you to download & install the appropriate MySQL JDBC driver for your database version, the database should be running in strict mode, it requires using the InnoDB engine, and you cannot use the MariaDB nor PerconaDB variants of MySQL.
Now that we know some of the key differences between the databases, which is best for you and why?
First & foremost, the database you choose should be the one that your DBA's (or other supporting teams) are most comfortable using; after all, these are the folks that will be required to set things up, and fix any database related issues you may experience, so using the option they best understand will go a long way towards keeping your applications running well. But, if there is no clear winner, both Atlassian & Contegix recommend using PostgreSQL; it is what Atlassian uses in the development of their applications, and it requires the least amount of configuration changes needed to get things working right away. If PostgreSQL is not an option, we often recommend MySQL; as despite its configuration requirements, it's generally easy to work with, and it has been widely adopted throughout the IT community so finding answer/help to any issues you may come across is fairly easy. As for Microsoft SQL Server, we tend to steer our clients away from this option; not only does it cost extra money to license the software, and requires some special configuration needs, but it also tends to have the most problems based on real-world experiences from us & many others mentioned online.