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
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 prosedursp_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:
- Hubungkan ke database Anda menggunakan klien SQL Server.
- 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:
Di konsol Google Cloud, buka halaman Instance Cloud SQL.
Tetapkan peran
db_owner
dandb_denydatawriter
untuk pengguna Anda:EXEC sp_addrolemember 'db_owner', 'USER_NAME'; EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';