LSRetailDataDirector UserGuide

(1)

Data Director - User Guide

(2)

1 Job Scheduling / Replication

1.1 About Replication

Overview

A Computer Database is a structured collection of data in the form of records stored in a computer system. A computer database relies on application software to organize the data it needs to store.

It is quite possible that multiple transactional databases are maintained in disparate locations due to reliability, accessibility, fault tolerance etc. These databases need to get consolidated into central database for reporting purposes or vice versa, central database needs to distribute data into multiple transactional databases. If a database can log its individual actions, it is possible to create a duplicate of the data in real-time.

The act of inserting, updating and deleting same information in a database based on changes in another database is called replication. Replication is a process of creating identical data records from one database to one or more other databases.

The aim of replication is to keep copy of similar data on the same or on a different platform and synchronize it to bring consistency. Usually, one database is the main database, or a head office, where master data is maintained and replicated to other databases.

(5)

1.2 LS Retail Architecture

LS Retail supports the following architecture

1. Online – Back office and POS are online with head office that is POS and back office users are accessing head office database.

FIGURE 1.1 – 1: ONLINE ARCHITECTURE Following are few advantages of online architecture

 Full access to Microsoft Dynamics NAV solution  Real-time access to all data

 No overhead of data replication needed Following are few disadvantages of online architecture

 Limited number of concurrent users at head office database  Highly dependent on communication lines

(6)

2. Online / Offline – Back office users are accessing head office database for back office transactions and POS is having its own database for all transactions.

FIGURE 1.1 – 2: ONLINE/OFFLINE ARCHITECTURE Following are few advantages of online/offline architecture

 POS is independent

 POS can use online services  Back-office has real-time data

Following are few disadvantages of online/offline architecture  Data Replication to and from POS

(7)

3. Mixed store – In this architecture, few stores may be online with head office and few may be offline. POS may be online with store server or may have its own database.

FIGURE 1.1 – 3: MIXED STORE ARCHITECTURE Following are a few advantages of mixed store architecture

 Less data processing at head office  Sales History available at store

 If store is online, Back-office has real-time data Following is one disadvantage of mixed store architecture

(8)

4. Standalone store – In this architecture, all the stores are accessing their local database and there is very limited online access to head office database. Here, POS may be online with store server or may have its own database.

FIGURE 1.1 – 4: STANDALONE STORE ARCHITECTURE Following are a few advantages of Standalone store architecture

 Store database has full visibility of store data

Following are a few disadvantages of Standalone store architecture

 All the data needs to be replicated to and from store database – which may take longer time When any of the databases is offline, data needs to be replicated to and from that database.

(9)

1.3 Methods of Replication

To replicate data in a table from database A to the same table in database B, two general methods can be used

 The program compares data of two tables in database A and database B to find the differences between them. It changes the data of table in database B and makes it identical to the data of table in database A. This method may prove to be slow if there are too many records in the tables.

 The program knows before replication starts which data has been inserted, updated or deleted in database A. The program replicates only the corresponding changes in database B.

A replication process is required to automatically replicate the changes made in one database to another. The replication process in LS Retail has following main functionalities:

 It allows replication of data between two databases.

 It allows to schedule the replication of each table at user defined intervals.

 It allows controlling data distribution; that is, which records are to be replicated where.

 It allows configuring which fields in a table are to be replicated depending on their functionality.  It offers several methods of replication, whether replication time is important, whether there is a

need to extend replicated data from databases to POS terminals, or a simple replication, all depending on the tables being replicated.

 It allows replication of data between two databases that do not have the same table and field structure.

LS Retail provides following methods for replication

Normal – In case of normal method, data is replicated between the two tables by comparing and making

them identical. It reads the records from the table (From-Table) in source database, finds the corresponding records in the table (To-Table) in destination database, and makes them identical. If the records in From-Table do not exist in the To-Table, the replication process adds them to the To-Table. The replication process reads through the records in the To-Table and deletes them if they do not exist in From-Table.

Normal replication of a table from the head office to stores leaves no trace of which records have been deleted in the table in the stores databases. Therefore, if the POS terminals are not online in the store, and a given table has to be replicated to the POS terminals in the store, the store's database cannot tell the POS terminal‟s database about which records to delete. It is therefore not recommended to replicate tables with normal method that is sent to the POS terminals.

Normal with Replication Counter – A normal replication with Replication Counter limits the number of

records replicated each time. A new field that acts as Replication Counter (datatype integer) is maintained in the table. Replication Counter value is incremented when there is any insertion or modification in the records of that table. The value is a number one higher than the Replication Counter value that was given to the last record inserted or modified in the table. The data in the field Replication Counter is compared with replication counter in last replication (This field is defined at subjob level) and the records with greater replication counter are replicated.

This method does not support delete. Records that are deleted from a table replicated with replication counter will not be deleted from the table in the database replicated to. The replication counter for record being deleted from source database cannot be compared with replication counter of last replication as the record does not exist in the source database.

(10)

1.3.1 Adding Replication Counter to Tables

If a table needs to replicate data with replication counter which has no field that is suited to act as replication counter, a replication counter field should be added to the table.

To Add Replication Counter to a Table 1. Click Tools, Object Designer.

2. Browse to the table, to which the replication counter field is to be added. Click Design. 3. At the bottom of the table, press F3 to add a new field.

4. Fill in the Field Name field (for example Replication Counter). 5. In the Data Type field, select the Integer option.

6. Close the table, save and compile. Then click Design again.

7. Click View, Keys to see the Keys window. Add the new field as a key to the table.

8. Press F9 to see the C/AL Editor window. In the Replication Counter - On Validate () function, add a global or local variable that denotes the table itself. Then add the following code (TableName is the name of the variable just created):

TableName.SETCURRENTKEY("Replication Counter"); If TableName.FIND('+') THEN

"Replication Counter" := TableName."Replication Counter" + 1; ELSE

"Replication Counter" := 1;

9. In the C/AL Editor, in the OnInsert () and OnModify () triggers for the table, add the following code:

VALIDATE("Replication Counter");

10. Save and compile the changes.

11. Look for all instances in the code in the database objects where records could possibly be inserted into the tables or existing ones modified, and make sure .INSERT and .MODIFY functions are called with parameter (TRUE), that is to make sure the triggers are run.

 By Actions – Whenever there is any change (insert, modify, delete or rename) in the records of a

table, the system keeps track of those changes in Preaction table. Using the By Actions method, the system uses Preaction/Actions table to replicate data from source table to destination. The system uses the Preaction/Actions table to send only the changes that have been made in the From-Table ID to the To-Table ID. This method limits the number of records that need to be replicated.

1.3.2 Adding Actions Generation Code to Tables

If a table needs to be replicated by action having no code written to generate actions on different triggers, code can be added to different triggers of the table.

(11)

To Add Code to Generate Actions to a Table 1. Click Tools, Object Designer.

2. Browse to the table, to which code needs to be added for by action. Click Design. 3. At the end of the table, press F9 to see the C/AL Editor window.

4. Add a new function CreateAction with a parameter of type integer for example with the name Type. This function should contain 3 local variables as follows:

Variable Name Data Type Subtype

RecRef RecordRef

XRecRef RecordRef

ActionsMgt Codeunit Actions Management

TABLE 1.1 – 1: REPLICATION COUNTER 5. Following code should be added in the function

//Type: 0 = INSERT, 1 = MODIFY, 2 = DELETE, 3 = RENAME RecRef.GETTABLE(Rec);

xRecRef.GETTABLE(xRec);

ActionsMgt.CreateActionsByRecRef(RecRef,xRecRef,Type); RecRef.CLOSE;

xRecRef.CLOSE;

6. Then add the CreateAction(0) code in Oninsert trigger, CreateAction(1) in OnModify trigger, CreateAction(2) in OnDelete trigger and CreateAction(3) in OnRename trigger of table.

7. Save and compile the changes.

8. Thereafter look for all instances in the code in the database objects where records could possibly be inserted into the table or existing records are modified, and make sure the triggers are called with parameter (TRUE).

1.4 Summary

This section explains about Data Replication and concepts of the Data Replication. Following are the topics discussed in this section:

 About Replication

 LS Retail Architecture – Online, Online/Offline, Mixed Store and Standalone.  Methods of Replication – Normal, Normal with Replication Counter and By Actions.  Adding Replication Counter to a table

(12)

2 About Data Director

The Data Director is a product of LS Retail ehf., the developers of LS Retail Back Office system and LS POS application. The Data Director is closely tied to these applications and plays an integral part in enabling the system to function as a whole in a distributed environment.

The Data Director is an application specialized in moving data between databases in a fast and efficient way. It can easily move data between different databases such as NAV and MS SQL Server. Generally, if the database supports Microsoft ADO interface, Data Director shall be able to use it. The benefit of using Data Director is that there is no programming involved in order to replicate data.

Data Director is a flexible tool that can be adapted to a variety of data transfer scenarios. The most commonly used configurations are for:

 Moving data between the head office, stores and POS Terminals in a Microsoft Dynamics NAV based retail organization.

2.1 Modes of Data Director

Data Director works in three different modes

 Data Director – This is the regular flavor of Data Director. This mode is used to send data between databases. This mode is non-interactive.

 2nd Stage Data Director – This mode of Data Director is used to forward the data packages being created by Data Director Service running in Data Director Mode.

 Transaction Server – This is interactive mode of Data Director and used by POS to make queries into remote databases.

2.2 Concepts in Data Director

Following is a list of terms and titles often used in connection with the Data Director

 DBServer.exe - This is the server component of the Data Director that handles requests to access

different database systems. The DBServer is run as a service on the host computer.

 TransAutomClient.dll (also referred as DD Client) – This is used by LS Retail for sending queries to

the Data Director and the Transaction Server. This is the automation server being called from LS Retail NAV to communicate with DBServer.exe.

 Database Plugins - The Data Director uses a plugin to connect to different types of databases. The

plugins can be regarded as drivers that allow the Data Director to read from and write into the different types of databases.

 Package - A package contains the data being transferred between the databases. A package has a

destination Data Director and a destination database, which can be one or many, depending on how it has been created. This essentially represents a unit of work for the Data Director.

 Host Computer - The computer on which the Data Director (DBServer) service is running.

 Source Database - This is the database from where data need to be read.

 Destination Database - This is the database where data need to be written.

 Log Database - This is the database used by Data Director to keep track of its tasks and the

packages it is currently working on.

 Scheduler Database - This is the Microsoft Dynamics NAV database that contains all the details

regarding tasks that the Data Director should perform (where to send, when to send, which tables, which fields etc.). The Data Director log and the Scheduler can also be configured to reside in the same database.

 Scheduler - The Scheduler is used for scheduling Data Director activities at predefined intervals,

such as sending sales data to head office database every hour. The Scheduler can be run either on Microsoft Dynamics NAV client or using Microsoft Dynamics Navision Application Server (NAS).  DD - An acronym for the Data Director

(13)

2.3 Summary

This section explains about Data Director, Data Director Modes and terminology used. Following are the topics covered in this section:

 About Data Director

 Modes of Data Director – Data Director, 2nd

Stage Data Director and Transaction Server.  Concepts in Data Director

(14)

3 How Data Director Works

The main functionality of Data Director is to replicate data between two or more databases as efficiently as possible. It accomplishes this by aggregating data into packages thus minimizing the amount of data transmitted over the network. These packages are processed in a multicast like way, enabling the Data Director to handle a very high count of end points.

The Data Director is run as a service and listens to incoming requests or packages. The DD Client is used by LS Retail application in order to interact with the Dbserver service and tells about what to do. There are two types of interactions: request to read data from the source database, and request to write data to the destination database.

If the Data Director receives a read instruction, it will start by connecting to the source database using the appropriate plugin. It then proceeds to read data from the database and stores it in a package. The package can contain data from many different database tables.

Once the requested data is read, the Data Director has two possibilities. The first is to write the data in the package into destination database, maybe by using another plugin. This provides an easy and convenient way to transfer data between different databases, since the Data Director converts the data along the way, making sure the destination database understands it.

The second option is to forward the package to one or more Data Directors. Once the package is received by the receiving Data Director, it can proceed to write the contents of the package into one or more destination databases.

The feature of being able to forward one package to more than one Data Director is most useful in the retail environment where price changes or updates of some product items need to distribute to some or all the stores. This is done automatically by the Data Director once configured, relieving the user to focus on store operations.

The DD Client can connect to a Data Director Service running on other host computers. This makes it easy to create a network of Data Directors that can be controlled from one central location.

3.1 Scheduler Database

The Scheduler Database (also known as Design Database) is included in the Microsoft Dynamics NAV application. It stores information that DD Client needs to communicate with the Data Director, such as access information, logins and passwords, table structure in different databases etc. The scheduler database needs to contain complete information required to replicate data from source to destination database. The scheduler database also contains data that describes the structure of the databases with which communication needs to be done. Based on this data, information to the DD Client can be provided to send requests to the Data Director. This simplifies the generation of requests. The requisite is to fill in the required information in the scheduler database and run scheduler job in order to generate requests with the DD Client.

In most of the cases, data would be transferred from multiple tables at a time. This data transfer would be configured for scheduled replication. This is done by running scheduler to start the transfers, hence named as scheduler database.

Microsoft Dynamics NAV Database Server must be running to enable an access to the scheduler database for the Data Director. The scheduler database must contain all the application objects needed by the Data Director.

(15)

3.2 Log Database

The Log Database is a database containing information of to-do of Data Director. Log database consists of two tables - IncomingMessages and OutgoingMessages. These tables are stored in a database accessible by the Data Director.

The IncomingMessages table contains all messages or requests sent to the Data Director in question. The information whether the data is to be sent or to receive is logged into the IncomingMessages table of log database.

For example, a request from the DD Client for the transfer of data from the Customer table in database A to the Customer table in database B will be written in the IncomingMessages table. When the data is read from database A, the Data Director updates the IncomingMessages table stating that this part of the transfer is complete.

The OutgoingMessages table contains the status of the outbound part of the transfer. Once the incoming part of the transfer is complete (the package is generated), the Data Director can start working on the outgoing part, which is to forward the data from database A to another Data Director or to write data into database B. When the data package is forwarded or written into the destination database, the OutgoingMessages table gets updated stating that the forward or write is complete.

Once the DD service receives a data package from another DD service, it updates the IncomingMessages table and after receiving the data package, it starts writing the data into the destination database for which DD service will update the OutgoingMessages table.

Under most circumstances, the incoming/outgoing tables are stored in the scheduler database. But the system can also be set up in such a way that those two entities are stored in different databases. When the incoming/outgoing tables are stored in a separate database, it is referred as a log database. It is important to note that database A, scheduler database, and log database can all be referred to the same database or can just be separate databases. The default settings of a DD service uses Microsoft

Access database as log database. In a production environment, it is suggested that a Microsoft

Dynamics NAV database is used as a log database which can be configured by defining the system connection of DD service as the connection string to connect with a Microsoft Dynamics NAV database.

(16)

3.3 Package Flow

When a Scheduler Job is run, a DD Client component (TransAutomClient.ocx) is used to instruct the Data Director to create a package by reading particular records from the source database. This package is registered in the table IncomingMessages in the log database. The DD Client also attaches a receiver list to the package, and the list is registered in the OutgoingMessages table.

Following is a step by step explanation of Package Flow. For illustration purposes this process shows the Replication of a job having Job ID as Item. (Purpose – replication of Item Master from head office to stores)

1. The scheduler at head office detects the job with ITEM as the job ID, which is due and runs it to initiate the DD Client to replicate the data.

2. The job is configured to replicate several tables, some by actions and some by normal with replication counter. The DD Client is used to inform the Data Directors which records from these tables should be added to packages. The data is divided into several packages, one for delete package and one for update package as per distribution of By Action jobs and one for all Normal jobs. If, for example, no delete actions are processed, no delete package is created.

PackageNo Type Description

101 Normal-Update Includes filter to use to retrieve records from source 102 Action-Del Includes primary key list of records to delete on destination 103 Action-Update Includes primary key list of records to read from source

TABLE 3.3 – 1: PACKAGE FLOW

3. The DD Client informs the scheduler of the package number assigned to each package. The scheduler registers the number to the relevant scheduler log entry. In this case it will register First Package = 101 and Last Package = 103.

4. A client session on the Data Director server (the one that received the commands from the DD client) creates a query package for each package and registers it to IncomingMessages, one for each package. The receiver list for each package is added to OutgoingMessages, usually many receivers for each package. In fact it is not written into the IncomingMessages and OutgoingMessages tables right away, but added to a temporary queue where it waits for the server session to pick it up. Temporary queue is a file which is stored in the hard disk and not in database.

5. The client session triggers its system session just before the client session exits.

6. The system session wakes up and pops up all messages from the temporary queues and registers them into the Incoming/OutgoingMessages tables. If the system session is busy, it will turn to this after finishing its existing tasks.

Following are the entries in IncomingMessages table before processing the packet

PackageNo JobID Status RemotePkg ServerMsg

101 ITEM Received 0 23 records affected

(17)

RemotePkg is 0 because the sender is the scheduler and not another Data Director. If the data package is created by a DD service, RemotePkg is 0. If it is received from another DD service, RemotePkg consist the package no. being given by the source DD Service.

Below are the entries in OutgoingMessages table at head office before the packages are processed. Here, RemotePkg is 0 because it has not been forwarded to another Data Director yet.

PackageNo Receiver JobID Status RemotePkg

101 DD_S01 ITEM Processing 0 101 DD_S02 ITEM Processing 0 102 DD_S01 ITEM Processing 0 102 DD_S02 ITEM Processing 0 103 DD_S01 ITEM Processing 0 103 DD_S02 ITEM Processing 0

TABLE 3.3 – 3: PACKAGE FLOW DD_S01 & DD_S02 are the DD services for store 1 and store 2.

7. For each IncomingMessages entry, the query package is processed. The result is written in the data packages and the status is changed to Processed. The relevant OutgoingMessages entries are marked as Waiting when the data package is ready.

8. For each OutgoingMessages entry with the status Waiting, the Data Director compares the Receiver name to its own name and further steps depend on this comparison. If the receiver is another Data Director, the status is set as To Forward and the entry waits to be forwarded to the other Data Director. If the receiver's name is its own name, the Data Director itself is responsible for updating the database, and steps 9 to 13 are skipped.

9. For each OutgoingMessages entry with the status To Forward, the package is forwarded to the relevant Data Director. The receiving Data Director returns with the local package number, which is stored in the RemotePkg field at the sender side and the outgoing entry is marked as Forwarded.

PackageNo Receiver JobID Status RemotePkg

101 DD_S01 ITEM Forwarded 10 101 DD_S02 ITEM Forwarded 55 102 DD_S01 ITEM Forwarded 11 102 DD_S02 ITEM Forwarded 56 103 DD_S01 ITEM Forwarded 12 103 DD_S02 ITEM Forwarded 57

Above is an example of OutgoingMessages entries at head office after all packages have been forwarded to all receivers.

10. On the receiver side, the same procedure follows as the one that retrieves the commands from the scheduler at head office which takes care of retrieving the data package. The package is registered into a temporary queue and the system session is triggered.

(18)

11. The system session picks up all registered packages from the queue, stores them in the message tables and goes through the entries.

12. On the incoming part, there is nothing to process since these are data packages coming from another Data Director. Therefore, the status is set to Processed and the OutgoingMessages are marked as

Waiting.

PackageNo JobID Status RemotePkg ServerMsg

10 ITEM Processed 101 Ready

Example of IncomingMessages on the Data Director at store 1. The Data Director Service name is DD_S01.

13. Now the destination Data Director is in same position as the head office was in step 8. It could possibly forward the data package to the third Data Director. It is unusual to have a setup of the Data Director like this but possible for special cases.

14. The system session goes through all OutgoingMessages and processes all Waiting packages. The data packages are imported to destination databases and delete packages will cause the relevant records to be deleted from the destination databases and the outgoing entries are marked as Done.

PackageNo Receiver JobId Status RemotePkg

10 DD_S01 ITEM Done 0

TABLE 3.3 – 6: PACKAGE FLOW Example of OutgoingMessages at the Data Director in store 1.

(19)

3.4 Data Distribution

The built in Data Distribution mechanism in LS Retail controls the way data is distributed from the head office to the stores or from stores to POS databases. For example, the data distribution of items can be defined in such a manner that they are available only in certain stores. It is necessary to set up data distribution if different data needs to be replicated to different stores.

The data distribution should be one of the first configurations to be set up while setting up a new system. The data distribution must be set up before users start entering data into the system. Data entered before the data distribution set up may or may not be distributed correctly. Using data distribution, the data is only sent to distribution locations that should receive it and not to any other distribution locations and shortens the transmitting time of data by sending only the data that the destination distribution location should receive. This can have an impact on costs related to the transmission of data.

Data distribution setups can vary between different organizations. A chain of supermarkets may need a full setup for all tables used in the system whereas a small single location store may need a simpler setup, since it does not have to distribute data to other stores.

(20)

3.5 Job Scheduling

In LS Retail, data is replicated using jobs. Jobs can be scheduled to run at a specified date and time. On running the Scheduler Server, it will process scheduler jobs according to the specified given schedule. The scheduler checks the Next Check Date and Next Check Time fields and processes the job with the lowest date and time, even if the date and time has passed. In order to run a specific scheduler job, priorities can be assigned to scheduler job to ensure that it may run ahead of other scheduler jobs by the scheduler server.

3.6 Summary

This section explained the way in which Data Director works, explains the Scheduler and Log Databases, Data Director Package Flow and the introduction of Data Distribution and Job Scheduling. Following are the topics that were covered in this section:

 How Data Director Works  Scheduler Database

 Log Database – Incoming Messages and Outgoing Messages Tables

 Package Flow in LS Retail – Package Creation and Package processing sequence.

 About Data Distribution and Job Scheduling – Introduction about Data Distribution and Job Scheduling.

(21)

4 Setting up Data Director

4.1 Prerequisites

Below are a few prerequisites required for setting up the DD on Microsoft Dynamics NAV.

4.1.1 General

 Basic understanding of the TCP/IP networking protocol is required. Knowledge to assign an IP address, preferably using a DNS server or the local hosts file, and to assign names to port in

services file is required.

 Knowledge to work with Microsoft Windows Services and view events from the Event Log is required.

 Working knowledge of Microsoft Dynamics NAV is required. The setup of the DD requires an installation of Microsoft Dynamics NAV database server as well.

 Microsoft Dynamics NAV License file (.flf) is required which has the permissions to access the DD application objects within Dynamics NAV and the CFront API.

 Necessary permissions to install programs and to start and stop services on the computer running the DD service are required.

 The DD comes with a demo DD license file (.lic). This license file restricts the usage of the DD but can be used for testing and demonstration purposes. The demo license allows replication to one subnet. In production environment where data should be replicated to more than one subnet, demo license should be replaced with client DD license. Same can be procured from LS Retail on request.

4.1.2 Hardware

 At least 32 MB of available RAM. The base process uses 5 - 30 MB of RAM for normal operation. The amount of RAM required depends on the number of plugins DD uses.

 50 MB disk space for the base application and plugins. This is the absolute minimum space required. The additional disk space required when moving data depends on the amount of data and how frequently it is moved. There should be at least 500 MB free space on hard drive for the temporary data generated by the DD.

 A Pentium III class processor or better. The DD is a CPU intensive application, especially when moving data on a LAN, so that a faster processor usually means improved performance. In general, faster the computer runs the DD, better it will perform.

4.1.3 Software

Supported Operating Environments

 MS Windows XP Professional, 32 bit  MS Windows 2003 Server, 32 bit  MS Windows 2008 Server, 32 bit  MS Vista, 32 bit

Supported Databases

 MS Dynamics NAV (1.30, 2.01, 2.50, 2.60, 2.65, 3.01, 3.10, 3.60, 3.70, 4.00, 4.01, 4.02, 4.03, 5.00, 5.01, 6.00)

Note:

The 5.00 version of the cfront has a known bug where it does not release database connections properly and accumulate temp files causing it to eventually hang. Use version 5.01 of the cfront instead.

 MS SQL Server 2000, 2005, 2008  MS SQL Express

(22)

The DD needs 1-3 client sessions in the database it is running from for type of activity it performs. The number of client sessions depends and may vary on the functionality used. Since the DD will be connected to one or more databases during the operation, ensure that those databases have enough free sessions to allow the DD to connect to them.

The databases where the DD should connect to, need to be accessible using TCP/IP.

4.1.4 Security Considerations

Most data communication tools like DD need access to databases in order to move data between them. For security purposes, DD access to the database tables should be restricted only to tables it needs to read or write into. This is important because, unlike regular database users, the DD can effectively access any table in a database, as it is not restricted to viewing data via a graphical user interface. By choosing not to restrict the DD access to a database, there is a risk that users get access to data which they should not have access to.

Most database systems allow database administrators to set up user‟s access permissions relatively easy. It is strongly recommended to spend some time in specifying access permissions for the user account that the DD will use to access database. For example, the Microsoft Dynamics NAV security system provides a powerful feature that limits a user‟s access to database tables only, making the user account useless to regular users since they do not have access to the database‟s graphic user interface. Similar features can be found in other database systems as well. If this feature is available, it should preferably be used for all user accounts which the DD uses.

The DD comes with a password protection mechanism that requires the user to supply the correct password before the DD accepts any input from that user. It is strongly recommended that this feature is enabled.

4.2 Installing the Data Director

The installation of the DD is divided into following steps: 1. Installing the Microsoft Dynamics Application Objects 2. Installing the Microsoft Dynamic NAV database 3. Creating the NAV user accounts

4. Installing the DD

5. Configuring the DD service 6. Configuring the NAV plugin

4.2.1 Installing the Microsoft Dynamics Application Objects

This first step requires the user to have access to a Microsoft Dynamics NAV database running on a server. The server can be either a native server or a server using an MS SQL server as the backend. This database will become the Scheduler and Log database. The Scheduler database contains the settings for the Data Director to run properly.

Note 1 – This step can be skipped if you plan to use a database containing LS Retail as your

Scheduler database. LS Retail already contains the application objects required in order to run the Data Director. However, you need to make sure that the version of the application objects in LS Retail matches your version of the Data Director. If not, see the section below: Upgrading Scheduler Objects in an Existing System

Note 2 – This step can be skipped if you already have a Scheduler database on your network. If you

need only the Log Database, you can import the NFMsgTables.fob file from the Data Director\Files subdirectory once the Data Director installation is completed. You can also use the default MS Access database as your Log Database and skip the import altogether.

(23)

The installation is done as follows:

1. Connect to the Microsoft Dynamics NAV database. 2. Open the Object Designer and select File, Import.

3. Locate the file named scheduler-objects.fob on the Data Director Installation CD. 4. Click Open to import the objects.

The application objects are imported to the database by now. You need to make some changes to the database in order to make the newly imported objects available from main menu. If not, you can always run form 99001823 (Scheduler Menu) to get access to the Data Director menu.

4.2.2 Upgrading Scheduler Objects in an Existing System

The latest version of LS Retail should include the latest version of the Data Director objects but when new versions of the Data Director are released, new objects are usually included in the released package. Generally, there are no critical changes and it is sufficient to wait for the next service pack release of LS Retail.

The Data Director packages include a standalone database which includes only the Scheduler, the Replicator and the Data Director as parts of the LS Retail system. This database includes all objects required to run the Scheduler and the Data Director. Most of the objects are identical to the ones in the retail system and can be imported into the retail system without conflicts. But some objects depend on other functionalities in the retail system and therefore, there is a need to remove those objects in the standalone database to compile the standalone one. These objects can be identified with the CONFLICT mark in the version list.

To upgrade an existing system with a new version of Scheduler objects, follow the below steps: 1. Make a backup of all objects in the existing system.

2. Check the latest version of the scheduler objects and note down the date. Filter on *SCH* in the version list and find the latest version number.

3. Mark the objects to be exported from the standalone database.

4. Open the standalone database and mark all objects from a newer version. Note down which objects are marked as CONFLICT and unmark them.

5. Import the „non conflict‟ objects into the system. Select the marked objects, export them to a fob file and import the file into the system.

6. Manually upgrade the „conflict‟ objects. In the standalone database, look into the documentation part of each „conflict‟ object and check which changes are made after the release of the latest Scheduler version in the existing system. Modify the object in the existing system manually. Usually these are small changes, for example a new menu item, new field or change in the code. If it is changed in the code, the change should be marked.

Note

Do not upgrade ‘non conflict’ objects without upgrading the ‘conflict’ ones.

4.2.3 Installing the Microsoft Dynamics NAV database

Data Director requires a database which can be used as scheduler and log database which can be same as transactional database or a separate database.

This step requires access to a Microsoft Dynamics NAV database running on a server. The server can be either Microsoft Dynamics NAV native server or an MS SQL server. This database will become scheduler and log database. The Scheduler database contains the settings of the Data Director to run properly. The LS Retail database can be used as scheduler database and log database which already contains the application objects required in order to run the Data Director. Default installation of Data Director and configuration of DD Service uses Microsoft Office Access database as log database.

(24)

4.2.4 Creating the NAV user accounts

One or more NAV user accounts need to be created for the DD depending on the configuration. 1. System account

The system account is used to access the log database. By default, the DD uses a Microsoft Access database to keep track of the tasks it is working on. A user account does not need to be created for the log database, if the Access database is selected for use. If the plan is to use a NAV database as log database, you need to create a Microsoft Dynamics NAV user account that allows the DD to access the log tables. This user only needs access to two tables in the database:

 Table 99001599 – IncomingMessages  Table 99001600 – OutgoingMessages

The DD must have permission to read and write into these tables. The connection string for log database while creating DD service should contain user and password, having permission for IncomingMessages and OutgoingMessages tables.

2. Data access accounts

The data access accounts are used to access the data in the source and destination databases. These accounts are used by the DD to read from source tables and write into destination tables. The access permissions of these accounts need to be limited to the tables that the DD will read from and write into. It is also a good practice to limit the delete permissions to the tables where deletions are needed. The connection string for source and destination database defined at distribution location card should use this user id and password.

Accounts with super-user privileges are often used in test environments, but should never be used in a production environment.

4.2.5 Installing the Data Director

To install the Data Director, the DD installation file needs to be run, usually called LSRetailDDx.x.x.exe, where x.x.x is the version number. The installation is straightforward; just follow the instructions in the dialogs. Once installed, everything required for DD to replicate between Microsoft Dynamics NAV and MS SQL Server databases is available since all the needed plugins are included by default.

Note

In Microsoft Vista, the user installing/configuring DD needs to have local administrative rights.

Locate the DDServer_XX.exe file in the Setup directory on the Data Director installation CD. Start the application. Click on Yes to start the installation. The following Setup Wizard window will appear:

(25)

FIGURE 4.2 – 1: DATA DIRECTOR INSTALLATION Click on Next to continue. The following screen will appear:

FIGURE 4.2 – 2: DATA DIRECTOR INSTALLATION

Select the directory where you want to install the Data Director. The default path is C:\Program Files\LS

Retail\Data Director, where user can change the path. Click on Next and the following screen will appear:

FIGURE 4.2 – 3: DATA DIRECTOR INSTALLATION Click on Close to complete the installation.

(26)

4.2.6 Configuring the Data Director Service

Once the DD is installed, one or more servers need to be configured:

1. Open the Data Director Setting tool located in the Data Director Start menu. It is a wizard-like interface, used to manage everything concerning the DD servers, like adding or removing servers, starting and stopping them and configuring their parameters.

When the tool is run, the window Register Data Director Service appears, see below:

FIGURE 4.2 – 4: DATA DIRECTOR SERVICE CONFIGURATION

1. In the field Server Name, enter the name of the new server which by default contains the computer name. Any name can be assigned to the server. Just make sure that the name used will resolve to the computer‟s IP number by adding it to DNS server or the hosts file. It is recommended that computer name should not be used as DD server name and the name should not conflict with any other windows service running on that machine.

2. Click Add to add the server to the list called All Servers. To remove the server, select it from the All Servers list and then click the Remove.

To Start a Server

1. Select the server from the server list and then click on Start.

To Stop a Server

1. Select the server from the server list and then click on Stop.

DD Service should not be started while defining the service. It should be started after defining all the parameters required for that service.

Licenses

Data Director comes with a test license that can be used in a test environment, but once the DD is deployed at the production site, it is recommended to have a valid customer license that includes the number of subnets the site will use.

(27)

Obtaining a Customer License

A valid license key for DD can be obtained by registering an incident at LS Retail support (LS Partner portal). The information that must be sent is: Customer Name, Microsoft Dynamics NAV License number as well as number of stores.

(28)

To Load a New License

1. On Register Data Director Service, a new license can be loaded by clicking the Load

License. If the license loaded is valid, it will show DD license valid message below the Load License.

2. Restart the DD service to activate the new license by selecting DD service from service group.

If a simple setup is run using just one DD server, there is no need to configure anything else and you can start the service right away by clicking Start. This will start the DD running as an NT service which will restart automatically when the computer starts.

Note

The Start and Stop buttons will reflect the current state the selected server is in by disabling or enabling the available action.

(29)

4.2.7 Configuring the NAV Plugin

In the same wizard, Plugins/Controls group are available in the upper right corner having three buttons, one for the Dynamics NAV plugin, one for the MS SQL Server plugin and one for the Client Controls. First two buttons will be enabled or disabled according to plugins included in the license and are used to configure the respective plugin. For example, if the license only contains the NAV plugin, the Dynamics NAV button will be enabled and the MS SQL Server button as disabled. Since the test license includes all available plugins, both buttons are enabled. The Client Controls is always enabled.

Dynamics NAV Plugin

On click of Dynamics NAV, the screen with the options to configure NAV plugin will be shown as:

FIGURE 4.2 – 6: MICROSOFT DYNAMICS NAV PLUGIN SETTING

To Load NAV License

1. Click on Load NAV License to load a new NAV license which makes it available for the DD to use. Once the license is loaded here, the Administration is used in the NAV scheduler database to set up the distribution of the license to other DDs.

2. A license can be installed manually by putting it in the cfront plugin root folder and the version folder that matches the version of NAV.

(30)

Other Settings Parameters at NAV Plugin

Following are the other parameters for the FinPlugin, that are used to access Microsoft Dynamics NAV databases (both native and SQL).

Property Description

Codeunit Permission This specifies the codeunit in NAV from which the plugin can inherit permissions. This can be important in cases where there is an end-user license and a need to replicate data into a write-protected table such as the Item Ledger Entry. Codeunit 99001483 contains permissions to write into most of the write protected tables. If left as 0, the plugin cannot write into protected tables. Multilang. Workaround It provides a fix for an error in NAV that occurs

when the source and destination databases are not using the same language.

Clear Illegal SQL Dates It corrects illegal dates so it can be written into NAV date field. It only affects NAV using SQL Server as the database.

Ignore Extra Fields If this is checked, packages will not cause an error even if some included fields do not exist in the destination table. They are just ignored.

Fix Decimal Precision This fixes Decimal Rounding where decimal values

may be replicated with large number of decimal places. Type in the number of decimal values you want to replicate in the Round To Decimal field. Load NAV License Enables to load a new NAV license for the DDs

use.

TABLE 4.2 -1 MICROSOFT DYNAMICS NAV PLUGIN SETTING

Client Controls

The Client Controls button will open up the screen that contains the Register and Unregister buttons which will register or unregister the ActiveX client controls that the NAV client uses to talk to the DD.

(31)

Register Client Controls

1. Click Register to register the ActiveX client control.

Unregister Client Controls

1. Click Unregister to unregister the ActiveX client control.

Normally, there will be no need to manually register these controls as it should be done at installation, but under certain circumstances while upgrading the DD, the old clients might be locked when the upgrade took place and the registration failed. If an error about missing components is reported while using the scheduler, try to unregister and then re-register the controls using these buttons to see if it fixes the problem.

To Debug Client Control Enable Log Mode

1. Log Mode can be enabled by placing a check mark in the Log Mode box. This will enable client controls to maintain a log of what they are doing which can be useful when

troubleshooting. If this option is enabled, please make sure that the Log Dir directory exists. 2. DD creates three log files and will rotate through them when the Max. Lines limit has been

reached for each log. On startup, the DD will make a copy of the old logfiles by appending .old to their names. This is useful if the DD service has been set to automatically restart on failure – the failure will then be in the old log files.

To Specify Log Level

Log Level specifies the detail level of debug information included in the Log file.Checking Error & Main usually is enough for basic debugging, but more details might be required in some cases.

1. Log Level specifies the detail level of debug information included in the Log file. Usually the Error and Main marks are enough but greater log detail might be required in some cases.

 Error: Logs all errors reported from DD  Main: Show main functionality in DD

 Actions: Shows detailed actions performed within DD

 Detail: Show very detailed information about what DD is doing  Fields: Show database field values and information.

 Functions: Extreme Debugging for programmers‟ use only.

Note

Log level Field & Functions will produce a lot of information in a busy system and should not be left on for long. If a log file exists, new entries will be appended to it.

Since there are various different client components used, like DD Client and Transaction Client, each component will produce its own log file, named after the component name.

Once all the settings at Register Data Director Settings are done, click Next to move forward with settings of DD Service.

To Define Data Director Mode

1. The Data Director Mode drop down list configures this service instance to run as a Data Director, a Forwarder (2nd Stage DD) or as a Transaction Server.

(32)

When the DD is running as a Transaction Server, the number in the Transaction Srv. Port field is used for all communications.

If running as a Forwarder (2nd Stage DD), the service will only forward packages to their destinations and will not be able to receive any packages. This means that at least one service must be running in DD mode which will be working as package owner and one or more running in Forwarder mode. The forwarder can be on a separate computer but make sure that the log database where the incoming and outgoing messages tables are stored is accessible from there. The name assigned to the forwarder is the name used in the Scheduler to indicate which forwarder should handle this location and should also be defined in the DNS or hosts files. This name is to be given in the Forwarder field of the Distribution Location card.

To Define Server Application Path

1. The Server Program field displays the path to the executable of the service application. This needs to be changed very rarely.

To Define Ports

1. DD Port (Port used by Data Director Service), the Telnet Port (Port used by telnet) and the Monitor Port (Port used by Data Director monitor explained later) for the DD service can be defined for the DD Service shown in Server Name. The values shown below are the default values.

Data Director Port 16750

Transaction Srv. Port 16752

Telnet Port 23

Monitor Port 16751

(33)

Note

If more than one instances of the service need to be run on the same computer, these values need to be changed so that it should not conflict with other DD services. Please note that all the ports including the telnet and monitor ports should be changed.

If Telnet server on the computer is run, then another port should be assigned to DD telnet interface to avoid conflicts.

On the click of Next, Data Director Specific Properties window will be opened where a number of properties need to be specified that affect the behavior of the DD. The following table describes these properties in more detail.

Server name Displays the name of the service being configured

System Connection Specifies the connection string the DD uses to connect to the Log Database. By default, the DD installs a Microsoft Office Access database to use as a Log Database. Dynamics NAV or MS SQL Server can also be used but then needs to specify another connection string.

Working Directory The path to the directory that the DD uses to store temporary (package) files.

Incoming Table The name of the table DD uses for incoming messages. Outgoing Table The name of the table DD uses for outgoing messages.

Days Message Exist Specifies the number of days processed incoming/outgoing messages should be kept. 0 means forever.

(34)

Timer Interval The DD checks for packages that need to be reprocessed (communication errors) at predefined intervals. The length of that interval can be specified here.

Limit Job Process (cnt) Limits the number of job records the DD will process per connection (to databases or remote DDs). Using this feature can be beneficial in systems experiencing heavy load to prevent the DD from going into a live lock. If the first job record destined to a connection has an error, the rest of the packages will be skipped for this run and the next connection will be processed. This enables the DD to handle virtually an unlimited number of waiting packages (space allowing) in linear time.

As a guideline, the number should be set low when the average package size is high and be set higher when the average package size is low. Set to 0 to disable (not suggested).

TABLE 4.2 -3 DATA DIRECTOR SERVICE CONFIGURATION

Data Director Connection Strings

Below are several examples of connection strings that can be used in setup. In most cases, it is just a matter of copying the string listed below and changing the database name, user and password.

The DD connection string consists of three sections: database specific connection string, plugin ID and a plugin-specific string. These three parts are separated by the | symbol. Each section is configured with parameters that can be different for each plugin and each database.

Use different database connection strings for different database servers. Syntax: <database connection string>|<plugin id>|<plugin specific string>

Following are few examples of connection strings. These are the strings that have been used in the past for each plugin.

Microsoft SQL Server

Provider=SQLNCLI;User ID=dd;Password=ddpwd;Initial Catalog=demo-data;Data Source=ho-dd;|ms|none

driver={SQL Server};server=aaron;database=DemoData;|ms|none;

Provider=SQLOLEDB.1;User ID=dd;Password=ddpwd;Initial Catalog=demo-data;Data Source=ho-dd;|ms|none

Microsoft SQL Server with AdoPlugin

Provider=SQLOLEDB.1;User ID=dd;Password=ddpwd;Initial Catalog=demo-data;Data Source=ho-dd;|ado|none

Microsoft Access with AdoPlugin

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files\LS Retail\Data Director\data\msg.mdb;|ado|none

Microsoft Dynamics NAV native server

company=the-company;server=db.ho;user=dd;passwd=ddpwd;|fin|ndbcn@400 company=the-company;server=db.ho:12025;user=dd;|fin|ndbcn@501

Navision 3.56 server

company=Demo;server=1;user=dd;|nav|none

On click of Next, Data Director & Transaction Server Properties window will be shown with following properties

(35)

Hold Connections The number of connections to the source database that the DD should reserve.

Idle Conn. Time Specifies the idle timeout for the reserved connections in Hold Connections. Maximum Sessions Specifies the maximum number of simultaneous connections that the

Transaction server can use.

Session Timeout Specifies the timeout for queries involving the Transaction server. Thread timeout Specifies the timeout for threads used in connecting to remote locations. Max. Forw. Threads Specifies the maximum threads used when the DD is in Forwarding mode. Max. Hop Counter Specifies the maximum number of hops a single package can take between

DDs. This prevents endless loops if the DD names are configured wrong. Socket Timeout Specifies how long the DD will wait for the network to finish a particular send

or receive operation. Socket Timeout = 0 will disable this check. Note: This

should always be set lower than the Thread Timeout to prevent the DD from killing itself if it is just waiting for the network.

Password The password required to get an access to the DD service remotely. Use Processor x This is the CPU number used by DD Service.

Allow File Trans When this is checked, the DD allows transfer of files using the MonitorClient from Dynamics NAV.

TABLE 4.2 -4 DATA DIRECTOR SERVICE CONFIGURATION

On click of Next, Server Debugging Properties window will be shown where various debug options for the DD service can be set.

(36)

Keep Package Files DD will not delete the temporary files in the work folder. Do not leave this on for long time otherwise you will run out of disk space and prevent the DD from operating correctly.

Exception Dump Creates Memory dump file if DD crashes.

Log/Dump Dir. Folder where the log files will be saved in. Make sure the folder exists.

Max Lines / Logfile DD creates three log files and will rotate through them when the Max Lines has been reached for each log. On startup the DD will make a copy of the old log files by appending .old to their names. This is useful if the DD service has been set to automatically restart on failure – the failure will then be in the old log files.

Log Level Log Level specifies the detail level of debug information included in the Log file. Usually the Error and Main marks are enough but greater log detail might be required in some cases.

Error: Logs all errors reported from DD Main: Show main functionality in DD

Actions: Shows detailed actions performed within DD

Detail: Show very detailed information about what DD is doing Fields: Show database field values and information.

Functions: Extreme Debugging for programmers use only.

(37)

Note

These checks should be used only for a short term for debugging purposes and do not leave these options On for long in a production environment, especially the Keep Package Files option. Otherwise disk space may run out and prevent the DD from operating correctly. The Log/Dump Dir should be a directory that exists and the log mode works in the same way as the log mode for the client controls described earlier. This is the last configuration screen of the Data Director Service configuration and can be closed by either clicking Finish or OK.

4.3 Managing the Data Director Service

After installing the Data Director Service successfully, following are the steps in order to start the service: Right-click on My Computer. Select Manage. The Computer Management window appears.

FIGURE 4.3 – 1: COMPUTER MANAGEMENT

Expand the Services and Applications branch. Select Services. Locate the LS Data Director entry. The window will look like this:

(38)

You can now start or stop the service by clicking the relevant buttons on the toolbar or by right-clicking the service entry. Start the service. Once the service is started, you must check whether it started successfully. Click on the System Tools branch and expand the Event Viewer. Select the Application branch. The window will look similar to this:

FIGURE 4.3 – 3: COMPUTER MANAGEMENT EVENT VIEWER

The event Viewer window will have an entry created by the Data Director. Double-click on the entry to view its contents. The window will look something like this:

FIGURE 4.3 – 4: COMPUTER MANAGEMENT EVENT VIEWER PROPERTIES

The service has already started. All subsequent messages from the Data Director service will be logged in the Event Log.

(39)

There is also a faster way to see whether the Data Director Service is running. Open a command prompt and enter telnet service name where service name is the name of the Data Director Service. The window will look like this:

FIGURE 4.3 – 5: COMMAND PROMPT Here you can view the Data Director‟s status and licenses by pressing Q and L.

4.4 Maintenance

Once installed, the Data Director Service requires very little maintenance. There are few things to be kept in mind in order to keep the service run smoothly.

 Make sure that there is enough free space in the Log database. A full Log database means that the Data Director is unable to process any packages.

 If the customer wants to add new granules to license, the license needs to be updated throughout the Data Director network. When the service is restarted, the new license will be read and the Data Director will get access to the new granules. You simply need to put the new Microsoft Dynamics NAV license file in the DataDirector\Plugins\FinPlugin\Incoming directory on each Data Director on the network before restarting the service. A script can be created to simplify this task. Once the service is restarted, the new license file will be used to access all Microsoft Dynamics NAV databases.

4.5 Upgrading Data Director

To upgrade Data Director from 2.23 or an older version of the DD, please follow the steps below: 1. Stop all running DD servers

2. Take note of any configuration changes made to the DD servers 3. Uninstall the DD server and all plugins

4. Reboot the machine

5. Stop all NAV clients, including NAS servers 6. Remove the directory where the DD was installed 7. Install the new DD version (do not uncheck any options) 8. Configure your DD servers according to the notes from step 2.

LSRetailDataDirector UserGuide

Data Director - User Guide

Contents

1

Job Scheduling / Replication

1.1

About Replication

Overview

1.2

LS Retail Architecture

1.3

Methods of Replication

1.3.1 Adding Replication Counter to Tables

1.3.2 Adding Actions Generation Code to Tables

1.4

Summary

2

About Data Director

2.1

Modes of Data Director

2.2

Concepts in Data Director

2.3

Summary

3

How Data Director Works

3.1

Scheduler Database

3.2

Log Database

3.3

Package Flow

3.4

Data Distribution

3.5

Job Scheduling

3.6

Summary

4

Setting up Data Director

4.1

Prerequisites

4.1.1 General

4.1.2 Hardware

4.1.3 Software

4.1.4 Security Considerations

4.2

Installing the Data Director

4.2.1 Installing the Microsoft Dynamics Application Objects

4.2.2 Upgrading Scheduler Objects in an Existing System

4.2.3 Installing the Microsoft Dynamics NAV database

4.2.4 Creating the NAV user accounts

4.2.5 Installing the Data Director

4.2.6 Configuring the Data Director Service

4.2.7 Configuring the NAV Plugin

4.3

Managing the Data Director Service

4.4

Maintenance

4.5

Upgrading Data Director