Azure SQL Managed Instance as Source

Capture

Fivetran HVR allows you to Capture changes from Azure SQL Managed Instance. HVR uses SQL Server ODBC driver to capture changes from an Azure SQL Managed Instance location. This section describes the configuration requirements for capturing changes from Azure SQL Managed Instance location.

Table Types

HVR supports capture from the following table types in Azure SQL Managed Instance:

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 only the SQL Access method (Capture_Method=SQL) for capturing (Capture) changes from Azure SQL Managed Instance.

SQL Access

In this capture method, HVR captures changes over an SQL connection. This method uses stored database function calls to retrieve incremental log fragments.

Grants for Log-Based Capture

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

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

SysAdmin

The HVR database User must be granted sysadmin role in this permission model.

DbOwner

The HVR database User must be granted db_owner role for the source database.

Following are the configuration requirements that must be met/performed for this 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 Azure SQL Managed Instance. But if Always On AG is installed, then this step is needed on each Always On AG node.
For SQL Access capture method, a user with a sysadmin privilege must create a special 'wrapper' SQL procedures called sp_hvr_dblog (replaced with sp_hvr_dblog2 in v6.1.5/5 and later) and sp_hvr_dbtable so that the HVR can 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 directory HVR_HOME\sql\sqlserver. 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:
For v6.1.5/5 and later, replace sp_hvr_dblog with sp_hvr_dblog2.
```
 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 special read-only privilege:
This step is needed once when HVR is installed for an Azure SQL Managed 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;
```
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 directory HVR_CONFIG\hubs\hub_name\channels\channel_name\locs\location_name\initsql 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

The HVR database User is not granted or does not require sysadmin or db_owner roles at runtime.

Following are the configuration requirements that must be met/performed for this 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.
For SQL Access capture method, a user with a sysadmin privilege must create a special 'wrapper' SQL procedures called sp_hvr_dblog (replaced with sp_hvr_dblog2 in v6.1.5/5 and later) and sp_hvr_dbtable so that the HVR can 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\sql\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:
For v6.1.5/5 and later, replace sp_hvr_dblog with sp_hvr_dblog2.
```
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 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 mastergrant view server state to username
```
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 three read-only procedures is available in the file called hvrcapdbowner.sql in directory HVR_HOME\dbms\sqlserver. 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

HVR supports only CDC tables (not the SQL Server's 'articles') for supplemental logging on Azure SQL Managed Instance. For this reason, the Transact-SQL operations WRITETEXT and UPDATETEXT are not allowed with the data to be replicated.

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 feature of SQL Server called as 'CDC tables'. To do log-based capture, HVR requires a CDC table to be created for each source table.

When performing Activate Replication (hvractivate -ol), if the option Supplemental Logging is selected, HVR will check whether supplemental logging is enabled in the database. If supplemental logging is not enabled in the database, then HVR will create a CDC table.

Supplemental logging is not supported when capturing from SQL Server using database triggers (Capture_Method=DB_TRIGGER).

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 option for SUPPLEMENTAL LOGGING is:

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.

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.

Log Truncater

The location property LOG TRUNCATER (Log_Truncater) specifies who advances the SQL Server transaction log truncation point (truncates the log). For Azure SQL Managed Instance, only the CAP_JOB_RETAIN method is supported.

To capture changes from Azure SQL Managed Instance, the location property Log_Truncater must be set to CAP_JOB_RETAIN. In HVR UI, this property is automatically defined while creating the Azure SQL Managed Instance location.

CAP_JOB_RETAIN

This method should be used when capturing from an Azure SQL Managed Instance 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.

Compare and Refresh from Azure SQL Managed Instance

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

Grants for Compare and Refresh from Azure SQL Managed Instance

This section lists the grants required for performing only Compare and Refresh from Oracle 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