Fivetran Beta Setup Guide: SQL Server - FleetOps Support

Welcome to the Beta Group for FleetOps' newest data movement solution! We are upgrading to use Fivetran to extract data securely from your SQL Server and sync it to the FleetOps cloud. Here is a guide on the prerequisites, installation steps and configuration.

The Beta is open to users with a TMS backed by SQL server 2016+ (Enterprise edition or Standard edition with SP1)

You do not need to make any firewall/networking changes, as Fivetran will connect to the SQL Server via a proxy service which opens an outgoing socket to their cloud.

ℹ️ This guide will take 30-60 min to complete.

ℹ️ If you have multiple databases within SQL Server which you want to sync to FleetOps, please contact us first so we can send you a form per database.

⚠️ You will need administrator access to Windows Servers and SQL Server to complete this guide.

⚠️ This guide will require you to restart the SQL Server Service if you don’t already have an SSL Certificate set up. Only complete this guide when downtime is acceptable.

❗We highly recommend you monitor performance and database usage before and after enabling CDC and FleetOps running the initial historical data and incremental sync.

There are two guides in the Troubleshooting document: Low Disk Space and Server Performance. Each of these should take 5-10 min to complete. These should be done at least a day before this guide is followed and CDC is enabled, so you have metrics for a normal work day for your server.

1. Prerequisites

You will need to make some small changes on your SQL Server and install an application on a Windows/Linux server with network access to the same SQL Server. You will need to prepare the following before continuing:

Windows Server running SQL Server for your TMS
- Administrator access to both Windows and SQL Server
- SQL Server Management Studio
- SQL Server Configuration Manager
Local network ip/host and port of your SQL Server
Name of the database(s) which you want to sync to FleetOps

ℹ️ If you have multiple databases inside your SQL Server to sync to FleetOps, repeat steps 2 and 4 for each of them.

List of tables inside these databases which are to be synced (see section 2.1 for examples per TMS), and a black list of any columns/tables which we should not sync.

A separate Windows/Linux server to install the proxy on
- Windows administrator access to this server
- No minimum system requirements. It is a very lightweight application, but the server should be up 24/7 with minimal downtime and good network connectivity.
- Network access to your SQL Server
SSL cert for the SQL Server or follow instructions below to create a self signed one

Then FleetOps will send you an email with:

the Fivetran Proxy installer
a proxy config file (proxy.json)
a link to enter database connection settings

2. Database Setup

ℹ️ In the SQL scripts below, the texts , ,

and are placeholders which you should replace with your database information.

ℹ️ These SQL scripts should be entered into the SQL Server Management Studio while logged in as an administrator user.

2.1. Enable Change Data Capture and User Access

We use CDC (Change Data Capture) to efficiently sync changes from your database to FleetOps' data warehouse. If you want to learn more about CDC and how it works, take a look at this explanation from Microsoft.

ℹ️ If this is the first time you use CDC on your database, we recommend you take a look at your baseline CPU / Memory / Disk IO usage for SQL Server. You can do this via Task Manager / Resource Monitor / Performance Monitor or similar monitoring tools. Also check the free disk space on your server. Then compare these values to new ones after enabling CDC.

You have to enable CDC first per database, then per table.

USE ;

/*Enable CDC for the database specified above*/
EXEC sys.sp_cdc_enable_db  
GO

Enabling CDC for the database you want to sync

Next set the CDC retention period to 3 days, allowing time for the sync to be recovered if there is an interruption. This does come at the cost of more disk space. If you have limited space on your SQL Server, discuss the retention period to use with your FleetOps Solutions Engineer.

USE ;

/*Retain CDC information for 3 days (4320 minutes)*/
EXEC sys.sp_cdc_add_job 
    @job_type=N'Cleanup',
    @retention=4320
GO

/* If the above job fails, run this instead */
EXEC sys.sp_cdc_change_job 
    @job_type=N'Cleanup',
    @retention=4320
GO

Now you have to enable CDC for each table inside this database which you want to sync to FleetOps. We have prepared the scripts below based different application database structures. Pick the relevant one and review it, as it’s not unusual for these tables to be customized for your specific needs:

Common command to enable CDC for a table (If the table doesn't have a primary key, you will have to change @supports_net_changes to 0)

USE ;

EXEC sys.sp_cdc_enable_table
    @source_schema = N'',
    @source_name   = N'

',
 @role_name     = null,
 @supports_net_changes = 1
GO

Enable CDC for tables in McLeod

USE ;

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'driver',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'tractor',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'trailer',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'payee',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'users',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'fleet',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'equipment_item',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'equipment_group',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'customer',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'location',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'zones',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'zone_area',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'zone_region',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'zipzone',
    @role_name     = null,
    @supports_net_changes = 0
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'orders',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'stop',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'servicefail',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'movement',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'movement_order',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'prorated_moveorder',
    @role_name     = null,
    @supports_net_changes = 0
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'journal_cash',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'open_item',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'edi_standard_code',
    @role_name     = null,
    @supports_net_changes = 0
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'seg_alloc_code',
    @role_name     = null,
    @supports_net_changes = 0
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'billing_history',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'misc_bill_hist',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'mb_detail_hist',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'other_charge',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'driver_hours',
    @role_name     = null,
    @supports_net_changes = 0
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'driver_extra_pay',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'drs_deduct_hist',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'drs_payroll_hist',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'auto_home_history',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'equipstatus',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'equipstatuscode',
    @role_name     = null,
    @supports_net_changes = 0
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'inspection',
    @role_name     = null,
    @supports_net_changes = 0
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'inspect_violation',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'motoraccident',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'accident_cost',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'fuel_ticket_hist',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'gl_account',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'gl_ledger',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'gl_ledger_hist',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'fiscal_year_dtl',
    @role_name     = null,
    @supports_net_changes = 0
G

Enable CDC for tables in TMW Suite

USE ;

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'ACCIDENT',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'carrier',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'chargetype',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'city',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'commodity',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'company',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'edi_orderstate',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'expiration',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'fuelpurchased',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'invoicedetail',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'invoiceheader',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'labelfile',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'legheader',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'log_driverlogs',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'manpowerprofile',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'orderheader',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'paydetail',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'payheader',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'SAFETYREPORT',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'stops',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'tariffheader',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'tariffrate',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'tractorprofile',
    @role_name     = null,
    @supports_net_changes = 1
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'trailerprofile',
    @role_name     = null,
    @supports_net_changes = 1
GO

2.2 Monitoring CDC

Now that CDC is enabled you should monitor the process to see it is running correctly.

First check the CDC error logs. This will contain errors for the 32 most recent log scans. It should be empty if there are no errors.

SELECT * FROM sys.dm_cdc_errors
ORDER BY entry_time DESC

Then check the CDC log scans. This should start containing entries within less than a minute of enabling CDC.

SELECT * FROM sys.dm_cdc_log_scan_sessions
ORDER BY start_time DESC

You could also check the Task Manager / Resource Monitor / Performance Monitor of your server to see the impact of enabling CDC on CPU, Memory and Disk IO. Take note of current values and compare them each hour with the current values during normal operation of your database and applications.

2.3 Setup a restricted User

Create a user just for the data sync with only the access they need. Replace below with a secure, randomly generated password (eg. using this Password Generator)

USE ;
CREATE LOGIN fleetops WITH PASSWORD = '';
CREATE USER fleetops FOR LOGIN fleetops;

Select the script below based on the TMS you are using to grant access to the new fleetops user to your tables, or use this template to select specific tables:

GRANT SELECT ON .

TO fleetops;

Grant Access to tables for McLeod

USE ;

GRANT SELECT ON dbo.driver TO fleetops;
GRANT SELECT ON dbo.tractor TO fleetops;
GRANT SELECT ON dbo.trailer TO fleetops;
GRANT SELECT ON dbo.payee TO fleetops;
GRANT SELECT ON dbo.users TO fleetops;
GRANT SELECT ON dbo.fleet TO fleetops;
GRANT SELECT ON dbo.equipment_item TO fleetops;
GRANT SELECT ON dbo.equipment_group TO fleetops;
GRANT SELECT ON dbo.customer TO fleetops;
GRANT SELECT ON dbo.location TO fleetops;
GRANT SELECT ON dbo.zones TO fleetops;
GRANT SELECT ON dbo.zone_area TO fleetops;
GRANT SELECT ON dbo.zone_region TO fleetops;
GRANT SELECT ON dbo.zipzone TO fleetops;
GRANT SELECT ON dbo.orders TO fleetops;
GRANT SELECT ON dbo.stop TO fleetops;
GRANT SELECT ON dbo.servicefail TO fleetops;
GRANT SELECT ON dbo.movement TO fleetops;
GRANT SELECT ON dbo.movement_order TO fleetops;
GRANT SELECT ON dbo.prorated_moveorder TO fleetops;
GRANT SELECT ON dbo.journal_cash TO fleetops;
GRANT SELECT ON dbo.open_item TO fleetops;
GRANT SELECT ON dbo.edi_standard_code TO fleetops;
GRANT SELECT ON dbo.seg_alloc_code TO fleetops;
GRANT SELECT ON dbo.billing_history TO fleetops;
GRANT SELECT ON dbo.misc_bill_hist TO fleetops;
GRANT SELECT ON dbo.mb_detail_hist TO fleetops;
GRANT SELECT ON dbo.other_charge TO fleetops;
GRANT SELECT ON dbo.driver_hours TO fleetops;
GRANT SELECT ON dbo.driver_extra_pay TO fleetops;
GRANT SELECT ON dbo.drs_deduct_hist TO fleetops;
GRANT SELECT ON dbo.drs_payroll_hist TO fleetops;
GRANT SELECT ON dbo.auto_home_history TO fleetops;
GRANT SELECT ON dbo.equipstatus TO fleetops;
GRANT SELECT ON dbo.equipstatuscode TO fleetops;
GRANT SELECT ON dbo.inspection TO fleetops;
GRANT SELECT ON dbo.inspect_violation TO fleetops;
GRANT SELECT ON dbo.motoraccident TO fleetops;
GRANT SELECT ON dbo.accident_cost TO fleetops;
GRANT SELECT ON dbo.fuel_ticket_hist TO fleetops;
GRANT SELECT ON dbo.gl_account TO fleetops;
GRANT SELECT ON dbo.gl_ledger TO fleetops;
GRANT SELECT ON dbo.gl_ledger_hist TO fleetops;
GRANT SELECT ON dbo.fiscal_year_dtl TO fleetops;

Grant Access to tables for TMW Suite

USE ;

GRANT SELECT ON dbo.ACCIDENT TO fleetops;
GRANT SELECT ON dbo.carrier TO fleetops;
GRANT SELECT ON dbo.chargetype TO fleetops;
GRANT SELECT ON dbo.city TO fleetops;
GRANT SELECT ON dbo.commodity TO fleetops;
GRANT SELECT ON dbo.company TO fleetops;
GRANT SELECT ON dbo.edi_orderstate TO fleetops;
GRANT SELECT ON dbo.expiration TO fleetops;
GRANT SELECT ON dbo.fuelpurchased TO fleetops;
GRANT SELECT ON dbo.invoicedetail TO fleetops;
GRANT SELECT ON dbo.invoiceheader TO fleetops;
GRANT SELECT ON dbo.labelfile TO fleetops;
GRANT SELECT ON dbo.legheader TO fleetops;
GRANT SELECT ON dbo.log_driverlogs TO fleetops;
GRANT SELECT ON dbo.manpowerprofile TO fleetops;
GRANT SELECT ON dbo.orderheader TO fleetops;
GRANT SELECT ON dbo.paydetail TO fleetops;
GRANT SELECT ON dbo.payheader TO fleetops;
GRANT SELECT ON dbo.SAFETYREPORT TO fleetops;
GRANT SELECT ON dbo.stops TO fleetops;
GRANT SELECT ON dbo.tariffheader TO fleetops;
GRANT SELECT ON dbo.tariffrate TO fleetops;
GRANT SELECT ON dbo.tractorprofile TO fleetops;
GRANT SELECT ON dbo.trailerprofile TO fleetops;

2.4 Set up SSL Cert for secure connections

Fivetran requires your SQL Server to use an encrypted connection with TLS 1.1 - 1.3

ℹ️ If you already have an SSL certificate configured for SQL Server you can skip these steps.

For these steps you will need access to the SQL Server Configuration Manager, Microsoft Management Console and PowerShell (if you need to generate a self-signed certificate).

You can read Microsoft’s Offical Guide or follow the steps here:

Run PowerShell as administrator and paste in the following command to generate a self signed SSL certificate:

ℹ️ You must use the fully qualified hostname of your server when generating the certificate for it to work with SQL Server

New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=$env:COMPUTERNAME" `
-DnsName ("{0}" -f [System.Net.Dns]::GetHostByName($env:computerName).HostName),'localhost' `
-KeyAlgorithm "RSA" -KeyLength 2048 -HashAlgorithm "SHA256" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" `
-NotAfter (Get-Date).AddMonths(36) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider" `
-CertStoreLocation "cert:\LocalMachine\My"

Now open the SQL Server Configuration Manager. Expand the SQL Server Network Configuration. Right Click on the Protocols for MSSQLSERVER option and select properties.

Select the Certificate tab. In the dropdown you should now see the certificate you created. Select it then click OK.

Before you can restart the SQL Server to start using the new certificate, you have to ensure the service account running SQL Server is allowed to read the certificate.

In SQL Configuration Manager, select the “SQL Server Services” option on the left. On the right, find the line with name “SQL Server” and take note of the “Log On As” column’s value. It should be NT Service\MSSQLSERVER.

Next open the Windows run dialog from the start menu

Type in mmc to open the Microsoft Management Console

If it’s empty, click File, then “Add/Remove Snap-in…”.

Select Certificates and click “Add >”.

Change the selection to “Computer account”, click Next and then Finish.

Now you should see the Certificates entry under the “Console Root” on the left.

Expand Certificates > Personal > Certificates

In the center panel you should see your new certificate. Right click on it, select “All Tasks”, then click on “Manage Private Keys…”

In the popup, click “Add…” under the list of user names.

Now enter the name of the service account which runs your SQL Server and click on “Check Names”

Select the matching name from the next popup and click OK. Now you can click OK in the previous two popups until back on the “Security” tab for the “Permissions” popup.

Change the permissions for the newly added user to only Read access. Click OK.

⚠️ This step will require you to restart the SQL Server Service. Only complete this step when downtime is acceptable.

Now you can return to SQL Server Configuration Manager, select the “SQL Server Services” option on the left, find the “SQL Server” line, right click and click “Restart”.

3. Proxy Setup

Next you will install the Fivetran Proxy Agent on a server on your local network which can reach your database. If you haven’t received an installer download link and config file, contact FleetOps to send it to you.

Below are the instructions for installing the Fivetran Proxy Agent on Windows Server. If you would prefer to use one of the other installers, please refer to Fivetran’s installation instructions.

Download and run the installer, you will see the following install wizard:

Click next.

Read and accept the license agreement.

(Optional) Change the config directories.

(Optional) Change the install location.

Select “Proxy Agent” from the list of modes.

Paste in the contents of the proxy_config.json file you were sent. Here is an example proxy_config.json file which you should have been sent along with these instructions and the Fivetran Proxy download links.

{"agent_id":"unstaffed_modestly","auth_token":"afc68608be374be97d07073c4a5b8,"proxy_server_uri":"wss://prod.gcp.us-east4.proxy.fivetran.com"}

Wait for the config settings to be validated. If this fails, check you copied all of the file, including the curly braces { } at the start and end, and that double quotation marks " are used rather than single.

Set a specific user to run the Fivetran Proxy Agent, or use the local system account.

(Optional) Add some environment variables, these shouldn’t be needed unless we need to troubleshoot your agent.

Wait for the Fivetran Proxy Agent to finish installing. Now the Fivetran Proxy Agent is installed and configured.

The agent will start connecting to Fivetran's services, and via the config file you supplied during installation we will use it to access your SQL Server in the next steps.

4. Configuration

Now you can open the configuration link you were sent via email. This will open a Fivetran Connector Card form where you can enter all of your connection details. You will need the following:

SQL server’s local IP address / domain name. Domain name is preferred
SQL server port, probably 1433
Newly created SQL Server user (should be named fleetops unless you chose a different name)
Password for the above user
Database name (If you have multiple, you will have to ask us for more links and fill out the form for each database.
Connection Method: Leave at Connect directly

⚠️ IMPORTANT!

The Fivetran Proxy Agent is a new feature and not yet supported on the Fivetran Connector Card form which we sent you. After filling out the form, the form will return an error as seen in the screenshot below. This is expected. After you fill out this form we will update your connector with the proxy we sent you, and finish testing the database connection.

We recommend you fill our this form while on a call with your FleetOps representative so they can finish the setup immediately.

Here is a screenshot of the Fivetran Connector Card form with the expected error message after filling out the form correctly:

Congratulations! You have completed setting up the new FleetOps data movement solution!

Next we will schedule the initial data replication with you so it will have minimal impact on your services which use the SQL Server.

Then we will be in contact once we are ready to share new analytics with you. In the meantime, your existing FleetOps account will continue to function as normal.

Knowledge Base