Mirroring Production SQL/SSRS server for Use as Epicor 10.2 Report Server

We are researching options to help relieve common resource contentions affecting our live production Epicor 10.2 implementation. As an example, our production departments (e.g. - shipping, receiving, sales order entry, etc) often experience extreme slowness within our Epicor application interfaces resulting from intensive reporting operations such those occurring during accounting end-of-month processes. We are curious if anyone else in the Epicor Users Community has considered or implemented a mirrored live environment solely for reporting purposes, and if so, would you mind sharing your experiences?

Welcome @woliver!!

This has been done in various ways from TransLog shipping, to replication, to nightly backup/restores via scripts. I’m sure any number of folks can respond with their methods, but can you elaborate a little more on skill/access level and environment? It would help us tailor our answers. Also, what version of SQL/Epicor.

We use two methods - one being SQL replication - one direction to another SQL server for AFR and SSRS reports for Accounting. Then we use SQL scripts to push certain data out for the EDA cloud tool. We allow most daily reports to happen in the production server, but use these other two for the more taxing reporting processes.

HTH
Mike

1 Like

Hi, Mike. Hope you’re well today and truly appreciate you for sharing! We are in the process of upgrading our Epicor version from 10.0 to 10.2.500 running on SQL Server 2017 and Windows Server 2019 inside of VMWare based virtual platform. We have broken out 1 server for SQL, 3 Epicor Application Windows servers, 2 utility windows servers (Epicor Task Agent, 3 party applications, etc.), and 3 Windows based terminal servers.

We have the skill sets in-house to accomplish, and initially plan on dedicating 1 box (virtual machine) to house Epicor and SQL Server for the sole purpose of acting as a SSRS/Epicor reporting server for Accounting and other intensive reporting requirements. Our initial intent is to replicate our live environment nightly using TransLog shipping or other similar method.

Our main intent with this post is to get a sense from others if the return on investment is worthy of setup in regards to improved performance during normal manufacturing business hours (transactional requirements).

1 Like

Sounds like your have everything well in hand. I don’t have perf stats to compare b/c we just went with the config we have knowing it was necessary. Maybe someone else can chime in with some stats… But I’ll say that multiple appservers with lots of RAM and a nice SQL server VM is the best you can do for your users. Moving heavy reporting off to another app/SQL pair is of course a good idea if you know the queries are going to be taxing the server. I’d say you’re on the right track.

We’re very similar in structure. We’ve got our appservers on 2 different VM hosts with SQL on a third VM host, all intermingled with our other VMs to spread out the burden on the VM hosts and were letting VMWare move them around with some basic rules in place to prevent a couple of scenarios. It works good for us for supporting about 60 users, 20 MES stations, and 20 remote users. We’ve not pushed Task Agent to another server yet.

Mike, it does sound like our environments are similar, and we’re grateful to you for sharing your experiences! -Thanks, Wes

Hi, out of interest, how big is your db? How many users etc? Volume of orders/shipments per day?

Hi, Brychan. Our production database is just over 400 GB and we process approximately 300 shipments a day. We generally have 100 +/- users active at any given time. Most of our “slowness” complaints (relating to simultaneous intense reporting) come from our shipping and customer service departments.

I think a little searching here will show that most folks have the same issue. Order Entry and Shipment Entry are two of the slowest functions and very little helps. There’s been some success with watching how complicated the customization layer is, BPMs and ‘tax’ related processing - especially if you allow “Invoice creation on shipment”… if I think of anything else I’ll post it.

Thanks, Mike. That has been our observation as well. Our primary goal with this post is to hear from other Epicor users about their experiences setting up a dedicated report server as an effort to help combat the issues associated with some of the slower transactional functions of every day Epicor usage. We have verified that there is a correlation between long running/intensive reporting and increased latency within core Epicor functionality. However, we’ve not found much information supporting the benefit of setting up a dedicated report server outside of this forum. It appears that Epicor does not officially promote this as a viable option, and so far yours is the first company we’ve heard from with experience implementing this approach.

1 Like

@woliver We are going live on March 7th with a similar setup to @MikeGross’s in 10.2.400.15. I don’t have live experience yet, but we have a reporting portal that will run from the SSRS in addition to Epicor reporting and that has always caused us grief in E9 and like Mike knew we had to use this approach.

1 Like

Hi, Greg. Hope you’re well today. I’m definitely grateful for @MikeGross’s input. His is the first tangible information received regarding a dedicated report server. There is definite value here in the Users Forum, and I look forward to many discussions with Mike, you, and hopefully many others in the near future. -Thanks, Wes

1 Like