Install and Configure SQL Server 2017 Availability Groups on Linux

By: Edwin Sarmiento | Updated: 2018-01-17 | Comments (3) | Related: 1 | 2 | 3 | 4 | 5 | > Availability Groups

Problem

In a previous tip on Installing SQL Server vNext CTP1 on Red Hat Linux 7.2, we have read about how we can now install SQL Server 2017 on a Linux operating system. We would like to evaluate running SQL Server 2017 Availability Groups on Linux. How do we go about building the Linux environment for SQL Server 2017 Availability Groups?

Solution

To continue this series on Step-by-step Installation and Configuration of SQL Server 2017 Availability Groups on a Linux Cluster, we will look at creating the SQL Server 2017 Always On Availability Group on the Linux cluster. In Part 2, you have learned how to enable the Always On Availability Groups feature and install the Linux resource agent for SQL Server Always On Availability Groups. You’ve also learned how to configure the security requirements for authenticating the replicas of the Always On Availability Group. This tip will walk you through the creation of the Availability Group and its corresponding listener name.

Create the SQL Server 2017 Always On Availability Group on Linux

For reference, a review of the high-level steps is shown below. This tip will continue on Step #14.

Enable SQL Server Always On Availability Groups feature
Install Linux resource agent for SQL Server Always On Availability Groups on all servers
Enable Always On Availability Group endpoint port on the firewall for all Linux hosts
Create a database master key on the primary replica
Create the certificate that will be used to encrypt the Availability Group endpoint
Create the Always On Availability Group endpoint for the primary replica using the certificate for authentication
Export the certificate to a file
Copy the certificate file to the secondary replicas
Grant the SQL Server account permissions on certificate files
Create a login on the primary replica for use by the secondary replicas
Create a user for the login
Associate the certificate created in Step #5 with the user
Grant the CONNECT permission on the login
Create the Always On Availability Group with a listener name

Step #14: Create the Always On Availability Group with a listener name

After the endpoints have been created and security requirements to authenticate the replicas have been configured, you can proceed to create the Always On Availability Group using the New Availability Group Wizard. For this example, the sample Northwind database is used, configured in FULL recovery model with at least one full database backup.

NOTE: Should you decide to use the New Availability Group Wizard, check that the server hostnames of all the replicas can be resolved – both using server names and fully qualified domain names (FQDN). The wizard uses server hostnames to join the Always On Availability Group instead of FQDN. This is a common networking issue that administrators face when using the New Availability Group Wizard.

From within Object Explorer, expand the AlwaysOn High Availability node and the Availability Groups node
Right-click the Availability Groups node and select the New Availability Group Wizard command. This opens the New Availability Group Wizard dialog box

In the Specify Availability Group Options dialog box, type the name of the Availability Group in the Availability group name: textbox. Notice the values available in the Cluster type: drop-down list – EXTERNAL and NONE. SSMS detected that the underlying operating system is Linux and did not provide the option Windows Server Failover Clustering. The following cluster types available are:
- Windows Server Failover Cluster. The traditional cluster type for Windows
- EXTERNAL. For Linux operating systems via integration with Pacemaker, a Linux cluster resource manager
- NONE. Can be used for either Windows or Linux and does not provide support for high availability

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 dialog box, select the database that you want to include in your Always On Availability Group. Click Next.

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

Notice the new Failover Mode value. In the past, Automatic and Manual were the only values available. The Failover Mode value External is for use with a Linux cluster resource manager like Pacemaker to handle the failover – be it automatic or manually invoked by the administrator.

In the Endpoints tab, notice that the endpoints created in Step #6 are all available.

specify availability group replicas endpoints

Proceed to create the Listener name in the Listener tab.
- Select the Create an availability group listener option
- Type the Listener DNS name and Port number
- Select Static IP in the Network Mode: drop-down list
- Provide the virtual IP address by clicking the Add … button

specify availability group replicas listener

This is another thing to consider with SQL Server Always On Availability Groups running on Linux. In a WSFC, the listener name is automatically added to the Microsoft DNS server via Active Directory-integrated DNS zones. In this case, you will have to manually add the listener name as a DNS record.

In the Select Initial Data Synchronization dialog box, you can choose to either use the new Automatic Seeding feature, the usual Full database and log backup option, Join only option or Skip the initial data synchronization option. This example uses Automatic Seeding since the database is relatively small. Refer to this tip for more information about Automatic Seeding. Click Next.

select availability group initial data synch

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

In the Summary dialog box, verify that all the configuration settings are correct. Click Finish to create the Always On Availability Group.

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

You can also use T-SQL to create the Always On Availability Group.

--Run this on the primary replica/LINUXHA-SQLAG1. Just to be sure, enable SQLCMD mode in SSMS
--Pass the SQL Server credentials since this is configured for mixed mode authentication  
:CONNECT LINUXHA-SQLAG1 -U sa -P y0ur$ecUr3PAssw0rd
USE [master]
GO
 
CREATE AVAILABILITY GROUP [LINUX_SQLAG]
WITH 
(
   AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
   DB_FAILOVER = ON,
   DTC_SUPPORT = NONE,
   CLUSTER_TYPE = EXTERNAL
)
FOR DATABASE [Northwind]
REPLICA ON 
N'LINUXHA-SQLAG1' WITH 
(
   ENDPOINT_URL = N'TCP://LINUXHA-SQLAG1.TESTDOMAIN.COM:5022', 
   FAILOVER_MODE = EXTERNAL, 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   SEEDING_MODE = AUTOMATIC,
   SECONDARY_ROLE
   (
      ALLOW_CONNECTIONS = NO
   )
),
   
N'LINUXHA-SQLAG2' WITH 
(
   ENDPOINT_URL = N'TCP://LINUXHA-SQLAG2.TESTDOMAIN.COM:5022', 
   FAILOVER_MODE = EXTERNAL, 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   SEEDING_MODE = AUTOMATIC,
   SECONDARY_ROLE
   (
      ALLOW_CONNECTIONS = NO)
   ),
   
N'LINUXHA-SQLAG3' WITH 
(
   ENDPOINT_URL = N'TCP://LINUXHA-SQLAG3.TESTDOMAIN.COM:5022', 
   FAILOVER_MODE = EXTERNAL, 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   SEEDING_MODE = AUTOMATIC,
   SECONDARY_ROLE
   (
      ALLOW_CONNECTIONS = NO)
   );
 
GO

Refer to the screenshots for the parameters used in the T-SQL script to create the Always On Availability Group.

After creating the Always On Availability Group on the primary replica, proceed to join the secondary replicas to the Availability Group. Also, since Automatic Seeding was used to initialize the databases on the secondary replicas, you need to grant the CREATE ANY DATABASE permission as well.

--Run this on the secondary replica/LINUXHA-SQLAG2. Just to be sure, enable SQLCMD mode in SSMS
--Pass the SQL Server credentials since this is configured for mixed mode authentication  
:CONNECT LINUXHA-SQLAG2 -U sa -P y0ur$ecUr3PAssw0rd
ALTER AVAILABILITY GROUP [LINUX_SQLAG] JOIN WITH (CLUSTER_TYPE = EXTERNAL)
ALTER AVAILABILITY GROUP [LINUX_SQLAG] GRANT CREATE ANY DATABASE;
GO
 
--Run this on the secondary replica/LINUXHA-SQLAG3. Just to be sure, enable SQLCMD mode in SSMS
--Pass the SQL Server credentials since this is configured for mixed mode authentication  
:CONNECT LINUXHA-SQLAG3 -U sa -P y0ur$ecUr3PAssw0rd
ALTER AVAILABILITY GROUP [LINUX_SQLAG] JOIN WITH (CLUSTER_TYPE = EXTERNAL)
ALTER AVAILABILITY GROUP [LINUX_SQLAG] GRANT CREATE ANY DATABASE;
GO

Use the T-SQL script below to create the Always On Availability Group listener name.

--Run this on the primary replica/LINUXHA-SQLAG1. Just to be sure, enable SQLCMD mode in SSMS
--Pass the SQL Server credentials since this is configured for mixed mode authentication  
:CONNECT LINUXHA-SQLAG1 -U sa -P y0ur$ecUr3PAssw0rd
USE [master]
GO
ALTER AVAILABILITY GROUP [LINUX_SQLAG]
ADD LISTENER N'LINUX-SQLAG' 
(
WITH IP
(
     (N'10.10.10.58', N'255.255.255.0')
)
,    PORT=1433
);
GO

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

You can also use the T-SQL script below to view the status of the Always On Availability Group.

SELECT ag.name 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'
ELSE 'REMOTE' END,
Role = CASE
WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'
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.
replica_id)
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;
GO
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

Notice the state_desc column from sys.availability_group_listener_ip_addresses. 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.

That’s because it is still not exposed to the Linux operating system. In order to access the Always On Availability Group thru the listener name, you will have to register it on the Linux cluster resource manager.

In the next tip in this series, you will install and configure the Linux cluster resource manager, Pacemaker. Once configured, you will then register the SQL Server Always On Availability Group together with the listener name on Pacemaker.

Next Steps

Review the previous tips on SQL Server on Linux
Read more on the following topics
- Configure Always On availability group for SQL Server on Linux

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-01-17

Install and Configure SQL Server 2017 Availability Groups on Linux - Part 3