Azure SQL Managed Instance as Source

Capture

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

Table Types

Local Data Processing supports capture from the following table types in Azure SQL Managed Instance:

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

Local Data Processing does not support capture from memory optimized tables and trigger-based capture from temporal tables.

Capture Methods

Local Data Processing allows only the SQL Access method (=SQL) for capturing () changes from Azure SQL Managed Instance.

SQL Access

In this capture method, Local Data Processing 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 Local Data Processing database User must be granted sysadmin role in this permission model.

DbOwner

The Local Data Processing 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:

Local Data Processing must already be installed before performing the following steps.

Create a special 'wrapper' SQL procedure:
This step is needed once when Local Data Processing 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 capture method, a user with a sysadmin privilege must create a special 'wrapper' SQL procedures called sp_hvr_dblog and sp_hvr_dbtable so that the Local Data Processing 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 Local Data Processing database User must then be allowed to execute this procedure. For this, the Local Data Processing database User must be added to the special msdb database and the following grants must be provided:
```
use msdb;create user username for login username;grant execute on sp_hvr_dblog to username;grant execute on sp_hvr_dbtable to username;
content_copy
```
Grant special read-only privilege:
This step is needed once when Local Data Processing 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 Local Data Processing user login a special read-only privilege in the master database.
```
use mastergrant view server state to username
content_copy
```
Perform special SQL statements:
When 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, 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 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 Local Data Processing database User with db_owner privilege.

Minimal

The Local Data Processing 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:

Local Data Processing must already be installed before performing the following steps.

Create a special 'wrapper' SQL procedure:
This step is needed once when Local Data Processing 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 capture method, a user with a sysadmin privilege must create a special 'wrapper' SQL procedures called sp_hvr_dblog and sp_hvr_dbtable so that the Local Data Processing 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 Local Data Processing database User must then be allowed to execute this procedure. For this, the Local Data Processing database User must be added to the special msdb database and the following grants must be provided:
```
use msdb;create user username for login username;grant execute on sp_hvr_dblog to username;grant execute on sp_hvr_dbtable to username;
content_copy
```
Grant special read-only privilege:
This step is needed once when Local Data Processing 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 Local Data Processing user login a special read-only privilege in the master database.
```
use mastergrant view server state to username
content_copy
```
Perform special SQL statements:
When 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, 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 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 Local Data Processing 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 Local Data Processing database User must then be allowed to execute these procedures.
The following grants must be granted to the Local Data Processing database User inside each capture database:
```
use capdb;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
content_copy
```
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 Local Data Processing database User a read-only privilege.
```
use capdb;alter role db_datareader add member username;
content_copy
```

Supplemental Logging

Local Data Processing 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 Local Data Processing 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 Local Data Processing.

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, Local Data Processing requires a CDC table to be created for each source table.

When performing ( -ol), if the option is selected, Local Data Processing will check whether supplemental logging is enabled in the database. If supplemental logging is not enabled in the database, then Local Data Processing will create a CDC table.

Supplemental logging is not supported when (=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 Local Data Processing 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 () defined, Local Data Processing 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, Local Data Processing 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 ( -d -ol), if the option is selected/enabled, Local Data Processing 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 () 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 must be set to CAP_JOB_RETAIN. In Local Data Processing UI, this property is automatically defined while .

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

Local Data Processing allows you to perform only and from Azure SQL Managed Instance (without using ). This section describes the configuration requirements for performing only and from Azure SQL Managed Instance.

Grants for Compare and Refresh from Azure SQL Managed Instance

This section lists the grants required for performing only and from Oracle database. Based on your requirement, the Local Data Processing database User must be granted either of the permission models listed below.

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

If the Local Data Processing database User needs to select from tables in another schema (for example if action is defined with parameter ), then the following select privileges should be granted.

grant select to username; -- Let Local Data Processing read all tables

grant select on schema::dbo to username; -- Let Local Data Processing only read DBO tables
content_copy