In recent years, Big Data, i.e. processing of huge, complex data volumes, has been gaining popularity. The processing of such large amounts of information is useful or even essential for smooth business operations, however, too large amount of data generated and maintained in a database generates costs for IT departments, makes it difficult to manage such a database, and what’s worse, leads to a decrease in performance and increases the potential downtime in case of failure. Such problems in managing the amount of data are faced every day by IT departments of companies using advanced systems for business management.
DVM – the system tune-up
For many years, BCC (now All for One Poland) has been offering and developing services aimed at the optimization and effectiveness of data volume management. The purpose of the package of services offered under the name Data Volume Management (DVM) is to develop a solution that will meet business requirements related to access to large amounts of information and at the same time will optimize the IT costs related to the infrastructure and maintenance of SAP systems.
The DVM services offered by BCC have been used by Herlitz, a company that has been cooperating with BCC in the field of IT solutions for business for many years. The key business application of the company is SAP ERP, in which finance and controlling management, materials and production management, as well as sales processes are carried out. The system was implemented in 2005.
Currently, the SAP ERP system environment of Herlitz consists of three systems: development, test (the so-called quality assurance) and production systems. The solution uses a virtual platform and runs on the Windows Server operating system and MS SQL Server database.
The main challenges resulting from the continuous increase in the volume of data maintained in the SAP system database faced by the IT department of Herlitz include:
- providing an efficient disk drive system,
- providing sufficient space for the landscape of SAP systems constantly growing in size,
- the need to ensure efficient mechanisms of backing up a large database,
- restoring a database after a potential failure with the system downtime acceptable for business,
- the long time it takes to refresh a test environment with data from the production system (due to the execution time, a client copy during a weekend maintenance break was impossible).
Ongoing SAP maintenance
Herlitz has been using the SAP administration support service at BCC virtually since launching of the system. As part of this service, during regular consultations, our specialists verify the status and condition of the SAP systems. During such an analysis, problems, potential threats and areas for improvement are identified.
Based on the DVM range, it is verified whether the database growth has an alarming long and short term trend or not. In the event of a sudden increase in data, its cause is determined and a remedial action plan is developed.
Based on many years of experience in the administration of SAP systems of our customers, we can say, for example, that the space in the database is very often wasted due to errors in the ongoing maintenance of the system (the so-called housekeeping tasks). Tables with data of a technical and simultaneously temporary nature by definition should be cleaned regularly. Examples of such data include some logs, such as a system log, logs of tasks, as well as ABAP printouts and screenshots. The cleaning of this type of data is done as part of the so-called standard tasks. During visits in Herlitz, BCC consultants verify whether these tasks are carried out in accordance with SAP recommendations and the requirements and needs of Herlitz, and make sure that there are no errors in selection variants for these tasks, which could lead to ignoring the portions of data that can be deleted.
Archiving of historical data
After several years of intensive use of the SAP system for running business on an ongoing basis, the system size usually considerably increases, while many older data are not used or are used only sporadically. Very often, data older than several years are stored in SAP due to the legal requirements (e.g. for purposes of providing them to such institutions as the Polish Social Insurance Institution or a tax office).
The mechanisms of archiving in the SAP system allow you to reduce the size of the database by transferring a selected portion of data to an external archive. Such an archive can be simply disk space or an external repository for archived data.
After transferring the data to the archive, they can be still accessed from the SAP system level. Typically, the access is made from the level of the same transaction as for the data available from the database. Sometimes, however, access may be provided from the transactions dedicated for this purpose, and the data can be presented differently.
For Herlitz, the project of archiving data from SAP was carried out in two stages. The first one, in 2014, included the archiving of data from MM and SD areas. The second stage, completed in 2015, concerned the FI, CO and PP areas.
In the first archiving run, ca. 300 GB were recovered from the database space. In the next step, the archiving was scheduled as a cyclic operation. A sequence of tasks repeated every month ensures that the oldest portion of data is archived on a regular basis. This strategy prevents excessive reaccumulation of older data in the database and eliminates the need to carry out another complex and long-term archiving process in the future.
Compression of the SQL database
Since the SQL Server 2008 version, we have had two basic types of compression:
- Row compression – it consists in storing records in words of variable length. For example, for data of the int type, where classic storing always uses 4 bytes, in the row compression, the value of 1 is stored using one byte, which saves redundant 3 bytes, and the values NULL and 0 are not stored at all. This type of compression does not affect the further use of CPU resources.
- Page compression – is independent of a data type. It consists in searching repetitive sequences of bits and storing them in a compression dictionary of each page (the page is a basic unit of data storage in MS SQL; write and read operations on the disks are carried out by pages). Compression and decompression during an operation on the data requires additional CPU operations, especially for DML operations. The compressed pages are also stored in the cache, which means that access to the cache of pages requires additional CPU resources. However, the storage of compressed pages in the cache has also a great advantage – with the same cache size and the same number of pages, the cache can store more useful data, which increases a cache hit ratio, and thus, additionally reduces operations of reading from disks and CPU load. The compression of pages allows you to achieve better compression ratio than the raw compression.
In May 2011, SAP started using for their products based on NetWeaver ABAP the page compression enabled by default for all new installations on MS SQL. As a result, the size of the database for e.g. SAP ERP EHP6 after the installation had only 30 GB.
Capture trends and react accordingly
An efficient and stable database is one of the cornerstones of an effective ERP system, and MS SQL Server is a good and efficient product that works well as a database facility of the SAP system. However, as each IT solution, also the database needs to be taken care of and supervised in order to perform its functions well. In this context, Herlitz IT department employees eagerly use the knowledge and experience of BCC (now All for One Poland) specialists. Under the administrative support agreement, during monthly monitoring visits, we analyze the performance indicators and logs of the operating systems, database and SAP in search of possible alarming factors.
The monthly rhythm of analyses and generation of EWA (Early Watch Alert) reports of the SAP system allows for trends in changes to the system load and disk usage to be well captured and enables reacting accordingly before threats emerge. With this approach, Herlitz IT department employees are able to maintain a high level of satisfaction of several dozen end users of the system, who do not experience annoying problems with performance or long response time of the system in everyday work.
The growth of the database was one of the visible trends that emerged with successive years of using the SAP system. It caused performance problems – to a lesser extent in daily work of the system users – mainly in the maintenance work carried out by the IT department, making daily backups or client copies. For example, the need to make a production client copy on the test system in a non-standard way caused a few days’ downtime of the test system, stopping the development work, and required a lot more effort from the IT department than the standard procedure.
BCC consultants suggested several parallel solutions to help deal with this problem.
One of them was the launch of archiving processes in individual SAP modules. They ran in two stages. The archiving of less sensitive data from the MM and SD areas in the first place enabled another client copy to be effectively made, and in the second stage it allowed for in-depth testing of the system with archiving more sensitive data from the FI and CO modules.
After consultation with BCC, alongside the hardware upgrade, we also decided to compress the database.
We were convinced by performance arguments, and the resulting size of the database after starting the compression exceeded our expectations. In my opinion, the compression works very well on a database using the Unicode character set, and SAP has required the support of this standard for a long time.
For the IT department of a mid-sized company, which is Herlitz, the maintenance and provision of administrative support of the SAP system on its own premises is a big challenge. The cooperation with BCCin the field of the administrative support of the system enables us to complete this task effectively and efficiently.
Dominik Zakrzewski, IT Department Head, Herlitz
Compression for SAP
In 2014, Herlitz carried out a periodic replacement of the hardware platform for the SAP environment. The project was implemented by BCC consultants. As part of the project, in addition to the preparation of a new hardware platform and Hyper-V virtual environment, SAP systems were migrated to the latest versions of the operating system and database.
When considering the launch of compression for SAP, a common concern of administrators is a risk of increased CPU resource utilization related to the overhead for the compression and decompression of data in flight, the more so that the read/write operations of the SQL cache also require compression/decompression. The experience shows, however, that the profit resulting from the significant decrease in disk I/O operations and the increased cache hit ratio exceeds the losses arising from the CPU overhead for compression, which translates into an overall increase in performance of the SAP system. This performance of the disk subsystem is usually the biggest limitation for the performance of database servers, and this is the compression that allows us to significantly reduce the need for disk I/O operations.
In the case of Herlitz, there were no concerns about possible problems with the CPU overhead for compression, because the new hardware platform, equipped with much more efficient CPUs than the old servers, provided a lot of spare computational power. In this case, it was decided to use page compression, since it gives better results.
During just one weekend of work on the production system, the following activities were carried out:
- virtualization – SAP ERP migration to a new virtual server,
- upgrading to more recent versions of the Windows Server operating system and MS SQL database,
- compression of MS SQL,
- reorganization of data files; as a result of compression, the size of the database files was reduced, which enabled the space occupied so far by the database to be recovered.
The compression results were impressive. The database size of 768 GB dropped to 158 GB of usage, which means the obtained size accounted for 21% of the original size. Even the data reorganization itself made during compression allows a part of the space to be recovered, however it is the compression that enabled such a significant decrease in the size of the database.
For all three systems in the SAP ERP landscape of Herlitz, after the completion of DVM work series (archiving and compression) the database size amounted to only 17% of the original one. Thereby, more than 1.7 TB of disk space was recovered. For the production system, the average annual growth of the database size fell by 70%. If we assumed the continuation of this growth trend, the size of the database of the production system would reach the pre-work size around the year 2042…
Herlitz is a manufacturer of branded school and office supplies. With more than 110 years of tradition, the company has been present in Poland since 1992. As part of the German Pelikan AG Group, it has been selling also the Pelikan products in Poland. The company’s headquarters and manufacturing facility are located in Baranów near Poznań. The Pelikan and Herlitz range of products comprises several thousand articles, including stationery for pupils, office supplies as well as decorative articles. Every year, the company refreshes its design and product lines in accordance with the latest trends. The extensive product portfolio allows for the offering to be adjusted to the changing needs of various target groups and customers. Pelikan and Herlitz brands are synonymous with top quality products that meet high safety standards.