# SQL Server care for an on-premises ERP database

> A working checklist for the SQL Server under an on-premises ERP such as Prophet 21 software, from backups and restore tests to memory, tempdb and patching.

Source: https://docs.lumina-erp.com/prophet-21/sql-server-care-for-an-erp-database/

**In short.** An ERP database is safe when its backup chain matches how much data the business can afford to lose, and when a restore has been tested recently enough to prove it. Integrity checks, sensible maintenance, regular patching and correct memory and tempdb settings keep it fast. Confirm with your ERP vendor before adding indexes or jobs to the ERP database itself.

When the ERP runs on servers you own, the SQL Server underneath it is yours to look after. Users experience every database problem as "the ERP is slow" or "the ERP is down." The worst one, a lost database with no working backup, is the one nobody notices until the day it matters. Below is the routine we set up for an on-premises ERP database, with the reason for each step and a checklist at the end.

Everything here is standard Microsoft SQL Server administration, drawn from Microsoft Learn and linked below. It applies to Prophet 21 software and to most other ERP systems that sit on SQL Server.

:::caution[Check your vendor support terms first]
ERP vendors often limit what you may change inside their database. Adding your own indexes, triggers, tables or jobs that touch ERP tables can affect support, and an upgrade may drop or conflict with them. Server-level care (backups, integrity checks, memory, patching) is normally yours to do, but confirm in writing with your ERP vendor before you add indexes, change database options or schedule jobs that modify the ERP database.
:::

## The routine at a glance

The table sums up each area, what good looks like and how often we check it.

| Area | What good looks like | How often we check |
|---|---|---|
| Recovery model and backups | Full recovery, with full, differential and log backups that meet the agreed RPO | Daily alert review |
| Restore testing | A real restore to another server, timed and checked | Monthly, and after any backup change |
| Integrity | `DBCC CHECKDB` completing clean | Weekly full check, more often physical-only |
| Indexes and statistics | Statistics current, index work only where it helps | Weekly |
| tempdb | Equal-sized data files, presized, on fast storage | At build, then when workload changes |
| Memory | `max server memory` set, leaving room for Windows | At build, then after hardware changes |
| Waits and blocking | Baseline known, blocking chains investigated | Daily glance, deeper monthly |
| Non-production copy | Refreshed on a schedule and scrubbed | Monthly or before each test cycle |
| Patching | Current cumulative update, tested first | Every one to three months |

## Decide RPO and RTO before you design backups

Two numbers drive the whole backup design, and the business should set them, not IT:

- **Recovery point objective (RPO):** how much work the business can afford to lose, measured in time. "Fifteen minutes of orders" is an answer. "None" usually means nobody has priced it.
- **Recovery time objective (RTO):** how long the business can be without the system before the damage is serious.

Write both down with the name of the person who agreed to them. Every other decision on this page is a way of meeting those two numbers at a reasonable cost.

## Recovery model and the backup chain

SQL Server offers three recovery models. For an ERP database the practical choice is between two:

| Recovery model | Log backups | Point-in-time restore | Fits an ERP when |
|---|---|---|---|
| Full | Required | Yes, if the log chain is unbroken | Almost always. It is the only way to get a small RPO |
| Simple | Not supported | No, only to the end of a backup | You can accept losing everything since the last full or differential backup |
| Bulk-logged | Required | Not within a log backup that contains bulk operations | Rarely, and only switched on briefly around a large load |

Microsoft documents that under the full model the transaction log keeps growing until a log backup runs. A database in full recovery with no log backups is the most common reason we find a log file larger than the data file.

A typical ERP backup chain has three layers:

| Backup type | What it holds | Typical schedule |
|---|---|---|
| Full | The whole database, plus enough log to make it consistent | Nightly or weekly, in a quiet window |
| Differential | Everything changed since the last full backup | Daily, between fulls |
| Transaction log | Every log record since the previous log backup | Every 5 to 15 minutes during business hours, set by your RPO |

Differentials shorten a restore because you apply one differential instead of a day of log backups. Log backups are what make point-in-time recovery possible, for example to the minute before someone ran a bad mass update.

### Habits that keep the chain usable

- Store backups away from the database files. Microsoft advises a separate physical device, because a backup on the same failed disk is not a backup. Keep at least one copy off-site or in cloud storage.
- Use checksums and verification. `BACKUP ... WITH CHECKSUM` and `RESTORE VERIFYONLY` catch damaged media early. They do not replace a real restore test.
- Take ad hoc backups as copy-only. A one-off full backup for a test refresh or before an upgrade should use `COPY_ONLY`, so it does not become the base for the next differential and confuse the restore sequence.
- Encrypt and compress backups where your edition allows, and restrict who can read the backup folder. A backup file is a complete copy of your customer, pricing and payroll data.
- Restore only backups you trust. Microsoft warns that restoring a backup from an untrusted source can compromise the whole instance.

## Test your restores

Microsoft is direct about this: you do not have a restore strategy until you have restored your backups and checked the result. A backup job that reports success every night proves the job ran, not that the file can be restored.

1. Restore to a different server. Use the most recent full, the latest differential and the log backups since, as you would in a real failure.
2. Time it. Compare the elapsed time with your RTO. If a restore takes four hours and the RTO is two, you have found the problem on a quiet day instead of a bad one.
3. Restore to a point in time at least once a quarter. Pick a time between two log backups and use `STOPAT`, so you know the team can do it under pressure.
4. Run `DBCC CHECKDB` on the restored copy. Microsoft recommends this to confirm the backup media was not damaged.
5. Open the ERP against it. Log in, look up a recent order, run a report. A database that restores but will not start the application is not a recovery.
6. Write down what happened. Keep the steps, timings and file locations in your run book, so the next restore does not depend on one person.

A quick way to check that backups are happening is to read the backup history SQL Server keeps in `msdb`:

```sql
SELECT d.name AS database_name,
       d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
       ON b.database_name = d.name
      AND b.is_copy_only = 0
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;
```

A database in full recovery with an empty or stale `last_log` column needs attention today.

## Check integrity with DBCC CHECKDB

`DBCC CHECKDB` checks the logical and physical integrity of every object in a database. It is how you find corruption from storage or hardware while you still have clean backups to fall back on.

- Run a full check regularly. Microsoft recommends frequent `PHYSICAL_ONLY` checks on large production databases, because they are much cheaper, plus a periodic full check with no options. Weekly full checks are a common rhythm for ERP databases. If the production window is tight, run them on a restored copy on another server.
- Alert on failure. Route the job result to someone who will act on it.
- Restore rather than repair. If `CHECKDB` reports errors, Microsoft recommends restoring from the last known good backup. `REPAIR_ALLOW_DATA_LOSS` is an emergency last resort that can remove data, and on an ERP database it can leave orders, invoices and ledger entries inconsistent with each other.

:::danger[Do not run REPAIR_ALLOW_DATA_LOSS on a live ERP database without your vendor]
Repair can deallocate rows or pages, and SQL Server does not check the ERP application rules while it does so. Call your ERP vendor and plan a restore first.
:::

## Maintain indexes and statistics without overdoing it

Many ERP servers run a nightly job that rebuilds every index. Microsoft's current guidance is more measured:

- Measure the effect of index maintenance on your workload before and after, for example with Query Store. It does not always help.
- Much of the benefit people see from a rebuild comes from the statistics update, which a rebuild performs with a full scan. Updating statistics costs far less than rebuilding indexes and often gives the same improvement.
- Reorganize is lighter than rebuild and is always online. Rebuilds can block users unless done online, and need space for a second copy of the index.
- Fixed fragmentation thresholds alone are a poor guide. Watch fragmentation and page density over time, and maintain the specific indexes that slow down real queries.

For most ERP databases that means: keep `AUTO_UPDATE_STATISTICS` on, add a scheduled statistics update on the busiest tables and run index maintenance selectively in a quiet window. Schedule it away from backups and `CHECKDB` so the three heavy jobs do not compete.

:::note[New indexes are a vendor conversation]
A missing index suggestion from SQL Server can be right, and still be the wrong thing to add to a vendor schema without agreement. Take the evidence (the query, its plan and the suggested index) to your ERP vendor, or ask whether a supported extension point exists for it.
:::

## Size tempdb and memory correctly

These two settings are often made once at install and never revisited. Both matter for an ERP, because reports, sorts and large imports lean on tempdb and memory.

### tempdb

Microsoft guidance for tempdb data files:

| Logical processors | tempdb data files |
|---|---|
| 8 or fewer | One per logical processor |
| More than 8 | Start with 8, and add in multiples of 4 only if allocation contention persists |

All tempdb data files should have the same initial size and the same growth increment, because SQL Server fills them proportionally. Presize them to hold your normal workload so tempdb is not growing from a few megabytes after every restart, and put tempdb on fast storage.

### Max server memory

Out of the box, `max server memory` is effectively unlimited, and SQL Server will try to take nearly all of the server memory over time. Microsoft recommends setting an upper limit in every version. As a starting point for a server that runs only SQL Server, Microsoft suggests roughly 75% of the memory not used by other processes, then refining from observed usage. If the ERP application tier, reporting services or anything else shares the box, subtract what they need first.

## Monitor waits and blocking

When users say the ERP is slow, two views of SQL Server tell you most of the story.

**Waits** show what SQL Server spends time waiting on: disk reads, locks, CPU, memory grants. `sys.dm_os_wait_stats` accumulates totals since the last restart, so record a baseline on a normal day and compare against it, rather than reading raw totals in isolation. Query Store, enabled by default for new databases from SQL Server 2022, keeps per-query history and waits, which makes "it was fine last week" answerable.

**Blocking** is normal in any database that uses locks, but long blocking chains show up as frozen screens. Microsoft's method is to find the session at the head of the chain and what it is running:

```sql
SELECT r.session_id,
       r.blocking_session_id,
       r.wait_type,
       r.wait_time AS wait_ms,
       s.login_name,
       s.host_name,
       s.program_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
  ON s.session_id = r.session_id
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;
```

The common head blockers on an ERP server are a long report or ad hoc query run during the working day, an integration holding a transaction open and maintenance jobs running into business hours. Our page on [reading Prophet 21 data with SQL, safely](/prophet-21/reading-p21-data-with-sql/) covers how to keep your own queries off that list.

Keep a little history. A scheduled job that saves wait statistics and any blocking longer than a minute into a small admin database gives you evidence when someone asks why last Tuesday was slow. Keep that admin database separate from the ERP database.

## Keep a non-production copy refreshed and scrubbed

A test or play copy is only useful if it looks like production. Refresh it on a schedule and before every test cycle, upgrade rehearsal or import trial.

- Use a copy-only backup, or your latest regular backup, so the refresh does not disturb the production backup chain.
- Change what the copy can reach before anyone logs in. Email and fax settings, printer and file paths, integration endpoints, payment and EDI connections and scheduled jobs should all point somewhere harmless. A test system that emails real customers or posts to a live payment gateway is the classic refresh mistake.
- Scrub sensitive data that testers do not need: bank details, card tokens, tax identifiers, employee pay and personal contact details. Do it with a script you keep and rerun, and have it reviewed by whoever owns data privacy.
- Make the copy look different, with a different company name, banner or theme where the ERP allows it, so nobody enters real work in test.
- Limit access. A scrubbed copy still holds pricing, costs and customer lists.

Ask your ERP vendor whether they provide a supported refresh procedure or utility, because some settings that point at production live in the database.

## Patch on a cadence

Microsoft releases cumulative updates for SQL Server 2017 and later on a regular schedule, monthly in the first year after a release and then every two months through mainstream support. Microsoft states that it always recommends installing the latest cumulative update for your version. Security fixes also ship as separate GDR updates.

A cadence that works for most ERP shops:

1. Check the Microsoft update list monthly for your version.
2. Confirm with your ERP vendor that the new cumulative update is supported for your ERP release. Some vendors certify specific SQL Server versions.
3. Apply it to the non-production copy first and run your usual smoke tests.
4. Apply it to production in a maintenance window, after a verified backup.
5. Plan major SQL Server version upgrades together with ERP upgrades, not separately.

Keep Windows patched on the same rhythm, and track the end of support dates for both your SQL Server version and your Windows Server version.

## What changes when Epicor hosts the system

If your Prophet 21 or other ERP system is hosted by Epicor or another provider, most of this page becomes the host's job. You usually cannot set the recovery model, schedule `CHECKDB` or change memory, and you may not have direct SQL access at all. See [How a Prophet 21 system fits together](/prophet-21/how-p21-fits-together/) for what moves to the host.

Your job becomes asking the right questions, and getting the answers in writing:

| Ask | Why it matters |
|---|---|
| What RPO and RTO does the contract commit to? | These are the numbers you would have designed for yourself |
| How often are full, differential and log backups taken, and how long are they kept? | Decides how far back you can recover |
| How do we request a point-in-time restore, and how long does it take? | A bad mass update needs a restore to a specific minute, fast |
| When was a restore last tested, and can we see the result? | The same proof you would demand of your own team |
| Can we get a copy of our database, and in what form? | Needed for a test refresh, an audit or leaving the host |
| How are non-production copies refreshed and scrubbed? | Test systems still hold production data |
| How are SQL Server and Windows patched, and how are we told? | Patches can change behavior the week before month-end |

## The checklist

### Backups and recovery

- [ ] **RPO and RTO agreed and written down.** Every backup decision depends on them.
- [ ] **ERP database in the full recovery model**, unless the business has accepted the data loss of simple recovery in writing.
- [ ] **Full, differential and log backups scheduled** to meet the RPO.
- [ ] **Backups stored off the database server**, with one copy off-site.
- [ ] **Backup jobs alert on failure** to a person who acts on it.
- [ ] **Backups encrypted and access to backup files restricted.**
- [ ] **Ad hoc backups taken as copy-only.**
- [ ] **Restore tested to another server this month**, timed against the RTO.
- [ ] **Point-in-time restore rehearsed this quarter.**
- [ ] **Restore steps and timings documented in the run book.**

### Integrity and maintenance

- [ ] **`DBCC CHECKDB` scheduled**, with failures alerting someone.
- [ ] **Plan written for a CHECKDB failure**: restore, not repair.
- [ ] **Statistics kept current**, with automatic updates on.
- [ ] **Index maintenance selective and measured**, not a blanket nightly rebuild.
- [ ] **Heavy jobs scheduled so they do not overlap** each other or business hours.
- [ ] **Vendor confirmation on file** before adding indexes, jobs or options to the ERP database.

### Configuration

- [ ] **tempdb data files equal in size and growth**, one per logical processor up to 8.
- [ ] **tempdb presized** for normal workload, on fast storage.
- [ ] **`max server memory` set**, leaving room for Windows and anything else on the server.

### Monitoring

- [ ] **Wait statistics baseline recorded** on a normal business day.
- [ ] **Query Store enabled** where your version and vendor allow it.
- [ ] **Blocking longer than a set threshold captured** for later review.
- [ ] **Disk space for data, log, tempdb and backups monitored.**

### Non-production and patching

- [ ] **Non-production copy refreshed on a schedule.**
- [ ] **Refresh script redirects email, printing, integrations and jobs.**
- [ ] **Sensitive data scrubbed** in non-production copies.
- [ ] **Current SQL Server cumulative update applied**, tested in non-production first.
- [ ] **ERP vendor support confirmed** for the SQL Server version and update level.

### If Epicor or another provider hosts the system

- [ ] **RPO, RTO, retention and restore process confirmed in writing.**
- [ ] **Date and result of the last restore test obtained.**
- [ ] **Process for getting a copy of your database agreed.**

## Next steps

If you inherit an ERP server and can do only one thing this week, run the backup history query above, then restore last night's backups to another server and time it. That single exercise tells you whether the business is protected, and it usually surfaces the next three items on this list.

## Sources

- [Recovery models (SQL Server) (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server)
- [Back up and restore of SQL Server databases (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases)
- [Restore a SQL Server database to a point in time, full recovery model (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model)
- [Copy-only backups (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server)
- [DBCC CHECKDB (Transact-SQL) (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql)
- [Maintain indexes optimally to improve performance and reduce resource utilization (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes)
- [Statistics (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics)
- [tempdb database (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database)
- [Server memory configuration options (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options)
- [sys.dm_os_wait_stats (Transact-SQL) (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql)
- [Understand and resolve SQL Server blocking problems (Microsoft Learn)](https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/understand-resolve-blocking)
- [Monitor performance by using the Query Store (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store)
- [Servicing models for SQL Server (Microsoft Learn)](https://learn.microsoft.com/en-us/troubleshoot/sql/releases/servicing-models-sql-server)
- [Latest updates and version history for SQL Server (Microsoft Learn)](https://learn.microsoft.com/en-us/troubleshoot/sql/releases/download-and-install-latest-updates)

---

Epicor, Prophet 21, P21 and DynaChange are trademarks or registered trademarks of Epicor Software Corporation registered in the United States and other countries. Kinetic is a trademark of Epicor Software Corporation. Lumina ERP is an independent consultancy and is not affiliated with, sponsored by or endorsed by Epicor.