[MS SQL] 비 도메인 환경의 SQL Server Mirror 구성 가이드

시스템 구성도

SQL Server 사전 준비 작업

1. 서버 구성 준비

- SQL Server #1 (Principal Server) - MS SQL Server 2019

- SQL Server #2 (Mirror Server) - MS SQL Server 2019

- SQL Server #3 (Mirror Monitor Server) - MS SQL Server 2019

2. 사용자 데이터베이스 준비

- Mirror 대상 사용자 데이터베이스

3. 계정 및 기타사항 준비

- 로그인 계정 및 비밀번호

- 미러링 엔드포인트 이름

- 마스터키 비밀번호

- 인증서 비밀번호

Hosts 파일 수정 (Principal / Mirror / Mirror Monitor SQL Server에서 모두 진행)

1. C:\Windows\System32\drivers\etc 경로로 진입하여 hosts 파일을 메모장으로 엽니다.

2. hosts 파일에 Principal / Mirror / Mirror Monitor의 Hostname과 IP정보를 입력합니다.

Mirror 대상 사용자 데이터베이스 백업 및 복원

1. Principal SQL Server에 로그인 합니다.

2. Mirror 대상 사용자 데이터베이스의 복구 모델을 '전체'로 설정합니다.

3. Mirror 대상 사용자 데이터베이스의 백업을 진행합니다.

4. 백업 파일을 Mirror Server에서 복원합니다.

Principal Server에서 복사해 온 전체 백업 및 트랜잭션 로그 백업 파일

**주의사항**

NORECOVERY 옵션을 사용하여 해당 사용자 데이터베이스가 '복원 중' 상태가 되도록 복원해야 합니다.

인증서 파일 생성하기

AD(Active Directory)Server의 Domain에 SQL Server들이 가입이 되어 있다면

AD Server를 통한 인증이 이뤄지기 때문에 인증서 설정이 필요하지 않습니다.

1. Principal Server에서 인증서 생성하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. 마스터 키 인증 비밀번호 생성
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Pa$$w0rd'
GO

--2. 생성된 마스터 키 확인
USE master
SELECT is_master_key_encrypted_by_server
FROM sys.databases
GO

--3. 인증서 생성, 인증서 이름과 만료일 설정
CREATE CERTIFICATE ALIENSQL01_Cert
WITH SUBJECT = 'ALIENSQL01_Cert',
EXPIRY_DATE = N'2100/12/31'
GO

--4. 생성된 인증서 확인
SELECT * FROM sys.symmetric_keys
GO

--5. 미러링 수행을 위한 환경 설정
CREATE ENDPOINT Hadr_endpoint
STATE = STARTED AS TCP(LISTENER_PORT=5022, LISTENER_IP=ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE ALIENSQL01_Cert, ENCRYPTION=REQUIRED, ROLE=ALL)
GO

--6. 인증서를 드라이브에 백업
BACKUP CERTIFICATE ALIENSQL01_Cert TO FILE = 'C:\SQL_Cert\ALIENSQL01_Cert.cer'
GO

2. Mirror Server에서 인증서 생성하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. 마스터 키 인증 비밀번호 생성
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Pa$$w0rd'
GO

--2. 생성된 마스터 키 확인
USE master
SELECT is_master_key_encrypted_by_server
FROM sys.databases
GO

--3. 인증서 생성, 인증서 이름과 만료일 설정
CREATE CERTIFICATE ALIENSQL02_Cert
WITH SUBJECT = 'ALIENSQL02_Cert',
EXPIRY_DATE = N'2100/12/31'
GO

--4. 생성된 인증서 확인
SELECT * FROM sys.symmetric_keys
GO

--5. 미러링 수행을 위한 환경 설정
CREATE ENDPOINT Hadr_endpoint
STATE = STARTED AS TCP(LISTENER_PORT=5022, LISTENER_IP=ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE ALIENSQL02_Cert, ENCRYPTION=REQUIRED, ROLE=ALL)
GO

--6. 인증서를 드라이브에 백업
BACKUP CERTIFICATE ALIENSQL02_Cert TO FILE = 'C:\SQL_Cert\ALIENSQL02_Cert.cer'
GO

3. Mirror Monitor Server에서 인증서 생성하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. 마스터 키 인증 비밀번호 생성
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Pa$$w0rd'
GO

--2. 생성된 마스터 키 확인
USE master
SELECT is_master_key_encrypted_by_server
FROM sys.databases
GO

--3. 인증서 생성, 인증서 이름과 만료일 설정
CREATE CERTIFICATE ALIENSQL03_Cert
WITH SUBJECT = 'ALIENSQL03_Cert',
EXPIRY_DATE = N'2100/12/31'
GO

--4. 생성된 인증서 확인
SELECT * FROM sys.symmetric_keys
GO

--5. 미러링 수행을 위한 환경 설정
CREATE ENDPOINT Hadr_endpoint
STATE = STARTED AS TCP(LISTENER_PORT=5022, LISTENER_IP=ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE ALIENSQL03_Cert, ENCRYPTION=REQUIRED, ROLE=ALL)
GO

--6. 인증서를 드라이브에 백업
BACKUP CERTIFICATE ALIENSQL03_Cert TO FILE = 'C:\SQL_Cert\ALIENSQL03_Cert.cer'
GO

4. Principal / Mirror / Mirror Monitor Server에 각각에서 생성한 인증서 가져오기

로그인 계정 생성 및 End Point 연결 권한 설정

1. Principal Server에서 로그인 계정 생성 및 End Point 연결 권한 설정하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. Alien 라는 로그인 계정 생성 및 비밀번호 설정
CREATE LOGIN Alien WITH PASSWORD = 'Pa$$w0rd'

--2. 사용자를 Alien 이라는 이름으로 생성
CREATE USER Alien FROM LOGIN Alien

--3. Alien 계정에 미러 서버의 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL02_Cert AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL02_Cert.cer'

--4. Alien 계정에 미러 모니터 서버의 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL03_Cert AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL03_Cert.cer'

--5. Alien 계정에 End Point 연결 허용
GRANT CONNECT ON ENDPOINT:: Hadr_endpoint TO Alien

계정의 속성에서 엔드포인트에 대한 연결 권한이 부여된 것을 확인할 수 있습니다.

2. Mirror Server에서 로그인 계정 생성 및 End Point 연결 권한 설정하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. Alien 라는 로그인 계정 생성 및 비밀번호 설정
CREATE LOGIN Alien WITH PASSWORD = 'Pa$$w0rd'

--2. 사용자를 Alien 이라는 이름으로 생성
CREATE USER Alien FROM LOGIN Alien

--3. Alien 계정에 주 서버의 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL01_Cert AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL01_Cert.cer'

--4. Alien 계정에 미러 모니터 서버의 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL03_Cert AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL03_Cert.cer'

--5. Alien 계정에 End Point 연결 허용
GRANT CONNECT ON ENDPOINT:: Hadr_endpoint TO Alien

3. Mirror Monitor Server에서 로그인 계정 생성 및 End Point 연결 권한 설정하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. Alien 라는 로그인 계정 생성 및 비밀번호 설정
CREATE LOGIN Alien WITH PASSWORD = 'Pa$$w0rd'

--2. 사용자를 Alien 이라는 이름으로 생성
CREATE USER Alien FROM LOGIN Alien

--3. Alien 계정에 주 서버의 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL01_Cert AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL01_Cert.cer'

--4. Alien 계정에 미러 서버의 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL02_Cert AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL02_Cert.cer'

--5. Alien 계정에 End Point 연결 허용
GRANT CONNECT ON ENDPOINT:: Hadr_endpoint TO Alien

로그인 계정에 권한 부여

1. Principal / Mirror / Morror Monitor Server에서 생성한 로그인 계정에 권한 부여하기

2. Principal / Mirror / Morror Monitor Server의 서버 인증 방식 설정

**앞서 생성한 계정이 SQL Server에 로그인할 수 있어야 합니다. 따라서 서버 인증 방식이 'SQL Server 및 Windows 인증 모드'로 변경되어야 합니다.

데이터베이스 Mirroring 구성

1. Principal Server에서 데이터베이스 Mirroring 구성하기

위 설정은 ADDS(Active Directory Domain Service)로 구성된 서버에서 설정하는 항목으로 설정이 필요하지 않습니다.

Mirroring 결과 확인

1. Principal Server에서 데이터베이스 상태 확인하기

2. Mirror Server에서 데이터베이스 상태 확인하기

3. Principal Server에서 Mirror Monitor 상태 확인하기

(부록) 만료된 인증서 교체 방법

1. Principal Server에서 기존 인증서 취소 및 신규 인증서 발급하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. 미러링 연결 취소
ALTER DATABASE HRDB2 SET PARTNER OFF
GO

--2. 신규 인증서 발급마스터 키 인증 비밀번호 생성
USE master
GO

CREATE CERTIFICATE ALIENSQL01_Cert_New
WITH SUBJECT = 'ALIENSQL01_Cert_New',
EXPIRY_DATE = N'2999/12/31'
GO

--3. EndPoint 인증서 변경
ALTER ENDPOINT Hadr_endpoint
STATE = STARTED AS TCP(LISTENER_PORT=5022, LISTENER_IP=ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE ALIENSQL01_Cert_New, ENCRYPTION=REQUIRED, ROLE=ALL)
GO

--4. 인증서를 드라이브에 백업
BACKUP CERTIFICATE ALIENSQL01_Cert_New TO FILE = 'C:\SQL_Cert\ALIENSQL01_Cert_New.cer'
GO

2. Mirror Server에서 기존 인증서 취소 및 신규 인증서 발급하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. 미러링 연결 취소
ALTER DATABASE HRDB2 SET PARTNER OFF
GO

--2. 신규 인증서 발급마스터 키 인증 비밀번호 생성
USE master
GO

CREATE CERTIFICATE ALIENSQL02_Cert_New
WITH SUBJECT = 'ALIENSQL02_Cert_New',
EXPIRY_DATE = N'2999/12/31'
GO

--3. EndPoint 인증서 변경
ALTER ENDPOINT Hadr_endpoint
STATE = STARTED AS TCP(LISTENER_PORT=5022, LISTENER_IP=ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE ALIENSQL02_Cert_New, ENCRYPTION=REQUIRED, ROLE=ALL)
GO

--4. 인증서를 드라이브에 백업
BACKUP CERTIFICATE ALIENSQL02_Cert_New TO FILE = 'C:\SQL_Cert\ALIENSQL02_Cert_New.cer'
GO

3. Mirror Monitor Server에서 기존 인증서 취소 및 신규 인증서 발급하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. 신규 인증서 발급마스터 키 인증 비밀번호 생성
USE master
GO

CREATE CERTIFICATE ALIENSQL03_Cert_New
WITH SUBJECT = 'ALIENSQL03_Cert_New',
EXPIRY_DATE = N'2999/12/31'
GO

--3. EndPoint 인증서 변경
ALTER ENDPOINT Hadr_endpoint
STATE = STARTED AS TCP(LISTENER_PORT=5022, LISTENER_IP=ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE ALIENSQL03_Cert_New, ENCRYPTION=REQUIRED, ROLE=ALL)
GO

--4. 인증서를 드라이브에 백업
BACKUP CERTIFICATE ALIENSQL03_Cert_New TO FILE = 'C:\SQL_Cert\ALIENSQL03_Cert_New.cer'
GO

4. Principal / Mirror / Mirror Monitor Server에 각각에서 생성한 인증서 가져오기

5. Principal Server에서 신규 인증서 권한 부여하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. Alien 계정에 미러 서버의 신규 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL02_Cert_New AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL02_Cert_New.cer'

--2. Alien 계정에 미러 모니터 서버의 신규 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL03_Cert_New AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL03_Cert_New.cer'

5. Mirror Server에서 신규 인증서 권한 부여하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. Alien 계정에 주 서버의 신규 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL01_Cert_New AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL01_Cert_New.cer'

--2. Alien 계정에 미러 모니터 서버의 신규 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL03_Cert_New AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL03_Cert_New.cer'

6. Mirror Monitor Server에서 신규 인증서 권한 부여하기

**빨간 글씨의 부분은 사용자에 맞게 설정해주세요.

--1. Alien 계정에 주 서버의 신규 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL01_Cert_New AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL01_Cert_New.cer'

--2. Alien 계정에 미러 서버의 신규 인증서 권한 부여
CREATE CERTIFICATE ALIENSQL02_Cert_New AUTHORIZATION Alien FROM FILE = 'C:\SQL_Cert\ALIENSQL02_Cert_New.cer'

7. 위 설정 이후 앞에서 안내한 미러링 마법사 실행 후 Mirror 재설정 작업을 진행합니다.

- 끝 -

'MS SQL' 카테고리의 다른 글

[MS SQL] 복원한 사용자 데이터베이스의 게시/구독 실패 (0)	2025.03.18
[MS SQL] SQL AlwaysOn 고가용성 구성가이드 Part.02 (2)	2023.09.08
[MS SQL] SQL AlwaysOn 고가용성 구성가이드 Part.01 (0)	2023.09.08
[MS SQL] 명명된 인스턴스 설치하기 (0)	2023.05.09
[MS SQL] SQL Server 관리 도구 SSMS 설치하기 (0)	2023.05.05

외계인코드의 IT LAB

[MS SQL] 비 도메인 환경의 SQL Server Mirror 구성 가이드

시스템 구성도

SQL Server 사전 준비 작업

Hosts 파일 수정 (Principal / Mirror / Mirror Monitor SQL Server에서 모두 진행)

Mirror 대상 사용자 데이터베이스 백업 및 복원

인증서 파일 생성하기

로그인 계정 생성 및 End Point 연결 권한 설정

로그인 계정에 권한 부여

데이터베이스 Mirroring 구성

Mirroring 결과 확인

(부록) 만료된 인증서 교체 방법

- 끝 -

'MS SQL' 카테고리의 다른 글

티스토리툴바

[MS SQL] 비 도메인 환경의 SQL Server Mirror 구성 가이드

시스템 구성도

SQL Server 사전 준비 작업

Hosts 파일 수정 (Principal / Mirror / Mirror Monitor SQL Server에서 모두 진행)

Mirror 대상 사용자 데이터베이스 백업 및 복원

인증서 파일 생성하기

로그인 계정 생성 및 End Point 연결 권한 설정

로그인 계정에 권한 부여

데이터베이스 Mirroring 구성

Mirroring 결과 확인

(부록) 만료된 인증서 교체 방법

- 끝 -

'MS SQL' 카테고리의 다른 글

'MS SQL' Related Articles

티스토리툴바