Company: Link Technologies
Case No: L12273. Project: 12.43: LinkSOFT Version 12.43
Logged By: Sanjay (Link Technologies) on 07 Jun 2021 10:10AM
Priority: High
Product: Framework
Group: New Feature
Time Taken: 119.00 (Weight: 160.00)
Version: 12.43.0706
Assigned To: Rashna (Edge Business Solutions)
Circulation: Development, Rashna, Sanjay
Resolve By: Tuesday, 06 July 2021 05:30 PM [1045 days since logged date]
Status: Closed
Subject: LinkSOFT WEB API to handle data transmission between two LinkSOFT Databases instead of using "Linked Servers"
Summary:    

LinkSOFT API development to handle data transmission between two LinkSOFT Databases. This is required to eliminate the following issues:

  1. Replication uses Linked Servers. We have a lot better control via an API and can throttle and add size limits to handle bandwidth and traceability.
  2. Opening SQL server PORT 1433 on a LAN environment poses some risk. Some customers require replication over the internet and are opening SQL server port despite being advised to use a VPN.
  3. Some data for TNA needs to be sent from local installations to the cloud.

High-level design:

  1. Each transmission should be a closed-loop transmission. i.e. there should have a known origin and each batch must be tracked from Initiation, transmission to closure.
  2. Create a function that allocates a unique number to each transmission. The unique ID should be readable, suggest YYMMDD-HHMMSSS-{3 digit code}. Let's name this "Transmission ID", referenced as "TID", to denote a readable code user can use to track each transmission.
  3. This code should be referenced in the status of each transmission. e.g. In TNA, a file being transmitted should have Statuses: "Queued {TID}", "Transmitted {TID}" and at the receiving end, "Received {TID}", "Processed {TID}", "Closed {TID}"
Audit Notes:Edited by sanjay on 06/07/21 13:40. Edited by sanjay on 25/06/21 10:04. Edited by sanjay on 24/06/21 10:29. Edited by sanjay on 07/06/21 10:10. 
18 Jun 202105:30PM Comment 1 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 18-06-2021 09:02 PM Time Taken: 88.00
PART A - Development work for this case has been completed.

1. The change will be available in version: 12.43.0618

2. The following changes were made(Include Database object names, Program classes, and any other relevant information):

  1. Removed Linked servers from LinkSOFT
  2. Added LinkSOFT.API in the integration system
    1. The API security is based on Security Token defined and managed in menu "Global Administration ~> Security Token"
    2. Batch size is used to define how much data is transmitted in each API call. The larger the number, the largest the Payload, however, larger payloads require faster connectivity between TRANSMITTING and RECEIVING servers
    3. An API Licence is required in each server, including the Head Office Server. We have combined REPL and API licences so if you have a REPL licence, you do not need API Licence
    4. Payload is capped at 20MB, Batch size is automatically reduced to meet this criterion
    5. If a single record payload exceeds 20MB, this will be transmitted, however, the limit will then be managed by the WEB.CONFIG Request limits on the receiving server. Keep in mind, Server Memory Limits apply, therefore Large Payloads can raise Memory issues.
  3. All errors are logged in the event log
  4. A new BATCH ID has been introduced to manage the closed Loop of transmissions.
  5. A new many has been added named "Replication ~> Queue Monitor". This will show the records currently in the transmission queue
  6. Menu "Global Administration ~> Process Setup ~> Detail View" will contain the payload being delivered. The payload will contain batch numbers that can be seen on the Transmission and Receiving ends
  7. Changed "Replication ~> Compare Data" to use LinkSOFT.API
    1. Introduced Batch Numbers to Compare Data functions
    2. Batch Numbers can be seen on both Transmission and Receiving ends to tracebility
  8. Removed STANDALONE from Connection Types. Standalone is now Equivalent to Standard Parent/Child configuration. This was done in an effort to simplify replication configuration. Standalone function can be achieved using Parent/Child Settings
  9. Significant improvements to error logging and handling transmission batches
  10. Significant improvements to efficiency in replication payloads

3. Affected Areas:

  1. Replication Functions
  2. TNA Data transmission to HQ/Cloud

4. The issue was caused by:

  1. Added functionality to achieve the following:
    1. Cloud-Based Head Office Configuration allowing POS and TNA devices to talk to cloud-based head office configurations
    2. Removal of SQL Server Linked Server Replication vulnerabilities

5. Notes
6. Next Step
(Review and System Test (Developer) -> UAT (Quality) -> Documentation): UAT


21 Jun 202101:36PM Comment 2 by Sanjay (Link Technologies) Assigned To: Rashna (Edge Business Solutions) Followup Date: 21-06-2021 05:36 PM
Proceed with UAT

24 Jun 202110:29AM Comment 3 by Sanjay (Link Technologies) ETC was changed from 30/06/2021 to 30/06/2021
06 Jul 202101:40PM Comment 4 by Sanjay (Link Technologies) Assigned To: Rashna (Edge Business Solutions) Followup Date: 06-07-2021 05:30 PM Time Taken: 19.00 Notes: ETC extended from: 30/06/2021 to 06/07/2021

Added the following improvements to LinkSOFT API

  1. To prevent "Memory Exceptions" and "Out of memory" issues, we have added the following
    1. Added a Configuration "LinkSOFT.API.GeneralSettings.APIPayLoadSizeKB" to limit the API Call Batch Size "API Payload Size in Kilobytes. Used to limit the size of each package."
    2. The default for API Batch sie set to 25MB
    3. Added maximum ayload size to use "LinkSOFT.API.GeneralSettings.APIPayLoadSizeKB"
    4. Set the Batch size to default to 5000 lines
  2. Added checks to ensure that if a process fails, all other pending processes are not affected
  3. Failed processes are delayed for 10 minutes before the next retry

Recommendations:

  1. There should be one application pool per website, Websites should not share application pools
  2. Heavy processes should use the "Windows Service" to ensure the load is balanced between Wen Scheduler and Windows Scheduler

12 Jul 202108:05AM Comment 5 by Sanjay (Link Technologies) Assigned To: Rashna (Edge Business Solutions) Followup Date: 12-07-2021 12:04 PM Time Taken: 12.00
Replication System Tests completed. We will continue to work on this case in 12.44

If you have any queries regarding this support incident, please email admin@linktechnologies.com.au and include the Case No: L12273 in the subject line of all emails regarding this issue.

Document size: 8.9 KB
For call complaints, please contact the Managing Director of the company using this form