January 23, 2013 at 4:31 am
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?
🙂
January 23, 2013 at 5:28 am
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.
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" 😉
January 24, 2013 at 5:27 am
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.
🙂
January 24, 2013 at 5:59 am
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
January 24, 2013 at 6:34 am
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" 😉
October 20, 2014 at 12:24 pm
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
October 20, 2014 at 1:20 pm
standard edition will support a 2 node cluster
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 20, 2014 at 2:03 pm
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.
October 22, 2014 at 5:36 am
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" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply