Praca z grupami dostępności AlwaysOn bazy danych Microsoft SQL Server

Grupy dostępności AlwaysOn bazy danych Microsoft SQL Server to wysoce dostępne rozwiązanie odzyskiwania awaryjnego, które stanowi alternatywę na poziomie korporacyjnym dla dublowania baz danych.

Wymagania wstępne

Dane poświadczające służące do łączenia się z indywidualnymi replikami powinny być identyczne z tymi, które są używane do łączenia z obserwatorem AlwaysOn.

Repliki powinny zezwalać na połączenia.

Aby to skonfigurować, administrator bazy danych powinien wykonać następujący skrypt:

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

Konfiguracja Brama danych ruchu danych pod kątem dostępu do obserwatora AlwaysOn

W przypadku pracy z zawsze włączonymi grupami dostępności należy określić adres IP/nazwę hosta i poświadczenia dostępu zawsze włączonej usługi nasłuchiwania. w ustawieniach połączenia.

Ograniczenia

Rozproszone grupy dostępności (DAG) nie są obsługiwane.

Dostęp do dzienników zapasowych w grupach dostępności AlwaysOn

W przeciwieństwie do aktywnych dzienników transakcji, które są synchronizowane na przestrzeni grupy dostępności AlwaysOn, zapasowe dzienniki transakcji są różne dla każdej indywidualnej repliki. W efekcie, kiedy Brama danych ruchu danych potrzebuje dostępu do zapasowych dzienników transakcji, „pyta” każdą replikę po kolei, czy ma wymagane dzienniki aż zlokalizuje zapasowe dzienniki transakcji. Jeśli jedna z replik, do których chce się odwołać Brama danych ruchu danych, jest offline, Brama danych ruchu danych poczeka, aż ta replika znów pojawi się online, a następnie „zapyta” ją o zapasowe dzienniki transakcji.

Brama danych ruchu danych potrzebuje dostępu do zapasowych dzienników transakcji na każdej replice (lub aż znajdzie wymagane dzienniki) w następujących sytuacjach:

Praca w trybie tylko kopii zapasowej.

Aby uzyskać więcej informacji na temat zaawansowanego trybu edycji, zobacz Microsoft SQL Server lokalny (na podstawie dzienników).
Ze względu na opóźnienie, tj. występuje wiele zdarzeń (zmian), których Brama danych ruchu danych nie może przetworzyć przy użyciu tylko aktywnego dziennika.

Wczytywanie dzienników kopii zapasowych z grup dostępności AlwaysOn wymaga otwarcia połączeń z wszystkimi replikami. Jeśli nie chcesz tego robić, ustaw opcję Change processing mode (Tryb przetwarzania zmian) ( właściwości połączenia) na Online Logs only (Tylko dzienniki online).

Konfiguracja użytkownika nie będącego administratorem systemu podczas pracy z grupami dostępności AlwaysOn

Ta sekcja zawiera objaśnienie sposobu konfiguracji użytkownika niebędącego administratorem systemu podczas pracy z grupami dostępności AlwaysOn. Informacje na temat konfiguracji użytkowników nie będących administratorami systemu w standardowym środowisku znajdują się w temacie Konfiguracja użytkownika nie będącego administratorem systemu w samodzielnym środowisku.

Aby obsługiwać użytkownika nie będącego administratorem systemu podczas pracy z grupami dostępności AlwaysOn.

Skonfiguruj Microsoft SQL Server dla Replication zgodnie z opisem w temacie Konfiguracja bazy danych Microsoft SQL Server pod kątem replikacji.
Włącz MS-REPLICATION na źródłowej bazie danych. Można to zrobić ręcznie lub przez uruchomienie zadania podczas korzystania z konta administratora systemu.

Informacja
Dystrybutor MS-REPLICATION powinien być skonfigurowany jako lokalny lub w sposób umożliwiający dostęp do użytkowników nie będących administratorami systemu przez odpowiedni połączony serwer.

Na każdej replice wykonaj następujące czynności (a-l):

Utwórz schemat [attrep] w głównej bazie danych.

Utwórz funkcję zwracającą tabelę [attrep].[split_partition_list] w głównej bazie danych w następujący sposób:

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

Utwórz procedurę [attrep].[rtm_dump_dblog] na głównej bazie danych w następujący sposób:

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

Utwórz certyfikat na głównej bazie danych:

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

Utwórz login z certyfikatu w następujący sposób:

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

Dodaj login do serwerowej roli administratora systemu w następujący sposób:
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];
```
Dodaj podpis do [master].[attrep].[rtm_dump_dblog] przy certyfikacie w następujący sposób:
```
Use [master]
GO
ADD SIGNATURE
TO [master].[attrep].[rtm_dump_dblog]
BY CERTIFICATE [attrep_rtm_dump_dblog_cert]
					WITH PASSWORD = 'choose_your_own_pwd';
```
Informacja
Jeśli zapisana procedura zostanie odtworzona, musisz ponownie dodać podpis.

Utwórz procedurę [attrep].[rtm_position_1st_timestamp] na głównej bazie danych w następujący sposób:

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

Utwórz certyfikat na głównej bazie danych:

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

Utwórz login z certyfikatu w następujący sposób:

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

Dodaj login do serwerowej roli administratora systemu w następujący sposób:

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

Dodaj podpis do [master].[attrep].[rtm_position_1st_timestamp] przy certyfikacie w następujący sposób:
```
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';
```
Informacja
Jeśli zapisana procedura zostanie odtworzona, musisz ponownie dodać podpis.

Utwórz użytkownika z następującymi rolami/uprawnieniami (w każdej z następujących baz danych):

Informacja
Login powinien mieć taki sam SID dla każdej repliki.
- Główna baza danych (na każdej replice):
  - select on sys.fn_dblog
  - view any definition
  - view server state (należy przydzielić loginowi).
  - 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]
- Baza danych MSDB (na każdej replice):
  - select on msdb.dbo.backupset
  - select on msdb.dbo.backupmediafamily
  - select on msdb.dbo.backupfile
- Źródłowa baza danych (ponieważ baza danych jest synchronizowana, można to zrobić tylko na głównej replice):
  - ROLA db_owner

Czy ta strona była pomocna?

Jeżeli natkniesz się na problemy z tą stroną lub jej zawartością — literówkę, brakujący krok lub błąd techniczny — daj nam znać, co możemy poprawić!

Przekaż tu opinię