Configure SQL Server AlwaysOn Availability Groups on Linux - Part 3

Create the SQL Server 2017 AlwaysOn Availability Group

In this post, we will see Step-by-step Installation and Configuration of SQL Server 2017 Availability Groups on a Linux Cluster. In Part 2, we enable the Always On Availability Groups feature and configure the security requirements for authenticating the replicas of the Always On Availability Group. This post is a walk through the creation of the Availability Group and its corresponding listener name.

Create the AlwaysOn Availability Group with a listener name

Open SQL Server Management Studio. Connect to the SQL Server instance. In Object Explorer, expand the AlwaysOn High Availability folder. Right-click the Availability Groups node and select the New Availability Group Wizard command. This opens the New Availability Group Wizard dialog box.

Cluster type and failover mode
New to SQL Server 2017 (14.x) is the introduction of a cluster type for AGs. For Linux, there are two valid values: External and None.Using External for cluster type requires that the failover mode be set to External as well.Automatic failover is supported, but unlike a WSFC, failover mode is set to External, not automatic, when Pacemaker is used. A cluster type of None means that there is no requirement for, nor will the AG use, Pacemaker.A cluster type of None only supports manual failover from a primary to a secondary replica.An AG created with None is primarily targeted for the read-scale out scenario as well as upgrades.A cluster type of External means that Pacemaker will be used underneath the AG.

Choose EXTERNAL as this is a Linux operating system. Pacemaker will be installed and configured after the Always On Availability Group is created and configured.

In the Select Databases page, select the checkbox beside the database to include in Availability Group. The databases have to be in Full recovery model prior to joining them in the Availability group. Click Next.
In below screenshot, we have selected AdventureWorks database to Always on availability.

In the Replicas tab, click on the Add Replica button to add the SQL Server instances that you want to configure as secondary replicas.

In the Endpoints tab, verify that the port number value is 5022 and endpoint name ENDPOINT_AVAILABILITY.

In the Listener tab, select the Create an availability group listener option. Enter the following details.

Listener DNS Name
Listener IP Address

Click the Add… button to provide an IP address. In the Add IP Address dialog box, enter virtual IP address in the IPv4 Address field. Click OK. Click Next.

In the Select Initial Data Synchronization dialog box, you can choose Automatic Seeding feature and click Next.

In the Validation dialog box, make sure that all checks return successful results. Click Next.

You can view the Availability Group dashboard for the state and configuration information.

Click Close after the wizard completes the creation of the Always On Availability Group.

SELECT AS 'AG Name', ar.replica_server_name AS 'Replica Instance',
DB_NAME(dr_state.database_id) AS 'Database',
Location = CASE
WHEN ar_state.is_local = 1 THEN N'LOCAL'
Role = CASE
ELSE ar_state.role_desc  END,
ar_state.connected_state_desc AS 'Connection State', ar.availability_mode_desc AS 'Mode',
dr_state.synchronization_state_desc AS 'State'
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 ar_state ON ar.replica_id = ar_state.
JOIN sys.dm_hadr_database_replica_states dr_state ON
ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id;
SELECT b.dns_name, a.ip_address, a.ip_subnet_mask, a.state_desc, b.port
FROM sys.availability_group_listener_ip_addresses a
INNER JOIN sys.availability_group_listeners b
ON a.listener_id=b.listener_id

While you were able to create the Always On Availability Group listener name and create the corresponding DNS entry, it is still considered OFFLINE. Hence, you still won’t be able to connect to it. This will create and configure the AlwaysOn Availability Group listener name and create the corresponding DNS entry but it will be offline and may not be available to connect.

That’s because it is still not exposed to the Linux operating system. In order to access the Always On Availability Group through the listener name, it has to be registered on the Linux cluster resource manager

No comments:

Post a Comment