High-availability on SQL Server, cons and pros of solutions?

Question

I'm studying about high-availability on SQL Server for my thesis. I've learned there're several solutions to archive this:

• Failover clustering
• Log shipping
• Replication

As I know, these solutions were supported in previous version of SQL Server 2008. SQLS 2008 provides database mirroring, which is supposed as a better solution. I really doubt about this. Can you please tell me the cons and pros of these solutions, what strategies they should be used and what strategies should not. Detail info and explain would help me a lot

Thank you very much.

Answer 1

Log Shipping:

Overview: Transactions on principal are queued to disk and then shipped over to mirror based on replication schedule. Logs are applied to mirror database based on a schedule.

SQL Edition: Standard, Enterprise

Admin effort: Setup network share on mirror as drop location for transaction logs, run SQL wizard for setup

Automatic Failover: Not possible unless Witness server is present

Manual Failover: Involves applying uncommitted transaction log files to database

Failure concerns: Medium, as it relies on Windows to present a network share on the mirror. If application of transaction logs on mirror stop, they will build up

I/O: Higher than mirroring

Mirroring:

Overview: Transactions on principal are committed on principal and sent to mirror. When mirror commits transaction, it tells principal that it is ready for another.

SQL Edition: Enterprise

Automatic failover: Not possible unless Witness server is present

Manual Failover: If connectivity is present, switch mirroring mode to Synchronous. If no connectivity, issue SQL statement to perform a Forced Service restore. Pairs can then be re-synched

Performance: Low when compared to Log Shipping

Personal Lab Work:

Conducted with SQL 2005 Standard and Enterprise.

I found log shipping to be a good idea on paper, but it was complicated to setup and perform a failover in my lab. Mirroring was very elegant and I knew for a fact that the transactions got commited to both pairs.

When it comes time to bring the target up as the primary, I don't want to have to tinker with applying transaction log files. If you need a short RTO, I would lab out mirroring.

You will need to read more about synchronous (transaction is committed to both pairs before marked as complete) vs asynchronous (commit on principal, then sent to target) modes of replication for a mirrored pair. With a LAN, you can run them in synchronous mode, but over a WAN you will have to watch latency when in synchronous mode (be under 10-20ms) otherwise your response time to the application will slow down.

Note that only SQL 2005 Enterprise edition supports asynchronous, which is also called "High-Performance Mode" or turning the SAFETY property "OFF" on the SQL server.

Sorry I don't have any clustering experience.

MSDN Sources

Overview of Database Mirroring http://msdn.microsoft.com/en-us/library/ms189852%28SQL.90%29.aspx

Answer 2

Brent covered log shipping and database mirroring well so I won't go into that. Required reading on this topic is Allan Hirt's book Pro SQL Server 2005 High Availability. I know this is for 2005, but it's 95% relevant for SQL Server 2008 as well. You must read this to have a good understanding of the options available. Here are my additions to Brent's response:

Failover Clustering

If financial, power and server room resources are not a constraint then this is my preferred choice for high availability for SQL Server. You need shared disk storage, usually a SAN for this to work and I prefer to place the C drives on the SAN too for easy DR. The way I set it up is to have a quorum LUN (Q), an MSDTC LUN (M), and a mount point for each Instance of SQL Server in the cluster. Within the mount point set up a LUN for SQLData, SQLLogs, SQLBackups and optionally SQLtempdb. For ONE instance you will end up with D:\SQLData, D:\SQLLogs, D:\SQLBackups, D:\SQLtempdb (for example). For the next instance you might have E:\SQLData, E:\SQLLogs, E:\SQLBackups, E:\SQLtempdb. All of the shared disks need to be presented to all nodes in the cluster. Failover is automatic and takes around 20 seconds in my production environment. It is robust, but can be tricky to set up if you are inexperienced.

Virtualised SQL Servers

An option you haven't explored is the use of vmware ESX server to host your database servers. I really like this option but haven't the confidence to deploy it in production environments yet. I have deployed it very successfully in non-production environments and the technology is outstanding. I think it is only suitable for moderate to lightly loaded SQL Servers and should not be used if performance is critical or you have high workloads. A one to one mapping of SQL Server to ESX hosts is a very desirable configuration. vmware VMotion is great technology with much shorter downtimes than failover clustering. I saw a demonstration once of a video being played on a server and the server was failed over with the video running with no glitches. Now, that's impressive!

SQL Server replication

This may not work well for third-party applications because it may require changes to the schema. SQL Server replication was not designed for high availability, rather it was designed to make copies of data available in other locations. I would not recommend using this for high availablity due to the complexities of it. However it can be useful in certain scenarios due to the low level of granularity that it offers - you can do horizontal and vertical partitioning of data for example.

Third party disk replication

A solution such as NSI's double take could be considered for high availability also, however I prefer to use it for disaster recovery for non-SAN based systems. It basically replicates data at the block level to a target server and the target server watches the source server for availablity. If it becomes unavailable, it triggers a failover condition and you can set it up to automatically fail-over or alert for manual fail-over. Fail-over times are similar to SQL Server clustering. The advantages are you don't need any special hardware to do it, but the software licenses can be expensive.

Backup and Restore

Not really a high availability solution, but for some people with looser requirements, this very simple solution may offer everything you need. Simply backup the databases on a schedule to a backup server, and make sure the backup files are available on the target machine. Set up a job to restore the files as they are backed up and you have a crude high availability solution on the cheap.