In "SQL Server High Availability for Developers, Part 1," we looked at core high-availability concepts and clustering as a solution for achieving high availability. In "SQL Server High Availability for Developers, Part 2," we discussed log shipping as another solution for achieving high availability. In this article, we'll take a high-level look at database mirroring and its SQL Server 2012 successor, AlwaysOn Availability Groups.
Database Mirroring
Although database mirroring is being replaced in SQL Server 2012 with AlwaysOn Availability Groups, it's still a great high-availability solution for organizations running SQL Server 2008 and SQL Server 2005. Logically, database mirroring provides benefits that are fairly similar to those offered by clustering—only the scope of database mirroring is limited to user databases (meaning that this solution can't mirror system databases or address server-level redundancy). Functionally, database mirroring is a more akin to log shipping. This is because database mirroring is actively tied to the transaction log, which it uses to keep a principal server synchronized with a maximum of one mirror server. Unlike log shipping in which log file backups are copied or shipped from one server to another, database mirroring actually pushes synchronization details back and forth over specialized listeners or network sockets that are set up on the principal and mirror servers as the means of communication.
With database mirroring topologies, it's possible to set up a third server that can act as a witness to transparently manage failover operations in the event the principal server goes down. Whenever failover occurs, the amount of data that's lost is determined by how the mirroring topology was set to operate. If the mirroring topology was set to operate in high-safety mode, then transactions must be committed on both the principal and the mirror server before transactions can complete (meaning that data can't be lost). If the mirroring topology was set to operate in high-performance mode, then there's a bit of lag in synchronization rates because transactions are allowed to complete asynchronously (meaning that some data can be lost).
Database mirroring provides a variety of benefits, including the following:
- Failover and role switching. With mirrored databases, developers provide a specialized connection string that points to both the principal and the mirror server. This lets applications intelligently fail over when a failover at the database level has occurred. Likewise, database mirroring lets users perform a role switch. With role switching, failover and fail-back operations can be manually initiated by DBAs or system admins to facilitate rolling updates or service packs.
- Development transparency. With the exception of the need to specify more intelligent connection strings that can manage failover options as needed, database mirroring is very transparent to developers—meaning that database mirroring doesn't require any coding or logic changes at the application level.
- Environmental benefits. Although log shipping can be easily set up to address environmental concerns, such as server configuration details and login information, database mirroring tends to lend itself to efficiently ensure that necessary environmental changes are deployed against both members of a mirroring topology. This is because mirrored databases are logically easier to think of as working in pairs. (Whereas with log shipping, it's easy for system admins and DBAs to make changes at primary servers. It's also easy for system admins and DBAs to forget about making similar changes at secondary servers because they exist as a logical afterthought.)
- Setup and cost. Database mirroring is fairly easy to set up in most LAN scenarios and isn't very expensive to manage. However, database mirroring is a feature of SQL Server Standard Edition, and Standard Edition is limited to only high-safety mode.
Although database mirroring offers several benefits, it does have some drawbacks. For example, high-performance mode is included only in the SQL Server Enterprise Edition. In addition, drawbacks for database mirroring include the following:
- Configuration and performance. Although configuring database mirroring for LANs can be fairly easy, it's harder to manage over long distances. Furthermore, because high-safety mode requires transactions to be successfully completed on two distinct servers, it's possible to run into situations in which performance problems can cause some ugly headaches. This is especially true when you're dealing with larger workloads on servers that haven't been properly sized or configured.
- Redundancy. Database mirroring can target only a single secondary or witness, unlike log shipping, which lets users target multiple secondaries.
AlwaysOn Availability Groups
Debuting with SQL Server 2012, AlwaysOn Availability Groups is the successor to database mirroring and adds a ton of great additions and improvements that will make this technology a definite game-changer in the SQL Server high-availability world. However, the only problem with AlwaysOn is that Microsoft applied that moniker to two totally separate and distinct technologies: AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances (FCIs). With FCIs, system admins and DBAs have the ability to utilize most of the benefits that failover clustering provides at the SQL Server instance level (as opposed to being at the entire server level). As a result, AlwaysOn FCIs provides huge benefits in terms of availability and flexibility, but requires much less configuration and setup overhead that's needed for today's traditional clusters. AlwaysOn FCIs also provide much better support for stretch clustering capabilities and remove many stringent hardware requirements that are needed for normal failover clustering at the server level.
In terms of AlwaysOn Failover Groups, this technology adds some major improvements and augmentations to what's currently available via database mirroring. To that end, be sure to check out the existing documentation if you're interested in learning more about the mechanics of how this technology works. Otherwise, the following list of benefits highlights some of three big bonuses and improvements that AlwaysOn Failover Groups provide:
- Builds upon mirroring. With AlwaysOn Failover Groups, developers enjoy the same basic benefits that database mirroring provides—meaning that synchronization is transparent to developers and that there are similar failover benefits such as intelligent connection strings.
- Increased redundancy. Unlike database mirroring, AlwaysOn Failover Groups allows multiple replicas (or secondaries). This includes the option to specify replicas that are read-only or read-write. Similar to database mirroring, administrators can specify whether replicas are maintained through synchronous or asynchronous methods.
- Versatility. AlwaysOn Failover Groups provides some dramatic versatility options that let DBAs run backups, perform checks, and run diagnostics against replicas instead of their primaries. This lets users off-load expensive operations to achieve greater overall performance and availability.
- Dependencies. Unlike database mirroring, which is managed at a database-by-database level, AlwaysOn Failover Groups provides the ability to specify availability database groups need to be grouped—and therefore failed over together—making AlwaysOn Failover Groups a much better solution for applications with dependencies on multiple databases.
Although AlwaysOn Failover Groups provides several benefits, it does have some drawbacks that DBAs might want to consider, including the following:
- Setup. AlwaysOn Availability Failover Groups is a little more difficult to setup and configure compared to database mirroring. This is because AlwaysOn Availability Failover Groups require minimal failover clustering components to be installed as part of the underlying failover-management platform.
- Performance. As with database mirroring, improperly configured or improperly sized AlwaysOn Failover Groups are likely to encounter similar performance problems.
Up Next
In our final installment, we'll take a look at replication, which differs from most of the high-availability options that we've looked at so far. Some forms of replication actually allow for multiple and simultaneous primaries or master servers. In addition, many forms or types of replication actually incur some changes and modifications that can affect developers and applications in certain cases.
