Clustering

Question

Clustering

Admingod

SSCertifiable

Points: 5997
More actions
March 15, 2019 at 3:35 pm

#377690

I have N-Way clustering setup and i need to configure the DR solution for the database located in the SQL Server N way cluster server. I am thinking of configuring DB Mirroring instead of Always on. Do you see any problems with configuring mirroring on Clustered server. What different it would be configuring mirroring on standalone server Vs on cluster server. What would i need other than server located in DR?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Brandon Forest SSCommitted Points: 1977 More actions · Answer 1

AlwaysOn FailOver Clustering (FCI) is associated with Windows Services FailOver Clustering (WSFC). This is a High Availability (HA) scenario where two (or more) servers share a SAN, or WAN, or NAS. The first server is the Active Node (Node1). The second server is the Passive Node (Node2). A 3rd server called the Witness or Quorum server, does a heartbeat check on both servers on a set interval, usually between 1 to 3 seconds. If either of the servers doesn't answer the heartbeat ping, the other server is declared the primary node. If that is Node1, then no changes occur. If that is Node2, then the Witness server automatically fails-over the control of the databases to that node. Failover usually is within 15 seconds. Any uncommitted transactions are automatically rolled-back.

Database mirroring is for single databases, and also has two or more nodes. In this scenario, each of the nodes has it's own storage. The mirroring mechanism will either synchronously or Asynchronously ship transactions to the mirrored node. Synchronous mode would wait for the validation signal from the mirror before committing the transaction in the primary. Asynchronous mode would ship off the transactions and go straight to a local commit. Again a Witness server keeps tabs on both nodes via a heartbeat ping. If the primary node goes offline, then that single database would failover to the passive node.

AlwaysOn Availability Groups improves upon that concept by grouping databases together in Availability Groups. When one database fails-over, they all do. You must have AlwaysOn FCI configured to utilize AlwaysOn Availability Groups, as the have some common libraries.

references: https://www.mssqltips.com/sqlservertip/3150/adding-sql-server-alwayson-availability-groups-to-existing-failover-clusters/
https://blogs.technet.microsoft.com/canitpro/2013/08/19/step-by-step-creating-a-sql-server-2012-alwayson-availability-group/
http://www.sqlservercentral.com/blogs/sql-server-overview/2018/01/13/alwayson-availability-groups-vs-alwayson-failover-cluster-instances/

Admingod SSCertifiable Points: 5997 More actions · Answer 2

Thanks for the reply.
Sorry if i wasn't clear with my question.I am looking to configure a mirroring for the database which is part of Always on FailOver Clustering Server.
So my question is that configuring mirroring would be any different for a database which is located on Failover Clustering? Also, if the fail-over happens how does the mirroring would handle in that scenario?

yelouati SSC Enthusiast Points: 108 More actions · Answer 3

It seems you are on SQL Server 2012 or higher. Database Mirroring is not an option.

That said, just join the DR node to the cluster. Then weigh the need for a fileshare witness instead of a quorum disk. It is not necessary in your case but do make sure you don't end up in a split brain scenario.

Admingod SSCertifiable Points: 5997 More actions · Answer 4

Why. SQL Server 2012 does have database mirroring.
So my primary server would be part of SQL Server clustering and mirror would be in DR. So you saying the add the DR to the part of SQL Server cluster and configure Database mirroring for DR?

yelouati SSC Enthusiast Points: 108 More actions · Answer 5

Actually you can. I think you want to avoid adding the third node to the cluster. Sure. ALWAYSON is better though for a variety of reasons. In any case, failover clustering does not affect DB Mirroring, so go for it. ALWAYSON requires you add the node to the cluster but not DB mirroring.

Personally, I'd opt for ALWAYSON especially if I have more than 1 DB to protect, so they failover consistently with each other. But to each their environment and constraints.