Introduction
The hospital’s business system is a typical OLTP + OLAP application. Due to the special nature of medical services, there are high requirements for system performance and availability. The hospital business system must operate continuously 7×24 hours, and it is highly sensitive to response time. System downtime cannot affect medical services or patient care, nor can it lead to medical disputes.
Current Situation
The hospital business system mainly includes HIS, PACS, LIS, etc. All systems rely on database support. Typical applications are mainly Oracle, SQL Server, and a small amount of DB2 and Caché. To achieve high availability of databases, Oracle can adopt the mature RAC method. DB2 and Caché are less commonly used, while the widely used SQL Server database lacked a satisfactory solution before the release of SQL Server 2012 (only similar to subscription publishing and Windows failover clustering methods), making it difficult to achieve multi-replica load balancing.
When using the traditional Windows failover clustering method (HA), the database server is in a primary-backup state. One host works while the other is in standby mode, leading to insufficient utilization of hardware resources. Due to shared storage, there is only one copy of the database, resulting in high read and write pressure, and IO performance can easily become a bottleneck affecting business efficiency. This is particularly evident during peak business periods, where speed slows down, and there is only one copy of the database, posing risks.
Design and Implementation of High Availability Database under All-Flash Virtualization Architecture
1. Design Plan
All-flash storage has now entered large-scale applications. Due to the inherent characteristics of all-flash media, it can achieve extremely high IOPS and very low latency. Additionally, the new native all-flash storage has built-in online data deduplication and compression features, making it very suitable for relational database application scenarios, effectively balancing efficiency and cost.
Server virtualization technology is also very mature now, with various solutions available. Server virtualization technology can fully utilize the computing performance of X86 servers, allowing one server to create multiple virtual machines, and deploying virtual machines is very convenient. Multiple servers can form a cluster, and virtual machines can drift across different servers.
SQL Server 2012 introduced the AlwaysOn high availability feature, which allows multiple online replicas of a single database to run simultaneously (one primary, multiple replicas), conveniently achieving read-write separation and load balancing. Additionally, when the primary database goes down, the replicas can automatically take over, effectively achieving high availability of the database. On the other hand, SQL Server 2012 AlwaysOn high availability utilizes Windows failover clustering, resulting in a tightly integrated system that is easy to deploy. The design architecture diagram is shown in Figure 1.
Figure 1 Design Architecture Diagram
2. Implementation
Multiple hosts use VMware vSphere virtualization to create multiple virtual machines (Windows 2008 R2). A domain controller is set up, and three Windows 2008 virtual machines form a Windows failover cluster, with SQL Server 2012 database installed and configured for AlwaysOn high availability.
The process of setting up the virtual environment is very common and will not be introduced here. The steps to implement AlwaysOn high availability are briefly described as follows.
The primary server SQL1, auxiliary server SQL2, and auxiliary server SQL3 are set up as follows: install failover; join the domain; create a cluster; change the SQL service account to log in with a domain account; add domain account login for SQL login name and configure AlwaysOn.
Since both SQL and Windows systems are core Microsoft systems, they can integrate seamlessly, and when configuring AlwaysOn, it will automatically find the hosts in the failover cluster.
There are a few points to note when setting up AlwaysOn high availability: the database needs to be in full recovery mode and have historical backups; when specifying replicas, select automatic failover/readable databases; when adding the AlwaysOn application listener, choose a floating IP.
Additionally, there is a common misconception that AlwaysOn can automatically achieve load balancing, which is incorrect. The actual situation is that read-write separation functionality requires corresponding settings in the application, such as directing reports and queries to operate in the database replicas while write operations are executed in the primary database.
3. Characteristics of the Plan
Since this plan’s application environment is pure database service, the requirements for response latency are very high, especially under high load, where querying large data cannot affect normal business operations. After diagnosing the system, it was observed that the bottleneck was not in computing performance, as CPU usage was low; the main factors affecting system efficiency were database concurrency performance and storage read/write speed. Additionally, the design of the application program often caused database blocking.
In response to these characteristics, this plan chose EMC’s all-flash storage XtremIO and utilized VMware virtualization technology to fully leverage the computing power of the current X86 server Xeon V3 processor, establishing a SQL AlwaysOn high availability environment. Due to the built-in online data deduplication and compression features of all-flash storage, three database replicas only occupy storage space equivalent to one copy. Because of metadata reuse, database synchronization does not require copy time, which is the biggest difference from traditional disk storage. Similarly, the three virtual machines, due to having the same operating system, are similar to three images and can achieve high deduplication. In a server virtualization environment, multiple virtual machines running concurrently present random read/write to shared storage, requiring high IO performance for random read/write. The high random read/write IO performance of all-flash storage also meets business requirements.
The storage management interface screenshot is shown in Figure 2, where it can be seen that through deduplication and compression, a data reduction effect of 3.9:1 can be achieved, and the read/write IO bandwidth exceeds 900M/S.
Figure 2 Storage Management Interface
During the implementation of this project, the HIS application program was also upgraded, and read-write separation settings were made, specifying that only read-only operations such as reports and queries are executed in the database replicas while write operations are executed in the primary database. As a result of setting up read-write separation and optimizing the application program, database blocking has been basically eliminated. The system’s running speed has significantly improved, and report query times have decreased by more than ten times.
Conclusion
By utilizing the latest technologies in virtualization, all-flash storage, and databases, we can fully leverage the performance of both hardware and software, achieving maximum returns with limited investment and saving on TCO.
