SQL Server Configuration, Tuning & Optimization
The perspectivism of an ERP system shifts based on one’s point of reference. To an end user, an ERP system might simply be a series of screens from which one enters and extracts data. But to an ERP administrator, the view from behind the curtain might be quite different.
For an Epicor admin, the Epicor ERP application’s curtain call generally includes a number of actors: the application server, the database server, and the end user client install among them. Each of these layers requires different tricks and techniques to keep them running smoothly.
Learn about SQL Server by watching an Epicor consulting video presentation:
SQL Server Training
Server-side wisdom is not attained simply by paying for the next round. Much of this kind of information is acquired by doing. There are guide books and training materials, of course, but these cover what we tend to call the “happy path” — and anything that veers off that path is uncharted. Also, there is a certain truism about software vendors keeping their cards close. I once had an instructor shut down one of my end-of-class questions simply: “I could tell you, but I’d have to bill you for it.”
Common Epicor Admin Tasks
In that light, we thought that it would be helpful to openly discuss some of the SQL Server tools and tricks of the trade, as to assist Epicor admins and members of the user community in solving common SQL server tasks. In the above video, recorded at a past EstesGroup User Summit, Daryl Sirota, EstesGroup’s Director of Technical Services, goes over some key SQL Server considerations that cover the range of challenges that a system administrator may encounter in managing the Epicor ecosystem, including the following:
SQL Server licensing models vary, often by the number of users vs. the number of cores. An important consideration with licensing is scalability. The more you look to scale an environment, the more licenses you may require. Moreover, how you deploy SSRS vis-à-vis also adds potential licensing complications. We would recommend that you explore the options in constructing your SQL server environments to manage these licensing concerns.
Database security begins with understanding who has admin access to a given database (be it a user with physical access, a database owner, or a local SQL admin or Windows administrator). Beyond basic access, a border concern has to do with understanding how data is leaving the database — whether through replication, application access, an external API or a basic user download. Understanding how your data may leave the server is a good starting point to understanding how to safeguard it through cybersecurity or endpoint security.
Backing up your data for future disaster recovery scenarios introduces a number of challenges. Firstly, it should be clear that backing up your data is not enough. You need to test your backups to make sure they are complete and can be restored properly. Moreover, RTO and RPO considerations extend beyond an individual DB. Backing up an individual database is one thing. Another equally important element is being able to back up and restore your entire SQL server. Disasters can happen to an individual DB or to your entire server, and different strategies will be required, depending on the kind of failure.
There are a number of simple steps that can be taken to optimize performance. First, confirm that you’ve formatted drives to a 64K cluster size, to optimize efficiencies. Another step might be as simple as separating the database and transactional log volumes, due to their different IO patterns. Additional decisions, such as how you choose to allocate data, or how to separate the SQL engine from SSRS, can also impact performance.
Redundancy & Availability
Redundancy is less about backup and disaster recovery than it is about constructing a server environment that is sufficiently resilient, such that the overall system can operate even when one of its components fails. This might involve virtual machine replication, in order to provide redundant database servers. In our ECHO managed hosting environment, for instance, our SQL servers are replicated such that if SQL server were to go down, a redundant VM steps in and takes its place.
A SQL Server Maintenance Plan
Beyond the above, a number of PowerShell and SQL scripts can be put to use to complete a number of common tasks, such as copying a production environment to a test instance, truncating transaction logs, updating the task agent settings or recreating SQL replication in support of e-commerce solutions.