You want to understand how the functioning of SQL Server database connectors affects Monthly Active Rows (MAR).
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 update mechanism 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 incurs greater MAR than CT.
- 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.
There are five 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
- A manual or automatic re-sync is triggered
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.
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
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 update mechanism is, we do the following:
If your incremental update mechanism is less than twenty-four hours, we perform a historical re-sync, which incurs MAR.
If the incremental update mechanism 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. 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.
Take the change log retention period into account before pausing a connector. Do not pause the connector for longer than those logs are retained.
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.