Oracle 19c vs SQL Server 2019: Architecture Comparison
Introduction to Databases and Non-Relational Databases
Database applications have been developed to streamline the organization and retrieval of data and information for various purposes. Currently, there are two main categories of databases in use: relational databases and the increasingly popular NoSQL (Not Only SQL) databases. Relational databases are distinguished from NoSQL databases because the latter, as the name implies, do not utilize SQL (Structured Query Language) for managing and retrieving data from database systems, which is a language commonly used in database-dependent applications.
Initially, NoSQL stood for "non-SQL," indicating its departure from SQL-based approaches. However, over time, its meaning has evolved to "not only SQL," reflecting its broader scope that encompasses a wide array of diverse database architectures and data models. NoSQL databases also do not require the definition of a fixed schema and are designed to handle unstructured data, such as text, efficiently. In contrast, relational databases organize information in tabular format with rows and columns.
For this article, we explore relational database management systems (RDBMS), particular two common database applications; Oracle Database 19c (c indicates cloud) and SQL Server 2019.
Introduction to Oracle 19c Database
Oracle Database 19c was introduced to the market in 2019 and has significantly influenced the business landscape due to its efficiency and robustness. Nonetheless, it is often regarded as intricate, especially by small-scale users and developers working on modest applications. It's worth noting that the complexity of Oracle 19c is a result of its rich and powerful architecture, which, in turn, brings substantial advantages in terms of scalability and performance.
The Oracle 19c database system features Oracle Enterprise Manager (OEM), widely recognized as one of the most robust database enterprise management solutions available. OEM equips users with comprehensive tools for overseeing and maintaining Oracle databases. Much like its predecessors, this database software boasts a broad array of architectural elements addressing security, performance, backup and recovery, resource utilization, and seamless integration with other applications.
Introduction to SQL Server 2019 Database
The SQL Server 2019 database application was released to the market in 2019 by Microsoft Corporation. Historically, SQL Servers were designed to work best with Microsoft's Windows operating systems. However, SQL server 2019 was optimized for Windows-based systems and designed to run on other platforms, such as Linux versions, including Red Hat and Ubuntu.
Oracle 19c Database vs SQL Server 2019 Database
Both the Oracle 19c database software and the MS SQL Server 2019 are relational database seeing that both of these systems use database tables to store and retrieve information. Further, both systems use the structured query language (SQL) to store, retrieve and modify the information existing in the databases. In terms of architecture, these two systems share certain similarities while also exhibiting differences. For instance, they both employ memory management techniques, but their approaches may vary. Similarly, their physical architectures exhibit variances based on factors like storage management and query optimization.
A. Memory Architecture
MS SQL Server 2019 puts in place a robust memory strategy to enhance performance in the system. The SQL server dynamically acquires and frees memory as required. The database administrator does not have to specify memory allocation, though it is at times necessary to do so in some environments. One of the primary design goals of the database software is to minimize the disk I/O operations as they are the most resource-intensive operations. With this in mind, the SQL server uses a buffer pool to maximize on the performance of the database. The buffer pool in created in the memory to hold pages from the database. The server software:
Keeps the buffer pool from becoming so big that the entire system is low on memory
Minimizes physical I/O to the database files by maximizing the size of the buffer pool.
In general, the SQL Server 2019 software requires approximately 64GB (this can differ based on specific use cases) of physical memory for large databases. Memory in a windows system can be called using the Virtual Address Space, shared by the Kernel Mode and the User mode. The user mode is where the buffer pool is classified.
The buffer pool is further divided into a buffer cache and a procedure cache. A buffer cache holds the data pages in memory that frequently accessed data can be retrieved from. Further, a procedure cache keeps the stored procedure and query execution plans to minimize the times that query plans have to be generated. Just like in SQL Server 2019, the Oracle 19c database software has packed memory architecture to enhance performance while increasing efficiency. When an instance is started, the Oracle Database allocates memory area and starts background processes. Stored in this area is:
Program Code
Information about each session
Information needed during program execution, such as the query state
Information shared and communicated among processes, and
Cached data, such as data blocks and redo records
The Oracle memory structure is divided into memory areas:
System global area (SGA): contains data and control information for the database instance
Program global area (PGA): contains data and control information for a database process
User global area: is associated with a user session
Software code areas: stores code that is run or can be run
Just like the MS SQL Server 2019, Oracle 19c implements a memory management scheme. As noted with the SQL Server 2019 too, Oracle memory management is based on the automatic memory management or the manual memory management. However, unlike the MS SQL Server 2019, Oracle introduces the automatic shared memory which is partially automated. The Oracle database manages memory based on the settings of memory-related initialization parameters.
Manual memory management takes in much energy and requires extensive database management skills and expertise. Both database systems discourage manual management as this can cause an overflow if the correct sizes, including the buffer size are set below the required sizes. However, scenarios are presented that require a database administrator to set the memory target values. These scenarios include:
The database is running on a PC system with limited physical memory.
The database is running a very heavy workload.
The database needs to be tuned for a specific type of workload.
The database is running on a system with a complex hardware configuration.
B. Physical Architectures
In the Oracle 19c database software, the physical architecture is also known as the physical structures. These represent the hierarchy of the data files created by the oracle program and used by the oracle database to run. They are a set of files that store the oracle data in persistent disk storage. When a database is created using the ‘CREATE DATABASE' query, Oracle 19c database creates a set of files that helps maintain the database. These files are:
Data files and temp files: a data file is a physical file created by oracle and contains data structures such as tables and indexes. A temp file on the other hand is a data file that belongs to a temporary tablespace. These files are written to by the Oracle database software and cannot be read by other programs.
Control files: is a root file that tracks the physical components of the database
Online redo log files: a set of files containing records of changes made to the data.
These database files are largely managed by the database instance, which is a set of memory tasked with this role.
In Microsoft's SQL Server 2019, the physical architecture is somewhat different from Oracle's data file structure. The SQL Server 2019 structure is made up of:
Pages and extents: This describes the data storage architecture. Extents are re basic units in which space is allocated to tables and indexes.
Physical database files and file groups: which describes the set of operating-system files used to store data and log files
Space allocation and reuse: It describes the space allocation algorithms and data structures. It also provides the database admins and users with the skills required to understand messages generated by SQL Server 2019.
Table and index architecture: which describes the way the pages for tables and indexes are organized.
Filegroups are named collections of files and they are used to help data placement and administrative tasks such as backup and restore operations. The physical database files contained in SQL Server 2019 differ from the physical files for Oracle 91c database server. The types of file called during a database process in SQL Server 2019 are:
Primary data files: shows the starting point of the database and points to other files in the database. This file is noted by its extension. .mdf. In SQL Server, primary data files contain system table information and user data, but multiple data files can be used for user data, and one table can span multiple data files, especially in scenarios like partitioning.
Secondary data files: make up all the other files other than the primary file. A database has options to have none, one or several database files. The recommended file extension is .ndf.
Log files: These files holds all the log information required to recover a database. Each database should have at least one log file, which is noted by is extension, .ldf.
C. Logical Architecture
Logical structure describes how data is logically grouped and presented to the user. In Oracle 19c database software, this structures are created and recognized by the Oracle database and are not known to the operating software. The Oracle database uses a logical space to store data. The logical units of the database space are:
Data blocks: is a unit of the logical space in the data files. It is also the minimum unit of database I/O. Data blocks typically have a fixed size, which can vary based on database configuration.
Extents: this is a logical unit of the database storage space allocation made up contiguous data blocks. An extent is always contained in one data file.
Segments: it is a set of extents that contains data for a logical storage structure within a tablespace. User segments can be a; table, table partition or table cluster, index or an index partition.
Tablespaces: it is a logical storage container for segments. Whereas segments are database objects, a tablespace stores data in one or more data files or temp files.
In Microsoft SQL Server 2019, the logical architecture consists of table, constraints, views, stored procedures, functions, triggers etc. These are the key components.
D. Client / Server architecture
This is contained in the application structure of the database software. In Oracle 19c database, the client runs the database application such as the SQL*Plus that interacts with the database information and interacts with the user. On the other hand, the server runs the Oracle Database software and handles the functions required for concurrent, shared data access to an oracle database.
This architecture usually supports a multitier architecture. In a traditional multitier architecture, an application server provides data for clients and serves as an interface between clients and database servers. Just like in Oracle, MS SQL Server 2019 implements its own client / server architecture. In this architecture, the server is designed to work in a two tier and multitier environments.
In two-tier architecture, users run the application on their local computer, which connects over network to the server running the SQL Server 2019. This is also known as thick client as the client application runs both the business logic and the code to display output. Further, in multitier system, the thin client runs on the user's computer and is aimed at displaying results while the business logic is located in the server applications running on the server. This is similar for both the Oracle 19c database software and the Microsoft SQL Server 2019 database software.
