Enterprise TechSQLSQL Server Database Mirroring February 28, 20164123 views0Share By IG ShareSQL Server Database MirroringSQL 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.Note: If you buy something from our links, we might earn a commission. See our disclosure statement.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 failoverAutomatic, transparent client redirectNo shared components; two separate copies of dataSQL Server 2008 compresses the log stream from principal to mirror.Considerations before Implementing Database MirroringFailover 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 SessionsIn 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.aspxI 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. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
Enterprise TechList of the Best HPTX Cases – Huge PC Cases for Large BuildsThe computer cases have been one of the essential aspects when it comes to enjoying ...
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise TechWhat Is A DataCenter Power Distribution Unit (pdu) Server Rack CabinetsNo matter whether you call it a server closet or server room or a cabinet, ...
Storage SystemsWD SE vs WD Red Pro Specifications Comparison – Enterprise Class NASThe HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...