SQL Server as Source

Capture

Fivetran HVR allows you to Capture changes from SQL Server database. This section describes the configuration requirements for capturing changes from SQL Server location. For the list of supported SQL Server versions from which HVR can capture changes, see Capture changes from location in Capabilities.

HVR uses the Microsoft ODBC Driver for SQL Server to capture changes from an SQL Server location.

Table Types

HVR supports capture from the following table types in SQL Server:

clustered (row/page compressed and uncompressed)
heap (row/page compressed and uncompressed)

HVR does not support capture from memory optimized tables and trigger-based capture from temporal tables.

Capture Methods

HVR allows the following methods for capturing (Capture) changes from SQL Server:

Direct Transaction Log Access
Capture changes directly from SQL Server's logging files. This capture method is supported on:
- The primary database server with a connection to the active database.
- A secondary node of an AlwaysOn cluster connected to the local read-only database.
Archive Only
Capture changes directly from SQL Server's transaction log backup files (similar to the Direct Transaction Log Access method). This capture method is supported on any server with access to the log backups.
SQL Access
Capture changes over an SQL connection. This capture method is supported on the primary active database or on a read-only secondary database of an AlwaysOn cluster.
Database Triggers Since v6.1.0/3
Capture changes from tables using database triggers.
The trigger-based capture method (Capture_Method=DB_TRIGGER) has been deprecated since 6.2.0/0.

Grants for Log-Based Capture

This section lists the grants/permissions required for capturing changes from SQL Server database. Based on your requirement, either of the permissions listed in this section can be used.

For simplicity, we have categorized the required grants into the following three permission models:

SysAdmin: The easiest to configure and offers broad permissions and authority over the entire SQL Server instance. While easy to set up, it carries potential security risks.
DbOwner: Provides extensive permissions but is restricted to a specific database. It requires more setup effort than the SysAdmin model.
Minimal: Requires the least permissions and does not need sysadmin or db_owner rights. However, it is the most complex to configure.

Until version 6.1.0/12, only the SysAdmin model is supported for the Direct Transaction Log Access capture method. Since version 6.1.0/13, all three permission models are supported for the Direct Transaction Log Access capture method.
All three permission models are supported for the Archive Only and SQL Access capture methods.
The DbOwner and Minimal models are only available for SQL Server 2012 and above. For the older versions of SQL Server, the SysAdmin model must be used.

SysAdmin

In this permission model, the HVR database user must be granted sysadmin role.

DbOwner

In this permission model, the HVR database user must be granted the db_owner role. However, a user with sysadmin privileges is required for certain configurations detailed in this section, such as executing specific SQL statements, creating procedures, or granting special permissions to the HVR database user.

Configure the DbOwner permission model

HVR must already be installed before performing the following steps.

Create a special 'wrapper' SQL procedure:
This step is needed once when HVR is installed for an SQL Server instance. But if Always On AG is installed, then this step is needed on each Always On AG node.
A user with sysadmin privilege must create special 'wrapper' SQL procedures called sp_hvr_dblog (replaced with sp_hvr_dblog2 in v6.1.5/5 and later), sp_hvr_dbtable, and sp_hvr_loginfo, to enable HVR to call the SQL Server's read-only function fn_dump_dblog. This must be done inside the SQL Server database's special database msdb, not the actual capture database. The SQL query to create these procedures is available in the file hvrcapsysadmin.sql in the HVR_HOME\dbms\sqlserver directory. The HVR database user must then be allowed to execute this procedure. For this, the HVR database user must be added to the special msdb database and the following grants must be provided:
Replace sp_hvr_dblog with sp_hvr_dblog2 for v6.1.5/5 and later.
```
USE msdb;
CREATE USER username FOR LOGIN username;
GRANT EXECUTE ON sp_hvr_dblog TO username;
GRANT EXECUTE ON sp_hvr_dbtable TO username;
GRANT EXECUTE ON sp_hvr_loginfo TO username;
```
Grant special read-only privilege:
This step is needed once when HVR is installed for an SQL Server instance. But if Always On AG is installed, then this step is needed on each Always On AG node.
A sysadmin user must grant the HVR user login a special read-only privilege in the master database.
```
USE master;
GRANT VIEW SERVER STATE TO username;
```
In SQL Server 2022, the VIEW SERVER STATE permission was split into two permissions: VIEW SERVER PERFORMANCE STATE and VIEW SERVER SECURITY STATE. When capturing from SQL Server 2022, the user only needs VIEW SERVER PERFORMANCE STATE.
Perform special SQL statements:
When Activate Replication is performed, it may need to perform special SQL statements that would require sysadmin or db_owner privilege. One example is that it may need to create an Article on a replicated table to track its changes. In that case, Activate Replication will write the necessary SQL statements into a script file in the HVR_CONFIG\hubs\hub_name\channels\channel_name\locs\location_name\initsql directory on the capture machine. An error message will be displayed, which will list the name of the script file and the necessary permission level. The first time when Activate Replication displays this message, a user with sysadmin privilege must perform these SQL statements on the capture database. Subsequently, these SQL statements can be performed by the HVR database User with db_owner privilege.

Minimal

In this permission model, the HVR database user is not granted, nor does it require, sysadmin or db_owner roles at runtime. However, a user with sysadmin or db_owner privileges is required for certain configurations detailed in this section, such as executing the required SQL statements, creating procedures, and granting the necessary permissions to the HVR database user.

Configure the Minimal permission model

HVR must already be installed before performing the following steps.

Create a special 'wrapper' SQL procedure:
This step is needed once when HVR is installed for an SQL Server instance. But if Always On AG is installed, then this step is needed on each Always On AG node.
A user with sysadmin privilege must create special 'wrapper' SQL procedures called sp_hvr_dblog (replaced with sp_hvr_dblog2 in v6.1.5/5 and later), sp_hvr_dbtable, and sp_hvr_loginfo to enable HVR to call the SQL Server's read-only function fn_dump_dblog. This must be done inside the SQL Server database's special database msdb, not the actual capture database. The SQL query to create these procedures is available in the file hvrcapsysadmin.sql in the HVR_HOME\dbms\sqlserver directory. The HVR database user must then be allowed to execute this procedure. For this, the HVR database user must be added to the special msdb database and the following grants must be provided:
Replace sp_hvr_dblog with sp_hvr_dblog2 for v6.1.5/5 and later.
```
USE msdb;
CREATE USER username FOR LOGIN username;
GRANT EXECUTE ON sp_hvr_dblog TO username;
GRANT EXECUTE ON sp_hvr_dbtable TO username;
GRANT EXECUTE ON sp_hvr_loginfo TO username;
```
Grant special read-only privilege:
This step is needed once when HVR is installed for an SQL Server instance. But if Always On AG is installed, then this step is needed on each Always On AG node.
A sysadmin user must grant the HVR user login a special read-only privilege in the master database.
```
USE master;
GRANT VIEW SERVER STATE TO username;
```
In SQL Server 2022, the VIEW SERVER STATE permission was split into two permissions: VIEW SERVER PERFORMANCE STATE and VIEW SERVER SECURITY STATE. When capturing from SQL Server 2022, the user only needs VIEW SERVER PERFORMANCE STATE.
Perform special SQL statements:
When Activate Replication is performed, it may need to perform special SQL statements that would require sysadmin or db_owner privilege. One example is that it may need to create an Article on a replicated table to track its changes. In that case, Activate Replication will write the necessary SQL statements into a script file in the HVR_CONFIG\hubs\hub_name\channels\channel_name\locs\location_name\initsql directory on the capture machine. An error message will be displayed, which will list the name of the script file and the necessary permission level. The first time when Activate Replication displays this message, a user with sysadmin privilege must perform these SQL statements on the capture database. Subsequently, these SQL statements can be performed by a user with db_owner privilege.
Create 'wrapper' SQL procedures in each capture database:
This step is needed once when each new source database is being set up.
A user with db_owner (or sysadmin) privilege must create 'wrapper' SQL procedures in each capture database so that HVR can call the SQL Server's read-only procedures sp_helppublication, sp_helparticle, and fn_dblog. The SQL query to create these five read-only procedures is available in the file hvrcapdbowner.sql in the HVR_HOME\dbms\sqlserver directory. The HVR database user must then be allowed to execute these procedures.
The following grants must be granted to the HVR database user inside each capture database:
Replace sp_hvr_dblog with sp_hvr_dblog2 for v6.1.5/5 and later.
```
USE source_database;
GRANT EXECUTE ON sp_hvr_check_publication TO username;
GRANT EXECUTE ON sp_hvr_check_article TO username;
GRANT EXECUTE ON sp_hvr_dblog TO username;
GRANT EXECUTE ON sp_hvr_repldone TO username;
GRANT EXECUTE ON sp_hvr_repltrans TO username;
```
Grant read-only privilege:
This step is needed once when each new source database is being set up.
A user with db_owner (or sysadmin) privilege must grant the HVR database user a read-only privilege:
```
USE source_database;
ALTER ROLE db_datareader ADD MEMBER username;
```

Supplemental Logging

Supplemental logging is required to ensure table updates can be replicated correctly using SQL statements on a target database.

Transaction log files are generally used for database recovery and normally SQL Server only logs data that is needed for recovery. For example, full row image (all columns) are usually not written into the transaction log but only the fragments that are actually changed are written. For HVR to perform log-based capture, SQL Server needs to write full row images as well as write some additional information into the transaction log, which is referred to as "Supplemental Logging" in HVR.

SQL Server does not allow enabling supplemental logging directly. However, you can enable it by using the native features of SQL Server such as 'CDC tables' or 'replication articles'. To do log-based capture, HVR requires either a CDC table or a replication article to be created for each source table.

When performing Activate Replication (hvractivate -ol), if option Supplemental Logging is selected/enabled, HVR will check whether supplemental logging is enabled in the database. If supplemental logging is not enabled in the database, then HVR will create either a CDC table or a replication article depending on the option selected/defined in the location property Supplemental_Logging.

Articles can only be created on tables with a primary key. Articles are always used for the SQL Server Standard edition (prior to SQL Server 2016 Service Pack 1) to implement supplemental logging.
Supplemental logging is not supported when capturing from SQL Server using database triggers (Capture_Method=DB_TRIGGER).

The location property Supplemental_Logging allows you to select what action should be performed to enable supplemental logging for tables.

When supplemental logging is enabled and if a user attempts DDL such as
truncate table, an error will be displayed.
drop table when a replication article exists for the table, an error will be displayed.

Available options for SUPPLEMENTAL LOGGING are:

Prefer CDCTABs (default): Enable supplemental logging of updates by creating a CDC table for the source table.
If it is not possible to create the CDC table, then HVR will create replication articles instead. And, if it is not possible to create the replication articles also, then an error will be displayed.
Prefer ARTICLEs: Enable supplemental logging of updates by creating SQL Server transactional replication articles if the source table has a primary key.
If it is not possible to create replication articles or if the source table does not have a primary key, then HVR will create CDC table instead. And, if it is not possible to create the CDC table also, then an error will be displayed.
Reuse any existing CDCTABs and ARTICLEs: Enable supplemental logging of updates by using the existing CDC table or replication article for the source table. If neither the CDC tables nor the replication article exists, then HVR will create a CDC table or replication article for the source table depending on the option (Prefer CDCTABs and Prefer ARTICLEs) selected along with this option.
- When Prefer CDCTABs is selected, HVR will create a CDC table for the source table. If it is not possible to create the CDC table, then HVR will create replication articles instead. And, if it is not possible to create replication articles also, then an error will be displayed.
- When Prefer ARTICLEs is selected, HVR will create replication articles for the source table. If it is not possible to create replication articles, then HVR will create the CDC table instead. And, if it is not possible to create a CDC table also, then an error will be displayed.
If existing articles are used and there are no subscribers while the @allow_anonymous property is set to false, replication may not work.

Configuration/Installation Required for Using Articles for Supplemental Logging

To use articles for supplemental logging, the following steps must be performed (once when HVR is installed for an SQL Server instance).

Install SQL Server Replication Components:
For log-based capture from the SQL Server Enterprise Edition or Developer Edition, if articles are used (see above), HVR requires that the SQL Server Replication Components are installed.
Create a distribution database:
- If articles are used (see above), a user with sysadmin privilege must create a distribution database for the SQL Server instance, unless one already exists. To do this, a user with sysadmin privilege should run SQL Server wizard ConfigureDistributionWizard, which can be run by clicking Replication ▶ Configure Distribution... Any database name can be supplied (click Next ▶ Next ▶ Next).
- If Always On AG is installed and articles are used, then only one distribution database should be configured. Either this can be set up inside the first node and the other nodes get a distributor that points to it, or the distribution database can be located outside the Always On AG cluster and each node gets a distributor that points to it there.

Dropping the Source Database

Depending on the location property SUPPLEMENTAL LOGGING (Supplemental_Logging) defined, HVR will use some of SQL Server's own 'replication components' or it will use SQL Server's Change Data Capture (CDC) feature. Based on this, HVR may enable the 'publish' replication option for the source database. When this replication database option is enabled, attempts to drop the database will give an SQL Server error.

When performing Deactivate Replication (hvractivate -d -ol), if the option Supplemental Logging is selected/enabled, HVR will check if there are any replication articles remaining in the database. If none are remaining, it will also disable the 'publish' replication database option (in SQL Server database). The database can then be dropped.

If articles are used for supplemental logging to drop the database immediately (without running Deactivate Replication first), the sysadmin must run the following SQL statement:

EXEC sp_replicationdboption 'source_database', 'publish', 'false';

Log Truncater

The location property LOG TRUNCATER (Log_Truncater) specifies who advances the SQL Server transaction log truncation point (truncates the log).

Following are the supported log truncater methods:

CAP_JOB

This method is used to indicate that the capture job regularly calls sp_repldone to unconditionally release the hold of the truncation point for replication. When this option is selected and Activate Replication (hvractivate) is run, depending on the value of the location property SUPPLEMENTAL LOGGING, HVR will also drop/disable the SQL Server agent jobs that are created when CDC tables are created through the CDC stored procedures and the agent jobs related to data distribution. As a result, the additional impact of auxiliary database objects to enable supplemental logging is minimized. For example, the CDC tables are not populated (and the space allocation increased) because the agent job to do this is dropped. Multiple Capture jobs can be set up on a single database with option CAP_JOB selected. However, note that if no Capture job is running with the CDC tables and/or Articles in place, the transaction log will grow because the truncation point for replication is not released. Do not set this option if there is another data replication solution or the database uses CDC tables.

CAP_JOB_RETAIN

This method should be used when capturing from a SQL Server database with the recovery mode set to Simple Recovery. The capture job moves the truncation point of the transaction log forward by calling the stored procedure sp_repldone at the end of each sub-cycle. Only part of the transaction log that has already been processed (captured) is marked for truncation (this is different from the CAP_JOB mode, where all records in the transaction log are marked for truncation, including those that have not been captured yet). This value is not compatible with multi-capture and does not allow for coexistence with a third party replication solution. This setting will also result in SQL Server's agent jobs being dropped/disabled, so the transaction log will grow when the capture job is not running and CDC tables and/or Articles are still in place. Do not set this option if another data replication solution is in place or CDC tables are used in the database.

LOGRELEASE_TASK

This method should be used if a separate job/task is created to release the truncation point for replication. For example, schedule a separate SQL Server Agent job to unconditionally call sp_repldone at an interval. Choosing the option LOGRELEASE_TASK will also result in SQL Server's agent jobs being dropped/disabled. However, as long as the scheduled log release task runs, the truncation point for replication is released, even if the capture job(s) is(are) not running. This option should only be used in conjunction with another replication or CDC solution if the log release task that is scheduled is aware of the requirements of the other solution.

NATIVE_DBMS_AGENT

This method should be used if native replication and/or CDC tables are used on the database. With this option, HVR will not drop/disable the native SQL Server agent jobs that are created when CDC tables and/or Articles are created. HVR will also not interfere with the release of the truncation point for replication. If CDC tables are used to enable supplemental logging it may cause I/O overhead (SQL Server jobs copy each change to a CDC table, which no-one uses).

Change Archive Directory

When using the Direct Transaction Log Access and SQL Access capture method, you can define the directory for transaction log backup files. Normally, HVR locates the transaction log backup files by querying the backup history table in the msdb database. Defining this parameter tells HVR to search for the log backup files in the directory specified in this property/field. It is recommended to define this parameter when using Always On Availability Groups.

Backup Directory

HVR will search for the log archives in the directory specified in this field/property.

When ARCHIVE WATCH DIRECTORY (Archive_Log_Path) is defined, it is mandatory to define FILE NAME FORMAT (Archive_Log_Format).

This field/property describes the filename format (template) of the transaction log archive files stored in the ARCHIVE WATCH DIRECTORY (Archive_Log_Path).

This parameter has no default, so it must be defined if ARCHIVE WATCH DIRECTORY (Archive_Log_Path) is defined.

This field/property accepts the following format variables:

%d - database name
%Y - year (up to 4 digit decimal integer)
%M - month (up to 2 digit decimal integer)
%D - day (up to 2 digit decimal integer)
%h - hours (up to 2 digit decimal integer)
%m - minutes (up to 2 digit decimal integer)
%s - seconds (up to 2 digit decimal integer)
%n - file sequence number (up to 64 bit decimal integer)
%% - matches %
* - wildcard, matches zero or more characters

HVR uses the %Y, %M, %D, %h, %m, %s and %n values to sort and processes the log backup files in the correct (chronological) order.
The combinations of the %Y, %M, %D and %h, %m, %s values are expected to form valid date and time values; however, no validation is performed.
Any value that is missing from the format string is considered to be 0.
When sorting the files comparison is done in the following order: %Y, %M, %D, %h, %m, %s, %n.

Capturing from SQL Server Always On availability groups

HVR allows you to capture from SQL Server Always On availability groups (AG) - a technology that provides High-Availability and Disaster-Recovery solutions in SQL Server.

All capture methods are supported for capturing changes from SQL Server Always On AG.

When using the Direct Transaction Log Access capture method, HVR can be configured to capture from either the primary or secondary node (active or passive).
When using the SQL Access capture method, HVR can be configured to capture from either the primary or secondary node (active or passive). Capture from the secondary node is supported since version 6.1.0/19.

Requirements for Capturing from Always On AG secondary

When you activate replication against a read-only secondary node, HVR cannot enable supplemental logging (create CDC tables) because this can only be done when connected to a primary node. In this case, HVR will generate a script named supp_log_add.sql on the source machine. This script contains commands to enable supplemental logging on the source database. The script must be executed manually on the primary node by a user with sysadmin or db_owner privileges.

Prior to version 6.1.0/30, HVR did not automatically create the supp_log_add.sql script for the above scenario. Hence, for log-based setup, we recommend activating replication using the Minimal permission model to generate the script. This will make it easy to manage supplemental logging.

The CDC tables will not be used by Microsoft or contain any data. The only reason they are created is so that Microsoft could log the primary key during updates. This will not cause any additional overhead on the primary node nor is it similar to running Microsoft's native CDC replication.

If you are not using CDC tables or any other type of replication, then you will need to create a job on the primary node to call the sp_repldone procedure on a regular basis. The sp_repldone procedure will move the truncation point within the transaction log so that the log does not continue to grow. This can be executed every time you perform the transaction log backup.

Configuration steps

Create a channel and location to connect to the SQL Server Always On AG standby node.
Configure location property Log_Truncater to be set to LOGRELEASE_TASK.
Activate replication in the channel with SQL Server Always On AG. The activation will fail and produce a script called supp_log_add.sql stored in the HVR_CONFIG\hubs\hub_name\channels\channel_name\locs\location_name\initsql directory.
Manually execute the supp_log_add.sql script on the primary node. Initially, a user with sysadmin privilege must run the script. Subsequently, a user with db_owner privilege can perform this action.
Create a job to run the sp_repldone procedure to move the truncation point.
```
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1.
```
The sp_repldone procedure does not truncate anything, the backup does that. The sp_repldone procedure just moves the truncation point.
Activate replication with option Supplemental Logging selected. The activation should complete without any errors.

Configuring Failover for Connections to SQL Server Always On AG

If HVR is connecting using HVR Agent to a database inside an SQL Server Always On AG, the following is required:

Create an HVR Agent Listener on each node.
Inside Failover Cluster Manager, configure a Role with a static IP address that controls the HVR Agent Listener service on all nodes.
1. Run Configure Role Wizard
2. Click Next ▶ Generic Service ▶ HVR Agent Listener ▶ Name ▶ Next ▶ Next ▶ Next ▶ Finish.
3. To configure a static IP address, click on Resources ▶ Name ▶ IP address ▶ then change it to an available static address.
Configure a Group Listener inside the Availability Group.
1. Start SQL Server Management Studio on the primary node.
2. Click AlwaysOn High Availability ▶ Availability Groups ▶ Name ▶ Availability Group Listeners ▶ Add Listener ….
For Always On AG inside Azure,
1. Configure an Internal or External Azure load balancer for the HVR Agent Listener using Powershell. Then attach each Azure node to this load balancer.
2. In HVR UI, enter the IP address of the Azure load balancer into the Agent Host field available in the Agent Connection section while creating a location or editing an existing location's agent connection properties.

HVR can now connect to Node as configured in step 1 or step 4 and Server as configured in Step 3.

Configuring Backup Mode and Transaction Archive Retention

HVR log-based capture requires that the source database is in Full recovery model and a full database backup has been done since this was enabled. Normally HVR reads changes from the 'online' transaction log file, but if HVR is interrupted (say for 2 hours) then it must be able to read from transaction log backup files to capture the older changes. HVR is not interested in full or incremental backups; it only reads transaction log backup files.

HVR supports only native single-media-family backup(s).
HVR does not support backup(s) on 'virtual devices'.
For Direct Transaction Log Access capture method, the backup(s) must be accessible to HVR on the file system.
For SQL Access capture method, the backup(s) must be accessible to SQL Server on the file system.
If SQL Access capture method is defined with BACKUP DIRECTORY (Archive_Log_Path), the backup(s) must be accessible to both SQL Server and HVR using the same path (UNC in case of network backup).

Transaction log (archive) retention: If a backup process has already moved these files to tape and deleted them, then HVR capture will give an error and a Refresh will be needed before replication can be restarted. The amount of 'retention' needed (in hours or days) depends on organization factors (how real-time must it be?) and practical issues (does a refresh take 1 hour or 24 hours?).

HVR normally locates the transaction log backup files by querying the backup history tables in the msdb database. But if Always On AG is configured then this information source is not available on all nodes. So when HVR is used with Always On AG, the transaction log backups must be made on a directory which is both accessible from all Always On AG nodes and also from the machine where the HVR capture process is running (if this is different) via the same path name. HVR should be configured to find these files by defining the following location properties ARCHIVE WATCH DIRECTORY (Archive_Log_Path) and FILENAME FORMAT (Archive_Log_Format).

ARCHIVE WATCH DIRECTORY should point to a file system directory which HVR will use for searching directly for the transaction log backup files, instead of querying msdb.
FILENAME FORMAT should specify a pattern for matching files in that directory.
This property accepts the following format variables:
- %d - database name
- %Y - year (up to 4 digit decimal integer)
- %M - month (up to 2 digit decimal integer)
- %D - day (up to 2 digit decimal integer)
- %h - hours (up to 2 digit decimal integer)
- %m - minutes (up to 2 digit decimal integer)
- %s - seconds (up to 2 digit decimal integer)
- %n - file sequence number (up to 64 bit decimal integer)
- %% - matches %
- * - wildcard, matches zero or more characters
- HVR uses the %Y, %M, %D, %h, %m, %s and %n values to sort and processes the log backup files in the correct (chronological) order.
- The combinations of the %Y, %M, %D and %h, %m, %s values are expected to form valid date and time values; however, no validation is performed.
- Any value that is missing from the format string is considered to be 0.
- When sorting the files comparison is done in the following order: %Y, %M, %D, %h, %m, %s, %n.

Capturing from SAP Source

HVR allows you to capture changes from a SQL Server database which is used by an SAP ECC system. To enable capture using SAP dictionary, the location property SAP Source (SAP_Source_Schema) must be defined while creating a location or by editing the existing location's source and target properties. Then while adding tables to a channel, the Table Selection dialog will display the SAP tables defined in the SAP dictionaries.

The SAP Unpack license is required for adding the pool, cluster, and long text (STXL) tables to a channel using the Table Selection dialog.

When SAP pool, cluster, and long text (STXL) tables are added to a channel using the Table Selection dialog, the following actions are automatically defined:

TableProperties with parameters PackedInside, CoerceErrorPolicy, and CoerceErrorType
- For each container (pool/cluster) table a separate action TableProperties is defined.
- This action is not defined for long text (STXL) tables.
Transform with parameter SapUnpack
Irrespective of the number of tables, only a single action Transform is defined.

SAP columns are non-nullable by default. They will be described as nullable in the hub's repository and thus as nullable in the target. This is valid for the non-key columns. Key columns will remain non-nullable.

SAP Data Types Conversion

Since v6.1.0/7

This option enables conversion/mapping of SAP specific data types (available in SAP dictionary meta-data) in source location to corresponding data type in the target location. The SAP specific data type will be localized with the source DB's data type and then mapped to HVR's Repository data type. For example, if you have an SAP system on SQL Server, the DATS data type will be localized as SQL Server's Date type, and then it is mapped to HVR Repository type ansidate.

This feature is supported for the following SAP specific data types:

DATS

If the SAP Data Types Conversion option is NOT selected, SAP specific data types are mapped to various other HVR Repositry data types. For more information, see Data Type Mapping for SAP NetWeaver (or SAP dictionary).

If the SAP Data Types Conversion option is selected during location creation, HVR will automatically define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

If the SAP Source (SAP_Source_Schema) location property/option is selected during location creation, by default, the SAP Data Types Conversion option also gets selected.

However, to enable SAP data type conversion for an existing location, select the SAP Data Types Conversion option by editing the location's source and target properties and then manually define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

Capture Limitations

HVR does not support log-based capture from Amazon RDS for SQL Server.
HVR does not support log-based capture from tables with Clustered Columnstore Index (CCI).

Compare and Refresh from SQL Server

HVR allows you to perform only Compare and Refresh from SQL Server database (without using Capture). This section describes the configuration requirements for performing only Compare and Refresh from SQL Server database.

Grants for Compare and Refresh from SQL Server

This section lists the grants required for performing only Compare and Refresh from SQL Server database. Based on your requirement, the HVR database user must be granted either of the permission models listed below.

DbOwner
The HVR database user must be granted db_owner role for the source database.
Minimal
In this permission model, the HVR database user does not need to be a database owner.

If the HVR database user needs to select from tables in another schema (for example if action TableProperties is defined with parameter Schema), then the following select privileges should be granted.

GRANT SELECT TO username; -- Let HVR read all tables

GRANT SELECT ON SCHEMA::dbo TO username; -- Let HVR only read DBO tables