Select Page
SQL Server: Turning Tips and Playing Tricks

SQL Server: Turning Tips and Playing Tricks

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:

Licensing

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.

Security

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.

Backups

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.

Performance

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.

On-Premise vs. Hosted vs. SaaS

On-Premise vs. Hosted vs. SaaS

Which is right for your business? On-Premise, Hosted or SaaS?

Technology changes at such a rapid pace that it can be hard to keep up. Today we are going to dive into the key differences of on-premise vs. hosted vs. SaaS (software as a service) and provide some great reference points that you can align best with your business.

On-Premise, Hosted, Cloud & SaaS Definitions

On-Premise Solutions

The best place is to start with a quick history lesson. Most businesses have some from of IT infrastructure that they leverage that allows them to operate efficiently and effectively. The traditional method that many businesses begin with is on-premise. In today’s world, on-prem deployment is considered a legacy approach. A legacy approach is not always wrong, as an on-premise solution does have its benefits.

Benefits of On-Premise Solutions

  • Increased security since control is controlled locally.
  • Performance can be important to users who have slower internet speeds and for when occasional software requires local installs for best performance.
  • On-premise software usually carries more features due to development cycles.

Weaknesses of On-Premise Solutions

  • Infrastructure: Average server life span is around 5 years and can be shorter depending on growth.
  • Cost: Considered a Cap-X expense and can be more expensive then SaaS counterparts.
  • Security: Endpoints, backups, patch management, etc. — all needs to be considered.
  • Future proofing: Many servers are more expensive upfront than required to account for future growth. If this is not applied correctly during initial purchase, it can lead to increases in long-term spending.
  • Remote access: Unless originally configured, users outside the office (remote workforces) will have a hard time accessing required resources.
  • Performance degradation: Over the course of time, hardware breaks down and will need to be replaced.

Hosted & SaaS Solutions

This is the future of where most businesses are heading. Hosted solutions generally come in two forms: hardware and software. A hosted server is very similar to on-premise as the main difference comes from the server physical location. This generally means that you get the same benefits as the on-premise solution but with far fewer of the weaknesses. SaaS generally refers to software without requiring the infrastructure to run the software but does not always have the same features.

Benefits of Hosting & SaaS

  • Time to deploy: SaaS-based solutions can be deployed almost immediately in most cases.
  • Expense: Upfront costs are low for SaaS.
  • Minimal Infrastructure: With SaaS solutions, hardware requirements are generally taken on by the company offering the SaaS solution. Hosted has the benefit of being able to right-size resources for the organization with the ability to scale on demand.
  • Flexibility: With both SaaS & hosted solutions, you can increase or decrease resources on the fly.
  • Security: Backups and updates are generally applied by the provider. This is not always the case and requires additional costs depending on the vendor.
  • Performance: Both solutions scale and are not affected by hardware degradation, as the underlying hardware is upgraded by either the data center or the SaaS vendor.

Weaknesses of Hosting & SaaS

  • Internet connection: Both solutions require decent bandwidth at location in order to function.
  • Transparency: Data storage with SaaS solution is beyond the control of the business owner. Hosted solutions will disclose where data is being stored.
  • Long-term costs: Upfront costs are generally lower and moved into an operating cost structure which can be higher, especially if on-prem hardware is owned.

Examples of Deployment Options

Scenario 1 – Startup / Small Engineering Consultancy

A small business with 5 people, you have 3 people working in one location, and 2 employees working remotely. You have minimal overhead, and you are expecting to grow quickly, so you need flexible and scalable systems.

What your key systems might look like:

Large Corporate Business Systems

In this example, a hosted, lightweight solution is totally appropriate. It allows you to focus on the business and not have to worry about managing an IT environment. New users can be added in minutes and can access information from anywhere with no specific hardware requirements other than an internet connection.

Scenario 2 – Established Mid-Size Engineering Consultancy

A mid-sized business with 50 people, you have 20 people working at one office location and users scattered throughout the states with no aspirations of any other offices at this stage. You have an established client base you work for and provide some specialist engineering design services which require some specific CAD software.

What your key systems might look like:

Key Small Business Systems

In this example, you probably have an existing investment in infrastructure and are probably already running a Windows network. You are probably also running an intranet and have appropriate network storage and data backup facilities. You have your own or regular IT support so you can manage your own environment. In this case, you may prefer the software to be installed on your network so you can control it. Hosting is less of a benefit for you, but you may still choose this option for convenience if your current environment is not appropriate for the software due to age or if it is already running at maximum capacity. Over the next few years, we will see a lot of businesses in this space start to run a hybrid model of on-premise and hosted software solutions.

Scenario 3 – Large International Corporate

As part of a global engineering consultancy, your systems are dictated by your owners. They are designed by an internal IT team to fit in with rules and processes as established by an internal governance team. They are very rigid and highly controlled, and most of your systems are on-premise where you have a team of internal IT technicians maintaining them.

What your key systems might look like:

Midsize Business Systems

In this example, the environment and the software are governed by internal policies. These are not agile systems, and they require a large investment in infrastructure. A massive amount of time and effort goes into establishing and maintaining these systems. Eventually, large corporates will start moving towards more agile hosted solution.

EstesGroup understands that not every business operates in the same manner. Some businesses require on-prem solutions while other businesses might be able to increase efficiency and reduce costs by moving to a hosted or SaaS-based solution.

If you are interested in finding out how you can make technology work better for your business, including which solution would fit best, we would love to help by setting up a 100% free business technology assessment. If you have any questions or are interested in find out how to make your business technology operate better, please email Chris Koplar at [email protected] or call 760-216-3452.

IT & Managed Services vs. Healthcare

IT & Managed Services vs. Healthcare

Managed Services vs. Healthcare: Similar Strategies, Similar Outcomes

I would like to start with a little self-reflection. If we are all honest with ourselves, we’d admit that no one enjoys purchasing or paying for health insurance. The process is cumbersome. There are a ton of options when it comes to purchasing health insurance, so how do I know which is the best option for myself or my family? Finally, health insurance is not exactly cheap. Most if not all of us have run into these hurdles looking at health insurance, and many of us have weighed the risk of not having insurance vs. the cost. Health insurance is investing in financial security for the unknown, and it’s shocking how closely this relates to IT and MSP services.

Business owners can view IT services in the same light as healthcare investments, and similar questions arise:

Managed IT Services vs. Healthcare Services
  • What are the associated costs? Is this cost prohibitive?
  • With so many options, how do I choose?
  • What is the risk if I do nothing?

The truth is that IT services very closely mimic health care.

Having a good MSP (Managed Service Provider) provide these critical services very much aligns with preventive health care. Going to the doctor for a routine annual checkup can head off a lot of health issues just like having an MSP can prevent a lot of IT issues. This includes hardware failure, data loss, and security issues that if left unattended would lead to larger problems down the road.

 

Critical IT services quickly justify the cost today by reducing the risk tomorrow.

Finally, IT and MSP services are critical to minimizing and reducing risk. IT services might not always be cheap, but the alternatives can be even more costly to business owners. Let’s consider this in the managed services vs. healthcare paradigm: you might not care to pay for the health insurance that covers lab panels or medications that you can currently live without, but if you ever need the tests and the treatments, enrolling in the healthcare plan today will lower your future costs and risks.

 

  • 93% of companies without Disaster Recovery that suffer a major data disaster are out of business within one year.
  • Downtime can be extremely expensive and range anywhere from $926 to $17,244 per minute.
  • On average, businesses lose over $100,000 per ransomware incident, including downtime and recovery costs.

A Managed Services vs. Healthcare Comparison Reveals Your Need for IT Expertise

Business owners who take IT seriously understand that the benefits outweigh the costs by leaps and bounds. 96% of business that have IT and MSP services in place, including BDR plans, are able to survive ransomware and fully recover operations. IT solutions and application hosting solutions can be expensive and require specialized knowledge. This is similar to choosing a specialized physician for a specific service. If you need a heart surgery, you see a cardiologist. Similarly, if you need cybersecurity, you visit an IT security specialist.

 

An IT Health Check First Appointment

Here at EstesGroup, we strive to make IT solutions simple for customers. Not only do we monitor the health of your business technology and provide the solution when something does go wrong, we also keep solutions affordable because we understand that not every business can afford or needs the same amount of coverage.

 

Imagine being able to visit a doctor and have an annual physical and have all the diagnostics to see your overall health — but at completely no cost. EstesGroup provides such a service, but instead of for your body, it is completed for your business, which is just as important. If you are interested in a free business technical assessment so you can get a handle on the health of your network, see your security risks, and get healthful recommendations, please email me at [email protected].

 

How to Create a Strategic BDR Plan

How to Create a Strategic BDR Plan

The Right Data For Backup & Disaster Recovery

For backup and disaster recovery (BDR) planning, you need more than a trusted solution. You need a data center that can’t fail. You need an IT team that won’t keep you up at night. Server room aside, you might want to back up everything, or you might want to delete outdated information. You might fear that your BDR plan will be too expensive if it becomes all-inclusive, or you might wonder if you’re cutting costs while risking a slow restore in the event of a disaster. Fortunately, many IT service plans for disaster recovery often rely on managed cloud services that allow you to scale up or down, adjusting your costs on a monthly, or even daily, basis (depending on your managed IT solution). However, BDR options abound, so let’s look at how to build the right BDR plan for your business needs.

BDR Solutions Across Devices

Choosing BDR

Things to consider when choosing a BDR plan revolve around your own personal preferences regarding on-premise backup vs. cloud backup. Where do you want your data stored? The varieties of BDR options are seemingly endless as we move toward a society that depends on cloud-based technology to enable nearly every aspect of business culture. Your BDR decisions are vital and unique to your company size, geography, climate, and more. Small business technology can help businesses struggling to grow stay competitive, even when business is slow. Larger businesses, especially manufacturers with complex ERP systems, choose cloud-based BDR for peace of mind against ever-evolving threats of cyberattacks and downtime. Across all industries and organizations, good BDR planning promotes the universally desired benefits of reduced risks and lower costs. So, information management similarities and differences in mind, where do you want to save, store and share your company data?

BDR Plan Quick Q&A

  • How much critical data do you need to fully protect?
  • How many users and devices will be affected by your backup and disaster recovery plan?
  • What are your greatest vulnerabilities (natural disasters, ransomware, malwaresocial engineering attacks)?
  • Do you know your RTO & RPO? Do you need DRaaS?
  • What is your BDR training and testing strategy?
  • Have you ever experienced data loss or data corruption? How did you respond? Did you achieve restoration?

Save Your Files & Save Them Again

Where is your data currently stored? In a web-based software? On a server in an office closet? In the basement storage area? In multiple places, including on personal devices (in light of BYOD trends)? You’re not alone if you’re struggling with data management. Likewise, you’re not alone if you’re struggling to choose a BDR solution that will be a perfect fit for your company’s future.

3 Basic BDR Roadmaps

If you want to back up everything, or if you want to back up one file, you have three basic options for saving your information.

  • Cloud services for BDR with true cloud environments and 100% virtual office infrastructure
  • Software solutions deployed on company-owned hardware that stores backups for disaster recovery
  • Hybrid cloud infrastructure that leverages cloud-based software solutions, off-site data centers and external technology specialists

Back up, Data Backup

A common concern is that a cloud-based BDR solution will cause excessive external data center usage, resulting in unforeseen ingress and egress expenses, among other unpredictable costs. The fear of creating luxury backups is real, and business owners have struggled in the past with surprise bills that read like fine-print privacy disclosures. This is why the planning stages of your business continuity strategy are critical in terms of IT budgeting. If you’re concerned about decisions regarding incremental backups, recovery point objectives, recovery time objectives, compliance, and all other backup and disaster recovery choices and expenses, then you’ll do well to first assess your core operations. If migrating to managed hosting, you might choose to waterfall excess data storage (such as old servers or unused servers) away from your cloud solutions. An IT specialist can assess your systems and make detailed server management recommendations.

Your BDR Plan Data Core

In a perfect business world, you can back up all of your data and also securely delete it at whim. Unfortunately, the burden of managing data often requires a highly skilled IT team to monitor and safeguard your BDR hardware and software. If you’re not at the point at which you can easily back up everything daily, then you’ll want to ensure you’re protecting critical information.

  • Financial data, including accounting software, invoices, payroll, transactions
  • Customer information and client data, including saved CRM information like prospect notes and lists
  • Critical data from project management activities
  • Employee information, including all HR files that enable operations
  • Paper-based communications, including image saves and scans
MSPAlliance Cyber Verify A Rating Badge Awarded to EstesGroup

A Perfect Plan For Your Business

If you need proactive or reactive backup and disaster recovery services, EstesCloud technology consultants are highly skilled at on-premise, hybrid and private cloud solutions. An IT expert can help you create a penny-wise BDR solution that keeps your data safe.

Epicor Process Set Maintenance: Bundle Up

Epicor Process Set Maintenance: Bundle Up

Scheduling ERP Processes

Batch processes have been with us since the inception of business computing. You can complete a batch of tasks as a single process for sake of efficiency. The benefits of such processes are clearly time-saving for an Epicor administrator. Batch processing allows for the automation of many tasks that would take an actual user an immense amount of time and effort to perform in order to accomplish the required manual tasks and calculations. In ERP software, the Materials Requirements Planning (or MRP) process is probably the most well known of such processes. As ERP systems have become more advanced, the need to group multiple processes to operate in harmony has become increasingly important.

Female using cloud technology on a mobile workstation

Epicor Process Bundling

In an Epicor context, there are many processes that you might want to sequentially bundle, such as following up an MRP regeneration by running the production planning and the shop schedule load graph processes, such that you can see the implication of the MRP run on material shortages and shop load respectively.

Sounds simple enough, but the problem with this scenario comes with the fact that such processes often run in the wee hours of the night, and only the most zealous members of the ERP fandom would wish to set their alarms for 3:00 AM so they can manually kick off a few ancillary processes once the MRP regen completes.

Enter Epicor’s Process Set Maintenance. Epicor process sets allow Epicor admins to bundle process runs into a single event. This allows you to sequentially run a suite of Epicor processes automatically, without human intervention. Process sets can include various differences:

  • processes
  • reports
  • executive queries

Once a process set is defined, and then attached to a system agent schedule, the related tasks are automatically processed according to the timing defined by the system agent.

Let’s look at a common issue, one that surfaces frequently for an Epicor admin. At times, you may wish to run processes in a manner that filters the actual processing. For instance, you may wish to run MRP by site, or PO suggestions according to a handful of part classes. Confusion is commonplace in handling process sets when the processes involved possess filtered activities. I’ll give you an example of the problem and an explanation of the actual behavior an Epicor admin can expect to experience when setting up and executing a process set.

Epicor Process Set Maintenance With Process Filters Enabled

Creating a process set occurs through the Process Set Maintenance screen. Once a process set is defined, individual processes can be assigned to a process set. In the example below, I created a process set:

Epicor Process Set Maintenance Screen

Next, I opened the PO Suggestion screen and configured its process parameters. Of those parameters, I set a site-specific filter:

Epicor Generating Purchase Suggestions Screenshot

Then I clicked the icon below to save the PO Suggestion process to the process set I previously created:

Save Epicor Process Set Screen

Returning to my original process set, I now see that the PO Suggestions process has been attached to the process set. Were I to go through the same actions with other processes, I could add multiple processes to this process set, and then use the “Move Up” and “Move Down” buttons to order them appropriately. But one point of confusion exists here. If I were to double-click on the process that I just added, to review its properties, the filter that I previous defined is no longer visible:

Epicor PO Suggestions

As we will see, this cosmetic issue is not detrimental to the actual execution of the processes themselves. To complete the setup of a process set, you need to assign it to a System Agent Schedule. This is accomplished through the Schedule Process Set screen. From this screen, you can select the Process Set:

Schedule ERP Process

Allowing the System Agent schedule to run according to its next run time, I can see in the Epicor System Monitor that the underlying process ran successfully:

Epicor System Monitor

Looking at the Log File related to the PO Suggestions run, I can see that the PO Suggestions process ran according to the filter that I had initially set. As you can see, the log file indicates the Epicor site that I had defined:

Epicor Process Log File

Epicor Admin Automation

In summary, while it may appear that an Epicor process loses its configured filters when added to a process set, in actuality, these parameters are retained, allowing the Epicor Admin great flexibility in automating a variety of ERP system activities.

Epicor Consulting? Epicor Managed Hosting?

Get advice from an ERP consultant or managed IT specialist. Chat with us to get a free ERP consultation or technology assessment.