Softpoint Data Cluster for OLTP systems
Introduction to the database servers clustering (background, objectives)
OLTP systems is a specific IS type, i.e. transaction information systems characterized by requirements to shorter response time from the DB server, higher reliability and data consistency. Usually, these systems process intensive transaction flows, and due to concurrent access for shared resources even a slight increase in processing times results in higher probability of locks.
Unlike other systems, e.g. OLAP intelligence reporting systems, horizonal scaling of OLTP systems is not a trivial effort due to the above requirements
- with new instances (servers in the DBMS cluster) the DB response times must be shorter or the same;
- all returned data must be consistent for any application communicating with the DBMS cluster (regardless of the instance that actually processed the query);
- any DB server added to the DBMS cluster must increase the total cluster perfomance;
- the implementation of DBMS cluster must not result in any modification both to the architecture and code of DB application.
Currently, the existing DB servers clustering solutions are often either adapted to OLAP systems (intelligence systems mostly with queries to read data, e.g. Microsoft PDW), or require significant investments and application architecture restructuring (with an increase in response time in some cases, e.g. in OracleRAC, MySQLCluster).
Meanwhile, hard or impractical horizonal scaling of DB servers for OLTP systems requires to add new separate DB instances, to replicate data and to maintain these technology processes while number of users and data/transaction flows increases. The article deliberately does not consider vertical scaling of DB server (HW upgrade).
Architecture of Softpoint Data Cluster for MS SQL
Fig. 1. High-level overview of SPC solution for MSSQL Server.
Core components of the Softpoint Data Cluster solution
- Softpoint Data Cluster Router (SDCR) is the main unit for the below functions:
— to forward the traffic of queries to DB servers,
— to control the data synchronization latency between MSSQL servers,
— to distribute queries between DB servers for load balancing,
— to ensure better failover recovery after failures of the network equipment and DB servers.
This unit may include multiple network interfaces (Infiniband, Ethernet) to distribute the network traffic according to the network and bandwidth utilization. For more reliability and performance of the Softpoint Data Cluster, several interconnected SDCRs may be installed.
- Softpoint Data Cluster Link (SDCL) is a service on the DB server and works as a gateway to the SDCR. The SDCL provides stable communication with the DB servers regardless of network failures between the SDCR and SDCL, and delivers data to the SDCR on current loads of DB servers for load balancing.
- Softpoint Data Cluster Console (SDCC) is a toolset to setup, manage and control all operations of the SDC.
The Always On technology (included into the Microsoft SQL Server Enterprise Edition 2012 and higher versions) is used for data replication between servers. The replication runs in asynchronous mode (without affecting the response time), the SDCR ensures data consistency. Due to the AlwaysOn technology, only one DB server is appointed to be a master server with access right to modify data, other DB servers are slave with the only access to read data.
Key aspects of real technology implementation and utilization in packaged solutions
Horizontal scaling of DB servers in packaged solutions (e.g. Navision, SAP, iScala) is relatively rare, partly due to its complicated customization and adaptation to a specific application. Usually, a full scale utilization of such horizonal scaling requires restructuring of application architecture with further code changes. Mostly, this is a complex, complicated and resources-consuming task (requiring a lot of financing, time, technology, and technical support).
Due to the above issue, the architecture of SDC solution was initially designed to be adaptable to any IS, i.e.: for horizonal scaling of SDC, no changes in application code are required; SDC supports virtually all popular data access drivers — ODBC, OLEDB, SQL Native Client etc.
For efficiency of application-based control over SDC technology (apart from the control via an SDC Console), we implemented a mechanism for specific command recognition in queries sent by an application. Thus, a standard data access driver in an application enables utilization of commands to forward queries to any specified DB server. In this mode, an application developer is able to bypass the automatic mode and to control load balancing for queries in the most efficient way.
Additional benefits of SDC technology
- Easy implementation
- Increase in performance
- Failover communication