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 =SQL. In this capture method, Fivetran Local Data Processing 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 a High-Volume Agent.

Remote capture is less efficient than capture using a High-Volume 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, Local Data Processing may receive partial/incomplete values and may require Local Data Processing to perform additional steps to retrieve the full value from the source database, this is called augmenting ( parameter ).

Grants for SQL Access

The Local Data Processing database User must be granted the privileges mentioned in section capturing changes from SQL Server database using SQL Access.