Google Cloud SQL for SQL Server setup guide

Cloud SQL for SQL Server is Google's managed SQL Server offering. This guide walks you through provisioning a Cloud SQL for SQL Server instance and configuring it for standard extraction (full or incremental SELECT-based reads) and Change Data Capture (CDC) for log-based incremental extraction.

Prerequisites

• A Google Cloud project with billing enabled.
• The Cloud SQL Admin IAM role on the project.
• For CDC: a Standard or Enterprise edition on SQL Server 2017, 2019, or 2022. Web and Express editions do not support CDC on Cloud SQL. Standard extraction works on all editions.

note

The default admin login on Cloud SQL for SQL Server is sqlserver, not sa. It has elevated privileges but is not a member of the sysadmin fixed server role. Google wraps certain sysadmin-only stored procedures with gcloudsql_* equivalents. For CDC, you must use msdb.dbo.gcloudsql_cdc_enable_db to enable CDC at the database level. The standard sys.sp_cdc_enable_db procedure is blocked on Cloud SQL.

Standard extraction setup

Use standard extraction when Data Integration reads tables directly with SELECT statements (full load or watermark-based incremental).

Step 1: Create the Cloud SQL for SQL Server instance

1. In the Google Cloud Console, navigate to SQL > Create instance > SQL Server.
2. Select the database version (for example, SQL Server 2022 Standard).
3. Set the instance ID and the password for the sqlserver user. Save this password securely.
4. Select the region. For lowest latency, place the instance in the same region as your integration runtime.
5. Under Machine configuration, select cores and memory based on your workload.
6. Under Storage, enable Automatic storage increase unless you have a strict storage cap.
7. Click Create instance. Provisioning takes a few minutes.

Step 2: Configure connectivity

Select one of the following connectivity options:

Option A: Public IP with authorized networks

1. On the instance page, navigate to Connections > Networking > Public IP and enable it.
2. Under Authorized networks, click Add network and add the CIDR ranges of your integration runtime. For Boomi Atom Cloud runtimes, use the authoritative egress IP address list published in Boomi Atom Cloud IP addresses. For local Atoms, add your static office or data center IP address range.
3. Ensure that port 1433 is open in the authorized networks entry. Cloud SQL for SQL Server listens on port 1433 only; no other port is used.
4. Under Connections > Security, enable SSL/TLS and require encrypted connections. Cloud SQL issues a server CA certificate that the Boomi connection must trust — download it from Connections > Security > Server CA and import it into your Boomi connector's trust store. You can also provision client certificates from the same page if your security policy requires mutual TLS.

Option B: Private IP via VPC peering (recommended for production)

1. On the instance page, navigate to Connections > Networking > Private IP and enable it.
2. Allocate an IP range to Google services if you have not already done so.
3. Set up VPC peering or VPN/Interconnect from your integration runtime's network to the Cloud SQL VPC.

Option C: Private Service Connect (PSC)

Use PSC when the consumer and producer are in separate VPCs and you need fine-grained access control. Configure a PSC endpoint in the consumer VPC that points to the Cloud SQL service attachment.

Step 3: Create the application database

Connect as sqlserver using SQL Server Management Studio, Azure Data Studio, or sqlcmd, then run:

CREATE DATABASE app_db;
GO

If you are migrating from an existing database, Cloud SQL accepts .bak files via Cloud Storage.

Step 4: Create a dedicated read user

Do not use the sqlserver login for integration reads. Create a least-privilege login instead:

USE master;
GO
CREATE LOGIN integration_reader WITH PASSWORD = 'STRONG_PASSWORD_HERE';
GO

USE app_db;
GO
CREATE USER integration_reader FOR LOGIN integration_reader;
GO

-- Grant read access on the application schema
ALTER ROLE db_datareader ADD MEMBER integration_reader;
GO

-- Optional: grant VIEW DEFINITION so the integration can read schema metadata
GRANT VIEW DEFINITION TO integration_reader;
GO

Step 5: Verify the connection

note

The runtime must already be on an authorized network (or in a peered VPC) before this test will succeed. If the sqlcmd command times out or refuses the connection, confirm that the runtime's egress IP address is listed under Authorized networks or that VPC peering is active.

From your integration runtime, test connectivity:

sqlcmd -S <PUBLIC_OR_PRIVATE_IP>,1433 -U integration_reader -P 'STRONG_PASSWORD_HERE' -d app_db -Q "SELECT TOP 1 name FROM sys.tables;"

If the query returns a row, standard extraction is ready. Configure Data Integration with this connection using the SQL Server connection topic.

CDC setup

Use CDC when you need to capture inserts, updates, and deletes incrementally with full row history.

note

CDC on Cloud SQL for SQL Server uses Google-specific wrapper stored procedures. You cannot use sys.sp_cdc_enable_db directly because Cloud SQL blocks it. Use msdb.dbo.gcloudsql_cdc_enable_db instead.

Step 1: Confirm prerequisites

Before enabling CDC, verify the following:

• The SQL Server edition is Standard or Enterprise. Web and Express editions do not support CDC on Cloud SQL.
• The SQL Server version is 2017, 2019, or 2022.
• The application database exists and contains the tables you want to track.
• The tables you want to track have a primary key. CDC works without a primary key, but downstream merge logic typically requires one.

Step 2: Enable CDC at the database level

Run the following command as the sqlserver user. A plain db_owner cannot execute msdb.dbo.gcloudsql_cdc_enable_db — the procedure requires the elevated privileges held only by the sqlserver login. Any db_owner can run the table-level procedure sys.sp_cdc_enable_table in Step 4.

EXEC msdb.dbo.gcloudsql_cdc_enable_db 'app_db';
GO

To disable CDC later:

EXEC msdb.dbo.gcloudsql_cdc_disable_db 'app_db';

After you enable CDC, Cloud SQL automatically creates the cdc schema in app_db and registers capture and cleanup jobs.

Step 3: Verify the capture and cleanup jobs

USE app_db;
GO
EXEC sys.sp_cdc_help_jobs;
GO

The result should include two rows: one capture job and one cleanup job. If these rows are missing, CDC enablement failed. Re-check that your edition and version meet the prerequisites.

Step 4: Enable CDC on each source table

Run the following for each table you want to track:

USE app_db;
GO
EXEC sys.sp_cdc_enable_table
  @source_schema        = N'dbo',
  @source_name          = N'customer',
  @role_name            = N'cdc_reader_role',
  @supports_net_changes = 1;
GO

note

• @role_name creates a database role that gates access to the change table. Set it to NULL to remove role gating.
• @supports_net_changes = 1 requires a primary key or a unique index specified via @index_name, and enables cdc.fn_cdc_get_net_changes_*. Set it to 0 if you only need all-changes tracking.
• The change table is created as cdc.dbo_customer_CT.

To verify which tables are tracked:

EXEC sys.sp_cdc_help_change_data_capture;
GO

To disable CDC on a specific table:

EXEC sys.sp_cdc_disable_table
  @source_schema    = N'dbo',
  @source_name      = N'customer',
  @capture_instance = N'all';
GO

Step 5: Tune retention and job parameters

The default capture interval is every five seconds, and the default cleanup retention is three days (4,320 minutes).

To increase retention to 7 days:

EXEC sys.sp_cdc_change_job
  @job_type  = N'cleanup',
  @retention = 10080;  -- minutes
GO

To reduce capture latency:

EXEC sys.sp_cdc_change_job
  @job_type        = N'capture',
  @maxtrans        = 500,
  @maxscans        = 10,
  @pollinginterval = 2;  -- seconds
GO

After changing job parameters, restart the capture job:

EXEC sys.sp_cdc_stop_job  @job_type = N'capture';
EXEC sys.sp_cdc_start_job @job_type = N'capture';
GO

Step 6: Create a CDC reader user

The integration needs read access on the cdc schema and on any role gating the change tables.

USE master;
GO
CREATE LOGIN cdc_reader WITH PASSWORD = 'STRONG_PASSWORD_HERE';
GO

USE app_db;
GO
CREATE USER cdc_reader FOR LOGIN cdc_reader;
GO

-- Read source tables (required for initial snapshot)
ALTER ROLE db_datareader ADD MEMBER cdc_reader;
GO

-- Read CDC schema and change tables
GRANT SELECT ON SCHEMA::cdc TO cdc_reader;
GO

-- If you used @role_name when enabling CDC on tables, add cdc_reader to that role
ALTER ROLE cdc_reader_role ADD MEMBER cdc_reader;
GO

-- Optional: VIEW DATABASE STATE for monitoring queries
GRANT VIEW DATABASE STATE TO cdc_reader;
GO

Step 7: Verify CDC is capturing changes

note

The runtime must already be on an authorized network or in a peered VPC before the test queries below will succeed. If the connection fails, confirm network access as described in Step 2: Configure connectivity.

Insert a test row and read it back through the CDC functions:

USE app_db;
GO

INSERT INTO dbo.customer (id, name) VALUES (1, 'Test');
GO

-- Wait a few seconds for the capture job to process, then run:
DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_customer');
DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_customer(@from_lsn, @to_lsn, N'all');
GO

A successful result returns one row with __$operation = 2 (insert).

Step 8: Connect to Data Integration

After CDC is configured, provide the following details when setting up your Data Flow in Data Integration:

• Host (public or private IP address)
• Port (1433)
• Database name
• User (cdc_reader) and password
• Capture instance names (default format: <schema>_<table>, for example, dbo_customer)
• Initial LSN behavior (snapshot first or CDC-only)

Refer to the SQL Server connection and SQL Server CDC walkthrough topics for configuration steps in Data Integration.

Cloud SQL limitations and known issues

The following table lists known behavioral differences between Cloud SQL for SQL Server and on-premises SQL Server. For a full list maintained by Google, refer to the Cloud SQL for SQL Server known issues page.

Area	Behavior on Cloud SQL	Workaround
sysadmin role	Not granted to any user.	Use gcloudsql_* wrapper procedures where available.
Enable or disable CDC at the database level	sys.sp_cdc_enable_db is blocked.	Use msdb.dbo.gcloudsql_cdc_enable_db, run as sqlserver.
SQL Server Agent job ownership	Capture and cleanup jobs are owned and managed by Cloud SQL.	Modify only via sys.sp_cdc_change_job.
High availability and failover	CDC capture jobs run on the primary instance and automatically resume on the new primary after a regional failover. You do not need to intervene manually, but a brief gap may appear in the change stream during switchover.	Increase CDC retention to cover the expected failover window.
Read replicas	Capture jobs do not run on read replicas. To access CDC changes, connect to the primary instance.	Always point the CDC reader user at the primary instance endpoint.
Dropping a CDC-enabled database	Fails until CDC is disabled.	Run gcloudsql_cdc_disable_db first, then DROP DATABASE.
Restoring a CDC-enabled .bak file	Cloud SQL preserves the KEEP_CDC flag and recreates capture and cleanup jobs automatically.	None required.
xp_cmdshell and other host-level procedures	Disabled.	Use Cloud SQL features instead.

Troubleshooting

Issue	Cause	Resolution
Capture job not running	Job is disabled or failed to start.	Run EXEC sys.sp_cdc_help_jobs to check status, then restart with EXEC sys.sp_cdc_start_job @job_type = N'capture'.
Change tables growing too large	Cleanup job not running or retention is too high.	Lower retention using sys.sp_cdc_change_job, then run sys.sp_cdc_cleanup_change_table to force immediate cleanup.
Error 22832: "Could not update the metadata..."	CDC procedure run by a non-elevated user.	Run the procedure as sqlserver or as a member of db_owner on the database.
Initial snapshot timing out on large tables	Snapshot duration exceeds the CDC retention window.	Increase retention before starting the snapshot to avoid data loss.