Trabajar con grupos de disponibilidad AlwaysOn de Microsoft SQL Server

La función de grupos de disponibilidad AlwaysOn de Microsoft SQL Server es una solución de recuperación ante desastres de alta disponibilidad que proporciona una alternativa de nivel empresarial a la creación de reflejo de la base de datos.

Requisitos previos

Las credenciales utilizadas para conectarse a réplicas individuales deben ser idénticas a las utilizadas para conectarse a AlwaysOn Listener.

Las réplicas deben configurarse para permitir conexiones.

Para configurar esto, el DBA debe ejecutar el siguiente 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 Data Movement gateway para acceder al AlwaysOn Listener

Cuando trabaje con grupos de disponibilidad AlwaysOn, debe especificar la dirección IP/nombre deL host y las credenciales de acceso del agente de escucha AlwaysOn. en las las propiedades de la conexión .

Limitaciones

DAG (Distributed Availability Groups) no es compatible.

Acceder a registros de copia de seguridad en grupos de disponibilidad AlwaysOn

A diferencia de los registros de transacciones activos que se sincronizan en el grupo de disponibilidad AlwaysOn, los registros de transacciones de respaldo son diferentes para cada réplica individual. En consecuencia, cuando Data Movement gateway necesita acceder a los registros de transacciones de respaldo, "pregunta" a cada réplica si tiene los registros requeridos, hasta que se localizan los registros de transacciones de respaldo. Tenga en cuenta que si una de las réplicas a las que Data Movement gateway intenta acceder está fuera de línea, Data Movement gateway esperará hasta que vuelva a estar en línea y luego consultará los registros de transacciones de respaldo.

Data Movement gateway necesita acceder a los registros de transacciones de copia de seguridad en cada Réplica (o hasta que encuentre los registros necesarios) en los siguientes escenarios:

Trabajando en modo de solo copia de seguridad.

Para más información sobre este modo, vea Microsoft SQL Server local (basado en registros).
Debido a la latencia, es decir si hay una alta tasa de eventos (cambios) que Data Movement gateway no puede procesar usando solo el registro activo.

La lectura de registros de copia de seguridad de los grupos de disponibilidad AlwaysOn requiere la apertura de conexiones a todas las réplicas. Si no desea hacer esto, configure el Modo de procesamiento de cambios en las propiedades de conexión de en Solo registros online.

Configurar un usuario que no es administrador del sistema cuando se trabaja con grupos de disponibilidad AlwaysOn

Esta sección explica cómo admitir a un usuario que no es administrador del sistema cuando se trabaja con grupos de disponibilidad AlwaysOn. Para obtener información sobre la configuración de no administradores de sistemas en un entorno estándar, vea Configurar un usuario que no sea administrador del sistema en un entorno independiente.

Para admitir a un usuario que no es administrador del sistema cuando trabaja con grupos de disponibilidad AlwaysOn:

Configure Microsoft SQL Server para Replication como se describe en Configurar Microsoft SQL Server para replicación.
Habilite MS REPLICATION en la base de datos de origen. Esto se puede hacer manualmente o ejecutando la tarea una vez con un usuario administrador del sistema.

Nota informativa
El distribuidor de MS-REPLICATION debe configurarse como local o de una manera que permita el acceso a usuarios que no sean administradores de sistemas a través del servidor vinculado asociado.

En cada réplica realice los siguientes pasos (a-l):

Cree el esquema [attrep] en la base de datos maestra.

Cree la función con valores de tabla [attrep].[split_partition_list] en la base de datos maestra, de la siguiente manera:

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

Cree un procedimiento [attrep].[rtm_dump_dblog] en la base de datos maestra, de la siguiente manera:

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

Cree el certificado en la base de datos maestra:

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

Cree un inicio de sesión desde el certificado de la siguiente manera:

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

Agregue el inicio de sesión al rol de sysadmin del servidor de la siguiente manera:
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];
```
Agregue la firma del certificado a [master].[attrep].[rtm_dump_dblog] de la siguiente manera:
```
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
Si se vuelve a crear el procedimiento almacenado, debe volver a agregar la firma.

Cree un procedimiento [attrep].[rtm_position_1st_timestamp] en la base de datos maestra, de la siguiente manera:

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

Cree el certificado en la base de datos maestra:

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

Cree un inicio de sesión desde el certificado de la siguiente manera:

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

Agregue el inicio de sesión al rol de sysadmin del servidor de la siguiente manera:
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_position_1st_timestamp_login];
```
Agregue la firma del certificado a [master].[attrep].[rtm_position_1st_timestamp] de la siguiente manera:
```
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
Si se vuelve a crear el procedimiento almacenado, debe volver a agregar la firma.

Cree un usuario con los siguientes permisos/roles (en cada una de las siguientes bases de datos):

Nota informativa
El inicio de sesión debe crearse con el mismo SID en cada réplica.
- Master DB (en cada réplica):
  - select on sys.fn_dblog
  - view any definition
  - view server state (debe concederse al inicio de sesión).
  - 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]
- Master DB (en cada réplica):
  - select on msdb.dbo.backupset
  - select on msdb.dbo.backupmediafamily
  - select on msdb.dbo.backupfile
- Para la base de datos de origen (ya que la base de datos está sincronizada, solo se puede hacer en la réplica principal):
  - db_owner ROLE

¿Esta página le ha sido útil?

No dude en indicarnos en qué podemos mejorar si encuentra algún problema en esta página o su contenido, como, por ejemplo, errores tipográficos, pasos que falta o errores técnicos.

Deje aquí sus comentarios