SQL Server on Linux 고가용성 그룹(AG) 구성

지난 포스팅에서는 Window VM에서 SQL Server를 설치하고 고가용성 그룹을 구성하는걸 진행해보았습니다. 이번 포스팅에서는 Linux VM 상에서 고가용성 그룹을 구성해보도록 하겠습니다. 

 

Linux(Ubuntu)용 가상 머신 두 대를 Azure에서 준비합니다. 2대의 Ubuntu Server 22.04 LTS 서버를 생성합니다. 

 

생성한 Linux 가상머신을 Putty 접속 방법으로 접속한 뒤 ip a 명령어를 수행하여 ip 주소를 확인합니다.

 

동일한 가상 네트워크 안에 만들어 주었기 때문에 10.~ 대의 Private IP로도 둘 간의 통신이 가능합니다.

가용성 그룹으로 묶을 머신 두 대가 모두 다 같은 가상 네트워크 안에 있기 때문에 가상 IP로도 통신을 수행할 수 있습니다.

 

간단하게 서로 간의 통신이 되는지 ping <상대의 ip주소> 을 통해서 통신이 되는지 양쪽 머신에서 모두 확인합니다.

그러면 아래와 같이 우선 서로 간의 ping 통신은 수행됩니다.

 

이제 서로 통신을 편하게 하기 위한 호스트 이름을 지정해 줄 예정입니다.

Root 계정에서 vi /etc/hosts 명령 수행 후 아래와 같이 이름 및 ip를 입력합니다. 그리고 ESC :wq! 버튼을 눌러 저장을 하고 빠져 나옵니다.

 

이제 VM 간 통신할 준비를 마쳤으므로 본격적으로 SQL Server를  설치해 줍니다.

참고로 SQL Server의 Linux 버전을 구동하기 위해서는 최소 3.25GB의 메모리가 필요합니다.

이번 설치는 편의를 위하여 root 계정에서 계속 진행하도록 합니다.

먼저 설치 사전준비를 위해 아래 명령어를 수행해 줍니다.

sudo apt update -y 
sudo apt install curl apt-transport-https software-properties-common -y

 

또 아래 명령어를 수행하여 Microsoft SQL Server Ubuntu repository를 등록해 줍니다.

 

앞선 명령어들로 저장소를 등록하고 나면, 이제 apt update를 실행할 때 마이크로소프트 서버에도 방문해서 SQL Server 설치 파일의 위치와 정보를 가져오게 됩니다.

curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg  
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list | sudo tee /etc/apt/sources.list.d/mssql-server-2022.list

 

이후 sudo apt-get update 명령어를 수행하여 업데이트를 수행해주고 sudo apt-get install -y mssql-server 명령어로 mssql-server를 설치해 줍니다.

sudo apt-get update
sudo apt-get install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup

 

설치가 완료되었으면 sudo /opt/mssql/bin/mssql-conf setup 명령어를 수행합니다. 이 명령어는 한마디로 "설치된 SQL Server의 초기 설정을 시작하는 마법사"라고 생각하시면 됩니다. setup 명령어는 실제로 사용할 수 있게 전원을 켜고 환경 설정을 하는 과정입니다.

 

이후 1. Evaluation를 선택합니다. 그러면 라이선스 동의 창이 나타나며 Yes를 입력해 줍니다.

 

설치가 완료되었다면 SQL Server가 동작하는지 systemctl status mssql-server 명령어로 확인합니다. 

 

두 번째 VM에서도 앞선 내용과 동일하게 진행하여 설치해줍니다. 

 

각 노드에서 Always On 가용성 그룹을 사용하도록 설정하는  set hadr.hadrenabled 1 명령어를 입력합니다.

여기서 HADR은 High Availability and Disaster Recovery(고가용성 및 재해 복구)의 약자입니다.

 

mssql-server를 다시 시작합니다. 다음 스크립트를 실행합니다.

 

이제 SSMS를 열어 객체 탐색기에 접속합니다. 새로운 가용성 머신에 둘 다 접속해 줍니다.

 

각각의 머신에 각각 아래 스크립트를 돌려 로그인 계정을 생성해 줍니다. 

 

이 명령어들은 SQL Server의 보안 체계를 설정하는 과정으로, 쉽게 말해 "서버에 접속할 수 있는 ID를 만들고(Login), 그 ID가 특정 데이터베이스에 들어갈 수 있는 권한을 주는(User) 작업"입니다.

 

이러한 특정 서비스 전용 ID(dbm_login)를 만들고, 이 ID에는 딱 필요한 데이터베이스의 데이터만 읽고 쓸 수 있는 권한만 부여해서 사용합니다.

CREATE LOGIN dbm_login WITH PASSWORD = 'Pass@word123'
CREATE USER dbm_user FOR LOGIN dbm_login

 

주 서버가 될 첫 번째 VM에서 마스터 키와 인증서 생성합니다.

 

이 코드는 두 대의 서버(VM)가 서로 안전한지 확인하기 위해 데이터를 주고받을 수 있도록 보안 통로를 만드는 작업입니다. 마스터 키 안에는 다른 비밀번호나 인증서들을 보관합니다. 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@word123'
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm'
BACKUP CERTIFICATE dbm_certificate
   TO FILE = 'C:\var\opt\mssql\data\dbm_certificate.cer'
   WITH PRIVATE KEY (
           FILE = 'C:\var\opt\mssql\data\dbm_certificate.pvk',
           ENCRYPTION BY PASSWORD = 'Pass@word123'
       )

 

그럼 아래와 같이 해당하는 AG1 서버 위치에 인증서가 생성된 것을 확인할 수 있습니다.

 

이제 여기 있는 인증서와 개인 키를 AG2 서버에 복사해주는 작업을 수행합니다. 복사를 하는 이유는 이름이 같아도 각자 서버에서 만들면 "서로 다른 신분증"이 되기 때문에 통신이 불가능해지기 때문입니다.

 

먼저 이를 위해서는 AG2 머신에서 root 계정을 이용한 원격 SSH 접속을 허용 해주어야 이 작업을 수행할 수 있습니다. 이를 위해 AG2 서버에 접속하여 root 계정의 Password를 설정해 줄 것입니다. 

 

이후 나타난 config 파일에서 PermitRootLogin 을 yes로 변경해 주고 ESC:wq!를 눌러 저장해 줍니다.

이 때 빠르게 찾기 위해서는 /Permit으로 검색을 수행할 수도 있습니다.

그리고 저장이 완료되었으면 아래와 같이 서버 서비스를 재시작 해줍니다.

 

이제 다시 AG1 머신의 SSH를 다시 열어서, 앞서 만든 인증서와 키가 저장되어 있는 곳으로 이동해 줍니다. 그리고 아래와 같이 키와 인증서를 AG2 머신에 아래와 같이 전송해 줍니다.

 

리눅스에서 서버 간에 파일을 복사할 때는 scp (Secure Copy)라는 명령어를 사용합니다. 이때 AG1에서 AG2로 파일을 "직접" 쏴주려면, AG2가 "그래, 외부에서 root가 접속해서 파일 넣는 거 허용해줄게"라는 설정을 앞에서 한 것입니다. 

 

AG2 머신의 root 비밀번호를 입력하고, 원격 접속이 허용되었다면 원활하게 전송이 될 것입니다.

 

AG1에서 복사된 인증서를 AG2 머신에서 정상적으로 복사되었는지 확인해 봅니다. 그러면 아래와 같이 복사된 인증서와 개인키가 나타나며 이 소유권이 root로 되어있음을 확인할 수 있습니다.

 

마찬가지로 이 두 파일의 소유권을 chown mssql:mssql dbm_certificate.* 명령어로 mssql로 변경해 줍니다.

 

소유권을 변경해주는 이유는 리눅스에서 SQL Server를 설치하면, 보안을 위해 mssql-server라는 프로그램은 시스템 전체를 관리하는 root 권한으로 실행되지 않고, mssql이라는 별도의 제한된 사용자 권한으로 실행됩니다. mssql 프로그램이 이 파일을 열어볼 수 있도록 바꿔주는 것입니다. 

 

소유권이 제대로 변경 되었는지 ls -al 명령어로 확인해 봅니다.

 

이제 SSMS를 다시 열어 AG2에서 작업을 수행해 줍니다. 이 때 방금 앞 단계에서 전달 받은 Private Key를 활용해 작업을 수행하여 마스터 키를 만들어 줄 것입니다.

 

FROM 이라는 명령어를 통해 파일을 가져와 등록하는 과정임을 알 수 있습니다. 

 

마스터 키는 각 서버(Instance)마다 개별적으로 존재하는 '로컬 금고'이기 때문에 AG2에도 만들어야 합니다. 이렇게 만든 마스터 키에 인증서를 저장하는 것입니다. 

 

마스터 키 (Master Key)는 SQL Server 데이터베이스(특히 master DB) 시스템 안에 존재하는 암호화 계층입니다. 우리가 CREATE MASTER KEY... 명령어를 입력했을 때 서버 메모리와 저장소 공간에 만들어지는 '보관함' 자체를 의미합니다.

dbm_certificate.cer는 인증서이고, dbm_certificate.pvk는 개인 키(Private Key)입니다.

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@word123' 
CREATE CERTIFICATE dbm_certificate   
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\var\opt\mssql\data\dbm_certificate.cer'
    WITH PRIVATE KEY (
    FILE = 'C:\var\opt\mssql\data\dbm_certificate.pvk',
    DECRYPTION BY PASSWORD = 'Pass@word123'
)

 

방화벽을 양쪽 서버 모두 내려주는 작업을 합니다. 보안상으로는 특정 포트만 열어주도록 허용하는 것이 권장이나 금번 실습에서는 간단하게 방화벽을 disable 시킬 예정입니다.

 

 

아래의 코드를 통해 이제 각각의 VM이 통신할 준비를 합니다. 

 

SQL Server 데이터 복제 전용으로만 쓰겠다고 지정해둔 끝점(Endpoint)를 만듭니다. 

데이터베이스 미러링 끝점은 TCP를 사용하여 DB 미러링 세션에 참여하는 또는 가용성 복제본들을 호스팅 하는 서버들 간의 메시지를 주고 받습니다. DB미러링 끝점은 TCP 유니크한 끝점 번호를 사용합니다.

 

가용성 그룹을 위한 리스닝 끝점을 Hadr_endpoint라는 이름으로 생성합니다. 그리고 만들어진 유저에게 연결할 수 있는 권한을 줍니다. 이 때 주의할 점은 각각 머신의 IP 주소를 환경에 맞게 바꾸고, 비밀번호를 지정하여 쿼리를 수행해 줍니다.

 

ROLE을 ALL로 설정해 데이터를 보낼 수도 있고 받을 수도 있게 만듭니다. 

그리고 앞서 만든 인증서를 사용합니다. 

 

STATE를 STARTED 대기 상태로 만들고 앞서 만든 사용자 dbm_login이 해당 끝점을 사용할 수 있도록 지정합니다. 

-- AG1에서 Hadr_endpoint 만들기
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_IP = (20.64.234.253), LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        )
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login]
 
 
-- AG2에서 Hadr_endpoint 만들기
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_IP = (20.120.178.45), LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        )
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login]

 

그 다음으로는 끝점이 정확하게 만들어졌는지 확인을 하기 위한 각각의 머신 모두에 endpoint를 조회해봅니다.

SELECT * FROM sys.endpoints

 

이제 실제 가용성 그룹을 만드는 쿼리를 수행해 볼 예정입니다. 가용성 그룹을 실제 생성하는 작업은 주 서버가 될 노드에서만 작업을 수행해 주면 됩니다. 여기서 각자 머신의 hostname으로 바꿔야만 됩니다.

 

아래 쿼리의 각각의 hostname을 실제 작업하신 머신들의 hostname으로 각각 바꿔주시면 됩니다.

 

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT (동기 커밋) 코드는 주 서버에서 데이터를 저장할 때, 부 서버에도 똑같이 저장되었다는 확인 신호가 올 때까지 기다렸다가 최종 완료(Commit)를 하는 방식입니다.

Failover를 수동으로 설정합니다. Failover는 주 서버(Primary)에 문제가 생겼을 때, 대기하고 있던 부 서버(Secondary)가 즉시 주인의 자리를 이어받아 서비스를 계속하는 과정을 말합니다.

Seeding은 주 서버에 있는 거대한 데이터베이스를 부 서버로 처음 복사해주는 과정을 말합니다.

-- 가용성 그룹 생성(주서버가 될 서버에서 작업)
CREATE AVAILABILITY GROUP [ag1]
    WITH (DB_FAILOVER = ON, CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'2dt116HAlinux01' WITH (
            ENDPOINT_URL = N'tcp://2dt116HAlinux01:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
            ),
        N'2dt116HAlinux02' WITH ( 
            ENDPOINT_URL = N'tcp://2dt116HAlinux02:5022', 
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
            )
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE

 

 

그리고 객체 탐색기(AG1 머신 부분에서)의 AlwaysOn High Availability 부분을 확장하여 확인해 봅니다. 그러면 방금 만든 ag1이라는 가용성 그룹이 생성된 것을 확인할 수 있습니다.

 

그리고 이제 아래 쿼리를 두 번째 VM에서 실행시켜 봅니다. 이 쿼리는 앞 단계에서 만들어진 가용성 그룹에 AG2를 조인시키는 작업을 수행합니다.

-- 앞서 만들어진 가용성 그룹에 조인
-- Secondary 보조 복제본에서 AG1에 조인하기 위한 쿼리
ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE)
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE

 

두 번째 보조 서버(AG2)가 가용성 그룹에 잘 조인되었는지는 마찬가지로 전 단계에서 확인했던 AlwaysOn High Availability에서 Availability Replicas를 확장하여 확인합니다.

 

 

이제 가용성 그룹에 추가해 주려고 하는 DB가 완전 복구 모드로 되어 있는지, 그리고 유효한 로그 백업이 있는지 확인해야 합니다. 만약 테스트할 DB가 또는 새로운 DB를 만들었다면 데이터베이스 백업을 수행해 주셔야 합니다.

 

가용성 그룹의 핵심은 "주 서버에서 일어난 모든 일을 부 서버도 똑같이 따라 하는 것"입니다. 이를 위해 SQL Server는 모든 변경 기록이 담긴 '트랜잭션 로그'를 부 서버로 계속 보내줍니다. 이때 완전 복구 모드는 삭제하지 않고 모든 로그를 기록합니다. 

 

완전 복구 모델로 설정을 바꾼 후, 첫 번째 전체 백업을 받아야만 그때부터 비로소 "데이터 변경 기록의 사슬(Log Chain)"이 꼬리에 꼬리를 물고 생성됩니다. BACKUP DATABASE [db1] TO DISK = N'NUL' 명령어를 통해 백업을 하되, 결과 파일을 저장하지 말고 그냥 허공에 날려버림으로써 완전 복구 모드를 실행하는 것입니다.

 

주 서버에서 다음 T-SQL 쿼리를 수행하여 DB1이라는 데이터베이스를 만들고, 백업을 수행합니다. 따라서 아래 쿼리를 수행하여 만들어진 가용성 그룹에 AG1머신에서 데이터베이스를 하나 만들고, 이 데이터베이스를 완전 복구 모델로 지정하고 미리 백업을 한 번 해 둡니다.

--가용성 그룹에 추가할 DB를 생성, 
--복구 모델을 완전 복구모드로 변환하고, 
--디스크에 이를 백업해 둠
CREATE DATABASE [db1]
ALTER DATABASE [db1] SET RECOVERY FULL
BACKUP DATABASE [db1] TO DISK = N'NUL'

 

그리고 마찬가지로 AG1 머신에서만 아래 쿼리를 또 수행하여 방금 만든 DB를 가용성 그룹에 추가해 줍니다.

 

그룹에 추가되지 않은 일반 DB는 서버가 고장 나면 그대로 멈추지만, 그룹에 속한 DB는 부 서버에서 즉시 살아날 수 있습니다. 이것이 바로 '고가용성(High Availability)'의 핵심입니다.

-- 가용성 그룹에 방금 만든 DB 추가
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1]

 

그리고 객체 탐색기에서 가용성 데이터베이스를 확장해서 확인하면 방금 추가된 db1을 확인할 수 있습니다.

 

그러면 아래 쿼리로 실제 보조 복제본에서도 AG1에서만 만들었던 DB가 제대로 동기화가 되었는지를 확인합니다.

--보조 복제본에서 아래 쿼리로 동기화가 잘 되었는지 확인
SELECT * FROM sys.databases WHERE name = 'db1'
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc 
FROM sys.dm_hadr_database_replica_states

 

그러면 아래와 같은 결과가 나타나고 현재 동기화 상태라는 것을 확인할 수 있습니다.

 

이제 객체 탐색기에서 AG2 머신에 해당하는 서버를 확장해서 살펴보더라도 db1이 보여지는 것을 확인할 수 있습니다.

 

Linux와 Window에서 SQL Server를 설치하고 관리할 때 몇 가지 차이점이 있습니다. 이는 별도의 관리자(클러스터 서비스)가 없는 순수 SQL Server만의 복제 상태이기 때문에 발생하는 특징들입니다. 

 

먼저 윈도우의 WSFC 같은 외부 관리자가 없어 쿼럼 설정이 없는 상태입니다. 

또한, 리눅스에서 클러스터 관리자(Pacemaker 등) 없이 구성하면 자동으로 Failover가 되지 않습니다. 따라서 직접 접속해서 "이제부터는 AG2 네가 주 서버를 해!"라고 명령어를 쳐줘야만 서비스가 재개됩니다.

 

아래의 상태에서 이를 확인할 수 있습니다. 

 

 

이 상태에서 수동 페일 오버를 수행해 보겠습니다.

 

1번 서버에서 SQL Server를 강제로 중지합니다.

sudo systemctl stop mssql-server

 

이후 2번 노드에서 강제 Failover를 수행합니다.

--final
ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;

 

주 서버가 바뀐 것을 확인할 수 있습니다.