Tools

Fully Tested
and Supported
Tools

ARCHIVE-TOOLS

1) MEDIFAST CASE STUDY

SQL Perform Achieves 23% Reduction in Live Data for Medifast!

"We are happy with the wins achieved as this is a perpetual process and will be repeated regularly so that we stay on top of the database growth issue and keep the system healthy and slim for the next years to come. If we let the system grow we would need to look at replacing it with tier one types of expensive products. This way, we can extend the life of NAV without frequent, expensive upgrades.”….Medifast

Intro

For more than two decades, Medifast has been working to help customers become healthier, happier, and more hopeful through safe, simple, medically formulated meal replacement products for both weight loss and maintenance. With 860 employees and millions of orders to date, a system like Dynamics NAV Enterprise Resource Planning was a must, and ArcherPoint, a Microsoft Partner with a long standing reputation for providing comprehensive business support at every level, handled their NAV deployment and support. With a live database size that was quickly creating blockage issues, a new solution had to be found, and Medifast with ArcherPoint discovered that SQL Perform has already made that solution available.

Identifying the need

Since 1980, Medifast has been creating, producing, and distributing their products through four different channels. They also have four different wholly owned subsidiaries. With electronic data of that size, the historical data, at some point, is just too large to keep and maintain both from resources point of view as well as from a performance standpoint. The larger and more successful your company becomes, the more data you have on hand. Adding data means the need for additional resources as well as lengthy performance time. While Medifast could have used ArcherPoint to deploy a SQL feature called Data Compression, it would only have dealt with half of the issue – the resources side of things. It doesn’t actually look at the quantity and weight of that data on the system.

“Medifast is a very active user of Dynamics NAV. With their high volume of customer sales, constant transfers between warehouses and daily manufacturing processes, their database was rapidly increasing in size. In some cases, the database size began to impact the transaction time of daily processes,” said Greg Kaupp, managing director of ArcherPoint.

As a result, archiving the data became an absolute must. Medifast needed the data available to meet legal and fiscal requirements, but wanted to keep the production database as small as possible to keep a clear, operational picture in an ongoing manner.

A Growing Issue

Just as SQL Perform was brought into the picture, Medifast had a database that was using 374 GB. Within just a few months after the initial review, the size had already ballooned to close to 500 GB. SQL Perform was brought into the picture because we continually provide cost-effective and dedicated performance solutions across global boundaries. Sitting in a unique niche market, SQL Perform offers a range of products and associated services that help enhance business performance for companies just like Medifast on a daily basis.

Creating a Comprehensive Solution

To meet Medifast’s needs, SQL Perform recommended the Archive-Tools suite they developed. The benefits to Medifast included the fact that the original data is never deleted, visibility remains simple, the undo function allows users to revert archiving runs, T-SQL function is fast, execution is possible within the same transaction (which makes rollback possible in case of system failure), traceability is built into the system, and any NAV table can be archived from the base application to the add-on or customized tables.

Working Together

SQL Perform worked closely with Medifast at all stages of the project while ensuring ArcherPoint was a valuable part of the process throughout to help make certain the ongoing needs of Medifast could be met after the SQL Perform solution was already in place. 

"This is not a tool, it is project method. SQL Perform has led us properly through the duration of the project, from early Proof of Concept stages, to User Acceptance Testing, followed by Deployment in Production,” said Aydan Golaszewski, the archiving project manager at Medifast.

The results were nothing short of amazing. Medifast was able to reduce their live data by 120 GB, archiving more than 295 million records in the process. By the end of 2013, they plan to archive another 70 million records, reducing the system size by another 50 GB. The process will be repeated on a yearly basis to help keep future problems to a minimum.

Reviews

The success of a project can often only be defined by the day to day users, and even there, SQL Perform’s work meant a solution that truly worked. “The archiving process was completed with minimum disturbance to daily business processes,” said Beth Graham, one happy Medifast user. “I wish we’d agreed to archive even more,” mentioned another user. A third added, “It’s great that we don’t have to scroll through old data.”

"We are happy with the wins achieved as this is a perpetual process and will be repeated regularly so that we stay on top of the database growth issue and keep the system healthy and slim for the next years to come. If we let the system grow we would need to look at replacing it with tier one types of expensive products. This way, we can extend the life of NAV without frequent, expensive upgrades,” said Carol Brown, Director of IT Corporate Applications and Support at Medifast. 

*****************************************************************************************************************************************************************

2) HOSPITAL LOGISTICS CASE STUDY

SQL Perform reduces system slowdowns by 85% for certain processes at Hospital Logistics.

“SQL Perform delivers a solution that really works. Batch processing has been reduced from one hour to 10 minutes. End-users were not told about the work and experienced no inconvenience whatsoever. However, they did notice that the system responds much faster to the slightest click. It was a small investment for an amazing ROI!”

Mark Gijbels, IT Manager, Hospital Logistics

For times each day, logistics partner Hospital Logistics processes a total of over 5,000 picking lines for its biggest customer, UZ Gasthuisberg Hospital. Because of performance problems in the SQL server, this was resulting in a blockage each time for certain processes in Microsoft Dynamics NAV, which could last up to an hour. The delay that this caused in the operating processes was initially  circumvented by workarounds. But this was a labour-intensive solution and placed a great deal of pressure on the IT department. Various attempts to solve the problem delivered little in the way of satisfying results. But by archiving and compressing data, SQL Perform succeeded in reducing the batch processing time from an hour to ten minutes, without any inconvenience to staff.

Hospital Logistics was established in 1996 as a spin-off of UZ Gasthuisberg Hospital. It provides hospitals and retirement homes with an integrated package of logistics services, ranging from food and office equipment to consumables. “Each department orders its goods electronically,” says Mark Gijbels, IT Manager at Hospital Logistics. “We assemble the list for each department on a trolley and then deliver it to the department. In addition, we also have a team of project managers. They guide and supervise the start-up processes and then assist customers in implementing improvements.”

Today, Hospital Logistics has a staff of approximately 150 and generated a turnover in excess of € 55 million in 2011. In addition to the head office in Aarschot (Belgium), the company has a further four locations in the Netherlands, where it has experienced significant growth since 2007.

Performance problems from the start

At the end of 2006, Hospital Logistics automated all of its processes (accounting, order processing, warehouse) with Microsoft Dynamics NAV. At the time, a large amount of customisation was also required and this was one of the causes of the performance problems.

“99% of our orders come in electronically straight into Microsoft Dynamics NAV,” continues Mr Gijbels. “For Belgium alone there are 4 batches a day, each with over 1,000 picking lines. We also work with batch numbers and expiry dates, which really loads up the system. As a result, each batch causes a system blockage that can last up to an hour for some operations. So it’s a major handicap. A new server helped to some extent, but because we continued to grow, we were soon back at square one.”

In a subsequent attempt, Hospital Logistics had the IT partner remove part of the customisation. A number of processes were placed in a queue during the batch, so that staff were able to keep working in the meantime. But unfortunately this was not effective enough for the long term and as time went by, some processes were back to performing badly.

SQL Perform produces a lifeline

Working with the IT partner, Hospital Logistics went looking for external solutions, which is how Mark Gijbels called in help from SQL Perform. SQL Perform provides specific applications for improving the performance and optimising Microsoft Dynamics NAV with SQL Server.  Company founder Ludo van den Ende was previously a product specialist at Microsoft and so knows this package like the back of his hand.

In September 2011, SQL Perform spent a week gathering the data traffic from Hospital Logistics. This was followed by a report setting out the problems.

“On the one hand it brought some less ideal SQL Server queries to light, while on the other the Microsoft Dynamics NAV retrieval process was a sluggish one,” recalls Mr Gijbels. “This latter problem could be resolved by installing specific indexes in the database. It’s like the classification in a library. Ludo spent one day making improvements and by the time he left, the delays had been halved. And this was achieved without any inconvenience at all.”

Powerful archiving and compression tool

But even half an hour was still too long for Hospital Logistics and continued growth meant that after a few months, 30 minutes was back up to 45. In November, SQL Perform announced the launch of a new archiving and compression product. Because Hospital Logistics had such large volumes data and had experienced major issues in the past, it made the ideal test case for the new tool.

Mark Gijbels: “SQL Perform emptied some of the tables out of the server and archived them in a different database. The whole operation only took two half-days and the result is amazing: instead of 45 minutes, the processing for each run now only takes ten minutes. The next phase involves SQL Perform installing a tool here for the automatic periodic archiving of our database. Then we will finally have seen the back of the problem.”

Amazing result

“Thanks to SQL Perform, the way Microsoft Dynamics NAV performs on some processes has improved by 85%. Earlier attempts to resolve the problem had not always produced the desired result, creating additional problems subsequently. SQL Perform has delivered a solution that really works and you don’t have to keep calling them back afterwards. What also pleased me enormously was the way SQL Perform and our IT partner worked together. Everything ran very smoothly and produced no problems during the process. Our end-users were not told about the work and didn’t experience any inconvenience. On the contrary, they now have a system that responds far faster to the slightest click. It was a small investment with a really big ROI,” concludes Mark Gijbels.