Based in Derby, SGS Engineering (UK) Ltd has over 30 years of experience manufacturing and supplying high quality tools and equipment to businesses and consumers, turning over circa £30 million per annum. They’re one of the UK’s leading companies within this sector and have a great range of their own brand products alongside numerous international brands.
Because of business continuity concerns, the management at SGS decided they wanted to closely replicate their existing Magento 1 in terms of look and feel, while updating the platform in the ‘background’. PCI compliance and security were the main drivers in replatforming, and in doing so, reducing exposure for the business.
There was also a late scope change to consider the performance of their Sage 200 integration for orders and customers, from an existing flat file exchange to using a more flexible and robust API.
Magento 1 integration
The Magento 1 site synced Orders and Customers from the Magento database and into the Sage 200 database. This was done using CSV exports and some custom code on the Sage end to manipulate data into a format that Sage could then import. There were issues with how this process functioned and the order/customer sync would regularly fail and require manual intervention.
Magento 2 legacy integration
For the launch of the site, it was decided to replicate the existing Magento 1 order and customer sync as closely as possible. This was to ensure that there were fewer points of failure on the launch of the Magento 2 site. Post launch we would revisit switching over to an API sync and disable the CSV based sync.
Magento 2 API integration
The client’s Sage 200 database is provided by a third party. They provide the ability to import data into their system via a CSV. At the time of the build they were in the process of building an API collection that could be used to push and pull data to/from the Sage 200 database, so this meant the API integration would not be available for the site launch; however we were able to progress this in anticipation of adding the API integration shortly afterwards.
Using these endpoints the following functionality was scoped:
The Sage 200 API is similar to others at a high level in that all the expected endpoints exist for pushing, pulling or querying different entities in the Sage database. But at a low level it differs in its structure, i.e. the specific format of the payloads which must pass validation to be processed.
The API integration is entirely one sided in terms of pushing data into Sage. Meaning that any push requests sent to Sage 200 would not return a successful response, but instead, the response would indicate that the message had been received and queued. It is Magento’s ‘responsibility’ to periodically check if the previous request had succeeded after it was received.
The Magento integration will also handle the sending of data in the correct order to help fix issues being experienced with the CSV integration. Before an order can be imported into Sage 200, the customer for that order must already exist in the Sage database. The API integration would first query the Sage database using the customer’s email and/or Magento ID to validate if the customer exists in Sage before attempting to sync the order. If no customer exists, it will then send a request to Sage to create one. Only when the customer is verified as existing in Sage will the order request be sent.
Errors in each step of this process are recorded in the admin and sent to select email addresses to help make the client aware of any issues that do arise so they can manually intervene and promptly fix the issue.
Legacy data from existing website
The existing Magento 1 data was migrated using a customised version of the Magento migration tool. Data transforms were built to convert data that didn’t meet the validation requirements of the Magento 2 database and/or remove data that was not required or corrupted.
Nitrolift is an SGS sub-brand that contains one of their main product lines. Part way through development it was decided to focus on the main SGS brand and to pick up the development on Nitrolift post launch.
During the project the Magento Open Source installation was upgraded to a version that included the core Magento Page Builder. This had an effect on the migrated data in that it was converted into page builder html blocks. This caused line breaks and formatting to break across the site in categories and products.
A series or SQL queries were written to locate all the affected data and then update it with the required changes to fix the formatting.
Attribute primary key conflicts and auto increments
There were several attributes that had been created during the Nitrolift store development either as a result of the theme development or through 3rd party module installation. These attributes conflicted with attributes that were being migrated from the Magento 1 database.
Each attribute needed to be identified and either removed if it was plausible to do so or manually altered by moving its entity IDs outside of the range of the importing Magento 1 data. In order to avoid this issue in the future the auto increments on all attribute related tables were set to start at a range well above the highest values in the respective Magento 1 tables to ensure no primary keys would conflict.
Core Attribute Data Patches
Some of the core attributes required manual re-running of their Data Patches from the patch_list table as the Magento one migrated data undid some required database schema updates when the migration was run in full.
URL rewrite table conflicts
A series of 301 redirects in the Magento 1 core_url_rewrite table caused URL key conflicts when migrating to Magento 2. It appeared that somehow conflicts had managed to be created in the Magento 1 data despite there being validation in the admin to prevent that.
Regardless of how this occurred the url_rewrite table in Magento 2 needed to be regenerated from scratch and the 301 redirects moved to an Nginx config file to be assessed post launch.
The Magento 1 site used a module by the vendor Codisto to sync product and order data to and from eBay.
Several tables in the Magento 1 database had triggers created by the Codisto module. These triggers prevented the creation and updating of the changelog tables the data migration tool requires to perform delta (catchup/partial) migrations.
All Codisto triggers needed to be identified and exported before being removed prior to each migration.
The production site would be hosted on a series of AWS servers that separated out the required services into separate containers and provided auto-scaling. This infrastructure was to be managed by a 3rd party developer that specialised in AWS infrastructure. Absolute assisted with the configuration of these services.
EFS mounted files
The AWS server used EFS to store all the files that would persist across all web nodes and between instance reboots or creations.
On several occasions, we helped diagnose issues with the creation of the required mounts to these directories. The issues were that when an instance was created it would on occasion not create the required EFS mounts. Resulting in the website not being usable in most cases.
S3 Bucket remote storage
Magento 2 has a feature that allows the media directory to integrate with remote file storage like AWS s3 buckets. The example for this setup in the Magento 2 documentation actually uses AWS s3 buckets as an example for the setup of remote storage.
For the most part this functioned. However several issues were discovered and needed to be remedied either by file patches or workarounds.
The Nginx server on the AWS web instances required a specific module to be installed to allow the remote storage to handle the resizing of the product image cache. After researching how to get this implemented there were still some issues with specific images not loading. Apparently, there is a bug in this Nginx module that is still not patched that breaks when it tries to process an image over 1MB. Most images fell under this limit since we’d run image optimisation. Though there were a select few that needed to be manually located via a command line search and optimised more aggressively.
Changes in scope
While API functionality was completed prior to launch, it hadn’t been through enough UAT to be deployed at launch. Further changes to the requirements of the scope including incremental stock updates being sent from Sage to Magento also played a part in putting this implementation on hold until post launch.
Legacy Customer and Order sync errors
As mentioned above there were some issues with the existing Legacy Custom and Order sync. This was primarily due to the fact that the Customer for any order being synced into Sage must already exist in Sage 200.
We built functionality to remedy this in the API scope, by altering some of the API functionality to enable part of it to be used by the legacy exports. This enabled the exports to ensure that a Magento customer was generated and attached to each Order before it was exported to Sage 200.
API collection development
Some endpoints weren’t configured to return the data as described in the Postman collection’s documentation. We communicated with the third party to help them identify some areas where the API needed work to get it to return data or alter the returned data. This allowed us to complete the API integration scope of work.
We continue to improve and develop the platform and the client is happy that they’re now on a vendor support platform with scheduled security updates, reducing their PCI exposure and allowing them to take the business forward without the limitation and exposure of a legacy platform.
Due to continual issues with the third party AWS hosting the decision was taken shortly after launch to help the client migrate to a dedicated Magento hosting provider, in this case Sonassi who are one of our trusted partners. This has helped increase performance and also remove multiple issues we encountered on the AWS platform. It has also added a layer of confidence knowing this is fully managed service and reduces the business risk.