Mirroring - High Availability

Question

Mirroring - High Availability

mostar5

Old Hand

Points: 303
More actions
May 13, 2008 at 1:27 pm

#190516

Hi all
I want to setup db mirroring with auto-failover, is it possible to accomplish this on 2 servers only?

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Yes. A witness server is recommended, but not required.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

mostar5 Old Hand Points: 303 More actions · Answer 2

So do I setup witness on mirror server, principal, or both?

GSquared SSC Guru Points: 260824 More actions · Answer 3

Best set it up on the mirror. If the whole idea is to handle problems when the principle goes down, you don't want the principle going down to take the witness down with it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 4

To have the mirror and witness on the same physical server you need a separate SQL instance running to host the witness.

Also, if the operating mode is high safety with automatic failover and the server with the witness and mirror goes down then the principal database will become unavailable too.

mostar5 Old Hand Points: 303 More actions · Answer 5

OK I got it, one more thing, is there any way to test the setup without actually taking down the principal server?

GSquared SSC Guru Points: 260824 More actions · Answer 6

There may be ways to test it without taking that down, but really, do you want a test that tells you, "oh sure, it'll probably work", or do you want to take down the primary server and KNOW it will work?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 7

GSquared makes a good point.

If your intended purpose of mirroring is to allow for rolling updates of your server then a manual failover test would probably be good enough. You can manually failover from the principal using [font="Courier New"]ALTER DATABASE SET PARTNER FAILOVER[/font]. Or, you can stop SQL service on any of the three instances to see the effects.

However, if you're trying to use it to protect against an unforseen outage then doing the following from the principal would be a little more realistic: rebooting, removing a drive or two, disconnecting the network cable.

Have a look at this article for some other ideas: Database Mirroring in SQL Server 2005

mostar5 Old Hand Points: 303 More actions · Answer 8

Ok I guess in order to be sure about the setup I need to recreate a disaster :D'

Oh one more Q, hope last one, I am looking at the mirroring monitor and under principal server it says the Witness Connection is connected but under the Mirror it says disconnected. Is this because mirror and witness are on the same server?

after I've set up endpoint, certs, users... I've executed from Principal server:

alter database DB set partner = 'TCP://172.16.1.1:7024';

alter database DB set witness = 'TCP://172.16.1.1:7025';

and from mirror server:

alter database njdb set partner = 'TCP://172.16.1.2:7024';