Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - The Cloud won't fix that (click for more info)
 

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


By:   |   Read Comments (1)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > 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.

  1. Enable SQL Server Always On Availability Groups feature
  2. Install Linux resource agent for SQL Server Always On Availability Groups on all servers
  3. Enable Always On Availability Group endpoint port on the firewall for all Linux hosts
  4. Create a database master key on the primary replica
  5. Create the certificate that will be used to encrypt the Availability Group endpoint
  6. Create the Always On Availability Group endpoint for the primary replica using the certificate for authentication
  7. Export the certificate to a file
  8. Copy the certificate file to the secondary replicas
  9. Grant the SQL Server account permissions on certificate files
  10. Create a login on the primary replica for use by the secondary replicas
  11. Create a user for the login
  12. Associate the certificate created in Step #5 with the user
  13. Grant the CONNECT permission on the login
  14. 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.

  1. From within Object Explorer, expand the AlwaysOn High Availability node and the Availability Groups node
  2. Right-click the Availability Groups node and select the New Availability Group Wizard command. This opens the New Availability Group Wizard dialog box
new availability group wizard
  1. 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.

specify availability group options
  1. In the Select Databases dialog box, select the database that you want to include in your Always On Availability Group. Click Next.
select availability group databases
  1. 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.
specify availability group 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.

  1. 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
  1. In the Validation dialog box, make sure that all checks return successful results. Click Next.
validate availability group settings
  1. In the Summary dialog box, verify that all the configuration settings are correct. Click Finish to create the Always On Availability Group.
summary availability group settings
  1. Click Close after the wizard completes the creation of the Always On Availability Group.
results availability group setup

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.

availability group status

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
   
availability group status query

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.

availability group status command line

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


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, March 19, 2018 - 3:40:16 AM - Divesh Mathur Back To Top

 Hi Edwin,

Kindly update the link for part 4. I'm trying to configure Linux cluster resource manager, Pacemaker. However once I create the availability group on linux operating system, the AG on SQL Server is not working.

 


Learn more about SQL Server tools