Skip to main content
Documentation
Troubleshooting
Feature Requests Status

Google Cloud SQL for SQL Server Setup Guide

Edit on GitHub
  • Updated
Fivetran System User Fivetranner

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

Prerequisiteslink

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’s create instance instructions.
  • SQL Server 2012 - 2019
  • Your database host’s IP (e.g., 1.2.3.4) or domain (your.server.com)
  • Your database’s port (usually 1433)

IMPORTANT: We do not support single-user mode.

----------

Setup instructionslink

Choose connection method link

First, decide whether to connect your Google Cloud SQL for SQL Server database to Fivetran directly or using an SSH tunnel.

Connect directly (TLS required)

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

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’s IPs 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, do the following:

  1. In your connector setup form, select Connect via an SSH tunnel to expose Fivetran’s public SSH key. Copy the key by clicking the blue clipboard icon:

    find public key in connector setup form

  2. Add the public key to the authorized_keys file of your SSH server. The key must be all on one line, so make sure that you don’t introduce any line breaks when cutting and pasting.

  3. If you want Fivetran to tunnel SSH over TLS, follow Google Cloud’s TLS setup instructions to enable TLS on your database.

Enable database accesslink

Grant Fivetran’s data processing servers access to your database.

  1. In your Google Cloud Platform SQL dashboard, click on your master database.

    Sqlserver-gcs-select-master

  2. In the left menu, go to the Connections tab.

  3. In the Networking section, click Add network.

    Sqlserver-gcs-add-network

  4. In the New network window, create a network for Fivetran. What you enter in the Network field depends on whether you’re connecting directly or using an SSH tunnel.

    • If you’re connecting directly, enter Fivetran’s IPs for your database’s region.
    • If you’re connecting using an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32.

    When you’ve finished, click Done, then click Save. It will take a few minutes for the database to be updated with the new settings.

    SqlServer-gcs-authorize-network

  5. In the left menu, go to the Overview tab.

  6. In the Connect to this instance section, find your database’s public IP address and make a note of it. You will need it to configure Fivetran.

    SqlServer-gcs-find-IP-address

Create user link

Create a database user for Fivetran’s exclusive use.

  1. Open a connection to your Google Cloud SQL for SQL Server master database in a SQL Server console (such as a SQL workbench, DataGrip, or mssql).

  2. Create a user for Fivetran by executing the following SQL command. Replace <username> and some-password with a username and password of your choice.

    CREATE USER <username> PASSWORD 'some-password';

Grant user permissionslink

Grant the Fivetran user SELECT permission for the databases, schemas, tables, or specific columns you want Fivetran to sync.

You can grant access to everything in a given database:

GRANT SELECT on DATABASE::<database> to <username>;

or all tables in a given schema:

GRANT SELECT on SCHEMA::<schema> to <username>;

or a specific table:

GRANT SELECT ON [<schema>].[<table>] TO <username>;

or a set of specific columns in a table:

GRANT SELECT ON [<schema>].[<table>] ([<column 1>], [<column 2>], ...) TO <username>;

Enable incremental updateslink

We use one of SQL Server’s two built-in tracking mechanisms for incremental updates: change tracking (CT) and change data capture (CDC). When enabled, both CT and CDC keep a record of the table rows that have changed in a certain window of time (the default window is the most recent 2 days). These mechanisms 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.

Choose to enable either change tracking or change data capture. Depending on whether you are connecting Fivetran to your primary instance or an availability group replica, you may be limited in the mechanism you can choose. See our Supported Configurations documentation for more information.

To learn more about CT and CDC, see our Updating Data documentation.

Change tracking

  1. Enable change tracking at the database level:

    ALTER DATABASE [<database>] SET CHANGE_TRACKING = ON;

  2. Enable CT for each table you want to integrate:

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

  3. Grant the Fivetran user VIEW CHANGE TRACKING permission for each of the tables that have CT enabled:

    GRANT VIEW CHANGE TRACKING ON [<schema>].[<table>] TO <username>;

Change data capture

  1. Enable change data capture at the database level:

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

  2. Enable CDC for each table you want to Fivetran to sync:

    EXEC sys.sp_cdc_enable_table  
@source_schema = [<schema>],
@source_name   = [<table>],
@role_name     = [<username>];

    To check if CDC is enabled, run the query below:

    EXEC sys.sp_cdc_enable_table
  @source_schema = N'[<schema>]',
  @source_name = N'[<table>]',
  @role_name = N'CDC'

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

Finish Fivetran configurationlink

  1. In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.

  2. In the Host field, enter your database host’s IP (for example, 1.2.3.4) or domain (for example, your.server.com).

  3. Enter your database instance’s port number. The port number is usually 1433.

  4. Enter the Fivetran-specific user that you created in Step 3.

  5. Enter the password for the Fivetran-specific user that you created in Step 3.

  6. Enter the name of your database (for example, your_database).

  7. Choose your connection method. If you selected Connect via an SSH tunnel, provide the following information:

    • SSH hostname (do not use a load balancer’s IP address/hostname)
    • SSH port
    • SSH user
    • If you enabled TLS on your database in Step 1, set the Require TLS through tunnel toggle to ON.

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

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 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 Connecting to Host Test validates the database credentials you provided in the setup form. The test verifies that the host is not private and then checks the connectivity to the host.
  • The Validating Certificate Test generates a pop-up window where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you’re connecting using a Google Cloud Virtual Machine.)
  • The Connecting to Database Test checks that we can access your database.
  • The Checking statement_timeout Value Test checks that we can access your database’s pg_settings table. It then verifies that the statement_timeout value is greater than 5 minutes.

NOTE: The tests may take a few minutes to finish running.

Related Contentlink

description Connector Overview

account_tree Schema Information

assignment Release Notes

settings API Connector Configuration

home Documentation Home

Was this article helpful?
0 out of 0 found this helpful
Return to top

Related articles

Didn’t find what you need?

Contact support