Select Page
12 Days of ECHO, Eighth Day: My Admin Gave to Me, Tips on Epicor SQL Transaction Log Maintenance!

12 Days of ECHO, Eighth Day: My Admin Gave to Me, Tips on Epicor SQL Transaction Log Maintenance!

Epicor and Microsoft SQL Transaction Log Maintenance – Performance Tips and Tricks

 

Many Epicor 10 ERP installations suffer from a “lack of DBA”. It’s not for lack of desire, but keeping a full-time DBA (Database Administrator) on staff can be expensive.  However, a lack of DBA can ultimately wreak havoc in your Epicor environment. 

 

SQL Databases all have transaction logs. The goal of such a log is to keep the data integrity in case of data loss, corruption or other bad things.  Think of a transaction where you transfer money from checking to savings. Imagine if the balance in your checking went down, but the savings didn’t increase by the same amount!  That would be a huge problem, and SQL transaction logs ensure that doesn’t happen. The entire transaction takes place, or none of it.  Another benefit to a transaction log is that it can be used to restore a database to a specific point in time. Imagine if you could rewind the clock and restore your database to the minute BEFORE the user posted all of today’s activity to the wrong GL account, or similar mayhem.  Point-in-time recovery can be very helpful, without it you are restoring from last nights full backup, potentially losing a day of data entry. 

 

Backups Stop Running?

 

If backups stop running (for ANY reason), then SQL transaction logs start filling up. Once a transaction log fills up (or fills the disk it’s stored on), then the database stops, and all your Epicor users stop too.  Therefore, monitoring your disk space (see yesterday’s Epicor ERP 10 Server Disk Space report) is critical, but so is knowing about SQL Transaction logs, and what they mean to your business and data recovery. 

 

SQL Databases can be set to SIMPLE recovery mode, where the transaction log doesn’t grow, and can’t be used for point-in-time recovery.  This is great for databases where you don’t need recovery – Temp databases, SSRS Temp databases, Epicor Test and Training databases, and others. 

 

SQL Databases can also be set to FULL recovery mode, where the transaction log will grow (and grow) until you do a transaction log backup.  Once the backup is complete, SQL knows it can reuse that space, and the transaction log wraps and generally doesn’t grow beyond that.  It reaches statis, and that’s good. 

 

Help!

 

About once a month, we get a call from a frantic (future) client who’s Epicor is crashed, and their disk is full, and the SQL transaction logs are HUGE – often larger than the database itself.  Our EstesGroup DBA’s jump to the rescue!  We generally perform emergency surgery – set the database to simple recovery mode, truncate the logs, set the database to full recovery mode and then do a full backup.  Now, before the DBA’s out there jump up and start yelling, I know that those steps can be potentially risky.  BUT, if the application is down, and time is money, then getting things back up and running FAST is the name of the game.   

 

After the system is back and running, then we look at why the transaction logs got that big and what can be done about it.  It usually involves backing up the transaction log regularly or setting the databases to SIMPLE recovery mode (with the understanding that point in time recovery is no longer an option for those databases). 

 

And, do yourself a favor, don’t backup your transaction logs to the same volume that the logs themselves are stored on!  It’s like shooting yourself in the foot – it hurts, and you don’t want to do that right before the holiday! 

If you liked reading the “Eighth Day of ECHO” return to our main list to read all of the other “12 Days of ECHO” posts.

 

Do you have questions or need assistance with your Epicor system?  Please feel free to contact us and see if we can help get your bits and bytes in order.

12 Days of ECHO, Seventh Day: My Admin Gave to Me, Tips on Epicor ERP 10 Server Disk Space!

12 Days of ECHO, Seventh Day: My Admin Gave to Me, Tips on Epicor ERP 10 Server Disk Space!

Epicor ERP 10 Server Disk Space – Where’d It Go?

 

Do you ever wonder where your server’s disk space goes?  Many times, you might not even notice until a server crashes with an ‘out of disk’ error message, or some application stops working.  Only then might you start digging around and find that a volume is out of disk space.  Proactive monitoring is essential, and if you sample free disk space once a day for a month, Excel will tell you when you’ll run out of disk space.  However, if you proactively monitor and cultivate your disk usage, your server will reach a general stasis and disk usage will remain relatively constant.

 

A disclaimer – if you never purge old data and keep every document, sales order and schematic from the beginning of time, you’ll keep buying more disk space.  However, disk storage is cheap, management of it is expensive!

 

If your Epicor Terminal Server runs lower than about 6GB free, Epicor.exe will start throwing errors.  You don’t even have to be THAT critically low before that will start impacting functionality and performance.  Disks that run low on space suffer fragmentation, and even on an SSD drive, the work of allocating new disk space on a mostly full volume hurts!

 

So what are the common culprits in an Epicor ecosystem?

  • IIS Logs (see our prior holiday report on IIS Log Sprawl)
  • SQL Transaction Logs (report coming soon)
  • Enabling Epicor server tracing in the Admin Console can be helpful, but the C:\inetpub\wwwroot\app\server will grow with those logs quickly!
  • The share called \\appserver\EpicorData stores many files; bad BAQ’s, BPM’s, and reports can leave large files here.
  • Server administrators often to download patches and new versions, extract them and forget to remove the downloads and extractions when done with the upgrade.

 

Here’s an example over a month of a SQL server transaction log volume.  Guess what day was a bad day?  And to boot, it was at midnight, when everyone was home, not watching the servers.

How can you manage this?  Use a tool like TreeSizeFree (from JAM Software) will quickly reveal the disk hogs, and the built-in windows tool forfiles.exe will make short work of removing the old files.

 

You can also use a management tool like EstesGroup Managed Services does and gain visibility into the critical component long before it becomes a problem in your business.

 

 

If you liked reading the “Seventh Day of ECHO” return to our main list to read all of the other “12 Days of ECHO” posts.

 

Do you have questions or need assistance with your Epicor system?  Please feel free to Contact Us and see if we can help get your bits and bytes in order.

12 Days of ECHO, Fifth Day: My Admin Gave to Me Too Much RAM for My Epicor VM!

12 Days of ECHO, Fifth Day: My Admin Gave to Me Too Much RAM for My Epicor VM!

Too Much CPU & RAM for Epicor Application Server

 

Sometimes, more memory is not better.  Often, server admins will throw more resources (CPU and RAM) at a server to make it go faster.  Check our tidbit on SQL Licensing to see what that might hurt your licensing model, and in general, with SQL, the more RAM the merrier.  There is a decreasing return on investment however, and when it comes to your Epicor application server, we often see clients who over-commit resources and cause hypervisor performance issues.  Assuming you run in a virtualized world (as most of our clients do), over-committing CPU and RAM can cause the host machine to ‘thrash’ and actually run slower than if you had less resources.  For more details, search on NUMA boundaries and “memory ballooning”.  Check your Epicor application servers, if they have a lot of unused RAM and low CPU utilization, you might be a victim of over-committing resources. 

 

If you liked reading the “Fifth Day of ECHO” return to our main list to read all of the other “12 Days of ECHO” posts.

 

Do you have questions or need assistance with your Epicor system?  Please feel free to Contact Us and see if we can help get your bits and bytes in order.

12 Days of ECHO, Fourth Day: My Admin Gave to Me Tips on SQL 64k Clusters!

12 Days of ECHO, Fourth Day: My Admin Gave to Me Tips on SQL 64k Clusters!

Tips on SQL 64K Clusters and Epicor SQL Services Database Bytes

 

Microsoft SQL likes to do all its input/output in 64k chunks, but Windows likes to format hard drives in 4K chunks called “clusters”. Studies have shown that formatting the volumes that store SQL databases and transaction logs benefit from 64k clusters – up to 35% better performance!  To check what your cluster size is, open an Elevated Command Prompt and type “CHKDSK D:” (where D: is where your databases are stored).  The line with xxx bytes in each allocation unit” should say 65536, and not 4096.   

 

If you find your server admin formatted with the default 4096 allocation unit, then changing is easy – just kick everyone out of Epicor, shutdown the SQL services and backup the entire volume.  Then, reformat with 64k clusters and do a volume restore.  Restart SQL services (and your Epicor Task Agent) and let the users back in! 

 

Sound like too much for you to handle? 

Give us a call or send us a message, and our Database Admins’s would be happy to assist. 

 

If you like this tip and trick post, please read our other 12 Days of ECHO.

About the Author

Daryl Sirota has served for 35+ years in IT, both as a sole proprietor and as a senior team at System Source, and now as VP of Managed Services at EstesGroup. He loves to travel and currently resides near EstesGroup headquarters in Loveland, Colorado.

Daryl Sirota – VP, Technical Services
12 Days of ECHO, Third Day: Some Notes on Epicor ERP Auto-Login and SysConfig!

12 Days of ECHO, Third Day: Some Notes on Epicor ERP Auto-Login and SysConfig!

On the Third Day of ECHO, my sys admin gave to me, swift yet safe: Auto-Login in Epicor 10.1 and beyond.

 

Interacting with technology can become a perpetual struggle between ease-of-use and security-of-use.  The two seem at odds, such that increases in security reduce the ease-of-use, and vice versa.  I once worked for a company that increased security after a user opened an infected email attachment and blew up her computer.  Thereafter, all email attachments were cloud-scanned before anyone could open them.  Safe—but now it took an extra five minutes to view an attachment.  We used to joke to ourselves that our network admin was going over the files byte-by-byte with a pair of tweezers in one hand and a scalpel in the other.

 

That is to say, Epicor users generally like things quick and dirty, while admins like things safe and clean.  Early versions of Epicor adhered to the quick and dirty principle: In early versions of Epicor, automatically logging into Epicor, in the absence of a single sign-on setup, was relatively easy—it was as simple as plugging the user ID and password into the .sysconfig file:

<userSettings>

<!– provide values for UserID and Password to enable auto-login –>

<UserID value=”manager” />

<Password value=”manager” />

 

This capability was especially helpful for those interacting with the system via Epicor’s Manufacturing Execution System (MES) or Handheld (HH) clients.  But storing plain-text passwords in a configuration file has long been anathema to system administrators.  As such, starting in version Epicor 10.1, a number of changes were made to improve Epicor’s security architecture.  As part of this effort, the ability to store passwords as plain-text in .sysconfig files was removed.  It was replaced with a more elegant means of achieving the same ends through the application itself.  The setup requires steps from both the Epicor administrator and the end user.

 

Epicor Administrator:

 

To allow users to auto-login, perform the following steps, while logged in as a security manager:

  • Open the “Password Policy” form (located under System Setup > Security Maintenance).
  • Select the “Allow save password” checkbox and save.

Epicor End User:

 

For those who intend to utilize auto-login capability, users must save their credentials in the following manner:

  • Log into the workstation as you normally would, using your Epicor username and password.
  • From the Epicor Homepage, click the “Settings” tile:
  • Select the “Preferences” option:
  • This will open the Preferences window. Select the “Automatic sign on” checkbox and click the “OK” button to commit the changes:

The next time the user logs in, the auto-login functionality will be invoked.

 

Note: The above user steps can only be done after the Epicor admin has performed the necessary prerequisite steps, else the user will receive the error below:

As part of our ECHO Epicor managed hosting solution, we’ve helped a number of customers migrate from Epicor’s older config file-based methodology to its current auto-login configuration.  The above configuration is a one-time “set it and forget it” activity, which allows the user to utilize the auto-login functionality without issue.  In this way, Epicor has devised a solution that improves security without impeding ease-of-use—it is swift, yet safe.

 

If you liked reading the “Third Day of ECHO” return to our main list to read all of the other “12 Days of ECHO” posts.

 

Do you have questions or need assistance with your Epicor system?  Please feel free to Contact Us and see if we can help get your bits and bytes in order.

12 Days of ECHO, Second Day: SQL Licensing

12 Days of ECHO, Second Day: SQL Licensing

On the First Day of ECHO, my System Admin gave to me, SQL Licensing!

 

You probably already know that Microsoft SQL Server is required for Epicor 10 and Prophet 21, but do you know if you are in Microsoft compliance? SQL licensing can be confusing, but in most cases, it can be broken down to either “by core” or “by user”.  There is an exception for SQL Enterprise licensing on a hypervisor, but that’s a specialized case. Most smaller organizations use SQL Standard Edition, as opposed to the more expensive and capable Enterprise Edition.  Likewise, most Epicor clients use the “By core” licensing model as opposed to the “by user” model. 

 

In short, if you are running SQL In the “per core” licensing model, each 2 cores that are available to the SQL server must have the appropriate licenses – with a minimum 4 cores.  If you have more than one SQL server, then you must have a minimum of 4 cores licensed PER server!  Keep in mind that the SQL Engine and the SQL Reporting Services are both licensed software, and if they are split to different servers, they EACH must have appropriate licenses. 

 

We’ve seen instances of clients who increase their SQL Server CPU core count to see if they get faster processing, but often end up violating their license agreement and creating expensive problems when Microsoft Auditors come knocking.  Likewise, splitting the SQL Engine and the SSRS functions will increase your license count. 

 

One small consolation prize – multiple instances of SQL on the same OS do not require additional licensing beyond the first instance.  Therefore, you might find some benefit to running another SQL instance on the same server to split queries.  (See our future blog 11th Day of ECHO: Separating OLTP and DS – detecting and avoiding deadlocks) 

 

The downside is that all SQL licenses are on an honor system – the application does NOT keep track of licenses, so it’s your job to make sure you’re in compliance! 

 

Till next time, keep the holiday cheer! 

 

If you liked reading the “Second Day of ECHO” return to our main list to read all of the other “12 Days of ECHO” posts.

 

Do you need assistance managing your SQL licenses or database administration?  Please feel free to Contact Us and see if we can help get your bits and bytes in order.