In den folgenden Schritten wird beschrieben, wie Sie eine selbstverwaltete SQL Server-Datenbank für die Verwendung mit Datastream konfigurieren:
Aktivieren Sie CDC für Ihre Quelldatenbank. Stellen Sie dazu eine Verbindung zur Datenbank her und führen Sie den folgenden Befehl an einer SQL-Eingabeaufforderung oder in einem Terminal aus:
USE [DATABASE_NAME] GO EXEC sys.sp_cdc_enable_db GO
Ersetzen Sie
DATABASE_NAME
durch den Namen Ihrer Quelldatenbank.Aktivieren Sie CDC für die Tabellen, für die Sie Änderungen erfassen müssen:
USE [DATABASE_NAME] EXEC sys.sp_cdc_enable_table @source_schema = N'SCHEMA_NAME', @source_name = N'TABLE_NAME', @role_name = NULL GO
Ersetzen Sie Folgendes:
DATABASE_NAME
: der Name Ihrer QuelldatenbankSCHEMA_NAME
: der Name des Schemas, zu dem die Tabellen gehörenTABLE_NAME
: der Name der Tabelle, für die Sie CDC aktivieren möchten
Starten Sie den SQL Server-Agent und stellen Sie sicher, dass er immer ausgeführt wird. Wenn der SQL Server-Agent über einen längeren Zeitraum nicht verfügbar ist, werden die Logs möglicherweise abgeschnitten. Dies führt zu einem dauerhaften Verlust der Änderungsdaten, die von Datastream nicht gelesen wurden.
Informationen zum Ausführen des SQL Server-Agents finden Sie unter Instanz des SQL Server-Agents starten, beenden oder neu starten.
Erstellen Sie einen Datastream-Nutzer:
Stellen Sie eine Verbindung zur Quelldatenbank her und geben Sie den folgenden Befehl ein:
USE DATABASE_NAME;
Erstellen Sie ein Log-in für die Einrichtung des Verbindungsprofils in Datastream.
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
Erstellen Sie einen Nutzer und weisen Sie ihm die Rollen
db_owner
unddb_denydatawriter
zu:CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
EXEC sp_addrolemember 'db_owner', 'USER_NAME'; EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';
Fügen Sie diesen Nutzer der Datenbank
master
hinzu:USE master; CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
Zusätzliche erforderliche Schritte für die CDC-Methode der Transaktionslogs
Die in diesem Abschnitt beschriebenen Schritte sind nur erforderlich, wenn Sie Ihre SQL Server-Quelldatenbank für die Verwendung mit der CDC-Methode für Transaktionslogs konfigurieren.
Gewähren Sie
SELECT
-Berechtigungen für die Funktionsys.fn_dblog
.USE master; GRANT SELECT ON sys.fn_dblog TO USER_NAME;
Fügen Sie der msdb-Datenbank den Nutzer hinzu und weisen Sie ihm die folgenden Berechtigungen zu:
USE msdb; CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN; GRANT SELECT ON dbo.sysjobs TO USER_NAME;
Weisen Sie Ihrem Nutzer in der Datenbank
master
die folgenden Berechtigungen zu:USE master; GRANT VIEW SERVER STATE TO YOUR_LOGIN;
Legen Sie die Aufbewahrungsdauer fest, für die die Änderungen in Ihrer Quelle verfügbar sein sollen.
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'
Der Parameter
@pollinginterval
wird in Sekunden gemessen. Der empfohlene Wert ist86399
. Das bedeutet,dass Änderungen 86.399 Sekunden (einen Tag) im Transaktionslog gespeichert werden. Mit dersp_cdc_start_job 'capture
-Prozedur werden die Einstellungen initiiert.Beenden Sie alle Bereinigungs- oder Erfassungsjobs in Ihrer Datenbank. Weitere Informationen finden Sie unter Change Data Capture verwalten und überwachen.
Richten Sie einen Schutz vor abgeschnittenen Logs ein.
Damit der CDC-Leser genügend Zeit hat, die Logs zu lesen, und gleichzeitig das Abschneiden von Logs zulässt, um zu verhindern, dass der Speicherplatz belegt wird, können Sie einen Schutz vor abgeschnittenen Logs einrichten:
- Stellen Sie eine Verbindung zur Datenbank über einen SQL Server-Client her.
Erstellen Sie eine Dummy-Tabelle in der Datenbank:
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) );
Erstellen Sie eine gespeicherte Prozedur, die eine aktive Transaktion für einen von Ihnen angegebenen Zeitraum ausführt, um das Abschneiden des Logs zu verhindern:
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;
Erstellen Sie eine weitere gespeicherte Prozedur. Dieses Mal erstellen Sie einen Job, der die gespeicherte Prozedur, die Sie im vorherigen Schritt erstellt haben, nach einem bestimmten Rhythmus ausführt:
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;
Führen Sie die gespeicherte Prozedur aus, die den Datastream-Job erstellt.
DECLARE @transaction_logs_retention_time INT = (INT) EXEC [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time
Ersetzen Sie INT durch die Anzahl der Minuten, für die Sie die Logs speichern möchten. Beispiel:
- Der Wert von
60
legt die Aufbewahrungsdauer auf 1 Stunde fest - Der Wert von
24 * 60
legt die Aufbewahrungsdauer auf 1 Tag fest. - Der Wert von
3 * 24 * 60
legt die Aufbewahrungsdauer auf 3 Tage fest.
- Der Wert von