I passaggi seguenti spiegano come configurare un database SQL Server autogestito per l'utilizzo con Datastream:
Abilita CDC per il tuo database di origine. Per farlo, collegati al database ed esegui questo 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
Sostituisci quanto segue:
DATABASE_NAME
: il nome del database di origineSCHEMA_NAME
: il nome dello schema a cui appartengono le tabelleTABLE_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 e i log potrebbero essere troncati, causando una perdita permanente dei dati delle modifiche non letti da Datastream.
Per informazioni sull'esecuzione dell'agente SQL Server, consulta Avvia, arresta o riavvia un'istanza dell'agente SQL Server.
Crea un utente Datastream:
Connettiti al database di origine e inserisci il comando seguente:
USE DATABASE_NAME;
Crea un accesso da utilizzare durante la configurazione del profilo di connessione in Datastream.
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
Crea un utente e assegnagli i ruoli
db_owner
edb_denydatawriter
:CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
EXEC sp_addrolemember 'db_owner', 'USER_NAME'; EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';
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 obbligatori solo quando configuri database SQL Server di origine da utilizzare con il metodo CDC dei log delle transazioni.
Concedi le autorizzazioni
SELECT
per la funzionesys.fn_dblog
.USE master; GRANT SELECT ON sys.fn_dblog TO USER_NAME;
Aggiungi l'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 all'utente nel database
master
:USE master; GRANT VIEW SERVER STATE TO YOUR_LOGIN;
Imposta il periodo di conservazione durante il quale vuoi che le modifiche siano disponibili nel tuo sorgente.
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
viene misurato in secondi con un valore consigliato impostato su86399
. Ciò significa che il log delle transazioni conserva le modifiche per 86.399 secondi (un giorno). Avvio dell'esecuzione della procedurasp_cdc_start_job 'capture
le impostazioni.Se sono presenti job di pulizia o acquisizione in esecuzione sul database, arrestali. Per ulteriori informazioni, consulta Gestire e monitorare l'acquisizione dei dati delle modifiche.
Configura una protezione da troncamento dei log.
Per assicurarti che il lettore CDC abbia tempo sufficiente per leggere i log, consentendo al contempo per evitare di consumare spazio di archiviazione, puoi configurare un troncamento dei log salvaguardia:
- Connettiti al database utilizzando un client SQL Server.
Crea una tabella fittizia nel database:
USE [DATABASE_NAME]; CREATE TABLE dbo.gcp_datastream_truncation_safeguard ( [id] INT IDENTITY(1,1) PRIMARY KEY, CreatedDate DATETIME DEFAULT GETDATE(), [char_column] CHAR(8) );
crea una stored procedure che esegue una transazione attiva per un periodo 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 devi creare un job che esegue stored procedure creata nel passaggio precedente in base a un cadenza:
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;
Eseguire la stored procedure che crea il job Datastream.
DECLARE @transaction_logs_retention_time INT = (INT) EXEC [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time
Sostituisci INT con il numero di minuti che vuoi i log. Ad esempio:
- Il valore di
60
imposta il tempo di conservazione su 1 ora - Il valore di
24 * 60
imposta il tempo di conservazione su 1 giorno - Il valore di
3 * 24 * 60
imposta il tempo di conservazione su 3 giorni
- Il valore di