Q

Database mirroring best practices for VARs

Database mirroring with Microsoft SQL Server can allow VARs to build a fail safe into customers infrastructure in order to keep data highly available at all times.

Will database mirroring affect how VARs approach managing and servicing customer's databases?
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

Dig deeper on Database Management Products and Solutions

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

MicroscopeUK

SearchCloudProvider

SearchSecurity

SearchStorage

SearchNetworking

SearchCloudComputing

SearchConsumerization

SearchDataManagement

SearchBusinessAnalytics

Close