Vai al contenuto principale Passa a contenuto complementare
Risorse Qlik

Lavorare con i gruppi di disponibilità AlwaysOn di Microsoft SQL Server

La funzione Gruppi di disponibilità AlwaysOn di Microsoft SQL Server è una soluzione ad alta disponibilità e di ripristino d'emergenza che offre un'alternativa di livello aziendale al mirroring dei database.

Prerequisiti

  • Le credenziali utilizzate per la connessione alle singole repliche devono essere identiche a quelle utilizzate per la connessione all'AlwaysOn Listener.

  • Le repliche devono essere configurate per consentire le connessioni.

    Per configurarlo, il DBA deve eseguire il seguente 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}'))

Configurazione di gateway di Data Movement per accedere al listener AlwaysOn

Quando si lavora con i gruppi di disponibilità AlwaysOn, è necessario specificare l'indirizzo IP/nome host e le credenziali di accesso di AlwaysOn Listener nelle Microsoft SQL Server source connettore settings.

Limitazioni

Il DAG (Distributed Availability Groups) non è supportato.

Accesso ai registri di backup nei gruppi di disponibilità AlwaysOn

A differenza dei registri delle transazioni attivi, che sono sincronizzati in tutto il gruppo di disponibilità AlwaysOn, i registri delle transazioni di backup sono diversi per ogni singola replica. Di conseguenza, quando gateway di Data Movement ha bisogno di accedere ai registri delle transazioni di backup, "chiede" a ciascuna replica a turno se dispone dei registri necessari, finché non vengono individuati i registri delle transazioni di backup. Si noti che se una delle repliche che gateway di Data Movement sta cercando di raggiungere è offline, gateway di Data Movement aspetterà fino a quando la replica non tornerà online e quindi la interrogherà per ottenere i registri delle transazioni di backup.

gateway di Data Movement deve accedere ai registri delle transazioni di backup su ogni Replica (o finché non trova i registri richiesti) nei seguenti scenari:

  • Utilizzo in modalità solo backup.

    Per ulteriori informazioni su questa modalità, vedere Microsoft SQL Server (basato su registro).

  • A causa della latenza, cioè se è presente un'elevata frequenza di eventi (modifiche) che gateway di Data Movement non riesce a elaborare usando solo il registro attivo.
Nota informatica

La lettura dei registri di backup dai gruppi di disponibilità AlwaysOn richiede l'apertura di connessioni a tutte le repliche. Se non si desidera compiere questa operazione, impostare Cambia modalità di elaborazione nelle proprietà di connessione su Solo registri online.

Impostazione di un utente non amministratore quando si lavora con i gruppi di disponibilità AlwaysOn

Questa sezione spiega come supportare un utente non amministratore di sistema quando si lavora con i gruppi di disponibilità AlwaysOn. Per informazioni sull'impostazione di non-sysadmin in un ambiente standard, vedere Impostazione di un utente non-sysadmin in un ambiente standalone.

Per supportare un utente non amministratore quando si lavora con i gruppi di disponibilità AlwayOn:

  1. Configurare Microsoft SQL Server per la replica come descritto in Configurazione di Microsoft SQL Server per la replica.

  2. Abilitare MS-REPLICATION sul database di origine. Questa operazione può essere eseguita manualmente o eseguendo l'attività una volta con un utente sysadmin.

    Nota informatica

    Il distributore MS-REPLICATION deve essere configurato come impostazione locale o in modo da consentire l'accesso agli utenti non amministratori tramite il server associato.

  1. Su ogni replica eseguire i seguenti passaggi (a-l):
    1. Creare lo schema [attrep] nel DB principale.

    2. Creare la funzione con valori di tabella [attrep].[split_partition_list] nel database principale come descritto di seguito:

      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

    3. Creare la procedura [attrep].[rtm_dump_dblog] nel database come descritto di seguito:

      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

    4. Creare il certificato sul DB principale:

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

    5. Creare il login dal certificato come segue:

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

    6. Aggiungere il login al ruolo di server sysadmin come segue:

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

    7. Aggiungere la firma a [master].[attrep].[rtm_dump_dblog] in base al certificato come descritto di seguito:

      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 informatica

      Se la procedura archiviata viene ricreata, è necessario aggiungere nuovamente la firma.

    8. Creare la procedura [attrep].[rtm_position_1st_timestamp] nel database come descritto di seguito:

      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

    9. Creare il certificato sul DB principale:

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

    10. Creare il login dal certificato come segue:

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

    11. Aggiungere il login al ruolo di server sysadmin come segue:

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

    12. Aggiungere la firma a [master].[attrep].[rtm_position_1st_timestamp] in base al certificato come descritto di seguito:

      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 informatica

      Se la procedura archiviata viene ricreata, è necessario aggiungere nuovamente la firma.

  2. Creare un utente con le seguenti autorizzazioni/ruoli (in ciascuno dei seguenti database):

    Nota informatica

    Il login deve essere creato con lo stesso SID su ogni replica.

    • DB master (su ogni replica):
      • select on sys.fn_dblog
      • view any definition
      • view server state (dovrebbe essere concesso all'accesso).
      • 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]
    • DB MSFB (su ogni replica):
      • select on msdb.dbo.backupset
      • select on msdb.dbo.backupmediafamily
      • select on msdb.dbo.backupfile
    • Per il database di origine (la sincronizzazione del database può essere effettuata solo sulla replica primaria):
      • db_owner ROLE

Hai trovato utile questa pagina?

Se riscontri problemi con questa pagina o con il suo contenuto – un errore di battitura, un passaggio mancante o un errore tecnico – facci sapere come possiamo migliorare!

Lascia qui il tuo feedback