Skip to main content

Capabilities for SQL Server

Justin Stevenson
Updated

Capabilities for SQL Server

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

Capture

Local Data Processing supports the following capabilities on SQL Server:

  • changes from location (SQL Server from 2005 to 2019).
  • Log-based capture (capture from DBMS logging system) (SQL Server from 2005 to 2019).
  • Capture from Archive log files only (SQL Server from 2005 to 2019, 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 2008 to 2019).
  • Direct access to logs on a file system (SQL Server from 2005 to 2019, 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 2005 to 2019).
  • Log-based capture of tables with LOB column (SQL Server from 2005 to 2019).
  • Rewind log-based capture to specific time ( option -i) (SQL Server from 2005 to 2019).
  • Rewind log-based capture to the beginning of currently active oldest transaction (SQL Server from 2005 to 2019).
  • Rewind log-based capture to the beginning of currently active oldest transaction for a specific list of tables (SQL Server from 2005 to 2019).
  • Online refresh using accurate LSN/SCN (SQL Server from 2005 to 2019).
  • Online refresh skips capture log records early in scanner (SQL Server from 2005 to 2019).
  • Populates column hvr_cap_user for use in {hvr_cap_user} substitutions (SQL Server from 2005 to 2019).
  • Multiple log-based capture jobs can capture from same database (SQL Server from 2005 to 2019, only FN mechanism, not SP).
  • Capture from tables with (data_compression=row) (SQL Server from 2008 to 2019).
  • Capture from tables with (data_compression=page) (SQL Server from 2008 to 2019).
  • Read archives from an alternative directory (location property Archive_Log_Path on the capture location) (SQL Server from 2005 to 2019).
  • Trigger-based capture (location property Capture_Method=DB_TRIGGER on the capture location) (SQL Server from 2005 to 2019).
  • Boundaries between transactions preserved by trigger-based capture (SQL Server from 2005 to 2019).
  • Trigger-based capture jobs can be woken by db-event when change happens (SQL Server from 2008 to 2019).

Local Data Processing does not support the following capabilities on SQL Server:

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

Repository Database

Local Data Processing supports Repository database on SQL Server.

Integrate

Local Data Processing supports the following capabilities on SQL Server:

  • changes into location (SQL Server from 2005 to 2019).
  • Burst integration ( with parameter Method=BURST) (SQL Server from 2005 to 2019).
  • with parameter BurstCommitFrequency (SQL Server from 2005 to 2019).
  • Continuous integration ( with parameter Method=CONTINUOUS) (SQL Server from 2005 to 2019).
  • Tables without a key and without with NoDuplicateRows for continuous integration (SQL Server from 2005 to 2019).
  • Continuous with parameter OnErrorSaveFailed (SQL Server from 2005 to 2019).
  • with parameter SoftDelete (SQL Server from 2005 to 2019).
  • Creation and update of Local Data Processing state tables (SQL Server from 1998 to 2019).
  • with parameter DbProc (SQL Server from 2005 to 2019).

Local Data Processing does not support the following capabilities on SQL Server:

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

Bi-directional Replication

Local Data Processing supports the following capabilities on SQL Server:

  • Detection of changes made by Local Data Processing in a bidirectional channel to prevent loop-back (SQL Server from 2005 to 2019).
  • with Trigger-based Capture (with location property Capture_Method=DB_TRIGGER on the capture location and without parameter TimestampColumn) (SQL Server from 2005 to 2019).
  • with parameter TimestampColumn (SQL Server from 2005 to 2019).

Local Data Processing does not support the following capabilities on SQL Server:

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

Refresh and Compare

Local Data Processing supports the following capabilities on SQL Server:

  • or from source location (SQL Server from 1998 to 2019).
  • into target location (SQL Server from 1998 to 2019).
  • Row-wise into target location (option -g) (SQL Server from 1998 to 2019).
  • Disable/enable foreign keys from table to others during (option -F) (SQL Server from 1998 to 2019).
  • Disable/enable triggers during (option -f) (SQL Server from 1998 to 2019).
  • Identity columns (SQL Server from 2005 to 2019).
  • Select data with 'snapshot' SQL isolation level ( option -Msnapshot) (SQL Server from 1998 to 2019).

Local Data Processing does not support the following capabilities on SQL Server:

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

Other Capabilities

Local Data Processing supports the following capabilities on SQL Server:

  • Use distribution key for parallelizing changes within a table ( with parameter DistributionKey) (SQL Server from 1998 to 2019).
  • Call database procedure dbproc during replication jobs ( with parameter DbProc) (SQL Server from 2005 to 2019).
  • with parameter IncludeSQLFile (SQL Server from 2005 to 2019).
  • International table and column names where DBMS is not configured with UTF-8 encoding (SQL Server from 1998 to 2019).
  • 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 1998 to 2019).
  • Always treat DBMS schema names case sensitive (SQL Server from 1998 to 2019).

Local Data Processing does not support the following capabilities on SQL Server:

  • Distinguish and support capture from 'materialized views'.

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk