Requirements for Azure SQL Database

Capture	Hub	Integrate

This section describes the requirements, access privileges, and other features of HVR when using Azure SQL Database for replication. Azure SQL Database is the Platform as a Service (PaaS) database of Microsoft's Azure Cloud Platform. It is a limited version of the Microsoft SQL Server. HVR supports Azure SQL Database through its regular SQL Server driver. For information about compatibility and supported versions of Azure SQL Database with HVR platforms, see Platform Compatibility Matrix.

For the capabilities supported by HVR on Azure SQL Database, see Capabilities for Azure SQL Database.

For information about the supported data types and mapping of data types in source DBMS to the corresponding data types in target DBMS or file format, see Data Type Mapping.

ODBC Connection

Microsoft SQL Server Native Client 11.0 ODBC driver must be installed on the machine from which HVR connects to Azure SQL Database. For more information about downloading and installing SQL Server Native Client, refer to Microsoft documentation.

HVR uses the SQL Server Native Client ODBC driver to connect, read and write data to Azure SQL Database during capture, integrate (continuous ), and refresh (row-wise).

Location Connection

This section lists and describes the connection details required for creating Azure SQL Database location in HVR.

Configuration Notes

The Azure SQL database server has a default firewall preventing incoming connections. This can be configured under Database server/Show firewall settings. When connecting from an Azure VM (through an agent), enable Allow access to Azure services. When connecting directly from an on-premises hub, add its IP address to the allowed range. An easy way to do this is to open the webportal from the machine, from which you connect to the database. Your IP address will be listed and by clicking Add to the allowed IP addresses, the IP address will be automatically added to the firewall.

Capture

HVR allows you to Capture changes from Azure SQL Database. This section describes the configuration requirements for capturing changes from Azure SQL Database.

• Only trigger-based capture is supported from Azure SQL Database. Log-based Capture is not supported.
• Capture parameter /ToggleFrequency must be defined because the Azure SQL Database does not allow HVR's hvrevent.dll (no DLL libraries allowed). Note that if a high frequency is defined (e.g. cycle every 10 seconds) then many lines will be written to HVR's log files. Configure the command Hvrmaint to purge these files.

Grants for Trigger-Based Capture

The User should be granted db_owner role for the source database.

Integrate and Refresh

HVR uses SQL Server BCP interface for copying data into Azure SQL Database tables during bulk Refresh and loading data into burst tables during Integrate with /Burst.

When using HVR Refresh with option Create absent tables in Azure SQL Database, enable the option "With Key" because Azure does not support tables without Clustered Indexes.

Grants for HVR on Target Database

This section provides information about the user privileges required for replicating changes into Azure SQL Database using HVR Refresh.

When replicating changes into a target Azure SQL Database, HVR supports the following two permission models: DbOwner, and Minimal.

• DbOwner
  In this permission model, the HVR User must be made a database owner (db_owner role). Normally, the database objects which HVR sometimes creates will be part of the dbo schema as the replicated tables.
  Alternatively, these HVR database objects can be put in a special database schema so that they are not visible to other users. Following are the SQL commands for this alternate method:

  create schema hvrschema;
  
  grant control on schema::hvrschema to hvruser;
  
  alter user hvruser with default_schema=hvrschema;
  

• Minimal
  In this permission model, the User does not need to be a database owner. This model cannot use action TableProperties /Schema to change tables with a different owner. The following SQL commands must be executed so that HVR can create its own tables:

  grant create table to hvruser;
  
  create schema hvrschema;
  
  grant control on schema::hvrschema to hvruser;
  
  alter user hvruser with default_schema=hvrschema;
  

  If action Integrate /DbProc is defined, then create procedure privilege is also needed.

  grant create procedure to hvruser ;