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

הגדרת מסד נתונים של SQL Server בניהול עצמי ל-CDC

בדף הזה מוסבר איך להגדיר לכידת נתונים לשינוי (CDC) כדי להזרים נתונים ממסד נתונים של SQL Server בניהול עצמי אל יעד נתמך, כמו BigQuery או Cloud Storage.

מפעילים CDC במסד הנתונים של המקור. כדי לעשות את זה, מתחברים למסד הנתונים ומריצים את הפקודה הבאה בשורת פקודה של SQL או בטרמינל:
```
USE [DATABASE_NAME]
GO
EXEC sys.sp_cdc_enable_db
GO
```
מחליפים את DATABASE_NAME בשם של מסד הנתונים של המקור.
מפעילים את CDC בטבלאות שרוצים לתעד בהן שינויים:

‫
```
USE [DATABASE_NAME]
EXEC sys.sp_cdc_enable_table
@source_schema = N'SCHEMA_NAME',
@source_name = N'TABLE_NAME',
@role_name = NULL
GO
```
הערה: צריך להריץ את הפקודה לכל טבלה שרוצים להפעיל בה CDC.

מחליפים את מה שכתוב בשדות הבאים:
- ‫DATABASE_NAME: השם של מסד הנתונים של המקור
- ‫SCHEMA_NAME: השם של הסכימה שאליה שייכות הטבלאות
- ‫TABLE_NAME: שם הטבלה שרוצים להפעיל בה CDC
מפעילים את SQL Server Agent ומוודאים שהוא פועל כל הזמן. אם SQL Server Agent לא יפעל במשך תקופה ארוכה, יכול להיות שהיומנים ייחתכו, מה שיוביל לאובדן קבוע של נתוני השינוי שלא נקראו על ידי Datastream.

מידע על הפעלת SQL Server Agent זמין במאמר הפעלה, עצירה או הפעלה מחדש של מופע של SQL Server Agent.

הערה: מומלץ להגדיר הפעלה אוטומטית מחדש בשירות SQL Server Agent כדי למנוע מצב שבו הסוכן לא פעיל למשך זמן ארוך יותר.
הפעלת בידוד תמונת מצב.

כשמבצעים מילוי חוזר של נתונים ממסד נתונים של SQL Server, חשוב לוודא שהתמונות המיידיות עקביות. אם לא תפעילו את ההגדרות שמתוארות בקטע הזה, שינויים שיתבצעו במסד הנתונים במהלך תהליך המילוי החוזר עלולים להוביל לכפילויות או לתוצאות שגויות, במיוחד בטבלאות ללא מפתחות ראשיים.

הפעלת בידוד snapshot יוצרת תצוגה זמנית של מסד הנתונים בתחילת תהליך מילוי החוסרים (backfill). כך תוכלו לוודא שהנתונים שמועתקים יישארו עקביים, גם אם משתמשים אחרים יבצעו שינויים בטבלאות הפעילות בו-זמנית. הפעלת בידוד snapshot עשויה להשפיע מעט על הביצועים, אבל היא חיונית לחילוץ נתונים מהימן.

כדי להפעיל בידוד snapshot:
1. מתחברים למסד הנתונים באמצעות לקוח SQL Server.
2. מריצים את הפקודה הבאה:
```
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
```
מחליפים את DATABASE_NAME בשם של מסד הנתונים.
יוצרים משתמש Datastream:
1. מתחברים למסד הנתונים של המקור ומזינים את הפקודה הבאה:
```
USE DATABASE_NAME;
```
2. יוצרים פרטי כניסה לשימוש בזמן הגדרת פרופיל החיבור ב-Datastream.
```
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
```
3. יצירת משתמש:
```
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```
4. מקצים להם את התפקיד db_datareader:
```
EXEC sp_addrolemember 'db_datareader', 'USER_NAME';
```
5. מעניקים להם את ההרשאה VIEW DATABASE STATE:
```
GRANT VIEW DATABASE STATE TO USER_NAME;
```
6. הוספת המשתמש למסד הנתונים master:
```
USE master;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```

שלבים נוספים שנדרשים לשיטת ה-CDC של יומני העסקאות

השלבים שמתוארים בקטע הזה נדרשים רק כשמגדירים את מסד הנתונים של SQL Server כמקור לשימוש בשיטת ה-CDC של יומני העסקאות.

מתחברים למסד הנתונים של המקור ומקצים את התפקידים db_owner ו-db_denydatawriter למשתמש:

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

נותנים ל-SELECT הרשאות לשימוש בפונקציה sys.fn_dblog.
```
USE master;
GRANT SELECT ON sys.fn_dblog TO USER_NAME;
```
מוסיפים את המשתמש למסד הנתונים msdb ומקצים לו את ההרשאות הבאות:
```
USE msdb;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
GRANT SELECT ON dbo.sysjobs TO USER_NAME;
```
מקצים למשתמש את ההרשאות הבאות במסד הנתונים master:
```
  USE master;
  GRANT VIEW SERVER STATE TO YOUR_LOGIN;
```
מגדירים את מרווח הזמן בין בדיקות הזמינות שרוצים שהשינויים יהיו זמינים במקור.
```
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'
```
הפרמטר @pollinginterval נמדד בשניות, והערך המומלץ שלו הוא 86399. כלומר, יומן העסקאות שומר את השינויים למשך 86,399 שניות (יום אחד). הפעלת הפרוצדורה sp_cdc_start_job 'capture מתחילה את ההגדרות.
אם יש עבודות ניקוי או לכידה שפועלות במסד הנתונים, צריך להפסיק אותן. מידע נוסף זמין במאמר בנושא ניהול ומעקב אחרי סימון נתונים שהשתנו (CDC).

הגדרת אמצעי הגנה לחיתוך יומנים.

כדי לוודא שלקורא ה-CDC יש מספיק זמן לקרוא את היומנים, ועדיין לאפשר חיתוך של היומנים כדי למנוע ניצול של נפח האחסון, אפשר להגדיר אמצעי הגנה לחיתוך היומנים:

מתחברים למסד הנתונים באמצעות לקוח SQL Server.

כדי למנוע חיתוך של יומן, יוצרים פרוצדורה מאוחסנת שמריצה עסקה פעילה לתקופה שאתם מציינים:

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;

יוצרים עוד תהליך מאוחסן. בשלב הזה, תיצרו משימה שתפעיל את הפרוצדורה המאוחסנת שיצרתם בשלב הקודם לפי קצב שצוין:

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;

מריצים את התהליך המאוחסן שיוצר את משימת Datastream.
```
DECLARE @transaction_logs_retention_time INT = (INT)
EXEC [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time
```
מחליפים את INT במספר הדקות שבהן רוצים לשמור את היומנים. לדוגמה:
- הערך 60 מגדיר את זמן השמירה לשעה אחת
- הערך 24 * 60 מגדיר את זמן השמירה ליום אחד
- הערך 3 * 24 * 60 מגדיר את זמן השמירה ל-3 ימים

המאמרים הבאים

מידע נוסף על עבודה עם מקורות SQL Server ב-Datastream

הגדרת מסד נתונים של SQL Server בניהול עצמי ל-CDC קל לארגן דפים בעזרת אוספים אפשר לשמור ולסווג תוכן על סמך ההעדפות שלך.

שלבים נוספים שנדרשים לשיטת ה-CDC של יומני העסקאות

המאמרים הבאים

הגדרת מסד נתונים של SQL Server בניהול עצמי ל-CDC