Capabilities for SQL Server

This section lists the Capabilities of Fivetran HVR when using 'SQL Server'. For more information about the pre-requisites, access privileges, and other configuration requirements, see SQL Server Requirements.

Capture

HVR supports the following capabilities on SQL Server:

Capture changes from location (SQL Server from 2012 to 2022).
Log-based capture (capture from DBMS logging system) (SQL Server from 2012 to 2022).
Capture from Archive log files only (SQL Server from 2012 to 2022, except on Amazon RDS for SQL Server, Google Cloud SQL for SQL Server, and Microsoft Azure SQL Database).
Log-based capture of tables without a primary key (SQL Server from 2012 to 2022).
Direct access to logs on a file system (SQL Server from 2012 to 2022, except on Amazon RDS for SQL Server, Google Cloud SQL for SQL Server, and Microsoft Azure SQL Database).
Access to logs using SQL interface (SQL Server from 2012 to 2022).
Log-based capture of tables with LOB column (SQL Server from 2012 to 2022).
Rewind log-based capture to specific time (hvractivate option -i) (SQL Server from 2012 to 2022).
Rewind log-based capture to the beginning of currently active oldest transaction (SQL Server from 2012 to 2022).
Online refresh using accurate LSN/SCN (SQL Server from 2012 to 2022).
Online refresh skips capture log records early in scanner (SQL Server from 2012 to 2022).
Populates column hvr_cap_user for use in ColumnProperties {hvr_cap_user} substitutions (SQL Server from 2012 to 2022).
Multiple log-based capture jobs can capture from same database (SQL Server from 2012 to 2022, only FN mechanism, not SP).
Capture from tables with with (data_compression=row) (SQL Server from 2012 to 2022).
Capture from tables with with (data_compression=page) (SQL Server from 2012 to 2022).
Read archives from an alternative directory (location property Archive_Log_Path on the capture location) (SQL Server from 2012 to 2022).
Trigger-based capture (location property Capture_Method=DB_TRIGGER on the capture location) (SQL Server from 2012 to 2022). This capture method has been deprecated since 6.2.0/0. (SQL Server from 2012 to 2022).
Boundaries between transactions preserved by trigger-based capture (SQL Server from 2012 to 2022).
Trigger-based capture jobs can be woken by db-event when change happens (SQL Server from 2012 to 2022).

HVR does not support the following capabilities on SQL Server:

Log-based capture from hidden rowid/RRN column (ColumnProperties with parameter CaptureFromRowId).
Log-based capture of truncate table statements.
Log-based capture checkpointing using location property Capture_Checkpoint_Frequency.

Repository Database

HVR supports Repository database on SQL Server.

Integrate

HVR supports the following capabilities on SQL Server:

Integrate changes into location (SQL Server from 2012 to 2022).
Burst integration (Integrate with parameter Method=BURST) (SQL Server from 2012 to 2022).
Integrate with parameter BurstCommitFrequency (SQL Server from 2012 to 2022).
Continuous integration (Integrate with parameter Method=CONTINUOUS) (SQL Server from 2012 to 2022).
Tables without a key and without TableProperties with NoDuplicateRows for continuous integration (SQL Server from 2012 to 2022).
Continuous integrate with parameter OnErrorSaveFailed (SQL Server from 2012 to 2022).
ColumnProperties with parameter SoftDelete (SQL Server from 2012 to 2022).
Creation and update of HVR state tables (SQL Server from 2012 to 2022).
Integrate with parameter DbProc (SQL Server from 2012 to 2022).

HVR does not support the following capabilities on SQL Server:

Disable/enable database triggers during integrate (with parameter NoTriggerFiring).

Bi-directional Replication

HVR supports the following capabilities on SQL Server:

Detection of changes made by HVR in a bidirectional channel to prevent loop-back (SQL Server from 2012 to 2022).
CollisionDetect with Trigger-based Capture (with location property Capture_Method=DB_TRIGGER on the capture location and CollisionDetect without parameter TimestampColumn) (SQL Server from 2012 to 2022).
CollisionDetect with parameter TimestampColumn (SQL Server from 2012 to 2022).

HVR does not support the following capabilities on SQL Server:

CollisionDetect with Log-based Capture (without parameter TimestampColumn).

Refresh and Compare

HVR supports the following capabilities on SQL Server:

hvrrefresh or hvrcompare from source location (SQL Server from 2012 to 2022).
hvrrefresh into target location (SQL Server from 2012 to 2022).
Isolated table hvrrefresh (option -i) into target location (SQL Server from 2012 to 2022).
Row-wise hvrrefresh into target location (option -g) (SQL Server from 2012 to 2022).
Disable/enable foreign keys from table to others during hvrrefresh (option -F) (SQL Server from 2012 to 2022).
Disable/enable triggers during hvrrefresh (option -f) (SQL Server from 2012 to 2022).
Identity columns (SQL Server from 2012 to 2022).
Select data with 'snapshot' SQL isolation level (hvrrefresh option -Msnapshot) (SQL Server from 2012 to 2022).

HVR does not support the following capabilities on SQL Server:

Bulk load of multiple slices of same table can be done in parallel.

Other Capabilities

HVR supports the following capabilities on SQL Server:

Call database procedure dbproc during replication jobs (AgentPlugin with parameter DbProc) (SQL Server from 2012 to 2022).
DbObjectGeneration with parameter IncludeSQLFile (SQL Server from 2012 to 2022).
International table and column names where DBMS is not configured with UTF-8 encoding (SQL Server from 2012 to 2022).
Always treat DBMS table names and column names as case sensitive, even if not configured (equivalent to location property Case_Sensitive_Names always set to TRUE) (SQL Server from 2012 to 2022).
Always treat DBMS schema names case sensitive (SQL Server from 2012 to 2022).

HVR does not support the following capabilities on SQL Server:

Use distribution key for parallelizing changes within a table (ColumnProperties with parameter DistributionKey).
Distinguish and support capture from 'materialized views'.