Primary Always On

gistfile1.txt

-- PRIMARY
USE [master]
GO

drop AVAILABILITY GROUP [AG1]
drop endpoint [Hadr_endpoint];
drop certificate aoag_certificate
drop Master key 
drop user aoag_user
drop login aoag_login;

CREATE LOGIN aoag_login WITH PASSWORD = 'XXXXXX1!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE USER aoag_user FOR LOGIN aoag_login;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXXX1!'

CREATE CERTIFICATE aoag_certificate WITH SUBJECT = 'aoag_certificate';

BACKUP CERTIFICATE aoag_certificate
TO FILE = '/var/opt/mssql/shared/aoag_certificate.cert'
WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/shared/aoag_certificate.key',
        ENCRYPTION BY PASSWORD = 'XXXXXX1!'
)

CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL
)
FOR DATA_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = CERTIFICATE aoag_certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES
)

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

CREATE AVAILABILITY GROUP [AG1]
WITH (
    CLUSTER_TYPE = NONE
)
FOR REPLICA ON
N'db1' WITH
(
    ENDPOINT_URL = N'tcp://db1:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'db2' WITH
(
    ENDPOINT_URL = N'tcp://db2:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'db3' WITH
(
    ENDPOINT_URL = N'tcp://db3:5502',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);

USE [master]
GO

CREATE DATABASE Sales
GO
USE [SALES]
GO
CREATE TABLE CUSTOMER([CustomerID] [int] NOT NULL, [SalesAmount] [decimal] NOT NULL)
INSERT INTO CUSTOMER (CustomerID, SalesAmount) VALUES (1,100),(2,200),(3,300)

USE [master]
GO