Microsoft SQL Server AlwaysOn 가용성 그룹 작업

Microsoft SQL Server AlwaysOn 가용성 그룹 기능은 데이터베이스 미러링에 대한 엔터프라이즈 수준 대안을 제공하는 고가용성 재해 복구 솔루션입니다.

전제 조건

개별 복제본에 연결하는 데 사용되는 자격 증명은 AlwaysOn 수신기에 연결하는 데 사용되는 자격 증명과 동일해야 합니다.

연결을 허용하도록 복제본을 구성해야 합니다.

이를 구성하려면 DBA가 다음 스크립트를 실행해야 합니다.

ALTER AVAILABILITY GROUP [{ag_name}]
MODIFY REPLICA ON '{replica_name}'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = 
'TCP://{replica_name}:{Port}'))

AlwaysOn 수신기에 액세스하도록 Data Movement gateway 구성

연결 속성의 AlwaysOn 가용성 그룹을 사용할 때 AlwaysOn 수신기의 IP 주소/호스트 이름 및 액세스 자격 증명을 지정해야 합니다..

제한 사항

DAG(분산 가용성 그룹)는 지원되지 않습니다.

AlwaysOn 가용성 그룹의 백업 로그 액세스

AlwaysOn 가용성 그룹에서 동기화되는 활성 트랜잭션 로그와 달리 백업 트랜잭션 로그는 개별 복제본마다 다릅니다. 따라서 Data Movement gateway가 백업 트랜잭션 로그에 액세스해야 하는 경우 백업 트랜잭션 로그를 찾을 때까지 필요한 로그가 있는지 각 복제본에 "요청"합니다. Data Movement gateway가 연결하려는 복제본 중 하나가 오프라인인 경우 Data Movement gateway는 해당 복제본이 다시 온라인 상태가 될 때까지 기다렸다가 백업 트랜잭션 로그를 쿼리합니다.

Data Movement gateway는 다음 시나리오에서 각 복제본의 백업 트랜잭션 로그에 액세스해야 합니다(또는 필요한 로그를 찾을 때까지).

백업 전용 모드에서 작업 중입니다.

이 모드에 대한 자세한 내용은 온프레미스 Microsoft SQL Server(로그 기반)을 참조하십시오.
지연으로 인해(즉, 활성 로그만 사용하여 Data Movement gateway가 처리할 수 없는 이벤트(변경)의 비율이 높은 경우).

AlwaysOn 가용성 그룹에서 백업 로그를 읽으려면 모든 복제본에 대한 연결을 열어야 합니다. 이 작업을 수행하지 않으려면 연결 속성에서 변경 처리 모드를 온라인 로그 전용으로 설정합니다.

AlwaysOn 가용성 그룹으로 작업할 때 sysadmin이 아닌 사용자 설정

이 섹션에서는 AlwaysOn 가용성 그룹으로 작업할 때 sysadmin이 아닌 사용자를 지원하는 방법에 대해 설명합니다. 표준 환경에서 sysadmin이 아닌을 설정하는 방법에 대한 자세한 내용은 독립 실행형 환경에서 sysadmin이 아닌 사용자 설정을 참조하십시오.

AlwaysOn 가용성 그룹으로 작업할 때 sysadmin이 아닌 사용자를 지원하려면:

Microsoft SQL Server에 복제 설정에 설명된 대로 Microsoft SQL Server에 복제를 설정합니다.
소스 데이터베이스에서 MS-REPLICATION을 활성화합니다. 이는 수동으로 수행하거나 sysadmin 사용자를 사용하여 작업을 한 번 실행하여 수행할 수 있습니다.

정보 메모
MS-REPLICATION 배포자는 로컬로 구성하거나 관련 연결된 서버를 통해 sysadmin이 아닌 사용자에게 액세스를 허용하는 방식으로 구성해야 합니다.

각 복제본에서 다음 단계(a-l)를 수행합니다.

마스터 DB에 스키마 [attrep]를 만듭니다.

다음과 같이 마스터 데이터베이스에서 테이블 값 함수 [attrep].[split_partition_list]를 만듭니다.

USE [master]
GO

set ansi_nulls on
go

set quoted_identifier on
go

if (object_id('[attrep].[split_partition_list]','TF')) is not null
drop  function [attrep].[split_partition_list];
go

create function [attrep].[split_partition_list] 
( 
@plist varchar(8000),	--A delimited list of partitions	
@dlm nvarchar(1)	--Delimiting character
) 
returns @partitionsTable table --Table holding the BIGINT values of the string fragments
(
pid bigint primary key
) 
as 
begin
declare @partition_id bigint;
declare @dlm_pos integer;
declare @dlm_len integer;

set @dlm_len = len(@dlm);

while (charindex(@dlm,@plist)>0)
begin 
set @dlm_pos = charindex(@dlm,@plist);
set @partition_id = cast( ltrim(rtrim(substring(@plist,1,@dlm_pos-1))) as bigint);
insert into @partitionsTable (pid) values (@partition_id)
set @plist = substring(@plist,@dlm_pos+@dlm_len,len(@plist));
end 
set @partition_id = cast (ltrim(rtrim(@plist)) as bigint);
insert into @partitionsTable (pid) values (  @partition_id  );
return
end
GO

다음과 같이 마스터 데이터베이스에서 절차 [attrep].[rtm_dump_dblog]를 만듭니다.

USE [master]
GO

use [MASTER] 
go 

if (object_id('[attrep].[rtm_dump_dblog]','P')) is not null
drop procedure [attrep].[rtm_dump_dblog]; 
go

set ansi_nulls on
go 

set quoted_identifier on 
go
							
create procedure [attrep].[rtm_dump_dblog]
(
@start_lsn        varchar(32),
@seqno            integer,
@filename         varchar(260),
@partition_list        varchar(8000), -- A comma delimited list: P1,P2,... Pn
@programmed_filtering integer,
@minPartition     bigint,
@maxPartition     bigint
) 
as begin

        declare @start_lsn_cmp varchar(32); -- Stands against the GT comparator

        SET NOCOUNT ON  -- Disable "rows affected display"

        set @start_lsn_cmp = @start_lsn;
        if (@start_lsn_cmp) is null
                set @start_lsn_cmp = '00000000:00000000:0000';

        if (@partition_list is null)
        begin
                RAISERROR ('Null partition list waspassed',16,1);
                return
                --set @partition_list = '0,';    -- A dummy which is never matched
        end

        if (@start_lsn) is not null
                set @start_lsn = '0x'+@start_lsn;

if (@programmed_filtering=0)
        SELECT
                [Current LSN],
                [operation],
                [Context],
                [Transaction ID],
                [Transaction Name],
                [Begin Time],
                [End Time],
                [Flag Bits],
                [PartitionID],
                [Page ID],
                [Slot ID],
                [RowLog Contents 0],
                [Log Record],
                [RowLog Contents 1] -- After Image
        FROM
                fn_dump_dblog (
                        @start_lsn, NULL, N'DISK', @seqno, @filename,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default)
        where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS -- This aims for implementing FN_DBLOG based on GT comparator.
        and
        (
          (  [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )
          or
          (  [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')
                and
                ( ( [context]   in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX') )
                and       [PartitionID] in ( select * from master.attrep.split_partition_list (@partition_list,','))
          )
          or
          ([operation] = 'LOP_HOBT_DDL')
        )
else
        SELECT
                [Current LSN],
                [operation],
                [Context],
                [Transaction ID],
                [Transaction Name],
                [Begin Time],
                [End Time],
                [Flag Bits],
                [PartitionID],
                [Page ID],
                [Slot ID],
                [RowLog Contents 0],
                [Log Record],
                [RowLog Contents 1] -- After Image
        FROM
                fn_dump_dblog (
                        @start_lsn, NULL, N'DISK', @seqno, @filename,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default,
                        default, default, default, default, default, default, default)
        where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS -- This aims for implementing FN_DBLOG based on GT comparator.
        and
        (
          (  [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )
          or
          (  [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')
                and
                ( ( [context]   in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX') )
                and           ([PartitionID] is not null) and ([PartitionID] >= @minPartition and [PartitionID]<=@maxPartition)
          )
          or
          ([operation] = 'LOP_HOBT_DDL')
        )
        SET NOCOUNT OFF -- Re-enable "rows affected display"

end
GO

마스터 DB에 인증서를 만듭니다.

Use [master]
Go
CREATE CERTIFICATE [attrep_rtm_dump_dblog_cert]
ENCRYPTION BY PASSWORD = N'choose_your_own_pwd'
WITH SUBJECT = N'Certificate for FN_DUMP_DBLOG Permissions';

다음과 같이 인증서에서 로그인을 만듭니다.

Use [master]
Go
CREATE LOGIN attrep_rtm_dump_dblog_login FROM CERTIFICATE
[attrep_rtm_dump_dblog_cert];

다음과 같이 sysadmin 서버 역할에 로그인을 추가합니다.
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];
```
다음과 같이 인증서로 서명을 [master].[attrep].[rtm_dump_dblog]에 추가합니다.
```
Use [master]
GO
ADD SIGNATURE
TO [master].[attrep].[rtm_dump_dblog]
BY CERTIFICATE [attrep_rtm_dump_dblog_cert]
					WITH PASSWORD = 'choose_your_own_pwd';
```
정보 메모
저장 프로시저가 다시 만들어지면 서명을 다시 추가해야 합니다.

다음과 같이 마스터 데이터베이스에서 절차 [attrep].[rtm_position_1st_timestamp]를 만듭니다.

use [master]
if object_id('[attrep].[rtm_position_1st_timestamp]','P') is not null
DROP PROCEDURE [attrep].[rtm_position_1st_timestamp];
go
create procedure [attrep].[rtm_position_1st_timestamp]
(
@dbname                sysname,      -- Database name
@seqno                 integer,      -- Backup set sequence/position number within file
@filename              varchar(260), -- The backup filename
@1stTimeStamp          varchar(40)   -- The timestamp to position by
) 
as begin

SET NOCOUNT ON       -- Disable "rows affected display"

declare @firstMatching table
(
cLsn varchar(32),
bTim datetime
)

declare @sql nvarchar(4000)
declare @nl                       char(2)
declare @tb                       char(2)
declare @fnameVar                 nvarchar(254) = 'NULL'

set @nl  = char(10); -- New line
set @tb  = char(9)   -- Tab separator

if (@filename is not null)
set @fnameVar = ''''+@filename +''''

set @sql='use ['+@dbname+'];'+@nl+
'select top 1 [Current LSN],[Begin Time]'+@nl+
'FROM fn_dump_dblog (NULL, NULL, NULL, '+ cast(@seqno as varchar(10))+','+ @fnameVar+','+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default,'+@nl+
@tb+'default, default, default, default, default, default, default)'+@nl+
'where operation=''LOP_BEGIN_XACT''' +@nl+
'and [Begin Time]>= cast('+''''+@1stTimeStamp+''''+' as datetime)'+@nl

--print @sql
delete from  @firstMatching 
insert into @firstMatching  exec sp_executesql @sql    -- Get them all

select top 1 cLsn as [matching LSN],convert(varchar,bTim,121) as [matching Timestamp] from @firstMatching;

SET NOCOUNT OFF      -- Re-enable "rows affected display"

end
GO

마스터 DB에 인증서를 만듭니다.

Use [master]
Go
CREATE CERTIFICATE [attrep_rtm_position_1st_timestamp_cert]
ENCRYPTION BY PASSWORD = N'choose_your_own_pwd'
WITH SUBJECT = N'Certificate for FN_POSITION_1st_TIMESTAMP Permissions';

다음과 같이 인증서에서 로그인을 만듭니다.

Use [master]
Go
CREATE LOGIN attrep_rtm_position_1st_timestamp_login FROM CERTIFICATE
[attrep_rtm_position_1st_timestamp_cert];

다음과 같이 sysadmin 서버 역할에 로그인을 추가합니다.

ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_position_1st_timestamp_login];

다음과 같이 인증서로 서명을 [master].[attrep].[rtm_position_1st_timestamp]에 추가합니다.
```
Use [master]
GO
ADD SIGNATURE
TO [master].[attrep].[rtm_position_1st_timestamp]
BY CERTIFICATE [attrep_rtm_position_1st_timestamp_cert]
WITH PASSWORD = 'choose_your_own_pwd';
```
정보 메모
저장 프로시저가 다시 만들어지면 서명을 다시 추가해야 합니다.

다음 각 데이터베이스에서 다음 권한/역할을 가진 사용자를 만듭니다.

정보 메모
각 복제본에서 동일한 SID로 로그인을 만들어야 합니다.
- 마스터 DB(각 복제본에서):
  - select on sys.fn_dblog
  - view any definition
  - view server state(로그인에 부여되어야 함).
  - execute on sp_repldone
  - execute on sp_replincrementlsn
  - execute on sp_addpublication
  - execute on sp_addarticle
  - execute on sp_articlefilter
  - select on [attrep].[split_partition_list]
  - execute on [attrep].[rtm_dump_dblog]
  - execute on [attrep].[rtm_position_1st_timestamp]
- MSDB DB(각 복제본에서):
  - select on msdb.dbo.backupset
  - select on msdb.dbo.backupmediafamily
  - select on msdb.dbo.backupfile
- 소스 데이터베이스의 경우(데이터베이스가 동기화되므로 기본 복제본에서만 수행할 수 있음):
  - db_owner ROLE

이 페이지가 도움이 되었습니까?

이 페이지 또는 해당 콘텐츠에서 오타, 누락된 단계 또는 기술적 오류와 같은 문제를 발견하면 개선 방법을 알려 주십시오!

여기에 피드백을 남겨주십시오.