Currently the major relational databases used by businesses are SQL Server, Oracle, PostgreSQL and MySQL, with the first two being proprietary code and the last two being free with the open source available. These databases are products that can be used for, among other tasks, store and retrieve data to applications that interact with them such as ERP systems, web sites and intranet applications, point of sale (POS) and others.
Due to market pressure and its users these four different databases provide similar features. Perhaps the main feature that is supported by the four products is the SQL language, which is implemented slightly differently in each product. Anyway, implementation and support for many common tasks eventually becomes a commodity and often the IT professional needs to know to perform the same task in different databases. This type of situation is common in mixed database environments, i.e. companies where that have installations of SQL Server, Oracle, PostgreSQL and MySQL living together.
Although you can perform common tasks in different databases, each product requires a different level of experience, e.g. there are different levels of difficulty to perform a similar task in SQL Server, Oracle, PostgreSQL and MySQL. Based on this context this article aims to present a comparison of difficulty to perform similar tasks in these databases. This comparison can help those who are beginning to work with a specific database and also provide a valuable guide for anyone who is evaluating the need to perform a task in a mixed database environment. The paper presents what are the criteria for the comparison, the context, what activities are compared and also an overview of how to perform each activity used in the comparison.
Definitions and details of the comparison
Before starting the comparison of the difficulty to perform common tasks on the four major databases we must define some concepts and also explain relevant details to the comparison. The first important feature that should be mentioned is the subjectivity. This means that every certainty will result in subjective ratings ranging from those made the comparing since the realization difficulty varies from person to person. For example, a group of people may find it difficult to build a clustered SQL Server due to various reasons such as lack of experience, availability of resources, difficulty in understanding technical details, etc. On the other hand, some people may find that the assembly of this cluster is somewhat easier due to factors such as the detailed knowledge, the number of times they have repeated the task, the ease in understanding the steps of the task, among others.
This means that in order to understand the comparison we must have a reference point. In this paper the comparison and the assignment of the degree of difficulty of the tasks were specified through the eyes of a junior DBA. This allows to state that the degree of difficulty was pointed out by someone who is starting in the area and just recently learned to perform the task being assessed. Thus, it is possible to have a viewpoint that covers most people who want to know how complex and difficult it is to perform a certain task in a database without going into details of the task itself.
Another important detail of the comparison is that the tasks are not always equal, since each database has unique features and aspects that differentiate them from others. To simplify this, we consider tasks whose results are similar and involve the same concepts. Additionally, tasks compared achieve something tangible and that is part of the tasks of day-to-day life of a DBA and developers who work with development such as backup, importing and exporting files, creating objects in the database data, etc. Although the comparison explain the main steps, commands or tools used we do not provided specific details of how to perform the task itself, i.e. the comparison will focus on aspects that can generate more or less difficult for those who are evaluating.
The comparison of difficulty employed SQL Server 2008 R2, Oracle 10g Release 2 (10.2), MySQL version 5.5 and version 8.4 of PostgreSQL. There is no distinction of operating system and platform, since the difficulty in performing the tasks compared rarely depends on specific features of these elements. The editions of the databases (Express, Enterprise, Developer, etc) were not detailed because the comparison took into account only the difficulty in performing the functions of the independent version of the database. Only official resources were considered, i.e. third-party tools does count in the comparison. However, in some cases it was necessary to consider open source projects sufficiently popular to be considered as a feature of the database itself.
As previously mentioned, the criterion for the comparison is the difficulty. The possible values for this criterion are: Easy, Medium and Hard. These values represent, respectively, the gradual assessment of little difficulty, moderate difficulty and high difficulty from the point of view of the junior DBA whose reference is adopted in the comparison. To facilitate the assignment of the comparative values of difficulty for each task and database we choose icons that represent the values Easy, Medium and Hard, as Table 1 shows. The choice of only three comparative figures is based on the facility to evaluate and understand the difficulty of performing each task under the eyes of a junior DBA.
Table 1. Icons representing the degree of difficulty for each task evaluated.
Each task that will be compared is quickly explained according to the main steps for its implementation in each database. Then you will see a table that indicates which is the degree of difficulty of the four databases for task execution in the order Oracle, SQL Server, PostgreSQL and MySQL. We highlight that the assessment focuses on the degree of difficulty encountered and not the amount of features or its characteristics. For details and specific steps to perform a given task in a database we encourage the reader to search by task name in the official documentation of the desired database.
Backup and Restored
The Backup and Restore task refers to the generation and restoration of a copy of the database using tools and commands of the database itself. In this comparison only dump backups were evaluated.
Oracle has several backup options and the RMAN tool is the recommend tool adopted officially by Oracle. Using RMAN requires deep knowledge about the types of data, permissions, files and backup options. In SQL Server you must use BACKUP and RESTORE commands, as well as learn the concepts of backup device, backup types and options regarding the location of the files. In MySQL just run the mysqldump command in the console to get a full backup of all objects and data in a text file that contains the SQL statements needed to create the objects and their data. PostgreSQL follows the same line of MySQL using the command pg_dump to simple backup simple without many parameters. A comparison of the level of difficulty to the task Backup and Restore is presented in Table 2.
Table 2. Comparison of the level of difficulty to the task Backup and Restore.
In part two we will look on Cluster High Availability and Replication