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

 

  • SSMS에서 AlwaysOn 고가용성 구성화면

  • 장애 조치(Failover)의 역할 소유자 노드

  • 장애 조치(Failover)의 노드의 작동상태

 

가용성 그룹 DB Restore

  • DB복구를 위해 가용성 그룹에서 데이터베이스 제거를 선택합니다.

  • 확인을 눌러 제거완료

  • 데이터베이스 속성에서 ‘Sngle_user’로 엑세스 제한 변경

  • 데이터베이스 복원

  • 복원 옵션 기존 데이터베이스 덮어쓰기 선택후 확인하여 복원 완료

  • 복원 완료후 가용성 그룹에 데이터 베이스 추가를위해 데이터베이스 추가 선택

  • 다음선택

  • 데이터베이스 선택

  • 기존 보조 복제본에 연결. 연결선택후 인증

  • 데이터 동기화 기본설정에서 초기 데이터 동기화 건너뛰기 선택

  • 유효성 검사 다음선택

  • 요약

  • 완료

  • Node1서버에서 데이터베이스 풀백업 및 로그백업 수행후 Node2로 복사

 

BACKUP DATABASE TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.bak'

GO

BACKUP LOG TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.trn'

GO

 

  • Node2서버에서 데이터베이스 풀백업 및 로그백업본을 복원

 

RESTORE DATABASE TestDatabase1 FROM DISK = 'c:\temp\TestDatabase1.bak' WITH NORECOVERY

GO 

RESTORE LOG TestDatabase1 FROM DISK = 'c:\temp\TestDatabase1.trn' WITH NORECOVERY

GO

 

  • Node2 서버에서 데이터베이스 가용성 그룹에 조인

 

ALTER DATABASE TestDatabase1 SET HADR AVAILABILITY GROUP = TestAG

GO

 

가용성 그룹 작동 확인 및 Failover Test

  • 가용성 그룹 TestAG 우클릭후 대시보드 표시선택

  • 가용성 그룹 정상작동 상태 확인

  • Failover Test를 위해 가용성 그룹 우클하여 장애 조치 선택

  • 장애조치시 실시간마이그레이션 적용되어 서비스 끊김에 체감은 없으나 외부 Ping Test시 1칸 빠짐

 

 

Posted by 시스템매니아
,