Prerequisites

• The Google Cloud SQL Server instance is running. To check this, log in to the Google Cloud SQL Console and look for an active indication in the Status field for your instance.

  

• The Google Cloud SQL Server version is 2019 or higher.

• Change Tracking is enabled for the database and table.

• Hevo’s IP address(es) for your region is added to the Google Cloud SQL Server database IP Allowlist.

• The SELECT and VIEW CHANGE TRACKING privileges are granted to the database user.

  Note: We recommend that you create a database user for configuring your Google Cloud SQL Server Change Tracking Source in Hevo. If you already have a database user, grant the required privileges.

• The database hostname, port number, and database names of the Source instance are available.

Perform the following steps to configure your Google Cloud SQL Server Change Tracking Source:

Enable Change Tracking

The Change Tracking mechanism captures changes made to a database. To enable change tracking, connect to your SQL Server database as a user with ALTER DATABASE privilege using any SQL client tool, such as sqlcmd, and enter the following commands:

Note: Replace the placeholder values in the commands below with your own. For example, <database_name> with demo.

1. Enable change tracking at the database level:

   ALTER DATABASE <database_name> SET CHANGE_TRACKING = ON 
   (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON)
   

   The CHANGE_RETENTION value specifies the duration for which change tracking information is retained. You can use AUTO_CLEANUP to enable or disable the cleanup task that removes old change tracking information. Read Enable Change Tracking for a Database.

   Note: Hevo recommends that you set the CHANGE_RETENTION value to 3 DAYS. This reduces the risk of log expiry in the case of Pipelines having a low sync frequency. For example, 24 hours.

2. Enable change tracking for each table that you want to replicate:

   ALTER TABLE <schema_name>.<table_name> ENABLE CHANGE_TRACKING
   

   Repeat this step for each table you want to replicate using Change Tracking. Read Enable Change Tracking for a Table.

   Note: If the Schema Evolution Policy for your Pipeline is set to Allow all changes and a new table is created in your schema after the Pipeline creation, you need to enable change tracking for the new table. After that, refresh the schema so that Hevo can begin ingesting data from it. If change tracking is not enabled, Hevo marks the object as inaccessible and does not ingest any data from it.

Allowlist Hevo IP addresses for your region

You need to allowlist the Hevo IP address for your region to enable Hevo to connect to your Google Cloud SQL Server database. To do this:

1. Log in to the Google Cloud SQL Console and click the Instance ID that you want to connect to Hevo.

   

2. In the left navigation pane, click Connections.

   

3. On the Connections page, click the NETWORKING tab, scroll down to the Authorized networks section, and then click ADD A NETWORK.

   

4. In the New network panel, specify the following:

   

   • Name: A name to describe the region for Hevo’s IP address.

   • Network: The IP address of your Hevo region in CIDR notation.

5. Click DONE.

6. (Optional) Click ADD A NETWORK and repeat steps 4 and 5 to add all the IP addresses you want to add.

7. Click SAVE.

   

Create a Database User and Grant Privileges

1. Create a database user (Optional)

2. Grant privileges to the user

The database user for Hevo requires the following privileges to connect to and ingest data from your Google Cloud SQL Server database:

Connect to your Google Cloud SQL Server database as a masteruser with any SQL client tool, such as sqlcmd, and run the following script:

# Grant SELECT privilege at the database level
GRANT SELECT ON DATABASE::<database_name> TO <database_username>;

# Grant SELECT privilege at the schema level
GRANT SELECT ON SCHEMA::<schema_name> TO <database_username>;

# Grant VIEW CHANGE TRACKING privilege at the schema level
GRANT VIEW CHANGE TRACKING ON SCHEMA::<schema_name> TO <database_username>;

# Grant VIEW CHANGE TRACKING privilege at the table level
GRANT VIEW CHANGE TRACKING ON OBJECT::<schema_name>.<table_name> TO <database_username>;

Note: Replace the placeholder values in the commands above with your own. For example, <database_username> with hevo.

Retrieve the Configuration Details (Optional)