독까의 이야기

AlwaysOn 가용성 그룹 구축 # 1 : Windows Server 2016 + MS SQL 2016 / None AD

Database/MS-SQL2020. 7. 3. 16:57

예전에 AD 로 구축 된 서버군에서 SQL 장애 조치 테스트를 진행 했었다. 

이번에는 AD 가 구축되지 않은, 윈도우와 SQL 만 설치 된 서버들로 DB 장애 조치 테스트를 진행한다.

Windows Server 2016 + MS SQL Server 2016 이후 부터는 AD 없이 클러스터 구성 가능하다. 

따라서 아래의 환경으로 테스트를 진행한다. 

- OS : Windows Server 2016

- DBMS : MS SQL Server 2016 SP2

- 테스트 서버 : AO_TEST_01 (192.168.1.121) / AO_TEST02 (192.168.1.122) 

- 클러스터 IP : 192.168.1.123 

- 방화벽 정책 추가 : In/Outbound : TCP 135, 445, 3343 / UDP 3343

- 공인 아이피로 구성할 때에는 각 서버의 C:\Windows\System32\drivers\etc\hosts 파일의 내용을 필히 아래와 같이 수정

192.168.1.121 AO-TEST-01

192.168.1.122 AO-TEST-02

192.168.1.123 AOT

 

 

# 1 

신규 설치 된 테스트 서버 두 대 접속 후, 역할 및 기능 추가 마법사에서 "장애 조치 클러스터링" 설치한다. 

그냥 Default 로 설치하면 된다. 

 

 

# 2

MS SQL Server 2016 을 설치한다. 이것도 Default 로 설치하면 된다. 

두 서버의 DB 데이타 저장 경로가 일치해야 하므로 아래와 같이 설정한다. 

 

2016 이후부터는 설치 패키지에 SSMS 가 포함되어 있지 않으므로, 링크에서 다운로드 한다.

https://docs.microsoft.com/ko-kr/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

 

SQL 서비스 로그온 계정은 반드시 사용자 계정으로 변경한다. 

AO_TEST_01 과 02 모두 동일한 계정 정보를 갖고 있어야 한다. 

 

 

 

# 3

클러스터 생성 단계로 넘어간다. 

관리도구에서 "장애 조치 클러스터 관리자" 를 실행 한다. 

AO_TEST_01 이나 AO_TEST_02 아무데서나 생성해도 된다. 

테스트 서버 상호간에 완전 통신이 가능하도록 방화벽 인바운드 정책 추가해야 한다. 

 

클러스터에 포함 될 서버의 아이피를 차례대로 입력한다. 

아이피 추가하면 컴퓨터명으로 서버 리스트가 호출된다. 

 

유효성 검사 경고 단계에서는 "아니요" 선택해서 넘어간다. 

 

클러스터 이름은 "AOT" 로 생성한다. 뭐 아무거나 입력해도 상관없다. 본인이 알아볼 수 있으면 된다. 

아이피에는 192.168.1.123 을 입력한다. 해당 아이피가 클러스터 IP 가 된다. 

 

 

Default 로 쭉쭉 넘어가면 클러스터 만들기 완료 된다. 

 

AO_TEST_02 에서는 장애 조치 클러스터 관리자 실행 후, 클러스터에 연결을 선택한다.

AOT 의 IP 인 192.168.1.123 을 입력해서 클러스터 정보를 확인한다. 

 

 

 

# 4 

SQL 서버의 AlwaysOn 기능을 설정한다. AO_TEST_01 과 AO_TEST_02 에서 동일한 작업을 진행한다. 

SQL Server 2016 구성 관리자 실행 후, SQL Server 서비스 트리 - SQL Server 속성을 확장한다. 

AlwaysOn 고가용성 탭 선택 후, AlwaysOn 가용성 그룹 사용을 체크한다. 해당 작업은 SQL 서비스 재시작이 필요하다. 

 

AO_TEST_01과 AO_TEST_02에서 SSMS 실행한다. 

AO_TEST_01 에서 동기화를 진행 할 테스트 DB 와 테이블을 생성한다. 

DB명 : gunnm

테이블명 : AO_Table_1

 

AO_TEST_01 에서 "새 가용성 그룹" 을 생성한다.

Always On 고가용성 - 가용성 그룹 - 우클릭 - 새 가용성 그룹 마법사 실행 

가용성 그룹 이름에는 "AO_DB" 를 입력한다. 이것도 본인이 식별 가능하도록 생성하면 된다. 

"데이터베이스 수준 상태 검색" 도 추가 체크해 주면 장애 조치 기능이 향상된다.

기존에는 가용성 그룹에 포함된 데이터베이스 중 하나가 오류 상태가 되어도 장애 조치가 실행되지 않았는데, SQL 2016 이상에서 "데이터베이스 수준 상태 검색" 을 체크하면 데이터베이스 수준의 장애에 대해서도 장애 조치가 진행된다.

 

가용성 그룹에 적용 될 DB 를 선택한다. 

전제 조건으로 해당 DB 백업이 1회 필요하다. 이걸 하지 않으면 다음 단계로 넘어가지 않는다. 

 

gunnm DB의 백업을 1회 진행하고 새로 고침 한다. 이제는 다음 단계로 넘어 갈 수 있다. 

 

복제본 지정 단계로 진입되었으면, "복제본 추가" 를 선택해서 AO_TEST_02 를 추가 한다. 

 

정상 진행시, 주 와 보조의 역할이 정해진다. 

 

엔드포인트 탭을 보면, 미러링 구성때와 동일하게 기본 포트인 5022 를 사용하는 것을 확인 할 수 있다. 

해당 포트도 사용자가 재구성 가능하지만, 지금은 테스트니까 기본으로 간다.

 

데이터 동기화 기본 설정은 "자동 시딩" 으로 체크한다. 

보조 복제본에 자동으로 DB 를 생성하기 때문에 동기화 대상의 데이타 및 로그 파일 경로가 동일해야 함을 알 수 있다. 

 

가용성 그룹 유효성 검사가 정상으로 체크되면 다음 단계로 진행 한다.

 

정상적으로 작업 완료 되면, 각 서버에서 아래와 같이 확인 할 수 있다. 

* AO_TEST_01 

 

* AO_TEST_02

 

 

# 5

이제 기본 설정 완료 했으니깐, 장애 조치 (Failover) 테스트를 진행한다.

 

장애 조치 클러스터 관리자 실행 후, 역할을 선택하면 "AO_DB" 가 추가 된 것을 확인 할 수 있다. 

현 상태에서는, AO_TEST_01 이 소유권을 갖고 있다. 

 

AO_TEST_01 의 IP 조회시, 아래와 같이 AO_DB 클러스터 IP 인 192.168.1.123 도 소유하고 있는 것을 볼 수 있다.

 

AO_TEST_01 의 SSMS 에서 테이블 추가를 해본다. 

추가 테이블명 : AO_Table_2

 

장애 조치를 해봐야 하는데, 우선은 가장 나쁜 상황을 만들기 위해 AO_TEST_01 서버의 전원을 OFF 한다.

AO_TEST_02 의 장애 조치 클러스터 관리자에서 역할 부분 확인시, 소유자가 AO_TEST_02 로 변경 되었음을 알 수 있다. 

 

AO_TEST_01 서버의 현재 상태는 "작동 중지" 이다.

 

 

AO_DB 클러스터 IP 인 192.168.1.123 도 AO_TEST_02 가 소유하고 있음을 알 수 있다.

 

이제 AO_TEST_02 의 SSMS 에 진입해서, DB 와 테이블, 가용성 그룹을 확인해 본다. 

 

AO_TEST_01 이 주 였을 때는, AO_TEST_02 에서는 gunnm DB 에 대해서 어떠한 작업도 할 수가 없었으나, 

주 와 보조의 역할이 변경 된 지금은 모든 작업이 가능하다.

 

AO_TEST_02 에서 테이블을 생성한다.

추가 테이블명 : AO_Table_3

 

AO_TEST_01 의 전원을 올리고 클러스터 및 DB 상태를 확인한다.

* 클러스터 상태

 

 

* DB 상태

 

 

# 6

장애 발생시 주 와 보조의 역할 변경을 확인했으니깐, 다시 역할을 복원하는 작업을 진행한다.

 

AO_TEST_02 의 SSMS 에서 

Alwayson 고가용성 - 가용성 그룹 - AO_DB - 우클릭 - 장애 조치 선택 한다. 

이 작업은 AO_TEST_01 에서 진행해도 무방하다. 효과는 같다.

 

프로그램에서 기본값으로 AO_TEST_01 을 새로운 주 복제본으로 선택하고 있다.

 

다음 단계에서 AO_TEST_01 서버 인스턴스에 연결한다.

 

작업에 대한 내용은 아래와 같이 요약 된다. 주 와 보조의 역할을 변경하고, 작업 중 데이터 손실이 없다는 뭐 그런 내용이다. 

 

 

작업 결과는 아래와 같다.

 

각 서버의 SSMS 트리 확인시, 처음 구성 되었던 상태와 같이 주 와 보조 의 역할이 변경되어 있음을 알 수 있다.

 

클러스터 관리자에서도 소유자 변경을 확인 할 수 있다. 

 

 

AO_DB 의 소유자는 변경 됐는데, 클러스터 IP 인 192.168.1.123 은 여전히 AO_TEST_02 가 소유하고 있다. 

 

이를 통해서 확인 가능한 것은, 클러스터 IP 는 AD 구성의 DC 역할을 수행하는 것으로 볼 수 있다. 

여기까지는 예전 SQL 미러링의 자동 장애 조치의 방식과 동일하다.

장애 발생시 주 와 보조 간 DB 데이타 동기화가 실시간으로 진행 되기 때문에 자료의 손실은 발생하지 않지만,

웹 서버의 DB 커넥션 부분을 수정하던가 주 와 보조 서버의 아이피를 변경해야 하는 불편함이 발생한다. 

 

그래서 DB 커넥션을 위한 VIP 설정이 필요하다. 

다음 글에서 가용성 그룹 수신기를 이용한 리스너 구성을 진행한다.

 

 

저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

MSSQL 업그레이드  (1) 2022.11.04
AlwaysOn 가용성 그룹 구축 # 2 : Windows Server 2016 + MS SQL 2016 / None AD  (1) 2020.07.07
SQL DB 암호화 / TDE(Transparent Data Encryption)  (0) 2018.12.07
MS-SQL CPU 사용량이 높은 쿼리문 확인  (0) 2018.06.22
현재 사용 중인 DB 중 제한 없는 DB 확인 쿼리  (0) 2016.07.25

SQL DB 암호화 / TDE(Transparent Data Encryption)

Database/MS-SQL2018. 12. 7. 15:03

MS-SQL 2008 ENT 이상 버전에서 지원되는 TDE 기능에 대한 테스트를 진행 한다. 
STD 에서도 복원은 가능하나, DB 는 사용 할 수 없다. 

OS : Windows 2016
DBMS : MS-SQL 2014 ENT

1. DB 생성
DB 명 : gunnm 
USE master;
CREATE DATABASE [gunnm]
ON
( NAME = gunnm, FILENAME = 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm.mdf')
LOG ON
( NAME = gunnm_log, FILENAME = 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm_log.ldf')
GO




2. 암호화 진행

마스터 키 생성
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord!2#'
GO

마스터 키로 보호 된 인증서 생성
CREATE CERTIFICATE gunnm_cert WITH SUBJECT = 'gunnm_cert'
GO

마스터 키 백업
USE master
BACKUP SERVICE MASTER KEY TO FILE = 'H:\service_master.key' ENCRYPTION BY PASSWORD = 'Service_PW@1119'
GO
BACKUP MASTER KEY TO FILE = 'H:\db_master.key' ENCRYPTION BY PASSWORD = 'DB_PW#1119'
GO

DB 암호화 키 생성
USE gunnm
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE gunnm_cert 
GO

실행 메시지 
경고: 데이터베이스 암호화 키를 암호화하는 데 사용된 인증서가 백업되지 않았습니다. 인증서와 인증서에 연결된 개인 키를 즉시 백업해야 합니다. 인증서를 사용할 수 없게 되거나 다른 서버에서 데이터베이스를 복원하거나 연결해야 할 경우 인증서와 개인 키의 백업본이 있어야 합니다. 그렇지 않으면 데이터베이스를 열 수 없습니다.

DB 암호화 적용
USE gunnm
GO
ALTER DATABASE gunnm
SET ENCRYPTION ON
GO

DB 암호화 키 백업
USE master
BACKUP CERTIFICATE gunnm_cert TO FILE = 'H:\gunnm_cert.cer' WITH PRIVATE KEY ( FILE = 'H:\gunnm_cert.pvk' , ENCRYPTION BY PASSWORD = 'gunnm_PW$1119' )
GO

DB 백업
BACKUP DATABASE [gunnm] TO  DISK = 'H:\gunnm.bak';
GO



3. 대상 서버에서 DB 복원 진행

원본 서버에서 생성 된 DB 인증서 파일 (.cer / .pvk) 및 DB 백업 파일 (.bak) 을 복사한다. 

마스터 키 생성 : 원본 서버와 동일한 패스워드를 입력할 필요는 없다. 
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord1@3'
GO
CREATE CERTIFICATE gunnm_cert WITH SUBJECT = 'DB_Enc'
GO

원본 서버 인증서 파일을 통한 인증서 생성 및 DB 복원
CREATE CERTIFICATE gunnm_cert
  FROM FILE = 'H:\gunnm_cert.cer'
  WITH PRIVATE KEY ( 
    FILE = 'H:\gunnm_cert.pvk',
 DECRYPTION BY PASSWORD = 'gunnm_PW$1119'
  );

실행 메시지 : 
메시지 15232, 수준 16, 상태 1, 줄 2
이름이 'gunnm_cert'인 인증서가 이미 있거나 이 인증서가 데이터베이스에 이미 추가되었습니다.
테스트이므로 서버간 이동을 하지 않고, 동일 서버에서 진행되어 발생 된 오류로 추정

-- 등록 인증서 제거
USE master; 
DROP CERTIFICATE gunnm_cert;


CREATE CERTIFICATE gunnm_cert
  FROM FILE = 'H:\gunnm_cert.cer'
  WITH PRIVATE KEY ( 
    FILE = 'H:\gunnm_cert.pvk',
 DECRYPTION BY PASSWORD = 'gunnm_PW$1119'
  );

RESTORE DATABASE [gunnm]
  FROM DISK = 'H:\gunnm.bak'
  WITH MOVE 'gunnm' TO 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm.mdf',
       MOVE 'gunnm_log' TO 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm_log.ldf';
 



4. TDE 모니터링 스크립트
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0'  THEN  'No database encryption key present, no encryption'
         WHEN '1'  THEN  'Unencrypted'
         WHEN '2'  THEN  'Encryption in progress'
         WHEN '3'  THEN  'Encrypted'
         WHEN '4'  THEN  'Key change in progress'
         WHEN '5'  THEN  'Decryption in progress'
         WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
         ELSE 'No Status'
         END,
percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys 


USE master
GO
SELECT * FROM sys.certificates

-- encryption_state = 5 is encrypted
SELECT * FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3; 



5. 암호화 제거

DB 암호화 비활성화
USE gunnm
ALTER DATABASE gunnm SET ENCRYPTION OFF
GO

DB 암호화 키 제거
USE gunnm
GO
DROP DATABASE ENCRYPTION KEY  

마스터 키 및 인증서 제거
-- 마스터 키 제거
USE master;
drop master key;
GO

-- 등록 인증서 제거
USE master; 
DROP CERTIFICATE gunnm_cert;
GO  



저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

AlwaysOn 가용성 그룹 구축 # 2 : Windows Server 2016 + MS SQL 2016 / None AD  (1) 2020.07.07
AlwaysOn 가용성 그룹 구축 # 1 : Windows Server 2016 + MS SQL 2016 / None AD  (6) 2020.07.03
MS-SQL CPU 사용량이 높은 쿼리문 확인  (0) 2018.06.22
현재 사용 중인 DB 중 제한 없는 DB 확인 쿼리  (0) 2016.07.25
DDL, DML, DCL  (0) 2016.07.25

MS-SQL CPU 사용량이 높은 쿼리문 확인

Database/MS-SQL2018. 6. 22. 14:37

MS-SQL DB 를 이용하는 서버의 CPU 사용량이 100% 가 출력되어 점검을 진행했다.


윈도우에서 성능 모니터에 카운터 추가를 해서 했는데 손이 너무 많이 간다. 


성능 모니터에서 카운터 추가

성능 개체 : Thread

카운터 : % Processor Time / ID Thread 

인스턴스 : sqlservr 리스트 전체 선택

CPU 사용량이 높은 SQL 스레드 ID 확인 : 4280

스레드 ID 로 spid 확인 

SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=4280

SPID : 240

spid 로 쿼리문 확인 

dbcc inputbuffer (240)


이렇게 했는데, 일을 두 번 할 필요는 없으니깐, SSMS 에서 한 번에 확인이 가능한 방법을 검색해서 정리해 보았다. 

SSMS 에 접속, tempdb 에서 새쿼리 실행 후 진행을 한다. 

그러면 tempdb 의 임시 테이블에 해당 테이블이 저장이 되는데, 다 확인하고 나면 맨 하단 drop 명령어로 테이블 제거를 하면 된다. 

use tempdb
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      *
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       Status = 'RUNNABLE'
-- Add any sorting of the results here :
ORDER BY    CPUTime ASC

CPU 사용량이 높은 spid 가 확인 됐으면, dbcc inputbuffer (SPID) 를 실행하여 쿼리문을 확인하면 된다. 

다 완료 되었으면 아래의 명령어를 통해 임시 테이블을 제거 한다. 

DROP TABLE #sp_who2


# 현재 실행 중인 쿼리문 확인하는 명령어

SELECT
   sqltext.TEXT,
   req.session_id,
   req.status,
   req.command,
   req.cpu_time,
   req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 


위에 생성 된 임시테이블의 select 구문과 현재 실행 중인 쿼리문을 조회하는 실행문을 동시에 실행해서 일치하는 spid 를 찾는 것도 좋은 방법일 것 같다. 



저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

AlwaysOn 가용성 그룹 구축 # 1 : Windows Server 2016 + MS SQL 2016 / None AD  (6) 2020.07.03
SQL DB 암호화 / TDE(Transparent Data Encryption)  (0) 2018.12.07
현재 사용 중인 DB 중 제한 없는 DB 확인 쿼리  (0) 2016.07.25
DDL, DML, DCL  (0) 2016.07.25
X 행에 데이터가 커밋되지 않았습니다.  (0) 2016.07.25

티스토리툴바