一个SQL server中间件介绍 - DBx™ SQL Server Cluster Middleware

大家都知道数据库系统也是所有服务器中最难保护，升级和维护的。

由于日益增长的在线应用，数据库系统弱点已经成为了企业信息系统的瓶颈。究其原因，当前各种各样的解决方案无外乎在下列三大方面无法取得同步的进展：

1） 数据库数据可*性 （数据冗余度）

2） 数据库系统的性能  (交易处理的速度)

3） 数据服务的可用性 （不间断服务）

当前所有的数据库系统集群解决方案要么通过牺牲速度来得到数据冗余(定时复制/恢复、窜行连续复制、或者干脆停机拷贝)，要么通过牺牲系统可*性来得到速度(应用级集群、应用级数据分区)。到目前为止，市场上还没有一个在上述三方面同时取得进展的真正的数据库集群解决方案。

具体来讲，当前数据库系统存在下列各种问题：

1．  企业只有一份实时生产数据集，一旦主数据库系统发生故障，将导致业务中断，数据恢复很麻烦，甚至丢失部分数据。

2．  目前的实时数据备份采用主备机串行复制数据的方式，该方式延长了业务处理的时间，降低了系统的工作效率，增大了系统出错几率。

3．  在实际的工作中，数据库系统宕机事件时有发生，无法完全实现7X24小时不停机服务。

4．  在实行连续异步数据备份的数据库系统中，备份数据库系统的资源被极大地浪费了。

5．  现有数据库系统的升级方式多为向上扩展方式，即： PC服务器->小型机->大型机的升级方式，既不经济，又缺乏伸缩性，同时上述各缺点同样存在。

6．  数据库系统属于磁盘密集型操作，目前的数据库集群系统解决方案采用多处理器单数据库(无冗余)，在应对大规模、频繁访问时（如电子交易网站等），既无法提高处理速度又增加宕机的几率。

经过分析发现每种数据库的复制实现技术不同：

Mysql :               Mysql Proxy   -    http://dev.mysql.com/downloads/mysql-proxy/

SQL server:      DB^x™             -    http://www.pcticorp.com/products/sql-server-cluster-middleware

Oracle:              Streams         -    http://www.oracle.com/technetwork/database/features/data-integration/index.html

下面将介绍美国出产的一款新的软件-DB^x™中间件可以将上述的问题轻松化解：

DB^x™ is a custom SQL Server® database solution. It is designed to increase the performance and availability of SQL Server ® Database clusters. It was designed to tackle three fundamental issues in database industry:

1) poor performance scalability
2) increasing planned service downtimes; and
3) high cost of disaster prevention.

Using DB^x™, all updates are synchronously replicated while the read-only queries are load balanced. This can substantially improve database server performance. DB^x™ comes with an automatic dataset resynchronization tool which can greatly reduce or totally eliminate planned downtime. With DB^x™, the promise of a proven, industrial strength, high availability and high performance replication solution is finally here.

 

How DB^x™ Works:

DB^x™ is an industrial-strength intelligent database packet processor (gateway). It resides on the network path between database clients and a cluster of database servers. The system will run on a dedicated appliance or as a service co-locate with a SQL Server®. It can be protected using built-in IP-takeover to eliminate the single-point-failure. For most applications, deploying DB^x™ is a plug-and play operation. Cluster configuration and monitoring are done through a Windows-based Control Center.

 

 

Zero-Loss Continuous Replication with Performance Boost

Transaction replication is the only guaranteed method against potential data losses. Transaction replication is difficult and costly. Up-scaling of SQL Server performance is also difficult if real transactions (not only for BI) are involved.

PCTI's breakthrough synchronous parallel transaction engine was designed to address both problems.

Dynamic Serialization and Load Balancing

Dynamic serialization solves the replication performance problems. There are two crucial aspects: In-Flight Transaction Serialization for replication consistency with minimal overheads and In-Flight Transaction Load Balancing for scalable performance.

 

 Using this technology, concurrent update transactions are dynamically serialized, read-only queries are automatically load balanced when passing through DB^x™ gateway. This ensures replication data consistency by enforcing the exact commit orders of serialized transactions on multiple standalone SQL Servers. Concurrent read-only queries are distributed to individual SQL Servers to boost performance.

Here are the specific rules for dynamic serialization:

1. INSERT (concurrent INSERTs are serialized only if insertion order is important)
2. DELETE (concurrent DELETEs are serialized only if deletion order is important)
3. UPDATE (concurrent UPDATEs are serialized only if they update the same row)
4. SELECT are load balanced across all target SQL Servers. The use of more SQL Servers in the cluster improves the performance. Load balancer's heuristics automatically takes care of load sharing via WAN connected SQL Servers.

Since most transactions are read-only and most concurrent updates do not need serialization, higher performance and higher availability can be achieved at the same time.

High Performance Synchronous Transaction Replication

Traditional two-phase-commit (2PC) protocol requires sequential transaction applications and immediate rollback if any error occurs. This design has negative impact on both transaction performance and availability.

PCTI introduces 2PC with parallel transaction application and non-stop batch resynchronization. Transactions are applied to multiple SQL Servers in parallel. Servers with inconsistent and inferior results are disconnected from the cluster. They must go through the non-stop batch resynchronization process before resuming service. This technology ensures consistent datasets on all replication targets at all times.

 

 

 This technology allows continuous protection with zero transaction loss regardless of SQL Server and DB^x™ gateway failures. It allows dynamic expansion and contraction of a SQL Server cluster without extended service downtimes; any SQL Server can be taken offline for repair or service pack application with little impact on service availability. New servers can also be added when the need arises.

Key Features:

ZERO TRANSACTION LOSS CONTINOUS PROTECTION
Our DB^x™ gateway replicates every transaction to multiple standalone SQL Servers® synchronously and in parallel. Therefore it offers very high replication speed. Out-of-sync SQL Servers® can be seamlessly re-synchronized without shutting down service. Therefore, it guarantees zero transaction loss with continuous protection permitting multiple SQL Server® and DB^x™ gateway failures.

HIGH PERFORMANCE
DB^x™ can automatically detect and distribute read-only queries and distribute them to the least loaded SQL Servers®.

NON-STOP CLUSTER SERVICE
DB^x™ SQL Server® cluster allows taking any individual server offline for repair and resynchronization without shutting down cluster. This not only allows drastic reduction in planned downtimes (for service pack installs) but also hot-online cluster expansion and contraction.

PROACTIVE MONITORING AND SECURE DATA VAULT
DB^x™ partitions your network into two physically separate networks: client-side and server-side. It constantly monitors the health of all database servers by looking for telltale signs of failure. It also monitors all incoming network traffic. Only queries from legitimate sources are to be accepted.

ZERO SINGLE POINT OF FAILURE
DB^x™ guarantees full hardware and software redundancy with multiple real time synchronized copies of data sets. Each DB^x™ gateways can be protected by IP-takeover for LAN or DNS-takeover for WAN.

SERVER CONSOLIDATION FRIENDLY
DB^x™ can be installed as a network appliance or run as a service sharing the same hardware with SQL Servers®.

Current Offerings:

1. Standard Edition - 200 or fewer concurrent connections, supports up to 4 servers in a cluster.
2. Enterprise Edition - 500-6000 concurrent connections, supports up to 16 servers in a cluster.

System Requirements:

Operating System: Windows NT/2000/2003 Servers, Standard or Enterprise Editions.
Database Support:SQL Servers 2000 and 2005, Standard and Enterprise Editions.

Download the DB^x™ Cluster Middleware Solution brochure (PDF, 211 Kb).

Zero-Loss Continuous Replication with Performance Boost

Transaction replication is the only guaranteed method against potential data losses. Transaction replication is difficult and costly. Up-scaling of SQL Server performance is also difficult if real transactions (not only for BI) are involved.

PCTI's breakthrough synchronous parallel transaction engine was designed to address both problems.

Dynamic Serialization and Load Balancing

Dynamic serialization solves the replication performance problems. There are two crucial aspects: In-Flight Transaction Serialization for replication consistency with minimal overheads and In-Flight Transaction Load Balancing for scalable performance.

  

Using this technology, concurrent update transactions are dynamically serialized, read-only queries are automatically load balanced when passing through DB^x™ gateway. This ensures replication data consistency by enforcing the exact commit orders of serialized transactions on multiple standalone SQL Servers. Concurrent read-only queries are distributed to individual SQL Servers to boost performance.

Here are the specific rules for dynamic serialization:

1. INSERT (concurrent INSERTs are serialized only if insertion order is important)
2. DELETE (concurrent DELETEs are serialized only if deletion order is important)
3. UPDATE (concurrent UPDATEs are serialized only if they update the same row)
4. SELECT are load balanced across all target SQL Servers. The use of more SQL Servers in the cluster improves the performance. Load balancer's heuristics automatically takes care of load sharing via WAN connected SQL Servers.

Since most transactions are read-only and most concurrent updates do not need serialization, higher performance and higher availability can be achieved at the same time.

High Performance Synchronous Transaction Replication

Traditional two-phase-commit (2PC) protocol requires sequential transaction applications and immediate rollback if any error occurs. This design has negative impact on both transaction performance and availability.

PCTI introduces 2PC with parallel transaction application and non-stop batch resynchronization. Transactions are applied to multiple SQL Servers in parallel. Servers with inconsistent and inferior results are disconnected from the cluster. They must go through the non-stop batch resynchronization process before resuming service. This technology ensures consistent datasets on all replication targets at all times.

 

This technology allows continuous protection with zero transaction loss regardless of SQL Server and DB^x™ gateway failures. It allows dynamic expansion and contraction of a SQL Server cluster without extended service downtimes; any SQL Server can be taken offline for repair or service pack application with little impact on service availability. New servers can also be added when the need arises.

Secure High Performance Data Vault

Many security breaches are committed within the corporate firewalls. Adding additional firewall to a database server will introduce additional latency. This is not commonly practiced. The introduction of DB^x™ gateway partitions the database access into two networks: client-side network and server-side network. These two networks can be physically separated to ensure the deployment of the strictest access control policy. Since DB^x™ acts as an advanced transaction pre-processor, adding access control (firewall) requires virtually no overhead.

  

  

Very Large Scale Database Cluster

There are limitations on the largest possible database that can be managed by a single SQL Server. Once the application's data requirement grows beyond that size, data partition is necessary.

Traditionally, partitioned database is referred to as "federated databases". The overall database image is represented by a set of SQL Servers, each managing a designated partition of the overall database.

Federated databases gain in performance due to reduced dataset size managed by each SQL Server. They suffer reduced availability and increased management complexity. The service status of any SQL Server directly impacts the entire system's availability.

There are at least three different ways we can apply DB^x™ technology to enhance the federated databases:

1. Use DB^x™ gateway to deliver higher availability and performance to the most critical data partitions.
2. Use DB^x™ gateway to protect the entire system; and
3. Use multiple DB^x™ gateways to further accelerate update and read-only queries.

 

 

 

Shifted replication of partitioned datasets can achieve significant performance advantages for update and read-only queries while maintaining the same high availability benefits.

 

For example, an INSERT query can be four times faster if the billion-row table is partitioned into four subsets and hosted by four SQL Servers. Shifted replication allows any SQL Server to crash without impacting the overall system availability. In the above figure, there are four copies of the same datasets. Each SQL Server hosts 1/4 primary partition and 3/4 backup partitions for others.

High Performance Cluster with Back-in-Time Restoration

A development SQL Server cluster differs from a production SQL cluster. It requires roll back in time capability due to potential erroneous data operations by developers. All transaction replication systems including DB^x™ cannot rollback in time since each replicated transaction overwrites the prior corresponding transaction history.

Using DB^x™ automatic resynchronization tool, you can implement a high- performance cluster with back-in-time restoration capability. There are at least two choices.

a. Without incremental backup and restore. This configuration does not require periodic backup. It can only restore data from the latest prior resynchronization point.

 

 

b. With incremental backup on one SQL Server. This configuration requires the administrator to activate periodic incremental backup on one of the clustered SQL Servers. Restoring the entire cluster to a point-in-time position requires activating the DB^x™ resynchronization tool based on the desired back-up set.

 

 

 

Reducing Planned Downtime Replicating Across Low-quality WAN

Amongst other IT services like desktop and network management, corporate portals, etc., WANs are often used for disaster prevention. Many applications are required to operate across a WAN. If an enterprise is also doing high performance and disaster prevention of SQL database servers, Low WAN quality adds more difficulties to these operations. They can lose connectivity a few times per day and the latencies can vary widely

A replication queue is the ideal device in order to absorb the intermittent WAN outages and varying latency. To provide a smooth recovery operation, a two-way replication scheme is typically used (see figure below). This design allows the replication queues to absorb imperfect WAN problems in both directions.

A replication queue is the ideal device in order to absorb the intermittent WAN outages and varying latency. To provide smooth recovery operation, a two-way replication scheme is typically used (see figure below). This design allows the replication queues to absorb imperfect WAN problems in both directions.

 

 

 

There are a few operating issues:

1. Instant SQL Server fail-over is not possible since it must wait for the replication queue to clear. Typically, this is a manual operation.
2. Similarly, restoring a SQL Server must also wait for the reversed-direction queue to clear before the cluster can resume service,
3. Data corruption in either replication queues, or in any SQL Server will require extended service downtime since rebuilding a dataset across WAN can take a substantially long time if the dataset is larger than 100GB.
4. The primary SQL Server must be throttled to ensure slower transaction processing speed so it will not flood the replication queue.
5. Primary and secondary SQL Server require different maintenance routines since not all database changes are replicated. Some database objects, such as logins, permissions, stored procedures and schema changes are not replicated.

Due to poor WAN quality, DB^x™ cannot directly eliminate these issues. It can, however, improve these operations.

 

 

 

This is called the "Intelligent Switch" configuration. DB^x™ is installed between clients and SQL Servers acting as an automated switch of operations for cases of fail-over and recovery:

1. Automated seamless automatic fail-over. With DB^x™ monitoring of all client traffic, using DB^x™ SDK (Software Development Kit), we can provide seamless primary->secondary switchover by monitoring Q1's state.
2. Automated seamless fail-back, using DB^x™ SDK when restoring the primary SQL.
3. No change in application code. No need to treat stored procedures.
4. When a complete dataset is needed, DB^x™ can reduce service downtime to be less than one minute per incident by using DB^x™ automatic resynchronization tool.
5. Tolerates intermittent WAN outages and long latencies.
6. Simple deployment: single (IP, port) entry.
7. DB^x™ offers a potential to upgrade system by adding load balancing SQL Servers over LAN and WAN for zero-loss continuous protection when network quality improves.