locked
Questions for Mirroring PM RRS feed

  • Question

  • Following a bunch of tests I've concluded that mirroring works very well, even in sync mode the perf impact is low. Log shipping integration is clean following failover. Excellent stuff, thanks. However there are a few things that would make it a much better sell against competetive solutions:

    1. It's my experience that Database Mirroring in HA mode effectively only supports one failure, so if your Witness goes bang you lose auto failover capability. If the mirror then fails you lose database service. This makes robustness requirements for the Witness very important. Are there any plans to allows multiple witnesses with some form of active/passive failover between them? I seems a little over the top to have to consider clustering a witness.

    2. It's extremely irritating that we can't create logins on the mirror if the login has the mirror database as their default database, just because the database is in recovery; I mean it not as if the db doesn't exist. Can this be addressed with the security team, perhaps masking the error somehow if the database can't be opened ? Bear in mind the database principal will already exist in the mirror database as it will have been created on the principal server, all we need is the output from sp_help_revlogin from the principal server to match up the sid and password.

    3. If number 2 is addressed, it would be possible to build a more elegant and robust sync mechanism using service broker, without having to rely on alerts to then create any missing logins on failover. Are MS looking at building something to address login synchronisation of the failover servers, perhaps using service broker? Seems for a supposed HA solution this is a very untidy missing piece and with some environments which might not be able to rely on using Windows groups (sql logins), it makes rapid failover difficult.

    4. It would also be useful, while on the subject of number 3, to provide some mechanism for, at least, auto-checking configuration/msdb differences between servers and reporting these, if not provision for keeping them in sync. I appreciate that not all systems will want exact matches between the partner servers, but at least providing mechanisms to sync them up would help.

    5. Any plans for supporting multiple mirrors? Even async? Would make for reporting provision using snapshots much cleaner as the main mirror could be left alone for HA.

    thx,

    Simon.

    Friday, November 3, 2006 10:35 AM

Answers

  •  

    Simon -

    1. We have no plans to create specific Witness mirroring functionality. The overall DBM model is one that is designed to tolerate any single failure. It would require a much more in depth design effort to move to a model that tolerates any two failures.

    2. Yes, it is true that logins can not be created with a default database that is offline. There is an open improvement request for this issue.

    3. There is an open improvement request to provide the ability to associate instance items outside the mirrored database with the mirroring session. This would include logins, as they are stored in master.

    4. I agree that this would be nice functionality for problem diagnosis. It is likely something that would be provided from tools and not by mirroring itself.

    5. Multiple logical disaster recovery sites are currently supported via log shipping. Feedback to date has not indicated a strong customer demand for multiple synchronously updated disaster recovery sites.

     

    Regards,

    Matt Hollingsworth

    Sr. Program Manager

    SQL Server High Availability

    Friday, November 10, 2006 10:54 PM

All replies

  •  

    Simon -

    1. We have no plans to create specific Witness mirroring functionality. The overall DBM model is one that is designed to tolerate any single failure. It would require a much more in depth design effort to move to a model that tolerates any two failures.

    2. Yes, it is true that logins can not be created with a default database that is offline. There is an open improvement request for this issue.

    3. There is an open improvement request to provide the ability to associate instance items outside the mirrored database with the mirroring session. This would include logins, as they are stored in master.

    4. I agree that this would be nice functionality for problem diagnosis. It is likely something that would be provided from tools and not by mirroring itself.

    5. Multiple logical disaster recovery sites are currently supported via log shipping. Feedback to date has not indicated a strong customer demand for multiple synchronously updated disaster recovery sites.

     

    Regards,

    Matt Hollingsworth

    Sr. Program Manager

    SQL Server High Availability

    Friday, November 10, 2006 10:54 PM
  • Nice to know there are improvement requests in the work already

    I would love to see Mirroring function getting better and more powerful, and syncing non-db objects is a good start (logins, jobs, maint. plans, etc...)

     

    One bad thing about log shipping for reporting is that the log shipped databases are "read only", so if the report/extraction needs some writeback to update the status, we're screwed. Still trying to figure out how we can offput extraction to a secondary server instead of production server

    Setup a job to automatically restore a Full backup at set interval? (this way the restored DBs are at least writeable)

    Monday, November 13, 2006 3:37 PM
  • Thanks Matt. The main driver for multiple mirrors, even if async, is for reporting purposes, where we might build snapshots of the db but don't want that overhead on the synchronous disaster recovery partner. Additionally for marketing purposes this is a feature of competitive products.
    Thursday, December 14, 2006 4:15 PM