Best Location for Mirror Witness

Question

Best Location for Mirror Witness

kenny782

Old Hand

Points: 302
More actions
April 26, 2011 at 2:54 pm

#235230

I'm setting up a mirror to an offsite location.
I'm trying to decide the best location for the witness, I'm pretty sure it should be the backup site.
My main concern is with the bottom half of the of this image
Server A is the principal, online and being changed.
Backup site loses internet connection but Backup server & witness are online.
However no changes are being made to it.
Backup sites internet connection comes backup what is the witness going to do with the changes made to Server A.
Considering it though the server was offline and the backup should've been principal
**Edit - Posted updated image

Viewing 9 posts - 1 through 8 (of 8 total)

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

JeremyE SSCoach Points: 16201 More actions · Answer 1

You should keep the witness server in the same site as the principal database. So in your situation a witness for server A in NY and a witness for server B in MX. The main reason for this is your second scenario would halt transactions on your principal.

From the mirroring best practices:

Loss of the mirror and the witness

Assume you have configured database mirroring with a witness. When the mirror is unavailable, the principal runs exposed. While the mirror is unavailable, if the witness is also lost, the principal becomes isolated and can’t service the clients. Even though the principal database is running, it is not available to the clients. If you attempt to connect to the database, you get the message “Database <dbname> is enabled for database mirroring, but neither the partner nor witness server instances are available: the database cannot be opened.”

kenny782 Old Hand Points: 302 More actions · Answer 2

Gotcha thanks, I updated the image for Mexico

I also clarified a little more on the NY server.

Their is a China location that is connected to both NY and the backup location via IPSec tunnels.

Almost all the NY server users are at that China location, they are the only ones I really care about keeping online.

If the few physically in the NY location are locked out so be it.

So...

If the NY connection or server go down

I need the backup server to switch to principal for the China users

If the backup sites connection were to go down

I need to make sure that the NY Server remains the principal

And the backup server doesn't try to make itself principal and cause problems when the backup locations connection comes up.

Thanks again,

Kenny

**EDIT**

I guess putting the witness in China might work but I'd rather avoid putting anything their if I can.

They run their own show, I have no control over their IT systems.

And I can't understand anything they're saying, so it could make troubleshooting difficult... 🙂

kenny782 Old Hand Points: 302 More actions · Answer 3

oh well I'll just have to put the witness in China.

In the event the NY connection goes down the server in the NY office will be worthless, but so be it.

That means 2 users in that office will be down while 20+ others will still be working via the backup server.

Thanks for the help with this.

JeremyE SSCoach Points: 16201 More actions · Answer 4

JeremyE

SSCoach

Points: 16201

April 27, 2011 at 4:10 pm

#1316952

Glad to help and good luck on your deployment.

Oliiii SSCertifiable Points: 5328 More actions · Answer 5

Database mirroring requires a very (very) reliable network as a 15sec lag (by default) will trigger a fail-over.

In your situation you might need something more resilient like replication if possible, or you might end up with a DB bouncing all the time between servers.

If your tables allow, a merge replication would fit your situation better. I does require more work but can withstand way more beating than mirroring.

kenny782 Old Hand Points: 302 More actions · Answer 6

Good point, if I ran into a delay like that I'd have some pissed off users on even if it didn't failover

I'll run some monitors the next few days and get some real numbers

The merge replication would greatly increase the risk of data loss upon failure wouldn't it?

I did look into that last week though, it seemed like it was primarily one way replication?

I was thinking lastnight I'd install a second wan connection at all sites and dedicate it to sql and other inter-server communication which should bring down latency.

Oliiii SSCertifiable Points: 5328 More actions · Answer 7

The transactional replication is the one way kind, the merge is a two way replication with some collision management (which can be easily avoided if the merged tables use uniqueid or identity primary key).

Upon failure you'll only lose data if one site is destroyed, if it's a "simple" connection lost, the change will remain in the replication queue and be sent at the next opportunity (it does keep your log from being truncated though), while both DB remain accessible.

The replications can withstand several connection failure and keep working as soon as the situation is back to normal, while the mirroring might keep your DB from being used (which is good if that's what you want).

Replication is much more work to setup though, so i guess it all depend on your connection quality.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Firstly, mirroring is way easier to set up and maintain than replication.

You may change the default timeout for mirroring using the following

ALTER DATABASE mydatabase SET PARTNER TIMEOUT 90

This will allow you to tune for your network conditions

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉