Configure SQL Server Always On Availability Group on Windows and Linux (Cross Platform) - Part 2

Create the SQL Server 2019 Always On Availability (AG) Group

The steps to create the AG are the same as the steps to create an AG for read-scale workloads. The AG cluster type is NONE, because there is no cluster manager.

Step #1: Create a login on both the replicas

On both the replicas, create a database login and password.

--Create login on the both replica
USE [MASTER]
GO
CREATE LOGIN AVG_LOGIN
WITH PASSWORD = 'P@ssw0rd';
GO
--Create a user for the login
USE [MASTER]
GO
CREATE USER AVG_LOGIN
FOR LOGIN AVG_LOGIN
GO


On the primary replica, create a master key and certificate, then back up the certificate with a private key.


Step #2: Create a master key and certificate on primary replica

SQL Server instance that will function as a primary replica requires a database master key. SQL-WINDOWS will be acting as the primary replica, create the database master key.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';

Create the certificate that will be used to encrypt the Availability Group endpoint

CREATE CERTIFICATE crossplatformavg_cert WITH SUBJECT = 'crossplatformavg_cert certificate for availability Group';



Step #3: Backup and copy the certificate with a private key on secondary replica.

Export the certificate to a file using a command as shown below.

BACKUP CERTIFICATE crossplatformavg_cert
TO FILE = 'C:\CertificatesBkp\crossplatformavg_cert.cer'
WITH PRIVATE KEY
       (
        FILE = 'C:\CertificatesBkp\crossplatformavg_cert.pvk',
        ENCRYPTION BY PASSWORD = 'P@ssw0rd>'
    );


Copy the Certificate and Private Key to the secondary replica.


Set the group and ownership of the private key and the certificate 
The following script sets the group and ownership of the files to mssql:mssql.

sudo chown mssql:mssql /var/opt/mssql/data/ crossplatformavg_cert.pvk
sudo chown mssql:mssql /var/opt/mssql/data/ crossplatformavg_cert.cer



Step #4: Create a master key and Certificate on secondary replica

On the secondary replica SQL-LINUX, create a database login and password and create a master key

--Create a login on the secondary replica
USE [MASTER]
GO
CREATE LOGIN AVG_LOGIN
WITH PASSWORD = 'P@ssw0rd';
GO
--Create a user for the login
USE [MASTER]
GO
CREATE USER AVG_LOGIN
FOR LOGIN AVG_LOGIN
GO
--Create master key on secondary replica
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO


On the secondary replica, restore the certificate you copied to /var/opt/mssql/data

CREATE CERTIFICATE crossplatformavg_cert  
    AUTHORIZATION AVG_LOGIN FROM
              FILE = '/var/opt/mssql/data/crossplatformavg_cert.cer'
    WITH PRIVATE KEY
       (
              FILE = '/var/opt/mssql/data/crossplatformavg_cert.pvk',
              DECRYPTION BY PASSWORD = 'P@ssw0rd>'
    )                                   



Step #5: Create an endpoint for use by Always-On Availability 

Create the Always On Availability Group endpoint on both the replicas using the certificate for authentication and grant CONNECT permissions on the login to successfully establish the communication between Always On Availability Group replicas.

CREATE ENDPOINT [CrossPlatformAVG_Endpoint]
AS TCP (LISTENER_IP = ALL, LISTENER_PORT = 5022)
FOR DATA_MIRRORING
                           (
                           ROLE = ALL,
                           AUTHENTICATION = CERTIFICATE crossplatformavg_cert,
                           ENCRYPTION = REQUIRED ALGORITHM AES
                     );
GO
ALTER ENDPOINT [CrossPlatformAVG_Endpoint] STATE = STARTED;
GO
GRANT CONNECT ON ENDPOINT::[CrossPlatformAVG_Endpoint] TO [AVG_LOGIN]
GO


Repeat the preceding script on the secondary replica to create the endpoint and grant permission.



Step #6: Create the Always On Availability Group

On the primary replica, create the AG with CLUSTER_TYPE = NONE. The script uses SEEDING_MODE = AUTOMATIC to create the AG.
To create the AG, update the values and run the script on the primary replica.

CREATE AVAILABILITY GROUP [AVG_Group1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'SQL-WINDOWS'
     WITH (
        ENDPOINT_URL = N'tcp://SQL-WINDOWS:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         SEEDING_MODE = AUTOMATIC,
         FAILOVER_MODE = MANUAL,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
         ),
        N'SQL-LINUX'
    WITH (
         ENDPOINT_URL = N'tcp://SQL-LINUX:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        SEEDING_MODE = AUTOMATIC,
        FAILOVER_MODE = MANUAL,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
        )
GO


On the secondary replica, Join the Availability Group.

ALTER AVAILABILITY GROUP [AVG_GROUP1] JOIN WITH (CLUSTER_TYPE = NONE)
ALTER AVAILABILITY GROUP [AVG_GROUP1] GRANT CREATE ANY DATABASE



Step #7: Create and add database to the AG

Creating sample database named TestDB1 using the below script.

Use [master]
GO
CREATE DATABASE [TestDB1]
CONTAINMENT = NONE
ON PRIMARY (NAME = N'TestDB1', FILENAME = N'C:\ShareFolder\TestDB1.mdf')
LOG ON (NAME = N'TestDB1_log', FILENAME = N'C:\ShareFolder\TestDB1_log.ldf')
GO

Take a full database backup and restore it on secondary replica SQL-LINUX with WITH NORECOVERY option.


Step #8: Add the database to the AG

On the primary replica, run the SQL query to add the database to the AG.

ALTER AVAILABILITY GROUP [AVG_Group1] ADD DATABASE TestDB1


Verify table in the database is in sync on the secondary replica.


Check the overall database health and role status for both the replicas using AlwaysOn Availability Dashboard.


Refer the link below to configure SQL Server Always On Availability Group on Windows and Linux (Cross Platform)

Note: Perform these steps in order.

No comments:

Post a Comment