SQL2016 AlwaysOn Basic 구성 가이드(AD없음)

MS-SQL 2020. 4. 29. 16:18

SQL2016 AlwaysOn Basic 소개

SQL2016 이전 버전의 SQL가용성 그룹은 Enterprise Edition의 기능이었으며 Standard Edition 에서는 사용할 수 없었습니다. 하지만 SQL Server 2016의 Standard Edition에서는 Basic 가용성 그룹을 지원합니다.

Enterprise보다는 기능 제약이 있고 Active/Active 고가용성을 지원하지 않으나 공유 스토리지없이 MSCS와 유사한 Active/Stanby(자동FaileOver)의 SQL고가용성을 제공하며 AD없이 구축 가능 합니다.

하지만 1개의 데이터베이스만 고가용성 지원하는 문제점이 있습니다.

제약 사항은 다음과 같습니다.

2개의 Replica만 가능(Primary 1대, Secondary 1대)
보조 복제본에서 읽기 불가능
보조 복제본에서 백업 불가능
1개의 데이터베이스만 가능

다음과 같은 기능은 지원합니다.

Listener 지원
자동 Failover
동기/비동기 선택 가능

소규모로 사용하는 경우나, standard edition에서 1개의 데이터베이스 이중화 고려시 비용이 유리합니다.

Windows2016 서버 구성

Test서버 구성 현황(2Node로 구성)

항목	Node1	Node2
서버	VM	VM
OS	Windows2016 Standard Edition	Windows2016 Standard Edition
CPU	4vCore	4vCore
메모리	8GB	8GB
Nic	2Port	2Port
HDD	200GB(동적)	200GB(동적)
설치 역할 및 기능	.Net Framework 3.5 장애조치(Failover) 클러스터링 SQL2016 Standard Edition SP2	.Net Framework 3.5 장애조치(Failover) 클러스터링 SQL2016 Standard Edition SP2
IP	Public : 203.231.238.139 Private : 192.168.1.101	Public :203.231.238.140 Private : 192.168.1.102
Cluster ip	sqlbag : 192.168.1.110	sqlbag : 192.168.1.110

Node1, Node2 Windows2016 Standard Edition 설치

- 200GB C:드라이브 설정

- 서버구성후 Windows Update는 최신버전으로 적용(Test시는 미적용함)

네트워크 설정

- Publc IP설정(Node1,2)

- Private IP설정(Node1,2)

컴퓨터 이름 및 DNS 접미사 변경

- AD를 사용하지 않으므로 클러스터 구성을 위해 변경작업후 서버 재시작

Host파일 수정 Node1, Node2

Node1, Node2의 Powershell 관리자 권한 으로 실행후 아래 명령어 수행

new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1

이 정책을 설정하지 않으면 기본 제공 관리자 계정을 사용하여 클러스터를 만들려고 할 때 다음 오류가 표시됩니다.

Node1에서 클러스터 생성

new-cluster -name sqlbag –Node node1,node2 -StaticAddress 192.168.1.110 -NoStorage –AdministrativeAccessPoint DNS

Node1, Node2에서 SQL2016 Standard Edition 설치

- Node1의 인스턴스는 MSSQLSERVER è Node1 로 변경

- Node2의 인스턴스는 MSSQLSERVER è Node2 로 변경

- 설치도중 Data디렉토리는 두 서버모두 동일하게 설정

예) C:\Program Files\Microsoft SQL Server\MSSQL13.SQL\MSSQL\DATA\

Node1, Node2에서 관리도구설치(SQL Server Management Studio) 설치

Node1, Node2 SQL구성관리자 è SQL Server 서비스 è SQL Server(MSSQLSERVER) 속성 è AlwaysOn 고가용성에서 사용 체크

SQL2016서버 설정

- Node1의 SSMS에서 마스터키, 인증서, 끝점 생성 및 인증서 생성후 C:\Temp폴더에 백업

USE master
GO
-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd1!'
GO
-- Create a new certificate
CREATE CERTIFICATE SQLBAG_Certificate_Node1_Private
WITH SUBJECT = 'SQLBAG_Certificate_Private - Node 1',
START_DATE = '20180912'
GO
-- Backup the public key of the certificate to the filesystem
BACKUP CERTIFICATE SQLBAG_Certificate_Node1_Private
TO FILE = 'c:\temp\SQLBAG_Certificate_Node1_Public.cert'
GO
-- Create an endpoint for the Availability Group
CREATE ENDPOINT SQLBAG_Endpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_Node1_Private,
ROLE = ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

Node2의 SSMS에서 마스터키, 인증서, 끝점 생성 및 인증서 생성후 C:\Temp폴더에 백업

USE master
GO
-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd1!'
GO
-- Create a new certificate
CREATE CERTIFICATE SQLBAG_Certificate_Node2_Private
WITH SUBJECT = 'SQLBAG_Certificate_Private - Node 2',
START_DATE = '20180912'
GO
-- Backup the public key of the certificate to the filesystem
BACKUP CERTIFICATE SQLBAG_Certificate_Node2_Private
TO FILE = 'c:\temp\SQLBAG_Certificate_Node2_Public.cert'
GO
-- Create an endpoint for the Availability Group
CREATE ENDPOINT SQLBAG_Endpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_Node2_Private,
ROLE = ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

Node1의 SSMS에서 사용자 생성후 끝점 연결 설정

-- Create login for the other node
CREATE LOGIN Node2Login WITH PASSWORD = 'passw0rd1!'
GO
-- Create user for the login
CREATE USER Node2User FOR LOGIN Node2Login
GO
-- Import the public key portion of the certificate from the other node
CREATE CERTIFICATE SQLBAG_Certificate_Node2_Public
AUTHORIZATION Node2User
FROM FILE = 'c:\temp\SQLBAG_Certificate_Node2_Public.cert'
GO
-- Grant the CONNECT permission to the login
GRANT CONNECT ON ENDPOINT::SQLBAG_Endpoint TO Node2Login
GO

Node2의 SSMS에서 사용자 생성후 끝점 연결 설정

-- Create login for the other node
CREATE LOGIN Node1Login WITH PASSWORD = 'passw0rd1!'
GO
-- Create user for the login
CREATE USER Node1User FOR LOGIN Node1Login
GO
-- Import the public key portion of the certificate from the other node
CREATE CERTIFICATE SQLBAG_Certificate_Node1_Public
AUTHORIZATION Node1User
FROM FILE = 'c:\temp\SQLBAG_Certificate_Node1_Public.cert'
GO
-- Grant the CONNECT permission to the login
GRANT CONNECT ON ENDPOINT::SQLBAG_Endpoint TO Node1Login
GO

Node1의 SSMS에서 Database생성 및 백업

USE master
GO
-- Create a new database
CREATE DATABASE TestDatabase1
GO
-- Use the database
USE TestDatabase1
GO
-- Create a simple table
CREATE TABLE Foo
(
Bar INT NOT NULL
)
GO
-- Make a Full Backup of the database
BACKUP DATABASE TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.bak'
GO
USE master
GO
-- Create a new Availability Group with 2 replicas
CREATE AVAILABILITY GROUP TestAG
WITH
(
AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
BASIC,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE
)
FOR DATABASE [TestDatabase1]
REPLICA ON
'NODE1' WITH
(
ENDPOINT_URL = 'TCP://node1.test.com:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
SECONDARY_ROLE
(
ALLOW_CONNECTIONS = NO
)
),
'NODE2' WITH
(
ENDPOINT_URL = 'TCP://node2.test.com:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
SECONDARY_ROLE
(
ALLOW_CONNECTIONS = NO
)
)
GO

Node2의 SSMS에서 TestAG 가용성 그룹에 연결

ALTER AVAILABILITY GROUP [TestAG] JOIN
GO

Node1의 SSMS에서 trn백업

-- Make a TxLog Backup of the database
BACKUP LOG TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.trn'
GO

백업받은 .bak, .trn 파일 Node2로 이동하여 복원하고 해당 Database 가용성 그룹으로 이동

-- Restore the Full Backup with NORECOVEY
RESTORE DATABASE TestDatabase1 FROM DISK = 'c:\temp\TestDatabase1.bak' WITH NORECOVERY
GO
-- Restore the TxLog Backup with NORECOVERY
RESTORE LOG TestDatabase1 FROM DISK = 'c:\temp\TestDatabase1.trn' WITH NORECOVERY
GO
-- Move the database into the Availability Group
ALTER DATABASE TestDatabase1 SET HADR AVAILABILITY GROUP = TestAG
GO