SQL Server Always On Availability Groups Key Concepts

SQL Server Always. On Availability Groups Key Concepts

High Availability High availability is a strategy where multiple service nodes are synchronized in such a way that any given time one node is active. Other nodes in the cluster are configured in such a way that they can quickly become active if needed. This is different than load balancing, where service requests can be handled by any node in a cluster. SQL Server Always. On Availability Groups is a High Availability solution. This is due to the nature of SQL needing to make sure that database writes are never conflicting.

Failover Cluster Windows Failover Clustering (WFC) is a strategy that allows for (among other things), multiple Windows servers to have a virtual IP address (VIP) that can dynamically be moved between nodes within the cluster. SQL Server Always. On Availability Groups uses Windows Failover Clustering for this purpose.

Always. On Availability Replicas Unlike most uses of WFC, Always. On Availability Groups do not share disks between nodes. Instead, SQL replication is used to copy all transactions from the current primary node to all secondary nodes. The nodes are referred to as Replicas, where the active node is the Primary Replica, and all other nodes in the group are Secondary Replicas. SQL Transaction Replication Primary Replica Secondary Replica

Always. On Availability Databases It’s important to note that individual databases are assigned to the Always. On Availability Groups. This means that the high availability characteristics apply at the DB level, not at the instance or host level. All databases within an Availability Group are active on the primary replication host for that group. If a fail over occurs, a new replication host becomes the primary, and all databases become active on that host.

Always. On Availability Listeners An Always. On Availability Group Listener is assignment of a VIP to the specific availability group. Using this address as the connection string allows applications to continue to connect to the databases in the Availability Group in the event of failover.

Configuration of the Always. On Group Listener Configuration of the Listener is straight forward. Make sure that the IP address is a VIP that exists in the WFC

A Load Balancing Side Effect While the Always. On Availability Group does not provide a true active / active scenario for individual databases, note that you can create multiple availability groups, and that each group can have it’s primary replication host as a different node in the cluster. For example, in the screenshot to the right, the databases that are assigned to the CWSAG 01 group is not active on the same server as the other groups. This helps disperse some of the load between the nodes in the cluster.

Another Load Balancing Side Effect Another side effect of Always. On Availability Groups is that the secondary replication server can be configured to allow the secondary replication hosts to be readable. While the secondary server cannot be used to update/insert/etc. data, it can be used to run reports, etc. , which lessens the load on the primary server.

A note on SQL logins 1. Logins do not propagate across the nodes in the availability group. For a user to login they must be added as a login to each node. 2. The login does not propagate across the node, but the database permissions for the user do propagate. 3. The permissions are determined by the user’s SID. If the login is using Windows authentication for a domain account, then the user will always have the same SID automatically (because it’s the SID of the domain account). 4. If the login is a SQL account, then the SID will not normally be the same!

How to use a SQL account If you need a SQL account to be able to correctly login to any node and have the proper database permissions, the following must be done: 1. Install the sp_help_revlogin stored procedure as indicated in this article. This only needs to be done time per node (not once per user) 2. Create the user on one node in the cluster as normal. 3. On the node where the user exists run the following query: sp_help_revlogin username Where username is the user that was just created. The output of this query is a SQL query that can be run on all of the remaining nodes to create the same username with the same password and SID. 4. Apply user permissions, etc. to the database(s) as normal.