A colleague recently asked for best practices concerning database maintenance in SAP environments. The top-ten advise from MSKK’s Takayuki Hoshino, which should be adjusted individually:
- “SAP database full backup daily. Technically there’s no problems to backup SAP database online, means end users or nightly batch jobs can continue to use SAP applications without problems. Everything required for SAP (business data, metadata and ABAP applications etc) is included in one database named “<SID>”. Sometimes the time to take a full backup (like a few hours) might become a problem, so the usage of SAN storage in SAP systems has become more popular because the backup time can be dramatically reduced to only seconds.
- SAP database t-log backup every 10 to 30 minutes. SQL’s recovery model setting recommended by SAP is “Full”, so if customers forget to backup t-log periodically, sooner or later t-log becomes full which makes SAP systems shut down. In general SAP customers tend to execute lots of heavy batch jobs during night, in this case SAP admins carefully need to calculate the volume size of t-log in advance.
- System partition backup in case of configuration changes. Use W2k3 ASR, third party tool like Symantec Ghost or SAN boot in case of restoring the systems partitions.
- System databases backup in case of configuration changes. Quite the same as usual systems (master, msdb, model and resource).
- Execute DBCC CheckDB periodically (Ideally before full backup). We need to understand CheckDB is a very time/resource consuming activity and puts heavy workload on SAP production systems especially on over TB SAP database. I know some customers who don’t execute CheckDB due to this reason, however sometimes commodity servers for SAP suddenly fail in an unexpected way, means there’re still possibilities to face with DB physical destructions. In my experience I have ever seen almost ten customers who lost SAP business data actually, furthermore very unfortunately two of them had failed even to get SAP database backups, which was nothing other than a nightmare. My recommendation to customers is to have a SQL log-ship secondary server on which DB admins can execute CheckDB. Ideally customers should start full database backup just after finishing CheckDB.
- Evaluate security patches monthly (and install if “really” necessary). I’d say for most of SAP customers availability is the most important thing. Especially if customers need to serve a single SAP instance globally, they don’t want to stop/restart tens of SAP servers to apply security patches. Plus, some testing in customer’s environment is definitely necessary before security patch installations. My opinion is that carefully evaluating patches and reduce the frequency of patch installations, hopefully almost to zero, is a realistic scenario for SAP customers. Unnecessary packet filtering, unnecessary service disabling and anti-virus real-time monitoring etc can also become good security measures.
- Evaluate update modules of hardware drivers and firm-wares and install if necessary. I’ve ever seen so many critical troubles due to bugs in hardware drivers and firm-wares within commodity servers. It’s really tough to find this kind of troubles from Microsoft side, and furthermore hardware companies tend not to provide commodity server customers with enough support services. So I’d say it’s a customer’s responsibility to some extent to manage drivers/firm-ware updates regularly. In this sense Datacenter Edition will find its position bringing very high level support services.
- Update statistics on the largest tables weekly or monthly. Although SAP recommends to turn on “auto update stats” which is a SQL’s functionality, SAP admins should not trust it too much because it waits until as many as 20% of records are modified to update stats automatically. Therefore I recommend to manually update statistics on the biggest tables to reduce the risk.
- Execute index defrag (online) or index rebuild (basically offline, online possible since SQL2k5) on the most important indexes. I understand most of SAP customers execute either indexdefrag or dbreindex. A example of the threshold to execute these commands is : (1) Average page density < 80%, (2) Logical scan fragmentation > 40%. This is a learning from a SAP engineer. However these days I’ve started to doubt the real performance impact of these index maintenance commands (I know that MSIT hasn’t execute any index maintenance activities for their over 2TB SAP database.)
- Health check using monitoring tool (Performance, availability etc). The unplanned downtime depends on how quickly system failures are notified to administrators and they could start the recovery processes. As for availability, SAP admins shouldn’t trust automatic failover mechanism of MSCS or DBM too much, because during failover SAP update requests and batch loads would fail and be written in the SAP error log which needs to be reviewed before restarting SAP services.”