SQL Server Database Mirroring

SQL Server Database Mirroring

SQL Server Database Mirroring is still the most popular way to replicate SQL Server across sites over a LAN or a WAN connection in near-realtime. introduced with SQL Server 2005 with SP1 onward, it has been a popular way to achieve High Availability and Disaster Recovery needs, especially for Standard Editions. Even thought Database mirroring is deprecated in SQL Server 2016, (with the introduction of Availability Groups for Standard Edition) it is still a popular technology all the way till SQL Server 2014 deployments.

Here are some of the benefits and features:

• Provides a fault-tolerant database and hot Standby options to protect against database or server failures.
• This considered being the best alternative for a real-time replication solution. It is cost effective and has no requirements for specialized hardware.
• Completely Wizard based with straightforward setup and administration.
• Automatic or manual failover. (Some options are only restricted to Enterprise Edition of SQL)
• SQL Server 2008 does not require a database restart after manual failover
• Automatic, transparent client redirect
• No shared components; two separate copies of data
• SQL Server 2008 compresses the log stream from principal to mirror.

Considerations before Implementing Database Mirroring

• Failover occurs at the Database level and not at the Instance or Availability Group level.
• If you want an Instance level failover, go for SQL Server Clustering. (Available in both Standard and Enterprise Editions)
• If you want Databases to failover in groups, consider implementing Availability groups.
• For Automatic Failover and Transparent Client Redirect to work, you need to have at least NET 2.0 based application and above. The Application connection string needs to be modified to point to the secondary server.
• The secondary server, where the mirrored database is available, cannot be accessed by applications or direct queries.
• The mirrored database can only be queried by taking a “Database Snapshot” which is an Enterprise Only feature.
• If you want “Read Only” secondaries, consider using Log shipping or Availability Groups (Enterprise feature Only till 2014)
• Some Licensing rules need to be followed, please see my articles on Licensing SQL Server for High availability environment.
• SQL Server Standard Editions (2005, 2008, 2012, 2014) does not support Asynchronous replication. This limits replication to far away sites due to performance and network latency issues.

Transparent Client Re-Direct for Database Mirroring Sessions

In addition to the Primary Server name, the Application Connection string also needs to specify failover partner name, which should identify the current mirror server instance. The failover partner is specified by one of the keywords for the failover partner attribute. The keyword for this attribute depends on the driver that you are using. For example, in the case of an ODBC connection, the following should be the syntax:

"Server=Partner_A; Failover_Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn"

Source: https://msdn.microsoft.com/en-us/library/ms175484.aspx

I hope this post was helpful. It is recommended to list out your options carefully before deciding if Database Mirroring is the right High Availability solution for your environment. In the next post, I will explain what is Log shipping and in what scenarios should you implement it.

If you have any comments or suggestions, please drop us a line below in the comment section.