question

sp_lock avatar image
sp_lock asked

HA with a open check book

Hi.

I am in the process of designing a SQL environement for my companies web app.

It has to be highly available and be within 2 difference Data centre's. Also I require another reporting database for users to run daily/weekly reports.

My initial thought are as follows (all power/infrastructure has been design)

Data centre 1 2 node cluster with share EMC SAN stored.

Data centre 2 1 x server for mirrored database(s) with DC1 1 x snapshot DB (of mirror) for reporting service.

Each DC will be in a 3 node VM cluster.

Do you guys have any other thought.. Also, as this is serving a web app I will advising the dev's to use the "failover" with the connection string, can this cause issues if node1 in the cluster fails over to node2??? Was just thing that the witness servers may thing that the server/db is unavailable therefore make the mirror in DC2 active!

Cheers

JL

sql-serverclusterdatabase-mirroringhigh-availability
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Robert L Davis avatar image
Robert L Davis answered

You have two choices for making sure that a mirroring failover doesn't occur as a result of the cluster failover.

  1. The official recommendation is to not use automatic failover for database mirroring. This of course means that if the entire side goes down, manual intervention will be required to fail over or you have to set up an automated process of your own to fail over mirroring if the entire principal cluster is down.
  2. My recommendation is to use automatic failover. Adjust the timeout value of the mirroring session to a value large enough so that a cluster failover will not trigger a mirroring failover. The default timeout is 10 seconds.

In addition to what Matt said, if youa re using VM's, make sure they are not all on the same VM host. If they are, all nodes go down when the host goes down.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

It sounds to me like you are on the right track. Do bear in mind though that, if you truly have an open check-book, then you want redundant everything.

I.e. servers each have 2 power supplies, servers have 2 network cards for the IP interface, servers have either 2 network cards or 2 FC cards for the SAN interface (depending on whether you are using Fibre Channel or iSCSI) etc etc.

If you are using Fibre Channel then it is worth having redundant fibre channel switches, and if you're using iSCSI, it's worth having redundant switches that are totally separate from the IP switches which will handle the server's 'front end' traffic.

Good luck.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.