Freigeben über

SQL Server 2008 Mirroring in Standard Edition

  • Artikel

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.

Technorati Tags: TechEd EMEA,SQL Server,daatabase mirroring

Comments

  • Anonymous
    January 06, 2009
    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

  • Anonymous
    February 19, 2009
    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)

  • Anonymous
    April 12, 2009
    @Robert Thats why its called mirroring

  • Anonymous
    April 07, 2011
    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

  • Anonymous
    April 15, 2011
    You apply mirroring at the database level by righ clicking on it and following the wizard ..http://bit.ly/e7Gww7 Andrew

  • Anonymous
    July 20, 2011
    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...

  • Anonymous
    July 22, 2011
    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

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

  • Anonymous
    November 30, 2011
    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

  • Anonymous
    July 23, 2012
    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