Introduction
The concept of High Availability is not limited to an Enterprise world anymore. It has already sneaked through the doors of SMB market which brings up potential challenges to DBAs. This paper discusses specifically two out of various options available for SQL Server high availability, Microsoft SQL Server Cluster (active/passive configuration) and Standby Server.
What is Microsoft SQL Server Cluster?
Microsoft SQL Server Cluster provides both automatic and manual failover capability for SQL Server services to another node in case the active node is down. The active node can be down due to an operating system or a hardware failure in which case the automatic failover to an available node on the same cluster can happen and the users will start using the application through the failover node. SQL Server services can be manually moved to a different node at times when a planned maintenance like operating system upgrade or patch maintenance is required on the active node.
What is Standby Server?
A Standby Server is a server where the data from the primary databases is restored periodically using scripts, Microsoft Log Shipping or through a third party software. It acts as a hot standby which can be promoted to a primary role if the primary server goes down so that business operations continue to function.
Microsoft SQL Server Cluster Vs Standby Server
Microsoft SQL Server Cluster is assigned a virtual server name and an IP address which is used by the applications to connect to the SQL Server. There is no change required on the application side as the failover node acquire the same virtual server name and an IP address in case the active node goes down. Whereas the Standby server possess different server name and the IP address which means either an application change or a DNS change will be required in case the Standby server is promoted to a primary role.
SQL Server Clustering provides high availability by protecting against a node failure. It is important to understand here is that the storage failure will result into an application disruption as all the nodes in the cluster uses shared storage which also contains database files of the SQL Server database. Whereas Standby server/=
database which is normally installed on the other independent SQL Server protects not only against the operating system or a hardware failure but also against storage failure as it is installed on a separate storage.
In case of a planned upgrade of an operating system or a SQL Server, clustering has the advantage, as it's relatively easy to configure one failover cluster to fail over to any other node in the failover cluster configuration. This way, system downtime can be minimized thus providing high server availability. On the other hand if the Standby server/database is promoted to primary server/database then switching back the roles is relatively a complex task. This involves executing some stored procedures and also make sure that the transaction logs of the database are not truncated which will break the log shipping sequence and hence the Standby process will need to be start all over again which will require a complete backup and restore of the database.
SQL Server cluster has high requirements in terms of hardware and software as opposed to Standby. Cluster requires Windows NT Enterprise Edition, Windows 2000 Advanced Server or Windows 2003 Enterprise Edition and SQL Server Enterprise Edition along with Microsoft Certified hardware whereas Standby can be configured using Log Shipping (provided in Microsoft Enterprise Edition) or using third party vendor software and using custom scripts in Standard Edition or through the third part software. Standby server doesn't have any special hardware requirements.
Setting up a SQL Server Cluster is a complex process and requires expertise to configure and maintained. Before
setting up a SQL Server, Windows Cluster needs to be configured which requires a shared storage, the setup of which itself is a complex task. Standby has no such requirements.
SQL Server cluster requires high speed LAN. This is required for nodes in a cluster, which need to send and receive what is called a heartbeat signal, among other communications. This signal is used by each node to determine if the other node is still available. In case any node is not available then the other node takes over. On the other hand Standby can work either on a local area network or over the WAN. Of course depending on the size of the database it can be a slow process.
Cost wise SQL Server Cluster is an expensive solution compare to Standby as it only supports hardware listed on the Microsoft Hardware Compatibility list and requires Enterprise Edition of Windows and SQL Server.
Sonasoft's Standby Solution:
At times setting up a Standby using Log Shipping can become a tedious and time consuming process but fortunately Sonasoft has made this process very simple. With just couple of screens one can easily setup Standby for all the databases in a SQL Server instance as opposed to Log Shipping where each database needs to be setup separately in order to have log shipped to a remote standby server.
Monitoring of Standby databases is a very easy task too with Sonasoft's SonaSafe for SQL Server product. It provides on-the-fly compression of up to 95%, which reduces the amount of traffic flows over the network and thus makes Standby creation feasible over the WAN and also saves on the bandwidth cost.
Network resilience is also built into the product, which buffers the data in case of the network glitches and applies it to the standby server automatically as soon as the network problems are resolved.
Sonasoft also supports both Standard and Enterprise edition of SQL Server so there is no need to write custom scripts in case of Standard SQL 2000 Server Edition or to use unsupported log shipping tools available in the Microsoft BackOffice 4.5 Resource Kit in case of SQL Server 7.
Sonasoft also takes advantage of multi-processors to speed up the Standby operation by a factor of '=
n' where 'n' is the number of CPUs on the machine.
Conclusion
In conclusion, both SQL Clustering & Standby have their own advantages but which solution to implement has to be decided based on requirements, resources and budget. Ideally if the resources and budget are available then both options can be implemented where Clustering can provide convenience for planned operating system and SQL Server upgrades and Standby can provide the protection against the primary server/database crash.
About Sonasoft®
Sonasoft, Inc. automates the disk-to-disk backup and recovery process for Microsoft Exchange, SQL and Windows Servers with its groundbreaking SonaSafe® Point-Click Recovery® solutions. Designed to simplify, automate, and eliminate human error in the backup and recovery process, SonaSafe solutions also centralize the management of multiple servers and provide a cost-effective turnkey disaster recovery strategy for companies of all sizes.
For more information, please visit www.sonasoft.com.
