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 서버의 작업이 시작되도록 시간을 설정해야 한다.
[참고자료]
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]
