One of the largest surplus, used and refurbished electrical equipment dealers in the U.S., Electric Motor Supply Company (EMSCO), is a family owned business started in 1948. With a 250,000 square foot warehouse and 16 miles of bus way, the company is able to keep most brands and styles of a variety of industrial electrical equipment right under their roof. Located just outside of Minneapolis in Fridley, Minnesota, EMSCO stocks thousands of bus plugs, transformers, circuit breakers, motor control centers, medium voltage gear, electric motors and many different sizes of switchgear.
A Disconnect with Software Vendor
For over 50 years, EMSCO has been buying and selling new, used and reconditioned electrical equipment. As a family business with very little staff turnover, much of the ever changing electrical supply inventory information was held by the individuals in the organization. The company had implemented Microsoft Dynamics NAV years ago to manage the accounting functions of the business. However, as time went by, they wanted to accomplish more in NAV than they were currently able to do. This included a desire to get the inventory information out of everyone's heads and into an inventory management solution.
According to Joe Faust, Sales and IT manager of EMSCO, “We were getting a lot of system errors and the daily functionality we needed wasn’t there for us. The vendor we had worked with to install NAV years before wasn’t able to give us the support we needed to make NAV work the way we needed it to.” Joe expanded, “In fact, we had decided to look for a new vendor and new software, as we didn’t know if NAV could accomplish what we needed.”
EMSCO started looking for a new vendor; specifically one that offered multiple product lines, was local, and who had expertise in the distribution industry. “We wanted someone who could offer us solutions for our issues, not just sell us new software,” stated Joe. “It was important that they understood distribution and could easily grasp our unusual inventory situation.” After looking at several vendors, Business Automation Specialists of Minnesota (BASM) became the clear choice.
Switching Gears Saves EMSCO from Complex and Costly System Change
“When EMSCO came to us, they thought that NAV might not be the right solution for them anymore,” recalled Ryan Ketterling, Sales Manager at BASM. “After our in-depth needs analysis, we identified that NAV would work for them and was indeed the best solution. EMSCO simply hadn’t been instructed on how to best use the system and they needed an upgrade to the latest version, with a few changes, to make it work the way the company needed it to work.”
Melissa Reichenbacher, Project Manager at BASM added, “By avoiding a major system conversion, EMSCO saved significant time and conversion costs. Upgrading allowed for a simple data conversion, and the staff didn’t have to spend time in extensive retraining.”
According to Ron Ketterling, President at BASM, “By upgrading, EMSCO was able to take advantage of new functionality in the latest version of NAV. This provided them the fastest uptime at the lowest cost, and reduced the complexity and pain often associated with changing systems. It was the easiest option for the staff as they didn’t lose their NAV knowledge.”
EMSCO’s System and Processes Transformed
With the upgraded version of NAV in place, and with the expert guidance of BASM, EMSCO finally had the functionality they had been lacking for so long. In fact, BASM did more than was planned. Because EMSCO had problems with their system due to the way it was initially set up by the previous reseller, they had developed work-a-rounds for these issues. During the course of the implementation, BASM was able to identify and correct all the errors thus streamlining these previously tedious processes.
Joe at EMSCO states that, “Some of the changes we are thrilled to finally have in place, include our ability to issue a sales-tax-only credit memo; view the effective (total) tax rate of individual customers on invoice documents as well as on the customer screen; and the improved integration between UPS and NAV, which allows us to create tracking numbers within NAV and automatically track them on the UPS website.”
For items not shipped with UPS, EMSCO printed shipping labels. However, their old NAV system printed labels for all shippers, including UPS. This caused the staff to have to scroll through all labels looking for the correct one. Not only was this time consuming, but during the busy season with seasonal staff, this frequently created shipping errors. BASM was able to change this by implementing order scanning so only the scanned order labels printed. By streamlining this process, EMSCO eliminated irrelevant order labels, saving time for regular staff and eliminating errors with seasonal staff.
Joe continued, “We are especially happy with our new quoting process. EMSCO does thousands of quotes and we needed a way to determine if a particular item had been previously quoted to a particular customer. Many times we don’t have an item number, and we need to look by type or description. BASM created a process within NAV that now allows us to look up quotes by customer or by type and description. This is a huge time saver and helps us provide the best customer service.”
During the initial needs assessment, BASM also suggested implementing EZPDF to fax and or email invoices, statements, and quotes. Customers can indicate how they want to receive these documents and it can be done on an individual or batch basis. Faxing or e-mailing invoices, statements and quotes not only saves time, but also saves a tremendous amount of money on paper and postage.
“We started this process to simply resolve the day to day challenges we were having with our system. We needed to find a way to streamline our processes and to be as efficient as possible so we could ultimately manage our inventory better,” stated Joe. “Now we are at the point where we can explore warehouse automation. By adding part numbers to our entire inventory instead of relying on individuals’ knowledge, we will be able to manage our growth through the use of technology.”
Joe finished by stating that, “Working with BASM made everything so smooth. They spent a lot of time preparing to make sure NAV was the right system, and in making sure the conversion and subsequent processes were done as efficiently and with as little disruption to our business as possible. The conversion was done over a weekend, and we were back up and running by 11:00 a.m. that Monday. From our owner’s perspective it was all perfect, and that makes my job so much easier. The BASM team responds quickly, is easy to work with, and everyone on the team is truly an expert in their field and ours!"
Click here to download the pdf version.
If your company accepts credit cards for payments, PCI compliance applies to you.
Regardless of size or industry, all companies that accept credit cards must adhere to the safeguards mandated by the Payment Card Industry Data Security Standard—referred to as the PCI DSS. 
While most companies are aware of PCI, many are unsure what it means for their business. Companies that use a third party for clearing and remittance often incorrectly assume that PCI compliance does not apply to them.
So, what are the risks of noncompliance? Beyond exposing your customers to fraud or identity theft, your business can be held responsible for the credit card company’s losses. In the event of a security breach or lack of PCI compliance, credit card institutions can assess your company higher credit card processing fees and levy fines of up to $500,000—or even bar your company from processing any credit card transactions at all. Keep in mind that this applies to all companies that accept payment by plastic—even if they don’t store any related data.
The PCI DSS is a multifaceted security standard that includes requirements for security management, policies, procedures, network architecture, software design and other critical protective measures. This comprehensive standard is intended to help organizations proactively protect customer account data. It is a group of principles and accompanying requirements, around which the specific elements of the DSS are organized:
Build and Maintain a Secure Network
• Requirement 1: Install and maintain a firewall configuration to protect cardholder data
• Requirement 2: Do not use vendor-supplied defaults for system passwords and other security parameters
Protect Cardholder Data
• Requirement 3: Protect stored cardholder data
• Requirement 4: Encrypt transmission of cardholder data across open, public networks
Maintain a Vulnerability Management Program
• Requirement 5: Use and regularly update anti-virus software
• Requirement 6: Develop and maintain secure systems and applications
Implement Strong Access Control Measures
• Requirement 7: Restrict access to cardholder data by business need-to-know
• Requirement 8: Assign a unique ID to each person with computer access
• Requirement 9: Restrict physical access to cardholder data
Regularly Monitor and Test Networks
• Requirement 10: Track and monitor all access to network resources and cardholder data
• Requirement 11: Regularly test security systems and processes
Maintain an Information Security Policy
• Requirement 12: Maintain a policy that addresses information security
Acquirer audits, which can be carried out at any time, cover the 12 areas of mandatory compliance. The failure rate for PCI certification audits is high; according to recent research by VeriSign in “Lessons Learned: Top Reasons for PCI Audit Failures and How to Avoid Them,” fewer than 30 percent of companies pass these examinations on the first try.
If you are concerned about your software PCI compliance, please feel free to call or contact us, or to complete a self-assessment questionnaire to evaluate your risk, visit: www.pcisecuritystandards.org.
Other helpful PCI resources include:
• www.pcicomplianceguide.org. A great source for FAQs and PCI Myths, Articles on various PCI topics, explanation of SAQ forms and a section to instant message questions to a PCI expert.
• www.sagepayments.com/pci
• PCI Webinar: https://trustwave.webex.com/trustwave/lsr.php?AT=pb&SP=EC&rID=50082902&rKey=57a7c878c0733489
Swiftpage is a revolutionary service that is fully integrated to Sage CRM, Microsoft Outlook, and other CRM systems. Now sales, marketing and channel partners work even better as a team. More than simply an email marketing service, SwiftPage provides companies the unique ability to take the tools of email marketing to your sales force. Marketing or sales develops core messaging and locks down key sales content, yet still allows each team member to personalize and ensure one-to-one marketing. Whether your sales force is 1 or 100, Swiftpage gives you unprecedented marketing power while helping your company achieve greater success.
-
Reach Your Contacts: Swiftpage Email Marketing gives you the tools to create beautiful email templates, send directly from your database to your contacts and track who opened and clicked on your messages.
-
Grow Your Database: Surveys and Web Forms from Swiftpage allow you to gather valuable new contacts or become more informed on your contacts with Swiftpage Surveys. And with numerous response type options and response reporting and statistics you’re sure to find the answers you’re looking for.
-
Qualify Your Leads: Swiftpage creates a ranked Call List of the most interested contacts based on your email recipients’ interactions with your message. When you send your email blast with Swiftpage you can track who opened, clicked, bounced and view graphical and detailed reports about your send.
-
Automate Your Business: Create an Autoresponder email to automatically and instantly contact your survey respondents when they complete your surveys.
We have been using SwiftPage for six months and are very happy with the results. Check out this SPECIAL OFFER …. Get a 60 day free trial of SwiftPage! Contact us today to get started.
The BASM team has been reading and discussing the book The Toyota Way for the last several months. We are going to share the lessons we’ve learned with you in this multi-part series. If you didn’t read part one or two, it is available on our website. Interestingly, the reason Toyota is having problems currently is that they didn’t follow their own processes. It’s a tough, but valuable lesson to learn.
Consider these results as you evaluate your continuous business improvement: “One technique that is part of … continuous improvement is to ask why a problem exists five times, going to a deeper level with each ‘Why?’ to get to the root cause of the problem.” The Toyota Way, page 47. Toyota engineer Ichiro Suzuki was entrusted with the task of creating Toyota’s first luxury vehicle which eventually became the Lexus. Suzuki wanted all of the characteristics of a high-end, luxury vehicle without the compromises automotive engineering said were necessary to achieve those characteristics. For example, assumptions of the day indicated that a high speed engine required a higher mass car in order to dampen the engine noise. Suzuki wanted to improve fuel efficiency which required a lower mass car. Using “Five-Why” analysis, Suzuki successfully overcame the engineering challenges of the day to produce a vehicle that sold 2.7 times more than Mercedes-Benz 300E, 420SE, and 560SEL combined in the first year.
Now, let’s get back to our original topic (to read the entire original article on our blog, click here.)
The Five Why’s – Part Three: Solving the Right Problem
In part one, you identified the root problem. In part two, you identified the underlying cause(s) of the problem. In part three, determine if all the causes should be addressed simultaneously or one at a time.
Typically, there are many threads to a problem. Unless these threads are hopelessly interwoven, it might make sense to develop a phased approach. The threads of a problem might require changing manual processes, equipment investments, employee training, process automation, etc. Attempting to tackle all the threads simultaneously can lead to over-committing resources and over-whelming staff which can lead to failure.
How do you get started? Evaluate the “investment” and “payback” on each part of the solution. Take the solution with the best ratio and implement it. Keep track of what you have left undone and set a time to deal with those issues.
Make sure to publish your findings and your solution to the whole company. You invested a lot of valuable resources. You have two important paybacks here. Not only did you solve a problem, your team has a victory. They can and should savor a good pat on the back. A more subtle payback is the new corporate knowledge; a better understanding of your business and further developed problem solving skills and team work.
Finally, just because you solved one problem, it doesn’t mean you can sit back. Improvement is a continuous event. Keep looking and keep solving. Conditions change. Customer requirements change. Vendors change. Set your goals and keep going after them. Your best days are still ahead.
A look inside the establishment of a great company
Business Automation Specialists of Minnesota, Inc. (BASM) began as a networking company in 1985, supporting the infrastructure for their clients’ accounting and manufacturing software. They quickly realized that there was a significant need in the area of accounting software and in the decade that followed, BASM began to refocus on the software side of the industry. They have since added a number of products enabling them to provide true end-to-end automation.
“Your company has helped us tremendously in moving forward with our technology goals. Our shared perspective of sound business objectives mesh well. Please use this letter as a testimonial to the experience we have had working with the Business Automation Specialists’ team.” – Belson Company, Green Bay, WI
A Solid Foundation
Any great achievement must begin with a solid foundation. One in which goals are clear, and those involved are willing to do whatever it takes to go the distance. With a commitment to meet the needs they saw within the industry, as well as to their clients’ continued business success, BASM was off to a great start. Of their success and longevity, Sharon Ketterling, General Manager for BASM reflects, “I think one of the most important factors of our business foundation has been our willingness to sacrifice personal gain to build our company – a philosophy based on Proverbs 24:27 which states, ‘Prepare your outside work, make it fit for yourself in the field; and afterward build your house.’ We then combined that viewpoint with a true concern for the profitability and longevity of the small and medium sized businesses we work with who provide jobs and security for their employees and families.” BASM’s attention to detail and passionate commitment to do whatever they can to help their clients successfully navigate the automation world; added to a concrete, sequential methodology within project management has helped them increase the likelihood for success of their clients.
“We have had a great experience working with Business Automation Specialists. I would advise anyone considering your services that choosing the wrong VAR can cost a great deal of time and money, and that it’s difficult to quantify just how much time and money can be saved by choosing the right VAR. Business Automation Specialists is definitely the right VAR for us!” – Saint Paul Port Authority, St. Paul, MN
Being a true Specialist in Your Industry
“We can back up our expertise with the hundreds of companies that we have helped,” stated Ron Ketterling, President of BASM. “It’s our job to see the big picture of our client’s business, consider the problems, causes and symptoms of their situation and then to think ahead and determine the very best options for them that will encourage their growth and the achievement of their goals. It is our sincere desire to ensure that our clients get the maximum benefit for the dollars they spend; the biggest impact for the lowest investment. We help them streamline and easily locate the data they need to make wise business decisions, without having to enter and re-enter that data into their system.” One of BASM’s strongest abilities is to find ways for their clients to increase their transaction volume without having to add staff. A great example of this is in the work they did with Belson Company. Ron recalls, “After working with us, they were able to increase their volume by 27% without hiring another employee – now that’s a great ROI.”
Anyone who has had the pleasure of working with the team at BASM can tell you that they genuinely care about their clients. Melissa Reichenbacher, Project Manager for BASM stated, “We always put the customer first. Putting that into action involves a dedication to doing what is right for them at all times. For example, if a client makes a request we first look at whether that request is beneficial to their organization, and then determine if that benefit is great enough to offset the cost. Next, we consider what the best course of action is; while there may be an obvious option - we want to make sure we look at it from every angle before moving forward. We want to do it right the first time. And finally, we stay ahead of the technological curve always learning and watching what’s coming up in our market. We want to be able to give an educated opinion on new products for our customers so they don’t end up spending money on bells and whistles that they don’t need.” Mark Langhus, Consultant and Implementation Specialist for BASM added, “We are committed to telling our clients the truth. Some resellers will tell people what they want to hear and then not be able to come through for them. We don’t see the point of doing business that way. Our clients know that they can expect a straightforward answer to their requests, and that includes whether or not a project is do-able, how much it will cost and the time it will take to complete it. And – we won’t do something for a client unless it will add value for them. I think our clients appreciate our straightforward approach.”
“We haven’t significantly changed the information we put into our system, but the information we get out of our current system is a vast improvement over our previous one. Our business management system has harnessed the power of our data. Any business owner or manager could certainly benefit from these powerful tools.” – Update Limited, Chaska, MN
“Implementation went beautifully. BASM worked well with us to make everything go smoothly. They recently devised a great sales analysis tool for us. As a result, I was able to reactivate five accounts that haven’t done business with us in years. These accounts could easily add half a million in sales this year.” – Cord Sets, Inc., Minneapolis, MN
Integrity + Honesty + Teamwork = Longevity & Success
The founders of BASM have a natural ability and wisdom in regards to running their company, and in understanding the mechanics of their clients’ businesses – approaching each one like a puzzle with multiple parts and identifying all of the pertinent details. But that is only the beginning of their true core assets and abilities. If you want to find out the true facts about a business owner, you ask the staff. Working closely with Ron and Sharon has given their team members a good look at the inner workings of BASM and why it has been so successful over the years.
Dan Eckhoff, Project Technical Lead since 1998 said, “Ron has a deeply imbedded moral conviction. He does the right thing for everyone he deals with and isn’t someone who just looks out for himself. He is able to live uninhibited because he doesn’t lie to people and this honest approach allows him to admit when he’s wrong and fix what he can. His approach has been passed down to the whole team because it works so well. A true test of his character is his expectation of reciprocation. He treats others as they would wish to be treated and expects the same in return. His support of his team even went so far as to ‘fire’ a client who was treating a team member badly.”
Barb Hedman, Project Coordinator since 2002 recalled, “I knew right away in my interview that there was something special about BASM. The integrity, character and professionalism were apparent from the moment I walked in. Some of the things I appreciate the most about Ron and Sharon are their humor, their openness to suggestions and their commitment to fairness. They are trustworthy through and through, and that’s what keeps clients coming back. They respect every person they work with and are consistently clear and direct. You always know where you stand with them.”
A Toast to Another 25 Years!
Time flies when you’re having fun! Though 25 years have passed, the team at BASM continues to enjoy what they do – helping clients get the most out of their business software investments. Ron reflects, “Success isn’t always easy. Sometimes it feels like you may not make it through and some changes can be challenging. But if we stay the course, and the client stays the course, the result will be positive and everyone will be happy.” Ryan Ketterling, Sales Executive since 2002 concludes, “We are dedicated to problem solving. We are not a sales based organization, and we view our customers as an extension of our family. Ron and Sharon have an uncanny ability to keep things going, stick to it and not take the easy way out. That’s what will keep us going for another 25 years!”
“Bernatello’s Pizza’s relationship with Business Automation Specialists has been very profitable! The products, service, support and training we have received has been prompt and of high quality. We are glad to have BASM behind us!” – Bernatello’s Pizza, Maple Lake, MN
“BASM was able to meet our expectations and complete the project on time. We were pleased with the product deliverable and have since selected BASM as our preferred vendor.” – Human Services, Inc., St. Paul, MN
With all the news lately about Software as a Service (SaaS), we are frequently asked about the benefits of each, pros and cons, costs, and how a company would decide which is best. Since every situation, company, and budget is different, there is no one-size-fits-all answer. However, we have put together a helpful comparison chart which might help you decide which is best for your company.
BASM - Software Comparison Chart
Still confused? Call us and we’d be happy to conduct a Business Needs Assessment and make a recommendation that fits your situation.
Purpose
You, as the executive or IT professional in charge, have a significant risk if you do not manage the items in this document. If you don’t have the time or understanding to make good decisions, then hire someone, either an employee or consultant who can take appropriate actions. Don’t let the fate of your company rest on chance or blind trust that your system is prepared. Parts of this document target the business owner or manager. Other portions are for the person responsible for managing SQL on an on-going basis. The article is a short read; use it as a framework to discuss these topics with your IT support providers to improve your business system.
Introduction
Microsoft SQL is a powerful, resilient database engine that can provide a stable platform for your business system. Like any sophisticated program, proper hardware, setup and maintenance are critical for success. Use this document as your “general best practices” guide based on our current knowledge level.
The discussion in this document is not some esoteric, theoretical information. Proper SQL maintenance and monitoring can prevent catastrophic failure. We setup automated maintenance for all our customers. We have found that these jobs fail for no specific reason. When these jobs fail, your company cannot work and you may lose data.
We created this document to provide your company the best performance and data integrity, since SQL contains the data for your operations and customer data. This document contains general recommendations; it is not a how-to guide. We will cover Server Selection, SQL Setup and Regular Maintenance. We also tell you other areas that you should consider.
Perspective
Our first recommendation for ‘best practices’: Question any suggestion, no matter what the source and validate all assumptions, including your own. The practical conditions encountered in each different location, continuing software changes, and hardware/software advances could greatly alter these recommendations at any time.
Our perspective is that of a vendor who supports ERP/accounting systems that may have a CRM (Customer Relationship Management) component or a standalone ERP or CRM system. Our main concerns are internal security, stability, reliability and reasonable performance to support the business operations and reporting requirements of the company. Your circumstances may be different and may have unique recommendations. Hardware, operating systems, applications, security requirements, Web access and other business requirements will affect these recommendations.
Not Covered
This discussion does not include virtualization as an infrastructure or disaster recovery method. Anecdotal evidence shows degradation of performance with virtualization of 20 to 50%, depending on the configuration and disk access. If you want to use virtual servers, discuss the pros and cons with your network and ERP vendor. Use highly qualified technical support to configure the hardware, virtual and physical environment to achieve satisfactory performance. The same advice holds if you are using a SAN as your server disk storage. Both of these are highly complex environments and can cause significant performance issues if configured incorrectly. Each one adds several layers of software that may cause problems.
We have not included an enterprise backup discussion here. You need one and SQL must be a part of the plan. Discuss this with your network support vendor. Then check for special implementation considerations with your ERP support group.
We have not included a discussion of good design and programming techniques here. Software and reporting design significantly affect performance. Poorly designed reports will take far more resources and time than well-designed reports. A poorly designed report may take hours to run, while a well-designed report may run in minutes. This difference dramatically affects both actual and perceived performance for all users on the system.
Server Selection:
This is a general framework for determining the specifics of the requirements for servers for your specific situation. Our intent is to provide the business manager or owner with the understanding to make a technical decision without becoming a technical guru.
- Separate Servers: Do not combine your SQL server with Exchange, Domain Controller, Active Directory Server. Depending on load, use separate IIS server, SQL Reporting Services and other SQL options. This means separate hardware, not virtual servers.
- Operating System: 64 bit operating Windows Server 2008 R2 for your SQL server. Make sure that your selection of operating systems includes consideration for the lowest common denominator for your applications. I.E. this recommendation is invalid if one application does not support 64 bit Windows Server.
- Microsoft SQL Version: Use the highest version of SQL supported by your applications. You may consider having multiple instances of SQL on the same or different servers if a particular application does not support Microsoft 64 bit SQL. Consider these versions of SQL for your ERP or CRM application:
1. SQL Express: Very small company, limited number of users, limited transactions; simple reporting and automation requirements
2. SQL Standard: Small to Mid-size company, more users, more transactions, more reporting and automation requirements
3. SQL Enterprise: Small to Mid-size company, high availability, low tolerance for any data loss (since last backup), more complex reporting and automation requirements
- Hard Drives: Databases are typically I/O bound (the speed of the hard disk first, the network second and the processor third). Disk speed, size and type matter more than anything else in your SQL server does. Disk drives are the slowest piece in the system.
1. Speed – pick the fastest drives you can afford. SATA drives are not as nearly as good as Serial Attached SCSI (SAS) drives. Consider using Solid State Drives for high-performance applications.
2. Use RAID 10 or Mirrored drives. Avoid any other type of RAID or disk configuration.
3. Three separate disk subsystems for operating system, database and log files are better than one.
4. If using a SAN, make sure your hardware support team knows how to configure it properly.
5. Many smaller drives are better than one large drive as this provides more throughput for reads and writes.
6. Quantity and Configuration Recommendations:
a. Operating System – Mirrored, serial attached SCSI drives
b. Database – RAID 10, serial attached SCSI drives
c. Transaction Logs – RAID 10, serial attached SCSI drives
d. Other volumes and systems – for backup, temporary files, as necessary
- RAM: More is better. For maximum performance, 4 GB more than largest database size. SQL will use RAM as cache for database.
- Network Interface:Use at least one Gigabit network interface per server, more with heavy demand. Make sure that your switches are Gigabit for your servers, preferably for users too.
- Processors: Current processors generally provide more than ample performance for SQL. That being said, don’t skimp, as you may need more processor tomorrow.
Setup
1. Separate the Program, Data and Log files as described above.
a. Beware of ‘virtual’ environments for now—anecdotal evidence suggests 20-25% performance loss through virtualization.
b. In high performance circumstances, consider moving your temp files to another physical drive.
2. Make sure virus scan leaves SQL and its data alone, including SQL backup folder locations.
3. Add a new user to control your application. Do not use SA as the user controlling your application. Creating a unique user will increase your flexibility and control over the database you will be using. This user should be the dbo (database owner) of your application database. This technique allows you to have a SQL administrator who does not have access to your ERP, Manufacturing or CRM data.
4. Remember that new databases take their location from the ‘Model’. Check after the creation of any new database to ensure that all aspects of the new database are set correctly. You can adjust the ‘model’, but it is often more trouble than it is worth because it is a single-user task.
a. Physical location
b. Type (Full or Simple)
i. Sage Pro: PROTEMP and PROSAMPLEDATA should be Simple and all others Full
ii. Microsoft Dynamics NAV: Full
iii. Sage Accpac: Full
iv. Sage CRM: Full
v. (A short digression – you may decide that you do not need to be able to restore to a point in time after your last backup. For example, your server crashes at 3:30PM and your last backup was at 1:00AM. A full database allows you to restore to the last completed transactions prior to the server crash (assuming you can get it off the disk drive). A simple database does not allow you to do so. The restore to 3:30 may not be a good idea as you may have many partially completed transactions. You might use it to find out what you did for the day and then restore the 1:00AM and reenter the transactions to make sure you enter all transactions.)
c. Collation – unless you have changed the Model, take the default. Improper Collation settings will cause very unpredictable results.
5. Defragment your data and transaction log drives before and after setting up your databases. Your SQL performance will be better on a defragmented drive. If you defragment in conjunction with growing your files sizes, you will not have to defragment these drives for a long time. Take steps to limit physical disk page fragmentation:
a. Increase database and log file sizes to deal with long-term needs.
i. Consider how large the log gets during re-indexing and how fast the data will grow. We frequently see log files one to three times the size of the data.
ii. Example:
1. Database size 10GB with 7 years data; setting database size to 15 GB will provide approximately 3.5 years data without “growing” the database. Consider setting database size at 20GB.
2. Set transaction log size at 10 GB to 15 GB depending on number of users and types of activities performed.
b. Detach databases in SQL and shutdown SQL services (all)
c. Defragment all server drives (several times needed to get true defragmentation). Consider using a special purpose defragmentation software program rather than the defragmentation utility Microsoft provides.
d. Reboot server
e. Re-attach databases
Automated Database Maintenance
1. Create many separate Maintenance Plans and create a SQL Job to run them. Create one or more Jobs as appropriate for your system, user schedule and hardware capacity. Schedule these Jobs at times when the Job will not conflict with users or each other. This may seem to be more difficult to manage one Maintenance Plan, but in the end, it will provide better flexibility and provide more assurance that SQL completes some tasks, even though some may fail.
2. Backup – Do it or Die! We suggest backing up to a local drive, and then back it up to another device. This gives you quick access to restore a database even if your latest backup is on a device that is now offsite or is on an Internet backup plan.
a. Setup a SQL backup Maintenance Plan for your databases. This will create .bak files. Backup these as a part of your Disaster Recovery Plan. Get a copy offsite on a regular basis; consult the Disaster Recovery Plan. If you do not have a Disaster Recovery Plan – develop one. The health of your business is at stake.
b. Databases:
i. System databases: backup on a regular basis, at least monthly, more often if frequent changes are made to users
ii. Company databases: back up at least nightly.
iii. Static ERP files: (such as Sage Pro ERP PROTEMP and PROSAMPLEDATA) backup monthly, could easily include these with the system tables.
c. Transaction logs
i. Setup a separate Transaction log backup that runs at least twice. The full backup above also backups up the transaction logs. However, SQL does not delete the transaction records until the next backup. As such, the log files have an opportunity to grow to great sizes if you do not add another specific job to backup the transaction logs AGAIN.
ii. Functions such as Sage Pro ERP indexing, recalculating, period closings, etc. create large log files. Manually run this SQL Transaction Plan after these actions to keep log files from growing.
iii. Note: We have encountered times where the SQL did not truncate the logs until the third backup.
3. Other Maintenance Tips
a. Delete old backups for databases (.bak) and transaction logs (.trn). Make sure you set the retention period so that at least three backups are always in the hopper (this will depend on your available drive space, do not run out of disk space because you fill the drive with backups).
b. If you have large database sizes and you are not regularly working on the weekend, don’t run backups on weekends in order to provide enough time for other maintenance (re-indexing, database integrity, etc.) to occur.
4. Re-index
a. Do you rebuild or reorganize? Rebuild defragments the index by dropping (deleting) the index and building it from scratch. Reorganize makes sure that the index is current and has all the data included in it. Consider jobs to do both at appropriate times.
b. Rebuild – do this monthly or more frequently with high transaction volume. (If you have SQL Enterprise addition, you have the option to leave indexes online while rebuilding the index.)
i. Consider this option when you can assure yourself that you can get exclusive use.
ii. The job will fail if it cannot get exclusive use and any other tasks following the rebuild will fail (depending on your settings). This is why we recommend that you create a Maintenance Plan for each task rather than many tasks in one Maintenance Plan.
c. Reorganize – Do this every night.
d. Update Statistics: include this task with either rebuild or reorganize to ensure the SQL engine has up-to-date information required to develop execution plans.
e. DO NOT FORGET THE SYSTEM TABLES!
5. Check Database Integrity
a. This task requires exclusive use. Schedule this task for a time that will not conflict with user access.
b. Suggestion: include as step or separate job after index reorganization discussed previously
6. Miscellaneous
a. The Sage Pro ERP PROTEMP database will sometimes contain orphans. Typically, environmental issues like workstation settings cause this. Any file with a creation date over a day old is probably not relevant. Manually delete tables on a monthly basis—more often if seen as a problem. Investigate workstation setups if there are often many temporary files left in PROTEMP.
b. Setup email alerts for system problems, failed jobs, etc.
Weekly Checks
Do not rely on automated systems as though they were bulletproof. They will fail for no apparent reason. (There always is a reason, it just is not apparent and investigation will determine why it failed. For example, an index rebuild will fail if someone is logged into the database when the job runs. You will have to adjust your timing or ensure that the “offender” follows company procedures and logs out at prescribed times.)
a. Did everything run successfully?
b. When did it last run?
c. Is it scheduled to run again? When?
d. Are all databases included in appropriate maintenance plans (including new databases)?
a. Is there adequate space in databases?
b. Are transaction logs staying near “empty”?
c. Are your indexes fragmented?
d. Do you have excessive Page fragmentation?
a. Is there enough free space on the volumes?
b. Are the drives fragmented?
c. Is the RAID degraded?
d. Are backups deleted per schedule?
a. Is the RAM over utilized?
b. Are there any warnings?
c. Are system updates overdue?
d. Is your virus scanner up-to-date and set correctly?
e. Do you see degradation of network traffic?
Quarterly Tasks
Backups are not of any value unless you can restore from them and have valid data after the restore. If you see a SQL backup file on a disk drive or get a message from your backup software that a backup completed successfully, consider this only a ‘quick’ check. You must verify that you can restore the data and that the data you restore is valid.
WARNING: make sure that you or the person you assign this to knows what to do. You run the risk of over-writing your live system with bad or corrupted data and programs if the backup is bad. Test your restore on a test server. (Your test system can be a virtual server.)
a. Restore both programs (from your system backup) and last night’s data (from your system backup and your local SQL backup) to your test system.
b. Have a user run the application, enter some data and run some reports. Make sure that these reports match reports run from the live system.
a. Are settings (see our Configuration Guidelines) in conformance to software vendors recommendations?
b. Are virus scanners and settings up to date?
c. Is the hardware showing signs of age or deterioration?
d. Are users putting files on local drives that should be on public drives for backup and security purposes?
e. Are there updates to your ERP or CRM that should be installed?
f. Is SQL up to date?
- Update your management team
a. System status
i. Drive space issues
ii. Purchases required
iii. Software updates & changes
b. Restore time
i. Is it acceptable?
ii. Are there system changes that can facilitate faster recovery in the event of a system or software failure?
Conclusion
SQL generally runs very well when setup properly. It is not maintenance free. If you are not comfortable with your SQL setup or with doing the maintenance and weekly checks, we provide this service on a contract basis. (Send us a message, www.bautomation.com/contact or give us a call 877-571-8580 or 763-571-8580) Do not risk your company’s data and livelihood by ignoring this task until it is too late.
For a .pdf version of this article, click here.
40% of PC’s are currently running Windows XP – service pack 2. Microsoft, however, is no longer supporting it. So what should you do? The options include doing nothing, which could be problematic; especially long term, or upgrading to XP service pack 3 OR Windows 7. It’s important to note that there are pros and cons to both. Regardless,
talk to us before you do anything. We’ll be happy to review your specific situation and make sure your software is compatible with an upgrade.
You know that successful project management is the key to integrating sustainable improvements in your organization. Learn how to ensure that your project succeeds, whether technology related or not. Click here to watch our pre-recorded project management webinar, “4 Questions to Help you Avoid a 68% Chance of Project Failure”, on our website at www.bautomation.com.
This webinar was presented by Gene Heupel who spent 30 years in Project Management. His diverse projects range from Lean Sigma for manufacturers and distributors to nuclear power plants. In this 1 hour presentation, he discusses 4 key questions distilled from his experience.
1. What does project success look like?
2. How can I help the project succeed?
3. What makes projects fail?
4. What questions should I be asking?
These answers are critical to providing the foundation necessary for your project team to join the 32% of successful projects.
Granite City Tool has been a manufacturer and distributor for the stone industry since 1885 and is now in its third generation of same-family ownership. With nearly 120 years of experience in the stone tooling business their longevity can be attributed to having built a solid reputation for providing customers with high-quality products through reliable and expedient service. Headquartered in Waite Park, Minnesota, with distribution centers in the southeast and centers in the south opening soon, customers in most parts of the U.S. can receive shipments in one to two days.
Expansion, via a company web-store, allowed Granite City Tool to become a drop ship partner with Amazon. As orders grew, so did the amount of time needed to enter the online orders into the company’s Sage Pro software system. According to Todd Wanchic, Granite City Tool’s Webmaster and IT specialist in charge of online sales, “We process 15 to 30 orders per day, which means on Mondays we could have 45-90 orders to handle. This could easily take me 2-3 hours every Monday morning, plus an hour per day the rest of the week.”
With almost an entire workday per week taken up with manual data entry, Todd knew there had to be a better way. After calls to their Pro reseller went unanswered, Todd contacted Sage directly in search of a solution. Sage, in turn, directed Todd to Business Automation Specialists of Minnesota (BASM).
According to Melissa Reichenbacher of BASM, “Granite City Tool needed an order import application written to take the web-store files and import them directly into Pro.” The BASM team met with Todd, determined the company’s needs, and provided a project plan and quote to complete the job. Granite City Tool approved the plan and BASM got to work.
Todd proclaimed, “I could tell BASM knew what they were doing. Not only do they know Sage Pro, but they know business, business processes, and how to automate those processes.” Todd continued, “Thanks to BASM, the order transfer process is now automated, and I can’t get over what I’m able to do with the time it has saved me.” Because Granite City Tool can now process orders so much faster, Todd has been able to focus on increasing the amount of drop ship traffic. “In just the first month since the order import application went live, I was able to increase stock and we went from being able to handle 500 orders per month to processing 648 orders, and I know that will continue to increase.”
Todd stated that with the time saved and increase in orders, the cost for the import tool paid for itself in just two weeks. “I’ve been at Granite City Tool for five years manually transferring orders from the web-store to Pro. I wish we had met BASM five years ago. They knew what they were doing and did what they said they would. BASM completed the project on time, on budget, and without any issues. That is unheard of these days!”
Todd and Granite City Tool are looking forward to working with BASM on their next project, an order import application for their e-commerce platform.
Business Automation Specialists only provides services and products to our clients that will offer a return on their investment. If you are interested in results like those outlined in this Success Study, we invite you to contact us today.