Load Balancing SQL with NetScaler

Scaling the Data Tier with Citrix NetScaler DataStream Technology

Native support and intelligence of SQL transactions is the key to extending the scalability, availability, and security benefits that the ADC brings to the web tier to the data tier, as noted in Sunil Potti’s blog. Similar to the benefits of HTTP  load balancing, a data tier load balancer should provide these key benefits:

  • A native SQL proxy like solution to perform SQL connection management and offload for database scale up scenarios to increase SQL connection limit.
  • A TDS protocol and SQL transaction intelligent load balancer that can accurately monitor backend server status including slave replication backlog to
    optimally distribute load in database scale out setups.
  • High Availability that can address both Master/Master and Master/Slave replication deployments to complete the database scaling solution. Both scaling approaches also need advanced, clustering-like automated IP failover for lower cost high availability with commodity hardware.
  • Database security encompassing transaction activity logging tied to user logins for compliance purposes to be generated from a central location to
    simplify record management.

NetScaler DataStream Technology

Leveraging its expertise in web applications, NetScaler with DataStream technology is the first ADC that justifiably addresses data tier challenges.
With DataStream technology, NetScaler introduces the industry’s first “true” load balancer for data that offers native SQL protocol, data format and
transaction support for scaling database infrastructures. Structured Query Language (SQL) which is the de-facto data format, TDS (Tabular Data Stream) and Oracle MySQL, protocols that are widely deployed, are natively parsed and interpreted in NetScaler’s high speed processing engine, providing all of the
advance policy framework available to act on data connections and transactions. Applying all of the same HTTP acceleration technologies to SQL, NetScaler is able to provide the same benefits to the data tier, such as SQL Connection Multiplexing, SQL Load Balancing, SQL Content Switching, SQL Health Monitoring, Automated Failover and Security.


SQL connection multiplexing: NetScaler’s SQL transaction intelligence enables NetScaler to act as a SQL proxy and terminate SQL connections from both
client and server. Client to server connection multiplexing ratios of 50:1 or more is achievable depending on the application, number of unique database user
logins and character sets in use. SQL Load balancing: Armed with SQL transaction awareness, NetScaler can switch one client’s many SQL transactions in a single TCP connection to several different servers. This delivers more optimal load balancing of SQL traffic since the load distribution is more granular when compared to a TCP load balancer.

  • SQL Content switching (read/write split): NetScaler can interpret SQL transactions and decipher between “select”, “drop”, “insert”, “update” and other SQL statements and perform a split between read and write transactions. Splitting the reads and writes simplifies application server deployments by offloading the decision making and policy enforcement to NetScaler.
  • SQL Health monitoring: NetScaler intelligently load balances SQL requests, by selecting the database server that will return up-to-date data the fastest at the time of the request and sending the read request to only that server.
  • Automated IP failover: NetScaler’s Virtual IP address solution is based on automated IP failover implementation where the virtual IP is assigned to the master node is switched over to the secondary node upon detection of heartbeat/monitor failure.
  • Security: NetScaler can apply granular user access policies to each database user. It also provides a consolidated log of all SQL transactions and user
    accesses for complete visibility, without taxing the database server. SQL protocol validation is also available with advanced Policy Infrastructure (PI)
    regular expressions.

As the industry’s first SQL aware load balancing solution, NetScaler addresses the scaling and availability needs of the data tier, leveraging its
HTTP ADC technology for the web tier. NetScaler’s SQL connection multiplexing scales connection limits for database scale up, native SQL load balancing
optimizes database scale-out, SQL transaction logs simplifies database security and replication aware monitors with Virtual IP based failover enables
cost-effective high-availability.

Combine SQL with Netscaler and you have a perfect match =o)

Sanbolic delivers SQL HA to XenDesktop 5

As many of you know, Sanbolic® has been enhancing Citrix® solutions for years, helping Citrix customers achieve greater value from enterprise applications such as XenApp™ and XenDesktop®.

Well, here we go again! This time, the beneficiary of Sanbolic’s industry-leading software-based storage solution “Melio Unified Storage Platform™” is the latest version of Citrix’s market-leading VDI solution, XenDesktop 5.0®.

XenDesktop 5.0 introduces several major changes with respect to it’s components, architecture and implementation. One of these major changes, located at the crux of the VDI solution, is how the configuration information for XenDesktop 5.0 is stored. No longer is the IMA data store used to store configuration information. Instead, a Microsoft® SQL Server™ database is used to store both configuration and session information. With this new dependence on SQL Server however, comes the necessity to ensure that the central database for XenDesktop is always accessible.

Reading through the XenDesktop user manuals, you’ll find various sections that cover the topic of SQL HA. Of notable importance is the following statement:

“In XenDesktop, all information is stored on the database; controllers communicate only with the database and not with each other. A controller may be unplugged or turned off without this affecting other controllers in the site. This means, however, that the database forms a single point of failure. If the database server fails, existing connections to virtual desktops will continue to function until the user either logs off or disconnects from their virtual desktop; new connections cannot be established if the database server is unavailable.”

In order to address the single point of failure introduced by SQL Server, Citrix recommends the following:

1. Backup the database regularly so that the database can be restored from the backup if the database server fails.

2. Implement one of the following SQL HA methods:

A. SQL Mirroring: (Recommended by Citrix.) Mirroring the database ensures that if the primary SQL Server hosting the database fails, database ownership will automatically fail over to another SQL Server within a few seconds. Although this method can be effective for minimizing the impact of SQL Server failures to users, Citrix notes that this method is more expensive than other methods for enabling SQL Server HA since full SQL Server licenses are required to employ this configuration. In addition, XenDesktop product documentation also notes the following: “If you intend to use SQL Mirroring, you must ensure that the database is created in “Full Recovery” mode. If Desktop Studio is used to automatically create a database on an external SQL server, the database is created in simple mode; this means the transaction log cannot be backed-up and the database mirrored. To ensure the database is created in full recovery mode and can be mirrored, you must create the database manually then use Desktop Studio to create the necessary setup scripts which you run on the database.”

B. Hypervisor HA: This option entails deploying the SQL Server as a virtual machine and leveraging the HA features of the hypervisor, making this solution easier and less expensive to implement than SQL Mirroring since it uses existing host software and allows the use of SQL Server Express™. The caveat to this method is that the automatic failover process will most likely be slower due to the extra time it takes for a new virtual machine to start up to host the database, which could adversely affect service to users.

C. SQL Clustering: This method utilizes Microsoft SQL clustering to automatically allow one SQL Server to take over the tasks and responsibilities of a failed SQL Server. The problem with this method however, is that it is much more complex to set up as it requires “extra” moving parts and services necessary to facilitate the recognition and automatic failover of a database from a failed server to a secondary server. For example, SQL Server Enterprise™ and Windows Server 2008 or Server 2008 R2 Enterprise™ editions must be used, and Windows Failover Clustering™ must be configured, which is necessary to enable database failover between SQL Servers. Lastly, XenDesktop product documentation notes that database failover may be slower than other methods (i.e., SQL Mirroring).

UGH… sure seems more complicated and more daunting than it needs to be.

Well everyone, you can relax… implementing SQL HA to ensure the availability of the XenDesktop configuration database doesn’t have to be as difficult as the XenDesktop product documentation seems to imply, and it’s not. At least not if you use Sanbolic’s Melio Unified Storage Platform (aka Melio USP™)!

Designed to enhance the performance, scalability, manageability and availability of today’s leading enterprise applications and solutions, Melio USP provides highly scalable and highly available shared storage that allows multiple Windows Servers to share concurrent read-and-write access to a single volume on block-based storage. With a robust, flexible and scalable storage platform, applications can take advantage of the active/active disk access provided by the Melio FS™ cluster file system (the core component of Melio USP) to achieve enhanced fault tolerance via high availability.

The beauty of it all is that with Melio USP, you not only get all the benefits of shared storage that’s easy to install, use and manage, you also get AppCluster™, an advanced software component within Melio USP that provides HA for applications such as SQL Server without all the hassles, extra moving parts and costs associated with other HA options (i.e., SQL Mirroring, Hypervisor HA or SQL Clustering). AppCluster makes it extremely easy for administrators to set up highly available SQL clusters so they can focus their time and attention on managing their XenDesktop infrastructures, not their SQL implementations.

AppCluster allows administrators to create “resource groups” comprised of SQL Servers and databases, which are stored on a Melio shared volume. When a server within a resource group fails, the database(s) it was hosting automatically fails over to another server in the same resource group, minimizing or preventing disruptions to user productivity. Failover occurs very quickly because unlike SQL Clustering, which employs an active/passive disk access model, all SQL Servers participating in the Melio cluster share active/active disk access to the Melio volume containing the XenDesktop configuration database, which means there’s no time lost waiting for disk access to be passed from one server to another.

The combination of highly scalable and highly available shared storage provided by the Melio cluster file system and the automated failover capability afforded by AppCluster makes Melio USP the ideal solution for enabling SQL HA for XenDesktop 5.0.

For more information about Sanbolic’s Melio Unified Storage Platform and how it enhances Citrix XenDesktop, please visit the Solutions page at http://www.sanbolic.com/Citrix.htm

Best practise – XD5 Database Sizing and Mirroring

Database Planning
There are a number of important areas to consider when planning an SQL database for XenDesktop 5.0, including:

  • Database Sizing
  • Database Mirroring

This article provides information about database sizing and mirroring, discusses some best practices, and provides links to additional information.

Database Sizing
When sizing an SQL database, two aspects are important:

  1. The database file
  2. The database transaction log file

Sizing the database file
The size of the database file depends on the following factors:

  • The number of configured and registered workstations
  • The number of connected sessions
  • The connection rate
  • The number of managed desktops
  • The number of provisioned desktops

Read the full whitepaper here