Capture from SQL Server using SQL Access - DSS 6 | Data Source Solutions Documentation
Documentation: Capture from SQL Server using SQL Access - DSS 6 | Data Source Solutions Documentation
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, Data Source Solutions DSS 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 DSS Agent.
Remote capture is less efficient than capture using an DSS 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, DSS may receive partial/incomplete values and may require DSS 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 DSS database User must be granted the privileges mentioned in section Grants for Log-Based Capture capturing changes from SQL Server database using SQL Access.