CODE #1

Step-2
CREATE AVAILABILITY GROUP [AG_CLUSTERLESS]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE,
CLUSTER_TYPE = NONE,
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0)
FOR DATABASE [LabDatabase]
REPLICA ON N'SQL01\SQL01' WITH (ENDPOINT_URL = N'TCP://SQL01.IMATIC.local:5022', FAILOVER_MODE = MANUAL, 
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, 
SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)),
N'SQL02\SQL02' WITH (ENDPOINT_URL = N'TCP://SQL02.IMATIC.local:5022',
FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, 
SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY));

GO


Step-3
-- ====================================
-- Execute the following code on SQL01
-- ====================================
USE master
GO
 
-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd1!'
GO
 
-- Create a new certificate
CREATE CERTIFICATE SQL_Certificate_SQL01_Private
WITH SUBJECT = 'AG_CLUSTERLESS_Certificate_Private - SQL01',
START_DATE = '20160101'
GO
 
-- Backup the public key of the certificate to the filesystem
BACKUP CERTIFICATE SQLBAG_Certificate_Node1_Private
TO FILE = 'c:\temp\AG_CLUSTERLESS_Certificate_SQL01_Public.cert'
GO
 
-- Create an endpoint for the Availability Group
CREATE ENDPOINT AG_CLUSTERLESS_Endpoint
STATE = STARTED
AS TCP
(
    LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
    AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_SQL01_Private,
    ROLE = ALL, 
    ENCRYPTION = REQUIRED ALGORITHM AES
)
GO
 
-- ====================================
-- Execute the following code on SQL02
-- ====================================
USE master
GO
 
-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd1!'
GO
 
-- Create a new certificate
CREATE CERTIFICATE SQL_Certificate_SQL02_Private
WITH SUBJECT = 'AG_CLUSTERLESS_Certificate_Private - SQL02',
START_DATE = '20160101'
GO
 
-- Backup the public key of the certificate to the filesystem
BACKUP CERTIFICATE SQLBAG_Certificate_Node1_Private
TO FILE = 'c:\temp\AG_CLUSTERLESS_Certificate_SQL02_Public.cert'
GO
 
-- Create an endpoint for the Availability Group
CREATE ENDPOINT AG_CLUSTERLESS_Endpoint
STATE = STARTED
AS TCP
(
    LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
    AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_SQL02_Private,
    ROLE = ALL, 
    ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

Step-4
-- ====================================
-- Execute the following code on SQL01
-- ====================================
 
-- Create login for the SQL02 node
CREATE LOGIN sql02Login WITH PASSWORD = 'passw0d11!'
GO
 
-- Create user for the login
CREATE USER sql02User FOR LOGIN SQL02Login
GO
 
-- Import the public key portion of the certificate from SQL02
CREATE CERTIFICATE SQL_Certificate_SQL02_Private
AUTHORIZATION SQL02User
FROM FILE = 'c:\temp\AG_CLUSTERLESS_Certificate_SQL02_Public.cert'
GO
 
-- Grant the CONNECT permission to the login
GRANT CONNECT ON ENDPOINT:: AG_CLUSTERLESS_Endpoint TO SQL02Login
GO
 
-- ====================================
-- Execute the following code on SQL02
-- ====================================
 
-- Create login for the other node
CREATE LOGIN SQL01Login WITH PASSWORD = ' passw0d11!'
GO
 
-- Create user for the login
CREATE USER SQL01User FOR LOGIN Node1Login
GO
 
-- Import the public key portion of the certificate from the other node
CREATE CERTIFICATE SQLBAG_Certificate_SQL01_Public
AUTHORIZATION SQL01User
FROM FILE = 'c:\temp\ AG_CLUSTERLESS_Certificate_SQL01_Public.cert'
GO
 
-- Grant the CONNECT permission to the login
GRANT CONNECT ON ENDPOINT:: AG_CLUSTERLESS_Endpoint TO SQL01Login
GO
Step-5
ALTER AVAILABILITY GROUP [AG_CLUSTERLESS]
ADD LISTENER N'PrimaryListener' (
WITH IP
((N'192.168.1.7', N'255.255.255.0')
)
, PORT=1433);

GO

Step-6
USE [master]
GO
ALTER AVAILABILITY GROUP [AG_CLUSTERLESS]
MODIFY REPLICA ON N'SQL01\SQL01' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'sql02\sql02:1433'))
GO
ALTER AVAILABILITY GROUP [AG_CLUSTERLESS]
MODIFY REPLICA ON N'SQL01\SQL01' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL01\SQL01',N'SQL02\SQL02')))
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [AG_CLUSTERLESS]
MODIFY REPLICA ON N'SQL02\SQL02' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'sql01\sql01:1433'))
GO
ALTER AVAILABILITY GROUP [AG_CLUSTERLESS]
MODIFY REPLICA ON N'SQL02\SQL02' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL02\SQL02',N'SQL01\SQL01')))
GO

Step-7
SELECT 
    ar.replica_server_name, 
    adc.database_name, 
    ag.name AS ag_name, 
    drs.is_local, 
    drs.is_primary_replica, 
    drs.synchronization_state_desc, 
    drs.is_commit_participant, 
    drs.synchronization_health_desc, 
    drs.recovery_lsn, 
    drs.truncation_lsn, 
    drs.last_sent_lsn, 
    drs.last_sent_time, 
    drs.last_received_lsn, 
    drs.last_received_time, 
    drs.last_hardened_lsn, 
    drs.last_hardened_time, 
    drs.last_redone_lsn, 
    drs.last_redone_time, 
    drs.log_send_queue_size, 
    drs.log_send_rate, 
    drs.redo_queue_size, 
    drs.redo_rate, 
    drs.filestream_send_rate, 
    drs.end_of_log_lsn, 
    drs.last_commit_lsn, 
    drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc 
    ON drs.group_id = adc.group_id AND 
    drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar 
    ON drs.group_id = ar.group_id AND 
    drs.replica_id = ar.replica_id
ORDER BY 
    ag.name, 
    ar.replica_server_name, 
    adc.database_name;