Zu Hauptinhalt springen Zu ergänzendem Inhalt springen
Qlik Ressourcen

Arbeiten mit Microsoft SQL Server AlwaysOn-Verfügbarkeitsgruppen

Die Funktion Microsoft SQL Server AlwaysOn-Verfügbarkeitsgruppen ist eine hochverfügbare Notfall-Wiederherstellungslösung, die eine Alternative für Datenbank-Mirroring auf Enterprise-Ebene bietet.

Voraussetzungen

  • Die Anmeldedaten, die für die Verbindung mit einzelnen Replikaten verwendet werden, müssen mit denen identisch sein, die für die Verbindung zum AlwaysOn Listener verwendet werden.

  • Replikate müssen so konfiguriert werden, dass sie Verbindungen zulassen.

    Um dies zu konfigurieren, muss der DBA folgendes Skript ausführen:

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

Konfigurieren von Data Movement Gateway für den Zugriff auf AlwaysOn Listener

Bei der Nutzung von AlwaysOn-Verfügbarkeitsgruppen müssen Sie die IP-Adresse bzw. den Hostnamen und die Zugangsdaten des AlwaysOn Listener angeben. in den Verbindungseigenschaften.

Einschränkungen

DAG (Distributed Availability Groups) wird nicht unterstützt.

Zugriff auf Sicherungsprotokolle in AlwaysOn-Verfügbarkeitsgruppen

Anders als bei aktiven Transaktionsprotokollen, die über die AlwaysOn-Verfügbarkeitsgruppe hinweg synchronisiert werden, unterscheiden sich Sicherungs-Transaktionsprotokolle für jedes einzelne Replikat. Wenn daher Data Movement Gateway auf die Sicherungs-Transaktionsprotokolle zugreifen muss, wird jedes Replikat einzeln „gefragt“, ob die erforderlichen Protokolle vorhanden sind, bis die Sicherungs-Transaktionsprotokolle gefunden werden. Wenn eines der Replikate, das Data Movement Gateway zu erreichen versucht, offline ist, wartet Data Movement Gateway, bis das Replikat wieder online ist und fragt es dann nach den Sicherungs-Protokolldateien.

Data Movement Gateway muss in den folgenden Szenarios auf die Sicherungs-Protokolldateien auf jedem Replikat zugreifen (bzw. bis die erforderlichen Protokolle gefunden sind):

  • Beim Arbeiten im Nur-Sicherungs-Modus.

    Weitere Informationen zu diesem Modus finden Sie unter Lokaler Microsoft SQL Server (protokollbasiert).

  • Aufgrund der Latenz, also wenn zahlreiche Ereignisse (Änderungen) vorliegen, die Data Movement Gateway nicht nur mit dem aktiven Protokoll verarbeiten kann.
Informationshinweis

Zum Lesen von Sicherungsprotokollen aus AlwaysOn-Verfügbarkeitsgruppen sind eingehende Verbindungen zu allen Replikaten erforderlich. Wenn Sie dies nicht tun möchten, legen Sie Change processing mode in den Verbindungseigenschaften auf Online Logs only fest.

Einrichten eines „non-sysadmin“r-Benutzers bei der Arbeit mit AlwaysOn-Verfügbarkeitsgruppen

In diesem Abschnitt wird erläutert, wie ein „non-sysadmin“-Benutzer bei der Arbeit mit AlwaysOn-Verfügbarkeitsgruppen unterstützt wird. Informationen zum Einrichten eines „non-sysadmin“-Benutzers in einer Standardumgebung finden Sie unter Einrichten eines „non-sysadmin“-Benutzers in einer eigenständigen Umgebung.

So wird ein „non-sysadmin-Benutzer bei der Arbeit mit AlwaysOn-Verfügbarkeitsgruppen unterstützt:

  1. Richten Sie Microsoft SQL Server for Replication wie unter Einrichten von Microsoft SQL Server für Replikation beschrieben ein.

  2. Aktivieren Sie MS-REPLICATION in der Quelldatenbank. Dies kann entweder manuell erfolgen oder indem Sie die Aufgabe einmal als „sysadmin“-Benutzer ausführen.

    Informationshinweis

    Der MS-REPLICATION-Distributor muss entweder lokal oder auf eine Weise konfiguriert sein, dass er „non-sysadmin“-Benutzern den Zugriff über den zugeordneten verlinkten Server ermöglicht.

  1. Führen Sie für jedes Replikat die folgenden Schritte (a-l) aus:
    1. Erstellen Sie das Schema [attrep] in der Master-Datenbank.

    2. Erstellen Sie die Tabellenwertfunktion [attrep].[split_partition_list] in der Master-Datenbank wie folgt:

      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. Erstellen Sie das Verfahren [attrep].[rtm_dump_dblog] in der Master-Datenbank wie folgt:

      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. Erstellen Sie ein Zertifikat in der Master-Datenbank:

      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. Erstellen Sie eine Anmeldung über das Zertifikat wie folgt:

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

    6. Fügen Sie die Anmeldung wie folgt der „sysadmin“-Serverrolle hinzu:

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

    7. Fügen Sie die Signatur wie folgt per Zertifikat zu [master].[attrep].[rtm_dump_dblog] hinzu:

      Use [master]
GO
ADD SIGNATURE
TO [master].[attrep].[rtm_dump_dblog]
BY CERTIFICATE [attrep_rtm_dump_dblog_cert]
					WITH PASSWORD = 'choose_your_own_pwd';
      Informationshinweis

      Wenn die gespeicherte Prozedur erneut erstellt wird, müssen Sie die Signatur erneut hinzufügen.

    8. Erstellen Sie das Verfahren [attrep].[rtm_position_1st_timestamp] in der Master-Datenbank wie folgt:

      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. Erstellen Sie ein Zertifikat in der Master-Datenbank:

      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. Erstellen Sie eine Anmeldung über das Zertifikat wie folgt:

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

    11. Fügen Sie die Anmeldung wie folgt der „sysadmin“-Serverrolle hinzu:

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

    12. Fügen Sie die Signatur wie folgt per Zertifikat zu [master].[attrep].[rtm_position_1st_timestamp] hinzu:

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

      Wenn die gespeicherte Prozedur erneut erstellt wird, müssen Sie die Signatur erneut hinzufügen.

  2. Erstellen Sie einen Benutzer mit den folgenden Berechtigungen/Rollen (in jeder der folgenden Datenbanken):

    Informationshinweis

    Die Anmeldung muss für jedes Replikat mit der gleichen SID erstellt werden.

    • Master-Datenbank (auf jedem Replikat):
      • select on sys.fn_dblog
      • view any definition
      • view server state (muss für die Anmeldung gewährt werden).
      • 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-Datenbank (auf jedem Replikat) :
      • select on msdb.dbo.backupset
      • select on msdb.dbo.backupmediafamily
      • select on msdb.dbo.backupfile
    • Für die Quelldatenbank (da die Datenbank synchronisiert ist, kann dies nur auf dem primären Replikat erfolgen):
      • db_owner ROLE

Hat diese Seite Ihnen geholfen?

Wenn Sie Probleme mit dieser Seite oder ihren Inhalten feststellen – einen Tippfehler, einen fehlenden Schritt oder einen technischen Fehler –, teilen Sie uns bitte mit, wie wir uns verbessern können!

Geben Sie hier Ihr Feedback ab