Principal server unexpected shutdown?

Question

Principal server unexpected shutdown?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
September 7, 2013 at 5:05 am

#297566

Hi,
Principal server unexpected shutdown and also not available Primary database , in that situation secondary server MIRROR database should be bring online mode for user access as well as application connectivity (mirror configured High Performance mode - version SQL2K8R2)
What command should be executed in secondary database server for bring online? Please confirm as below command
Option 1
Use master
Go
ALTER DATABASE <Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
Go
RESTORE DATABASE <Databasename> WITH RECOVERY
option 2
Use master
Go
ALTER DATABASE <Databasename> SET PARTNER OFF
GO
RESTORE DATABASE <Databasename> WITH RECOVERY

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

ananda.murugesan (9/7/2013)
Hi,
Principal server unexpected shutdown and also not available Primary database , in that situation secondary server MIRROR database should be bring online mode for user access as well as application connectivity (mirror configured High Performance mode - version SQL2K8R2)
What command should be executed in secondary database server for bring online? Please confirm as below command
Option 1
Use master
Go
ALTER DATABASE <Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
Go
RESTORE DATABASE <Databasename> WITH RECOVERY
option 2
Use master
Go
ALTER DATABASE <Databasename> SET PARTNER OFF
GO
RESTORE DATABASE <Databasename> WITH RECOVERY

Option 1 without the RESTORE DATABASE.

Option 2 will break database mirroring.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Is it any data loss after firing as below command? pl. confirm

Use master

Go

ALTER DATABASE <Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Thanks

ananda

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

ananda.murugesan (9/7/2013)
Is it any data loss after firing as below command? pl. confirm
Use master
Go
ALTER DATABASE <Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
Thanks
ananda

If I read your original post correctly, meaning that you are running asynchronously, then yes you could lose some data.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Ok.. understand.. Thanks for reply

1. High performance mode - there is some data loss due to ackownledgement not received to Principal server from secondary server ..( so how to resolve zero data loss in that mode)

2. High safty mode - committed ackownledgement recived to Principal server from secondary server. so there is no data loss when ever shutdown principal server.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 5

ananda.murugesan (9/7/2013)
Ok.. understand.. Thanks for reply
1. High performance mode - there is some data loss due to ackownledgement not received to Principal server from secondary server ..( so how to resolve zero data loss in that mode)
2. High safty mode - committed ackownledgement recived to Principal server from secondary server. so there is no data loss when ever shutdown principal server.

If your mirroring mode is set to high performance, then change it to high safety mode before failing over....this is one way to make sure that they are insync.

However, if your mirror is in high performance mode and if principal goes down, then definitely there will be some data loss.

Regards,
SQLisAwe5oMe.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

SQLisAwE5OmE (9/7/2013)
However, if your mirror is in high performance mode and if principal goes down, then definitely there will be some data loss.

Not definitely. There may be some data loss.

Depends whether or not the mirroring was in the synchronised state at the time of failure or not. If it was, then there'll be no data loss. if it was synchronising, then there will be data loss.

The force service option is required in both cases as the mirror has no way of knowing whether or not it was in sync if running in asynchronous mode.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 7

GilaMonster (9/7/2013)
SQLisAwE5OmE (9/7/2013)
However, if your mirror is in high performance mode and if principal goes down, then definitely there will be some data loss.
Not definitely. There may be some data loss.
Depends whether or not the mirroring was in the synchronised state at the time of failure or not. If it was, then there'll be no data loss. if it was synchronising, then there will be data loss.
The force service option is required in both cases as the mirror has no way of knowing whether or not it was in sync if running in asynchronous mode.

Yes Gail, you are correct. Thanks for clarifying.

Regards,
SQLisAwe5oMe.