Happy New Year
From all the folks here at SSWUG!
High Availability
What is the big difference between a SQL Server Cluster and Database mirroring? Why would you use one over the other? Can you mix and match them? These questions, along with many others, are common from individuals considering High Availability techniques with SQL Server. Throw SQL Azure and SQL Server 2012 in the mix and you have a lot of options available to you.
Here’s a quick high level comparison to get you started.
Clustering is performed at a SQL Server Instance level. That means all databases hosted by an instance are managed by the servers participating in the cluster. Only one server instance may have both read and write access to a database. Some instances can be made to access the database in a read only mode with the correct edition of SQL Server and other configuration steps.
Clusters share the same hard disk space. When the active machine goes offline, the hard disk is mounted by the passive machine, and it takes over the SQL Server read/write responsibilities. All machines operate on a virtual IP address for the SQL Service cluster, shared by all participants in the SQL Cluster.
Mirroring is configured one database at a time, and results in complete duplication. There is a lot more to it than that. The basic difference from clustering is that each instance uses its own disk space…nothing is shared. They are completely different machines. Additionally, all machines have their own IP address. In order for mirroring to work, the client must know about both instances. When a failover occurs, the client simply selects the mirror for all activity instead.
Mirroring is similar to replication. The only difference is that when you configure replication you are not required to replicate the entire database. You can simply replicate one or more tables, as well as some other interesting options. replication takes a lot of configuration and maintenance.This contrasts with database mirroring in that the duplication is configured once for the entire database, and requires little or no maintenance once established.
There are other techniques, some even outside of SQL Server itself, allowing for High Availability. The biggest question is, do you need it? Can you live without it? What does it cost to implement? We’ll talk about that a little bit tomorrow.
I haven’t had the opportunity to work with SQL Server 2012 High Availability systems yet. So, if you have had experience in that area, why not share some of that by writing to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Go Beyond SCOM Monitoring with Foglight for SQL Server
read more)
Featured Script
dba3_CreateViewFromTwoDifferentDatabasesOnSameServer_Demo
It is generally possible to create a view that combines data from two different DBs on the same Sql Server instance… (read more)