Select Page
12 Days of ECHO, Twelfth Day: My Admin Gave to Me, Ransomware 2020 the Good, Bad, and Ugly

12 Days of ECHO, Twelfth Day: My Admin Gave to Me, Ransomware 2020 the Good, Bad, and Ugly

Ransomware the hits keep coming going into 2020

 

By now, we’ve all heard about someone affected by ransomware. If it wasn’t a friend’s business, or a company you do business with, or the town you live in, or the hospital you visit – all you have to do is look at the news to see major enterprises being attacked and ‘taken out’ by this nefarious deed.  As long as people pay, the bad guys will keep using it as a tool. After all, they’re just chasing the money. 

 

So why do I title this “the good, the bad and the ugly”?  Well, if you’ve been hit, you know the bad part.  It’s expensive in both dollars and perception.  What good can come of ransomware? And besides the rising ransom amount, why is it about to get uglier! 

 

First, the good part.

 

It raises awareness that the bad guys are afoot. Wherever there’s profit, fame, political gain and more, there will be someone to play the villain (or hire them) to get the goods. Technology just made it easier. So, the good news is that you know about it!  

 

Second, the bad part.

 

Knowledge without action is a travesty. It would be even better if you acted on that knowledge and improved your defenses. Backups and disaster recovery plans are hopefully in place, but don’t assume YOUR backups and DR plans are solid.  Test them occasionally to find the problem before you need a restore. I can’t tell you how many businesses think their backups are solid to find out differently after the attack. 

 

Internet access should be a privilege, not a right. Virtually nobody should have unfettered access to any website they want. Users should get internet access based on their role in the company, not because they have a computer and a browser. ALL emails and internet access should be filtered, blocked, logged and if needed, analyzed. You need to be current on patches, antivirus, spam filtering, blah blah blah.  Sorry if I lost you there, but we’ve been beating that drum for years.  In fact, you might want to take away the internet from your users – let users surf only on their phones, on the guest wifi and NOT the corporate wifi.  Perhaps provide an internet kiosk that’s separate from the corporate network. 

 

Lastly, the ugly

 

The *really* ugly. Once you get ransomedyou can no longer assume that it’ll just lock your files up. That data of yours (oh, customer files, payroll info, vendor lists, etc.)  could have just as easily been copied to the attackers and then encrypted. So now, you don’t have your customer spreadsheet, but the bad guys do!  Imagine the horror when they go to all your clients to tell them you’ve been hacked and they have all this data about YOUR customers! If you are under HIPAA, you might as well close up shop, the HIPAA fines alone will knock a small practice down and out. What customer will solicit a company that not only leaked their information, but that same confidential information was POSTED on FaceBook? The depravity and damage can only be imagined at this time. 

 

So, if you got ransomed, and all you lost was a few (thousand) bucks, consider yourself lucky. It’s about to get a whole lot uglier.  The cities of Atlanta, Pensacola, and Baltimore will agree! 

 

Happy New Year to all and may 2020 be brighter, smarter and safer. 

If you liked reading the “Twelfth 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 ERP system or data security?  Please feel free to Contact Us and see if we can help get your bits and bytes in order.

12 Days of ECHO, Eleventh Day: My Admin Gave to Me, notes on Online Transaction Processing vs. Decision Support!

12 Days of ECHO, Eleventh Day: My Admin Gave to Me, notes on Online Transaction Processing vs. Decision Support!

Enterprise Resource Planning (ERP): Online Transaction Processing vs. Decision Support

 

So, you’ve got your ERP system up and running, and before long, the management team wants reportsdashboards and executive data out of the system. That makes perfect business sense, and most ERP systems (including Epicor) have a slew of built-in reports as well as a report designer – Epicor E10 uses SSRS, Microsoft’s flagship product for writing reports. 

 

However, there’s a potential problem. The activity of entering data, called “Online Transaction Processing” or OLTPis fundamentally different than the activity of reporting and summarizing that data, called “Decision Support”, or DS for short. Before we go further, let me also explain database locking. A lock is a basic database ‘tool’ that prevents other user from changing a piece of data that you are using. There are many types of locks, but for this discussion, a row (record) lock prevents others from editing that specific record– let’s say an invoice.  A table lock prevents anyone from editing anything in that whole table. It is our sincere desire to keep all locks as short as possible, for the longer the lock is held, the more likely it is for someone else to want that locked data. 

 

Online Transaction Processing (OLTP) locks individual records to allow parts to be sold, inventory to be adjusted, and invoices entered. Decision Support (DS) locks whole database tables to run a reportWhen managewants to see ainvoice report, nobody can be entering a new invoice while the report is being generated! While most locks are handled automatically, they cause delays and in rare cases of a deadlock, data loss. 

 

I’m oversimplifying the issue, but the long and short of it is that Online Transaction Processing (OLTP) and Decision Support (DS) fight each otherall day long.  In fact, locking contention is one of the main causes of database performance issues! There are several solutions, but a common one is to simply time the DS to occur after OLTP – that is, after the business closes. Many companies run their reports at night, not only because the system is more available, but all those pesky users aren’t entering data, locking records and causing issues! 

 

A more complex, but also common solution, is to copy the Online Transaction Processing (OLTP) database to a independent Decision Support (DS) database on a regular basis.  OLTP users get an optimized database for their activities, and the DS users can run reports all day long without locking the OLTP users out.  An ideal solution for a busy database, but it does have its downsides. You’ll need twice the disk space and a method to move the data from OLTP to DS.  Our clients use backup & restore, SQL replication, mirroring and all kinds of technology to duplicate the database and prevent the dreaded locking contention. 

 

Need help? Let us know and we’ll help you get your Online Transaction Processing and Decision Support properly segmented for best performance. 

If you liked reading the “Eleventh 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, Tenth Day: My Admin Gave to Me, Epicor Performance and Diagnostic Tool Checks!

12 Days of ECHO, Tenth Day: My Admin Gave to Me, Epicor Performance and Diagnostic Tool Checks!

SQL and the Reporting Engine

 

Epicor ERP 10 provides the Performance and Diagnostics Tool as part of your Epicor Administration Console.  While the tool is often installed when setting up an E10 solution, its often forgotten about afterwards.  The full tool has lots of capabilities, but I’d like to highlight the Config Check”. 

 

When first run, you have to go to Options-Settings and define which E10 application you are going to check, along with the Epicor username/password to access the data. 

 

Then, click the “Check Configuration” button and wait a few moments. The tool will go look at several parameters and find out which settings are Pass, Warning or Fail.  Depending on your environment, you’ll want to qualify those warnings or failures, as they might not be as disastrous as it seems.   

 

Here’s the output of one of our hosted Epicor servers running 10.2.500.  Looks like there might be something wrong with the SQL Setup. 

Using the ConfigCheck Details shows the underlying issues, in this case, there are several issues!  Some of the red lines are problems (like I might not have enough space in the SQL MDF file), while others are not (SIMPLE recovery mode is not a problem for this application)

In any case, before I start tuning, tweaking and fixing, I always export the result to Excel so I have a record of what it looked like today.  After I fix these items, I’ll re-run and re-export the check to show my client that the appropriate items were fixed.  Of course, if items are flagged but not fixed, I’ll include an explanation of why.  For example, Simple recovery mode on a SQL databases means I don’t have to worry about transaction log growth.  (See our prior post “SQL Transaction Log Maintenance”)

 

If you need details on how to correct each issue, you can drill into the ExternalLink provided. Warning – many corrections will require downtime, so while you can run the tool anytime, correcting things will likely be during a maintenance window.

 

I recommend running this tool as part of a quarterly or annual basis just to help keep your Epicor E10 system running smoothly.

If you liked reading the “Tenth 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, Ninth Day: My Admin Gave to Me, Fixes so SSRS Won’t Hog Epicor CPU!

12 Days of ECHO, Ninth Day: My Admin Gave to Me, Fixes so SSRS Won’t Hog Epicor CPU!

SQL and the Reporting Engine

 

Epicor ERP 10 requires two primary SQL functions, the SQL Engine and the Reporting Engine, also known as SSRS – SQL Server Reporting Services.   Each require a SQL licensed, but if you run both on the SAME operating system instance, you can pay for only one set of licenses. If you run the SQL Engine on one OS and SSRS on another, you must purchase TWO sets of licenses – an expensive choice.  Therefore, most clients choose to run SQL and SSRS on the same OS. 

 

This co-existence can be a problem when one or the other get resource hungry and crowds the other out.  For example, SQL (the engine) will use ALL available RAM, and starve any other application (sometimes even the Windows OS itself!).  Once of the tuning options we set is to limit SQL RAM to approximately 80% of the total server RAM.  If SSRS is running on the same OS, then we also need to leave some room for it to do it’s job. 

 

Another resource grab is for the CPU, if a poorly written SSRS report is let loose, it can take too much time and CPU and starve the SQL Engine for CPU cycles, effectively driving CPU utilization to 100% and before you know it, SQL is s-l-o-w, and Epicor responds in kind.  Users call up saying Epicor screens are frozen, reports are queueing up and not running, in general, the business comes to a full stop. 

 

Looking at Task Manager / Details and seeing SSRS service “ReportingServicesService.exe” at 100% is a dead giveaway. The quick fix is to restart that service.  Resources are released and SQL engine gets the horsepower it needs to keep running.  Unfortunately, the currently running reports fail. 

 

Microsoft used to include a function called “Resource Manager”, but they phased that out a few versions ago.  

 

The best solution is multi-pronged attacktake your pick 

  • Split SSRS off into its own server (don’t forget to buy the license!) 
  • Monitor CPU utilization on your SQL servers and alert someone if they get pegged at 100% 
  • Create a Performance Monitor Alert and Task to restart SSRS if it goes to 100% 
  • Ensure any problem SSRS reports are debugged on a test server where they won’t impact production performance. 
  • Use a 3rd party tool called “Process Lasso Server Edition” from www.bitsum.com to force SSRS to behave. 

 

In our EstesGroup Cloud Hosting ECHO hosting model, we use a combination of these solutions to ensure your Epicor ERP 10 system stays responsive. 

If you liked reading the “Ninth 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, 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.