Trabalhar com Grupos de disponibilidade AlwaysOn do Microsoft SQL Server

O recurso Grupos de disponibilidade AlwaysOn do Microsoft SQL Server é uma solução de recuperação de desastres de alta disponibilidade que fornece uma alternativa de nível corporativo ao espelhamento de banco de dados.

Pré-requisitos

As credenciais usadas para conexão com réplicas individuais devem ser idênticas às usadas para conexão com o Listener AlwaysOn.

Réplicas devem ser configuradas para permitir conexões.

Para configurar isso, o DBA deve executar o seguinte script:

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

Configurar o Gateway Data Movement para acessar o Listener AlwaysOn

Ao trabalhar com grupos de disponibilidade AlwaysOn, você precisa especificar o endereço IP/nome do host e as credenciais de acesso do Listener AlwaysOn nas Microsoft SQL Server source conector settings.

Limitações

O recurso DAG (Grupos de disponibilidade distribuídos) não tem suporte.

Acessar logs de backup em grupos de disponibilidade do AlwaysOn

Ao contrário dos logs de transações ativas que são sincronizados no Grupo de disponibilidade AlwaysOn, os logs de transações de backup são diferentes para cada réplica individual. Consequentemente, quando o Gateway Data Movement precisa acessar os logs de transações de backup, ele "pergunta" a cada réplica alternadamente se ela tem os logs necessários, até que os logs de transações de backup sejam localizados. Observe que se uma das Réplicas que o Gateway Data Movement estiver tentando acessar estiver offline, o Gateway Data Movement aguardará até que a réplica volte a ficar online e, em seguida, consultará os logs de transações de backup.

O Gateway Data Movement precisa acessar os logs de transações de backup em cada réplica (ou até encontrar os logs necessários) nos seguintes cenários:

Trabalhando somente no modo de backup.

Para obter mais informações sobre esse modo, consulte Microsoft SQL Server local (baseado em log).
Devido à latência, ou seja, se houver alta taxa de eventos (alterações) que o Gateway Data Movement não consiga processar usando apenas o log ativo.

A leitura dos logs de backup dos Grupos de disponibilidade AlwaysOn exige a abertura de conexões com todas as réplicas. Se você não quiser fazer isso, defina o Modo de processamento de alterações nas propriedades de conexão como Somente logs online.

Configurar um usuário não administrador do sistema ao trabalhar com grupos de disponibilidade AlwaysOn

Esta seção explica como oferecer suporte a um usuário não administrador do sistema ao trabalhar com grupos de disponibilidade AlwaysOn. Para obter informações sobre como configurar um usuário não administrador do sistema em um ambiente padrão, consulte Configurando um usuário não administrador do sistema em um ambiente autônomo.

Para oferecer suporte a um usuário não administrador do sistema ao trabalhar com grupos de disponibilidade AlwayOn:

Configure o Microsoft SQL Server para replicação conforme descrito em Configurando o Microsoft SQL Server para replicação.
Ative MS-REPLICATION no banco de dados de origem. Isso pode ser feito manualmente ou executando a tarefa uma vez usando um usuário sysadmin.

Nota informativa
O distribuidor MS-REPLICATION deve ser configurado como local ou de uma forma que permita acesso a usuários que não sejam administradores do sistema por meio do servidor vinculado associado.

Em cada réplica, execute as seguintes etapas (a-l):

Crie um esquema [attrep] no banco de dados principal.

Crie a função com valores de tabela [attrep].[split_partition_list] no banco de dados principal, da seguinte forma:

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

Crie o procedimento [attrep].[rtm_dump_dblog] no banco de dados principal, da seguinte forma:

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

Crie um certificado no banco de dados principal:

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';

Crie o login a partir do certificado, da seguinte maneira:

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

Adicione o login à função de servidor sysadmin, da seguinte maneira:
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];
```
Adicione a assinatura a [master].[attrep].[rtm_dump_dblog] ao certificado, da seguinte forma:
```
Use [master]
GO
ADD SIGNATURE
TO [master].[attrep].[rtm_dump_dblog]
BY CERTIFICATE [attrep_rtm_dump_dblog_cert]
					WITH PASSWORD = 'choose_your_own_pwd';
```
Nota informativa
Se o procedimento armazenado for recriado, você precisará adicionar a assinatura novamente.

Crie o procedimento [attrep].[rtm_position_1st_timestamp] no banco de dados principal, da seguinte forma:

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

Crie um certificado no banco de dados principal:

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';

Crie o login a partir do certificado, da seguinte maneira:

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

Adicione o login à função de servidor sysadmin, da seguinte maneira:

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

Adicione a assinatura a [master].[attrep].[rtm_position_1st_timestamp] ao certificado, da seguinte forma:
```
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';
```
Nota informativa
Se o procedimento armazenado for recriado, você precisará adicionar a assinatura novamente.

Crie um usuário com as seguintes permissões/funções (em cada um dos seguintes bancos de dados):

Nota informativa
O login deve ser criado com o mesmo SID em cada réplica.
- Banco de dados principal (em cada réplica):
  - select on sys.fn_dblog
  - view any definition
  - view server state (deve ser concedida ao login).
  - 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 (em cada réplica):
  - select on msdb.dbo.backupset
  - select on msdb.dbo.backupmediafamily
  - select on msdb.dbo.backupfile
- Para o banco de dados de origem (quando o banco de dados é sincronizado, isso pode ser feito somente na réplica primária):
  - db_owner ROLE

Esta página ajudou?

Se você encontrar algum problema com esta página ou seu conteúdo - um erro de digitação, uma etapa ausente ou um erro técnico - informe-nos como podemos melhorar!

Deixe seu feedback aqui