Clustering and fail over for CF and SQL Server

We were recently working on a site that was growing rapiding in traffic. How should it be clustered and why? In putting together this recommendation we took into account the anticipated increase in traffic volumes and also are striving to avoid too much distribution of assets across a network.  By avoiding the use of Network Attached Storage (NAS) or Storage Area Network (SAN) we believe you can achieve maximum efficiency and maximum redundancy. 

 

Initially with two physical ColdFusion servers and two physical SQL Servers, a failure of one will not cause a total loss of service.  If we employ either a NAS or SAN device, that could be a single point of failure.  The only complication of having two Web-ColdFusion-File Servers is in making sure that both systems are fully replicated.  If this is seen as too considerable a job we could easily add a file-server connected via Gigabit Ethernet, however that once again would introduce a single point of failure should the file server fail. There is software available to automate the copying of code across a server farm (more on that in a later blog post)

 

One other major point, relating to clustering.  The software clustering in ColdFusion cannot handle fail-over from server to server.  So in the event that Server 1 of 2 in a cluster fails, all users on Server 1 would lose their sessions and work.  In addition, without a hardware clustering device,  there is no effective way to share incoming traffic between the two ColdFusion servers.  In order to deal with this we recommended a hardware clustering device which would handle all incoming traffic and who’s main job will be to send users to Server 2 should server 1 fail.

 

We are also taking account of another good feature in database design and use.  There are two distinct uses for databases:

 

OLTP : On Line-Transactional-Processing – This is what we would characterize as the ongoing day-to-day functional copy of the database. It is where data is added and updated but never overwritten or deleted.  This what most companies have and what they use for everything, including, typically heavy, reporting needs.

 

OLAP: On Line-Analytical-Processing – In this model data is stored in a format which enables the efficient creation of data mining/reports. OLAP design should accommodate reporting on very large record sets with little degradation in operational efficiency.

 

Our proposal involves two SQL Server databases which will operate in an Active-Passive method for OLTP work, the ongoing adding and updating of data.  So Database Server 1 will handle all the OLTP traffic unless it fails in which case all traffic will go to Database Server 2 which will have an up to date replicated copy.  Database Server 2 will function as a read-only reporting server in an OLAP way and in the case of a failure of Database Server 2 traffic can be sent to Database Server 1 for OLTP and OLAP duties until Database Server 2 recovers.

Using this method there is good redundancy-failure coverage and also some distribution of load for reporting needs.