SQL Server care for an on-premises ERP database

Prophet 21
Any ERP

How-toIntermediate14 min read

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.

Written for Administrators.

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.

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.

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.
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.
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.
Run DBCC CHECKDB on the restored copy. Microsoft recommends this to confirm the backup media was not damaged.
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.
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:

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.

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.

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:

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 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:

Check the Microsoft update list monthly for your version.
Confirm with your ERP vendor that the new cumulative update is supported for your ERP release. Some vendors certify specific SQL Server versions.
Apply it to the non-production copy first and run your usual smoke tests.
Apply it to production in a maintenance window, after a verified backup.
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 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

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.