Key takeaways
- Your business data lives in SQL Server databases — protecting them is protecting the business.
- Choose the recovery model deliberately, and back up the transaction log if you use Full — or the log will grow until the disk is full.
- A backup is only proven when you have restored it successfully.
- Never expose SQL Server to the internet, never run on a weak
sapassword, and keep it patched.
What SQL Server is
Microsoft SQL Server is a database engine: the program that stores your invoices, stock, customers and journal entries, and answers the queries your application sends. Users rarely see it directly — they use the ERP or POS screens — but every one of those screens reads from and writes to SQL Server in the background.
That means the health of one server, often a single machine in a back room, decides whether the whole company can sell, invoice and report.
Editions in brief
- Express — free, and fine for small applications, but it has limits on database size and on the memory and CPU it will use. Check Microsoft's documentation for the current limits of your version. A growing business can outgrow Express without noticing until performance drops or the database hits its cap. Express also has no SQL Server Agent, so scheduled backups must be arranged another way.
- Standard — the usual choice for small and mid-sized businesses running a real ERP; licensed per core or per server plus client licences.
- Enterprise — for large workloads and advanced high-availability features.
- Developer — full features, free, but only for development and testing, never production.
Backups: full, differential and log
- Full backup — a complete copy of the database at a point in time.
- Differential backup — everything changed since the last full backup. Smaller and faster; a restore needs the full plus the latest differential.
- Transaction log backup — every change since the previous log backup. This is what lets you restore to a specific moment, such as five minutes before someone deleted a batch of invoices.
Which of these you can use depends on the database's recovery model.
| Recovery model | What it allows | Watch out for |
|---|---|---|
| Simple | Full and differential backups only; restore to the time of the last backup | Everything since the last backup is lost in a failure |
| Full | Full, differential and log backups; restore to a point in time | The log keeps growing until it is backed up — scheduled log backups are mandatory |
| Bulk-logged | Like Full, but large bulk operations are minimally logged | Point-in-time restore may not be possible within a bulk operation; usually used temporarily |
Test your restores
Backup jobs that report “success” can still produce files that are incomplete, unreadable or stored on the same disk as the database. Regularly restore a recent backup to a different server or under a different database name, open it in the application, and check the latest transactions are there. Keep copies off the server and off-site, following the 3-2-1 backup rule — ransomware targets database servers and their backup folders first.
Performance basics
- Memory. By default SQL Server will use as much memory as it can. On a server that also runs other things, set a sensible maximum server memory so Windows and other services aren't starved.
- Indexes. Most “the system is slow” complaints trace back to queries without suitable indexes. Your software vendor should be involved before adding or removing indexes on their database.
- Statistics and maintenance. Schedule regular statistics updates, index maintenance and integrity checks (
DBCC CHECKDB) through maintenance plans or scripts, outside working hours. - Disks. Data, logs and backups on separate volumes where possible, and fast storage (SSD) for data and logs.
Security
- The
saaccount. Disable it, or give it a long unique password that no application uses. - Never expose port 1433 to the internet. Internet-facing SQL Servers are scanned constantly and attacked with password guessing. Remote branches should connect over a VPN, behind a properly configured firewall.
- Least privilege. Each application and person gets its own login with only the permissions it needs — not sysadmin “to make it work”.
- Patches. Install SQL Server cumulative updates and security updates, and plan an upgrade before your version reaches end of support.
- Encrypt backups or store them where only authorised people can reach them — a backup file is a full copy of your data.
Monitor disk space and jobs
Many SQL Server outages are simply a full disk. Set up alerts for free space on data, log and backup drives, and make sure failed backup or maintenance jobs send an alert to someone who actually reads it. A weekly glance at database and log file sizes catches trouble weeks before it becomes an emergency.
The bottom line
SQL Server rarely fails on its own; it fails because a log grew unchecked, a backup was never tested, or a login was left open. A few hours of setup and a little monitoring prevent most of those disasters. If you're not sure how your ERP database is backed up or secured, book a free IT assessment.
