SQL Server 2008 Mirroring in Standard Edition

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

… 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