Database Mirroring with clustered Instances, and failover procedures

Category: sql server mirroring

Question

Samer F. Mustafa on Sat, 22 Aug 2015 09:10:30


Dears,

my setup as follow:

SiteA:

SQLA-01

SQLA-02

ClusterA (SQLA-01,with SQLA-02)

and application server 

SiteB: will be used as DR Site

SQLB-01

SQLB-02

ClusterB(SQLB-01,SQLB-02)

i want to use SQL Mirroring as DR solution for SQL i could not find how to build the mirroring using clustered environment, any help is appreciated.

one more thing i want to know what is the procedure for failover and failback.

and if clusterA is down can i  failover the database on clusterB. and once clusterA came alive what will happen?


Samer F. Mustafa Microsoft Technical Team Leader. sf_mustafa@hotmail.com

Replies

Lydia Zhang on Mon, 24 Aug 2015 02:40:11


Hi Samer,

Database mirroring will work between clusters, and the process of setting database mirroring in clustered environment is similar to the process of creating database mirroring between two standard-alone instances. For more detailed steps, please review this article.

The failover procedure depends on the database mirroring operating mode that you configure. When you mirror a database in high-safety mode with automatic failover, if the node running the current principal server fails, automatic failover of the database begins within a few seconds, while the cluster is still failing over to another node. The database mirroring session fails over to the mirror server on the other, and the former mirror server becomes the principal server. The new principal server rolls forward its copy of the database as quickly as possible and brings it online as the principal database. After the cluster failover completes, which typically takes several minutes, the failover clustered instance that was formerly the principal server becomes the mirror server.

However, if you intend to mirror a database in high-performance mode, it is recommended to place the principal server on the failover clustered instance of a cluster and placing the mirror server on an unclustered server in a remote location, and you can force service onto the mirror server when the entire clusterA has problems.

Reference:
Database Mirroring and SQL Server Failover Cluster Instances
https://msdn.microsoft.com/en-us/library/ms191309(v=sql.120).aspx

Thanks,
Lydia Zhang

Kumar muppa on Mon, 24 Aug 2015 04:15:23


You can setup mirroring between your active nodes of the two sites.

you can use manual fail over or automatic.

TO use automatic you need synchronous commit.

If you use async you do not know how data do you loose in case of disaster.

Another advise if you have a single cluster across two sites and it is SQL 2012 enterprise edition you can use always on availability groups.

Samer F. Mustafa on Tue, 22 Sep 2015 13:00:05


Dears,

many thanks for your valuable information, but i still have one question and i hope that you can help me with

now i have configured the DB Mirroring and i am using high Safety without automatic failover, now once the principle cluster goes down i used the the following statement to failover Alter Database <DB_Name> set partner force_service_allow_data_loss.

and i was able to force the failover, but once the original cluster come alive shall i use the resume Mirror or i have to remove the mirror from the current principle and start creating the mirror over?

and is it a best practice to have the high safety for such implementation, or shall i use the high performance?

thanks in advance

Andreas Kreuzberg on Fri, 25 Sep 2015 13:57:55


Hi,

if a failover happend, you didn't have to resume or to rebuild the mirror again. You just had to run the same script "Alter Database <DB_Name> set partner force_service_allow_data_loss." 

on the principal.

You had to decide which mirror-mode you want to use. For mirror with sychronous mode and witness, you need a fast network between both server. Every Transaction had to be commited from the "synchronized" server, and this could cost time, if your network is not fast enough, and you had a lot of transactions on your server.

Best regards,

Andreas