Low cost backup and recovery for SQL Server Express POS terminals

Cost effective, robust and reliable data protection for SQL Server POS databases

published: 2014-11-20 08:59

A large retailer required a cost effective, robust and reliable method of providing data protection for their SQL Server Express POS databases in over 100 store locations. Without proper data protection at the store level, if data corruption was experienced, data loss would occur unless costly recovery processes were undertaken to re-establish the store database.

Problem

A retail customer had over 100 store locations, with each location supporting one or more POS terminals. Each location also maintained a local SQL Server Express database which retained transaction data which is periodically uploaded to a central SQL Server data warehouse. Each store was running SQL Server on standard PC hardware, i.e. not server quality infrastructure and no disk redundancy.

While this centralised upload occurs, retaining the transaction data at the store level was still very important. Referencing customer transactions, historical orders etc. at the store level was a requirement. While if data corruption occurred at the store level this data could be partially regenerated from the central warehouse, this was a time intensive and costly solution that was only partially effective. In order to protect the stores from data loss, which could occur periodically due to disk or other hardware failure in the local store’s PC’s, a local backup solution was required.

Due to the high volume of POS terminals, a cost effective solution would require a low overhead to implement and maintain. In addition, as store staff would on occasion turn off individual PC’s at the store location at the end of each business day, the backup solution had to be able to cope with unexpected failures and outages.

Solution

RockSolid SQL provided a solution via the automated backup capabilities of their RockSolid management environment. RockSolid allowed a single policy to be defined which specified how the SQL Server database FULL and LOG database backups were to be performed, and this policy was automatically rolled out and implemented on the store SQL Server Express databases.

As SQL Server Express does not contain a native SQL Agent scheduler, the RockSolid central scheduling service was used to schedule and execute the relevant backup jobs. This scheduling functionality of RockSolid is automatically invoked if the SQL Server node being managed does not contain native scheduling function and is seamless in terms of both the policy definition and status reporting.

To cope with situations when stores turned off individual PC nodes unexpectedly, or if there were failures on individual nodes, a series of alternative nodes were defined in order of priority. These may located within the store or to an alternate store, this provided the backup process a list of nodes to try in order to achieve a successful backup. To ensure the backup process did not cause disk space issues on the backup nodes, disk space was also managed by RockSolid. If a backup would cause low disk space on the node to be backup up to, either the number of retained backups would be reduced on that node or an alternative node would be selected.

If the SQL Server Express host had been switched off at the time of a scheduled backup, this backup would automatically run when the node became available. This ensured, as much as possible, at all times a current database backup was located on an alternate node to the SQL Server Express host. Reporting of backup history and locations was available via the RockSolid console, with the history data being held externally not affected by node failure within the store.

Finally, a daily status report was scheduled to provide the client a summary of all backup statuses to provide reassurance that backups were being performed as per policy, and also to highlight any node issues impacting on backup and recovery. However,as the RockSolid automation capability provides significantly more functionality than just backup and recovery, the instances managed by this service also benefit for the full capability of RockSolid automated management. This includes the automated resolution of capacity issues, performance issues as well as common break/fix style operational issues. This has improved the reliability of the store POS instances and reduced the likelihood of failure due to preventable issues considerably.

Summary

The RockSolid solution provided the customer the ability to cost effective create local backups of the SQL Server Express POS databases. The solution has highly automated, self-managing and self-repairing making it highly cost effective. In the advent of failure at the store level, the customer can be confident of local backup copies being available for rapid restoration.

Technical Details

  • SQL Server 2008 R2 Express Edition
  • SQL Server 2005 Express Edition
  • RockSolid DBAaaS
  • RockSolid

RockSolid Automation Platform

Related News

SQL Server Disaster Recovery VS. High Availability (Update)

SQL Server Cluster Instance failing over from Primary Node to Secondary with no specific error

SQL Server 2008 R2 rules the roost

SQL Server Version Usage Update #1

SQL Server 2012 - Where is the Love?