How do I configure AlwaysOn using certificates in SQL Server 2016 Enterprise edition.
---------------------------------------------------------------
I have 3 nodes
1.Win2k12-1
2.Win2k12-1
3.Win2k12-1
These 3 nodes are in Windows Server 2012 R2 Cluster and Each Node has default SQL Server Instance Installed locally.
1.Win2k12-1
2.Win2k12-1
3.Win2k12-1
SQL Server service account of each node is running under local system Account and customer wants to configure AlwaysOn between these 3 replicas, but the condition is MS SQL Server instance running under the local system account.
Hence we have to create 3 endpoints on each node with AUTHENTICATION = CERTIFICATE.
Hence Start the Work Now.
-------------------------------------
Connect First Replica
--Win2k12-1
select @@SERVERNAME
GO
output -- -WIN2K12-1
Use master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@123'
GO
Use master
go
CREATE CERTIFICATE [AG_CERT_Win2k12-1_Default]
WITH SUBJECT = 'AlwaysOn certificate',
EXPIRY_DATE = '20201031'
go
BACKUP CERTIFICATE [AG_CERT_Win2k12-1_Default]
TO FILE = 'c:\Xfer\AG_CERT_Win2k12-1_Default.cer'
GO
Use master
GO
CREATE ENDPOINT [Win2k12-1]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [AG_CERT_Win2k12-1_Default]
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
-->Verify ENDPOINT [WIN2K12-1] has been created on SQl Server Instance Win2k12-1
We have to repeat the same on another Node.
Connect 2nd Replica using SSMS
---------------------------------------------
Use master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@123'
GO
Use master
CREATE CERTIFICATE [AG_CERT_WIN2K12-2_Default]
WITH SUBJECT = 'AlwaysOn certificate',
EXPIRY_DATE = '20201031'
go
BACKUP CERTIFICATE [AG_CERT_WIN2K12-2_Default]
TO FILE = 'c:\Xfer\AG_CERT_WIN2K12-2_Default.cer'
GO
Use master
GO
CREATE ENDPOINT [WIN2K12-2]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [AG_CERT_WIN2K12-2_Default]
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
-->Verify ENDPOINT [WIN2K12-2] has been created on SQl Server Instance Win2k12-2
Connect 3rd replica using SSMS
Use master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@123'
GO
Use master
CREATE CERTIFICATE [AG_CERT_WIN2K12-3_Default]
WITH SUBJECT = 'AlwaysOn certificate',
EXPIRY_DATE = '20201031'
go
BACKUP CERTIFICATE [AG_CERT_WIN2K12-3_Default]
TO FILE = 'c:\Xfer\AG_CERT_WIN2K12-3_Default.cer'
GO
Use master
GO
CREATE ENDPOINT [WIN2K12-3]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [AG_CERT_WIN2K12-3_Default]
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
-->Verify ENDPOINT [WIN2K12-3] has been created on SQl Server Instance Win2k12-3
Copy Certificate Files among replicas...
Copy Certificate File which created on Win2k12-1 to Win2k12-2 & Win2k12-3
Copy Certificate File which created on Win2k12-2 to Win2k12-1 & Win2k12-3
Copy Certificate File which created on Win2k12-3 to Win2k12-1 & Win2k12-2
Means each server must have all 3 certificate files of its adjuscent replicas..
Connect Win2k12-1
Create logins and create user for the same and grant on endpoint to user..
--Create login AG_NODE_Win2k12-1_Default on Win2k12-1
USE [master]
GO
CREATE LOGIN [AG_NODE_Win2k12-1_Default] WITH PASSWORD=N'password@123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
--Create user AG_NODE_Win2k12-1_Default on Win2k12-1
USE [master]
GO
CREATE USER [AG_NODE_Win2k12-1_Default] FOR LOGIN [AG_NODE_Win2k12-1_Default] WITH DEFAULT_SCHEMA=[dbo]
GO
-- Grant connect on endpoint Win2k12-1 to user AG_NODE_Win2k12-1_Default
GRANT CONNECT ON ENDPOINT::[Win2k12-1] TO [AG_NODE_Win2k12-1_Default]
GO
USE [master]
GO
CREATE LOGIN [AG_NODE_WIN2K12-2_Default] WITH PASSWORD=N'password@123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
--Create user AG_NODE_WIN2K12-2_Default on WIN2K12-2
USE [master]
GO
CREATE USER [AG_NODE_WIN2K12-2_Default] FOR LOGIN [AG_NODE_WIN2K12-2_Default] WITH DEFAULT_SCHEMA=[dbo]
GO
-- Grant connect on endpoint Win2k12-2 to user AG_NODE_Win2k12-2_Default
GRANT CONNECT ON ENDPOINT::[WIN2K12-2] TO [AG_NODE_WIN2K12-2_Default]
GO
Connect Win2k12-3
USE [master]
GO
CREATE LOGIN [AG_NODE_WIN2K12-3_Default] WITH PASSWORD=N'password@123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
--Create user AG_NODE_WIN2K12-3_Default on WIN2K12-3
USE [master]
GO
CREATE USER [AG_NODE_WIN2K12-3_Default] FOR LOGIN [AG_NODE_WIN2K12-3_Default] WITH DEFAULT_SCHEMA=[dbo]
GO
-- Grant connect on endpoint Win2k12-3 to user AG_NODE_Win2k12-3_Default
GRANT CONNECT ON ENDPOINT::[WIN2K12-3] TO [AG_NODE_WIN2K12-3_Default]
GO
Connect First Replica
-- Connect Win2k12-1
In win2k12-1 restore certificates of win2k12-2 and win2k12-3 with login of win2k12-1 which we created earlier..
Associate the certificate AG_CERT_Win2k12-2_Default.cer of machine Win2k12-2 with the user AG_NODE_Win2k12-1_Default
Use master
go
CREATE CERTIFICATE [AG_CERT_Win2k12-2_Default] AUTHORIZATION [AG_NODE_Win2k12-1_Default]
FROM FILE ='c:\Xfer\AG_CERT_Win2k12-2_Default.cer'
GO
Associate the certificate AG_CERT_Win2k12-3_Default.cer of machine Win2k12-3 with the user IL1DBTS-US-AP07_login
Use master
go
CREATE CERTIFICATE [AG_CERT_Win2k12-3_Default] AUTHORIZATION [AG_NODE_Win2k12-1_Default]
FROM FILE ='c:\Xfer\AG_CERT_Win2k12-3_Default.cer'
GO
Repeat the same for other 2 replicas
-------------------------------------------
--on Win2k12-2
On Win2k12-2 restore certificates of Win2k12-1 and Win2k12-3 with login of Win2k12-2
Use master
go
CREATE CERTIFICATE [AG_CERT_Win2k12-1_Default] AUTHORIZATION [AG_NODE_Win2k12-2_Default]
FROM FILE ='c:\Xfer\AG_CERT_Win2k12-1_Default.cer'
GO
Use master
go
CREATE CERTIFICATE [AG_CERT_Win2k12-3_Default] AUTHORIZATION [AG_NODE_Win2k12-2_Default]
FROM FILE ='c:\Xfer\AG_CERT_Win2k12-3_Default.cer'
GO
--on Win2k12-3
on Win2k12-3 Restore\Associate certificates of Win2k12-1 and Win2k12-2 with login of Win2k12-3
Use master
go
CREATE CERTIFICATE [AG_CERT_Win2k12-1_Default] AUTHORIZATION [AG_NODE_Win2k12-3_Default]
FROM FILE ='c:\Xfer\AG_CERT_Win2k12-1_Default.cer'
GO
Use master
go
CREATE CERTIFICATE [AG_CERT_Win2k12-2_Default] AUTHORIZATION [AG_NODE_Win2k12-3_Default]
FROM FILE ='c:\Xfer\AG_CERT_Win2k12-2_Default.cer'
GO
Now try to telnet ports among each other machine on port 5022..
Endpoint port will get telnet and you should be able to configure AlwaysOn among these nodes.
Now take full Backup of databases from primary replica and restore to all secondary replicas in norecvery mode.
Take log Backup of databases from primary replica and restore to all secondary replicas in norecvery mode.
Script to configure AlwaysOn..
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect WIN2K12-1
IF (SELECT state FROM sys.endpoints WHERE name = N'Win2k12-1') <> 0
BEGIN
ALTER ENDPOINT [Win2k12-1] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Win2k12-1] TO [NT Service\MSSQLSERVER]
GO
:Connect WIN2K12-1
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect WIN2K12-2,1433
IF (SELECT state FROM sys.endpoints WHERE name = N'WIN2K12-2') <> 0
BEGIN
ALTER ENDPOINT [WIN2K12-2] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[WIN2K12-2] TO [NT Service\MSSQLSERVER]
GO
:Connect WIN2K12-2,1433
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect WIN2K12-3,1433
IF (SELECT state FROM sys.endpoints WHERE name = N'WIN2K12-3') <> 0
BEGIN
ALTER ENDPOINT [WIN2K12-3] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[WIN2K12-3] TO [NT Service\MSSQLSERVER]
GO
:Connect WIN2K12-3,1433
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect WIN2K12-1
USE [master]
GO
CREATE AVAILABILITY GROUP [Win2k12AG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = ON,
DTC_SUPPORT = NONE)
FOR DATABASE [AdventureWorks2014]
REPLICA ON N'WIN2K12-1' WITH (ENDPOINT_URL = N'TCP://Win2k12-1.Adven.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'WIN2K12-2' WITH (ENDPOINT_URL = N'TCP://Win2k12-2.Adven.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'WIN2K12-3' WITH (ENDPOINT_URL = N'TCP://Win2k12-3.Adven.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO
:Connect WIN2K12-2,1433
ALTER AVAILABILITY GROUP [Win2k12AG] JOIN;
GO
:Connect WIN2K12-3,1433
ALTER AVAILABILITY GROUP [Win2k12AG] JOIN;
GO
:Connect WIN2K12-2,1433
-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes
if (serverproperty('IsHadrEnabled') = 1)
and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'Win2k12AG'
select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
while @conn <> 1 and @count > 0
begin
set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
if @conn = 1
begin
-- exit loop when the replica is connected, or if the query cannot find the replica status
break
end
waitfor delay '00:00:10'
set @count = @count - 1
end
end
end try
begin catch
-- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [AdventureWorks2014] SET HADR AVAILABILITY GROUP = [Win2k12AG];
GO
:Connect WIN2K12-3,1433
-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes
if (serverproperty('IsHadrEnabled') = 1)
and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'Win2k12AG'
select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
while @conn <> 1 and @count > 0
begin
set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
if @conn = 1
begin
-- exit loop when the replica is connected, or if the query cannot find the replica status
break
end
waitfor delay '00:00:10'
set @count = @count - 1
end
end
end try
begin catch
-- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [AdventureWorks2014] SET HADR AVAILABILITY GROUP = [Win2k12AG];
GO
GO
--Configure Listener
From Primary replica
--------------------------
USE [master]
GO
ALTER AVAILABILITY GROUP [Win2k12AG]
ADD LISTENER N'Win2k12AGListen' (
WITH IP
((N'192.168.1.105', N'255.255.255.0')
)
, PORT=1433);
GO
AlwaysOn Configuration completed successfully...
Thanks for Reading...
