Selbstverwaltete SQL Server-Datenbank konfigurieren

In den folgenden Schritten wird beschrieben, wie Sie eine selbstverwaltete SQL Server-Datenbank für die Verwendung mit Datastream konfigurieren:

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

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

   Hinweis: Sie müssen den Befehl für jede Tabelle ausführen, für die Sie CDC aktivieren möchten.

   Ersetzen Sie Folgendes:

   • DATABASE_NAME: der Name Ihrer Quelldatenbank
   • SCHEMA_NAME: der Name des Schemas, zu dem die Tabellen gehören
   • TABLE_NAME: der Name der Tabelle, für die Sie CDC aktivieren möchten

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

   Hinweis: Wir empfehlen, den automatischen Neustart für den SQL Server-Agent-Dienst zu aktivieren, um zu verhindern, dass der Agent über einen längeren Zeitraum nicht ausgeführt wird.

4. Erstellen Sie einen Datastream-Nutzer:

   1. Stellen Sie eine Verbindung zur Quelldatenbank her und geben Sie den folgenden Befehl ein:

      USE DATABASE_NAME;
      

   2. Erstellen Sie ein Log-in für die Einrichtung des Verbindungsprofils in Datastream.

      CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
      

   3. Erstellen Sie einen Nutzer und weisen Sie ihm die Rollen db_owner und db_denydatawriter zu:

      CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
      

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

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

1. Gewähren Sie SELECT-Berechtigungen für die Funktion sys.fn_dblog.

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

2. 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;
   

3. Weisen Sie Ihrem Nutzer in der Datenbank master die folgenden Berechtigungen zu:

     USE master;
     GRANT VIEW SERVER STATE TO YOUR_LOGIN;
   

4. 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 ist 86399. Das bedeutet,dass Änderungen 86.399 Sekunden (einen Tag) im Transaktionslog gespeichert werden. Mit der sp_cdc_start_job 'capture-Prozedur werden die Einstellungen initiiert.

5. Schutz vor abgeschnittenen Logs einrichten

   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:

   1. Stellen Sie eine Verbindung zur Datenbank über einen SQL Server-Client her.

   2. 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)
        );
      

   3. Erstellen Sie eine gespeicherte Prozedur, die eine aktive Transaktion für einen Zeitraum von 24 Stunden ausführt, um zu verhindern, dass das Log abgeschnitten wird:

      CREATE PROCEDURE dbo.DatastreamLogTruncationSafeguard
      AS
      BEGIN
        -- Start a new transaction
        BEGIN TRANSACTION;
        INSERT INTO dbo.gcp_datastream_truncation_safeguard (char_column) VALUES ('a')
        -- Wait for one day before ending the transaction
        WAITFOR DELAY '23:59';
        -- Commit the transaction
        COMMIT TRANSACTION;
      END;
      

   4. Erstellen Sie eine weitere gespeicherte Prozedur. Dieses Mal erstellen Sie täglich einen Job, der den gespeicherten Prozedur, den Sie im vorherigen Schritt erstellt haben, täglich ausführt:

      CREATE PROCEDURE dbo.SetUpDatastreamJob 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')
      
        DECLARE @job_name_1 VARCHAR(MAX);
        Set @job_name_1 = CONCAT(@database_name, '_', 'DatastreamLogTruncationSafeguardJob1')
        -- Schedule the procedure to run again tomorrow
        IF NOT EXISTS (
          select * from msdb.dbo.sysjobs
          WHERE name = @job_name_1
        )
        BEGIN
          EXEC msdb.dbo.sp_add_job
          @job_name = @job_name_1,
          @enabled = 1,
          @description = N'Execute the procedure every day' ;
      
          EXEC msdb.dbo.sp_add_jobstep
          @job_name =  @job_name_1,
          @step_name = N'Execute_DatastreamLogTruncationSafeguard1',
          @subsystem = N'TSQL',
          @command = @command_str;
      
          -- Add a schedule that runs the stored procedure every day.
          DECLARE @start_time_1 time;
          SET @start_time_1 = DATEADD(MINUTE, 1, GETDATE());
      
          DECLARE @schedule_name_1 VARCHAR(MAX);
          Set @schedule_name_1 = CONCAT(@database_name, '_', 'DatastreamEverydaySchedule1')
      
          DECLARE @formatted_start_time_1 INT;
          SET @formatted_start_time_1 = CONVERT(INT, REPLACE(CONVERT(VARCHAR(8), @start_time_1, 114), ':' ,''));
      
          EXEC msdb.dbo.sp_add_schedule
          @schedule_name = @schedule_name_1,
          @freq_type = 4,  -- daily start
          @freq_interval = 1,
          @active_start_time = @formatted_start_time_1;
      
          EXEC msdb.dbo.sp_attach_schedule
          @job_name = @job_name_1,
          @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 = @formatted_start_time_1;
      
          EXEC msdb.dbo.sp_attach_schedule
          @job_name = @job_name_1,
          @schedule_name = @schedule_name_agent_startup ;
      
          EXEC msdb.dbo.sp_add_jobserver
          @job_name = @job_name_1,
          @server_name = @@servername ;
        END
      
        DECLARE @job_name_2 VARCHAR(MAX);
        Set @job_name_2 = CONCAT(@database_name, '_', 'DatastreamLogTruncationSafeguardJob2')
      
        IF NOT EXISTS (
          select * from msdb.dbo.sysjobs
          WHERE name = @job_name_2
        )
        BEGIN
          EXEC msdb.dbo.sp_add_job
          @job_name = @job_name_2,
          @enabled = 1,
          @description = N'Procedure execution every day' ;
      
          EXEC msdb.dbo.sp_add_jobstep
          @job_name =  @job_name_2,
          @step_name = N'Execute_DatastreamLogTruncationSafeguard2',
          @subsystem = N'TSQL',
          @command = @command_str;
      
          DECLARE @start_time_2 time;
          SET @start_time_2 = DATEADD(HOUR, 12, GETDATE());
          DECLARE @formatted_start_time_2 INT;
          SET @formatted_start_time_2 = CONVERT(INT, REPLACE(CONVERT(VARCHAR(8), @start_time_2, 114), ':' ,''));
          DECLARE @schedule_name_2 VARCHAR(MAX);
          Set @schedule_name_2 = CONCAT(@database_name, '_', 'DatastreamEverydaySchedule2')
      
          EXEC msdb.dbo.sp_add_schedule
          @schedule_name = @schedule_name_2,
          @freq_type = 4,  -- daily start
          @freq_interval = 1,
          @active_start_time = @formatted_start_time_2;
      
          EXEC msdb.dbo.sp_attach_schedule
          @job_name = @job_name_2,
          @schedule_name = @schedule_name_2 ;
      
          EXEC msdb.dbo.sp_add_jobserver
          @job_name = @job_name_2,
         @server_name = @@servername ;
        END
      End;
      

   5. Führen Sie die gespeicherte Prozedur aus, die den Datastream-Job erstellt.

   EXEC dbo.SetUpDatastreamJob