가용성 그룹 만들기(Transact-SQL)

  • 아티클

이 항목에서는 AlwaysOn 가용성 그룹 기능이 설정된 SQL Server 2012 인스턴스에서 Transact-SQL을 사용하여 가용성 그룹을 만들고 구성하는 방법을 설명합니다. 가용성 그룹은 단일 단위로 장애 조치(failover)될 사용자 데이터베이스 집합과 장애 조치(failover)를 지원하는 장애 조치(failover) 파트너 집합(가용성 복제본이라고 함)을 정의합니다.

[!참고]

가용성 그룹에 대한 개요를 보려면 AlwaysOn 가용성 그룹 개요(SQL Server)를 참조하십시오.

  • 시작하기 전 주의 사항:  

    필수 구성 요소

    보안

    태스크 및 해당 Transact-SQL 문 요약

  • 다음을 사용하여 가용성 그룹을 만들고 구성하려면:  Transact-SQL

  • **예: ** Windows 인증을 사용하는 가용성 그룹 구성

  • 관련 태스크

  • 관련 내용

[!참고]

Transact-SQL을 사용하는 대신 가용성 그룹 만들기 마법사나 SQL Server PowerShell cmdlet을 사용할 수도 있습니다. 자세한 내용은 새 가용성 그룹 마법사 사용(SQL Server Management Studio), 새 가용성 그룹 대화 상자 사용(SQL Server Management Studio) 또는 가용성 그룹 만들기(SQL Server PowerShell)를 참조하십시오.

시작하기 전 주의 사항

가용성 그룹을 처음 만들어 보는 경우 이 섹션을 먼저 읽는 것이 좋습니다.

필수 구성 요소, 제한 사항 및 권장 사항

보안

사용 권한

CREATE AVAILABILITY GROUP 서버 권한, ALTER ANY AVAILABILITY GROUP 권한, CONTROL SERVER 권한 중 하나와 sysadmin 고정 서버 역할의 멤버 자격이 필요합니다.

맨 위로 이동 링크와 함께 사용되는 화살표 아이콘[맨 위로 이동]

태스크 및 해당 Transact-SQL 문 요약

다음 표에서는 가용성 그룹을 만들고 구성하는 데 필요한 기본 태스크와 이러한 태스크에 사용할 Transact-SQL 문을 보여 줍니다. AlwaysOn 가용성 그룹 태스크는 표에 나오는 순서대로 수행해야 합니다.

태스크

Transact-SQL 문

태스크를 수행할 위치*

SQL Server 인스턴스당 하나의 데이터베이스 미러링 끝점 만들기

CREATE ENDPOINT endpointName … FOR DATABASE_MIRRORING

데이터베이스 미러링 끝점이 없는 각 서버 인스턴스에서 실행합니다.

가용성 그룹 만들기

CREATE AVAILABILITY GROUP

초기 주 복제본을 호스팅할 서버 인스턴스에서 실행합니다.

가용성 그룹에 보조 복제본 조인

ALTER AVAILABILITY GROUP group_name JOIN

보조 복제본을 호스팅하는 각 서버 인스턴스에서 실행합니다.

보조 데이터베이스 준비

BACKUP 및 RESTORE.

주 복제본을 호스팅하는 서버 인스턴스에 백업을 만듭니다.

RESTORE WITH NORECOVERY를 사용하여 보조 복제본을 호스팅하는 각 서버 인스턴스에 백업을 복원합니다.

가용성 그룹에 각 보조 데이터베이스를 조인하여 데이터 동기화 시작

ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = group_name

보조 복제본을 호스팅하는 각 서버 인스턴스에서 실행합니다.

* 지정된 태스크를 수행하려면 표시된 서버 인스턴스에 연결합니다.

맨 위로 이동 링크와 함께 사용되는 화살표 아이콘[맨 위로 이동]

Transact-SQL을 사용하여 가용성 그룹 만들기 및 구성

[!참고]

이러한 각 Transact-SQL 문의 코드 예가 포함된 예제 구성 프로시저는 예: Windows 인증을 사용하는 가용성 그룹 구성을 참조하십시오.

  1. 주 복제본을 호스팅할 서버 인스턴스에 연결합니다.

  2. CREATE AVAILABILITY GROUP Transact-SQL 문을 사용하여 가용성 그룹을 만듭니다.

  3. 새 보조 복제본을 가용성 그룹에 조인합니다. 자세한 내용은 가용성 그룹에 보조 복제본 조인(SQL Server)을 참조하십시오.

  4. 가용성 그룹의 각 데이터베이스에 대해 RESTORE WITH NORECOVERY를 사용하여 주 데이터베이스의 최신 백업을 복원하는 방법으로 보조 데이터베이스를 만듭니다. 자세한 내용은 가용성 그룹 만들기(Transact-SQL)에서 데이터베이스 백업을 복원하는 단계부터 참조하십시오.

  5. 모든 새 보조 데이터베이스를 가용성 그룹에 조인합니다. 자세한 내용은 가용성 그룹에 보조 복제본 조인(SQL Server)을 참조하십시오.

맨 위로 이동 링크와 함께 사용되는 화살표 아이콘[맨 위로 이동]

예: Windows 인증을 사용하는 가용성 그룹 구성

이 예에서 만드는 예제 AlwaysOn 가용성 그룹 구성 프로시저는 Transact-SQL을 사용하여 Windows 인증을 사용하는 데이터베이스 미러링 끝점을 설정하고, 가용성 그룹과 해당 보조 데이터베이스를 만들고 구성합니다.

이 예에는 다음과 같은 섹션이 포함되어 있습니다.

  • 예제 구성 프로시저를 사용하기 위한 사전 요구 사항

  • 예제 구성 프로시저

  • 예제 구성 프로시저에 대한 전체 코드 예

예제 구성 프로시저를 사용하기 위한 사전 요구 사항

이 예제 프로시저에 대한 요구 사항은 다음과 같습니다.

  • 서버 인스턴스에서는 AlwaysOn 가용성 그룹을 지원해야 합니다. 자세한 내용은 온라인 설명서의 AlwaysOn 가용성 그룹(SQL Server)에 대한 사전 요구 사항, 제한 사항 및 권장 사항을 참조하십시오.

  • MyDb1 및 MyDb2라는 두 예제 데이터베이스는 주 복제본을 호스팅할 서버 인스턴스에 있어야 합니다. 다음 코드 예에서는 이러한 두 데이터베이스를 만들고 구성하며 각 데이터베이스의 전체 백업을 만듭니다. 예제 가용성 그룹을 만들려는 서버 인스턴스에서 이러한 코드 예를 실행합니다. 이 서버 인스턴스는 예제 가용성 그룹의 초기 주 복제본을 호스팅합니다.

    1. 다음 Transact-SQL 예에서는 이러한 데이터베이스를 만든 다음 전체 복구 모델을 사용하도록 데이터베이스를 변경합니다.

      -- Create sample databases:
CREATE DATABASE MyDb1;
GO
ALTER DATABASE MyDb1 SET RECOVERY FULL;
GO

CREATE DATABASE MyDb2;
GO
ALTER DATABASE MyDb2 SET RECOVERY FULL;
GO

    2. 다음 코드 예에서는 MyDb1 및 MyDb2의 전체 데이터베이스 백업을 만듭니다. 이 코드 예에서는 \\FILESERVER\SQLbackups라는 가상의 백업 공유를 사용합니다.

      -- Backup sample databases:
BACKUP DATABASE MyDb1 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH FORMAT
GO

BACKUP DATABASE MyDb2 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH FORMAT
GO

[예의 맨 위]

예제 구성 프로시저

이 예제 구성에서는 서비스 계정이 다르지만 트러스트된 도메인 계정(DOMAIN1 및 DOMAIN2)으로 실행되는 두 개의 독립 실행형 서버 인스턴스에 가용성 복제본이 만들어집니다.

다음 표에는 이 예제 구성에 사용된 값이 요약되어 있습니다.

초기 역할

시스템

호스트 SQL Server 인스턴스

주

COMPUTER01

AgHostInstance

보조

COMPUTER02

기본 인스턴스입니다.

  1. 가용성 그룹을 만들 서버 인스턴스(COMPUTER01에 있는 AgHostInstance라는 인스턴스)에 dbm_endpoint라는 데이터베이스 미러링 끝점을 만듭니다. 이 끝점은 포트 7022를 사용합니다. 가용성 그룹을 만드는 서버 인스턴스는 주 복제본을 호스팅합니다.

    -- Create endpoint on server instance that hosts the primary replica:
CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

  2. 보조 복제본을 호스팅할 서버 인스턴스(COMPUTER02에 있는 기본 서버 인스턴스)에 dbm_endpoint라는 끝점을 만듭니다. 이 끝점은 포트 5022를 사용합니다.

    -- Create endpoint on server instance that hosts the secondary replica: 
CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

  3. [!참고]

    가용성 복제본을 호스팅할 서버 인스턴스의 서비스 계정이 동일한 도메인 계정으로 실행되는 경우에는 이 단계가 필요하지 않습니다. 이 단계를 생략하고 다음 단계로 직접 이동합니다.

    서버 인스턴스의 서비스 계정이 다른 도메인 사용자로 실행되는 경우에는 각 서버 인스턴스에서 다른 서버 인스턴스에 대한 로그인을 만들고 로컬 데이터베이스 미러링 끝점에 액세스할 수 있는 권한을 이 로그인에 부여합니다.

    다음 코드 예에서는 로그인을 만들고 이 로그인에 끝점에 대한 사용 권한을 부여하기 위한 Transact-SQL 문을 보여 줍니다. 여기에서 원격 서버 인스턴스의 도메인 계정은 domain_name\user_name으로 표시됩니다.

      -- If necessary, create a login for the service account, domain_name\user_name
  -- of the server instance that will host the other replica:
  USE master;
  GO
  CREATE LOGIN [domain_name\user_name] FROM WINDOWS;
  GO
  -- And Grant this login connect permissions on the endpoint:
  GRANT CONNECT ON ENDPOINT::dbm_endpoint 
     TO [domain_name\user_name];
  GO

  4. 사용자 데이터베이스가 있는 서버 인스턴스에서 가용성 그룹을 만듭니다.

    다음 코드 예에서는 MyDb1 및 MyDb2라는 예제 데이터베이스가 만들어진 서버 인스턴스에 MyAG라는 가용성 그룹을 만듭니다. COMPUTER01에 로컬 서버 인스턴스 AgHostInstance가 먼저 지정됩니다. 이 인스턴스는 초기 주 복제본을 호스팅합니다. COMPUTER02에 기본 서버 인스턴스인 원격 서버 인스턴스가 보조 복제본을 호스팅하도록 지정됩니다. 두 가용성 복제본 모두 수동 장애 조치와 함께 비동기 커밋 모드를 사용하도록 구성됩니다. 비동기 커밋 복제본에 대한 수동 장애 조치는 데이터 손실이 가능한 강제 장애 조치를 의미합니다.

    -- Create the availability group, MyAG: 
CREATE AVAILABILITY GROUP MyAG 
   FOR 
      DATABASE MyDB1, MyDB2 
   REPLICA ON 
      'COMPUTER01\AgHostInstance' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022', 
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL
         ),
      'COMPUTER02' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL
         ); 
GO

    가용성 그룹을 만드는 다른 Transact-SQL 코드 예제를 보려면 CREATE AVAILABILITY GROUP(Transact-SQL)을 참조하십시오.

  5. 보조 복제본을 호스팅하는 서버 인스턴스에서 보조 복제본을 가용성 그룹에 조인합니다.

    다음 코드 예에서는 COMPUTER02의 보조 복제본을 MyAG 가용성 그룹에 조인합니다.

    -- On the server instance that hosts the secondary replica, 
-- join the secondary replica to the availability group:
ALTER AVAILABILITY GROUP MyAG JOIN;
GO

  6. 보조 복제본을 호스팅하는 서버 인스턴스에서 보조 데이터베이스를 만듭니다.

    다음 코드 예에서는 RESTORE WITH NORECOVERY를 사용하여 데이터베이스 백업을 복원하는 방법으로 MyDb1 및 MyDb2 보조 데이터베이스를 만듭니다.

    -- On the server instance that hosts the secondary replica, 
-- Restore database backups using the WITH NORECOVERY option:
RESTORE DATABASE MyDb1 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH NORECOVERY
GO

RESTORE DATABASE MyDb2 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH NORECOVERY
GO

  7. 주 복제본을 호스팅하는 서버 인스턴스에서 각 주 데이터베이스의 트랜잭션 로그를 백업합니다.

    중요 정보중요

    실제 가용성 그룹을 구성할 때는 해당 보조 데이터베이스를 가용성 그룹에 조인한 후에 주 데이터베이스에 대한 로그 백업 태스크를 수행하는 것이 좋습니다.

    다음 코드 예에서는 MyDb1 및 MyDb2에 트랜잭션 로그 백업을 만듭니다.

    -- On the server instance that hosts the primary replica, 
-- Backup the transaction log on each primary database:
BACKUP LOG MyDb1 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH NOFORMAT
GO

BACKUP LOG MyDb2 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITHNOFORMAT
GO
    팁팁

    일반적으로 로그 백업은 각 주 데이터베이스에서 만든 다음 WITH NORECOVERY를 사용하여 해당하는 보조 데이터베이스에 복원해야 합니다. 그러나 데이터베이스를 방금 만들었으며 아직 로그 백업을 만들지 않았거나 복구 모델이 방금 SIMPLE에서 FULL로 변경된 경우에는 이 로그 백업이 필요하지 않을 수도 있습니다.

  8. 보조 복제본을 호스팅하는 서버 인스턴스에서 보조 데이터베이스에 로그 백업을 적용합니다.

    다음 코드 예에서는 RESTORE WITH NORECOVERY를 사용하여 데이터베이스 백업을 복원하는 방법으로 MyDb1 및 MyDb2 보조 데이터베이스에 백업을 적용합니다.

    중요 정보중요

    실제 보조 데이터베이스를 준비할 때는 처음부터 항상 RESTORE WITH NORECOVERY를 사용하여 보조 데이터베이스를 만들 때 사용한 데이터베이스 백업보다 나중에 만들어진 모든 로그 백업을 적용해야 합니다. 물론, 전체 데이터베이스 백업과 차등 데이터베이스 백업을 모두 복원하는 경우에는 차등 백업 이후에 만들어진 로그 백업만 적용하면 됩니다.

    		 
    -- Restore the transaction log on each secondary database,
-- using the WITH NORECOVERY option:
RESTORE LOG MyDb1 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH FILE=1, NORECOVERY
GO
RESTORE LOG MyDb2 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH FILE=1, NORECOVERY
GO

  9. 보조 복제본을 호스팅하는 서버 인스턴스에서 새 보조 데이터베이스를 가용성 그룹에 조인합니다.

    다음 코드 예에서는 MyDb1 보조 데이터베이스를 조인한 다음 MyDb2 보조 데이터베이스를 MyAG 가용성 그룹에 조인합니다.

    -- On the server instance that hosts the secondary replica, 
-- join each secondary database to the availability group:
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;
GO

ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;
GO

[예의 맨 위]

예제 구성 프로시저에 대한 전체 코드 예

다음 예에서는 예제 구성 프로시저의 모든 단계에 포함된 코드 예를 병합합니다. 다음 표에는 이 코드 예에 사용된 자리 표시자 값이 요약되어 있습니다. 이 코드 예의 단계에 대한 자세한 내용은 이 항목 윗부분의 예제 구성 프로시저를 사용하기 위한 사전 요구 사항 및 예제 구성 프로시저를 참조하십시오.

자리 표시자

설명

\\FILESERVER\SQLbackups

가상의 백업 공유입니다.

\\FILESERVER\SQLbackups\MyDb1.bak

MyDb1의 백업 파일입니다.

\\FILESERVER\SQLbackups\MyDb2.bak

MyDb2의 백업 파일입니다.

7022

각 데이터베이스 미러링 끝점에 할당된 포트 번호입니다.

COMPUTER01\AgHostInstance

초기 주 복제본을 호스팅하는 서버 인스턴스입니다.

COMPUTER02

초기 보조 복제본을 호스팅하는 서버 인스턴스입니다. 이 인스턴스는 COMPUTER02의 기본 서버 인스턴스입니다.

dbm_endpoint

각 데이터베이스 미러링 끝점에 지정된 이름입니다.

MyAG

예제 가용성 그룹의 이름입니다.

MyDb1

첫 번째 예제 데이터베이스의 이름입니다.

MyDb2

두 번째 예제 데이터베이스의 이름입니다.

DOMAIN1\user1

초기 주 복제본을 호스팅할 서버 인스턴스의 서비스 계정입니다.

DOMAIN2\user2

초기 보조 복제본을 호스팅할 서버 인스턴스의 서비스 계정입니다.

TCP://COMPUTER01.Adventure-Works.com:7022

COMPUTER01에 있는 SQL Server의 AgHostInstance 인스턴스의 끝점 URL입니다.

TCP://COMPUTER02.Adventure-Works.com:5022

COMPUTER02에 있는 SQL Server의 기본 인스턴스의 끝점 URL입니다.

[!참고]

가용성 그룹을 만드는 다른 Transact-SQL 코드 예제를 보려면 CREATE AVAILABILITY GROUP(Transact-SQL)을 참조하십시오.

-- on the server instance that will host the primary replica, 
-- create sample databases:
CREATE DATABASE MyDb1;
GO
ALTER DATABASE MyDb1 SET RECOVERY FULL;
GO

CREATE DATABASE MyDb2;
GO
ALTER DATABASE MyDb2 SET RECOVERY FULL;
GO

-- Backup sample databases:
BACKUP DATABASE MyDb1 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH FORMAT
GO

BACKUP DATABASE MyDb2 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH FORMAT
GO

-- Create the endpoint on the server instance that will host the primary replica:
CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

-- Create the endpoint on the server instance that will host the secondary replica: 
CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

-- If both service accounts run under the same domain account, skip this step. Otherwise, 
-- On the server instance that will host the primary replica, 
-- create a login for the service account 
-- of the server instance that will host the secondary replica, DOMAIN2\user2, 
-- and grant this login connect permissions on the endpoint:
USE master;
GO
CREATE LOGIN [DOMAIN2\user2] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::dbm_endpoint 
   TO [DOMAIN2\user2];
GO

-- If both service accounts run under the same domain account, skip this step. Otherwise, 
-- On the server instance that will host the secondary replica,
-- create a login for the service account 
-- of the server instance that will host the primary replica, DOMAIN1\user1, 
-- and grant this login connect permissions on the endpoint:
USE master;
GO

CREATE LOGIN [DOMAIN1\user1] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::dbm_endpoint 
   TO [DOMAIN1\user1];
GO

-- On the server instance that will host the primary replica, 
-- create the availability group, MyAG: 
CREATE AVAILABILITY GROUP MyAG 
   FOR 
      DATABASE MyDB1, MyDB2 
   REPLICA ON 
      'COMPUTER01\AgHostInstance' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         FAILOVER_MODE = AUTOMATIC
         ),
      'COMPUTER02' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:7022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         FAILOVER_MODE = AUTOMATIC
         ); 
GO

-- On the server instance that hosts the secondary replica, 
-- join the secondary replica to the availability group:
ALTER AVAILABILITY GROUP MyAG JOIN;
GO

-- Restore database backups onto this server instance, using RESTORE WITH NORECOVERY:
RESTORE DATABASE MyDb1 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH NORECOVERY
GO

RESTORE DATABASE MyDb2 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH NORECOVERY
GO

-- Back up the transaction log on each primary database:
BACKUP LOG MyDb1 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH NOFORMAT
GO

BACKUP LOG MyDb2 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITHNOFORMAT
GO

-- Restore the transaction log on each secondary database,
-- using the WITH NORECOVERY option:
RESTORE LOG MyDb1 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH FILE=1, NORECOVERY
GO
RESTORE LOG MyDb2 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH FILE=1, NORECOVERY
GO

-- On the server instance that hosts the secondary replica, 
-- join each secondary database to the availability group:
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;
GO

ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;
GO

맨 위로 이동 링크와 함께 사용되는 화살표 아이콘[예의 맨 위]

관련 태스크

가용성 그룹 및 복제본 속성을 구성하려면

가용성 그룹 구성을 완료하려면

가용성 그룹을 만드는 다른 방법

AlwaysOn 가용성 그룹을 사용하도록 설정하려면

데이터베이스 미러링 끝점을 구성하려면

AlwaysOn 가용성 그룹 구성 문제를 해결하려면

맨 위로 이동 링크와 함께 사용되는 화살표 아이콘[맨 위로 이동]

관련 내용

맨 위로 이동 링크와 함께 사용되는 화살표 아이콘[맨 위로 이동]

참고 항목

개념

데이터베이스 미러링 끝점(SQL Server)

AlwaysOn 가용성 그룹 개요(SQL Server)

가용성 그룹 수신기, 클라이언트 연결 및 응용 프로그램 장애 조치(failover)(SQL Server)

온라인 설명서의 AlwaysOn 가용성 그룹(SQL Server)에 대한 사전 요구 사항, 제한 사항 및 권장 사항