SQL Server Always Availability Group

Ensure all perquisites are completed prior to commencing, as this blog is only focused on configuration steps.

Step 1;

On both SQL Server Nodes open SQL Server Configuration Manager, double-click the SQL Server Service (MSSQLSERVERR) to open the properties. In the properties, box select the AlwaysOn High Availability tab and check the enable AlwaysOn Availability Group. Note: The SQL Server service will require a restart for the change to take effect.

Within the Failover Cluster name add the cluster name: AAG-SQL

Step 2: Now we can proceed with SQL Server AlwaysOn Availability groups setup:

2.1 Open SQL Server management studio and connect to the SQL Server instance, then expand the AlwaysOn High Availability from the object explorer.

2.2 Right-click the AlwaysOn High Availability Group folder and click the New Availability Group Wizard.

Step 3:

Click next on the introduction page.

3.1: In the Specify Availability Group Name page, enter the name of the Availability Group in the Availability group name: field. For the Cluster type choice: Windows Server Failover Cluster.

Cluster Type: New with SQL Server 2017

  • WSFC = A Windows Server Failover Cluster will manage the availability group. See reference: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/high-availability-solutions-sql-server?view=sql-server-2017
  • EXTERNAL = An external entity will manage the availability group. This would mean you will be joining the availability group to an external cluster. In instance, if you have a windows cluster on another data center that is on a different network or you ‘d like to add it to a Linux cluster.
  • NONE = No cluster entity will manage the availability group.
  • The database level health detection, this would enable constant validation for the databases that are part of the always-on availability group. Therefore, if anything goes wrong with any of the databases it would trigger an automatic failover.
  • The per database DCT support feature in availability group was made available since SQL Server 2016 SP1, which allows distributed transaction coordinator through the availability group.

Step 4:

Select the databases that will be part of the availability group. Please note the required databases should be in full recovery model and should have a full backup.

Step 5:

  1. Replicas: Add all server instances that will be participating as part of the Always on availability group.
  2. Failover Mode: Manual or Automatic.
  3. Availability Mode: Synchronous commit or Asynchronous commit: See Reference: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-2017
  4. Readable Secondary:
  • No – This option would mean no user connections are allowed to the secondary databases of the replica.
  • Read-intent only – Read-only connections are allowed to the secondary databases and the databases would be available for read access. This will guarantee that reporting workload is always routing to the secondary replicas. Note: only if the application connection string contains the parameter: Application Intent=Read-only.
  • Yes – All connections would be allowed to the secondary replica. This setting allows read-only workloads regardless of whether the application connection string contains the parameter: Application Intent=Read-only.

  1. The endpoints tab will show you the URL and ports each replica is set to.

  1. Backup preferences tab: You can configure if you want to take the backups from the primary or the secondary servers.

  1. Listener tab: In this window you can configure the availability group listener that will provide client connection point. The listener would be your single point of entry for the availability group, therefore, an application should be configured to use the listener domain name or IP address which will give the high availability and Always ON. Note: The lister can always be setup later.
    • Configure a port that you would like the listener to listen to and needs to be different than the endpoints (5022) and default SQL Server port 1433.
    • Configure the IP Address make sure you choose an IP address from the same subnet that is being used.

  1. Read-Only Routing: tab This lets you configure your read-only routing for the read-intent setup, this allows you to load balance the queries, so you have only the queries that will Insert, Delete or Update on the primary and all the Select queries routed to your secondary’s so they don’t consume resources the Primary will use.

Step 6:

Step 7: Check that validation completes successfully, reporting no errors. Note: if you have chosen to add listener later then warning will be shown for listener configuration during the validation. But not to worry as you will be configuring the listener at the next stage.

Step 8: View your summary to confirm all configuration looks appropriate and click finish.

Step 9: Your New availability group configuration should complete successfully:

Step 10: Let’s verify, you will find in your domain server in DNS the new listener address:

View the Always On High Availability dashboard, you should see all databases synchronized eventually:

Under the Always On High Availability object explorer you will find your replicas, databases that are part of the availability group and the availability group listener:

Step 11:

Now we are finished with Always On High Availability configuration let’s proceed with testing the read-intent only to ensure it’s working as expected.

Connect to Listener from SSMS Click Option then with database chosen and additional connection parameters as ApplicationIntent=ReadOnly.

Using listener name to connect:

Options:

Chosen master database and added ApplicationIntent=ReadOnly in additional connection parameters.

Run a query against a table and check server name to confirm it’s reading from the secondary node and not from the Primary:

Alternatively, check Availability group object explorer you should see the connection to the secondary node:

Example query:

SELECT SERVERPROPERTY(‘ServerName’)

Helpful TSQL query to check read only routing configuration:

select ar.replica_server_name

, ar.endpoint_url

, ar.replica_server_name

, ar.availability_mode_desc

, ar.failover_mode_desc

, ar.session_timeout

, ar.primary_role_allow_connections_desc

, ar.secondary_role_allow_connections_desc

, ar.backup_priority

, ar2.read_only_routing_url

from sys.availability_replicas ar

inner join sys.availability_read_only_routing_lists avr

on ar.replica_id = avr.replica_id

inner join sys.availability_replicas ar2

on avr.read_only_replica_id = ar2.replica_id

Routing configuration:

Note: the routing cannot be changed via the GUI, if you want to remove a replica and re-add then you’d need to re-configure the routing for the Always on Availability Group by TSQL.

ALTER AVAILABILITY GROUP [NAAG-SQL]

MODIFY REPLICA ON

N’NODE1′ WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

 

ALTER AVAILABILITY GROUP [NAAG-SQL]

MODIFY REPLICA ON

N’NODE1′ WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://NODE1.CONTOSO.COM:1433′));

ALTER AVAILABILITY GROUP [NAAG-SQL]

MODIFY REPLICA ON

N’NODE2′ WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [NAAG-SQL]

MODIFY REPLICA ON

N’NODE2′ WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://NODE2.CONTOSO.COM:1433′));

ALTER AVAILABILITY GROUP [NAAG-SQL]

MODIFY REPLICA ON

N’NODE1′ WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘NODE2′,’NODE1′)));

ALTER AVAILABILITY GROUP [NAAG-SQL]

MODIFY REPLICA ON

N’NODE2’ WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘NODE1′,’NODE2’)));

GO

Leave a Reply

avatar
  Subscribe  
Notify of