MS SQL Server - Database Mirroring in High Safety Mode

Author

Database Mirroring in High Safety Mode

Ola Hallengren

2006-10-24, 6:33 pm

Hello!

I'm reading about database mirroring in High Safety Mode (Synchronous with
no witness). I'm a little confused about what happens if the mirror server
disappears.

From Books Online
"If the mirror server instance goes down, the principal server instance is
unaffected and runs exposed (that is without mirroring the data)."
http://msdn2.microsoft.com/en-us/library/ms179344.aspx

I was to the course [Upgrading your Database Administration Skills to SQL
Server 2005] and the course material does not say the same."Scenario:
Synchronous mirroring without a witness server. If the mirror server becomes
unavailable, the principal server will take the database offline. This action
attempts to maximize the protection of the database and ensure the integrity
of its contents"

I've made tests that shows that Books Online is right,
but I would like to know if this is a behaviour that has changed.

I also would like to know if it's possible to run in a more secure mode,
where exposed operations is not allowed.

Best regards

Ola Hallengren

David Browne

2006-10-24, 6:33 pm

"Ola Hallengren" < OlaHallengren@discus
sions.microsoft.com> wrote in message
news:C6208896-3167-4E66-B4EB- 15D10BD1E1F8@microso
ft.com...
> Hello!
>
> I'm reading about database mirroring in High Safety Mode (Synchronous with
> no witness). I'm a little confused about what happens if the mirror server
> disappears.
>
> From Books Online
> "If the mirror server instance goes down, the principal server instance is
> unaffected and runs exposed (that is without mirroring the data)."
> http://msdn2.microsoft.com/en-us/library/ms179344.aspx
>
> I was to the course [Upgrading your Database Administration Skills to SQL
> Server 2005] and the course material does not say the same."Scenario:
> Synchronous mirroring without a witness server. If the mirror server
> becomes
> unavailable, the principal server will take the database offline. This
> action
> attempts to maximize the protection of the database and ensure the
> integrity
> of its contents"
>
> I've made tests that shows that Books Online is right,
> but I would like to know if this is a behaviour that has changed.
>
> I also would like to know if it's possible to run in a more secure mode,
> where exposed operations is not allowed.
>

It's not built in, but if you would prefer to shut down the database rather
than run exposed, you should be able to use an event notification.

Alerting on Database Mirroring Events
http://www.microsoft.com/technet/pr...ringevents.mspx

David

Ola Hallengren

2006-10-24, 6:33 pm

Thanks, David.

Am I right that this behavour has changed since the course material states
the following? "If the mirror server becomes unavailable, the principal
server will take the database offline."

About the event notification. I understand the approach, but would that
guarantee that not a single transaction is committed in exposed mode?

"David Browne" wrote:

>
>
> "Ola Hallengren" < OlaHallengren@discus
sions.microsoft.com> wrote in message
> news:C6208896-3167-4E66-B4EB- 15D10BD1E1F8@microso
ft.com...
>
> It's not built in, but if you would prefer to shut down the database rather
> than run exposed, you should be able to use an event notification.
>
> Alerting on Database Mirroring Events
> http://www.microsoft.com/technet/pr...ringevents.mspx
>
>
>
> David
>
>

Tibor Karaszi

2006-10-24, 6:33 pm

The course material was based on a pre-release of SQL Server, and that behavior explained in there
wasn't deemed practical, so it was changed to what you see in BOL.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Ola Hallengren" < OlaHallengren@discus
sions.microsoft.com> wrote in message
news:C6208896-3167-4E66-B4EB- 15D10BD1E1F8@microso
ft.com...
> Hello!
>
> I'm reading about database mirroring in High Safety Mode (Synchronous with
> no witness). I'm a little confused about what happens if the mirror server
> disappears.
>
> From Books Online
> "If the mirror server instance goes down, the principal server instance is
> unaffected and runs exposed (that is without mirroring the data)."
> http://msdn2.microsoft.com/en-us/library/ms179344.aspx
>
> I was to the course [Upgrading your Database Administration Skills to SQL
> Server 2005] and the course material does not say the same."Scenario:
> Synchronous mirroring without a witness server. If the mirror server becomes
> unavailable, the principal server will take the database offline. This action
> attempts to maximize the protection of the database and ensure the integrity
> of its contents"
>
> I've made tests that shows that Books Online is right,
> but I would like to know if this is a behaviour that has changed.
>
> I also would like to know if it's possible to run in a more secure mode,
> where exposed operations is not allowed.
>
> Best regards
>
> Ola Hallengren
>

Ola Hallengren

2006-10-24, 6:33 pm

Thanks for making clear that this is a changed behaviour.

What worries me is the following.
You have some system instabilities (network, power whatever) and suddenly
your principal server is down (dead).
The dba is called in and is thinking about bringing the mirror server online.
Now there is two scenarios.
1. The servers were synchronized when the principal server went down.
If that's the case then there is no problem bringing the mirror server
online.
2. The servers were not synchronized (running exposed) when the principal
server went down. (That could be because of a network device was temporary
down just before.) If that's the case then there could be severe consequences
bringing the mirror server online.
The problem as I see it is that there is no way of knowing which scenario
that is true.

"Tibor Karaszi" wrote:

> The course material was based on a pre-release of SQL Server, and that behavior explained in there
> wasn't deemed practical, so it was changed to what you see in BOL.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Ola Hallengren" < OlaHallengren@discus
sions.microsoft.com> wrote in message
> news:C6208896-3167-4E66-B4EB- 15D10BD1E1F8@microso
ft.com...
>
>

David Browne

2006-10-24, 6:33 pm

"Ola Hallengren" < OlaHallengren@discus
sions.microsoft.com> wrote in message
news:14AEFB8F-82EA-4220-9CE9- AD0F10BF702F@microso
ft.com...
> Thanks, David.
>
> Am I right that this behavour has changed since the course material states
> the following? "If the mirror server becomes unavailable, the principal
> server will take the database offline."
>
> About the event notification. I understand the approach, but would that
> guarantee that not a single transaction is committed in exposed mode?
>

Events are delivered asynchronously, so I don't think there's a guarantee.
You would just be able to limit your exposure.

David

Tibor Karaszi

2006-10-24, 6:33 pm

I understand the situation, and there's probably not simple solution for that except using a
witness.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Ola Hallengren" < OlaHallengren@discus
sions.microsoft.com> wrote in message
news:72E953E4-D749-4DDA-890A- 3532D0E8BFFE@microso
ft.com...[color=darkred]
> Thanks for making clear that this is a changed behaviour.
>
> What worries me is the following.
> You have some system instabilities (network, power whatever) and suddenly
> your principal server is down (dead).
> The dba is called in and is thinking about bringing the mirror server online.
> Now there is two scenarios.
> 1. The servers were synchronized when the principal server went down.
> If that's the case then there is no problem bringing the mirror server
> online.
> 2. The servers were not synchronized (running exposed) when the principal
> server went down. (That could be because of a network device was temporary
> down just before.) If that's the case then there could be severe consequences
> bringing the mirror server online.
> The problem as I see it is that there is no way of knowing which scenario
> that is true.
>
> "Tibor Karaszi" wrote:
>

Sponsored Links