This article discusses options for scaling-out, and for increasing availability of Digital-Clay systems using redundant failover machines. For scaling up, or for first-stop tips on increasing performance see Increasing Digital-Clay Performance.

Although this article focuses on Windows Clusters, note that most of the issues and concepts also apply to other solutions such as hardware-based network load-balancers.

Failover Clusters are used when high availability is needed, and when automatic and rapid recovery from either hardware or software failures is critical. Although an understanding of Windows Clusters is assumed, here are some important notes and highlights:

• Failover Clusters are different than Load Balancing Clusters in that they run through a more advanced Windows Cluster Service that manages and ensures the availability of Cluster resources (hardware or software).

• Failover Clusters may require special hardware compatibility, especially in older versions.

• Up to SQL Server 2008, a shared storage solution was required since the database is not replicated, making the storage the potential single point of failure (although this can be made redundant separately with hardware using SANs with replication, or RAID). With SQL Server 2012 “AlwaysOn Failover Clustering” (which works even in SQL Standard), the cluster still depends on shared storage. However with SQL 2012 “AlwaysOn Availability Groups” (SQL Enterprise only), databases can be mirrored to any site or server with its own storage, either synchronously or asynchronously, and these mirrors can even be connected to for read-only access.

• Failover Clusters are only supported on the Advanced/Enterprise/Datacenter Windows Server editions.

• Typically, a Failover Cluster runs in Active/Passive mode. An Active/Active configuration is only useful when running two instances of a database, two separate databases, or a database split in two, and is therefore not applicable to Digital-Clay. But see below for a discussion on Load Balancing. Note that AlwaysOn Failover on SQL Standard only supports Active/Passive configurations. Also keep in mind that only Active/Passive configurations require a single SQL Server license.

• Failover Clusters are also useful for performing rollover updates and maintenance tasks by working on one machine at a time, which reduces downtime to several seconds.

Although a fully 'cluster-aware' application may make use of Windows Clustering API, expose special Clustering Properties via a Custom Cluster Resource Type, and listen to all Cluster events, to make an application work as a highly available Cluster Resource takes minimal work:

• Since ClayCentral uses a database as its backend, the database minimally needs to be stored on the shared storage, and optimally, as in the case of SQL Server, it also features transactional integrity, database recovery, and Cluster-aware functionality built-in.

• Lifecycle: By adding ClayCentral as a Generic Service Resource Type, the Cluster Service automatically checks that the ClayCentral service is running, it stops and starts the service as necessary on all the cluster nodes and when switching to a different node or performing a failover.

• Software Failure: One of the features a Cluster-aware application typically needs to implement, is to report on critical software failures to the Cluster Service, which would then failover to another machine. Since the most critical and complex component in a Digital-Clay system is the database (in the sense of having more fail points), the SQL Server clustering functionality should be enough for most systems. In other words, by adding SQL Server as a cluster resource as well as ClayCentral as a cluster service, crashes by either service will cause an automatic failover. But if more comprehensive checks are needed, a script may be run to check up on ClayCentral to make sure, for example, that the \DigitalClay folder is accessible or that ClayCentral and SQL Server are connectable. To do this, a simple script can be added as a cluster resource that performs the necessary checks when the Cluster Service asks it to. For more details, see Generic Script Resource.

• Connectivity: ClayCentral can be configured (ClayCentral Settings) to bind itself to a Windows Cluster IP address instead of the default local address of the machine. Thus when a failover occurs, the new ClayCentral simply takes over using the same IP but on another machine.

• Digital-Clay Clients: Clients are disconnected from ClayCentral for at least a few seconds while the failover occurs, but they automatically attempt to reconnect and relogin for a configurable amount of time before giving up. This means that users should not even experience the failover except as a pause of responsiveness for a few seconds, and only in the case where they were running a query or action during the failure, they would receive an error and have to resend the request. Note that this behaviour includes the ClayCGI web module.

• Storage: As explained earlier, the database itself and its storage location is an SQL Server configuration issue, but ClayCentral also stores additional data in the form of Attachments, Profiles, Injection data, etc. in the \DigitalClay folder. Typically, this folder is created in the system drive, but can be configured to be located on the shared drive using ClayCentral settings. By configuring both the database and the Central files on the shared drive, all data is always up to date and never lost when a failover occurs.

• Registry settings: ClayCentral's registry settings may be replicated between the Cluster Nodes using built-in Windows Cluster registry replication settings while adding the ClayCentral Cluster Resource.

The goal of Load Balancing is to increase performance in a system with many concurrent users and queries by spreading the requests and connections over several machines.

Note that before scaling-out this way, it is much more preferable and simple to scale-up or tweak more performance out of the system using the methods listed in Increasing Digital-Clay Performance. For example, before even considering a load balancing solution, SQL Server and ClayCentral should be split onto two machines, each with maximum CPUs or CPU cores, and maximum memory/FSB speeds, thus spreading the load over two machines and allowing Clay to handle more concurrent requests faster.

With database solutions, load balancing would only help with components that require no persistent data, or with read operations. A fully load-balanced database solution means that any write would have to be replicated, thus making all write operations that much slower. Thus a write-intensive system may not benefit from load balancing and may even be slower. But since most solutions read more than they write, load balancing should provide performance gains if they are set up correctly.

This section will cover load balancing of three critical and resource-hungry components in a Digital-Clay system:

This is a common and relatively easy target for load balancing. Web-farms are often used to handle many concurrent WWW requests, and ClayCGI may be run within these environments just like any other CGI program. For example, IIS servers may be clustered and load balanced in the standard way, each running an identical copy of the ClayCGI module and support files, all of which retrieve data as necessary from a single ClayCentral/SQL Server.

This is the easiest component to load-balance because there is no persistent data.

The only requirement is that the load balancer needs to support Affinity rules. I.e. since ClayCGI is a FastCGI component and maintains session, caching and connection information between requests for speed, requests originating from the same IP need to be routed to the same machine every time. This is easily done in Windows Load Balancing Clusters by checking the Affinity rule option when adding Port Rules.

The bottom line is that currently, SQL Server does not support load balancing in a comprehensive way that is adequate for many Digital-Clay systems due to some SQL Server limitations and the nature of Digital-Clay solutions. But there are some practical usage scenarios described below. Here are some good articles:

• http://www.sql-server-performance.com/articles/clustering/massive_scalability_p1.aspx
• http://msdn.microsoft.com/en-us/library/aa479364.aspx

And here are the relevant summarized points, some of which apply to other databases as well:

• Obviously, two database server instances cannot use the same database files for writing due to conflicting locks, integrity and caching issues. This simple fact rules out two databases on two machines sharing and writing to the same files on RAID/SAN and it means that data must be replicated or mirrored.

• Since the vast majority of Clay systems need to write to the database, a read-only mirrored database is usually not relevant. Even with SQL Server 2012 AlwaysOn Availability Groups, the mirrored databases must be read-only. However, a secondary mirrored read-only database may be useful to offload reports and users that only require BI functionality. This 'load balances' by offloading some heavy queries to a second system. The SQL Server database in this case may be mirrored using AlwaysOn Availability Groups, or with Log Shipping (backup/copy/restore transaction logs), or using any form of Replication.

• The 'SQL Server mirroring' feature is deprecated in 2012 and does not even allow read-only access to the secondary database. It has been replaced by AlwaysOn.

Replication: There are two primary forms of replication in databases: Synchronous and asynchronous.

• Asynchronous replication queues up updates or polls for changes and handles synchronization with some latency and with no guarantee for the latest data updates during a failure. Replication latency for SQL Server is usually measured in minutes. Typical usage scenarios include warehousing, backups, duplication of subsets of data over several sites, etc but not for failover scenarios. This type of replication is also not good for solutions that require real-time data, or that feature multiple users working concurrently on the same data since this will result in increased conflicts.

• Conflict management during 'Merge Replication' is typically handled either by automatically assigning priorities (the highest priority wins and the other data update is discarded), or by using/writing custom code that decides which update wins automatically, or the conflict is stored and handled later manually. In other words this is an 'optimistic lock' solution where it is assumed conflicts will not happen often and if they do, that it is OK to discard one of the updates.

• Notwithstanding the above, SQL Server supports column-level conflict tracking, which can greatly reduce conflicts unless two users change the same column at the same time, which is rare. This feature should be a practical solution for database conflicts in most cases.

• Synchronous replication, on the other hand, only commits data after it has been stored at replica servers, ensuring that updates will only be accepted if they are safely replicated. This approach should never result in lost data due to conflicts since the databases must lock and synchronize with each other before accepting updates.

• The disadvantage of synchronous replication is that data writes are much slower due to the fact that any transaction would have to wait for all the other systems to update before continuing. The performance gains from load balancing would be counter-balanced by slower write access. Thus in systems that involve a lot of updates relative to read-only queries, this may not result in significant performance gains and may even be slower overall in extreme cases.

• ClayCentral has its own advanced logic for updating records and handling conflicts between users with a friendly interactive interface, However, with both synchronous and asynchronous replication, updates will be coming in from other databases, effectively rendering this feature useless unless ClayCentral uses its own synchronization mechanisms between clusters of ClayCentrals (see below).

• As of 2012, SQL Server replication does not support synchronous (real-time) replication. MySQL, however, does support full synchronous replication with write-access to all nodes, as well as a compromise 'semi-synchronous' mode.

Partitioning: A database may be split in several ways in order to achieve a kind of load balancing by spreading out the data:

• Each client can connect to a separate database on a separate server. This is obviously not relevant for a single Clay system, unless the system can be split into distinct databases where users do not require access to all of the data at once. For example, a single system managing multiple schools may be split so that each system handles a different set of schools, as long as the same user doesn't need to update and manage multiple schools. Or, two departments with different and unlinked sets of tables may be split into two systems/databases.

• Tables may be partitioned over multiple files over multiple disks to spread the query load over multiple storage resources. Multiple tables can be split where groups of tables are stored in different files (e.g. order data vs. activity data), or a single table can be partitioned by sets of rows (e.g. students belonging to different schools). This may improve performance in some cases but is limited to specific performance gains in specific cases where the queries and usage patterns are known in advance.

• Querying over multiple databases, or building table partitions is VERY application specific and each table structure and query must be tailored to the specific solution, therefore Digital-Clay cannot make use of such features for now. In some cases, summary data may even need to be duplicated in multiple tables in order to avoid querying over multiple partitions/databases.

• Note that using Views over linked tables in remote databases, including views on partitioned tables, is also not an option because Digital-Clay requires control over the table schema and the ability to easily add/remove columns and indexes.

In summary, these are the current limited possibilities for load balancing databases:

• Splitting the system into two or more pairs of SQL Server/ClayCentral with different tables or sets of data in each system, thus, in effect, load balancing system usage. This is only appropriate when departments can be separated without requiring constant visibility into the other departments' data.

• Using mirroring or replication for offloading heavy read-only access to another server. This can be useful for example, for an informational Web portal, or for reporting and analysis servers.

• SQL Server replication may be used only in solutions that don't require real-time data, and that feature few conflicts between multiple users concurrently updating the same records.

• Future versions of SQL Server may add additional replication functionality, or Digital-Clay may address this in the future by supporting MySQL databases for synchronous replication.

See above regarding SQL Server limitations on load balancing with Digital-Clay solutions. Since most of the load of Digital-Clay systems is handled by the database, the database will most often become a bottleneck before ClayCentral does. Which means that the limitations described above need to be addressed, and that priority needs to be placed on one of the aforementioned solutions before even considering ClayCentral load balancing.

To load-balance ClayCentral itself would require advanced cluster mechanisms to handle the updating of caches both at ClayCentral and clients, as well as for safe conflict management. All of this is currently being researched for future releases.