Ga naar hoofdinhoud Ga naar aanvullende inhoud
Qlik-bronnen

Werken met Microsoft SQL Server AlwaysOn-beschikbaarheidsgroepen

De functie Microsoft SQL Server AlwaysOn-beschikbaarheidsgroepen is een oplossing met hoge beschikbaarheid voor noodherstel dat geschikt is voor ondernemingen als alternatief voor database-mirroring.

Vereisten

  • De referenties die worden gebruikt voor het verbinden met afzonderlijke replica's moeten identiek zijn aan de replica's die worden gebruikt voor het verbinden met de AlwaysOn Listener.

  • Replica's moeten worden geconfigureerd om verbindingen toe te staan.

    Voor de configuratie moet de DBA het volgende script uitvoeren:

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

Data Movement gateway configureren voor toegang tot de AlwaysOn Listener

Bij het werken met AlwaysOn-beschikbaarheidsgroepen, moet u het IP-adres of hostnaam en de toegangsgegevens van de AlwaysOn Listener opgeven. in de verbindingseigenschappen.

Beperkingen

DAG (Distributed Availability Groups - gedistribueerde beschikbaarheidsgroepen) wordt niet ondersteund.

Toegang verkrijgen tot back-uplogboeken in AlwaysOn-beschikbaarheidsgroepen

In tegenstelling tot actieve transactielogboeken die binnen de AlwaysOn-beschikbaarheidsgroep worden gesynchroniseerd, zijn back-up transactielogboeken verschillend voor elke afzonderlijke replica. Bovendien moet Data Movement gateway in staat zijn om de back-up transactielogboeken te openen, deze "vraagt" elke replica of deze over de vereiste logboeken beschikt, totdat de back-up transactielogboeken zijn gevonden. Als een van de replica's die Data Movement gateway probeert te bereiken offline is, zal Data Movement gateway wachten totdat deze replica weer online is en dan vragen om de back-up transactielogboeken.

Data Movement gateway moet de back-up transactielogboeken voor elke replicatie kunnen openen (of totdat deze de vereiste logboeken vindt) in de volgende scenario's:

  • Werken in de modus Alleen back-up.

    Zie On-premises Microsoft SQL Server (op basis van logboek) voor meer informatie over deze modus.

  • Vanwege latentie, dat wil zeggen wanneer er veel gebeurtenissen (wijzigingen) plaatsvinden die Data Movement gateway niet kan verwerken met alleen het actieve logboek.
Informatie

Voor het lezen van back-uplogboeken van AlwaysOn-beschikbaarheidsgroepen moeten verbindingen naar alle replica's worden geopend. Als u dit niet wilt, moet u de Verwerkingsmodus wijzigen in de verbindingseigenschappen instellen op Alleen online logboeken.

Een niet-SysAdmingebruiker instellen bij het werken met AlwaysOn-beschikbaarheidsgroepen

Deze sectie legt uit hoe u ondersteuning biedt aan een niet-SysAdmingebruiker bij het werken met AlwaysOn-beschikbaarheidsgroepen. Voor informatie over het instellen van een niet-SysAdmin in een standaardomgeving raadpleegt u Een niet-SysAdmingebruiker instellen in een zelfstandige omgeving.

Voor het bieden van ondersteuning aan een niet-SysAdmingebruiker bij het werken met AlwaysOn-beschikbaarheidsgroepen:

  1. Stel Microsoft SQL Server for Replication in zoals beschreven in Microsoft SQL Server voor replication instellen.

  2. Schakel MS-REPLICATION in voor de brondatabase. Dit kunt u handmatig doen of door eenmalig de taak uit te voeren met een SysAdmingebruiker.

    Informatie

    De MS-REPLICATION distributeur moet lokaal worden geconfigureerd of zo dat toegang tot niet-SysAdmingebruikers via de gekoppelde server is toegestaan.

  1. Voer voor elke replica de volgende stappen (a-l) uit:
    1. Maak schema [attrep] in de masterdatabase.

    2. Maak de tabelfunctie [attrep].[split_partition_list] in de masterdatabase als volgt aan: 

      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. Maak procedure [attrep].[rtm_dump_dblog] in de masterdatabase als volgt aan:

      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. Maak een certificaat in de masterdatabase:

      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. Maak aanmeldgegevens op basis van het certificaat:

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

    6. Voeg de aanmeldgegevens toe aan de serverrol SysAdmin:

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

    7. Voeg de handtekening als volgt toe aan [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';
      Informatie

      Als de opgeslagen procedure opnieuw is gemaakt, moet u de handtekening opnieuw toevoegen.

    8. Maak procedure [attrep].[rtm_position_1st_timestamp] in de masterdatabase als volgt aan:

      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. Maak een certificaat in de masterdatabase:

      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. Maak aanmeldgegevens op basis van het certificaat:

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

    11. Voeg de aanmeldgegevens toe aan de serverrol SysAdmin:

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

    12. Voeg de handtekening als volgt toe aan [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';
      Informatie

      Als de opgeslagen procedure opnieuw is gemaakt, moet u de handtekening opnieuw toevoegen.

  2. Maak een gebruiker met de volgende machtigingen/rollen (in elk van de volgende databases):

    Informatie

    De aanmeldgegevens moeten worden gemaakt met dezelfde SID voor elke replica:

    • Masterdatabase (voor elke replica):
      • select on sys.fn_dblog
      • view any definition
      • view server state (moet worden toegewezen voor het aanmelden).
      • 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-database (voor elke replica):
      • select on msdb.dbo.backupset
      • select on msdb.dbo.backupmediafamily
      • select on msdb.dbo.backupfile
    • Voor de brondatabase (aangezien de database wordt gesynchroniseerd, hoeft dit alleen voor de primaire replica):
      • db_owner ROLE

Was deze pagina nuttig?

Als u problemen ervaart op deze pagina of de inhoud onjuist is – een typfout, een ontbrekende stap of een technische fout – laat het ons weten zodat we dit kunnen verbeteren!

Geef hier uw feedback