--sqlcmd -S db1 -U sa -P 'root@123'
--sqlcmd -S db2 -U sa -P 'root@123'
more /etc/hosts
192.168.1.10 db1
192.168.1.20 db2
192.168.1.30 db3
---==========================Before Change Edition========================
1. DBs Available
2. Always on Configured & Sync
3. Email Configured
---=======================Changing Edition ==========================
db1@db1:~$ sudo systemctl stop mssql-server
db1@db1:~$ sudo /opt/mssql/bin/mssql-conf setup
Choose the language for SQL Server:
(1) English
(2) Deutsch
(3) Español
(4) Français
(5) Italiano
(6) 日本語
(7) 한국어
(8) Português
(9) Руѝѝкий
(10) 中文 – 简体
(11) 中文 （繝体）
Enter Option 1-11: 1
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...
The licensing PID was successfully processed. The new edition is [Standard Edition].
---======================After Change Edition==========================
1. DBs Available
2. Always on Configured & Sync
3. Email Configured
--db1--db2--
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
systemctl restart mssql-server
ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
----db1----------
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'root@123';
Go
USE [master]
GO
CREATE CERTIFICATE alwayson1_cert
WITH SUBJECT = 'alwayson1_cert certificate for Availability Group'
GO
select name from sys.certificates;
GO
USE master
GO
CREATE ENDPOINT AGendpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5022, LISTENER_IP = ALL
) FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE alwayson1_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
);
GO
select name from sys.endpoints;
go
USE master
GO
BACKUP CERTIFICATE alwayson1_cert
TO FILE = '/var/opt/mssql/data/alwayson1_cert.cer';
GO
sudo scp /var/opt/mssql/data/alwayson1_cert.cer db2@db2:/home/db2
---db2------------
On Secondary(alwayson2)
Sudo su -
cp /home/db2/*.cer /var/opt/mssql/data/
chown mssql:mssql /var/opt/mssql/data/*_cert.cer
chmod 660 /var/opt/mssql/data/*_cert.cer
--Run below script on db2 server
--Create Master Key
Use master
GO
Create master key encryption by password ='root@123';
GO
--Create Certificate
Use master
GO
Create certificate alwayson2_cert with subject = 'alwayson2 certificate for Availability Group'
GO
--Create Endpoint
Create ENDPOINT AGendpoint
STATE=STARTED
AS TCP
(
LISTENER_PORT=5022, LISTENER_IP=ALL
)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE alwayson2_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE =ALL
);
GO
--Backup Certificate
Backup Certificate alwayson2_cert to file ='/var/opt/mssql/data/alwayson2_cert.cer';
GO
--Copy Certificate to other replicas
--Execute the below command on terminal
--On db2
sudo scp /var/opt/mssql/data/alwayson2_cert.* db1@db1:/home/db1/
--On db1
cp /home/db1/*.cer /var/opt/mssql/data/
chown mssql:mssql /var/opt/mssql/data/*_cert.cer
chmod 660 /var/opt/mssql/data/*_cert.cer
--------
-----db1----
--Create a login on the primary replica
USE [MASTER]
GO
CREATE LOGIN AVG_LOGIN WITH PASSWORD = 'root@123';
GO
--Create a user for the login
USE [MASTER]
GO
CREATE USER AVG_LOGIN FOR LOGIN AVG_LOGIN
GO
-Associate certificate from clusteralwayson2 with user
CREATE CERTIFICATE alwayson2_cert
AUTHORIZATION AVG_LOGIN
FROM FILE = '/var/opt/mssql/data/alwayson2_cert.cer'
GO
-Run this on primary replica clusteralwayson1--Grant the CONNECT permission on the login
USE [MASTER]
GO
GRANT CONNECT ON ENDPOINT::AGendpoint TO [AVG_LOGIN];
GO
--------db2------------
--Run this on the secondary replica alwayson2.
--Repeat the same process for steps #10 to #13 on the secondary replicas
USE master
GO
CREATE LOGIN AVG_login WITH PASSWORD = 'root@123';
GO
CREATE USER AVG_login FOR LOGIN AVG_login ;
GO
--Associate certificate from clusteralwayson1 with user
CREATE CERTIFICATE alwayson1_cert AUTHORIZATION AVG_login
FROM FILE = '/var/opt/mssql/data/alwayson1_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::AGendpoint TO AVG_login;
GO
---------
On primary:
--create database pt
go
-- Full DB Backup
BACKUP DATABASE [pt] TO DISK = '/var/opt/mssql/backup/pt.bak'
-- Tlog Backup
BACKUP LOG [pt] TO DISK = '/var/opt/mssql/backup/pt.trn'
On Secondary:
-- Full DB restore
RESTORE DATABASE [pt] FROM DISK = '/var/opt/mssql/data/pt.bak' WITH NORECOVERY
-- Tlog restore
RESTORE LOG [pt] FROM DISK = '/var/opt/mssql/data/pt.trn' WITH NORECOVERY
--------
--db2--db3
mkdir -p /home/db2/backup
chmod -R 777 /home/db2/backup
Copy the backup to secondaries
On primary
sudo scp /var/opt/mssql/backup/* db2@db2:/home/db2/backup
sudo scp /var/opt/mssql/backup/* db3@db3:/home/db3/backup
NOTE--scp will only work etc host entry of server
On Secondary
cp /home/db2/backup/pt.bak /var/opt/mssql/data/
cp /home/db2/backup/pt.trn /var/opt/mssql/data/
chown mssql:mssql /var/opt/mssql/data/pt.bak /var/opt/mssql/data/pt.trn
chmod 660 /var/opt/mssql/data/pt.bak /var/opt/mssql/data/pt.trn
-------------
On primary (db1)
CREATE AVAILABILITY GROUP [clusterlessAG]
WITH (DB_FAILOVER = ON, CLUSTER_TYPE = NONE)
FOR REPLICA ON
N'db1'
WITH (
ENDPOINT_URL = N'tcp://db1:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = Manual,
SEEDING_MODE = Manual),
N'db2'
WITH (
ENDPOINT_URL = N'tcp://db2:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = Manual,
SEEDING_MODE = Manual
);
Go
ALTER AVAILABILITY GROUP [clusterlessAG] GRANT CREATE ANY DATABASE;
-------------
Note – Run on all the Secondary replicas
ALTER AVAILABILITY GROUP [clusterlessAG] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [clusterlessAG] GRANT CREATE ANY DATABASE;
Note – Run on the primary
ALTER AVAILABILITY GROUP [clusterlessAG] ADD DATABASE [pt];
Run the below on secondary replica:
ALTER DATABASE [pt] SET HADR AVAILABILITY GROUP = [clusterlessAG];
-------
On each secondary SQL Server replica, run the following query to see if the anurag_test_db database was created and is synchronized:
SELECT name,create_date FROM sys.databases WHERE name = 'anurag_test_db';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;