About Me

My photo
I am MCSE in Data Management and Analytics with specialization in MS SQL Server and MCP in Azure. I have over 13+ years of experience in IT industry with expertise in data management, Azure Cloud, Data-Canter Migration, Infrastructure Architecture planning and Virtualization and automation. Contact me if you are looking for any sort of guidance in getting your Infrastructure provisioning automated through Terraform. I sometime write for a place to store my own experiences for future search and read by own blog but can hopefully help others along the way. Thanks.

How do i configure AlwaysOn using certificates

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


Connect Win2k12-2

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...