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 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.
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.
This section lists and describes the connection details required for creating Azure SQL Database location in HVR.
|Server||The fully qualified domain name (FQDN) name of the Azure SQL Database server. |
|Database||The name of the Azure SQL database. |
|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: [email protected]
|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.|
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.
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
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.
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:
MinimalIf action Integrate /DbProc is defined, then create procedure privilege is also needed.
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: