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!
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.
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.
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.
[pardot-form id=”856″ title=”Blog Forms Submission”]
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 SQLIn the “per core” licensing model, each 2 coresthat are available to the SQL servermust havethe 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!
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.
[pardot-form id=”856″ title=”Blog Forms Submission”]
On the first day of ECHO, my admin gave to me, some notes about my SysRowID!
Relating Epicor Extended Tables via SysRowID
As part of an ERP implementation, it’s not uncommon for companies to find the need to store information that does not easily fit into the ERP system’s existing database schema. Fortunately, Epicor ERP offers a robust ability to extend an existing database table and add fields to store custom data, to better address the requirements of an implementing company, and The Estes Group’s Echo hosting platform completely supports these critical capabilities.
On the technical side, adding UD fields to an existing table actually creates a new table in the Epicor database, a table that is structured to relate back to the original “parent table.” While two tables exist at the database level, the data model at the application level presents the two tables as a single unified entity, which allows customers to interact with the standard and custom data through BPMs, BAQs and form customizations, as if they were working against a single table.
While this capability is of great utility, there are times when an ERP administrator may have a need to compare child and parent tables, for administrative purposes. As such, it is helpful to understand the architecture for relating standard Epicor tables to their UD children at the application and database level. The following example intends to serve this purpose.
Adding UD fields to the Epicor ERP application is done though the “User Defined Column Maintenance” form. In the following example, I added a UD field to the standard Epicor ERP Part table:
Once UD fields are added to one or more tables, the administrator needs to regenerate the data model from the Epicor Administration Console on the AppServer. In the example, I regenerated the data model as follows:
Performing the above tasks adds a “Part_UD” table to the Epicor database:
It also synchronizes the application’s data model with the database:
In the following example, I went on to query the Part UD table. Note the “ForeignSysRowID” field—as you can see, the key to a UD table is based on the SysRowID of its parent, since the records of a UD table exist in a 1-to-1 relationship with their parents:
As such, if you join the Part_UD table to its parent Part table where Erp.Part.SysRowID = Erp.Part_UD.ForeignSysRowID, you will receive results similar to the following:
Whether on premise or in a hosted solution such as ECHO by EstesGroup, Epicor’s database extension capabilities are of great use to implementation teams and end users. For ERP admins, an understanding of the underlying structure can serve to assist in managing the database and the application, should the need arise.
This Holiday Season, EstesGroup would like to give you “12 Days of ECHO” tips and tricks for those ERP System Admins out there.
We understand this time of year there is a lot going on for companies, such as: year end preparations, budgeting for the next year, personnel changes, and company shifts, etc. So that being the case, we wanted to bring a bit of humor and help to your holiday.
I just need to get this off my chest – so bear with me.
First off, I’ve been doing sysadmin work for scores of years now, and the idea of backups, business continuity, and “bad guys” isn’t new. However, this week it was brought to a new and interesting head for one small business.
Rewind the clock two years and we were in the conversation with this business about where they host their “golden nuggets” of their business, what servers did what, where were the users, how did the backups fare, state of malware, web filtering protection, etc. You know, all the “normal” stuff any qualified IT provider would ask a prospective customer. “We’re fine” was the answer – they had an in-house IT guru watching all that stuff. However, they did make a (wise) decision to host their ERP solution with us.
Last week, our monitoring went suspiciously quiet, it looked like the company went on vacation, or they had fallen asleep at the keyboard. I reached out to the company, and was informed that they had been the victim of the latest ransomware attack, and all their documents were encrypted and unusable. Thankfully, since they were hosting their ERP system with us, that was safe from the attack. All their ERP data was secure but everything else they controlled was locked. Backups proved unreliable or inaccessible, so the ransom was paid. The company got lucky and the recovery key worked and they got their documents back. What they didn’t get back was Active Directory. Ouch! Nobody could login, even though their documents were back on a server, nobody could access them.
A week later, a new domain, and new profiles on everyone’s desktop, new shares, new permissions, and they were back up and running. After everything, the company is back to doing business, but it could have been a much worse situation. A critical note: the ERP system was never at risk and no ERP data was lost since that was safely stored elsewhere.
Moral of the story:
Test your backups. Not just documents, but the whole server. How long does it take to get it back? It should not be more than a few hours.
Just because you can restore files doesn’t mean you can go out, buy a new server and restore your existing workload onto a new server.
If you can’t live without it, and you don’t have the in-house expertise to manage it – outsource it! Let the pros handle the critical IT while you do what you do best: making essential product and making your business grow.
I just need to get this off my chest – so bear with me.