SQL SERVER – Quick Note of Database Mirroring

Just a day ago, I was invited at Round Table meeting at prestigious organization. They were planning to implement High Availability solution using Database Mirroring. During the meeting, I have made few notes of what was being discussed there. I just thought it would be interested for all of you know about it.

  • Database Mirroring works on physical log records.
  • SQL Server 2008 compresses the Transaction Log at Principal Server before it is transferred to mirror server.
  • System databases can not be mirrored.
  • Database which needs to be mirrored requires it to be in FULL recovery mode.
  • High Safety Mode – Synchronous operation – Log are committed on Principal and Mirror when databases are synchronized.
  • High Performance Mode – Asynchronous operation – Principal commits and Mirror tries to keep up with received log records from Principal.
  • Database mirroring can be configured along with log shipping, database snapshots and replication.
  • Unlike replication (subscriber server) in mirroring the mirrored server can not be used during mirroring operation going on.
  • Automatic Failover is only possible when high safety mode is configured with automatic failover.

Mirroring is very big subject and as I mentioned this is just a quick note of what we had discussed there. Let me know if you like me my blog which are quick note kind of.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Mirroring
Previous Post
SQL SERVER – MAXDOP Settings to Limit Query to Run on Specific CPU
Next Post
SQL SERVER – Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE

Related Posts

9 Comments. Leave new

  • ISHTIAQ AHMED
    March 16, 2010 9:36 am

    Thanks for the Quick Note

    I would suggest you to write up large articles as do on another posts. because the way you express the concept it-i am very easily able to understand it.

    so from my opinion is to write as much as you can on the posts , which make more elaborate.

    Thanks pinal Dave you are the one of the Best MVP i have seen.

    Reply
  • Thanks for the info!
    Quick Note – yep not bad, useful!

    Reply
  • Good quick overview, database mirroring has been catching on in organizations here, this is preferred over using the Log shipping method.

    Reply
  • Pinal is a becoming a big daddy in SQL Server.Kudos for creating such an interesting information portal:-)

    Reply
  • Hi,

    I’ve been trying to configure database mirroring on 2005 for a while. I continue to receive the error message that says the mirror can not be reached.

    I have all 3 instances on the same box while i’m studying for my MCITP certification.

    I’ve done everything by the book, still no mirror session.

    Help me if you can.

    Reply
  • Hello Dave,

    Your article has been very helpfull. Under which system table can i find mirroring details like does it have a witness server and things like that. I know i could use db monitor but is there a dmv or system table where i can find this info?
    Thanks

    Reply
  • thanks for the info…………….

    Reply
  • johan sebastian
    October 9, 2014 7:39 am

    Dear Pinal, do you have any ideas how to prepare the sql server for mirroring ? I believe the list will contains but not limited to tempdb collation, login, certificate, scrirpt to transfer user login from primary to secondary. Thank you

    Reply
  • Hello,

    I kindly request your advice about using the Sql Database mirroring for the below issue.

    My company has 3 branches inside the city namely A, B and C. The main server is setup in Branch A. And all the users from Branches A, B and C access this main server. Branch B and C are connected to main server via VPN. This means that if VPN fails, then Branch B and Branch C users cannot access the main server.

    Now my question is,

    1. can i use this mirroring concept by setting up new servers in Branch B and Branch C?
    2. If yes, then lets say that all users from A, B and C are accessing the main server from A. during VPN failure, will the users from B and C would be automatically connected to their local servers at B and C? And after the connection establishes, will the data be replicated to the main server at A?
    3. If no, then what best option can i use to allow the users of A, B and C can work without interruption even if the VPN fails.

    i kindly request your advise or solution regarding the above query.

    Anoop

    Reply

Leave a Reply

Menu
Exit mobile version