Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 Mirroring in Standard Edition

SQL Server 2008 Mirroring in Standard Edition

  • Comments 10
  • Likes

I opened the data management track at TechEd EMEA yesterday, to a full room and as ever if I am unsure of a question I research it and put it on this post the next day.

I was pretty sure of the differences in the way database mirroring in standard and enterprise edition of SQL Server 2008 which are:

Standard Edition you can only use mirroring in full safety mode i.e. synchronous mirroring optionally with a witness to enable automatic failover. btw the witness can be express, but the mirror must be standard as well. 

Enterprise Edition.

  • Mirroring can be run in high performance (asynchronous mode) which can be useful in a geo-mirror configuration over a slow network, where synchronous mirroring would create unacceptable delays for the user.
  • Torn page detection allows the principal to discover and automatically copy a damaged page from the mirror to the principal.
  • log compression dramatically improves the performance of mirroring in several ways, the send queue is reduced the transaction per/sec transmitted to the mirror are up by 2.5x on a 10mbs network (lees on faster ones). There is some CPU overhead for this

There are no restrictions on how many mirrored databases can be setup on the mirror in standard or enterprise (which could come from different principal servers), however there are some things to remember:

  • An instance can only have one endpoint which could be a bottleneck if there are lots of databases in the one instance.
  • It is not recommended to have more than ten databases mirrored to one server, I have not seen that change for SQL Server 2008 but the safe limit should be higher for Enterprise edition given the compression feature.

If you are TechEd I have now got the shuttle working like this

image

… so that the database in the cluster is mirrored to another server which in turn is log shipped to another instance, so come and say hello on the SQL Server Ask the Experts stand.

Comments
  • Are you sure that torn page detection and log compression are only in Enterprise Edition?  Microsoft's website, http://msdn.microsoft.com/en-us/library/cc645993.aspx, says that "automatic corruption recovery from the mirror" is in Standard Edition, and I think this is the torn page feature you mention.  Also, Sanjay Mishra on the Microsoft CAT Team said that log compression is in Standard Edition.  http://sqlcat.com/technicalnotes/archive/2007/09/17/database-mirroring-log-compression-in-sql-server-2008-improves-throughput.aspx

  • question: when I insert a row in my server, in my mirror server, the same row has the same primary key (I´m using a int identity key)

  • @Robert

    Thats why its called mirroring

  • hello frenz i have 2 servers can i apply the concept of mirroring if so how??? what are the requirement is der any changes during installation... kindly help thank you in advance

    mail:bhavesh.ved@gmail.cm

  • You apply mirroring at the database level by righ clicking on it and following the wizard ..http://bit.ly/e7Gww7

    Andrew

  • when the primary fails and the mirror takes over - how do my web applications know where that server is? theyre pointing to an IP or to a "computer name" both are different on the mirrored server...

  • Sam

    when you first connect you use a SQL native client connection which allows you to define the principle and the mirror  as you won't know which is which at initial connection.  

    the details are at msdn.microsoft.com/.../ms130822.aspx

    and look for the bit on failover connection

    If a failover happens during a session then SQL takes care of the redirection for you.

    Andrew

  • on standard edition without witness ,how restore the mirrored database on the mirrored server, restore the last pool of transaction log ?

  • Eric

    I am not sure I understand the question: If only the witness dies then the principal will still be the prinicipal.  If the Principla and the witness aren't there it will depend on whihc failed first.  Mirroring is like democracy the bit of the infrastructure with the most votes wins

    full details on how to setup and test are here..

    technet.microsoft.com/.../cc917680.aspx

    Andrew

  • Hi All,

    I've made some SQL scripts to help mirror the databases. These might be usefull.

    kevinrr3.blogspot.nl/.../sql-mirroring-scripts-for-sql-server.html

    Kevin

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment