Working with Large Sage Datasets and Tradebox One

Introduction - Potential Problems

Big datasets have to work harder; that’s a fairly universal rule of working with data. If you’re pushing the boundaries beyond what your software/infrastructure was designed to handle, you increase the likelihood of running into problems such as:

• Slowdown
• Failed posting attempts
• Operations timing out
• Unexpected behaviour
• Data corruption

These hold true of your Sage data and also your Tradebox data.

Contents:

• What's a 'big' dataset?
• What are my options?
• Keeping Tradebox data in check
• Keeping Sage data in check
• Environment

What’s a ‘big’ dataset?

This is where things get a bit vague. On the one hand, solutions providers often give guidance on the advised limits of their systems and what’s ‘safe’ to work with. On the other hand, anything that’s big enough to cause problems in your environment is a problem. So there aren’t really any hard and fast limits and every user would have a slightly different experience with the same volumes due to variations in the settings used, power of the computers, speed of the network etc.

Where you’re working with Sage 50, their advised limits are on page 2 of this article:

The headlines that apply to your Tradebox integration are (on modern versions), Sage recommend keeping your data inside of:

• 1 million splits (a split is shorthand for ‘transaction’ – ledger invoice transaction, bank receipt, supplier payment etc.)
• 50,000 invoices
• 50,000 sales orders
• 20,000 product records
• 2,000 customer records

This is consistent for Sage v21/2015 and later. Unfortunately, handling lots of data isn't something that'll be taken care of by installing a Sage upgrade.

Sage 50 doesn't prevent you from going over these limits. Think of them as a reference point; it's not as though 1,999 customer records runs completely without flaws and then everything breaks when you create two more records. But if you consistently process beyond these limits, you have a greater likelihood of problems.

Working within Tradebox, most of our users do up to a few hundred transactions per week through all their channels. Heavy users do a few hundred per day. While we don’t have a hard limit, if you’re doing thousands of orders a day, you should probably be looking for a system that’s built with that volume in mind, which Tradebox is not.

What are my options?

If you're running at or around the above limits then in short you have a bit of a choice to make.
You could either continue with a setup of Tradebox posting into Sage 50, with the knowledge that your system is more liable than the average user to be slow or run into data corruption and that regular data maintenance is a must. Often, bigger users keep Sage 50 for the bookkeeping and accounting side, and add other specialist software for tasks like stock control or CRM.
Or, you could migrate to a system built with larger datasets in mind. Moving to MMD software like Sage 200 often means a bigger price tag, and an investment of time and energy into getting a new system up to speed. We’re aware that running with Tradebox and Sage 50 does have a life cycle; hopefully your business will continue growing in size and scope and if that happens you will at some point need to upgrade your infrastructure to keep up.

We’re sometimes asked if we support larger accounts packages, or if we’re willing to build these integrations or build bespoke software. Unfortunately that’s just not something we have the bandwidth for. Moving to a larger accounts package than Sage 50 does mean you also need an alternative to Tradebox, as Sage 50 and Xero (which has far smaller safe limits than Sage 50) are the only accounts integrations we offer.

If you want to continue with a Tradebox and Sage 50 integration and large volumes, we can offer the following guidance on data maintenance. This guide is designed to inform and educate based on our knowledge and experience; following these steps should be safe but as with all things taking sufficient precautions such as backups and checking data is your responsibility as the user.

Keeping Tradebox Data in Check

Over time, temporary records and items stored in memory can slow the performance of your Tradebox data, so it’s important to run some routines to compress the database and reclaim disk space. To run these, go to Configuration and click Rebuild Indexes. Once the rebuild routine has finished, you'll see a message to let you know it has completed successfully. Click OK.

Keeping Sage Data in Check

Preparation

As your bookkeeping tool, Sage should be a permanent record of the money through your business; what you’re doing now is relevant today but might suddenly be relevant again in five years if you need to check something for an audit. What users often don’t realise is that you’re able to keep a permanent record of data while also removing it from live tables; stopping things from bogging down with years upon years of heavy data.

Before deleting anything from your live data, you should:

• Set aside some time. These routines can be very slow; if you've got a lot of data, backing it up or clearing it down means digging through all of it in one go. It's worth it; you'll regain that time in improved performance for your everyday processing.
• Take a backup. In fact, take a couple. Once you've taken a backup, it's just a file like any other that can be copied and moved around. Store one somewhere off-site; be it a memory stick out of the office (fire or flood won’t discriminate between your hard drive and your removable storage) or in a cloud storage system like Google Drive or Dropbox. Backup your Sage via File > Backup.
• Archive your company data. While the backup is a permanent record, if you’ve ever restored one you’ll know it’s slow and inconvenient; other users need to remain logged out while you load up an entire company worth of records to check one old thing and then load back to today.
  Thankfully, Sage's archive feature gets round much of this problem; the tool takes a copy of your entire Sage dataset as it stands today, and saves a read-only copy in a separate folder. Why is this useful? Because once the archive's created it only takes a couple of seconds to swap between the live company and the archive. So you can delete historic data to keep the live company running smoothly, but still have easy access to that invoice from a couple of years ago when you have a query.
• To archive your Sage data, go to Tools > Period End > Archive Company
• Archives are non-destructive – a copy of the data is saved but nothing is removed
• On older versions of Sage, you could only archive at year end. In modern versions, you can archive any time so long as no other users are logged in
• To open an archive, go to File > Open > Company Archive. Choose the archive you want to open and log in (logins remain as they were at the time, relevant if you’ve changed your logons or passwords since).
• Once in the archive, everything is read-only; you can view, search, print, email, drill down and run reports. You just can’t change anything...which makes sense given that we're talking about data from months or years ago.
• To go back to normal processing once you’re done working in the archive, go to File > Open > Company Data and log in to your live data as normal.
• Include archives in your backups. The routines you're going to be running will remove data from your live Sage company, so you'll need to keep backup copies of the archives to protect your processing history.
  • In File > Backup > Advanced Options, you’ll see which files are included for backup. Data Files refers to your normal Sage company for live processing. Company Archives are the archives; while you don’t need to include them in every backup, you should make sure you’ve got copies periodically. Incidentally the same holds true for the other options in this screen like customised layouts and reports – an All Files backup once per quarter and after any major routines like year end is best practice. Remember, if it’s not in the backup, it won’t be there if you lose all your data and have to restore.
  • After taking an All Files backup, remember to go back into Advanced Options and change it back the next time you backup - taking Data Files only is best for day-to-day as it makes the backup and restore processes much faster.
  • The same holds true for moving Sage data. If you relocate Sage to a new PC or a different server, as well as taking the live data, you should also take the archives. Opening an archive is quick and easy, but if the data for the archive isn't present because you've only relocated the live data, you might be stuck. A bad habit when moving Sage data is to move only the ACCDATA folder which contains the live accounts data; where you have archives this isn't sufficient.

Removing data from Sage

Once you’ve given yourself a sufficient safety net, you can start removing old data:

1) Audit trail transactions. In Tools > Period End > you'll find the Clear Audit Trail option which removes transactions prior to a certain date. This doesn't mean you're wiping your old account balances; anything that needs to reflect an older balance will still do so by bouncing down to a new brought-forward figure.

Any date prior to your current financial year is fair game; most people keep current year to date and last full financial year. If you're a very heavy user (e.g. you're pushing through 300,000 audit trail transactions per year or more), consider clearing up until your last year end date instead...it's not ideal as you'll lose figures on some reports that have a prior year comparison but if we're talking about those sorts of volumes that's probably the lesser of two evils compared with having your system struggle with day-to-day processing.
Any transactions that Sage thinks you still have anything left to run processing on (unpaid, unallocated, not VAT reconciled, not bank reconciled) will not disappear. It's often good to run the routine and then check how much old stuff is left over - if old transactions aren't clearing then Sage's support team can help you out with some reports that highlight why and what to do. Don't assume the routine isn't working if it doesn't clear all the data, there will be a reason and often an easy fix.

2) Invoices. An invoice in Sage is purely a document. It’s functional in Sage only at one point in time – when you update ledgers (‘post’ the invoice). At this point, the invoice transaction (SI) is created on the customer account and nominal ledger, and if it's a product invoice the stock adjustment happens.
Once an invoice has been posted, the invoice record in the invoice list isn't needed. Deleting the invoice does not delete or otherwise impact on the transactions associated with it. This is why you might have noticed that Sage are perfectly happy for you to have a million transactions but only recommend 50,000 invoices; the invoice list should be kept for relatively recent records. Anything older is only useful if you need to print or email an old copy, or run any reports using the Reports button within the Invoices and Credits screen; all of which can be done from your company archive anyway, so keep the invoice list recent. Trust us, your Tradebox download will be faster and smoother with a small Sage invoice list.
To delete invoices, go to the Invoices and Credits screen (called the invoice list in older versions) > create a filter to show only before or between a certain date range > click Swap to select all> Delete. This process can struggle a bit, so it’s best to do it in batches; if deleting 3 months of invoices takes 30 seconds, great, do more in the next batch. If it takes an hour of staring at a progress bar to get there, try doing one month in the next batch. Be aware that unposted invoices will also be deleted if you've selected them, so make sure you're happy to lose anything you can see on the screen after applying the filter, but before hitting swap and delete.

3) Sales Orders. The same principle applies as with invoices; a Sales Order is only functional up until the point where it’s despatched and invoice(s) created. As with invoices, filter the list > swap > delete.

4) Customers. Open the customer list > Swap to select all > Delete. It’s safe to ‘try’ to delete the entire customer list; only accounts that have no outstanding transaction and no historic activity before your clear audit trail date will be removed; if you’re following our other recommendations that means you haven’t dealt with them either since your last year end, or the year before. Bearing in mind that Sage's lowest recommended limit is for customers (2,000 records), if it’s been that long since a customer's bought from you they shouldn’t really be in your live data (if you need ongoing CRM for a lot of customers, Sage 50 probably isn't the best solution for that). As with everything else, their details and history are in your archive if you need them.
If you've got tens of thousands of customers, it'll take a pretty long time to do anything with the entire list. In this case, apply a filter to break the list into smaller batches before Swap and Delete.

5) Purchase orders and suppliers. Tradebox doesn't interact with these. But if you're running routines to clear things up anyway, follow same routine you did for invoices in the purchase order list, and follow the same routine you did for customers in the supplier list.

6) Products. Old product records can be deleted from Sage if they have no stock on hand, no current allocations, aren't part of a bill of materials and have no historic activity. The first two points shouldn't really apply to old products anyway but run a stocktake to adjust out old stock if needed. If you use BOM's in Sage, you can run the product explosion reports to check what's part of a BOM and amend their records accordingly.

To clear historic stock activity, go to Tools > Period End > Clear Stock. This is similar to the clear audit trail routine, but for stock activity rather than financials. Once you've cleared the activity of your old products, you can delete them. In products highlight a range of product records/highlight all/use a filter and then click Swap to highlight > click Delete. As with customers, it's safe to 'try' to delete all product records; any that have current activity won't be deleted. Newer versions of Sage offer a list of suggested product deletions after you run Clear Stock, which will give the same effect.

After removing data

After removing any significant volume of data in Sage, go to File > Maintenance > Recovery Tools (this will force a backup, data files only is fine) > Reindex and Compress All.
Once that’s done, go to File > Maintenance > Check Data. Removing accounts can cause warnings or comments, check if they need to be actioned at https://my.sage.co.uk/public/mydata.aspx

Preventing future buildup

While there's not much you can do to process fewer transactions or invoices, running these routines periodically is often better than waiting until things are out of control.

One option that can help is to have Tradebox posting all sales into generic customer accounts rather than creating individual customer records for each sale. This article covers the advantages and disadvantages of the customer choice.

Environment

Data is only some of the battle. Your environment will also have a big impact on how well your systems handle volume; this means both the power of the hardware you’re using, and other factors like your network speed.

Network

Running any program across a network will be slower than running with the database and relevant files stored locally on your PC, as requests and transfer of data will be impacted by your network speed.

You can check the setup of your Tradebox by going to Support and looking to the folders under Program Information. The Program Folder should always be installed locally within a directory on your computer's C:\ drive. If it's installed on another drive and you're having issues, try uninstalling and reinstalling to the boot drive.
The Data and Logs folders always sit alongside each other. The location of these folders will let you know whether Tradebox is running purely from your PC (data folder on local drive e.g. C:\) or across a network (data folder has either a mapped network drive e.g. S:\tradebox, or a path starting with a double backslash e.g. \\server\tradebox or \\192.168\tradebox).

If your Tradebox is currently running across a network and you are having problems, it may be worth relocating the database to your computer to see if it improves performance. If you don't need to access Tradebox from any other PCs on your network, there's no benefit to running across your network (though we would recommend storing your Tradebox backup files on a network location or off-site). It's also worth checking the location of the Input/Output folders in the Import File section of the channels. These should be kept on the same machine as your Tradebox data.

Multi-user Sage always involves at least one of the users accessing the data across a network. Again, this will always be slower than using local data. Often networking is necessary, but if you are a heavy user on the data side it’s worth exploring your options, including installing and running Tradebox from the same machine that holds the Sage data, and making sure this is the most powerful machine available to you.

If you do need to remain in a network environment for either program, we would always recommend a wired connection as this will always be faster and more stable than running via wi-fi.

PC Spec

Sage's system requirements are available here. Any machine capable of running Sage should also be capable of running Tradebox. Bear in mind that those specifications are purely a minimum and if you're a heavy user you should look to be well beyond that.

A faster processor has more raw computational power at its disposal. More RAM (memory) allows your PC to multitask more efficiently. For storage, SSD's (solid state drives) can read and write data much faster than traditional mechanical hard drives (HDD; where your files are stored and also where Windows and your programs are installed).

If you suspect your PC or server would benefit from an upgrade, you should talk it through with an IT professional. In our own internal benchmarks, extracting 1,000 product records from local Sage data had as varied results as taking 20 minutes on a couple-of-years-old relatively underpowered machine (that still met minimum requirements and we were still using for everyday processing), to 2 minutes on a brand new high spec machine running on an SSD.