SQL Replication Quick Start Guide

After SQL Replication has been enabled on your system and the installation tasks complete, you are ready to begin replicating Eclipse data over to your Eclipse SQL Data Warehouse for use in your ICE solution (or other 3rd party SQL tools). Below are some helpful guidelines and settings to successfully replicate your Eclipse data.

Start by enabling one small file first. Confirm the SQL Table is created in SQL Server Management Studio and the data is successfully loaded. If leveraging the technical consulting assistance provided by Epicor, this is typically the point where they will hand over the reins to you to enable files that you care about.
Modify your SQL Configuration Global Settings to increase the number of Mapping Processors and Sending Processors:
By default there is 1 Mapping Processor and 1 Sending Processor. The Mapping Processor is a phantom process (DATAREP.QUEUE.SERVICE) that maps a given Eclipse file record (pulled from the DATAREP.QUEUE) into SQL statements and places them in the DATAREP.SEND.QUEUE where a Sending Processor phantom process (DATAREP.SEND.SERVICE) picks them up and sends them over to the Epicor Data Warehouse on the Windows Server.
In a typical system, the number of Mapping Processors can remain small, 1-3 processors can more than fill the DATAREP.SEND.QUEUE faster than the sending phantoms can send over to SQL Server and get a response.
The number of Sending Processors is more variable based on your SQL System hardware capacity. Sending Processors do use a Universe Phantom License (which is typically not a constrained license). 10 Sending Processors is a good starting estimate. If your SQL server has CPU capacity unused while LEDGER is replicating (the most intensive file), increasing the Sending Processors to 15, 20 or even 30 can help expedite the initial load completion.
After the initial file loads have completed, the number of Sending Processors will need to be set at a value that maintains an acceptable pace with real-time updates by users and handles large volumes of record updates when phantom process run overnight or on weekends. 10 seems a good number. Some known phantom offenders of updating lots of records:
High Crd/Avg Days to Pay (AR.PH.CREDIT.STATS) – updates every ENTITY record; with all sub-Tables enabled, ENTITY is one of the slower files to replicate.
Product Ranking (PROD.PH.RANKING) – depending on parameters, can create lots of PROD.CALC.BR records, which replicate quickly, by volume can be high.

Note: you may find it necessary to reschedule some phantoms that stress your replication to occur at a more convenient time. Alternatively, you may relieve replication stress by reducing the files (and particularly child tables) that you choose to replicate.

Changing the number of Mapping and Sending Processors while active replication is occurring (i.e. the DATAREP.SERVICE.MONITOR has more than 0 queued and 0 sending) has a theoretical chance to cause a record to replicate old data. Explanation: the mapping and sending processes use a “hash” value based off the record id to determine which thread to handle that record. As long as the number of processors is a static value that record id will always get handled by the same thread, preserving first in first out processing to ensure data integrity. However if the hash value changes and a given record happens to be in the queue twice and one thread handles the first change and a second thread handles the second change and the second thread happens to be further ahead, the later change could get replicated first and then get overwritten by the prior change. The likelihood of this actually happening is minuscule, but if you are risk averse, only change the number of processors when 0 records are replicating (or more broadly, when no user/phantom updates are occurring…initial loads only add a record id once for replication).

Enable a larger file like ENTITY or PRODUCT. Before enabling, review the “View Detail” screen from the SQL Sync Maintenance screen for the ENTITY file (as an example) and disable any Table IDs that contain information you don’t care to replicate (or won’t have any data)—this will improve the speed of the initial load and particularly the real-time replication:
Monitor the server performance on your Windows server for improvement opportunities
Dedicate 1 physical disk to the .ldf SQL server file and 1 physical disk to the .mdf SQL server file (on the same disk, access contention can occur when SQL server goes to write logs that can slow down replication)
Modify the Database Properties to increase the default auto-grow threshold from 1 MB to at least 1024MB (1 GB)
Ensure no SQL Server Maintenance Plans are running to rebuild indexes or perform other SQL Server Maintenance when high replication stress is occurring
Monitor the SQL Server Activity Log for errors.
Data errors may be a result of bad data in your Eclipse system, or may be a mapping problem. If the error record is important (i.e. recent and you care) and you can duplicate creating the error on a new record from within Eclipse, submit an SR with your example for the mapping issue to get resolved. If the data is bad (possibly from a massload gone awry by a prior employee) in Eclipse and you desire it to be corrected, contact your Account Manager to engage consulting services/custom programming assistance to resolve.
Any timeout errors could be due to an overextended SQL server. Reduce the number of Sending Processors on the Global settings screen and/or increase the timeout setting on your Epicor Data Warehouse
Duplicate Key errors may be “correct” if replication is occurring while users are modifying Eclipse data. Example. You enable the LEDGER file at 9am. At 10am, a user creates a new sales order that would get added to the DATAREP.QUEUE and could replicate over to eclipse.sales_order SQL Table. Then, at 2pm, that salesorder LEDGER ID is included in the DATAREP.QUEUE by the initial load phantom (as an INSERT command, rather than a coupled DELETE and INSERT which is common for real-time record updates). When the datawarehouse attempts to insert this record, a duplicate key error is thrown because the record already exists. This is not a problem
Watch the phantoms (Hint: in Phantom Status add Selection Criteria of Search String = “DATAREP” to limit the phantoms to those involved in SQL Replication)
DATAREP.SERVICE.MONITOR in the phantom status screen includes a count of records every 10 seconds in the DATAREP.QUEUE and DATAREP.SEND.QUEUE
DATAREP.SERVICE.MONITOR also controls the starting and stopping of the mapping and sending phantom when the settings are changed in the Global Settings screen in SQL Server Configuration. Killing any of the phantoms in the phantom status screen will be temporary as SYS.ADMIN will restart DATAREP.SERVICE.MONITOR, which will in turn restart the mapping and sending phantoms. Note: setting the mapping and/or sending processors to 0 will be treated as 1.
As each Eclipse file is enabled for replication in the SQL Server Sync Maintenance screen, a “Load” phantom (DATAREP.FILE.RECORDS.SYNC) is kicked off which selects all the records in the file and places them in the DATAREP.QUEUE for the mapping and sending processes to replicate the data over to SQL.
To keep the size of the DATAREP.QUEUE and DATAREP.SEND.QUEUE manageable for replication to process records efficiently, the load phantom will only add new records to the DATAREP.QUEUE when the DATAREP.QUEUE or the DATAREP.SEND.QUEUE is below 100,000 records. Note: the load phantom checks the record counts in those files every several seconds and will likely load more than 100,000 fresh records within the time interval…this is not something to worry about.
Don’t enable a bunch of files at once…the individual file sync times captured in the SQL Server Sync Maintenance screen will be inaccurate (eg: they will indicate it takes 1 hour to sync ABC.CODES but it only took that long because ABC.CODES records were intermingled with other files replicated along with it, so you don’t truly know how long it would take to resync ABC.CODES on its own—which admittedly is not critical information, but useful). More importantly, enabling a bunch of phantoms to select a bunch of Eclipse files at the same time can tax your Eclipse system
Large records, typically found in LEDGER can occasionally cause the DATAREP.QUEUE.SERVICE phantom status to display the LEDGER ID that is taking a long time to map. No action needs to be taken. This is an informational event that informs the phantom watcher about why a particular phantom appears to be “stuck”
Database maintenance plan to rebuild indexes
Create a Maintenance Plan in SQL Server to rebuild all indexes on the Eclipse database (and do a backup if you desire) weekly.
Real-time replication of user changes and (in greater volume) phantom process updates is accomplished by a SQL DELETE statement of the existing table records and a corresponding INSERT statement. Updating a lot of records in SQL can cause the indexes to get fragmented, which slows down the replication over time. Rebuilding the indexes de-fragments the tables and keeps replication performance optimized.
If you need assistance setting up your maintenance plan, contact your account manager about obtaining some technical consulting assistance.