Quick Start for HVR on SQL Server

From HVR
Jump to: navigation, search

This appendix shows how to set up an HVR channel (called hvr_demo01) to replicate between SQL Server databases. 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. Likewise these databases would normally be on different machines, but again for simplicity everything is just kept on the hub machine.

Before following this quickstart, please make sure the requirements have been met; see Requirements for SQL Server.

WD-Quickstart-for-SQL-Server.png

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

For the target, create two test databases, each containing two empty tables named dm01_product and dm01_order.

In SQL Server Management Studio, create databases testdb2 and testdb3. You can either create the tables using HVRs scripts or let HVR create them during initial loading (HVR Refresh with Create Absent Tables).

Create the test tables using HVRs script:

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


Install HVR

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.

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.

SC-Hvr-RegisterHub SqlServer.png

For a new hub database a dialog will prompt: Do you wish to create the catalogs? Answer Yes.

Create SQL Server Locations

Next create three locations (one for each test database) using right–click on Location Configuration ▶ New Location.

SC-Hvr-Location SQLServer.png

In this example 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.

Make locations for testdb2 and testdb3 too.

Now define a channel using Channel Definitions ▶ New Channel.

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).
SC-Hvr-LocationGroup demo01 CENTRAL generic.png

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 members db2 and db3.

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 of the three locations ▶ 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 on group CENTRAL ▶ New Action ▶ Capture.
  • Right–click on Group DECENTRAL ▶ New Action ▶ Integrate. Check /OnErrorSaveFailed, this affects how replication errors are handled.

SC-Hvr-Gui Channel demo01.png

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.

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.

SC-Hvr-InitializeFinished demo01.png

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

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

SC-Hvr-Gui-Scheduler sqlserver.png

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

SC-Hvr-Gui demo01 Start.png

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 two jobs (hvr_demo01–integ–db2 and hvr_demo01–integ–db3) pick up these transaction files and perform inserts, updates and deletes on the two target databases.  

Test Replication

To test replication, make a change in testdb1:

SQL> insert into dm01_product values (1, 19.99, 'DVD');

In the HVR log file you can see the output of the jobs by clicking on View Log. This log file can be found in %HVR_CONFIG%\log\hubdb\hvr_demo01–cap–db1.

SC-Hvr-Gui demo01 viewlog.png

The job output looks like this:

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 \
2 locations.
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)
hvr_demo01–integ–db3: Integrate cycle 1 for 1 transaction file (212 bytes).
hvr_demo01–integ–db3: Integrated 1 change from 'dm01_product' (1 ins).
hvr_demo01–integ–db3: Integrate used 1 transaction and took 0.02 seconds.
hvr_demo01–integ–db3: Finished. (elapsed=0.15s)

This indicates that the jobs replicated the original change to testdb2 and testdb3. A query on testdb2 confirms this:

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.

SC-Hvr-Compare bulk generic.png

The outcome of the comparison is displayed below;
SC-Hvr-CompareResult demo01 identical.png