locked
Clarification RRS feed

  • Question

  • I just finished getting a local database mirrored to an offsite "DR" server.  Interesting experience getting that working, but that's another story!   It's set up as "High Safety without automatic failover (synchronous) (without a witness).  I was hoping I could get a sanity check in this flow....

     

    Local database gets updated each morning with a few meg of data, so it's pretty low use.  It's mirrored through a T1 (soon to be dual).  My remove copy was restored from yesterday’s backup, and the mirror was sync'd in less than 10 minutes, so I don't expect bandwidth to be an issue...

     

    In the event that my local server or network is down, I'd be able to log into my remote server, and run my application remotely.  From what I've seen, the "Failover" option is only available on the "Primary" server?  Will this become an option if the remote no longer sees the primary, or do I need to do this through the command: ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS?

     

    Since the updates are so limited, I'm not too concerned with data loss, but will need to make sure I can get this running quickly if needed...

     

    As I understand it, this will put the mirrored database as the primary, and when the old primary comes back only, it'll become the new mirror?  I can then let them sync, and force a failover from the remote system to put the primary back to the local network. 

     

    I just want to make sure I understand this correctly before I start testing.  Thanks for any assistance!

     

    Steve

     

    Tuesday, January 22, 2008 7:15 PM

Answers

  • Steve,
             You have the following options for failover as per the below link, http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

    Safety Full with Witness (High Availability)

    This scenario provides the high safety with automatic failover. In the event of the failure of the principal, the mirror forms a quorum with the witness. Automatic failover will take place, thereby minimizing the database downtime.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror, respectively. Server_A fails. Following the automatic failover, Server_B takes on the role of the principal. However, since there is no mirror after the failover, the mirroring state is DISCONNECTED and the principal is exposed. Once the database on Server_A becomes operational, it automatically assumes the role of the mirror.


    Safety FULL without a witness (High Protection)

    This scenario provides high safety, but automatic failover is not allowed. In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

    ALTER DATABASE <database name> SET PARTNER OFF
    RESTORE DATABASE <database name> WITH RECOVERY

    Once Server_A becomes available, you need to re-establish the mirroring session.

    Safety OFF (High Performance)

    If the safety level is OFF, the witness doesn’t add any value to the database mirroring scenario. Therefore, it is recommended that if you plan to run database mirroring when the safety level is OFF, don’t configure a witness. In the event of failure of the principal, the database service becomes unavailable. You can perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that didn’t make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual failover with safety OFF involves acknowledging the possibility of data loss.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:


    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    Once the database on Server_A becomes operational, it automatically assumes the role of the mirror. However, the mirroring session remains SUSPENDED, and you will need to manually RESUME the mirroring session.


    Another option in the event of failure of the principal, is to break the mirroring session and then recover the mirror database, as in Safety FULL without a witness (High Protection). Whether you choose to break the mirroring session or force service, you will lose the transactions that haven’t yet made it to the mirror at the time of failure.

    If you use the 3rd one your interpretation is correct. Once the principal db is up it will be acting as 
    new mirror and all you need to do is to resume the mirroring operation manually using the command,

    ALTER DATABASE <db_name> SET PARTNER RESUME

    If you use the 2nd one you need to reconfigure mirroring once the principal server is up.

    - Deepak

    Wednesday, January 23, 2008 12:53 AM
  • Hi Steve,
    Pls refer this link, http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx Refer the topic Transaction Safety Levels

    SQL Server 2005 Standard Edition allows only the FULL transaction safety level.

    Asynchronous mirroring is fully supported in Enterprise edition.

    - Deepak
    Thursday, January 24, 2008 1:43 AM

All replies

  • Steve,
             You have the following options for failover as per the below link, http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

    Safety Full with Witness (High Availability)

    This scenario provides the high safety with automatic failover. In the event of the failure of the principal, the mirror forms a quorum with the witness. Automatic failover will take place, thereby minimizing the database downtime.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror, respectively. Server_A fails. Following the automatic failover, Server_B takes on the role of the principal. However, since there is no mirror after the failover, the mirroring state is DISCONNECTED and the principal is exposed. Once the database on Server_A becomes operational, it automatically assumes the role of the mirror.


    Safety FULL without a witness (High Protection)

    This scenario provides high safety, but automatic failover is not allowed. In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

    ALTER DATABASE <database name> SET PARTNER OFF
    RESTORE DATABASE <database name> WITH RECOVERY

    Once Server_A becomes available, you need to re-establish the mirroring session.

    Safety OFF (High Performance)

    If the safety level is OFF, the witness doesn’t add any value to the database mirroring scenario. Therefore, it is recommended that if you plan to run database mirroring when the safety level is OFF, don’t configure a witness. In the event of failure of the principal, the database service becomes unavailable. You can perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that didn’t make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual failover with safety OFF involves acknowledging the possibility of data loss.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:


    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    Once the database on Server_A becomes operational, it automatically assumes the role of the mirror. However, the mirroring session remains SUSPENDED, and you will need to manually RESUME the mirroring session.


    Another option in the event of failure of the principal, is to break the mirroring session and then recover the mirror database, as in Safety FULL without a witness (High Protection). Whether you choose to break the mirroring session or force service, you will lose the transactions that haven’t yet made it to the mirror at the time of failure.

    If you use the 3rd one your interpretation is correct. Once the principal db is up it will be acting as 
    new mirror and all you need to do is to resume the mirroring operation manually using the command,

    ALTER DATABASE <db_name> SET PARTNER RESUME

    If you use the 2nd one you need to reconfigure mirroring once the principal server is up.

    - Deepak

    Wednesday, January 23, 2008 12:53 AM
  •  Deepak Rangarajan wrote:
    Steve,
             You have the following options for failover as per the below link, http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

    Safety Full with Witness (High Availability)

    Don't want to use this through an internet line - as this mirror is only for "disaster mode".

    Safety FULL without a witness (High Protection)

    This scenario provides high safety, but automatic failover is not allowed. In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

    ALTER DATABASE <database name> SET PARTNER OFF
    RESTORE DATABASE <database name> WITH RECOVERY

    Once Server_A becomes available, you need to re-establish the mirroring session.
    This is what I'm currently using. Running a mornings data flow, I found that this is significantlyly slower.  A delimited field import of 5000 records that normally takes 4-5 seconds took 20-30 seconds, and according to the Database mirroring monitor, created about a 7Meg "Unsent Log".  I've got a script that executes about a dozen queries, and was extremely surprised that it too well over a minute (instead of about 10 seconds), and also create a log of over 60 Meg. (I have to wonder if it was still dealing with log updates of the previous file imports - I just ran that same script, and it ran in several seconds with no log updates.) 
    Another observation is that on my local network, the Database Mirroring Monitor shows green (Synchronized) for both my local (Principal) and remote (Mirror) servers.  Looking at the remote monitor, it shows green for the Mirror (it's local), but red (Not Connected/Waiting for data) for the primary.  Is this normal?


    Safety OFF (High Performance)

    If the safety level is OFF, the witness doesn’t add any value to the database mirroring scenario. Therefore, it is recommended that if you plan to run database mirroring when the safety level is OFF, don’t configure a witness. In the event of failure of the principal, the database service becomes unavailable. You can perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that didn’t make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual failover with safety OFF involves acknowledging the possibility of data loss.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:


    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    Once the database on Server_A becomes operational, it automatically assumes the role of the mirror. However, the mirroring session remains SUSPENDED, and you will need to manually RESUME the mirroring session.


    Another option in the event of failure of the principal, is to break the mirroring session and then recover the mirror database, as in Safety FULL without a witness (High Protection). Whether you choose to break the mirroring session or force service, you will lose the transactions that haven’t yet made it to the mirror at the time of failure.

     

    Since my data updates are fairly limited, I can easily reload a day if it crashed during the morning processing.  So would going to Safety off significantly improve performance? Looking at the link you sent, it looks like it!  Thanks for the info!

    If you use the 3rd one your interpretation is correct. Once the principal db is up it will be acting as 
    new mirror and all you need to do is to resume the mirroring operation manually using the command,

    ALTER DATABASE <db_name> SET PARTNER RESUME

    If you use the 2nd one you need to reconfigure mirroring once the principal server is up.

    - Deepak
    So it looks like I want to change this to "Safety off", and I'll then be able to do a "force" failover.  
    One more interesting observation from this morning - each data import resulted in log-file updates.  Makes sense, though I'm amazed that importing a 500K file with a few thousand records can result in a 5-6Meg log update.  Anyway, the last two imports I did (about 30 minutes after the main set of files) did NOT result in any updates.  Looking at the history on either the local or remote ends don't show any data since the main loads earlier this morning.  Why would some table imports be "mirrored", but these two skipped?  I'll be forcing a failover later to actually verify data, but it sure looks like nothing updated since 7:45.  Strange...  
    Thank you for your comments and assistance!
     
    Steve

     

    Wednesday, January 23, 2008 1:53 PM
  • In Synchronous mirroring, as soon as data is committed in the principal it is sent to the mirror server and it is also committed there.After that the mirror sends an Acknowledgement to principal that data has been committed there.The key concept is that the client i.e principal waits until it receives an Acknowledgement from mirror before it commits the next set of datas.

    If the network is slow, it will definitely cause performance issues because the principal is waiting for receiving an Acknowledgement to proceed with subsequent transactions. May be can opt for High performance mode.


    - Deepak



    Wednesday, January 23, 2008 6:38 PM
  • Is Synchronous mirroring limited to the Enterprise version?  I'm using standard, and when I tried issuing ALTER DATABASE <database> SET PARTNER SAFETY OFF, it said it's not supported in my version?

     

    "This SQL Server edition does not allow changing the safety level. ALTER DATABASE command failed."

     

    Thanks again for the help!

     

    Steve

     

     

    Wednesday, January 23, 2008 6:49 PM
  • One of difference between mirroring in std and enterprise is that database snapshot feature 
    is available only in enterprise edition. I am not sure about the support in standard edition but i'll check and get
    let you know sooner..

    - Deepak
    Wednesday, January 23, 2008 7:09 PM
  • I got the same message taking off "partner" from the command.  Edition just returns "Standard Edition"

     

    In the Mirror task, all the "Operating Mode" options are greyed out.

     

     

    Wednesday, January 23, 2008 9:32 PM
  • Hi Steve,
    Pls refer this link, http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx Refer the topic Transaction Safety Levels

    SQL Server 2005 Standard Edition allows only the FULL transaction safety level.

    Asynchronous mirroring is fully supported in Enterprise edition.

    - Deepak
    Thursday, January 24, 2008 1:43 AM
  • Thanks - found that as well.  Not worth upgrading for this - just frustrating that this would be a crippled feature... 

    Oh well, thanks again for the help and clarification!

     

    Steve

     

     

    Thursday, January 24, 2008 11:45 PM