SQL Server MAR Management

Use Case

You want to understand how the functioning of SQL Server database connectors affects Monthly Active Rows (MAR).

Environment

SQL Server database connectors

Connector Overview

The initial sync (first-ever historical sync) replicates the entire SQL Server data into your destination for all tables the Fivetran user was given SELECT permissions. The sync doesn't include the tables that you exclude in the Schema tab of your connector dashboard.

You can configure your connector(s) in multiple ways. Creating one single connector is easier to manage, whereas creating separate connectors lets you track and isolate changes coming to the database and keep them independent of each other:

• You can set up one connector for static tables and another connector for dynamic tables to track MAR.
• You can group your connectors by any arbitrary group. For example, business unit, function, or relationship.
• You can include small and large tables in a connector.

An important configuration option to consider is the incremental sync method you choose to proceed with:

• Change Tracking (CT) and Change Data Capture (CDC) are available on all platforms where your database is hosted.
• There are pros and cons to the use of CT versus CDC. However, CDC may incur greater MAR than CT for tables that do not have primary keys.
• You can specify either CDC or CT at a table level and leverage both options within the same database. If both CDC and CT are enabled for a table, Fivetran defaults to CT for that table.

Contributing Factors

There are four primary actions that contribute to MAR:

• A new row is added to an existing table
• A value is updated for an existing row in a table
• A value is deleted within a table
• A new column (with values) is added to a table

Managing MAR

• For accounts on the contracted spend model, re-syncs that you trigger count towards free MAR. You have an unlimited number of free customer-triggered re-syncs per connector for each month. This includes table- and connector- level re-syncs and historical re-syncs initiated from the Fivetran dashboard or using our REST API.

• Re-syncs that Fivetran triggers in your dashboard or while fixing incidents count towards free MAR. We only perform a re-sync when it's absolutely necessary.

• Two exceptions to this free re-sync plan:

  • Schema changes in non-database connectors that trigger automatic table re-syncs count towards paid MAR. Schema changes in database connectors that trigger automatic table re-syncs count towards free MAR.

  • Automatic re-syncs of a database table you had previously excluded from your syncs count towards paid MAR.

• If separate connectors sync the same data from the same source (with the same primary keys), they contribute separately towards your MAR. This means if you have two or more of the same connector type that sync from one source to multiple destinations, we count each connector’s MAR separately.

• If you add an empty column, it does not contribute to MAR.

• Tables without a primary key can only be synced if CDC is enabled:

  • For these tables, Fivetran creates a synthetic primary key (with the column _fivetran_id) that is a composite hash of all non-Fivetran column values within the row. You incur MAR for the updates based on the _fivetran_id.

  • An update in the source table without the primary key is treated as both a DELETE and an INSERT. You incur MAR for each action (as two rows are created in the destination).

• If you delete a row that has a primary key, and then, after a while, create another row with the same primary key, this counts toward only one MAR.

• In a table without a primary key, if you remove or add columns from which we generate the synthetic primary key, you incur MAR for every change that you made.

• If you want to migrate tables (from your current setup to a differing setup), this may count toward MAR. For example, if you migrate from one large table to three smaller tables, you incur MAR only if you modify the existing incumbent table.

• Make sure the destination is always connected. If Fivetran can't write data to your destination, we cache this data for up to twenty-four hours before discarding it.

• If the destination is disconnected for more than twenty-four hours. Depending on how long your incremental sync method is, we do the following:

  • If the log retention period is less than twenty-four hours, we perform a historical re-sync, which does not incur MAR.

  • If the log retention period is long enough, we may not need to perform a re-sync because we can read through the change logs.

• The configuration of the CDC or CT log retention period is important. Ensure that the retention period is more than the sync frequency of your connector. Take the change log retention period into account before pausing a connector. If you pause the connector for longer than those logs are retained, then a historical re-sync is needed which will be free but could lead to additional delays syncing the data.

• CDC and the associated stored procedures incur more storage and compute and may require a more frequent purge of the change tables. If you choose CDC, make sure that you are mindful of the sync frequency.

• Modify only new records. Modifying very old records during every sync causes a high percentage of the tables to incur MAR, while the updated records may not possess any significant business value.