This documentation is for an older version of HVR.

Quick Start for HVR - Azure SQL Database

This quick start guide helps you to get started with HVR for replicating data into Azure SQL Database.

To proceed with this replication you must have basic understanding about HVR's architecture and terminologies like Hub, Location, Channel, Location Groups, Actions etc.

The example here demonstrates how to replicate tables from one local SQL Server database (source location) to an Azure SQL server database (target location) residing in the Azure cloud.

To create the Azure components, the HVR for Azure VM image will be used. In Azure, it is possible to acquire a HVR for Azure image with license. For more information about installing HVR for Azure image on Azure, see Installing HVR on Azure using HVR Image.

The steps actually start by creating new databases and tables for HVR to replicate between. In real life these databases would already exist and be filled with the user tables, but for simplicity everything is created from scratch. Also, for simplicity, we will assume the source database resides on the hub as well and SQL server has already been installed there.

Before proceeding with this example ensure that the requirements for using HVR with Azure SQL Database are met.

For information about access privileges and advanced configuration changes required for performing replication using Azure SQL Database, see Requirements for Azure SQL Database.

Create Test Databases and Tables

Generally when getting started with HVR a source schema with tables and data already exists. If so then this step can be skipped.

This Quickstart uses two empty tables named dm01_product and dm01_order. In an existing SQL server database, create a test schema and create the tables using the following commands.

Make sure the database is setup for log-based capture as described in Grants for log-based capture.

In SQL Server Management Studio, create database testdb1. Next, create the test tables:

C:\> cd %HVR_HOME%\demo\hvr_demo01\base\sqlserver
C:\> osql -U hvr -P hvr -d testdb1 < hvr_demo01.cre
C:\> osql -U hvr -P hvr -d testdb1 < hvr_demo01.mod

In the Azure portal, create an Azure SQL database using SQL Database -> Add called testdb2.
Set the database server configuration's firewall to enable Allow access to Azure services:

Create the tables either using HVR's script as shown earlier or use HVR to create the tables during the initial load (HVR Refresh with Create Absent Tables). Check With key as Azure SQL requires tables to have a primary key.

Install HVR on-premises

First read section Architecture Overview which explains the HVR's terminology and architecture. In particular this explains the importance of a hub database.

Then install the HVR software on the hub machine by performing the steps in section Installing HVR on Windows.

Follow the steps in section Grants and steps for log-based capture database for enabling (log-based) capture on SQL Server.

Install HVR remote listener agent on Azure VM

You can find HVR for Azure in the Azure Marketplace and Azure Portal or do the installation manually: Installing HVR on Azure Manually

For steps to install HVR on Azure, see Installing HVR on Azure.

Create the Hub Database

This section describes how to create a hub database. The hub database is a repository database that HVR uses to control its replication activities. It contains HVR catalog tables that hold all specifications of replication such as the names of the replicated databases, the replication direction and the list of tables to be replicated. For more information about HVR hub server and database, see section Hub Server in System Requirements.

HVR supports the creation of a hub database on certain databases (location classes) only. For the list of supported location classes, see section Hub Database in Capabilities.

For this demonstration, the hub database (e.g. hvrhub) is created in SQL Server.

Create the hub database using the SQL Server Management Studio. Alternatively, use the following SQL statement:

create database hvrhub

Connect to Hub Database

This section describes how to connect HVR GUI to the hub database.

When HVR GUI is launched for the first time, the Register Hub dialog is displayed automatically. The Register Hub dialog can also be accessed from the main menu File ▶ Register Hub.

Skip steps 1 to 4, if HVR GUI is executed directly on the hub machine or if HVR hub is connected to a remote SQL Server database without using the SQL Server protocol. For more information about connecting HVR hub with remote SQL Server database, see section Connecting HVR Hub to a Remote SQL Server Database in Requirements for SQL Server.

Click Connect to HVR on remote machine.
To connect HVR GUI on a PC to a remote HVR hub machine, the HVR Remote Listener must be configured and running on the HVR hub machine.
Enter the name or IP-address of the hub machine in the Node field (e.g. myserver).
Enter the port number (defined in the HVR Remote Listener of the hub machine) in the Port field (e.g. 4343).
Enter the Login (e.g. myserveradmin) and Password for the hub machine. By default, this is the operating system login credentials of the hub machine.
Select SQL Server in the Class pane.
Specify Database Connection details. For more information about the Database Connection fields, see section Location Connection.
1. Enter the HVR hub database name in the Database field. For example, hvrhub.
2. Enter the SQL Server user name in the User field. This username is used to connect HVR to the SQL Server database. For example, hvr.
3. Enter the password for the SQL Server user in the Password field.
Click Connect.
Click OK in the prompt dialog asking to create catalog tables in the hub database. HVR displays this prompt when connecting to a hub database for the first time.

Upon successful connection to the hub database, the navigation tree pane displays the hub machine and the hub database. Location Configuration, Channel Definitions, and Scheduler are displayed under the hub database.

Create SQL Server Locations

Next create two locations (one for each database) using right-click on Location Configuration ▶ New Location.

For the source database location there is no need to check Connect to HVR on remote machine because testdb1 is on the same machine as the hub.

Ignore the Group Membership tab for now.

For the Azure database location, check the option Connect to HVR on remote machine. Enter the connection details of the VM. For SQL Server authentication Enter the Azure SQL database credentials at the Database Connection or use Windows Authentication by leaving the database credentials blank.

Create Location Groups

The channel needs two location groups. Under the new channel: right-click on Location Groups ▶ New Group. Enter a group name (for instance CENTRAL).

Add location db1 as a member of this group by checking the box for db1.

Then create a second location group, called DECENTRAL that has member db2.

The new channel also needs a list of tables to replicate. This can be done as follows; right–click on Tables ▶ Table Explore.

Choose the first location ▶ Connect.
In the Table Explore window, click on both tables and click Add.
In new dialog HVR Table Name click OK.
Close the Table Explore window.
Perform table select again on one of the other locations and confirm that all tables to be replicated have value Same in column Match.

Define Actions

The new channel needs two actions to indicate the direction of replication.

Right-click group CENTRAL ▶ New Action ▶ Capture.
Right-click Group DECENTRAL ▶ New Action ▶ Integrate. Check /OnErrorSaveFailed, this affects how replication errors are handled.

Note that the Actions pane only displays actions related to the objects selected in the left-hand pane. So click channel hvr_demo01 to see both actions.

Perform Initial Loading and Table Creation

HVR Refresh copies the data from one location to another location and optionally creates missing or mismatched tables and keys. In the navigation tree pane, right-click the channel and select HVR Refresh.

SC-Hvr-Refresh_demo01_create_absent_tables_with_key_recreate.png

For the source select location db1 and for target select location check db2. Check the options Create Absent Tables, With Key, Recreate Mismatched Tables and click Refresh.

Enable Replication with HVR Initialize

Now that the channel definition is complete, create the runtime replication system.

Right-click channel hvr_demo01 ▶ HVR Initialize.
Choose Create or Replace Objects and click HVR Initialize.

From the moment that HVR Initialize is done, all changes to database sourcedb will be captured by HVR when its capture job looks inside the logging.

HVR initialize also creates three replication jobs, which can be seen under the Scheduler node in the GUI.

Start Scheduling of Replication Jobs

Start the Scheduler on the hub machine by clicking in the HVR GUI on the Scheduler node of the hub database.

Next, instruct the HVR Scheduler to trigger the replication jobs.

The replication jobs inside the Scheduler each execute a script under $HVR_CONFIG/job/hvrhub/hvr_demo01 that has the same name as the job. So job hvr_demo01–cap–db1 detects changes on database testdb1 and stores these as transactions files on the hub machine.

The other job (hvr_demo01–integ–db2) picka up these transaction files and perform inserts, updates and deletes on the target database.

Test Replication

To test replication, make a change in testdb1:

 $ sqlplus testdb1/hvr
 SQL> insert into dm01_product values (1, 19.99, 'DVD');
 SQL> commit;

View the output of the jobs using a right mouse click on the job and select View Log. This log file is stored in %HVR_CONFIG%\log\hubdb\hvr_demo01–cap–db1.

Here is a sample job output:

 hvr_demo01-cap-db1: Capture cycle 1.
 hvr_demo01-cap-db1: Selected 1 row from 'dm01_product__c0' (201 wide).
 hvr_demo01-cap-db1: Routed 212 bytes (compression=42.7%) from 'db1' into \
 1 location.
 hvr_demo01-cap-db1: Finished. (elapsed=1.20s)
 hvr_demo01-integ-db2: Integrate cycle 1 for 1 transaction file (212 bytes).
 hvr_demo01-integ-db2: Integrated 1 change from 'dm01_product' (1 ins).
 hvr_demo01-integ-db2: Integrate used 1 transaction and took 0.017 seconds.
 hvr_demo01-integ-db2: Finished. (elapsed=0.14s)

This log indicates that the jobs replicated the original change to testdb2. Run a query on testdb2 to confirm:

 $ sqlplus testdb2/hvr
 SQL> select * from dm01_product;

prod_id	prod_price	prod_descrip
1	19.99	DVD

HVR Compare and Refresh

HVR Compare checks whether two locations have identical rows, and HVR Refresh copies the content of one location to the second location. In the HVR GUI, right–click on a channel ▶ HVR Compare (or HVR Refresh). Choose two locations by clicking on the Select buttons.

The outcome of the comparison is displayed below;