Recently, one of the largest fortune 500 companies came to us with a requirement of an always available web application for its customers. The solution required 5-nines reliability in every component of the infrastructure and suddenly the network architect and the db architect who seldom see eye to eye had to roll up their sleeves and bring their “A” game to the table. While the network architect had already conquered 5-nines reliability in the HTTP infrastructure with NetScaler, the DBA was going through a mind wrecking process deciding on a High-Availability architecture for applications requiring 99.999% uptime. Add into the mix DR (disaster recovery) and utilizing all of the available infrastructure at all times to maximize ROI and the DBA was staring a formidable design task. With NetScaler DataStreamTM the DBA now could also take advantage of the numerous easy to implement HA solutions that were once only available to the HTTP tier.

The key requirements revolve around

  1. Distribution of load among 2 or more datacenters
  2. Automatic failover of the Microsoft SQL servers.
  3. Always having one globally unique instance of publisher/author database available. Read vs Write workloads.
  4. Minimizing or complete elimination of database conflicts.
  5. Fastest Data replication between the Microsoft SQL servers.
  6. No SPF ( single point of failure) in any component of the infrastructure.
  7. Best bang for the buck.

The first requirement of having load distributed between the two datacenters is achieved by GSLB feature of NetScaler that can use Geolocation Database to provide HA as well as good load distribution. Check out  http://community./display/ns/How+GSLB+Works for more details. GSLB distributes load at the HTTP application layer eliminates SPF at the application layer and also satisfies DR.

SQL Server Clustering was not considered here since the shared storage requirement is a SPF and also is not the best solution here from cost perspective. SQL Server Mirroring, on the other hand is a viable solution with each mirrored pair being an independent copy of the database. With the witness server achieving failover between the principal and the mirror the solutions seems complete, but on a failover the witness does not shutdown the SQL server instance leaving clients/HTTP application servers to timeout and then switch to their second choices. Add NetScaler DataStreamTM which achieves failover between multiple SQL servers with advanced SQL health monitoring (http://blogs./2011/07/30/advanced-microsoft-sqlmysql-health-monitors-in-netscaler-9-3-ncore/) and you have a perfect failover solution to achieve 5-nines and eliminate SPF at the DB tier. NetScaler DataStreamTM allows for seamless maintenance/upgrade windows, site to site backups and a single SQL connection string for the application server ( imagine that!).

With NetScaler DataStreamTM SQL content switching, one can split all of the SQL Queries or Stored procedures and redirect writes to one server pool and reads to another server pool. Achieving the publisher/subscriber model without having to modify applications or the database server architecture is one sweet deal.  This also helps minimize conflicts and helps scale/distribute DB workloads across datacenters.

The choice of replication vs service broker to synchronize data from one SQL server mirror setup to another depends on the application requirements. Here, the read workload queries/Stored procs modify non-critical data like last login time that can be easily resolved on conflict by just keeping the latest value. This eliminates the use of peer to peer replication, so the customer implemented message queue infrastructure on top of service broker achieves data synchonization between the mirrored server pairs.

As to the best bang for the buck, most NetScaler customers already have them doing wonders to the HTTP tier, just upgrade the throughput license with pay-as-you-grow on your MPX or VPX appliance to accomodate the db tier traffic. One should also consider NetScaler SDX to have seperate NetScaler instances for the GLSB, HTTP and DB tiers for resource isolation.  You are all set to rock the data tier! ( as shown below). DBA’s interested in this architecture should definitely check out this link for more information http://www./English/ps2/products/subfeature.asp?contentID=2309522

Microsoft SQL Server-HA with NetScaler DataStreamTM

Microsoft SQL Server-HA with NetScaler DataStreamTM