Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

Configura un database SQL Server autogestito per CDC

Questa pagina descrive come configurare Change Data Capture (CDC) per trasmettere dati da un database SQL Server autogestito a una destinazione supportata, come BigQuery o Cloud Storage.

Assicurati che il database utilizzi il modello di recupero completo. Per controllare e impostare il modello di recupero, connettiti al database ed esegui il seguente comando in un prompt SQL o in un terminale:
```
USE master
GO
ALTER DATABASE [DATABASE_NAME] SET RECOVERY FULL
GO
```
Sostituisci DATABASE_NAME con il nome del database di origine.

Nota: dopo aver impostato il modello di recupero su FULL, il database rimane in uno stato di recupero pseudo-semplice finché non esegui un backup completo del database. Devi eseguire un backup completo del database prima che il log delle transazioni inizi a conservare i record per Change Data Capture.
Abilita CDC per il database di origine. Per farlo, connettiti al database ed esegui il seguente comando in un prompt SQL o in un terminale:
```
USE [DATABASE_NAME]
GO
EXEC sys.sp_cdc_enable_db
GO
```
Sostituisci DATABASE_NAME con il nome del database di origine.
Abilita CDC nelle tabelle per le quali devi acquisire le modifiche:
```
USE [DATABASE_NAME]
EXEC sys.sp_cdc_enable_table
@source_schema = N'SCHEMA_NAME',
@source_name = N'TABLE_NAME',
@role_name = NULL
GO
```
Nota: devi eseguire il comando per ogni tabella per cui vuoi abilitare CDC.

Sostituisci quanto segue:
- DATABASE_NAME: il nome del database di origine
- SCHEMA_NAME: il nome dello schema a cui appartengono le tabelle
- TABLE_NAME: il nome della tabella per cui vuoi abilitare CDC
Avvia l'agente SQL Server e assicurati che sia sempre in esecuzione. Se l'agente SQL Server rimane inattivo per un periodo prolungato, i log potrebbero essere troncati, causando una perdita permanente dei dati di modifica non letti da Datastream.

Per informazioni sull'esecuzione dell'agente SQL Server, consulta Avviare, arrestare o riavviare un'istanza dell'agente SQL Server.

Nota: ti consigliamo di impostare il riavvio automatico per il servizio dell'agente SQL Server per evitare che l'agente rimanga inattivo per un periodo di tempo più lungo.
Abilita isolamento degli snapshot.

Quando esegui il backfill dei dati dal database SQL Server, è importante assicurarti che gli snapshot siano coerenti. Se non applichi le impostazioni descritte in questa sezione, le modifiche apportate al database durante il processo di backfill potrebbero generare duplicati o risultati errati. L'applicazione delle impostazioni di isolamento degli snapshot è obbligatoria se il flusso include tabelle senza chiavi primarie.

L'abilitazione dell'isolamento degli snapshot crea una visualizzazione temporanea del database all'inizio del processo di backfill. In questo modo, i dati copiati rimangono coerenti, anche se altri utenti apportano modifiche alle tabelle live contemporaneamente. L'abilitazione dell'isolamento degli snapshot potrebbe avere un leggero impatto sulle prestazioni, ma è essenziale per un'estrazione affidabile dei dati.

Per abilitare l'isolamento degli snapshot:
1. Connettiti al database utilizzando un client SQL Server.
2. Esegui questo comando:
```
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
```
Sostituisci DATABASE_NAME con il nome del database.
Crea un utente Datastream:
1. Connettiti al database di origine e inserisci il seguente comando:
```
USE DATABASE_NAME;
```
2. Crea un accesso da utilizzare durante la configurazione del profilo di connessione in Datastream.
```
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
```
3. Crea un utente:
```
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```
4. Assegna il ruolo db_datareader:
```
EXEC sp_addrolemember 'db_datareader', 'USER_NAME';
```
5. Concedi l'autorizzazione VIEW DATABASE STATE:
```
GRANT VIEW DATABASE STATE TO USER_NAME;
```
6. Aggiungi questo utente al database master:
```
USE master;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```

Passaggi aggiuntivi richiesti per il metodo CDC dei log delle transazioni

I passaggi descritti in questa sezione sono necessari solo quando configuri il database SQL Server di origine da utilizzare con il metodo CDC dei log delle transazioni.

Connettiti al database di origine e assegna i ruoli db_owner e db_denydatawriter al tuo utente:

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

Concedi le autorizzazioni SELECT per la funzione sys.fn_dblog.

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

Aggiungi il tuo utente al database msdb e assegnagli le seguenti autorizzazioni:

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

Assegna le seguenti autorizzazioni al tuo utente nel database master:
```
  USE master;
  GRANT VIEW SERVER STATE TO YOUR_LOGIN;
```
Imposta l'intervallo di polling per il quale vuoi che le modifiche siano disponibili nell'origine.
```
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'
```
Il parametro @pollinginterval è misurato in secondi con un valore consigliato impostato su 86399. Ciò significa che il log delle transazioni conserva le modifiche per 86.399 secondi (un giorno). L'esecuzione della procedura sp_cdc_start_job 'capture avvia le impostazioni.
Se sono in esecuzione job di pulizia o acquisizione nel database, arrestali. Per ulteriori informazioni, consulta Amministrare e monitorare Change Data Capture.

Configura una protezione per evitare il troncamento dei log.

Per assicurarti che il lettore CDC abbia tempo sufficiente per leggere i log, consentendo al contempo il troncamento dei log per evitare di esaurire lo spazio di archiviazione, puoi configurare una protezione per evitare il troncamento dei log:

Connettiti al database utilizzando un client SQL Server.

Crea una stored procedure che esegue una transazione attiva per un periodo di tempo specificato per evitare il troncamento dei log:

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), t.lsnMarkers, 1), 19, 0, ':'), 11, 0, ':'), DEFAULT)
          WHERE Operation = 'LOP_BEGIN_XACT'
        ) < @cutoffDateTime

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

Crea un'altra stored procedure. Questa volta, crea un job che esegue la stored procedure creata nel passaggio precedente in base a una cadenza specificata:

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;

Esegui la stored procedure che crea il job di Datastream.
```
DECLARE @transaction_logs_retention_time INT = (INT)
EXEC [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time
```
Sostituisci INT con il numero di minuti per cui vuoi conservare i log. Ad esempio:
- Il valore 60 imposta il tempo di conservazione su 1 ora
- Il valore 24 * 60 imposta il tempo di conservazione su 1 giorno
- Il valore 3 * 24 * 60 imposta il tempo di conservazione su 3 giorni

Passaggi successivi

Scopri di più su come Datastream funziona con le origini SQL Server.

Configura un database SQL Server autogestito per CDC Mantieni tutto organizzato con le raccolte Salva e classifica i contenuti in base alle tue preferenze.

Passaggi aggiuntivi richiesti per il metodo CDC dei log delle transazioni

Passaggi successivi

Configura un database SQL Server autogestito per CDC