Utilisation des groupes de disponibilité AlwaysOn de Microsoft SQL Server

La fonctionnalité des groupes de disponibilité Microsoft SQL Server AlwaysOn est une solution de haute disponibilité et de reprise après sinistre qui constitue une alternative à la mise en miroir des bases de données au niveau de l'entreprise.

Dans un Availability Group (Groupe de disponibilité), il est conseillé d'utiliser une configuration de distributeur à distance. Cela permet un basculement en toute transparence entre les répliques. Pour plus d'informations sur la configuration d'un distributeur à distance, consultez Configuration de la publication et de la distribution.

Prérequis

Les informations d'identification utilisées pour se connecter aux répliques individuelles doivent être identiques à celles utilisées pour se connecter à l'AlwaysOn Listener.

Les répliques doivent être configurées pour permettre les connexions.

Pour configurer cela, le DBA doit exécuter le script suivant :

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

Configuration de Data Movement gateway pour accéder à l'AlwaysOn Listener

Lors de l'utilisation de groupes de disponibilité AlwaysOn, vous devez indiquer l'adresse IP/le nom d'hôte ainsi que les informations d'identification pour l'accès d'AlwaysOn Listener. dans les propriétés de connexion.

Limitations

Les Groupes de disponibilité distribués (DAG) ne sont pas supportés.

Accès aux journaux de sauvegarde dans les groupes de disponibilité AlwaysOn

Contrairement aux journaux de transactions actifs qui sont synchronisés dans le groupe de disponibilité AlwaysOn, les journaux de transactions de sauvegarde sont différents pour chaque réplique individuelle. Ainsi, quand Data Movement gateway a besoin d'accéder aux journaux de transactions de sauvegarde, il demande à chaque réplique à tour de rôle si elle dispose des journaux requis, jusqu'à ce que les journaux de transactions de sauvegarde soient localisés. Remarque si l'une des répliques que Data Movement gateway tente d'atteindre est hors ligne, Data Movement gateway attendra que cette réplique soit de nouveau en ligne et lui demandera les journaux de transactions de la sauvegarde.

Data Movement gateway doit accéder aux journaux des transactions de sauvegarde sur chaque Réplique (ou jusqu'à ce qu'il trouve les journaux requis) dans les scénarios suivants :

Fonctionnement en mode sauvegarde uniquement.

Pour plus d'informations sur ce mode, voir On-premises Microsoft SQL Server (basé sur le journal).
En raison de la latence, c'est-à-dire s'il y a un taux élevé d'événements (changements) que Data Movement gateway ne peut être traités en utilisant uniquement le journal actif.

La lecture des journaux de sauvegarde des groupes de disponibilité AlwaysOn nécessite l'ouverture de connexions à toutes les répliques. Si vous ne souhaitez pas procéder ainsi, définissez le Mode de traitement des modifications dans Propriétés de connexion sur Journaux en ligne uniquement.

Configuration d'un utilisateur non-sysadmin lors de l'utilisation des groupes de disponibilité AlwaysOn

Cette section explique comment prendre en charge un utilisateur non sysadmin lors de l'utilisation de groupes de disponibilité AlwaysOn. Pour plus d'informations sur la configuration de la fonction non-sysadmin dans un environnement standard, voir Configuration d'un utilisateur non sysadmin dans un environnement autonome.

Pour permettre à un utilisateur non-sysadmin à travailler avec des groupes de disponibilité AlwayOn :

Configurez Microsoft SQL Server pour Replication comme indiqué dans Configuration de Microsoft SQL Server pour la réplication.
Activez MS-REPLICATION sur la base de données source. Cela peut être fait manuellement ou en exécutant la tâche une fois en utilisant un utilisateur sysadmin.

Note Informations
Le distributeur MS-REPLICATION doit être configuré soit comme local, soit de manière à permettre l'accès aux utilisateurs non sysadmin via le serveur lié associé.

Sur chaque réplique, effectuez les étapes suivantes (a-l) :

Créez le schéma [attrep] dans la base de données principale Master DB.

Créez la fonction valorisée par la table [attrep].[split_partition_list] sur la base de données principale Master DB, comme suit :

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

Créez la procédure [attrep].[rtm_dump_dblog] sur la base de données principale Master DB, comme suit :

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

Créez un certificat sur la base de données principale Master DB :

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

Créez un identifiant à partir du certificat, comme suit :

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

Ajoutez l'identifiant au rôle de serveur sysadmin comme suit :

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

Ajoutez la signature à [master].[attrep].[rtm_dump_dblog] par le certificat, comme suit :
```
Use [master]
GO
ADD SIGNATURE
TO [master].[attrep].[rtm_dump_dblog]
BY CERTIFICATE [attrep_rtm_dump_dblog_cert]
					WITH PASSWORD = 'choose_your_own_pwd';
```
Note Informations
Si la procédure stockée est recréée, vous devez à nouveau ajouter la signature.

Créez la procédure [attrep].[rtm_position_1st_timestamp] sur la base de données principale Master DB, comme suit :

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

Créez un certificat sur la base de données principale Master DB :

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

Créez un identifiant à partir du certificat, comme suit :

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

Ajoutez l'identifiant au rôle de serveur sysadmin comme suit :

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

Ajoutez la signature à [master].[attrep].[rtm_position_1st_timestamp] par le certificat, comme suit :
```
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';
```
Note Informations
Si la procédure stockée est recréée, vous devez à nouveau ajouter la signature.

Créez un utilisateur avec les autorisations/rôles suivants (dans chacune des bases de données suivantes) :

Note Informations
L'identifiant doit être créé avec le même SID sur chaque réplique.
- Base de données principale Master DB (sur chaque réplique) :
  - select on sys.fn_dblog
  - view any definition
  - view server state (accordé à l'identifiant).
  - 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]
- BD MSDB (sur chaque réplique) :
  - select on msdb.dbo.backupset
  - select on msdb.dbo.backupmediafamily
  - select on msdb.dbo.backupfile
- Pour la base de données source (comme la base de données est synchronisée, cela ne peut être fait que sur la réplique primaire) :
  - db_owner ROLE
Pour activer Qlik Talend Data Integration afin de créer la publication sans autorité sysadmin, activez l'indicateur de publication et précréez la tâche de lecteur de journal :

USE master

EXEC sp_replicationdboption @dbname = '<databaseName>', @optname = 'publish', @value = 'true'

EXEC <databaseName>.sys.sp_addlogreader_agent

Cette page vous a-t-elle aidé ?

Si vous rencontrez des problèmes sur cette page ou dans son contenu – une faute de frappe, une étape manquante ou une erreur technique – faites-le-nous savoir.

Laissez vos commentaires ici