Enterprise TechSQL SQL Server Database Mirroring 4559 views0 Share By Share 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. 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 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. 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 Tech List of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By
Enterprise Tech List of the Best HPTX Cases – Huge PC Cases for Large Builds The computer cases have been one of the essential aspects when it comes to enjoying ...
Azure Best Industrial IoT Routers & Gateways For AWS and Azure IoT Services In this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise Tech What Is A DataCenter Power Distribution Unit (pdu) Server Rack Cabinets No matter whether you call it a server closet or server room or a cabinet, ...
Storage Systems WD SE vs WD Red Pro Specifications Comparison – Enterprise Class NAS The HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...