Failover clustering with Database Mirroring

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » Failover clustering with Database Mirroring

Failover clustering with Database Mirroring

Rate Topic

Display Mode

Topic Options

Author

Message

SQL*

Posted Wednesday, January 23, 2013 4:31 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 414, Visits: 1,451

hi all,
I'm learning these HA/DR things.

"Failover clustering with Database Mirroring"
What does it means?

Clustering: Two nodes being synchronized. (Physically & SQL Server level)

Mirroring: Primary & Secondary (Standby server).

What is benefit over individuals?
How can we combined these two?
In which scenario it can be done?

Smile

Post #1410469

Perry Whittle

Posted Wednesday, January 23, 2013 5:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 5,201, Visits: 11,150

SQL* (1/23/2013)

Not quite right my friend, this is more accurate.

Clustering: Has a standby node which takes ownership of cluster resources after failover. Nodes are synchronised in that all information regarding the cluster resources is synchronised between the nodes.

Mirroring: Principal and Mirror, Two nodes being synchronized. at database level

SQL* (1/23/2013)

What is benefit over individuals?

Basically, Clustering provides redundancy at the SQL Server instance level, Mirroring provides redundancy at the database level.

SQL* (1/23/2013)

How can we combined these two?

Combining clustering and mirroring is possible, although with AlwaysOn groups in SQL Server 2012, you may not need to do this. It all depends on your requirements. What makes you think you require both these technologies combined?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1410502

SQL*

Posted Thursday, January 24, 2013 5:27 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 414, Visits: 1,451

Hi,

Thanks,

But can you tell me any scenarios where we can use Clustering/Mirroring together.

Actually I am very much confused on High availability/Disaster Recovery options.

High availability : Means 99.99% of times data must be available.

For this if we take an example, if the Primary db goes down then the secondary will become as primary in each and every options (Clutering/Miroring/Logshipping), we are achieving this Manually/Automatically. Why the Primary goes down, may be because of H/W failures or scheduled maintenance right... then we can say this as Disaster Recovery.... what is the point to mention two technical words HA/DR for the same thing.

Smile

Post #1411080

GilaMonster

Posted Thursday, January 24, 2013 5:59 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

SQL* (1/24/2013)

High availability : Means 99.99% of times data must be available.

By who's definition? 4 nines is too much for some, not enough for others. Your uptime requirements for HA are defined by the business, not an arbitrary set of numbers.

For this if we take an example, if the Primary db goes down then the secondary will become as primary in each and every options (Clutering/Miroring/Logshipping),

Not necessarily. A single database failure may well not force the cluster to fail over as clustering is designed to protect the instance and the server. If a database becomes unavailable because the disk array it was on has failed, clustering will be of no help there because the databases are on shared storage (unless you're doing a cluster without shared storage, which is hard)
If someone drops a critical table, neither clustering nor mirroring will help there. Log shipping however might allow for the table to be retrieved.

what is the point to mention two technical words HA/DR for the same thing.

They're not the same thing.

High availability - remaining available and online no matter what
Disaster recovery - getting back online after some disaster

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1411096

Perry Whittle

Posted Thursday, January 24, 2013 6:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 5,201, Visits: 11,150

SQL* (1/24/2013)

Hi,

Thanks,

But can you tell me any scenarios where we can use Clustering/Mirroring together.

I'll keep this brief as there are already a wealth of articles and white papers on this subject and I do have a day job Wink

A typical use of clustering and mirroring would be to provide mitigation in the following scenario

Live site

has a 2 node cluster serving the SQL Server instance that has the database "ImportantFinancialData". The 2 node cluster's only purpose is to provide high availablility in that, if the active node suffered a CPU failure, there is a spare node to continue serving the database to clients.

DR Site

On the DR site there is a sql server instance that is a mirror partner to the Primary database "ImportantFinancialData" on the Live site. The only purpose of this DR server is to serve a copy of the database if the cluster is lost on the Live site.

SQL* (1/24/2013)

Actually I am very much confused on High availability/Disaster Recovery options.

It would help if you understand a little about each of the technologies about what they do and how they achieve it, you would then be able to see how they can bolt together as a single entity.

Clustering purely provides hardware mitigation at the server\instance level. If the active node fails the whole clustered sql server instance fails over not just individual databases.

Database mirroring provides failover capability at the database level. If a mirrored database fails over it does not fail all databases on the instance of SQL Server. The mirror database is in a state that does not allow users to query the database unless you are using the approrpiate edition of sql server and have snapshots configured.

SQL* (1/24/2013)

High availability : Means 99.99% of times data must be available.

For this if we take an example, if the Primary db goes down then the secondary will become as primary in each and every options (Clutering/Miroring/Logshipping), we are achieving this Manually/Automatically. Why the Primary goes down, may be because of H/W failures or scheduled maintenance right... then we can say this as Disaster Recovery.... what is the point to mention two technical words HA/DR for the same thing.

The availability depends on your SLA, it just numbers but some organisations actually factor in 5% downtime for example as a maintenance window.

HA and DR are essentially 2 different things. Providing HA just means that my 2 cluster nodes on my Live site ensure that the database server is always available even when i want to apply patches.
DR can mean so much but is essentially a means of providing service when the Live site has disappeared down a black hole in the planets surface.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1411107

« Prev Topic | Next Topic »

Permissions