Clustering with Remote based mirroring support help

Question

Post reply

Clustering with Remote based mirroring support help

ebenraja

SSC Eights!

Points: 955
More actions
April 23, 2011 at 9:30 am

#235215

Hello,

One of my recent requirement is to provide my client with an 100% high availability solution for SQL Database.

Thinking about proposing a Clustering solution, 2 Cluster pairs with 2 independent SQL instances running on them, for eg

Cluster Pair 1:

Node A : 025 N1

Node B : 025 N2

SQL instance : 025SQL

Cluster Pair 2:

Node A : 026 N1

Node B : 026 N2

SQL Instance : 026 SQL

Principal database name : 'Remote db' present in 025SQL

Mirrored database name : 'Remote db' present in 026 SQL

How should i setup my witness in this scenario for favoring an automatic failover ? Can my Witness be just a standalone VM or Physical server, is there any additional disk\process requirement to set up a Witness server.

Do any of you have any alternate moves for setting up an 100% availability scenario, if so please do help.;HArdware concepts like EMC's SRDF etc or SAN based mirror will be taken from Hardware engineer perspective, am to suggest from DB end only.

I know Log ship is out of the box for 100% availability, if there is any difference to Remote based mirror on Cluster , help me know

Thanks

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

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

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 1

It will be same as you create witness server in a normal server environment,in cluster environment you always use cluster SQL name,now scenerio is

Cluster 1 Name Primary : MSSQL_PRI

here is 2 Nodes Active/Passive

Cluster 2 Name Secondary : MSSQL_SEC

here is 2 Nodes also Active/Passive

you can create witness server on the PRIMARY cluster server as well as SECONDARY Node or create on a separate pc or server as you want,there is no high requirement for witness server because its use for heartbeat of a primary server nothing else

and if you developed an application which is dotnet 3.5 or later then you can define failover connection in the connection string of the application,this will work autmatically

My Blog
www.aureus-salah.com

ebenraja SSC Eights! Points: 955 More actions · Answer 2

ebenraja

SSC Eights!

Points: 955

April 28, 2011 at 7:47 am

#1317257

Thanks Syed, the info is useful

ebenraja SSC Eights! Points: 955 More actions · Answer 3

Syed,

If we have developed an application which relies on dotnet 3.5 or later then we can define failover connection in the connection string of the application, this will work automatically

Can you help me know on where and how to do the changes to application, where would this connection string be present, will the application adminsitrator be aware of doing this, does this have to do anything with ODBC etc.

Thanks

Eben

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 4

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

http://msdn.microsoft.com/en-us/library/ms366348.aspx

http://msdn.microsoft.com/en-us/library/ms130822.aspx

My Blog
www.aureus-salah.com

ricer Right there with Babe Points: 722 More actions · Answer 5

I am currently setting up a clustered mirrored server on a offsite collocation for DR.

The problem with having automatic failover for mirroring is that if the principal clustered server fails over to another node in the cluster the witness server will see this and then fail over to the mirror server. You might not want this behavior and would prefer the mirroring to remain on the principal server so we are not going to use the automatic failover. Here is an article you can read about your exact situation.

http://technet.microsoft.com/en-us/library/ms191309.aspx

-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer

ebenraja SSC Eights! Points: 955 More actions · Answer 6

ebenraja

SSC Eights!

Points: 955

May 5, 2011 at 2:52 am

#1320261

Yes thats a well made statement, offcourse when sysadmin is going to apply windows patches it can affect the database with bounce..

Eben