Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Database Mirroring FAQ: If the principal fails while running in high performance mode, what options do I have for bringing the mirror online?

Question: If the principal fails while running in high performance mode, what options do I have for bringing the mirror online?

This question was sent to me via email. My reply follows.

Quick question on DB mirroring client redirection after Principal fails ...

Consider a simple scenario with Asynchronous mirroring:

  1. We are implementing asynchronous [high performance] mirroring without witness.
  2. The client application connects to the mirroring session using below connection string:

    Source={PrincipalServerName};Initial Catalog={DBName};Integrated Security=SSPI;Failover Partner= {MirrorServerName}
  3. Now due to some disaster, the Principal Server goes offline and will be offline for let’s say next 48 hours.
  4. We notice that client connections fail since primary is offline and a failover has not yet be done.
  5. What steps should be taken now to connect the client application to the mirrored database?

I can force the service or break the mirroring session (assuming with possible data loss) and re-setup later when Principal comes online.

  1. Is above statement correct?
  2. If yes, what changes do I need do in the connection strings for force service and breaking the mirror?
  3. Would connection string in step#2 works with failover partner parameter?

My answer:

  1. Yes, those are your only options for bringing the mirror online at this point. Forcing the service is preferable.
  2. If you force the service, no changes will need to be made to the connection strings because the clients will be able to connect to the failover partner.

    If you break mirroring, the Failover Partner parameter of the connection string will not be honored because the database is not currently participating in mirroring. You would have to alter the connection string to point at the mirror as the principal. One caveat to this is if you were already running on the original mirror as the current principal and the server you force service on is listed as the principal in the connection string already, no changes will need to be made.
  3. As described above. Always yes for forced service, usually no but sometimes yes for breaking the mirror.

Comments

Posted by Ajay Prakash on 17 February 2009

Good compilation of question. One doubt, will force service work even if the client wants to modify the data.

Posted by Robert Davis on 18 February 2009

Forcing the service will work as long as the principal database is offline. Any transactions on the original principal that have not been applied to the mirror will be lost when the principal returns online and mirroring is resumed.

Posted by Heslous on 8 June 2010

I did test this way, can you confirm i'm in the right path ???

Create Database TESTDB  in server A

backup TESTDB

Restore TESTDB with mode "NORECOVERY" in server B

Configure mirroring from server A

principal server A

Mirror : server B

Witness( No witness)

Start the mirroring  

--> OK  both are now syncrhonized  

GREAT  but i need to be prepared of a crash somewhere ..

so let's start simulation.

I stop SQL server on server A

Mirror in server B is still alive  but is now disconnected from mirroring  status : (Mirror, Disconnected/In Recovery)

in order to allow the application to connect to server B and be able to continue to work i need to force TESTDB in server B to service

ALTER DATABASE TESTDB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The mirror server immediately transition to principal server, and mirroring is suspended.

then its status moves to “principal, disconnected”.

i restart now SQL Server on server A

the TESTDB on this box has now the status : "mirror,suspended/restoring"

I can resynchonized them by resuming the mirroring function. but i would still have my principal in server B.

how can i handle to get back my principal to server A ??

am i supposed to rebuild the mirroring from scratch ??

Thanks al lot for helping  i'm sorry this was a lot to read.

Posted by Robert L Davis on 8 June 2010

Hi Heslous. Mirroring should resume when Server A is back online. If not, issue the following command on ServerB:

Alter Database TESTDB Set Partner Resume;

Once mirroring is running and the two partners are synchronized, you can failover from ServerB to ServerA by issuing the following command on ServerB:

Alter Database TESTDB Set Partner Failover;

This will swap the roles of the 2 databases.

Leave a Comment

Please register or log in to leave a comment.