SQL-backed POS systems protect data integrity during busy trading by saving every sale as a database transaction that is either written in full or not at all, logging each change before the data itself is changed, and backing the database up on a schedule, and yes, a Microsoft SQL Server POS can handle mission-critical retail and hospitality transactions when it runs on the right edition with tested backups. Those guarantees come from the SQL Server engine itself, as Microsoft documents them, not from a promise on a brochure.
Busy trading is where integrity gets tested. A month-end Saturday at a grocery store, a Friday dinner service with six tables splitting bills, a hotel bar posting drinks to rooms: many tills write to the same stock and the same accounts at the same moment, and in South Africa the power or the internet can drop in the middle of it. This guide explains what SQL Server does with each sale in those moments, where its limits are, and what to ask a POS supplier before you trust it with your trading day.
In short: each sale is one transaction that saves completely or not at all. Locks stop two tills from overwriting the same stock figure. The write-ahead log lets the database finish or undo half-done work after a power cut. Backups cover what the log cannot: a failed disk, a deleted table, a stolen server. The edition and the recovery model decide how much of that you actually get.
How do SQL-backed POS systems protect data integrity during busy trading, and can Microsoft SQL Server POS handle mission-critical retail and hospitality transactions?
Yes. A SQL Server POS protects each sale with four safeguards that Microsoft builds into the database engine: transactions, locking, the write-ahead transaction log and backup and restore. Together they mean a sale is never half-saved, two tills cannot silently overwrite each other's stock count, a power cut does not leave broken records behind, and a failed server can be restored from a copy.
Whether a particular POS handles mission-critical trading depends on how it uses those safeguards. Microsoft describes its Enterprise edition as enabling "high service levels for mission-critical workloads", while the free Express edition is limited to a 10 GB database and the lesser of 1 socket or 4 cores. The edition, the recovery model, the backup schedule and a restore that has actually been tested are the four things to check. The rest of this guide takes each one in turn.
The four safeguards SQL Server gives every sale
Microsoft SQL Server is a relational database management system. For a till, that means sales, stock and customers live in related tables, and the engine enforces the rules between them. Microsoft's transaction locking guide says a transaction must have four properties, atomicity, consistency, isolation and durability (ACID), to qualify as a transaction. Here is what each safeguard does at the counter.
| Safeguard | What Microsoft's documentation says | What it means at the till |
|---|---|---|
| Transactions (atomicity) | A transaction is a single unit of work. If it succeeds, all its changes are committed; if it fails or is rolled back, all its changes are erased. | The sale line, the payment, the stock deduction and the loyalty points save together, or none of them do. No sale is recorded without its payment. |
| Locking (isolation) | The engine provides locking facilities that preserve transaction isolation. Without concurrency control, "lost updates" happen: the last update overwrites the others. | Two cashiers selling the last bottle of the same wine at the same second cannot both leave stock at "1". One waits a moment, then sees the other's change. |
| Write-ahead log (durability) | No data change is written to disk before its log record is. After a stop, finished work is rolled forward and incomplete transactions are rolled back. | If the power dies mid-sale, the database comes back with either the whole sale or none of it, never half a basket. |
| Backup and restore | Backing up is the only way to protect your data. Valid backups recover from media failure, user errors and hardware failures. | A dead disk or a deleted price list is a restore, not a lost month of trading history. |
Transactions: a sale saves whole or not at all
Microsoft's page on transactions in Transact-SQL is direct about it: if a transaction is successful, all of its data changes are committed and become a permanent part of the database; if it hits errors and is rolled back, all of its changes are erased. A POS that wraps each sale in one transaction cannot produce the classic busy-day fault where a card was charged but the sale never reached the day's takings, or stock moved without a sale behind it.
Locking: busy tills cannot overwrite each other
A busy shop is a concurrency problem. Ten tills update the same stock rows, and a manager runs a report at the same time. Microsoft's locking and row versioning guide describes what goes wrong without concurrency control. Lost updates occur when two transactions read the same row and each update it from the value they read, so the last one overwrites the other's change. SQL Server's locks keep concurrent transactions isolated from each other, so each sale sees the stock figure either before or after another sale changed it, never a mix.
The write-ahead log: a power cut does not leave half a sale
SQL Server uses a write-ahead logging algorithm, which guarantees that no data change is written to disk before the log record that describes it. When the server restarts after a failure, changes from finished transactions that had not reached the data files are rolled forward, and changes from incomplete transactions, with no commit record, are rolled back. Microsoft's locking guide adds that for fully durable transactions the log record is hardened to disk before the transaction commits, so a committed sale survives even if the hardware, the operating system or the database engine fails.
One honest caveat: SQL Server 2014 and later also allow delayed durable transactions, which commit before the log record reaches the disk. That trades a little safety for speed. Ask your POS supplier whether sales use it. For takings, full durability is the setting you want.
Backup and restore: the copy that covers what the log cannot
The log protects against a stop in the middle of work. It does not help if the disk itself fails, a table is deleted by mistake or the server is stolen. Microsoft's backup and restore overview says backing up is the only way to protect your data, and recommends testing your strategy by restoring a set of backups and storing copies off-site. A backup nobody has ever restored is a hope, not a plan.
What happens to a sale when the power or the internet drops?
The power and the internet are separate risks, and a SQL Server POS handles them differently.
- The power goes off at a till. The write-ahead log means the database recovers to a clean state when it restarts: whole sales kept, half-finished ones undone. The sale being rung up at that moment has to be rung up again. A UPS stops it happening at all; our POS hardware page notes that a standard 650VA unit runs a touch terminal and receipt printer for 2 to 4 hours.
- The internet goes down. If the database is on a server in the shop, the tills reach it over the shop's own network, so selling carries on. Only what genuinely needs the internet stops, such as cloud dashboards and off-site backup, which catch up when the line returns. A cloud-only POS that writes each sale to a remote database cannot do this unless it has an offline mode, which is worth testing before you rely on it.
- The whole site loses power. Plan for it rather than hope. Eskom's load-shedding status page publishes the current status and the schedule for your area, so you can see a slot coming. Our guide to keeping a POS running through load shedding covers UPS sizing for the server, the router and every till.
Our comparison of cloud POS and SQL-backed on-premise POS sets out where each model commits a sale and what each one needs to keep trading.
Can SQL Server handle mission-critical retail and hospitality transactions?
The engine can. The question for a buyer is which parts of it your POS actually uses, because Microsoft sells SQL Server in editions with very different limits.
| What to check | What Microsoft documents | Why it matters for a POS |
|---|---|---|
| Edition | Express is the entry-level, free database, limited to a 10 GB database and the lesser of 1 socket or 4 cores. Standard and Enterprise have far higher limits. | Express is fine for a small shop's till. A multi-till store with years of history can reach 10 GB, so ask what happens then. |
| Recovery model | Enterprise and Standard default to the full recovery model; Express defaults to simple. Under simple, changes since the most recent backup are not protected. | Under simple recovery, a disk failure at 18:00 loses every sale since the last backup. Under full recovery with log backups, you can restore to a point in time. |
| High availability | Always On availability groups fail a set of databases over together to a secondary copy, with up to eight secondary replicas, in Enterprise edition. Standard edition has Basic availability groups for a single database. | A hotel or a large store that cannot stop for a server repair can keep a second server ready to take over. Most single shops do not need this; they need a tested backup. |
Retail: many tills, one stock count
In retail the integrity test is the stock figure. A grocer scanning hundreds of baskets an hour, a butchery selling by weight or a bottle store with a tight stock count all depend on every sale and return updating stock exactly once. That is the lost-update problem the locking guide describes, solved by the engine. For groups, the harder part is branches: on our TimeWorks POS software, each branch's tills sync sales and stock to one central database, so head office and each store see the same stock figure. Our grocery and supermarket POS page covers the scanning and stock side for high-volume stores.
Hospitality: a bill is many records that must agree
Hospitality transactions are longer and messier than a retail scan. A restaurant table stays open for two hours, collects orders from several waiters, sends tickets to the kitchen and then splits into four payments. A hotel bar posts a guest's drinks to their room, and the folio has to match the bar's takings at the end of the night. Each of those steps writes several related records, and a transaction keeps them in step: a split bill either settles completely or stays open, it never half-closes.
That is why we build hospitality on the same SQL database. Our restaurant POS runs table management, kitchen display integration and split bills from one SQL-backed system, and our hotel POS handles restaurant billing, bar tabs, room charge posting and consolidated guest folios in the same way.
How TimeWorks runs SQL Server POS
TimeWorks has supplied point of sale systems to South African businesses since 1999, and more than 700 businesses use it. The software is built on Microsoft SQL Server. Here is what that means in practice, in the same words our product pages use:
- Local commits. Transactions commit locally against Microsoft SQL Server, so the shop trades through a connectivity failure. Cloud reporting and off-site backup are available as add-ons.
- Offline trading and sync. When the internet goes down, transactions continue processing locally and sync to the central database when the connection is restored.
- Scheduled backups. Sales, stock and customers sit in one set of records, with scheduled backups you can restore a day's trade from.
- Adding tills. Additional terminals connect to your existing SQL database and start with your full product catalogue and pricing.
- Your data stays yours. If you ever change supplier, your data leaves as a standard SQL Server backup file that restores anywhere.
- Support when it matters. For critical system faults, our on-call engineers respond within 30 minutes, day or night, weekends included.
On price, TimeWorks POS software is R350 per month excluding VAT for the Cloud-Based Entry POS or R500 per month excluding VAT for the Full Business Solution, both per branch. If you would rather not buy hardware, a monthly POS rental is from R995 per month excluding VAT and includes the hardware, the software and the support. Daily rental for an event is quoted per booking: contact us for pricing.
Why data integrity is also a legal duty in South Africa
Two South African laws turn "accurate POS data" from a nice-to-have into an obligation.
- POPIA. Section 19 of the Protection of Personal Information Act says a responsible party must secure the integrity and confidentiality of personal information by taking appropriate, reasonable technical and organisational measures to prevent its loss, damage or unauthorised destruction. Loyalty members, account customers and hotel guests in your POS are personal information. Transactions, access controls and tested backups are exactly those technical measures. Our POPIA guide for POS systems covers the rest of the Act.
- SARS record keeping. SARS requires records to be kept for five years from the date a return is submitted, in an orderly fashion and in a safe place. Electronic records kept at a place outside South Africa need SARS authorisation. A POS database on your own premises, with backups you can restore, keeps five years of sales evidence where you control it.
Questions to ask any SQL Server POS supplier
- Which SQL Server edition does each site run? If it is Express, ask how big the database is today and what happens near the 10 GB limit.
- Which recovery model, and how often are log backups taken? This decides how many sales you could lose if a disk fails mid-afternoon.
- Where do backups go, and when was a restore last tested? Ask for the date and what was restored.
- Is each sale one transaction with full durability? The answer should be yes for every sale and payment.
- What keeps working when the internet drops? Ask to see it: unplug the router during the demo and ring up a sale.
- Who answers when the server stops on a Friday night? A response time in writing beats a ticket queue.
Frequently Asked Questions
What is a POS server?
A POS server is the computer that holds the point of sale database, usually in the back office, and that every till in the shop reads from and writes to over the local network. In a SQL Server POS it runs Microsoft SQL Server, keeping sales, stock, prices and customers in one place, while the tills at the counter do the ringing up.
What is SSRS and why is it used?
SSRS is SQL Server Reporting Services. Microsoft describes it as a set of on-premises tools and services to create, deploy and manage paginated reports, meaning formatted, printable reports drawn straight from a SQL Server database. In a POS setting it is the kind of tool used for end-of-day, stock and sales reports. Microsoft notes that from SQL Server 2025, on-premises reporting services is consolidated under Power BI Report Server. Source: Microsoft Learn.
What is the difference between port 1433 and 1434?
TCP port 1433 is the port a SQL Server default instance listens on, and UDP port 1434 is used by the SQL Server Browser service, which helps clients find named instances. Microsoft's firewall guide gives both as the ports to open for a default instance and the Browser service. For a POS, this matters when tills on the shop network have to reach the database server through Windows Firewall. Source: Microsoft Learn.
What is POS in programming?
In retail and hospitality software, POS stands for point of sale: the program that records a sale, takes payment, prints the slip and updates stock, usually with a database such as SQL Server behind it. Outside retail, in language processing, POS can also mean "part of speech", which is unrelated.
Is SQL Server Express enough for a POS system?
For a small shop with one or two tills, often yes. Express is free, but Microsoft limits it to a 10 GB database and the lesser of 1 socket or 4 cores, and it uses the simple recovery model by default, so changes since the last backup are not protected. A busy multi-till store, or one keeping years of history, should check the database size and the backup schedule, or run Standard edition.
Does a SQL Server POS keep working when the internet goes down?
Yes, when the database is in the shop. The tills write to the local SQL Server over the shop network, so sales carry on and sync to head office when the connection returns. TimeWorks tills continue processing transactions locally and sync to the central database once the internet is back.
Is a cloud POS enough for mission-critical retail?
It can be, if it has a tested offline mode and a reliable internet path, but a cloud-only POS writes each sale to a remote database, so it depends on the connection in a way a local SQL Server does not. Our cloud vs SQL-backed POS comparison sets out the trade-offs, including cost and POPIA.
Does renting a POS change how my data is protected?
No. The safeguards in this guide come from SQL Server itself, so they apply whether you rent the till or buy it. A TimeWorks monthly rental is from R995 per month excluding VAT with the hardware, software and support included; daily rental for events is quoted per booking, so contact us for pricing.
Conclusion
A SQL Server POS protects your busiest trading day with safeguards Microsoft documents and builds into the engine: whole-or-nothing transactions, locks between tills, a write-ahead log that survives a power cut, and backups for everything else. It can carry mission-critical retail and hospitality trading, provided the edition fits the business, the recovery model and backups are set up properly, and someone has tested a restore. If you want to see how that works on a real till, book a free demo and ask us the six questions above.