SAP on the Microsoft SQL Server database

Environmental hygiene

Elementary errors in the manner of base management, performance tuning and current maintenance constitute the most frequent reason for gradual decline in the performance of SAP and MS SQL Server. Here, the first element appears which should be treated globally: environmental hygiene. Dependencies between Windows Server and Microsoft SQL Server constitute a basic and indivisible whole. Both elements are inseparable and mutually affect each other. It is unacceptable to treat the operational system as untouchable, as it results in a (wrong) belief that a stable system should not be moved. The same applies to the Microsoft SQL Server base, which – if not updated during its operation – will become inefficient, encumbered with errors, and with time begin to cause problems. Summing up, the first condition of efficient operation is upgrading.

It is worth paying attention to several factors that directly affect administrators’ decisions regarding relevant updates of the Microsoft SQL Server base. In particular, they include:

• compliance of the Microsoft SQL Server version with PAM (Product Availability Matrix),
• frequency of upgrading,
• reliability of correction sources,
• adequate SAP notes to each of them and an update guide issued for particular releases of the base and the SAP system,
• correction implementation plan,
• emergency plan in case of a failure,
• regular confirmed reconstruction tests,
• support of Basis consultants in case of problems.

Updates have an essential meaning for proper functioning of the Microsoft SQL Server base as they involve both small corrections, i.e. SP (Support Package) and large updates, i.e. CU (Cumulative Update). Detailed notes describe both their adjustment to the operational system, as well as dependencies between the SAP system and the base. Even though such updates only concern a layer of the database and the operational system, the SAP system requires updates with regards to the base. In particular, it is a part of the system’s kernel responsible for communication with the base, i.e. DBSL (Database Shared Library).

Increasing performance

Correctness of the SAP system implementation based on the Microsoft SQL Server base determines the quality of the environment. Usually sizing and system parameters are optimized according to system production losses, and the target performance decreases with time. This general principle applies to all types of bases and system platforms. However, it does not mean that in the MS Windows environment and the Microsoft SQL Server environment it must always be like this, and the administrator’s reaction results only from system performance problems reported by the users.

The administrator who takes care of the environmental hygiene, uses EWA reports (Early Watch Alert) and based on them they undertake adequate actions. What can be read from EWA reports about databases? It is a mine of information without delving into nuances of Microsoft SQL Server. Basically, it is worth paying attention to several Microsoft SQL Server parameters in order to increase its performance by several percent. SAPDATA data file management should take place in a way that expansion of the environment is convenient from the administrator’s perspective and optimal due to performance. What I have in mind is distribution of three key elements on the file system, i.e. SAPDATA files, SAPLOG transaction log files and the TEMPDB base.

The TEMPDB base is frequently omitted in the tuning process of Microsoft SQL Server and SAP system performance. We should not forget that in accordance with Microsoft base architecture the majority of data transactions “pass” through it. Its allocation in inadequate disc space can initially be imperceptible, however after over time, when the system has more data to process, its performance will be directly proportional to the total system performance! Its improper construction will affect the quality of implementation in such a way that when the total performance decreases, administrators will neglect its significance, thinking that if it has been good so far, it cannot be the problem. What a mistake!

As we know, each base is characterized by features such as size or structure. We cannot interfere in the TEMPDB base structure; however, we can moderate its performance using its size. Adequate selection of the base size is very strictly specified and very well documented both in SAP (OSS) notes and in Microsoft (MSDN). However, consulting practice recommends maintaining moderation and intuition in the application of recommendations.

Frequently mathematical determination of some features of bases in reality does not have to result in complete optimization, but only in its development without enhancing performance, which consequently will be perceived as a waste of time to look for the problem without clear improvement. Therefore, the question appears whether there is a chance to improve a suboptimally defined TEMPDB base? Yes, it is possible and it can be done by Basis consultants experienced in Microsoft SQL Server bases or certified Microsoft SQL Server engineers. Obviously, due to a specific procedure for changing a given base, it is also connected to implementation of a more or less complex project; however, it is such a safe operation that there are no reasons to be concerned.

A more popular method to improve base performance is optimization of transaction logs. General principles of proceedings with base transaction logs are well documented both in OSS and in MSDN, however, it is worth mentioning that their size, growth and location on the filesystem constitute their most important features. Optimization of transaction logs should be commenced from determination of the number of transactions in the system based on EWA reports. A good practice is to assume a several-percentage redundancy margin of their size, however maintaining their size above the threshold of the extension value constantly makes no sense, and only causes administrative problems. Unlike data files, files or log files can be safely shrunk, even though this operation requires several tuning like procedures.

SAP system implementation correctness based on the Microsoft SQL Server base determines the quality of the environment.

The principle of minimum redundancy also concerns setting of the expansion threshold. Determining the maximum size of transaction logs constitutes responsibility of the local administrator and mainly it depends on the possibility to use disc space. Periodical base engine monitoring should indicate that efforts should be undertaken to minimize or eliminate virtual logs (VLF) in the system, which in critical situations can lead to a significant increase of write / read operations in the disc subsystem and a drastic periodical decrease of performance.

At this point perhaps a question regarding the interpretation of base parameter values by the infrastructure administrator (sometime also the Microsoft SQL Server administrator) will appear – it is only possible through reference data analysis while maintaining the redundancy margin mentioned above.

Parameterization

Among parameterization elements, which appear after the implementation stage, there are distribution and size of SAPDATA data on the disc subsystem. The ability to properly assess the situation is essential during implementation; however, the assessment should be verified when the server environment starts to evolve. The equipment owned by the administrator always constitutes a limitation; however, it does not interfere in the flexible operation on data files during their regular working time.

In a perfect environment, data files will have an identical or nearly identical size and will be evenly distributed between disc subsystems and separated, as during system installation. The administrator’s role will be to manipulate them in such a way to ensure optimum performance results. Depending on the size of the environment, the numbers of data files vary, and their size changes dynamically, not necessarily evenly and not always at similar times. It is not a reason to undertake radical actions, however observation is always recommended.

In case of any doubts, one can always consult with the Basis consultant and the infrastructure administrator, emphasizing Basis. It directly results from the fact of a specific SAP base structure and requires – as mentioned earlier – certain intuition and experience. Observations are always conducted in two directions: based on daily base inspections, as well as cumulative EWA reports. An important role here is played by the disc subsystem itself and the RAID configuration, different for data files and the transaction log.

We should remember about maintaining two factors: performance and safety. We are talking about a proper selection of the disc matrixes and application of the RAID technology, in particular RAID 10, 5, 50, 60. In case of any doubts regarding the proper selection we recommend you contact a Basis consultant who will select an optimal disc subsystem depending on your possibilities and needs. Here, I will underline the matter of scale again – oversizing will certainly not improve the situation, but generate high costs of maintenance or purchase.

Like all databases, Microsoft SQL Server also has extended configuration possibilities. Here, we are concerned about the base engine, i.e. how do the base’s binary and working processes proceed. Numerous parameters noticeably translate into quality of the entire system functioning. Some of them are rather obvious, e.g. database instance memory management, and some are incomprehensible and treated based on “I do not touch something I know little about”. Considering the fact that some parameters are set automatically, they can be treated in such a way, however only until our environment ceases to expand or automatic settings cease to be optimal for the production system. Then, we enter the world of advanced configuration of the Microsoft SQL Server base and only then can it spread its wings. We should not be afraid of advanced configuration which seems to be complicated (in which base it is not?), and just prepare yourself well for it, including the consulting support.

Manipulating the distribution of memory, processors, users’ accounts or roles require some experience and knowledge; however, it may bring about significant changes in the quality of the entire system’s functioning. Well-documented management of memory distribution can be found on SAP Marketplace (OSS) in the form of notes suitable for a given version of the base; the same applies to other parameters or even compression (this part can be done from the SAP system level, and it is recommended to do it this way).

It is also worth mentioning about matters significant for performance, such as controlling the statistics for the base and its fragmentation. Generally, KB SAP (Knowledge Base) provides information on how to proceed with automatic statistics on the table in the base and which tables should not be included in them. A good source of such analyses is also the EWA report, which provides the procedure for proceedings with accuracy of the table name. Following the report instructions is highly recommended.

The Microsoft SQL Server base is characterized by the fact that its SAPDATA data files are fragmented. It is worth asking a consultant who will recommend proper actions, if needed. A bad practice is experimenting with settings and procedures found on some forums, as they

Tools

Microsoft SQL Server has many in-built administration tools we should use. Yes, the most common sin of negligence is failing to use the in-built tools in daily administration! The first one is Maintenance Plan management. The lack of well-prepared maintenance plans may potentially cause problems in the short term. It starts trivially – backup configuration, then cleaning the space after it, and finishing with the base consistency checkup. Then we have the Server of an Agent whose painstaking work allows us to precisely specify the system condition and predict its performance and base maintenance cycle.

Finally, it is worth mentioning the diversity of environments and their performance. It is hard to try to tune a developing environment, as developers working on a report or another solution are aware of the suboptimal environment. Obviously, we should not allow such extreme failure to occur, but it is recommended to maintain moderation in the effort made.

More frequently, there is a need to test a solution in the test environment and here we face the performance issue – depending on the type of the solution it may be necessary (we want to test report performance in the system) or marginal (we want to test the solution functioning itself and time is not critical). Here, the administrator always makes the decision, however, it is recommended to apply configuration similar to the production system configuration, if resources allow it.

The productive system should be a class of its own – here everything should be optimized, as well as possible in order to maintain maximum performance.

Virtualization, clusters and scale

In times of the omnipresent virtualization, we can boldly state that the Microsoft environment progresses with time. Adjusting it to work in virtual environments is one of the strongest advantages of the Microsoft programming development strategy, including the Microsoft SQL Server base. Obviously, the virtual environment has its own rules and requirements, which do not differ significantly from physical rules and requirements, even though the environment itself is somewhat limited by virtualization. On the other hand, virtualization makes the configuration and extension of the system scale more flexible.

I dare say that Microsoft SQL Server is able to grow with the SAP system, keeping pace with regards to performance. In each subsequent version of the Microsoft SQL Server base prognosis for efficient work in large environments are getting better, and it results from the changing architecture of the internal base, memory management mechanisms, data files and finally data structures. Both the virtualized environment and the physical SAP based on Microsoft SQL Server can work in clusters, which are natively supported by both Microsoft and SAP.

Configuration simplicity and a graphical configuration interface mean that even a less experienced administrator can administer a database cluster. The degree of complexity of SAP system installation in a complete High Availability model is medium, while its maintenance is rather simple obviously subject to knowledge of the technology. If the HA model and the cluster do not fulfill the expectations, and we would like to implement and maintain a safe system, we can also use the possibility of activating the backup base in Stand-by mode, which constitutes classic Log Shipping, however this is a subject for another article.

Based on the above guidelines and applying the generally accepted good practices specific for MS SQL Server, we can significantly improve or maintain good performance of the environment on an ongoing basis, regardless of its size. We should not be afraid of changes – all systems require changes. However, we should use verified online sources of knowledge and support from consulting companies that will help you maintain your system in good condition.