Ask the Expert

Database mirroring best practices for VARs

Will database mirroring affect how VARs approach managing and servicing customer's databases?

    Requires Free Membership to View

Database mirroring is a component of SQL Server 2005 Standard Edition and above which does real time log shipping. It can be set up in two modes High Safety and High Performance. High Safety mode is essentially a split write operation where a write is performed on the principal (the source database) and then on the mirror (the destination database) before the application receives an acknowledgement that the write operation has completed.

In High Safety mode there can be considerable latency on each write operation which may degrade the performance of an application using a database which is part of a database mirroring topology. Microsoft recommends using short running transactions, using many connections as opposed to a single connection and ensuring that you have a high speed network.

High Performance mode offers high performance but may result in some data loss during failover. If you are using database mirroring keep in mind that all your databases must be using the full recovery model, you cannot mirror to the same server (but you can mirror between instances on the same mirror), and your source and destination databases must have the same name.

Mirroring does consume some resources on the principal server and is not scalable to a large number of databases on a single server. Mirroring does support automatic failover and the failover times are generally quick -- typically less than 10 seconds. To perform automatic failover you must configure a failover partner in your application's connection string. You can optionally use a witness server which will arbitrate between the servers which form the database mirroring pair and determine who will be the principal and who will be the mirror.

When VARs deploy and manage database mirroring they must:

  • Select the most appropriate mode for their mirroring operation
  • Select the appropriate security – you can mirror over the internet and use certificate based security to secure the connection
  • Ensure the network has available bandwidth and speed for the mirroring operation
  • Monitor your mirroring topology using the mirroring DMVs or the database mirroring monitor.
  • Monitor the network to ensure that it has available bandwidth and is not experiencing congestion.
  • set appropriate thresholds and alerts to alert your DBA team when the mirroring operation performance becomes critical. Use the stored procedure sp_dbmmonitorchangealert for this.

This was first published in April 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: