Quick Start for HVR into Azure SQL Database
This quick start guide helps you to get started with HVR for replicating data into Azure SQL Database.
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.
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 in Azure SQL Database, see Requirements for Azure SQL Database.
- 1 Create Test Databases and Tables
- 2 Install HVR on-premise
- 3 Install HVR remote listener agent on Azure VM
- 4 Create the Hub Database
- 5 Connect to Hub Database
- 6 Create SQL Server Locations
- 7 Create Location Groups
- 8 Define Actions
- 9 Perform Initial Loading and Table Creation
- 10 Enable Replication with HVR Initialize
- 11 Start Scheduling of Replication Jobs
- 12 Test Replication
- 13 HVR Compare and Refresh
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 and steps for log-based capture database
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-premise
First read section Introduction 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
To create a new VM using HVR for Azure from the Azure Marketplace, perform the following steps:
- In the Azure portal, go to the Marketplace and select HVR for Azure Choose the version most appropriate for you: “Bring Your Own License”, “1 source” or “5 sources”. Note the 1 source and 5 source variants include a HVR license and come at an additional cost. Press Create.
- You will now go through a wizard with sequential steps “Basics”, “Size”, “Settings”, “Summary” and “Buy”. In each step you can abort or return.
- In Basics: Select VM Disk type to be HDD as to be able to select the entry level machine types (A) in the next step. Enter the credentials you wish to use, select or create a resource group and determine the location you want HVR to run in(typically the same as your databases). Press OK. In Size: Select the appropriate machine type. Azure will suggest machine type A1, which is perfectly suitable to run the HVR for Azure image when used as a remote listener agent only. Press Select. In Settings, you will need to choose the storage account and network settings. Default Azure will create new ones. If this is your first time, the default settings are OK. Advanced users may want to reuse existing storage accounts and virtual networks. Press OK. In Summary, all your choices are listed for your reference. Press OK. In Buy, you will be shown the regular costs of the VM you are going to deploy plus the price of the HVR license. The BYOL version is priced €0.000, as the image does not contain a license. Press Purchase.
- You will now see the VM image seeing deployed in the Marketplace. This will take a while. Once it is finished, the details of the created VM can be accessed in Virtual Machines.
- Optional step: Default, no DNS name is created for the IP address of the VM. To create a DNS name, click on DNS Name /Configure of the created VM. You can then enter a DNS name for your VM.
- The HVR for Azure Image will create a remote listener agent with network encryption enabled by default. There is a certificate needed to make a connection. Your connection details on the hub should be configured to force encryption of all data. For this you will need to download the necessary certificate from the Agent VM. For this, Login to your Azure VM using remote desktop and open the Getting Started Web page on its home page and click on the certificate link.:
- Copy the contents of the certificate and save into a certificate file (.pub_cert) on your hubmachine in %HVR_HOME%\lib\cert.
- Exit the Remote Desktop session to the Azure VM. On your hub, create a new Location. Check the box /SslRemoteCertificate and enter the name of the certificate file you just created.
- If you want to update an existing Azure location, add Action LocationProperties /SslRemoteCertificate <filename> for your Azure location (or group) on your hub instead.
Create the Hub Database
In SQL Server Management Studio, create the hub database (e.g. hvrhub) to store the channel definition.
Connect to Hub Database
Start the HVR GUI on the hub machine by clicking on the HVR GUI icon.
First, Register the hub database: Right–click on hub machines ▶ Register hub. Enter connection details.
For a new hub database a dialog will prompt: Do you wish to create the catalogs? Answer Yes.
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
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.
The new channel needs two actions to indicate the direction of replication.
- Right–click on group CENTRAL ▶ New Action ▶ Capture.
- Right–click on 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 on 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 HVR GUI, right-click on the channel and select HVR Refresh
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 on 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 testdb1 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
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.
To test replication, make a change in testdb1:
$ sqlplus testdb1/hvr SQL> insert into dm01_product values (1, 19.99, 'DVD'); SQL> commit;
Here is sample job output:
C:\> notepad %HVR_CONFIG%\log\hvrhub\hvr.out 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;
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.