For an instructor lead, in-depth look at learning SQL click below.
In the realm of database management, SQL Server Distributed Availability Groups play a pivotal role that many newbies often underrated or completely overlooked. First introduced in SQL Server 2016, Distributed Availability Groups is a wonderful feature that offers disaster recovery and geographical scalability solutions for SQL Server databases. This post will provide an introduction to this important adjunct of SQL Server and provide you with basic SQL code examples for better understanding.
What are SQL Server Distributed Availability Groups?
Distributed Availability Groups is an enterprise-only feature that connects multiple availability groups spread across different locations. It provides a viable alternative to log shipping, mirroring, and basic availability groups in SQL server databases to handle large-scale, geographically distributed, and cross-network deployments.
How to Create Distributed Availability Groups
Now let’s dive into how to create a Distributed Availability Group using SQL code:
|
1 2 3 4 5 6 7 |
CREATE AVAILABILITY GROUP ag1 WITH (DB_FAILOVER = ON, DTC_SUPPORT = PER_DB) FOR DATABASE db1; ALTER AVAILABILITY GROUP ag1 JOIN; ALTER AVAILABILITY GROUP ag1 GRANT CREATE ANY DATABASE; |
The above SQL script will create a new availability group named ‘ag1’. Then with ALTER statements, the script joins the ‘ag1’ availability group and grants it permission to create any database.
Creating Secondary Replicas
To add redundancy and improve the system’s performance, it can be beneficial to create secondary replicas of the distributed availability groups. Here’s how to create secondary replicas using SQL:
|
1 2 3 4 5 6 7 |
ALTER AVAILABILITY GROUP ag1 ADD REPLICA ON 'SQLNode2' WITH (ENDPOINT_URL ='TCP://SQLNode2:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC); |
In the above SQL script, we added a secondary replica ‘SQLNode2’ to the ‘ag1’ availability group.
Conclusion
In summary, SQL Server Distributed Availability Groups provide a high performance and high redundancy solution for large scale SQL Server databases. In this post, we’ve only touched the surface of this feature. There’s more to SQL Server Distributed Availability Groups such as managing read-only replicas, performing manual failovers, and so on. Keep exploring!