SQL Server Database Mirroring Technique – Advantages and Disadvantages

In this post, we have talked about database mirroring, which is basically a high availability and disaster recovery technique. The post delves deeper into its advantages/disadvantages and how to create an environment for this technique.

SQL Server Database MirroringWhen you talk about protecting SQL server from sudden unpleasant surprises such as natural disasters or human errors, backups offers a great source of relief. If you are a database administrator, you must have wished at some point that there existed an identical twin of your server. A twin server that could hold the same data as the original one! So, you could immediately start using it whenever needed.

Well, SQL Server mirroring technique offers exactly that. It’s one of the many techniques that are part of high availability and disaster recovery program intended to recover SQL database. You too can choose it as part of disaster recovery for your database.

What Exactly is Database Mirroring?

We have already explained that this technique is part of high availability and disaster recovery plan. The technique involves using two server instances on different machines. The two instances can be present on same machine as well. One instance would be considered primary or principal. The other would mimic the data of the first one and hence called a mirror. In some cases, administrator can have a third server instance as well.

Advantages/Disadvantages of Database Mirroring

High Availability Options In SQL Server 2005There are several benefits to using a mirror server to a primary one. One of the advantages is that it offers automatic failover when in high safety mode. The mirroring technique for disaster recovery can also be combined with other options available for recovery plan like replication, log shipping, and clustering.

Do note that this technique will not be available in future versions of SQL server. The technique will be replaced by AlwaysOn Availability.  Also, mirroring is a per database solution. It means that the jobs and Logins from principal server must be recreated manually in the mirror server. The possibility for delays is also there. The only way to reduce that is to have a better hardware.

Creating the Environment for Database Mirroring

The feature to mirror a server is present in SQL Server 2005 and later versions. Whether or not such an option is available will depend on SQL Server edition. It is possible to combine different versions of the SQL server as well. However, it is not recommended.

The database of which a mirror is created should be in the full recovery mode. Also, it is not possible to mirror system database.  Transaction log backups and the complete database of the server that has to be replicated must be restored in the Server, which acts like a mirror. The process of restoring is performed using the option of WITH NORECOVERY.

The setup for creating a database mirror is initiated by the principle server which uses the T-SQL code or SQL Server Management Studio Wizard. If you want high safety with automatic failover mode, you can choose a witness SQL Server instance while beginning the setting up procedure. In order for server instances to communicate with each other, you will have to create end-points with its name and port specified. This setting will have to be performed on both mirror server instance as well as primary/principal server instance.

Author Introduction:

Peter Song is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including outlook recovery and PDF recovery software products. For more information, visit www.datanumen.com

Comments are closed.