RDBMS Architecture | MySQL, MS SQL Server & Oracle

Compare RDBMS (Relational database management system) MySQL, SQL Server and Oracle Database Architecture, what are their components? Note the differences between them and why.

8 thoughts on “RDBMS Architecture | MySQL, MS SQL Server & Oracle

  1. mykh74

    MySQL, SQL Server and Oracle Database Architecture are widely used RDBMSs. According to DB-Engine Ranking (http://db-engines.com/en/ranking), Oracle has rank #1, Microsoft Sql Server – rank #2, and MySQL – rank #3. MySQL is open source RBDMS owned by Oracle. SQL Server is a reliable database developed by Microsoft that can be installed only on a machine with Microsoft Windows OS. Oracle is a enterprise-level proprietary database system which is considered as the most advanced and expensive widely used RDBMSs. MySQL, SQL Server, and Oracle RBMSs has many common features. All systems support ANSI SQL standards, partitioning, replications, foreign keys, triggers, stored procedures, ACID transaction concepts, concurrency control, and durability. Differences between the RBMSs are that each of them supports different dialects of SQL. For example, a command to select first n rows is different for all three db systems. MYSQL supports statement: “LIMIT n,” SQL Server pulls first n rows using statement: “SELECT Top n”, Oracle uses statement: “WHERE rownum <= 3.” Also, Oracle has its own scripting language PL/SQL (Procedural Language/Structured Query Language). Microsoft supports Transact SQL. Each of these RBMS fills its own niche on the market. MySQL fits well for projects with primary open source languages such as PHP or Python. SQL Server fits well for projects tied with Microsoft programming languages such as .Net. Oracle is RDBMS that can be used for highly scalable enterprise-level applications under mixed environment.

  2. manishekar

    The components of MySQL are the MySQL server, MySQL clients and utilities, SQL and MySQL data directory. MySQL server is the hub of MySQL that performs all manipulation of databases and tables. MySQL clients and utilities help in communicating with the server. The server’s language, SQL, helps us to talk to the server in its own language. MySQL data directory is where the server stores its databases and status files.

    In SQL, the client requests come into the server via SQL Server’s Net-Libraries. These requests are then schedules for processing via User Mode Scheduler (UMS). The language processing and execution (LPE) component within the server then takes each request and passes it to the query processor (QP) for optimization. Once a client query is optimized and an execution plan is produced for it, LPE executes it via calls from the relational engine to the storage engine (SE). The storage engine carries out the physical I/O, table and index traversal, data retrieval, and so on necessary to carry out the request from the relational engine. The communication between the LPE and SE components occurs via COM using calls to OLE DB interfaces.

    The major components or Oracle are the files, memory structures and physical processes/threads. The files can be parameter, data, temp, control, and redo log files. The memory structures are referred to as the System Global Area (SGA), Process Global Area (PGA), and User Global Area (UGA). The processes could be server processes, background processes, and slave processes.

    MySQL is open source architecture. It supports only partial support for Unicode whereas SQL and Oracle provide complete support for Unicode. MySQL has maximum row size of 64KB. The maximum column size is 4096. My SQL does not support intersection and parallel query. It does not support security certificate and table locking capability. It supports more types of tables. They are used mainly by web applications. One of its major disadvantages is that it lacks add-on possibilities. MySQL does not support user-defined functions, triggers, cursors, stored procedures, full join, and import/export capabilities. It has limited transaction support. It requires very less disk space and has poor recovery feature.

    SQL is closed source software. SQL server has a maximum database size of 524258 terabytes. SQL server has unlimited row size. The maximum column size is 30000. SQL does not support list and hash function. SQL requires more disk space and supports extensive foreign key features. It has an efficient recovery mechanism.

    Oracle is used by companies for enterprise products. Oracle has maximum row size of 8KB. The maximum column size is 1000. Oracle is an expensive software for higher end capabilities. Oracle has the auditing capability, better user management and better security. Oracle has row-locking feature. Supports optimized algorithms for concurrency, query and storage. Oracle supports advanced security features. Oracle provides more flexibility for programming languages.

  3. Vinay Venkatachala

    MySQL, SQL server, Oracle have similar architecture if not the same.

    MySQL has certain limitations like row size, column size restrictions. It is one of the very lightweight database. Server, Client and Utilities form the major components of this database. Files are stored in the data directory of the MySQL installation by default.

    SQL Server from Microsoft provides advanced capabilities compared to MySQL. Row size is unlimited. However, the column size is limited to 30000. This software is more disk expensive comapred to MySQL. The recovery mechanisms are efficient and reliable. User Mode Scheduler, Language Processing and Execution, Query Processor form the major components of SQL server.

    Oracle is another database providing advanced capabilities like SQL server. In Oracle, memory components are-
    1. System Global Area
    2. Process Global Area
    3. User Global Area

    User management and security is well handled in Oracle. Several optimizations are available for query and storage.

  4. rahul

    MySQL is an opensource database with components including connection pool manager, query processor , transaction management , recovery management, storage management. Applications and interfaces include administrative interfaces and utilities, client interfaces and a query interface (mysql).The query processor includes DDL compiler, DML precompiler, query parser,security and integration management, query optimizer and execution engine. Transaction management includes a concurrency-control manager and transaction manager. Recovery management includes log manager and recovery manager. Storage management includes resource manager,buffer manager and a storage manager. This architecture is based on simplicity of design, the use in mostly Linux machines using apache and php/perl/python.
    SQL server is provided by Microsoft and supports interfaces to Microsoft .NET framework in addition to xml, html, GUI, API and command prompt. SQL server allows access to the server from clients using Tabular Data Stream(TDS) which in turn can be over TCP/IP, named pipes or shared memory. It supports the storage of binary data as BLOBs. SQL server includes some additional components like administration APIs like SQL Distributed Management Objects(SQL-DMO), Decision Support Objects (DSO) and Windows Management Instrumentation (WMI). It also includes a component called English query, a Meta Data Services along with an interface called MDC Open Information Model (OIM) specification which can be used to communicate with different components. This architecture is basically to support the Microsoft service framework and also to support different services like online transaction processing (OLTP) and online analytical processing(OLAP)
    Oracle database consists of a System Global Area (SGA) consisting of database buffer cache, redo log buffer and a shared pool of library cache and data dictionary cache which is started as a service (background process) and all the clients will connect to. The other components like process monitor, database writer, process monitor, log writer etc. will be started as background process. This architecture is based on the specialization of products. Oracle offers different database options and features and they can be achieved by starting a different process attached to the SGA based on license.

  5. ramya

    SQL Server Architecture:
    The major components of SQL Server are:
    1. Relational Engine
    2. Storage Engine
    3. SQL OS
    Now we will discuss and understand each one of them.
    1) Relational Engine: Also called as the query processor, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.
    Different Tasks of Relational Engine:
    1. Query Processing
    2. Memory Management
    3. Thread and Task Management
    4. Buffer Management
    5. Distributed Query Processing
    2) Storage Engine: Storage Engine is responsible for storage and retrieval of the data on to the storage system (Disk, SAN etc.). to understand more, let’s focus on the concepts.
    When we talk about any database in SQL server, there are 2 types of files that are created at the disk level – Data file and Log file. Data file physically stores the data in data pages. Log files that are also known as write ahead logs, are used for storing transactions performed on the database.
    Let’s understand data file and log file in more details:
    Data File: Data File stores data in the form of Data Page (8KB) and these data pages are logically organized in extents.
    Extents: Extents are logical units in the database. They are a combination of 8 data pages i.e. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents are dedicated to only one type (object).
    Pages: As we should know what type of data pages can be stored in SQL Server, below mentioned are some of them:
    • Data Page: It holds the data entered by the user but not the data which is of type text, ntext, nvarchar(max), varchar(max), varbinary(max), image and xml data.
    • Index: It stores the index entries.
    • Text/Image: It stores LOB ( Large Object data) like text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data.
    • GAM & SGAM (Global Allocation Map & Shared Global Allocation Map): They are used for saving information related to the allocation of extents.
    • PFS (Page Free Space): Information related to page allocation and unused space available on pages.
    • IAM (Index Allocation Map): Information pertaining to extents that are used by a table or index per allocation unit.
    • BCM (Bulk Changed Map): Keeps information about the extents changed in a Bulk Operation.
    • DCM (Differential Change Map): This is the information of extents that have modified since the last BACKUP DATABASE statement as per allocation unit.
    Log File: It also known as write ahead log. It stores modification to the database (DML and DDL).
    • Sufficient information is logged to be able to:
    o Roll back transactions if requested
    o Recover the database in case of failure
    o Write Ahead Logging is used to create log entries
     Transaction logs are written in chronological order in a circular way
     Truncation policy for logs is based on the recovery model
    SQL OS: This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. It is a highly configurable operating system with powerful API (application programming interface), enabling automatic locality and advanced parallelism. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure. Other services include exception handling, hosting for external components like Common Language Runtime, CLR etc.
    MySQL architecture:
    The MySQL architecture is broken down into three layers: an application layer, a logical layer, and a
    physical layer. These three layers have their own functionalities:
    • The application layer deals with Query processing ,
    • the logical layer carries out operations like transaction management and recovery management
    • the physical layer deals with storage management.
    My Sql works in client/server as well as Embedded Systems.
    Application Layer
    • Users and clients interacts with the MySQL RDBMS.
    • There are three components in this layer.
    1. Administrators
    2. Clients
    3. Query Users
    Administrators use various administrative interface and utilities like mysqladmin, isamchk etc.
    • Clients communicate with the MySQL RDBMS through various interfaces and utilities like the MySQL APIs.
    • Query users interact with MySQL RDBMS using “mysql”.
    • “mysql”is actually a query interface. It’s a monitor that allows users to issue SQL statements and view the results.
    Oracle Database Architecture:
    An Oracle database system—identified by an alphanumeric system identifier or SID[4]—comprises at least one instance of the application, along with data storage. An instance—identified persistently by an instantiation number (or activation id: SYS.V_$DATABASE.ACTIVATION#)—comprises a set of operating-system processes and memory-structures that interact with the storage. (Typical processes include PMON (the process monitor) and SMON (the system monitor).) Oracle documentation can refer to an active database instance as a “shared memory realm”.[5]
    Users of Oracle databases refer to the server-side memory-structure as the SGA (System Global Area). The SGA typically holds cache information such as data-buffers, SQL commands, and user information. In addition to storage, the database consists of online redo logs (or logs), which hold transactional history. Processes can in turn archive the online redo logs into archive logs (offline redo logs), which provide the basis (if necessary) for data recovery and for the physical-standby forms of data replication using Oracle Data Guard.
    If the Oracle database administrator has implemented Oracle RAC (Real Application Clusters), then multiple instances, usually on different servers, attach to a central storage array. This scenario offers advantages such as better performance, scalability and redundancy. However, support becomes more complex, and many sites do not use RAC. In version 10g, grid computing introduced shared resources where an instance can use (for example) CPU resources from another node (computer) in the grid.
    The Oracle DBMS can store and execute stored procedures and functions within itself. PL/SQL (Oracle Corporation’s proprietary procedural extension to SQL), or the object-oriented language Java can invoke such code objects and/or provide the programming structures for writing them.

  6. leekris

    The following are the major components of Oracle Database Architecture, MySQL and SQL Server

    Oracle Architecture – components:
    • Memory structure: SGA, PGA
    • Process structure (background processes)
    • Storage structure (disk utilization) – physical (data files, segments, extents, blocks) and logical (table spaces, tables/clusters/indexes, rows, columns)

    The SGA is used to store various components of memory usage that are designed to execute processes to obtain data for user queries as quickly as possible. Also to maximize the number of users accessing the oracle instance in parallel. It consists of the following items
    a. Buffer cache
    b. Shared pool
    c. Redo log buffer

    The PGA is an area in memory that helps user processes execute, such as bind variable information, sort areas, and other aspects of cursor handling.

    Following are various background processes –
    • DBWR – Database writer process
    • LGWR – Log writer process
    • SMON – System monitor process
    • PMON – Process monitor process
    • RECO – Recoverer process
    • ARCH – Archiver process
    • CKPT – Checkpoint process
    • LCK0 – Lock Process
    • LCK9 – Parallel server option
    • S000 – Server process
    • D001 – Dispatcher process
    • D999 – to connect user processes to shared server processes that will handle their SQL processing needs.

    My SQL Architecture – components:
    Connection pool, SQL Interface, Parser, Optimizer, cache & buffers, pluggable storage engines, Files & logs, ERPM services and utilities.

    At heterogeneous level, Application layer comprises of administrators, clients and query users. Logical layer comprises query processor, transaction management, recovery management, storage management. Query processor includes Embedded DML Precompiler, DDL Compiler, Query Parser and Preprocessor, Security Manager, Query Optimizer, Execution Engine.
    Transaction management includes Lock manager, Recovery management, Log manager, Recovery manager, Storage management, Storage manager, Buffer manager, Resource manager

    SQL Server Architecture – components:
    Below is the component structure of SQL server –
    Relational Engine
    Query processing
    Memory management
    Thread and task management
    Buffer management
    Distributed query processing
    Storage Engine
    SQL OS

    Oracle, MySQL, SQL Server have few features in common however each has its own benefits. Oracle has real application clusters which are a powerful way to make database clusters. Oracle offers lots of features and functionality for solving complex problems and is suitable for OLTP and VLDB’s. The Oracle architecture supports advanced server features, such as record locking with versioning, data replication, advanced query optimization, the PL/SQL programming language, distributed database management, and other important features.

    MySQL is an open-source relational database management system. It uses client/server architecture and is a multi-threaded, multi-user database server. It was designed specifically to be a fast server and hence does not provide many of the features provided by other relational database systems, such as foreign keys, referential integrity, subselects, stored procedures. MySQL has its own share nothing cluster storage engine. MySQL is relatively light weight and can be fast when applications leverage its architecture.

    SQL server relies on Microsoft clustering technology. SQL Server is designed to work as a server in a client/server network and it is also capable of working as a stand-alone database directly on the client. SQL Server offers vertical scalability and offers better performance with medium to large databases. The scalability, ease-of-use features of SQL Server allow it to work more efficiently on a client without consuming too many resources.

  7. Niki

    MySql: Database Instance stores global memory in Mysql background process. User sessions are managed through threads.(2) Made up of database schemas. Each storage engine stores information differently.
    Binary logs are used for point-in-time recovery(3)InnoDB and upcoming Falcon and Maria storage engines (4) No online backup built-in, Replication, OS Snapshots, InnoDB Hot Backup.
    Oracle: Database instance has numerous background processes dependent on configuration. System Global Area is shared memory for SMON, PMON, DBWR, LGWR, ARCH, RECO, etc. Sessions are managed through server processes.(2)Uses tablespaces for system metadata, user data and indexesRedo and archive log files are used for point in time recovery.
    (3)Regular and Index only tables support transactions.(4)Recovery Manager (RMAN) supports hot backups and runs as a separate central repository for multiple Oracle database servers.
    A SQL Server instance refers to a functional installation of the product with all appropriate binaries and allows the creation of multiple user databases, each with its own hierarchy of objects and settings. When someone refers to a SQL Server instance, he or she is typically referring to the installation along with the memory and other server resources it can consume. A database in SQL Server can mean system or user database and this is where the actual data is stored. In contrast, Oracle users are generally referring to the physical files when they mention database

    The database server of Microsoft SQL Server has two main parts; the relational engine (RE) and the storage engine (SE). One of the most important architectural changes made in SQL Server version 7.0 is to clearly separate the relational and storage engine components within the server and to have them use the OLE DB API to communicate with each other.

  8. sthiaga1

    The MySQL, SQLServer and Oracle database architecture belongs to the Relational Database management System. The SQL server is been developed by Microsoft and the MySQL is an open source database which is developed by Oracle, which is considered to be one of the expensive database. All these databases are widely used RDBMS. The SQL server can be used in the Windows Operating system whereas; the SQL server and the Oracle database can be used in the Operating systems which includes Windows, FreeBSD, Solaris, OS X. SQL server targets the MS platform which is easy to use and allows remote access. With MySQL the access to administration program is obtained and the access to the query browser is obtained as a GUI tool. SQL server also allows reporting services such as services portal, and OLAP engine and an ETL framework. The Oracle database is complex and is appropriate for use by database administrator. It contains a web server too. MySQL is cheaper and very much user friendly to use it.
    Apart from these differences, there lies certain similarities – they use the same database schema, Secondary indexes and SQL in these databases. All these databases use ADO.NET, JDBC and ODBC as their API’s. Apart from this, MySQL uses OLE DB API too. The access controls are given to users with fine grained authorization concept in all these databases. SQL server and Oracle DB server are one of the top three commercial DBMS.

Leave a Reply