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개의 데이터베이스만 고가용성 지원하는 문제점이 있습니다.
제약 사항은 다음과 같습니다.
다음과 같은 기능은 지원합니다.
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
- 200GB C:드라이브 설정
- 서버구성후 Windows Update는 최신버전으로 적용(Test시는 미적용함)
- Publc IP설정(Node1,2)
- Private IP설정(Node1,2)
- AD를 사용하지 않으므로 클러스터 구성을 위해 변경작업후 서버 재시작
new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
new-cluster -name sqlbag –Node node1,node2 -StaticAddress 192.168.1.110 -NoStorage –AdministrativeAccessPoint DNS
- Node1의 인스턴스는 MSSQLSERVER è Node1 로 변경
- Node2의 인스턴스는 MSSQLSERVER è Node2 로 변경
- 설치도중 Data디렉토리는 두 서버모두 동일하게 설정
예) C:\Program Files\Microsoft SQL Server\MSSQL13.SQL\MSSQL\DATA\
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
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
-- 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
-- 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
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
-- 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
가용성 그룹 DB Restore
BACKUP DATABASE TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.bak'
GO
BACKUP LOG TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.trn'
GO
RESTORE DATABASE TestDatabase1 FROM DISK = 'c:\temp\TestDatabase1.bak' WITH NORECOVERY
GO
RESTORE LOG TestDatabase1 FROM DISK = 'c:\temp\TestDatabase1.trn' WITH NORECOVERY
GO
ALTER DATABASE TestDatabase1 SET HADR AVAILABILITY GROUP = TestAG
GO
가용성 그룹 작동 확인 및 Failover Test