In most SMEs, the same SQL server does two things at once: it runs the day-to-day ERP work and tries to spit out a 15-page sales-with-margin report for the last quarter. No wonder that at 10:30 on the last Friday of the month, the whole company grinds to a halt.
We've seen the same scenario a dozen times in the last year, and it always looks the same. The CEO or the financial controller fires up their favourite report in Comarch Optima or enova365. While it runs, the warehouse clerk can't scan a goods receipt, the sales rep can't open a customer card, the accountant's invoice window hangs. Thirty minutes later the report pops out, everybody goes back to work — and waits for the next report. This is not a "slow ERP" problem. It's a problem of one disk, one SQL instance and two very different workloads chewing each other up.
The fix, which works for companies between 15 and 60 people, is boring, has been well known in the industry for 20 years, and is still rarely done in Polish SMEs: a separate reporting database that copies data from production every night (or every hour). That instance does nothing else. No invoices are issued there, no orders are entered. The only people with access are the ones who ask "how much did we actually earn last quarter".
Why reports and transactions fight
The production ERP database is optimised for short, predictable queries. One sales rep issues one invoice. One warehouse worker takes one item off stock. SQL Server keeps its indexes, row locks and cache tuned for that kind of traffic.
A management report does the opposite. It reads hundreds of thousands of rows across several tables and aggregates them by customer, product group, region. It takes locks that transactional work doesn't normally need. One bigger query can block, for tens of seconds, the exact tables that the sales module touches every five seconds.
You can't optimise your way out of this forever. At some point the only sensible answer is physical separation: let reports live on a different instance than transactions.
At one of our clients — a 22-person electrical wholesaler running Comarch Optima — the monthly "sales with margin by product group" report took 27 minutes. During that time four sales reps and two warehouse operators regularly got timeout messages. After we deployed a replica, the same report running against the copy takes 11 seconds (because it has its own indexes tuned for reporting), and the production ERP doesn't even notice someone fired it.
Three ways to set up a replica
In a Polish SME, one of these three options is usually enough. You don't need a data warehouse.
Transactional Replication in SQL Server. If your ERP sits on Microsoft SQL Server (Optima, enova365, Subiekt GT Pro, most Dynamics AX installations), this is built in. The production database continuously pushes changes to the replica — the lag is measured in seconds. Downside: it needs the Standard edition or higher and a bit of care around ERP upgrades (replication occasionally argues with schema changes).
Mirroring / Always On / log shipping. If you already have a second server for disaster recovery, you can use it for reporting "on the side". A Readable Secondary in Always On is the cleanest option — a standby copy of the database that can be queried live. The DR database and the reporting database are the same machine with two jobs.
A plain nightly copy (ETL). The simplest option, and the one we push for most 10–30-person companies. Each night a script (SSIS, PowerShell, Python, sometimes just bcp) pulls the changed records from production into a separate database. The data is 24 hours old — and for 90% of management reports that's enough. Advantage: no interference with the production ERP, the ERP vendor has nothing to say about it, and a failing job doesn't stop the business.
At that same electrical wholesaler, we went with option three. One PowerShell script runs at 02:00 every night and copies modified invoices, goods receipts and customer records into a separate OptimaReport database. The whole run takes 6 minutes. The Excel and Power BI reports point at that database, not at production. Deployment cost: two of our working days plus 1200 PLN per year for a second SQL Server Web licence. Return: no more timeout messages, no more "who's allowed to fire the big report at which hour" discussions.
What to decide before you start
Regardless of which option you pick, a few decisions need to be made up front. Skipping them is the main reason we walk into companies to find "a replica that stopped working six months ago, and nobody noticed".
- Acceptable lag. 1 second (replication), 15 minutes (log shipping), 24 hours (nightly ETL). Most management reports work on data older than a day — don't pay for real-time you don't need.
- What to replicate and what not. You don't need to copy price-change history from 2008 every night. But the full sales invoice table, invoice line items, stock and customer master data — yes.
- Who has access to the replica. Separate read-only SQL logins for the controller, the reporting people, Power BI. Nobody sits on the replica with a transactional tool — that's the first step toward strange bugs.
- Monitoring that the replica is alive. A simple email alert: "if the latest timestamp in the
Invoicestable on the replica is older than 36 hours, scream". Without that you'll learn about the outage from the controller, who will get week-old data and trust it.
What not to do
Two mistakes we see regularly end up costing more than the fix itself.
The first is "let's just build a data warehouse while we're at it". For a 20-person company this is overkill. A separate SQL database that mirrors production plus a few reporting views covers 95% of needs. A warehouse starts to make sense at tens of thousands of transactions per day across many sources — not with one Optima instance.
The second is "let's run reports straight off the backup". A backup is a backup — a copy that is supposed to work once every few years when everything burns down. Querying a backup is the surest way to find out, on the day you actually need it, that someone changed a structure and the restore doesn't work. A replica and a backup are two different functions — one for reporting, one for recovery. Don't merge them.
What next
If the CEO's favourite report in your company takes more than 2 minutes to run and during that time anybody gets a "query timeout" message — you have exactly the problem described above. You don't need to replace the ERP. You need to split two workloads across two databases, even if both run on the same box.
The first half hour we spend mapping out which reports hurt the most and which of the three options makes sense for you. That usually produces a concrete list that can be deployed in two or three working days.
The production ERP is there to handle today's work. Let the reports live next door — on a copy, used only by the people who ask about the numbers.