Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Failover clustering with Database Mirroring


Failover clustering with Database Mirroring

Author
Message
SQL*
SQL*
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 1726
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?

:-)
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8815 Visits: 16561
SQL* (1/23/2013)
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).

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" ;-)
SQL*
SQL*
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 1726
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.

:-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47343 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8815 Visits: 16561
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 ;-)

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" ;-)
pawana.paul
pawana.paul
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 354
I have question related to this topic.

I want to have 2 node cluster. Node 1 will have instanceA, Database ABC, Node 2 will have instanceB, Database ABC. Can I configure database mirroring between ABC database of instanceA and instanceB?

I want to provide instance level and database level redundency (like few of the features of AlwaysON).

The reason is to use standard edition.

Thanks in advance
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8815 Visits: 16561
standard edition will support a 2 node cluster

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
pawana.paul
pawana.paul
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 354
I am not clear.

Can I configure mirroring on 2 node cluster having 2 instances with primary database on one instance (InstanceA) and mirror database on one instance (InstanceB).

Thanks in advance.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8815 Visits: 16561
pawana.paul (10/20/2014)
I have question related to this topic.

I want to have 2 node cluster. Node 1 will have instanceA, Database ABC, Node 2 will have instanceB, Database ABC. Can I configure database mirroring between ABC database of instanceA and instanceB?

If all you want to do is use database mirroring you do not need a Windows Server Failover Cluster.


pawana.paul (10/20/2014)
I want to provide instance level and database level redundency (like few of the features of AlwaysON).

Database mirroring provides only database level redundancy. You would need to implement a Failover Cluster Instance of sql server to provide instance level redundancy.


pawana.paul (10/20/2014)
The reason is to use standard edition.

Supports a 2 node Failover Cluster instance
Supports only synchronous replication for database mirroring

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search