Google utilise la technologie IA pour traduire le contenu dans votre langue préférée. Les traductions générées par IA peuvent contenir des erreurs.

Configurer une base de données SQL Server autogérée pour la CDC

Pour diffuser les mises à jour de la capture de données modifiées (CDC) à partir d'une base de données SQL Server autogérée avec Datastream, vous pouvez utiliser des tables de modifications ou des journaux de transactions. Les deux méthodes répliquent les modifications vers des destinations compatibles, telles que BigQuery ou Cloud Storage. Avant de créer un flux, vous devez activer la CDC et l'isolation des instantanés, exécuter l'agent SQL Server, créer un utilisateur Datastream et configurer la conservation des journaux.

Configurer votre base de données source

Pour configurer votre base de données source :

Assurez-vous que votre base de données utilise le modèle de récupération complète. Connectez-vous à la base de données et exécutez la commande suivante à une invite SQL ou dans un terminal :
```
USE master
GO
ALTER DATABASE [DATABASE_NAME] SET RECOVERY FULL
GO
```
Remplacez DATABASE_NAME par le nom de votre base de données source.

Remarque : Après avoir défini le modèle de récupération sur FULL, la base de données reste dans un état de récupération pseudo-simple jusqu'à ce que vous effectuiez une sauvegarde complète de la base de données. Vous devez effectuer une sauvegarde complète de la base de données avant que le journal des transactions ne commence à conserver les enregistrements pour la capture de données modifiées.
Activez la CDC pour votre base de données source. Pour ce faire, connectez-vous à la base de données et exécutez la commande suivante dans une invite SQL ou un terminal :
```
USE [DATABASE_NAME]
GO
EXEC sys.sp_cdc_enable_db
GO
```
Remplacez DATABASE_NAME par le nom de votre base de données source.
Activez la CDC sur les tables pour lesquelles vous devez capturer les modifications :
```
USE [DATABASE_NAME]
EXEC sys.sp_cdc_enable_table
@source_schema = N'SCHEMA_NAME',
@source_name = N'TABLE_NAME',
@role_name = NULL
GO
```
Remarque : Vous devez exécuter la commande pour chaque table pour laquelle vous souhaitez activer la CDC.

Remplacez les éléments suivants :
- DATABASE_NAME : nom de votre base de données source
- SCHEMA_NAME : nom du schéma auquel appartiennent les tables
- TABLE_NAME : nom de la table pour laquelle vous souhaitez activer la CDC
Démarrez l'agent SQL Server et assurez-vous qu'il s'exécute en permanence. Si l'agent SQL Server reste indisponible pendant une période prolongée, les journaux peuvent être tronqués, ce qui entraîne une perte permanente des données de modification qui n'ont pas été lues par Datastream.

Pour en savoir plus sur l'exécution de l'agent SQL Server, consultez Démarrer, arrêter ou redémarrer une instance de l'agent SQL Server.

Remarque : Nous vous recommandons d'activer le redémarrage automatique pour le service de l'agent SQL Server afin d'éviter que l'agent ne soit indisponible pendant une période prolongée.
Activez l'isolation d'instantané.

Lorsque vous effectuez un backfill des données de votre base de données SQL Server, il est important de garantir la cohérence des instantanés. Si vous n'appliquez pas les paramètres décrits dans cette section, les modifications apportées à la base de données pendant le processus de backfill peuvent entraîner des doublons ou des résultats incorrects. Vous devez appliquer les paramètres d'isolation d'instantané si votre flux inclut des tables sans clé primaire.

L'activation de l'isolation d'instantané crée une vue temporaire de votre base de données au début du processus de remplissage. Cela garantit que les données copiées restent cohérentes, même si d'autres utilisateurs modifient les tables actives en même temps. L'activation de l'isolation d'instantané peut avoir un léger impact sur les performances, mais elle est essentielle pour une extraction de données fiable.

Pour activer l'isolation d'instantané :
1. Connectez-vous à votre base de données à l'aide d'un client SQL Server.
2. Exécutez la commande suivante :
```
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
```
Remplacez DATABASE_NAME par le nom de votre base de données.
Créez un utilisateur Datastream :
1. Connectez-vous à la base de données source et saisissez la commande suivante :
```
USE DATABASE_NAME;
```
2. Créez un identifiant à utiliser lors de la configuration du profil de connexion dans Datastream.
```
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
```
3. Créez un compte utilisateur :
```
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```
4. Attribuez-leur le rôle db_datareader :
```
EXEC sp_addrolemember 'db_datareader', 'USER_NAME';
```
5. Accordez-lui l'autorisation VIEW DATABASE STATE :
```
GRANT VIEW DATABASE STATE TO USER_NAME;
```
6. Ajoutez cet utilisateur à la base de données master :
```
USE master;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```

Étapes supplémentaires requises pour la méthode CDC des journaux de transactions

Les étapes décrites dans cette section ne sont requises que lorsque vous configurez votre base de données SQL Server source pour l'utiliser avec la méthode CDC des journaux de transactions.

Connectez-vous à la base de données source et attribuez les rôles db_owner et db_denydatawriter à votre utilisateur :

USE DATABASE_NAME;
EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';

Accordez les autorisations SELECT pour la fonction sys.fn_dblog.

USE master;
GRANT SELECT ON sys.fn_dblog TO USER_NAME;

Ajoutez votre utilisateur à la base de données msdb et attribuez-lui les autorisations suivantes :

USE msdb;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
GRANT SELECT ON dbo.sysjobs TO USER_NAME;

Attribuez les autorisations suivantes à votre utilisateur dans la base de données master :
```
  USE master;
  GRANT VIEW SERVER STATE TO YOUR_LOGIN;
```
Définissez l'intervalle d'interrogation pour lequel vous souhaitez que les modifications soient disponibles sur votre source.
```
USE [DATABASE_NAME]
EXEC sys.sp_cdc_change_job @job_type = 'capture' , @pollinginterval = 86399
EXEC sp_cdc_stop_job 'capture'
EXEC sp_cdc_start_job 'capture'
```
Le paramètre @pollinginterval est mesuré en secondes, avec une valeur recommandée définie sur 86399. Cela signifie que le journal des transactions conserve les modifications pendant 86 399 secondes (un jour). L'exécution de la procédure sp_cdc_start_job 'capture lance les paramètres.
Si des jobs de nettoyage ou de capture sont en cours d'exécution sur votre base de données, arrêtez-les. Pour en savoir plus, consultez Administrer et surveiller la capture de données modifiées.

Configurez une protection contre la troncation des journaux.

Pour vous assurer que le lecteur CDC dispose de suffisamment de temps pour lire les journaux tout en autorisant la troncation des journaux afin d'éviter d'épuiser l'espace de stockage, vous pouvez configurer une protection de troncation des journaux :

Connectez-vous à la base de données à l'aide d'un client SQL Server.

Créez une procédure stockée qui exécute une transaction active pendant une période que vous spécifiez pour empêcher la troncation des journaux :

CREATE PROCEDURE dbo.DatastreamLogTruncationSafeguard @transaction_logs_retention_time INT
AS
BEGIN

DECLARE @transactionLog TABLE (beginLSN BINARY(10), endLSN BINARY(10))
INSERT @transactionLog EXEC sp_repltrans

DECLARE @currentDateTime DATETIME = GETDATE()
DECLARE @cutoffDateTime DATETIME = DATEADD(MINUTE, -@transaction_logs_retention_time, @currentDateTime)

DECLARE @firstValidLSN BINARY(10) = NULL
DECLARE @lastValidLSN BINARY(10) = NULL
DECLARE @firstTxnTime DATETIME = NULL
DECLARE @lastTxnTime DATETIME = NULL

SELECT TOP 1
    @lastTxnTime = t.logStartTime,
    @lastValidLSN = t.beginLSN
FROM (
  SELECT
    beginLSN AS beginLSN,
    (SELECT TOP 1 [begin time]
    FROM fn_dblog(stuff(stuff(CONVERT(CHAR(24), beginLSN, 1), 19, 0, ':'), 11, 0, ':'), DEFAULT)) AS logStartTime
  FROM @transactionLog
) t
ORDER BY t.beginLSN DESC

-- If all transactions are before cutoff, clear everything
IF (@lastTxnTime < @cutoffDateTime)
BEGIN
    EXEC sp_repldone NULL, NULL, 0, 0, 1
END
ELSE
BEGIN
    -- Find the earliest transaction
    SELECT TOP 1
      @firstTxnTime = t.logStartTime,
      @firstValidLSN = ISNULL(@firstValidLSN, t.beginLSN)
    FROM (
      SELECT
        beginLSN AS beginLSN,
        (SELECT TOP 1 [begin time]
        FROM fn_dblog(stuff(stuff(CONVERT(CHAR(24), beginLSN, 1), 19, 0, ':'), 11, 0, ':'), DEFAULT)) AS logStartTime
      FROM @transactionLog
    ) t
    ORDER BY t.beginLSN ASC

    IF (@firstTxnTime < @cutoffDateTime)
    BEGIN
        -- Identify the earliest and latest LSNs within VLogs before cutoff
        SELECT
          @firstValidLSN = SUBSTRING(MAX(t.lsnMarkers), 1, 10),
          @lastValidLSN = SUBSTRING(MAX(t.lsnMarkers), 11, 10)
        FROM (
          SELECT MIN(beginLSN + endLSN) AS lsnMarkers
          FROM @transactionLog
          GROUP BY SUBSTRING(beginLSN, 1, 4)
        ) t
        WHERE (
          SELECT TOP 1 [begin time]
          FROM fn_dblog(stuff(stuff(CONVERT(CHAR(24), SUBSTRING(t.lsnMarkers, 1, 10), 1), 19, 0, ':'), 11, 0, ':'), DEFAULT)
          WHERE Operation = 'LOP_BEGIN_XACT'
        ) < @cutoffDateTime

        EXEC sp_repldone @firstValidLSN, @lastValidLSN, 0, 0, 0
    END
  END
END;

Créez une autre procédure stockée. Cette fois, vous allez créer un job qui exécute la procédure stockée que vous avez créée à l'étape précédente selon une cadence spécifiée :

CREATE PROCEDURE [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time INT
AS
BEGIN

DECLARE @database_name VARCHAR(MAX)
  SET @database_name =  (SELECT DB_NAME());;

  DECLARE @command_str VARCHAR(MAX);
  SET @command_str = CONCAT('Use ', @database_name,'; exec dbo.DatastreamLogTruncationSafeguard @transaction_logs_retention_time = ' + CAST(@transaction_logs_retention_time AS VARCHAR(10)));

  DECLARE @job_name VARCHAR(MAX);
SET @job_name =
  CONCAT(@database_name, '_', 'DatastreamLogTruncationSafeguardJob1')
    DECLARE @current_time INT
  = CAST(FORMAT(GETDATE(), 'HHmmss') AS INT);

  -- Schedule the procedure to run after every 5 minutes.
  IF NOT EXISTS (
    SELECT * FROM msdb.dbo.sysjobs
    WHERE name = @job_name
  )
  BEGIN
    EXEC msdb.dbo.sp_add_job
    @job_name = @job_name,
    @enabled = 1,
    @description = N'Execute the procedure every 5 minutes.' ;

    EXEC msdb.dbo.sp_add_jobstep
    @job_name =  @job_name,
    @step_name = N'Execute_DatastreamLogTruncationSafeguard',
    @subsystem = N'TSQL',
    @command = @command_str;

      DECLARE @schedule_name_1 VARCHAR(MAX);
    SET @schedule_name_1 = CONCAT(@database_name, '_', 'DatastreamEveryFiveMinutesSchedule')

    EXEC msdb.dbo.sp_add_schedule
    @schedule_name = @schedule_name_1,
    @freq_type = 4,  -- daily start
    @freq_subday_type = 4,  -- every X minutes daily
    @freq_interval = 1,
    @freq_subday_interval = 5,
    @active_start_time = @current_time;

    EXEC msdb.dbo.sp_attach_schedule
    @job_name = @job_name,
    @schedule_name = @schedule_name_1 ;

    -- Add a schedule that runs the stored procedure on the SQL Server Agent startup.
    DECLARE @schedule_name_agent_startup VARCHAR(MAX);
    SET @schedule_name_agent_startup = CONCAT(@database_name, '_', 'DatastreamSqlServerAgentStartupSchedule')

    EXEC msdb.dbo.sp_add_schedule
    @schedule_name = @schedule_name_agent_startup,
    @freq_type = 64,  -- start on SQL Server Agent startup
    @active_start_time = @current_time;

    EXEC msdb.dbo.sp_attach_schedule
    @job_name = @job_name,
    @schedule_name = @schedule_name_agent_startup ;

    EXEC msdb.dbo.sp_add_jobserver
    @job_name = @job_name,
    @server_name = @@servername ;
  END
END;

Exécutez la procédure stockée qui crée le job Datastream.
```
DECLARE @transaction_logs_retention_time INT = (INT)
EXEC [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time
```
Remplacez INT par le nombre de minutes pendant lesquelles vous souhaitez conserver les journaux. Exemple :
- La valeur 60 définit la durée de conservation sur une heure.
- La valeur de 24 * 60 définit la durée de conservation sur un jour.
- La valeur de 3 * 24 * 60 définit la durée de conservation sur trois jours.

Étapes suivantes

Découvrez comment Datastream fonctionne avec les sources SQL Server.