SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기

SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기

 

·         Version : SQL Server

 

SQL Server AlwaysOn Availability Group으로 데이터베이스가 구성된 경우 해당 데이터베이스는 복원할 수 없다. 먼저 데이터베이스를 복원하기 위해서는 가용성 그룹의 SQL Server 데이터베이스를 제거해야 한다. 이번 포스트에서는 SQL Server Job Agent를 사용하여 AlwaysOn 설정이 자동으로 구성되록 하는 방법을 살펴 본다. 각 단계별 스크립트를 Job Agent에 등록하여 사용할 수 있다.

 

1. Primary 서버의 AlwaysOn 가용성 그룹에서 데이터베이스 제거

-- runs on primary server   USE master GO   ALTER AVAILABILITY GROUP [<Availability Group>] REMOVE DATABASE [<Database Name>]; GO

 

2. Primary 서버의 데이터베이스 복원

이 단계는 Primary 서버의 데이터베이스를 복원한다. 사용 권한 부여등 복원후 특정 요구사항에 대한 추가 단계를 추가하여 실행 할 수 도 있다. 아래 스크립트는 복원하려는 파일이 네트워크 상의 다른 서버에 있는 경우이다.

-- runs on primary server   USE master GO   RESTORE DATABASE [<Database Name>] FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH REPLACE GO

 

3. Primary 서버에 복원된 데이터베이스를 전체 복구 모델로 변경

-- runs on primary server   USE master GO   ALTER DATABASE [<Database Name>] SET RECOVERY FULL WITH NO_WAIT GO

 

4. Primary 서버에서 데이터베이스 전체 백업 및 로그 백업 진행

복원 작업을 쉽게 수행하기 위해 Primary 서버보다 Secondary 서버가 액세스할 수 있는 네트워크 공유에 백업파일을 만드는것이 좋다. 이렇게 하면 Secondary로 백업을 복사하지 않아도 되며 복사단계를 생략하여 시간을 절약할 수 있다.

-- runs on primary server   BACKUP DATABASE [<database Name>] TO DISK='<Shared Network Location>\<Database Name>.bak' WITH FORMAT, INIT, COMPRESSION GO   BACKUP LOG [<database Name>] TO DISK='<Shared Network Location>\<Database Name>.trn' WITH FORMAT, INIT, COMPRESSION  GO

 

5. Primary 서버에서 가용성 그룹에 데이터베이스를 추가

-- runs on primary server   USE master GO   ALTER AVAILABILITY GROUP [<Availability Group>] ADD DATABASE [<Database Name>];  GO

 

6. Secondary 서버에서 데이터베이스 복원

Secondary에서 가요성 그룹에 추가할 데이터베이스를 전체 백업 및 로그 백업 파일을 이용해서 복원한다.

-- runs on secondary server   USE master GO   RESTORE DATABASE [<database Name>] FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH FILE=1, REPLACE, NORECOVERY GO   RESTORE LOG [<database Name>] FROM DISK='<Shared Network Location>\<Database Name>.trn' WITH FILE=1, REPLACE, NORECOVERY  GO

 

7. 데이터베이스 상태를 확인한 다음 가용성 그룹에 추가

-- runs on secondary server   -- Wait for the replica to start communicating begin try    declare @conn bit    declare @count int    declare @replica_id uniqueidentifier    declare @group_id uniqueidentifier    set @conn = 0    set @count = 30 -- wait for 5 minutes      if (serverproperty('IsHadrEnabled') = 1)       and (isnull((select member_state                    from master.sys.dm_hadr_cluster_members                    where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)       and (isnull((select state                    from master.sys.database_mirroring_endpoints), 1) = 0)    begin       select @group_id = ags.group_id       from master.sys.availability_groups as ags       where name = N'<Availability Group>'         select @replica_id = replicas.replica_id       from master.sys.availability_replicas as replicas       where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id         while @conn <> 1 and @count > 0       begin          set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)          if @conn = 1          begin             -- exit loop when the replica is connected, or if the query cannot find the replica status             break          end          waitfor delay '00:00:10'          set @count = @count - 1       end    end end try   begin catch    -- If the wait loop fails, do not stop execution of the alter database statement end catch   ALTER DATABASE [<Database Name>] SET HADR AVAILABILITY GROUP = [<Availability Group>]; GO

 

지금까지의 위 단계를 각 Primary 서버 및 Secondary 서버에 Job Agent로 추가한다. 순서를 정리하면 아래 표와 같다.

	Primary	Secondary
1	가용성 그룹에서 데이터베이스 제거
2	데이터베이스 복원
3	데이터베이스 복구 모델을 FULL로 설정
4	가용성 그룹에 데이터베이스 추가
5	데이터베이스 전체 백업 및 로그 백업 생성
6	(sp_start_job을 사용하여 보저 서버의 작업을 호출)
7		데이터베이스 백업 및 로그 복원
8		가용성 그룹에 추가

 

Primary 서버에서 Secondary 서버의 작업을 호출하지 않는 경우 Primary 작업이 완료 된 후 Secondary 서버의 작업이 시작되도록 시간을 설정해야 한다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5194/automate-refresh-of-a-sql-server-database-that-is-part-of-an-availability-group/

 

 

2018-11-07 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, AlwaysOn, SQL Server Availability Group, 가용성 그룹