If you encounter any problems while following the Fivetran Upgrade Guide, search for a solution here or contact FleetOps support in case of an emergency.

Cannot Enable CDC

There are a few scenarios where CDC cannot be used, or where there are restrictions on it. Please review the official documentation on interoperability and known issues. These restrictions include

already having a user / schema called cdc
contained databases
clustered columnstore indexes
partition switching
data-tier Import/Export
Extract/Publish operations
ALTER COLUMN with VARCHAR or VARBINARY

If any of these restrictions prevent you from enabling CDC on your SQL Server database, please contact FleetOps so we can work on an alternative solution.

SQL Server Service not starting after adding SSL Cert

If your SQL Server Service isn't starting after adding the Self Signed SSL Cert as per the Setup Guide, then you should remove the certificate if you don't have time to investigate and fix the issue. We have steps for removing the SSL Cert in the Removal Guide.

If downtime is acceptable and you can investigate the issue, you should follow the Microsoft troubleshooting steps to check the error message or logs. Also refer to the list of possible startup errors.

For example, it is possible that the MSSQLSERVER service user doesn't have access to the certificate. In this case review the process outlined in the Setup Guide for granting access and check if the user your service uses differs from the default.

It could also fail to start due to an password policy expiring the service user's password in which case the password has to be updated and the service restarted again.

Disk Space Usage

⚠️ Please inform FleetOps of any changes you make to the CDC retention period.

Enabling CDC will consume extra disk space on your Windows Server. The space used will depend on how many tables you are adding to CDC, the size of each row and the frequency of inserts/updates. The most reliable way to estimate size consumed is to monitor the size of tables over time and then review the size of the CDC tables themselves after enabling it.

Here are some SQL scripts which will create a new Table to store historical data of table size so it can be compared over time and predict growth:

Replace in the scripts below with the name of the database you want to analyze.

Create a temp table to hold metrics

use 
CREATE TABLE TEMP_TABLE_METRICS
(
	[schema]      VARCHAR(100),
	[table]       VARCHAR(300),
	[object_id]   VARCHAR(300),
	[rows]        INT,
	[reservedKB]  INT,
	[dataKB]      INT,
	[indexKB]     INT,
	[unusedKB]    INT,
	[seeks]       INT,
	[scans]       INT,
	[updates]     INT,
	[lookups]     INT,
        [timestamp]   DATETIME
)

Collect Metrics (run this on schedule or manually every hour / day)

This will fetch space used and index usage for each table in your database and save it with a timestamp into the table created above.

You can run this manually every few minutes / hours / days or schedule a job to run it automatically (don't forget to remove the schedule once you are finished with it)

use 

DECLARE @spaceused TABLE
(
    tableName VARCHAR(100),
    numberofRows VARCHAR(100),
    reservedSize VARCHAR(50),
    dataSize VARCHAR(50),
    indexSize VARCHAR(50),
    unusedSize VARCHAR(50)
)

DECLARE @YourCursor CURSOR, @schema VARCHAR(10), @table VARCHAR(100), @command VARCHAR(200)

SET @YourCursor = CURSOR FOR
  SELECT [schema] = s.name, [table] = t.name
  FROM sys.tables t
  INNER JOIN sys.schemas as s on s.schema_id = t.schema_id
OPEN @YourCursor;

FETCH NEXT FROM @YourCursor
  INTO @schema, @table

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @command = FORMATMESSAGE('%s.%s', @schema, @table)
  INSERT INTO @spaceused
    EXEC sp_spaceused @command
  INSERT INTO TEMP_TABLE_METRICS
    SELECT
      [schema] = @schema,
      [table] = @table,
      [object_id] = t.object_id,
      [rows] = CAST(x.numberofRows AS INT),
      [reservedKB] = CAST(LEFT(x.reservedSize, LEN(x.reservedSize) - 3) AS INT),
      [dataKB] = CAST(LEFT(x.dataSize, LEN(x.dataSize) - 3) AS INT),
      [indexKB] = CAST(LEFT(x.indexSize, LEN(x.indexSize) - 3) AS INT),
      [unusedKB] = CAST(LEFT(x.unusedSize, LEN(x.unusedSize) - 3) AS INT),
      [seeks] = u.user_seeks,
      [scans] = u.user_scans,
      [updates] = u.user_updates,
      [lookups] = u.user_lookups,
      [datetime] = GETDATE()
    FROM @spaceused x
    INNER JOIN sys.schemas as s on @schema = s.name
    INNER JOIN sys.tables t on @table = t.name and s.schema_id = t.schema_id
    LEFT OUTER JOIN (
      SELECT
       object_id,
       [user_seeks] = SUM(user_seeks),
       [user_scans] = SUM(user_scans),
       [user_updates] = SUM(user_updates),
       [user_lookups] = SUM(user_lookups)
     FROM sys.dm_db_index_usage_stats
     GROUP BY object_id
   ) as u on u.object_id = t.object_id
  DELETE FROM @spaceused
  FETCH NEXT FROM @YourCursor
  INTO @schema, @table
END;
CLOSE @YourCursor;
DEALLOCATE @YourCursor;

Analyze trend of collected Metrics

Once you have run the script above at least twice, you can look at the growth in both your data tables and CDC tables using this script:



use 
WITH first_last AS (
  SELECT
    [schema],
    [table],
    [timestamp],
    [firstData] = CASE timestamp WHEN MIN(timestamp) OVER (PARTITION BY [schema], [table]) THEN [datakb] END,
    [lastData] = CASE timestamp WHEN MAX(timestamp) OVER (PARTITION BY [schema], [table]) THEN [datakb] END,
    [firstRows] = CASE timestamp WHEN MIN(timestamp) OVER (PARTITION BY [schema], [table]) THEN [rows] END,
    [lastRows] = CASE timestamp WHEN MAX(timestamp) OVER (PARTITION BY [schema], [table]) THEN [rows] END
  FROM TEMP_TABLE_METRICS
), differences AS (
    SELECT
        [schema],
        [table],
        [firstTimestamp] = MIN(timestamp),
        [lastTimestamp] = MAX(timestamp),
        [firstDataSize] = MIN(firstData),
        [lastDataSize] = MAX(lastData),
        [firstRows] = MIN(firstRows),
        [lastRows] = MAX(lastRows),
        [diffData] = MAX(lastData) - MIN(firstData),
        [diffRows] = MAX(lastRows) - MIN(firstRows),
        [diffHours] = DATEDIFF(hour, MIN(timestamp), MAX(timestamp))
    FROM first_last
    GROUP BY [schema], [table]
)
SELECT
    [schema],
    [table],
    [firstTimestamp],
    [firstDataSize KB] = [firstDataSize],
    [firstRows],
    [lastTimestamp],
    [lastDataSize KB] = [lastDataSize] ,
    [lastRows],
    [diffHours],
    [diffData KB] = [diffData],
    [diffRows],
    [new MB per day] = CAST( ([diffData] / 1024.0) * (24.0 / [diffHours]) AS NUMERIC(10, 2)),
    [new rows per day] = CAST([diffRows] * 24.0 / [diffHours] AS NUMERIC(10, 2))
FROM differences
ORDER BY [new MB per day] DESC

If you prefer not creating tables and scheduling / running scripts, you can use the built in Disk Usage report and save an hourly / daily copy.

To run this report, right click on your database, select Reports > Standard Reports > Disk Usage by Table. You can save the report via the

If you are running very low on free space, you can either provision more space or reduce the retention period for the CDC job. To do this run the script below, replacing with the minutes of CDC data you want to retain. Do not go below one day (1440 minutes) as this would raise the chance of any sync error resulting in a full database re-sync, which will consume a lot of CPU & Memory.

EXEC sys.sp_cdc_change_job 
    @job_type=N'Cleanup',
    @retention=
GO

Server Performance

Enabling CDC and running full historical data sync on your SQL Server can have a significant impact on your Windows Server resources.

If you have any concerns setting up CDC and the Fivetran Proxy, please reach out to the FleetOps Backend Engineering team via FleetOps Support to discuss this.

We recommend using an enterprise third party tool to measure metrics of your Windows Servers (Nagios, Grafana, Splunk etc.). But you can also use the built in Windows Performance Monitor to collect metrics before going through the setup guide to quantify the impact on your Windows Server.

Run perfmon via the windows start menu or run dialog. On the left hand side panel, expand "Data Collector Sets". Right click on "User Defined" and select "New", then click "Data Collector Set".

Enter a name for your Data Collector Set (eg. CDC Monitoring).

Select "Create Manually" and click next.

Select "Create Data Logs" and check the "Performance Counter" option

In the next window, click the "Add" button to open the next dialog.

Here you can select the metrics you want to collect.

We recommend using:

Logical Disk > Free Megabytes
Logical Disk > Disk Write Bytes/sec
Memory > Available MBytes
Network Interface > Bytes Sent/sec
Processor > % Processor Time

For each of these options select total for the "Instances of Selected Object" before clicking "Add >>"

Leave the sample interval at the default 15 seconds and click Next.

Select the "Save and close" option before finishing.

Now select your newly created Data Collector Set in the left hand panel, right click on the "DataCollector01" and select Properties

Here I recommend changing the "Log format" to "Comma Separated", as it's easier to review in Excel than the Performance Monitors graphs.

So it automatically stops after 1 week, check the "Maximum Samples " box and enter 40320 (4 times a second for 1 week).

Now you can confirm the dialog, then right click your Data Collector Set in the left hand panel and click start.

Afterwards wait a minute and open the folder where the log is stored (C:\PerfLogs\Admin) and check the correct data is being logged.

SQL Server Agent Error

CDC works by running scheduled jobs via the Server Agent. If the Server Agent stops, CDC will stop working.

If it stops, you can start it again via the SQL Server Management Studio. Expand your server name in the left hand side panel, and right click on the “SQL Server Agent” at the end of the list. Select “Start” and click OK in the confirmation dialog.

If it fails to start, you should check the error log. Right click on the “SQL Server Agent” again, this time select “Properties” to see where the error log is saved. Open it and check for the most recent error messages.

Proxy Not Running

It is difficult to tell if the proxy is the reason why FleetOps cannot connect to your SQL Server, but it is easy to check if the Proxy is running and to restart it if it isn't.

On the Windows Server where your Fivetran Proxy Agent is installed, open the Task Manager.

Navigate to the services tab, and search for a service named "FivetranProxyPrunsrv_...".

Check the Status column to see if it is currently running. If not, you can right click the row and select the "Start" option.

If you are still concerned that the Fivetran Proxy Agent is not connecting to your database and the Fivetran Cloud, you can check the proxy logs.

You can find them in the C:\Fivetran\hvr_config\proxy\logs folder unless you changed the default installation locations during the installation process.

If you need help interpreting the logs, you can forward them to fleetops support. Please include the whole folder, which should contain multiple log files named after the dates they were collected.

Knowledge Base

Fivetran Troubleshooting Guide: SQL Server