Capture From SQL Server Using SQL Access

This section describes the configuration requirements for capturing changes from SQL Server database using SQL Access capture method or Capture_Method=SQL. In this capture method, Fivetran HVR captures changes over an SQL connection. This method uses stored database function calls to retrieve incremental log fragments.

The benefits of the SQL Access capture method are:

• the ability to run with minimal OS and database privileges
• the ability to capture from a local/remote database without using an HVR Agent.

Remote capture is less efficient than capture using an HVR Agent on the database server. The SQL Access capture method will impose more overhead on the transactional database than the DIRECT capture method.

The SQL capture method is slower than the DIRECT method. It exposes additional load on a source database and, for certain column types, HVR may receive partial/incomplete values and may require HVR to perform additional steps to retrieve the full value from the source database, this is called augmenting (Capture parameter AugmentIncomplete).

Grants for SQL Access

The HVR database User must be granted the privileges mentioned in section Grants for Log-Based Capture capturing changes from SQL Server database using SQL Access.