데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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]
No. Subject Author Date Views
2170 SQL Server 프로토콜과 SQLCMD를 사용한 연결 jevida(강성욱) 2019.04.23 1598
2169 SQL Server Edition 다운그레이드 후 확인사항 [1] jevida(강성욱) 2019.04.23 1503
2168 AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기 jevida(강성욱) 2019.04.23 894
2167 SSRS에서 E-mail 세팅시 SSL 사용 유무 수정하기 jevida(강성욱) 2019.03.26 863
2166 SQL Linux Instance Name 변경 jevida(강성욱) 2019.03.26 741
2165 SQL Linux 업그레이드 jevida(강성욱) 2019.03.26 988
2164 Azure SQL Managed Instance 에서 SQL Agent의 Job history 기록 보관하기 jevida(강성욱) 2019.03.26 849
2163 Azure VM에서 SQL Server에 대한 저장소 구성 지침 jevida(강성욱) 2019.03.26 860
2162 Azure SQL에 도입된 새로운 집계 함수 APPROX_COUNT_DISTINCT jevida(강성욱) 2019.03.26 647
2161 Azure SQL의 데이터베이스 소유권 체인 jevida(강성욱) 2019.03.26 763
2160 Azure SQL의 내부 디스크 할당 방법 및 저장 가능 용량 확인 jevida(강성욱) 2019.03.26 785
2159 Azure SQL에서 이메일 보내기 jevida(강성욱) 2019.03.26 650
2158 Azure SQL에서 읽기 전용 복제본에 대한 접속 및 링크 서버 생성 jevida(강성욱) 2019.03.26 770
2157 Azure SQL에서 네트워크를 구성하는 방법 jevida(강성욱) 2019.03.26 496
2156 인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block jevida(강성욱) 2019.03.26 742
2155 SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상 jevida(강성욱) 2019.03.26 625
» SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기 jevida(강성욱) 2019.03.26 611
2153 SQL Server MySQL PostgreSQL 비교 jevida(강성욱) 2019.03.26 1049
2152 SQL Server 복잡한 쿼리가 옵티마이저에 미치는 영향과 옵티마이저 timeout jevida(강성욱) 2019.03.26 624
2151 SQL Server 2019 에서 문자열 잘림에 대한 향상된 에러 메시지 반환 jevida(강성욱) 2019.03.26 436





XE Login