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)) COLLATELatin1_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 frommaster.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, 가용성 그룹



출처: https://sqlmvp.tistory.com/1277?category=618825 [Database Lab]




profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.