Google Cloud SQL for SQL Server Setup Guide

Follow these instructions to replicate your Google Cloud SQL for SQL Server database to your destination using Fivetran.

Prerequisites

To connect your Google Cloud SQL for SQL Server database to Fivetran, you need:

A Google Cloud SQL for SQL Server database instance. If you don't have one, follow Google Cloud create instance instructions.
SQL Server 2012 - 2025
TLS 1.2 or higher enabled on your database if you connect directly, connect using Proxy Agent with TLS enabled, or connect using SSH with TLS enabled
Your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com)
Your database's port (usually 1433)
Additional requirements depending on the connection method you choose
(Optional, Private Preview only) To manage credentials outside of Fivetran, you need an External Secret Manager configured for your account before starting setup.

We do not support single-user mode.

Setup instructions

Choose connection method

If you're using Hybrid Deployment, skip this step. Hybrid Deployment manages its own secure connection to your database.

Decide on your preferred method for connecting Fivetran to your Google Cloud SQL for SQL Server database, and then configure the necessary settings for that method. This connector supports the following connection methods:

Connect directly (TLS required)

You must have TLS enabled on your database to connect directly to Fivetran. Follow Google Cloud TLS setup instructions to enable TLS on your database.

We require TLS 1.2 or higher.

Fivetran connects directly to your database instance. This is the simplest connection method.

To connect directly, configure your firewall and/or other access control systems to allow incoming connections to your SQL Server host and port (usually 1433) from Fivetran IP addresses for your database's region.

Connect using SSH (TLS optional)

Fivetran connects to a separate server in your network that provides an SSH tunnel to your database. You must connect through SSH if your database is in an inaccessible subnet.

To connect using SSH, follow our SSH connection instructions. If you want Fivetran to tunnel SSH over TLS, follow Google Cloud TLS setup instructions to enable TLS on your database.

If you enable TLS, we require TLS 1.2 or higher.

Connect using Google Cloud Private Service Connect

You must have a Business Critical plan to use Google Cloud Private Service Connect.

This connection method is in Beta.

Google Cloud Private Service Connect lets VPCs and Google-hosted or on-premises services communicate with one another without exposing traffic to the public internet. Learn more in Google Cloud Private Service Connect documentation.

Follow our Google Cloud Private Service Connect setup guide to configure Private Service Connect for your database.

Connect using Proxy Agent

Fivetran connects to your database through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS.

To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.

Enable database access

Grant Fivetran data processing servers access to your database.

These instructions apply to direct connections and SSH tunnels that connect to the instance's public IP address. If you use Google Cloud Private Service Connect, Proxy Agent, or an SSH tunnel that reaches the instance's private IP address, skip ahead to Create user.

In your Google Cloud Platform SQL dashboard, click your primary database instance.
In the left navigation menu, click Connections.
Click the Networking tab. Fivetran connects through your instance's public IP, so make sure public IP is enabled for your instance. If your instance was created with private IP only, enable public IP first. For more information, see Google Cloud public IP documentation.
In the Authorized networks section, click Add network.
In the New network window, create a network for Fivetran. Optionally, in the Name field, enter a name for the network (for example, Fivetran). What you enter in the Network field depends on your connection method.
- If you're connecting directly, enter Fivetran IP addresses for your database's region.
- If you're connecting using an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32, using the public IPv4 address from which your SSH server connects to your Cloud SQL instance.
Click Done, then click Save. It will take a few minutes for the database to be updated with the new settings.
Go to the Overview page of your instance and make a note of the instance's Public IP address. You need it to configure Fivetran.

Create user

Create a database user for Fivetran exclusive use.

Open a connection to your Google Cloud SQL for SQL Server primary database as the sqlserver user or another administrative user, in a SQL Server console (such as a SQL workbench, DataGrip, or SSMS). If you can't connect, set the password for the default sqlserver user first. For more information, see Google Cloud create and manage users documentation.
Run the following SQL statements to create a user for Fivetran. Replace <database> with the name of your database, <username> with the username of your choice, and <password> with a password of your choice:
```
USE [master];
CREATE LOGIN <username> WITH PASSWORD = '<password>';
USE [<database>];
CREATE USER <username> FROM LOGIN <username>;
```

Grant user permissions

Grant the Fivetran user SELECT permission for the data you want to sync. Choose the most appropriate scope:
- Database-level access: Grants access to all objects in a database.
```
GRANT SELECT ON DATABASE::[<database>] TO [<username>];
```
- Schema-level access: Grants access to all current and future tables in a schema.
```
GRANT SELECT ON SCHEMA::[<schema>] TO [<username>];
```
- Table-level access: Grants access only to specific tables.
```
GRANT SELECT ON [<schema>].[<table>] TO [<username>];
```
  If you grant table-level access, newly created tables aren't accessible automatically. Grant SELECT on each new table you want Fivetran to sync.
- Column-level access: Grants access only to specific columns in a table.
```
GRANT SELECT ON [<schema>].[<table>] ([<column 1>], [<column 2>], ...) TO [<username>];
```
  If you grant column-level access, newly added columns aren't accessible automatically. Rerun the command with the additional columns.
- Exclude specific columns: Grants access to all columns in a table except the ones you specify.
```
GRANT SELECT ON [<schema>].[<table>] TO [<username>];
DENY SELECT ON [<schema>].[<table>] ([<column X>], [<column Y>], ...) TO [<username>];
```
Grant VIEW DEFINITION permission to the same principal that has SELECT access so we can read column default definitions during schema changes. We need this access to detect and backfill new columns with deterministic default values. If the Fivetran user already has equivalent access through other permissions, you don't need an additional VIEW DEFINITION grant.
```
GRANT VIEW DEFINITION ON DATABASE::[<database>] TO [<username>];
```
You can also grant VIEW DEFINITION on a specific schema or table if you want to scope it more narrowly.
(Optional) If you are using Change Tracking with history mode enabled, to improve performance and reduce sync delays, you may want to grant the Fivetran user the necessary permissions by running the following SQL statement:
```
USE [master]; GRANT VIEW SERVER STATE TO [<username>];
```
If your SQL Server instance is version 2022 or later, grant VIEW SERVER PERFORMANCE STATE instead of VIEW SERVER STATE.
This grants the Fivetran user access to server state information, minimizing the dependency on checkpoint timing and reducing update delays. See the Sync Delays with SQL Server for Linux Using Change Tracking with History Mode Enabled troubleshooting page for details.

Enable incremental updates

For incremental updates, choose an update method. These update methods let Fivetran copy only the rows that have changed since the last data sync, so we don't have to copy the whole table every time. Learn more in our updating data documentation.

Read changes using CT or CDC mechanisms

Use this update method if you want Fivetran to read new, modified, or deleted data using SQL Server-native change capture mechanisms: change tracking (CT), change data capture (CDC), or both. You don't need to enable both CT and CDC for every table.

Fivetran chooses the mechanism to use for each table based on your SQL Server configuration:

If a table has only CT enabled, we use CT.
If a table has only CDC enabled, we use CDC.
If a table has both CT and CDC enabled, we use CDC.
If a table has neither CT nor CDC enabled, the table appears in Fivetran but is disabled until you enable CT or CDC.

Use the following instructions to enable CT, CDC, or both based on the tables you want to sync.

You can't enable CT on an availability group replica. If you enable CDC on your primary instance, it applies to your availability group replica. To use CT, connect Fivetran to the primary instance instead. See our Supported Configurations documentation for more information.

Enable change tracking

Enable change tracking at the database level:
```
ALTER DATABASE [<database>] 
SET CHANGE_TRACKING = ON 
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);
```
While we recommend seven days of change retention, you can set your retention period as low as one day. However, a shorter retention period increases the risk that your logs will expire in between syncs, triggering an automatic full source re-sync.

Enable CT for each table you want to integrate:

ALTER TABLE [<schema>].[<table>] ENABLE CHANGE_TRACKING;

Grant the Fivetran user VIEW CHANGE TRACKING permission for each of the tables that have change tracking enabled:
```
GRANT VIEW CHANGE TRACKING ON [<schema>].[<table>] TO [<username>];
```

Enable change data capture

Enable change data capture at the database level:

EXEC msdb.dbo.gcloudsql_cdc_enable_db '[<database>]';

Enable CDC for each table you want Fivetran to sync:
```
EXEC sys.sp_cdc_enable_table  
@source_schema = N'<schema>',
@source_name   = N'<table>',
@role_name     = NULL,
@supports_net_changes = 0;
```
To check if CDC is enabled, run the following SQL statement:
```
EXECUTE sys.sp_cdc_help_change_data_capture
@source_schema = N'<schema>',
@source_name   = N'<table>';
```
When @supports_net_changes is set to 1, an additional non-clustered index is created on the change table. Because this index needs to be maintained, enabling net changes can degrade CDC performance.

Fivetran only supports tables with a single CDC capture instance. Our syncs only include tables and columns that are present in a CDC instance. If you add new tables or columns, you must create a new CDC instance that includes them and delete the old instance.

Automatically enable CT or CDC for schema changes (optional)

By default, when you create a new table in your database, you must manually enable CT or CDC for that table before we can sync it. Additionally, if you add a new column to an existing CDC-enabled table, you must manually recreate the CDC capture instance before we can sync the column.

If you want Fivetran to automatically enable CT or CDC for newly created tables and to update CDC capture instances when a new column is added, install the following stored procedures on your database. If your connector contains only CT-enabled tables, then any subsequent new tables will be CT-enabled. If your connector has a mixture of CT- and CDC-enabled tables or contains CDC-enabled tables only, then we will enable CDC over CT by default.

Install the following script on your database. Before running the script, replace <database> with your database name.

If you have configured your connector to Allow columns, you can exclude the [dbo].[sp_ft_enable_cdc] and [dbo].[sp_ft_enable_change_tracking] stored procedures. If you have configured your connector to Block All, you can exclude all the following stored procedures.

USE [<database>];

/****** Object:  StoredProcedure [dbo].[sp_ft_enable_cdc]    Script Date: 1/17/2024 11:12:55 PM ******/
SET ANSI_NULLS ON
GO
   
SET QUOTED_IDENTIFIER ON
GO
   
CREATE OR ALTER PROCEDURE [dbo].[sp_ft_enable_cdc]
   @TableList NVARCHAR(MAX),
   @FivetranUser NVARCHAR(MAX)
WITH EXECUTE AS OWNER -- Change to an appropriate sysadmin user
AS
BEGIN
   SET NOCOUNT ON;

   -- Table to store tables with results
   CREATE TABLE #ResultTables (TableName NVARCHAR(MAX), Result NVARCHAR(MAX));

   -- Enable CDC for each table
   DECLARE @TableName NVARCHAR(MAX);
   DECLARE @SqlStatement NVARCHAR(MAX);

   WHILE LEN(@TableList) > 0
   BEGIN
      -- Get the first table in the list
      SET @TableName = NULL;
      SET @TableName = SUBSTRING(@TableList, 1, CHARINDEX(',', @TableList + ',') - 1);

      -- Remove the processed table from the list
      SET @TableList = STUFF(@TableList, 1, LEN(@TableName) + 1, '');

      -- Build and execute the SQL statement to enable CDC for the table
      SET @SqlStatement = '
      BEGIN TRY
         EXEC sys.sp_cdc_enable_table
             @source_schema = ''' + PARSENAME(@TableName, 2) + ''',
             @source_name   = ''' + PARSENAME(@TableName, 1) + ''',
             @role_name     = ''' + @FivetranUser + ''',
             @supports_net_changes = 0;
         
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ''Success'');
      END TRY
      BEGIN CATCH
         PRINT ERROR_MESSAGE();
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ERROR_MESSAGE());
      END CATCH
      ';

     -- Execute the dynamic SQL statement
     EXEC sp_executesql @SqlStatement;
   END

   -- Return the list of tables with results
   SELECT * FROM #ResultTables;
      
   -- Drop the temporary table
   DROP TABLE #ResultTables;
END;
GO


/****** Object:  StoredProcedure [dbo].[sp_ft_enable_change_tracking]    Script Date: 1/17/2024 11:10:45 PM ******/
SET ANSI_NULLS ON
GO
   
SET QUOTED_IDENTIFIER ON
GO
   
CREATE OR ALTER PROCEDURE [dbo].[sp_ft_enable_change_tracking]
   @TableList NVARCHAR(MAX),
   @FivetranUser NVARCHAR(MAX)
WITH EXECUTE AS OWNER -- Change to an appropriate sysadmin user
AS
BEGIN
   SET NOCOUNT ON;
      
   -- Table to store tables with results
   CREATE TABLE #ResultTables (TableName NVARCHAR(MAX), Result NVARCHAR(MAX));
      
   -- Enable change tracking for each table
   DECLARE @TableName NVARCHAR(MAX);
   DECLARE @SqlStatement NVARCHAR(MAX);
      
   WHILE LEN(@TableList) > 0
   BEGIN
      -- Get the first table in the list
      SET @TableName = NULL;
      SET @TableName = SUBSTRING(@TableList, 1, CHARINDEX(',', @TableList + ',') - 1);
      
      -- Remove the processed table from the list
      SET @TableList = STUFF(@TableList, 1, LEN(@TableName) + 1, '');
         
      -- Build and execute the SQL statement to enable change tracking
      SET @SqlStatement = '
      BEGIN TRY
         ALTER TABLE ' + @TableName + ' ENABLE CHANGE_TRACKING;
         GRANT VIEW CHANGE TRACKING ON ' + @TableName + ' TO ' + @FivetranUser + ';
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ''Success'');
      END TRY
      BEGIN CATCH
         PRINT ERROR_MESSAGE();
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ERROR_MESSAGE());
      END CATCH
      ';
         
      -- Execute the dynamic SQL statement
      EXEC sp_executesql @SqlStatement;
   END
      
   -- Return the list of tables with results
   SELECT * FROM #ResultTables;
      
   -- Drop the temporary table
   DROP TABLE #ResultTables;
END;
GO

  
/****** Object:  StoredProcedure [dbo].[sp_ft_get_tables_with_ddl_changes]    Script Date: 1/30/2024 9:10:47 PM ******/
SET ANSI_NULLS ON
GO
   
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[sp_ft_get_tables_with_ddl_changes]
   @TableList NVARCHAR(MAX),
   @ddlTime DATETIME = NULL -- New parameter for ddlTime
WITH EXECUTE AS OWNER -- Change to an appropriate sysadmin user
AS
BEGIN
   SET NOCOUNT ON;

   -- Declare a table variable to store the results
   DECLARE @ChangedTables TABLE (
      TableName NVARCHAR(MAX),
      DdlTime DATETIME
   );

   -- Split the comma-separated list into a table variable
   DECLARE @TableNames TABLE (
      TableName NVARCHAR(MAX)
   );
      
   INSERT INTO @TableNames (TableName)
   SELECT value
   FROM STRING_SPLIT(@TableList, ',');
      
   -- Check for DDL changes in cdc.ddl_history
   INSERT INTO @ChangedTables (TableName, DdlTime)
   SELECT
      tn.TableName,
      MAX(dh.ddl_time) AS LatestDdlTime
   FROM cdc.ddl_history dh
   INNER JOIN @TableNames tn ON CONCAT(OBJECT_SCHEMA_NAME(dh.source_object_id), '.', OBJECT_NAME(dh.source_object_id)) = tn.TableName
   WHERE @ddlTime IS NULL OR dh.ddl_time >= @ddlTime
   GROUP BY tn.TableName;
      
   -- Return the list of tables with the latest DDL changes
   SELECT TableName, DdlTime
   FROM @ChangedTables;
END;
GO



/****** Object:  StoredProcedure [dbo].[sp_ft_update_cdc]    Script Date: 1/31/2024 1:19:40 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[sp_ft_update_cdc]
   @TableName NVARCHAR(MAX),
   @FivetranUser NVARCHAR(MAX),
   @CaptureInstance NVARCHAR(MAX),
   @CapturedColumnList NVARCHAR(MAX)
WITH EXECUTE AS OWNER
AS
BEGIN
   SET NOCOUNT ON;

    -- Table to store tables with results
    CREATE TABLE #ResultTables (TableName NVARCHAR(MAX), Result NVARCHAR(MAX));

    DECLARE @SourceSchema NVARCHAR(MAX);
    DECLARE @SourceName NVARCHAR(MAX);

    BEGIN TRY
        -- Assign values to variables for PARSENAME function
        SET @SourceSchema = PARSENAME(@TableName, 2);
        SET @SourceName = PARSENAME(@TableName, 1);

        -- Step 1: Disable the current CDC instance
        EXEC sys.sp_cdc_disable_table
            @source_schema    = @SourceSchema,
            @source_name      = @SourceName,
            @capture_instance = @CaptureInstance;

        -- Step 2: Create a new CDC instance
        EXEC sys.sp_cdc_enable_table
            @source_schema        = @SourceSchema,
            @source_name          = @SourceName,
            @role_name            = @FivetranUser,
            @capture_instance     = @CaptureInstance,
            @captured_column_list = @CapturedColumnList,
            @supports_net_changes = 0;

        -- Insert the result into the #ResultTables table
        INSERT INTO #ResultTables (TableName, Result) VALUES (@TableName, 'Success');
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
        -- Insert the error message into the #ResultTables table
        INSERT INTO #ResultTables (TableName, Result) VALUES (@TableName, ERROR_MESSAGE());
    END CATCH;

    -- Return the list of tables with results
    SELECT * FROM #ResultTables;

    -- Drop the temporary table
    DROP TABLE #ResultTables;
END;
GO

Grant the Fivetran user execute permission.

USE [<database>];
GRANT EXECUTE ON dbo.sp_ft_enable_cdc TO <username>;
GRANT EXECUTE ON dbo.sp_ft_enable_change_tracking TO <username>;
GRANT EXECUTE ON dbo.sp_ft_get_tables_with_ddl_changes TO <username>;
GRANT EXECUTE ON dbo.sp_ft_update_cdc TO <username>;

Detect changes using Fivetran Teleport Sync

You don't need to do any additional configuration to use Fivetran Teleport Sync.

Finish Fivetran configuration

In your connection setup form, enter a Destination schema prefix. This is used as the connection name and cannot be modified once the connection is created.
In the Destination names field, choose the naming convention you want Fivetran to use for the schemas, tables, and columns in your destination:
- Source naming: Preserves the original schema, table, and column names from the source system in your destination, and ignores the Destination schema prefix specified in the setup form. However, when multiple connections share the same source schema name (for example, public), Fivetran stores their tables in the same destination schema. Tables with duplicate names may lead to overwrites and data inconsistencies. Be sure to use unique table names across connections that write to the same schema.
- Fivetran naming: Standardizes the schema, table, and column names in your destination according to the Fivetran naming conventions.
If you want to modify your selection, make sure you do it before you start the initial sync.
Depending on your selection, we will either prefix the connection name to each replicated schema or use the source schema names instead.
In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com). If you connect directly or through an SSH tunnel, use the Public IP address you noted in the Enable database access step. If you use Google Cloud Private Service Connect, use the connection URL that Fivetran provided.
In the Port field, enter your database instance's port number. The port is usually 1433.
In the User field, enter the Fivetran-specific user that you created in the Create user step.
In the Password field, enter the password for the Fivetran-specific user that you created in the Create user step.
In the Database field, enter the name of your database (for example, your_database).
(Hybrid Deployment only) If your destination is configured for Hybrid Deployment, the Hybrid Deployment Agent associated with your destination is pre-selected for the connection. To assign a different agent, click Replace agent, select the agent you want to use, and click Use Agent.
(Not applicable to Hybrid Deployment) From the Connection method drop-down menu, select how Fivetran connects to your database and enter the required information:
- Connect directly
- Connect via an SSH tunnel
  - Make a note of the Public Key and add it to the authorized_keys file while configuring the SSH tunnel.
  - In the SSH Host field, enter the hostname or IP address of your SSH server. Do not use a load balancer's IP address or hostname.
  - In the SSH Port field, enter the port used by the SSH server.
  - In the SSH User field, enter the username of the SSH user.
  - If you enabled TLS on your database in the Choose connection method step, keep the Require TLS through Tunnel toggle turned ON.
- Connect via Private Networking
  - Fivetran enables TLS for private networking connections by default. To disable it, turn the Require TLS when using Private Networking toggle OFF.
- Connect via Proxy Agent
  - Select an existing agent from the Proxy agents drop-down list or click + Configure a new proxy agent to set up a new agent.
  - If you enabled TLS on your database in the Choose connection method step, keep the Require TLS when using Proxy Agent toggle turned ON.
Choose your incremental Update Method:
- Read changes using CT or CDC mechanisms
- Detect Changes via Fivetran Teleport Sync
Click Save & Test. Fivetran tests and validates our connection to your Google Cloud SQL for SQL Server database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup tests

Fivetran performs the following tests to ensure that we can connect to your Google Cloud SQL for SQL Server database and that it is properly configured:

The Validating form input test checks that the values you provided in the setup form are valid, including your host, port, database, user, password, connection method, and update method. If you connect directly, it also checks that the database host you provided isn't a private IP address that's inaccessible from the internet.
The Connecting to SSH tunnel test validates the SSH tunnel details you provided in the setup form. It then checks that we can connect to your database using the SSH tunnel. We skip this test if you aren't connecting using SSH.
The Checking Host connectivity test checks that we can connect to your database host.
The Connecting to database test validates the database credentials you provided in the setup form and checks that we can access your database. If Always Encrypted is enabled, it also validates the TLS certificate.
The Validating replication config test verifies that your database has CT or CDC enabled. We skip this test if you're using Fivetran Teleport Sync.

If you selected Connect via Proxy Agent, Fivetran checks the Proxy Agent status instead of running the Connecting to SSH tunnel test.

The tests may take a few minutes to finish running.

Connector Overview

Schema Information

Release Notes

API Connector Configuration

Documentation Home