A Practical Path Towards the Ultimate Microsoft SQL Server Scalability and Availability

How ICX-UDS Middleware Work

The answer to the earlier question actually contains two parts:

  • How to replicate concurrent transactions synchronously?

  • How to re-synchronize an out-of-sync database server without stopping the cluster?

This might not be obvious, but solving one without the other is simply not good enough. ICX-UDS middleware (gateway) is network-sandwiched between database clients and a cluster of SQL Servers. Each gateway can be configured to have one of the three “personalities”: replicator, load balancer, and read-only server.

In the replicator mode, an ICX-UDS gateway replicates all concurrent transactions using a proprietary dynamic serialization algorithm. Since it can “see” all concurrent transactions, it can easily apply serialization, deadlock resolution and fail over controls in extremely short times.

The two most important features of ICX-UDS are:

  • It does not use 2PC in transaction replication. Therefore, your transaction will be forced to roll back only if all SQL Servers crash at the same time.

  • It employs an automatic resynchronization algorithm that allows 10-second downtime for each dataset resynchronization, independent of dataset size.

In the load balancer mode, an ICX-UDS gateway uses multiple heuristics to distribute queries to all servers in the cluster.

The read-only server mode provides high quality read-only service regardless SQL Server crashes in the cluster until the last.

ICX-UDS gateways have built-in IP-takeover functions that can be used to remove the last single-point-of-failures when the gateways are on the same network segment. Otherwise, DNS (Domain Name Service) appliance can provide gateway monitor and fail over support.

The optimal scalability and availability is achieved by sending update queries to the replicator gateway and read-only queries to the load balancer gateway. As long as the update/read-only ratio remains low, you can add SQL Servers to the cluster and expect to see improved performance. Your cluster availability increases linearly as the number of SQL Servers in the cluster.

Having a middleware will introduce additional overhead. In lab tests running 30,000 commands, we compare Microsoft SQL Server immediate update replication and ICXUDS synchronous replication. There are two tests for each category: without replication and with replication. For ICX-UDS, they are direct

SQL Server connection versus indirect going through ICX-UDS gateway then to the SQL Server. For Microsoft SQL Server transaction replication, the tests are with and without replication engine running. We obtained the following performance table (all measures in seconds).

(*) Reference:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx

In the above table, only the overhead measures are important due to possible differences in the testing environments.

In practice, ICX-UDS has been used in 24×7 E-commerce applications from online transaction processing to help disk support. It has delivered multiplied performance using two to four SQL Servers. At the time of this writing, the largest ICX-UDS cluster consists of 15 SQL Servers. For detailed technical description, please refer to www.pcticorp.com/onlinebook.

The Ultimate Growth Path

Microsoft SQL Server is as good as any other databases on the market. A recommended practical, and the most cost effective growth path for SQL Server scalability and availability is:

a) When your application is small, start with a single SQL Server with scheduled backups.

b) Add a replication middleware to build a SQL Server cluster with 2 servers.

c) Grow this cluster until the overhead in the replication middleware becomes time prohibitive.

d) At this time, your business must be doing pretty well. Now, hire some smart people to re-code your application to adapt to DPV. Move the replication middleware to scale and protect the SQL Servers hosting the most popular tables.

e) Expand your enterprise by repeating the proven practice to other growing SQL tables.

It should now be obvious that once we break the technological barrier on concurrent transaction replication and resynchronization, database scalability and availability should be within reach for all enterprises.

About the Author

Justin Y. Shi is the founder and chief executive of Parallel Computers Technology Inc. Justin is an inventor and pioneer in high performance and high availability multiprocessor system designs. His inventions include dynamic communication protocol generation for heterogeneous operating environments and highly available high performance multiprocessors (the Stateless Machine). Justin is also known as Yuan Shi as the inventor of the Stateless Machine. Justin was the first to discover the timing models for parallel performance analysis and the equivalence between Amdhal’s and Gustafson’s Laws.

After seven-year research and developments, Justin’s team has brought to market the first synchronous database replication product without using the two-phase-commit protocol. This breakthrough allows building practical high performance Very Large Scale Databases (VLDB) without suffering availability concerns. Other technology products include fault tolerant parallel processing system Synergy and Parallel Markup Language compiler (PMLc) for generating Synergy parallel programs directly from sequential programs.

Justin also teaches at Temple University, Philadelphia, PA. He can be reached at shi@pcticorp.com or shi@temple.edu.

Copyright 2005 by the author.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |