For an instructor lead, in-depth look at learning SQL click below.
The SQL Server Always On Availability Groups feature is a high-availability and disaster-recovery solution that provides a rich set of options to increase database availability. Today, we will discuss how to work with this feature using SQL code.
Setting Up Always On Availability Groups
The first thing you need to establish is the Availability Group itself. The following script does exactly this. Make sure to replace the placeholder values with the actual names you want to use for your databases and Availability Groups.
|
1 2 3 4 5 6 7 8 |
CREATE AVAILABILITY GROUP [YourAvailabilityGroupName] WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY) FOR DATABASE [YourDatabaseName] REPLICA ON N'SQL_Server_Instance_Name' WITH (ENDPOINT_URL = N'TCP://SQL_Server_Instance_Name:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)); |
Adding a Replica to the Availability Group
Once the Availability Group is set up, you can then add a replica to it. A replica is a copy of your database that the Availability Group will use in case the primary database goes down. Here’s how you can add a replica:
|
1 2 3 4 5 6 |
ALTER AVAILABILITY GROUP [YourAvailabilityGroupName] ADD REPLICA ON N'SQL_Server_Instance_Name' WITH (ENDPOINT_URL = N'TCP://SQL_Server_Instance_Name:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)); |
Checking the Status of Availability Groups
Your Always On Availability Groups and replicas are now set up. To check their health or status, you can use the following command:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT ag.name AS AGName, ar.replica_server_name, ar.availability_mode_desc, ar.failover_mode_desc, ars.primary_replica, ars.synchronization_health_desc FROM sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id WHERE ag.name = N'YourAvailabilityGroupName' ORDER BY ag.name, ar.replica_server_name; |
In conclusion, SQL Server Always On Availability Groups is a powerful feature that provides database high-availability and disaster recovery. When implemented correctly, it can ensure that your data is always available even in the event of a database failure.
References
For more detailed information about SQL Server Always On Availability Groups, you can refer to the official Microsoft documentation here.