Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What are the main differences between Mirroring and Failover Clustering?

What kind of problem each one solves and in what kind of scenarios is each one recommended over the other?

share|improve this question
1  
To start check this section from microsoft BOL: High Availability Solutions Overview [msdn.microsoft.com/en-us/library/ms190202.aspx] and Selecting a High Availability Solution[msdn.microsoft.com/en-us/library/bb510414.aspx] –  DaniSQL Mar 6 '12 at 15:04
1  
To add to your reading, I have a blog post I wrote up a couple weeks ago on this topic. sqlmag.com/blog/troubleshooting-sql-server-storage-problems-51/… –  mrdenny Mar 6 '12 at 20:16
add comment

2 Answers

up vote 7 down vote accepted

Mirroring is a technology that increases the high availability for user databases and creates a copy of the source db that is maintained in real time. This is done by delivering all the active transaction log records to the mirror server.

From MSDN:

"Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Database mirroring is supported in SQL Server Standard and Enterprise. "

Clustering is a technology that provides high availability for the entire SQL Server instance. This means that it's possible to have to servers with identical structure (OS, applications, services, service packs, patches...etc) and with shared storage (the data itself).

From MSDN:

"SQL Server failover clustering provides high-availability support for an entire SQL Server instance. SQL Server failover clusters are built on top of Windows Server failover clusters. To create a SQL Server failover cluster, you need to first create the underlying Windows Server failover cluster."

Here's a course from the Microsoft Virtual Academy about High Availability as offered today by MS: Understanding Microsoft’s High-Availability Solutions.

share|improve this answer
    
Great link! I'll be taking this course! –  ivanmp Mar 7 '12 at 12:03
add comment

Mirroring is at the database level. A disadvantage is that even when set to high safety with automatic failover, you will still need to have scripts to copy jobs, logins, alerts and so forth from the principal to the mirror server. Clients need to include the mirror details in connection strings to facilitate failovers. Advantages of mirroring include automatic fixing of corrupted pages and storage is not a SPOF. I have found that automatic failovers with mirroring are faster than with clustering (which necessitates a complete service restart).

Clustering is allows for complete including level failover including system databases. It is transparent to clients. The disadvantage of clustering is that it requires shared storage, usually a SAN which both a SPOF and often expensive.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.