MVsync quick start

How to get your MultiValue data synchronized and normalized in SQL Server in the fewest steps.

Introduction

This guide walks through the minimum steps needed to get MVsync up and running: synchronizing files from a MultiValue database into an MVsharp SQL Server account, and then presenting that data as normalized SQL views. The guide covers the following areas:

1. Installing the MVsync Administrator.
2. Adding your MultiValue and MVsharp servers.
3. Installing the MVsync server software into your accounts.
4. Selecting the files to synchronize.
5. Starting and monitoring the synchronization server.
6. Creating normalized views of your data.

Supported MultiValue source databases are UniVerse, UniData, OpenQM, jBase and D3. Synchronization can be one-way (MultiValue to SQL Server) or bi-directional.

For full details and screenshots of every step, see Data synchronization and Data normalization.

Prerequisites

The following environment is required in order to use MVsync:

1. MVsharp version 4.0.8.0 or later.
2. MVsync version 1.0.12 or above.
3. Windows 7 or above.
4. .NET Framework 4.5.
5. Microsoft SQL Server 2012 or later.
6. Microsoft Message Queue (MSMQ) server enabled.

To enable MSMQ, open Control Panel > Programs and Features > Turn Windows features on or off, tick Microsoft Message Queue (MSMQ) Server and click OK.

Installing MVsync

MVsync is delivered as a single Microsoft installer which installs the MVsync Administrator.

1. Run the MVsync installer and click Next.
2. Accept the default install folder or choose a new one with Browse.
3. Select Everyone to make MVsync available to all users on the machine.
4. Click Next to confirm, accept the license agreement with I Agree, and click Next to install.

Once installed, launch the MVsync Administrator. The tool is made up of four panels:

Panel	Description
Servers	The list of configured MultiValue and MVsharp servers.
Synchronization and Normalization configuration, and Server Status	The tabs used to install software, configure files, normalize and monitor.
Activity	The file(s) that IO operations are currently taking place on.
Object Properties	The properties of the currently selected object, where settings are saved.

See Installation for screenshots of each installer step.

Adding servers

Both the MultiValue source server and the MVsharp destination server must be defined before anything else can be configured.

1. Expand the Servers tree.
2. Select File > Add Server.
3. In the Object Properties pane choose the Server Type (UniVerse, UniData, OpenQM, jBase, D3 or MVsharp) and complete the connection details.
4. Click Save, then click Test to confirm a connection can be made.

Repeat for each server. Default settings for each MultiValue environment can be reviewed under File > Defaults.

Installing the server software

The MVsync software must be installed into both the MultiValue account holding the source data and the MVsharp account that will receive it. Both are done from the Installation tab.

Installing on the MultiValue source account

1. Select the Installation tab.
2. Choose your MultiValue server from Source System (for example a UniVerse server).
3. Choose the account from Source Account (for example UVQA).
4. Click Install Software and click OK when the progress window completes.

Installing on the MVsharp destination account

1. Still on the Installation tab, choose your MVsharp server from Source System.
2. Choose the MVsharp account from Source Account (for example CUSTOMER).
3. Click Install Software and click OK when complete.

Synchronizing files

Files are selected for synchronization on the Synchronized Files tab.

1. Select the MultiValue server in Source System and the account holding your data in Source Account. The files in the account will be listed (this can take a while for large accounts).
2. Select the MVsharp server in Destination System and the target account in Destination Account.
3. Tick the box next to each file to be synchronized, for example CUST.MASTER.
4. Click Validate Synchronization Settings and click OK.
5. Click Deploy Settings and click OK on each confirmation.

To stop synchronizing a file, untick it and click Deploy Settings again.

Starting the synchronization server

The synchronization server is configured and controlled from the Status tab.

Server settings

Select the MVsharp server in Source Server and the MVsharp account in Source Account, then set the following in the Object Properties pane and click Save:

Setting	Description
MaxImports	The number of processes started to perform the initial synchronization of files.
MaxClients	The number of clients running in parallel servicing the message queue. More clients increase the available bandwidth.
MaxUpdates	The number of parallel clients processing updates from SQL Server back to the MultiValue database. 0 = one-way only.

Licensing

Each client requires a connection to your MultiValue server. This has licensing implications.

Starting and verifying

The Status window has three buttons: Refresh, Start Server and Stop Server.

1. With the Source Server and Source Account selected, click Start Server and click OK.
2. Click Refresh to see the current status.

To verify that data is flowing, check the file in MVsharp, update it on the MultiValue side, and check again. For example, in a NetShell session on the CUSTOMER account:

Terminal (NetShell)

>COUNT CUST.MASTER
0 record(s) counted

In a UniVerse session on the UVQA account, touch every record so it is sent across:

Terminal (UniVerse)

>COPY FROM CUST.MASTER TO CUST.MASTER ALL OVERWRITING
102 records copied.

Back in NetShell, check the queues and the file:

Terminal (NetShell)

>MvSyncStatus
No of messages on the remote queue is 0
No of messages in the process queue is 0
No of messages in the update queue is 0
No of client processes processing messages is 3
...

>COUNT CUST.MASTER
102 record(s) counted

Bi-directional synchronization

To have changes made in SQL Server written back to the MultiValue database, set MaxUpdates to a non-zero value. This should be the same as MaxClients.

Normalizing data

MVsync creates normalized SQL views over the synchronized base table, so the base table itself never needs to be normalized. All data transformation (dates, conversion codes etc.) is handled by the Dynamic Normalization engine inside SQL Server.

After upgrading MVsharp

If a new version of MVsharp is installed, run UPGRADE.SQLSERVER in each account to deploy the latest SQL extensions.

Analyzing a table

1. Select the Data Normalization tab.
2. Choose the MVsharp server in Source System and the database in the database dropdown (for example CUSTOMER).
3. Choose the file in TableName (for example CUST.MASTER, which is the _CUST_MASTER table).
4. Click Analyze Table and click Yes.

The analysis creates the following automatically:

View	Description
Single valued view	All single valued dictionary fields, named after the file. A phrase with the file name is added to the dictionary.
Associated multi value views	One view per association, named after the table plus the first field of the association.
Data driven views	Views created from multi values found in the data itself, useful where the dictionary is incomplete. Review these to confirm they reflect the true data structure.

Deploying views

1. Select the view from the View Name dropdown (for example CUST.MASTER). Its columns are listed.
2. Optionally, add more columns from the Column Names list with Add Column.
3. Click Deploy View for a single valued view, or Deploy Mv View for a multi valued view, and confirm.

The views can now be queried from SQL Server Management Studio like any other table:

SQL

SELECT * FROM CUST_MASTER

Creating a custom view

1. Create a phrase in the file dictionary with the name of the view:

   Terminal

   >ED DICT CUST.MASTER CUST_MASTER_C3
   New record.
   ----: I
   0001= PH
   0002=
   ----: FI
   "CUST_MASTER_C3" filed in file DICT "CUST.MASTER".
   

2. On the Data Normalization tab select the table in Table Name and the new view in View Name.

3. For each column required, choose it from Column Name and click Add Column.
4. Click Deploy View (single valued) or Deploy Mv View (multi valued) and click Yes.

The dictionary phrase is updated with the columns added, and the view appears in SQL Server.

Deleting a view

Select the view, click Delete View and click Yes. The view is removed from SQL Server and its phrase is removed from the dictionary.

Useful commands

Command	Description
MvSyncStatus	Displays the remote, process and update queue sizes and the activity of each client.
COUNT {FileName}	Quick check that records have arrived in the synchronized file.
UPGRADE.SQLSERVER	Updates and deploys the latest SQL extensions in the account after an MVsharp upgrade.
ED DICT {FileName} {View}	Create or inspect the dictionary phrase that defines a view.

Next steps

• Data synchronization: the full synchronization guide with screenshots.
• Data normalization: analyzing tables and creating views in detail.