Mengonfigurasi database SQL Server yang dikelola sendiri

Aktifkan CDC untuk database sumber Anda. Untuk melakukannya, hubungkan ke database dan jalankan perintah berikut di prompt SQL atau di terminal:
```
USE DATABASE_NAME
GO
EXEC sys.sp_cdc_enable_db
GO
```
Ganti DATABASE_NAME dengan nama database sumber Anda.
Tetapkan periode retensi data yang Anda inginkan agar perubahan tersedia di sumber:
```
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'
```
Parameter @pollinginterval diukur dalam detik dengan nilai yang direkomendasikan ditetapkan ke 86399. Hal ini berarti log transaksi menyimpan perubahan selama 86.399 detik (satu hari). Mengeksekusi prosedur sp_cdc_start_job 'capture akan memulai setelan.
Aktifkan CDC pada tabel yang perubahannya perlu Anda tangkap:
```
USE DATABASE_NAME
EXEC sys.sp_cdc_enable_table
@source_schema = N'SCHEMA_NAME',
@source_name = N'TABLE_NAME',
@role_name = NULL
GO
```
Catatan: Anda perlu menjalankan perintah untuk setiap tabel tempat Anda ingin mengaktifkan CDC.

Ganti kode berikut:
- DATABASE_NAME: nama database sumber Anda
- SCHEMA_NAME: nama skema tempat tabel berada
- TABLE_NAME: nama tabel tempat Anda ingin mengaktifkan CDC
Aktifkan isolasi snapshot:

Saat mengisi ulang data dari database SQL Server, penting untuk memastikan snapshot yang konsisten. Jika Anda tidak menerapkan setelan yang dijelaskan lebih lanjut di bagian ini, perubahan yang dilakukan pada database selama proses pengisian ulang dapat menyebabkan duplikat atau hasil yang salah, khususnya, untuk tabel tanpa kunci utama.

Mengaktifkan isolasi snapshot akan menciptakan tampilan sementara database Anda di awal proses pengisian ulang. Ini memastikan bahwa data yang disalin tetap konsisten, bahkan jika pengguna lain membuat perubahan pada tabel live secara bersamaan. Mengaktifkan isolasi snapshot mungkin memiliki sedikit dampak performa, tetapi hal ini penting untuk ekstraksi data yang andal.

Untuk mengaktifkan isolasi snapshot:
1. Hubungkan ke database Anda menggunakan klien SQL Server.
2. Jalankan perintah berikut:
```
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
```
Ganti DATABASE_NAME dengan nama database Anda.

Menyiapkan pengamanan pemotongan log

Guna memastikan bahwa pembaca CDC memiliki cukup waktu untuk membaca log sekaligus mengizinkan pemotongan log agar ruang penyimpanan tidak penuh, Anda dapat menyiapkan pengaman pemotongan log:

Menghubungkan ke database menggunakan klien SQL Server.

Buat tabel contoh di 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)
  );

Ganti DATABASE_NAME dengan nama database tempat Anda ingin membuat tabel contoh.

Buat prosedur tersimpan yang menjalankan transaksi aktif selama periode 24 jam untuk mencegah pemotongan log:

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;

Buat prosedur lain yang tersimpan. Kali ini, Anda membuat tugas yang menjalankan prosedur tersimpan yang Anda buat di langkah sebelumnya setiap hari:

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;

Menjalankan prosedur tersimpan yang membuat tugas Datastream.

EXEC dbo.SetUpDatastreamJob

Membuat pengguna Datastream:

Hubungkan ke database sumber dan masukkan perintah berikut:
```
 USE DATABASE_NAME;
 ```
```
Buat login untuk digunakan saat menyiapkan profil koneksi di Datastream.
```
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
```

Buat pengguna dan tetapkan peran db_owner dan db_denydatawriter ke pengguna tersebut:

CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;

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

Tambahkan pengguna ini ke database master dan tetapkan izin berikut kepada pengguna tersebut:

USE master;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
GRANT VIEW SERVER STATE TO YOUR_LOGIN;
GRANT SELECT ON sys.fn_dblog TO USER_NAME;

Tambahkan pengguna ini ke database msdb dan tetapkan izin berikut:

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