Mengonfigurasi database Cloud SQL untuk SQL Server

Bagian berikut membahas cara mengonfigurasi database Cloud SQL untuk SQL Server.

Hubungkan ke instance Cloud SQL Anda. Anda dapat melakukannya menggunakan perintah cloud sql connect pada prompt Cloud Shell.
Aktifkan CDC pada database dengan menjalankan perintah berikut:
```
EXEC msdb.dbo.gcloudsql_cdc_enable_db 'DATABASE_NAME'
```
Ganti DATABASE_NAME dengan nama database sumber Anda.
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.
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 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:
1. Di konsol Google Cloud, buka halaman Instance Cloud SQL.
  
  Buka Instance Cloud SQL
2. Buat pengguna.
3. Tetapkan peran db_owner dan db_denydatawriter untuk pengguna Anda:
```
EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';
```