Introduction

Always On Availability Groups provides high availability for groups of databases on direct-attached storage. The feature enables multi-database failover, fast failure detection and failover, and database-level health monitoring and failover triggering.  This broad set of capabilities allows you to achieve optimal-availability service level agreements (SLAs) for your workloads.

Always On Availability Groups is now supported on Linux. To accommodate SQL Server workloads with rigorous business continuity requirements, availability groups run on all supported Linux OS distributions. All capabilities that make availability groups a flexible, integrated, and efficient high availability/disaster recovery (HADR) solution are available on Linux as well. Always On Availability Groups works on all supported Linux distributions, namely Red Hat Enterprise Linux (RHEL), Ubuntu, and the SUSE Linux Enterprise Server.

Always On Availability Groups for Linux in SQL Server 2017 uses a cluster manager to provide business continuity. In Windows, SQL Server relies on Windows Server failover clustering to provide the infrastructure features that support HADR scenarios. Similarly, on Linux platforms, SQL Server is natively integrated with popular clustering solutions like Pacemaker, so it can benefit from the health monitoring, failure detection, or failover coordination of the clustering layer. Integration with Pacemaker enables orchestration for monitoring, failure detection, and failover.

Understanding the environment

Here are the architecture details of the lab environment:

   A virtual network containing single subnet and the lab is setup with no shared disks or separate storage network.

   Three SQL Servers deployed on Linux.

   One workstation machine running RHEL with the GNOME graphical UI; Node.js to run the web application, and Visual Studio Code with extensions to run the web application and connect to instances of SQL Server.

   A three-node Pacemaker cluster with the Node Majority quorum model.

RHEL Workstation agapphost

VSCode Node.js MyExpenses


RHEL Server agnode01

SQL Server

Pacemaker


RHEL Server agnode02

SQL Server

Pacemaker


RHEL Server agnode03

SQL Server

Pacemaker


Overview of Always On Availability Groups and

RHEL clusters for SQL Server Availability Groups

Always On Availability Groups requires a cluster manager. In Windows, failover clustering provides the cluster manager. In Linux, you can use Pacemaker. To create the availability group for HA on Linux, configure the availability group with the CLUSTER TYPE as EXTERNAL.

The EXTERNAL value for the CLUSTER_TYPE option specifies that an external cluster entity manages the availability group. A pacemaker is an example of an external cluster entity.

The steps to create an availability group on Linux for high availability are different from the steps on a Windows

Server failover cluster. The following list describes the high-level steps:

1.    Configure SQL Server on the cluster nodes.

2.    Create the availability group.

3.    Configure a cluster resource manager, like Pacemaker.

4.   Add the availability group as a resource in the cluster.

Configure Always On Availability Groups for

SQL Server on Linux

Enabling Always On Availability Groups on both machines

Before you begin to create the availability group, the Always On Availability Groups feature must be enabled on every instance of SQL Server 2017 that is to participate in an availability group.

We will execute this step using Transact-SQL on Visual Studio Code, a graphical code editor for Linux, macOS, and Windows that supports extensions. It will enable you to connect to SQL Server, query with Transact-SQL, and view the result.

1.    Go to the Linux Server desktop environment, open Applications, and select Terminal.

2.    In the terminal window, launch a secure session to the first availability group node, agnode01

$ ssh labuser@agnode01

Warning: If you are accessing the nodes for the first time, a warning will appears and display for the ECDSA

fingerprint, type yes (the full word) and press enter. When prompted, enter the password for labuser: Pass@word1

The authenticty of host ‘agnode01 (192.168.1.11)’ can’t be established. ECDSA key fingerprint is SHA256:XXXXX+XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX. ECDSA key fingerprint is MD5:0X:XX:00:X0:0X:0X:0X:XX:00:X0:0X:0X:X0:0X:0X. Are you sure yo want to continue connecting (yes/no)?

Warning: Permanently added ‘agnode01,192.168.1.11’ (ECDSA) to the list of known hosts.

labuser@agnode01’s password:

3.    Run the following commands to enable Always On Availability Groups on each node that hosts SQL Server service, and then restart the SQL Service:

When prompted for the password, use the default password, Pass@word1

$ sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1

$ sudo systemctl restart mssql-server.service

Once complete, type ‘exit’ and press enter.

4.   You will need to repeat the process for the remaining nodes:

NOTE: You can open additional terminal tabs for easy session switching, File -> Open Tab.

$ ssh labuser@agnode02

labuser@agnode02’s password:

$ sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1

$ sudo systemctl restart mssql-server.service

$ exit

$ ssh labuser@agnode03

labuser@agnode03’s password:

$ sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1

$ sudo systemctl restart mssql-server

$ exit

6.    Next, create a database mirroring endpoint user. Run the following command on all instances of SQL Server to create the database mirroring endpoint user (01_CreateLogin.sql):

a.        If VS Code is not already open, go to the Linux Server desktop environment, open Applications, search for Visual Studio Code, and then click the icon to launch. Visual Studio will open. Open the scripts folder using the File menu, or the shortcut keys [ctrl + K, ctrl +O], located in ~/HandsOnLabs/Apps/developer-immersion-data/scripts

b.   In the file explorer (left pane), select the file ‘01_CreateLogin.sql’.

USE [master]; GO

DROP USER IF EXISTS dbm_user;

IF EXISTS(SELECT name FROM sys.server_principals WHERE name = ‘dbm_login’)

DROP LOGIN dbm_login;

GO

CREATE LOGIN dbm_login WITH PASSWORD = 'Pass@word1';

CREATE USER dbm_user FOR LOGIN dbm_login;


c.    Open the command palette from the View menu, or use the shortcut keys [ctrl + shift + P].

Begin typing ‘SQL’ in the command palette to get a list of MS SQL commands. Select ‘Connect’, and then the desired SQL Server instance, or create a new connection. For this lab, connection profiles to all 3 nodes have been created in advance.

Or, use the connection shortcut keys [ctrl + shift + c] to get a list of connections.

d.   Choose the desired connection (AGNode01). Enter the password (Pass@word1) when prompted.

e.    Right-click in the editor pane and select ‘Execute Query’, or use the shortcut keys [ctrl + shift +

E] to execute the script.

f.        Repeat for node 02. Use shortcut keys [ctrl + shift + C], select the next node (AGNode02). Enter the password (Pass@word1), then use shortcut keys [ctrl + shift + E] to execute the script.

g.   Repeat for node 03. Use shortcut keys [ctrl + shift + C], select the next node (AGNode02). Enter the password (Pass@word1), then use shortcut keys [ctrl + shift + E] to execute the script.

7.    Now we need to create a SQL Server certificate. SQL Server service on Linux uses certificates to authenticate communication between the mirroring endpoints. The following Transact-SQL script creates a master key and certificate. It then backs up the certificate and secures the file with a private key. Update the script with strong passwords. Connect to the primary instance of SQL Server and run the following Transact-SQL to create the certificate (02_CreateSqlServerCert.sql):

a.    In the file explorer, select the file ‘02_CreateSqlServerCert.sql’.

USE [master];


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'M4sterK3y_Pass@word1'; CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';

BACKUP CERTIFICATE dbm_certificate

TO FILE = '/var/opt/mssql/data/dbm_certificate.cer' WITH PRIVATE KEY (

FILE = '/var/opt/mssql/data/dbm_certificate.pvk',

ENCRYPTION BY PASSWORD = 'Pr1vateK3y_Pass@word1'

);

b.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode01’. c.    Use the shortcut keys [ctrl + shift + E] to execute the script.

8.    At this point, your primary SQL Server replica has a certificate at /var/opt/mssql/data/dbm_certificate.cer, and a private key at var/opt/mssql/data/dbm_certificate.pvk. Copy these two files to the same location on all servers that will host availability replicas. You will have to provide read and write access to the target directories on each node. Additionally, you will need to add a firewall rule to allow traffic on TCP port

5022; this enables adding replicas to the availability group.

a.    Switch to the terminal window, and connect to AGNode01:

$ ssh labuser@agnode01

$ sudo usermod -a -G mssql labuser

$ sudo chmod g+rw /var/opt/mssql/data

$ sudo firewall-cmd --zone=public --add-port=5022/tcp --permanent

$ sudo firewall-cmd --reload

$ exit

b.   You will be prompted for the password twice (Pass@word1). The usermod command (note the upper-case ‘G’) adds labuser to the mssql group. The chmod command adds read and write permissions to the /var/opt/mssql/data directory for all members of the mssql group.

c.    Repeat for AGNode02:

$ ssh labuser@agnode02

$ sudo usermod -a -G mssql labuser

$ sudo chmod g+rw /var/opt/mssql/data

$ sudo firewall-cmd --zone=public --add-port=5022/tcp --permanent

$ sudo firewall-cmd --reload

$ exit

a.    Repeat for AGNode03:

$ ssh labuser@agnode03

$ sudo usermod -a -G mssql labuser

$ sudo chmod g+rw /var/opt/mssql/data

$ sudo firewall-cmd --zone=public --add-port=5022/tcp --permanent

$ sudo firewall-cmd --reload

$ exit

b.   Finally, start the copy operation (from AGNode01):


$ ssh labuser@agnode01

$ sudo scp /var/opt/mssql/data/dbm_certificate.*

labuser@agnode02:/var/opt/mssql/data/

$ sudo scp /var/opt/mssql/data/dbm_certificate.*

labuser@agnode03:/var/opt/mssql/data/

9.    On the target server, give permission to mssql user to access the certificate:

$ ssh labuser@agnode02

$ sudo cd/var/opt/mssql/data

$ sudo chown mssql:mssql dbm_certificate.*

$ exit

$ ssh labuser@agnode03

$ sudo cd /var/opt/mssql/data

$ sudo chown mssql:mssql dbm_certificate.*

$ exit

10.  Now we need to create the certificate on the secondary servers. The following Transact-SQL script creates a master key and certificate from the backup that you created on the primary SQL Server replica. The command also authorizes the user to access the certificate (04_CreateSecondaryCert.sql):

a.    Return to VS Code. In the file explorer, select the file ‘04_CreateSecondaryCert.sql’.

USE [master]; GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'M4sterK3y_Pass@word1'; CREATE CERTIFICATE dbm_certificate

AUTHORIZATION dbm_user

FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'

WITH PRIVATE KEY (

FILE = '/var/opt/mssql/data/dbm_certificate.pvk', DECRYPTION BY PASSWORD = 'Pr1vateK3y_Pass@word1'

);

b.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode02’.

c.    Use the shortcut keys [ctrl + shift + E] to execute the script.

d.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode03’.

e.    Use the shortcut keys [ctrl + shift + E] to execute the script.

NOTE: the virtual lab version of this script includes DROP clauses which may report errors if the drop condition fails. The critical portion is the success of the CREATE clauses.


11.   Finally, we need to create the database mirroring endpoints on all replicas. Database mirroring endpoints use Transmission Control Protocol (TCP) to send and receive messages between the server instances participating in database mirroring sessions or hosting availability replicas. The database mirroring endpoint listens on a unique TCP port number. The following Transact-SQL creates a listening endpoint named for the availability group. It starts the endpoint and gives connect permission to the user that you created (05_CreateListenerEndpoint.sql).

a.    In the file explorer, select the file ‘05_CreateListenerEndpoint.sql’.

USE [master]; GO

CREATE ENDPOINT [Hadr_endpoint]

AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = 5022)

FOR DATA_MIRRORING (

ROLE = ALL,

AUTHENTICATION = CERTIFICATE dbm_certificate,

ENCRYPTION = REQUIRED ALGORITHM AES

);

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];

b.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode01’.

c.    Use the shortcut keys [ctrl + shift + E] to execute the script.

d.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode02’.

e.    Use the shortcut keys [ctrl + shift + E] to execute the script.

f.    Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode03’.

g.   Use the shortcut keys [ctrl + shift + E] to execute the script.

NOTE: the virtual lab version of this script includes DROP clauses which may report errors if the drop condition is not met. The critical portion is the success of the CREATE clauses.

Creating the availability group

After enabling the Always On Availability Groups feature on all SQL instances, we are now ready to configure an availability group using Transact-SQL on Visual Studio Code. In order to create the availability group for HA on Linux, use the CREATE AVAILABILITY GROUP Transact-SQL DDL with CLUSTER_TYPE = EXTERNAL.

The EXTERNAL value for CLUSTER_TYPE option specifies that the external cluster entity manages the availability group. Pacemaker is an example of an external cluster entity.


1.        From Visual Studio Code, use the following Transact-SQL script to create an availability group for HA named ag1. The script configures the availability group replicas with SEEDING_MODE = AUTOMATIC. This setting causes SQL Server to automatically create the database on each secondary server (06_CreateAvailabilityGroup.sql).

a.    In the file explorer, select the file ‘06_CreateAvailabilityGroup.sql’.

USE [master]; GO

CREATE AVAILABILITY GROUP [ag1]

WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL) FOR REPLICA ON

N'AGNode01' WITH (

ENDPOINT_URL = N'tcp://agnode01:5022',

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC

SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)

),

N'AGNode02'

WITH (

ENDPOINT_URL = N'tcp://agnode02:5022',

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL,

SEEDING_MODE = AUTOMATIC

SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)

),

N'AGNode03' WITH (

ENDPOINT_URL = N'tcp://agnode03:5022',

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL,

SEEDING_MODE = AUTOMATIC

SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)

);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

b.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode01’.

c.    Use the shortcut keys [ctrl + shift + E] to execute the script.

Note: Here you have configured an availability group with CLUSTER_TYPE=EXTERNAL

2.    Now we need to join secondary replicas to the availability group. On each SQL Server instance that will host a secondary replica, run the following Transact-SQL to join the availability group (07_JoinSecondaryToAG.sql):

a.    In the file explorer, select the file ‘07_JoinSecondaryToAG.sql’.

USE [master]; GO

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

b.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode02’.


c.    Use the shortcut keys [ctrl + shift + E] to execute the script.

d.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode03’.

e.    Use the shortcut keys [ctrl + shift + E] to execute the script.

3.    Next, we will add a database to the availability group. But before this, we need to ensure that the database we are adding to the Availability group is in full recovery mode and has a valid log backup. If this is a test database or a new database that was created, take a database backup. On the primary SQL Server, run the following Transact-SQL to create and back up a database (08_BackupExpensesDB.sql).

Note: For this exercise, the Expenses database has been created and populated beforehand. If you are creating your own environment, you will need to follow steps listed in “Setting up the application database” before starting this step. For more details, please see Page 35 in the appendix

ALTER DATABASE [Expenses] SET RECOVERY FULL; BACKUP DATABASE [Expenses]

TO DISK = N'/var/opt/mssql/data/Expenses.bak';

4.   On the primary SQL Server replica, run the following Transact-SQL to add a database to an availability group (09_AddPrimaryToAG.sql)

a.    In the file explorer, select the file ‘09_AddPrimaryToAG.sql’

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [Expenses];

b.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode01’.

c.        Use the shortcut keys [ctrl + shift + E] to execute the script. We can also verify and ensure that the database is created on the secondary servers (10_VerifyReplication.sql):

a.    In the file explorer, select the file ‘10_VerifyReplication.sql

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


b.   If you run the above query on AGNode01, you can see the synchronization states of all the nodes.

Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode01’. Use the shortcut keys [ctrl + shift + E] to execute the script.

In the next section, you will learn how to configure the Red Hat Enterprise Linux cluster for SQL Server

Availability Groups cluster resources.

Configure RHEL clustering for SQL Server availability groups

After you create the availability group, you must configure RHEL clustering and integration with a cluster technology like Pacemaker for HA. In this section, we will walk you through the steps to set up a Pacemaker cluster and add an availability group as a resource in the cluster for high availability.

Configuring Pacemaker for RHEL

1.    On each cluster node, we need to create a passwd file:

$ sudo touch /var/opt/mssql/secrets/passwd

$ sudo echo 'pacemakerLogin' >> /var/opt/mssql/secrets/passwd

$ sudo echo 'P4cemaker_Pass@word1' >> /var/opt/mssql/secrets/passwd

$ sudo chown root:root /var/opt/mssql/secrets/passwd

$ sudo chmod 600 /var/opt/mssql/secrets/passwd

2.    On each node, open the Pacemaker firewall ports. To open these ports with firewall, run the following

$ sudo firewall-cmd --permanent --add-service=high-availability

$ sudo firewall-cmd --reload

commands in the terminal:

3.    By default, the repositories for high availability are not enabled and need to be added:

$ sudo subscription-manager repos --enable=rhel-ha-for-rhel-7-server-rpms

$ sudo subscription-manager repos --enable=rhel-ha-for-rhel-7-server-eus-rpms

4.   Install Pacemaker packages on each node:

$ sudo yum install pacemaker pcs fence-agents-all resource-agents

5.    Set the password for the default user that is created when installing Pacemaker and Corosync packages.

$ sudo passwd hacluster

Use the password ‘Clust3rPass@word1’

6.    Enable and start pcsd service and Pacemaker. This will allow a node to rejoin the cluster after the reboot.

Run the following command on each node:

$ sudo systemctl enable pcsd

$ sudo systemctl start pcsd

$ sudo systemctl enable pacemaker

7.    Create the cluster. To create the cluster, run the following command on the primary node:

$ sudo pcs cluster auth agnode01 agnode02 agnode03 -u hacluster -p

Clust3rPass@word1

$ sudo pcs cluster setup --name ag1 agnode01 agnode02 agnode03

$ sudo pcs cluster start --all

8.    Install the SQL Server resource agent for SQL Server. Run the following commands on each node:

$ sudo yum install mssql-server-ha

9.    Pacemaker cluster vendors require STONITH to be enabled, and a fencing device configured for a supported cluster setup. You can use resource-level fencing, for instance, with DRBD (Distributed Replicated Block Device) to mark the disk on a node as outdated when the communication link goes down. Node-level fencing ensures that a node does not run any resources. This is done by resetting the node, and the Pacemaker implementation of it is called STONITH (which stands for "Shoot the Other

Node in the Head").

$ sudo pcs property set stonith-enabled=false

Note: STONITH also requires some additional configuration, from this lab standpoint the additional configuration part is not required and not covered in this exercise.

Creating a SQL Server login for Pacemaker

1.    Now we need to set the permission level. On all instances of SQL Server, create a server login for

Pacemaker (11_CreatePacemakerLogin.sql):

a.    In the file explorer, select the file ‘11_CreatePacemakerLogin.sql’

USE [master] GO

CREATE LOGIN [pacemakerLogin] with PASSWORD= N'P4cemaker_Pass@word1'; ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin];

b.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode01’.

c.    Use the shortcut keys [ctrl + shift + E] to execute the script.

d.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode02’.

e.    Use the shortcut keys [ctrl + shift + E] to execute the script.

f.    Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode03’.

2.    The following Transact-SQL grants only the required permission to the Pacemaker login

(12_GrantPacemakerPermissions.sql):

a.    In the file explorer, select the file ‘12_GrantPacemakerPermissions.sql’

USE [master] GO

GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::ag1

TO[pacemakerLogin];

b.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode01’.

c.    Use the shortcut keys [ctrl + shift + E] to execute the script.

d.   Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode02’.

e.    Use the shortcut keys [ctrl + shift + E] to execute the script.

f.    Use the shortcut keys [ctrl + shift + C] and choose the connection ‘AGNode03’.

Creating the availability group resource

   To create the availability group resource, use the pcs resource createcommand and set the resource properties. This command creates a resource for an availability group with a name. In the terminal type:

$ sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=ag1 master notify=true

Creating the virtual IP resource

   To use a connection string that points to a string server name and not use the IP address, register the virtual IP resource address. To create the virtual IP address resource, run the following command on one node. Use a static IP address that’s available on the network.

$ sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=192.168.1.115 cidr_netmask=24

Verify resources are online and the virtual IP is in the ‘started’ state

   Show overall cluster and resource status with:

$ sudo pcs status

   Bring any offline resources online with:

$ sudo pcs resource enable [name_of_resource]

Adding a colocation constraint

Almost every decision in a Pacemaker cluster, like choosing where a resource should run, is made by comparing scores. Scores are calculated per resource, and the cluster resource manager chooses the node with the highest score for a resource. (If a node has a negative score for a resource, the resource cannot run on that node.) We can manipulate the decisions of the cluster by using constraints. Each constraint has score. We want to ensure that the primary of the availability group and the virtual IP resource are run on the same host, so we’ll define a colocation constraint with a score of INFINITY.

   To add the colocation constraint, run the following command on one node:

$ sudo pcs constraint colocation add virtualip agcluster-master INFINITY with-rsc-role=Master

Adding an ordering constraint

The colocation constraint has an implicit ordering constraint. It moves the virtual IP resource before it moves the availability group resource. To prevent the IP address from temporarily pointing to the node with the pre-failover secondary, add an ordering constraint.

   To add an ordering constraint, run the following command on one node:

$ sudo pcs constraint order promote agcluster-master then start virtualip

We have now configured the RHEL machine as a secondary replica for SQL Server availability groups. Now we need to test this implementation with a sample application.

Configure the sample application

Now, to demonstrate continuity in the event of a failover, we’ll configure a sample application with a database called MyExpenses.

This app runs on Node.js.

Setting up the sample application project

1.        In Visual Studio Code, open the web app folder. One the File menu, select Open Folder (Ctrl+K, Ctrl+O) and navigate to /home/labuser/HandsOnLabs/Apps/developer-immersion-data- master/source/Expenses.SQLLinux/MyExpenses.Web.

2.    You’ll need to update the database connection details for the app. Open the file

./server/config/server.config.js and update the login password and port information. For this lab, use the following information:

     userName: sa

     password: Pass@word1

     host: agcluster

     port: 1433


3.   Supply the following system administrator (SA) password: Pass@word1

4.   The default port in the sample db.config.js file expects TCP port 11433. Change this value to port 1433.

5.   After the configuration is updated, run the Prepare task to build the project:

6.   Press F5 in Visual Studio Code to run the application. You can see it executing and listening on port 8000:

7.    Open your browser and go to http://localhost:8000.


8.   Click SIGN IN to ensure that the app shows the data.

Execute a manual failover

For training purposes, we’ll manually trigger a failover by temporarily stopping the HA services on the primary node. This will cause the failover to execute once communication is lost with the primary node.

1. Open a terminal prompt, connect to the primary node (agnode01) and enter the following:

$ ssh labuser@agnode01

$ sudo systemctl stop pacemaker

2.    Check the status of the cluster:

$ ssh agnode02

$ sudo pcs status

NOTE: It may take a minute or two for the AG to fully failover

3.    Refresh the sample application page. Navigate to a couple of pages to confirm that the app is working.


Install the mssql-server package

Requirements for package installation

The package can be deployed in a Minimal Server (Terminal only) or within a GUI-enabled installation with

Terminal. Requirements:

   Red Hat Enterprise Linux 7.3, Ubuntu 16.04 LTS

   Minimum of 4 GB of RAM

   Minimum of 4 GB of disk space

   XFS file system (the default on RHEL) or EXT4 file system (the default on Ubuntu)

   1433 TCP port open on the firewall for SQL Server connections

   Connectivity to the Internet to download the package

Additional notes:

   firewalld is a recommended package. In this lab, it will be installed before mssql-server is installed.

   dos2unix is a required package. In this lab, it will be installed before mssql-server is installed. Login info:

User name: labuser

Password: Pass@word1


Installing Red Hat Enterprise Linux

Configuring the distribution repository

1.    Run the following commands before installation to ensure greatest system stability:

$ sudo yum update

2.    Add the repo information for mssql-server to /etc/yum/repos.d/:

$ sudo curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo >

/etc/yum.repos.d/mssql-server.repo


3.    Run the following command to prepare the repository data for SQL Server:

$ sudo yum update

Installing SQL Server on Linux

1.    Install the mssql-server package:

$ sudo yum install -y mssql-server


After installation on Linux, you need to do the following to accept the license agreement and provide the system administrator (SA) password.

2.    Run the configuration script to accept the license agreement and provide the SA password:

$ sudo /opt/mssql/bin/mssql-conf setup

You should be prompted to allow SQL Server to start and enable it to start on boot. If you’re not, you can

enable and start SQL Server by using the following commands:

$ sudo systemctl enable mssql-server

$ sudo systemctl start mssql-server

3.    You need to open a port on the firewall. If you’re using firewalld as your firewall, you can use these commands:

$ sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent

$ sudo firewall-cmd --reload

4.   Verify that the package was installed:

$ rpm -qa | grep mssql

Note: The current release also includes the package msssql-conf. You can run /opt/mssql/bin/mssql- confto learn more.

Installing the command-line tools and ODBC drivers

The command line utilities for SQL Server (sqlcmd and bcp utilities) are in private preview and available for Red Hat Enterprise Linux 7.3. and Ubuntu 16.04. The sqlcmd utility allows users to connect to, send Transact-SQL batches from, and output rowset information from SQL Server instances. The bcp utility bulk copies data between an instance of SQL Server and a data file in a user-specified format. Installing these utilities will also


install the Microsoft ODBC driver and all its dependencies by default. To install these tools, run the following commands as detailed in the terminal.

The command-line tools (sqlcmd and bcp) can also be installed on Red Hat Enterprise Linux through the Microsoft ODBC drivers package. You can use an installation script to simplify the process. You can download and run it by following these steps:

1.    Add the repo information for mssql-tools to /etc/yum/repos.d/:

$ sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo >

/etc/yum.repos.d/msprod.repo

2.    If you had a previous version of mssql-tools installed, remove any older unixODBC packages:

$ sudo yum update

$ sudo yum remove unixODBC-utf16 unixODBC-utf16-devel

3.    Install the mssql-tools package:

$ sudo yum -y install mssql-tools unixODBC-devel

4.   You should now be able to call the sqlcmdand bcpcommands:

$ sqlcmd -H <hostname> -U <username> -P <password>

5.    Optional: Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.

6.    To make sqlcmd and bcp accessible from the bash shell for login sessions, modify your PATH in the

~/.bash_profile file with the following command:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

7.    To make sqlcmd and bcp accessible from the bash shell for interactive/non-login sessions, modify the

PATH in the ~/.bashrc file with the following command:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc


Set up the application database

This section describes how to set up the application database. We‘ll import the database to SQL Server. The

MyExpense app repository includes a data generation tool.

1.        Open the project folder in VS Code. From VS Code, select File, select Open Folder (Ctrl+K Ctrl+O), and navigate to /home/labuser/HandsOnLabs/Apps/developer-immersion-data- master/source/Expenses.SQLLinux/MyExpenses.DataGenerator:

2.    In the Explorer panel, expand the config subfolder and highlight the db.config.js file:

3.    You’ll need to supply the SA password you provided when you installed SQL Server.

4.   The default port in the sample db.config.js file is TCP port 11433. Change this value to whichever port has been mapped for 1433 on the container. In this lab, the external port number is also

1433.

5.    After the configuration is updated, build the project by running the prepare task:


6.    Press F5 to run the data generator app in debug mode.

7.    You will be presented with two data load options. Choose option 1. It will take a few seconds to run. This will create the database named Expenses and populate data that’s needed for the app. You can use the sqlcmd prompt to query the Expense database.