Free SQL Help for Devs, DBAs, and the Curious

Always On Availability Groups, a feature introduced in SQL Server 2012, gives you a high-availability and disaster-recovery solution. With this, you get a failover environment for a discrete set of user databases, known as availability databases. This solution enhances the existing capabilities of database mirroring and provides a complete alternative to traditional SQL Server Failover Clustering.

The Always On Availability Groups feature provides a robust set of options for managing failover for a group of databases as a single entity rather than as separate databases. This ability to manage failover at the group level is a significant enhancement over the capabilities of database mirroring, which manages failover at the individual database level.

Let's look at some real-world design considerations for Always On Availability Groups. The first step in designing a solution using Always On Availability Groups is to identify the databases that need to be included in the availability group. These should be databases that need to be highly available and that have similar recovery point objective (RPO) and recovery time objective (RTO) requirements.

Next, you need to decide on the number of replicas that you will have in your availability group. You can have up to four secondary replicas, and these can be used for read-only access and backup operations. This is a great way to offload some of the read and backup operations from your primary replica, improving its performance.

The Always On Availability Groups feature is easy to set up and manage. Here's an example of how to create an availability group using T-SQL:

CREATE AVAILABILITY GROUP [AG1]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR
DATABASE [DB1],
DATABASE [DB2]
REPLICA ON
N'INSTANCE1' WITH (ENDPOINT_URL = N'TCP://INSTANCE1:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50),
N'INSTANCE2' WITH (ENDPOINT_URL = N'TCP://INSTANCE2:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50);
GO

In conclusion, Always On Availability Groups in SQL Server 2012 provides a powerful, flexible, and manageable solution for high availability and disaster recovery. By carefully designing your availability groups and correctly configuring your replicas, you can ensure that your critical databases are always available and that you can quickly recover from any disaster.