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 .

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

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 .

ODBC Connection

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

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

Location Connection

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

Field	Description
Server	The fully qualified domain name (FQDN) name of the Azure SQL Database server. Example: cbiz2nhmpv.database.windows.net
Database	The name of the Azure SQL database. Example: mytestdb
User	The username to connect HVR to the Azure SQL Database. The username should be appended with the separator '@' and the host name of the Server. The format is <username>@<hostname>. Example: hvruser@cbiz2nhmpv
Password	The password of the User to connect HVR to the Azure SQL Database.
ODBC Driver	The user defined (installed) ODBC driver to connect HVR to the Azure SQL Database.

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 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 is not supported.
• 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 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 and loading data into burst tables during with /Burst.

When using 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 .

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;
  content_copy

• Minimal
  In this permission model, the User does not need to be a database owner. This model cannot use action /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;
  content_copy

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

  grant create procedure to hvruser ;
  content_copy