Google Cloud SQL Server (Edge)

You must enable change tracking on your SQL Server database to replicate incremental data.

Perform the following steps to do this:

1. Connect to your SQL Server database as any user with the ALTER DATABASE privilege or as a masteruser, using an SQL client tool such as sqlcmd.

2. Run the following commands to enable change tracking:

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

   1. At the database level:

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

      The CHANGE_RETENTION value specifies the duration for which change tracking information is retained. When the AUTO_CLEANUP value is set to ON, SQL Server runs a cleanup task to remove lapsed or invalid change tracking metadata. Read Enable Change Tracking for a Database for more information on the available options.

      Note: Hevo recommends setting the CHANGE_RETENTION value to 7 DAYS. This reduces the risk of log expiry and missed data changes for Pipelines with a low sync frequency, such as 24 hours.

3. At the table level:

    ```sql
    ALTER TABLE <schema_name>.<table_name> ENABLE CHANGE_TRACKING
    ```
   

   Run the command given above for each table that you want to replicate. Read Enable Change Tracking for a Table for more information on the available options.

You must enable change data capture on your SQL Server database to replicate incremental data.

Perform the following steps to do this:

1. Connect to your SQL Server database as a masteruser with an SQL client tool, such as sqlcmd.

2. Run the following commands to enable change data capture:

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

   1. At the database level:

      EXEC msdb.dbo.gcloudsql_cdc_enable_db '<database_name>';

   2. At the table level:

      USE <database_name>;
      
      --Begin tracking a table
      exec sys.sp_cdc_enable_table
          @source_schema          = N'<schema_name>'
      ,   @source_name            = N'<table_name>'
      ,   @role_name              = NULL;
      

      Run the command given above for each table that you want to replicate using CDC. Read Enable Table CDC for more information on the available options.

Note: Skip to the Grant privileges to the user section if you are using an existing database user.

Perform the following steps to create a database user in your Google Cloud SQL Server database:

1. Connect to your Google Cloud SQL Server database as a masteruser with any SQL client tool, such as sqlcmd.

2. Run the following commands:

   ```sql – Select the master database to create a login USE master; CREATE LOGIN WITH PASSWORD = '';

   – Add a database user to your database for the login created above USE ; CREATE USER FOR LOGIN ;`

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

To enable your database user to replicate data from Change Tracking–enabled tables, the following privileges are required:

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 commands:

-- 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.

To enable your database user to replicate data from CDC-enabled tables, perform the following steps:

1. Connect to your Google Cloud SQL Server database as a masteruser with any SQL client tool, such as sqlcmd.

2. Run the following commands:

   Note: Replace the placeholder values in the commands below with your own. For example, __ with _hevo_.

   USE <database_name>;
   
   -- Grant SELECT privilege at the schema level
   GRANT SELECT ON SCHEMA::<schema_name> TO <database_user>;
   
   -- Optionally, grant read access to the CDC schema
   GRANT SELECT ON SCHEMA::cdc TO <database_user>;
   

Note: Hevo recommends using a dedicated, minimum-privilege database user in production Pipelines.

Perform the following steps to obtain the configuration details required to create your Hevo Pipeline:

1. Retrieve the database hostname

1. Log in to the Google Cloud SQL Console.

2. Locate the database hostname of the master instance under the Public IP address column.

   

Note: Google Cloud SQL always uses the default port number, 1433, for the SQL Server instance.

2. Retrieve the database names

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 Databases.

   

On the Databases page, you can locate the name of your database under the Name column.