SQL Server - Deploying Database Mirroring and Clustering parallely on same machine, performance and failover support?
When you think about high availability in SQL Server then you would think about Clustering and in addition to this within 2005 version we have Database Mirroring too that can provide automated failover support to your database server applications. By default you can see a failover cluster as a combination of one or more nodes (servers) with two or more shared disks, known as a resource group. The combination of a resource group, along with its network name, and an internet protocol (IP) address that makes up the clustered application or server, is referred to as a failover cluster or a failover cluster instance. To go further you may have Active/Active and Active/Passive configurations, that can react differently to each other as compared to failover clustering as the number of nodes supported for failover clustering depends on the operating system.
Its a general assumption that using Failover Clustering you can obtain the performance to the application, it isn't quite right. As both of these features are different to each other and dependant from application to application, that varies in hardware aspect too. To keepup the performance you have to deploy the load balancing approach, as time goes by the data within the database grows. Based on this you can expect to see some degradation in performance, particularly when table scans are involved. When you get into the millions or billions of rows, the traditional solution has been to use partitioned views, which are made up of tables with identical schemas hooked together with UNION ALL's. So the general rule to have the member tables are on their own filegroups, you may get better disk performance if the files in those filegroups are on separate physical drives. The tables can even be in separate databases and with 2005 feature of partitioning as long as all of the data is in the same database, you can use table partitioning, which is far easier to implement.
Further I might have seen the recommendation of using such database partitioning within SQL Clustered setup stating the major difference is that the member tables can reside on different instances of SQL Server and those instances can be installed on an N+1 cluster. So what is the big idea in having in such setup, say if any one member table goes offline in a partitioned view, the entire view goes offline. So not all of the data is offline and only for those members part of a cluster then gives you the reliability you need to support performance and provide load balancing. Another big question do you really want to deploy cluster when you have other high availability features are availble from SQL Server 2005 onwards, such as Database Mirroring (automated) and Log Shipping (manual failover tasks).
So going this way for the concept of DB Mirroing will have mirror as a separate instance of SQL Server, unlike in a cluster, and can be located thousands of miles away. Its caches get populated by the update activity that occurs as a result of the transactions duplicated from the principal. Within my setup of database mirroring I have seen that failover is generally quicker than in a cluster since SQL Server is already running on the mirror. Because the caches are at least partially primed, the initial performance is not as sluggish as it might be in the clustered scenario. And note that when a mirrored database fails over, the role of principal and mirror are reversed. The only task you need to be aware within DB mirroing that you need to deploy the equal amount of storage between Principal and Mirror server as the hardware is not shared between these instances, that is applicable to CPU too in case if you go for synchronous mode with no data loss. This varies in Clustering because multiple instances can run on a single node, they would be sharing CPU and RAM-and that could spell poor performance. Ideally, only a single instance should run on a single node.
Here comes the big question on licensing thought, when doing failover support, a server is designated as the passive server. The purpose of the passive server is to absorb the data and information held in another server that fails. A passive server does not need a license if the number of processors in the passive server is equal to or less than the number of processors in the active server. The passive server can take the duties of the active server for 30 days. Afterwards, it must be licensed accordingly.
Database mirroring and failover clustering are available for SQL Server 2005 Standard Edition and SQL Server 2005 Enterprise Edition. Backup log shipping is available in SQL Server 2005 Workgroup Edition, SQL Server 2005 Standard Edition, and SQL Server 2005 Enterprise Edition.
Both Database Mirroring and Clustering passive/mirrored nodes will need to have licenses applied if you are using for Reporting purpose and see the Licensing-FAQ in this regard to avoid any confusion. Also you can download the SQL Server 2005 Pricing and Licensing document for further information on SQL Server 2005 installations.
For the performance & scalability aspects within the Clustered/Mirrored environment follow these links:
SQL Server 2005 and Disk drive allocation unit size to 64K - any benefit or performance?
SQL Server Clustering - what is the difference between Active/Passive and Active/Active Clustering, which is good for better performance?
SQL Server 2005 Scalability and Performance - recommended limit on number of instances on a clustered environment?
Whats the best way to setup memory on a four-node cluster?
SQL Server 2005: Clustering FAQ is Active/Active a load balancing solution?