Are you interested in getting certified as SAP HANA database administrator? In this blog, I will explain how you can prepare for the SAP Certified Technology Associate - SAP HANA certification exam. This exam covers the traditional DBA topics like monitoring, operations, security, backups, etc.
The SAP Certified Technology Associate - SAP HANA certification is a prerequisite for the certification SAP Certified Technology Specialist - SAP HANA Installation, which you will need if you wish to perform SAP HANA installations as part of the Tailored Data Center Integration (TDI) program.
This blog is part of a series.
- SAP Certified Technology Associate - SAP HANA
- SAP Certified Technology Specialist - SAP HANA Installation
About the Certification
At the time of writing there are two editions of the SAP Certified Technology Associate - SAP HANA certification:
Typically you would go for the most recent exam: 141, i.e. 1st edition 2014. There is not a lot of difference between both exams, the distinction concerns mainly the topic database migration using the Database Migration Option (DMO) of the Software Update Manager (SUM) tool, which is new for exam edition 141.
For the latest information, see the blog by Tim Breitwieser on SCN: SAP HANA Education: Course & Certification Program 2014. The SPS 08 edition can be expected at the end of this year.
There are 80 questions divided over 10 topic areas. Cut score = 59%, which means that you need to answer at least 47 questions correct. Below the different topics and their relative weight.
10 questions or more (The Big Four)
- Monitoring - Set-up and execute effective monitoring of SAP HANA using DBA Cockpit, SAP HANA Studio, and SAP Solution Manager.
- Operations - Design and implement an administration and operation strategy for SAP HANA, e.g. transport management, patching and updating, etc.
- Security and Authorization - Describe the authorization concept of SAP HANA and implement a security model using analytic privileges, SQL privileges, pre-defined roles and schemas. Perform basic security and authorization troubleshooting.
- System architecture - Design a system architecture for a SAP HANA implementation including hardware sizing, network requirements and integration with existing architecture.
6 to 10 questions
- Installation - Evaluate prerequisites for a SAP HANA installation, verify hardware and operation system, and describe the installation and post-installation tasks.
- Troubleshooting - Troubleshoot SAP HANA and system performance by debugging SAP HANA, analyzing system locks, system files, and traces.
6 questions or less
- High Availability and Disaster Tolerance - Design and implement a strategy for high availability and disaster tolerance.
- Data Provisioning - Describe possible scenarios and tools for replicating and loading data into SAP HANA from different data sources, e.g. SAP Landscape Transformation (SLT), SAP Data Services, or Direct Extractor Connection (DXC).
- Backup and Recovery - Design and implement a back-up and recovery strategy.
- Database Migration to SAP HANA - Prepare and execute database migration to SAP HANA using DMO.
When you prepare of the exam, best to focus on The Big Four topics first. When I took the exam, there were 13 questions just on the topic of monitoring, whereas the four topics in the last category combined only had slightly more: 18 questions. Of course, your mileage may vary, but this may give you an idea.
The main resource is the SAP Education training HA200 - SAP HANA - Installation & Operations. This 5-day training covers all the topics mentioned above. Most topics correspond directly to the chapters from the course, e.g. Operations (Unit 8) or Backup and Recovery (Unit 9). Some topics are bundled (Security) or split (Monitoring and Troubleshooting). The only exception is the topic Data Provisioning, which is covered in the HA100 SAP HANA Introduction training.
Because of the direct relation of the exam with the HA200 training, participation is certainly recommended. However, it is not a requirement. I passed the first edition of the exam, the now retired E_HANATEC1, just by reading the SAP HANA documentation and working with the product. I have to say, I was a bit surprised at the time by the number of questions on topics like DBA Cockpit, CTS+, SAP Solution Manager, etc. While this material is clearly covered in the HA200 course with exercises and all, you will have to look for it a bit in the SAP HANA guides. So if you decide to skip the HA200 training, make sure you do not skip those topics.
Additional resources as preparation for the exam (in order of importance) on the publicly available SAP Help Portal (help.sap.com):
- SAP HANA Administration Guide (612 pages)
- SAP HANA Security Guide (132 p.)
- SAP HANA Troubleshooting and Performance Analysis Guide (140 p.)
- SAP HANA Master Guide (72 p.)
- SAP HANA Server Installation and Update Guide (108 p.)
- SAP HANA Developer Guide Chapter 12. Implementing Lifecycle Management
- DBA Cockpit for SAP HANA (HTML)
- Installation Guide – Trigger-Based Data Replication Using SAP LT Replication Server for SAP HANA (23 p.)
- Application Operations Guide – Trigger-Based Data Replication Using SAP LT Replication Server for SAP HANA (76 p.)
- SAP HANA Direct Extractor Connection Implementation Guide (40 p.)
The Database Migration Option (DMO) of the Software Update Manager (SUM) tool is not included in the SAP HANA documentation set but part of the System Landscape Toolset on the SAP Service Market Place (requires login). A good start is the SCN document Database Migration Option (DMO) of SUM. There are only a handful of questions about this topic; no need to attend the 2-day course HA250 Migration to SAP HANA using DMO training for the exam.
Note that the SAP Help Portal on http://help.sap.com/hana_platform only shows the latest documentation, SPS 08 at the time of writing. For SPS 06 and SPS 07, you need to go to the SAP Service Marketplace on http://service.sap.com/hana.
You also may want to take a look at SAP note 1905389 - Additional Material for HA200 and HA200R. This note contains additional information and documents. Most of the SAP Notes mentioned are also listed in the Server Installation and Update Guide under Important SAP Notes.
SAP HANA Academy
To help you prepare for the exam, I have recorded some tutorial videos on SAP HANA administration topics.
- Playlist Backup and Recovery discusses concepts and shows how to backup and recover the SAP HANA database using Studio and command line.
- Playlist Monitoring SAP HANA shows the different tools available and how to use them.
- Playlist SAP HANA Installations SPS 07 discusses concepts and shows how to install the SAP HANA server.
- Playlists SAP HANA Administration and Administration SPS 05 show miscellaneous DBA topics. An update is being prepared.
On the certification page, a link to a PDF with sample questions is included. Below I marked the answers in bold and included a reference to the source with some tips and to do's.
1. Which of the following can you use to analyze an expensive SQL statement? There are 2 correct answers to this question.
a. Open the Plan Visualizer.
b. Open the Plan Explanation.
c. Open the hdbcons tool.
d. Open the SQL Plan Cache.
Source: This information can be found in the Troubleshooting and Performance Analysis Guide: When you have identified a critical SQL statement and identified its overall key figures from the SQL plan cache analysis you can have a closer look at the actual runtime behavior of the SQL statement.The following tools can be used for a more detailed analysis:
- Plan Explanation - Creation of an execution plan
- Plan Visualizer - Detailed graphical execution plan
The hdbcons tool serves another purpose and is documented in the Administration Guide. See also SAP Note 1786918 and note 1758890 - SAP HANA: Information needed by Product/Development Support.
The SQL Plan Cache is documented in the Administration Guide and the Troubleshooting and Performance Analysis Guide: As the SQL plan cache collects statistics on the preparation and execution of SQL statements, it is an important tool for understanding and analyzing SQL processing. For example, it can help you to find slow queries, as well as measure the overall performance of your system.
Personally, I find this question to be a bit tricky. The idea is that the SQL Plan Cache should be used to identify expensive statements while the Plan tools are to analyse. However, the documentation is less rigid on the distinction. Fortunately there are not too many questions of this kind.
To do: I can highly recommend to read the SAP HANA Troubleshooting and Performance Analysis Guide. Monitoring and troubleshooting is an important topic in the exam accounting for almost 25% of the questions. Make sure you are familiar with the tools by trying them out on your own system.
2. You are reviewing the execution plan of an SQL statement. You want to find out which plan operators (POPs) have been executed in parallel and for how long each of them has been active. How can you accomplish this?
a. Use the Visualize Plan functionality in the SAP HANA studio.
b. Use Job Progress in the SAP HANA studio.
c. Use Performance Trace in HDB admin.
d. Use EXPLAIN (graphically) in the DBA Cockpit.
Source: There is actually no reference to plan operators (POPs) in the SAP HANA documentation but by elimination (see below) the Visualize Plan functionality of the SAP HANA studio is the only right answer. The tool is documented in the Troubleshooting and Performance Analysis Guide, as mentioned above.
One reference I found to POPs is in a response to a question by CSA Expert Lars Breddemannin this forum post: The SAP HANA Engines? As Lars also wrote a presentation on Understanding SAP HANA Performance he may just have come up with the question. Lars?
You can certainly monitor the progress of jobs using HANA Studio but this will not give you any information about the execution plan of an SQL statement. Job monitoring is documented in the Administration Guide.
The mention of HDB admin is a bit curious. When executed from the command line as <SID>adm user and with a X-Windows environment available it will start a UI similar to the TREX admin tool used for TREX/BWA. The tool is not documented for HANA, hence not supported, but in case you are interested, see the blog How to use HDBAdmin to analyze performance traces in SAP HANA by John Appleby.
About the EXPLAIN function in the DBA Cockpit, see the DBA Cockpit for SAP HANA documentation. The tool is similar but does not work with plan operators.
To do: Did I mention that monitoring and performance analysis is an important topic? Same advice as with question number one: Read the guide.
3. Which of the following update scenarios can be selected for execution in the SAP HANA lifecycle manager? There are 2 correct answers to this question.
a. Perform automated updates of SAP HANA and SAP HANA components
b. Apply Support Package Stacks
c. Update the SAP HANA studio on local machines
d. Update SAP HANA replication technology components.
Source: SAP HANA lifecycle manager is documented in the SAP HANA Update and Configuration Guide (SPS 07). This guide has been merged with the installation guide for SPS 08 as there have been some significant changes (see above under Resources where to download the previous documentation set).
The lifecycle manager UI can be displayed inside SAP HANA studio or a browser and there is also a command line interface (HLM), not to be confused with the SAP HANA lifecycle management tools, documented in the LCM Tools Reference. Below a print screen with the different menu options.
You can apply complete support package stacks (SPS) or update single components to a certain revision. This can be automated in the sense that the tool will download and apply the update after some user interaction. You cannot fully automate a scheduled update with this tool.
You can use lifecycle manager to update the SAP HANA studio version on the central update site, typically this will be the HANA server. However, you cannot use the tool to update local installations. Updating local SAP HANA studio installations is self-service. Users can configure studio to check for a new version with the update site at startup or at an interval. However, they will need interact with a dialog to perform the update.
SAP Replication Server (SRS) and the SAP LT Replication Server (SLT) are examples of replication technology components. SRS is Sybase technology, SLT is Netweaver-based. You cannot use SAP HANA Lifecycle Manager to update these components.
How to perform automated updates of SAP HANA and SAP HANA components using SAP HANA lifecycle manager
To do: It certainly helps to be familiar with installing SAP HANA for this type of questions. I can recommend to get yourself a developer system and practice with the HLM and LCM tools. If this is not an option, you may want to review the playlist on SAP HANA installations - SPS 07 on the SAP HANA Academy:
4. Which combination of authorisations is required for this user?
a. USER ADMIN, SERVICE ADMIN, DATA ADMIN
b. USER ADMIN, CREATE STRUCTURED PRIVILEGE, RESOURCE ADMIN
c. USER ADMIN, SERVICE ADMIN, ROLE ADMIN
d. USER ADMIN, CREATE STRUCTURED PRIVILEGE, ROLE ADMIN
Source: SAP HANA system privileges are documented in the SAP HANA SQL and System Views Reference. It is not clear to me where "this" in "for this user", refers to. I hope that on the exam the question had some context as all combinations are valid. For a user administrator, the privileges service and resource admin are not relevant, this leaves only answer d. as correct.
To do: Security is a big topic for the exam. On the HA200 course, two units are dedicated to this topic, security (authentication, encryptions, auditing) and maintaining users and authorizations (user management, roles and privileges). To prepare for this topic, read the Security Guide and chapter 3 of the Administration Guide.
5. Which of the following are pre-delivered template roles? There are 2 correct answers to this question.
Source: Documented in the Security Guide, Standard Roles. There is a role named SAP_INTERNAL_HANA_SUPPORT, not HANA_INTERNAL, but this role is not a template role.
To do: As mentioned, security is a big topic. Do you know the 7 restrictions for the SAP_INTERNAL_HANA_SUPPORT, for example? What purpose it serves? It is a good idea to be familiar with the different type of privileges, e.g. object, system, analytic, etc. How to display privileges granted to a user, and what tools you can use.
6. A user cannot query an information model because of missing authorizations. What is the fastest way to find out which authorization is missing?
a. Query the system view EFFECTIVE_PRIVILEGES.
b. Investigate the authorisation trace.
c. Use the Authorization Dependency Viewer.
d. Check the assigned roles in the user editor.
Source: The Authorization Dependency Viewer is documented in the Administration Guide. As mentioned there, you can use the authorization dependency viewer as a first step in troubleshooting the following authorization errors and invalid object errors for these object types: NOT AUTHORIZED, INVALIDATED VIEW and INVALIDATED PROCEDURE.
The other answers are not wrong and may be needed as a second or third step but are certainly not the fastest.
To do: Did I mention security is a big topic? See above at question 4 and 5.
7. Which of the following actions are required in the SAP HANA studio to use the Enhanced Change and Transport System (CTS+)?
a. Create a delivery unit that contains all of the runtime objects.
b. Create the HTTP connection named CTSDEPLOY.
c. Configure the connection to the CTS in the preferences.
d. Use an authorised user to attach SAP HANA to the transport request.
Source: Transporting changes, including HANA Application Lifecycle Management (HALM) and CTS+ are covered in the Operations unit of HA200. This is where you will learn about delivery units and packages and how to import and export content. HALM is documented in the SAP HANA Developer Guide but not CTS+. See Resources on CTS+ on SCN and in particular the How-To Guide How to Configure SAP HANA for CTS (SPS 07). Chapter 8 of that how to guide shows you all the Studio print screens.
How to configure the CTS Deploy Web Service is documented in Software Logistics for SAP Netweaver. This step is performed outside of the SAP HANA studio.
To do: You probably will get a few (but not many) questions about transporting changes. It is good to be familiar with the HANA Application Lifecycle Management topic documented in the Developer Guide. CTS+ in my view is more of a Netweaver ABAP topic. If you are not familiar with Netweaver or ABAP this will be a bit of a learning curve. When short on time, I would focus on the Big Four topics mentioned above. Mastering Data Provisioning in a short time will be challenging as these all touch different technologies.
8. Where in the SAP HANA Studio can you change the path of the backup folder? There are 2 correct answers to this question.
a. Backup Catalog
d. backup editor
Source: Documented in the SAP HANA Administration Guide, chapter Backup and Recovery. The backup editor in HANA Studio provides a user-friendly interface to make backup-related configuration changes. These changes are recorded in configuration files with INI extension. For backup, this will be the global INI file for most settings.
The Backup Catalog stores location and time stamp plus some additional information about all the data and log backups made but does not contain any configuration.
With the executor.ini file you can enable the Executor Trace to collect internal details about SQL statement execution.
To do: Read the Administration Guide or watch the SAP HANA Academy playlist on Backup and Recovery. Expect a couple of questions on the topic. They are not too difficult if you are familiar with the topic and so can help to boost your score.
How to change the path of the backup folder.
The Backup Catalog tutorial video may covers an exam question or two.
9. Which of the following can be performed in the SAP HANA lifecycle manager? There are two correct answers to this question.
a. Uninstall an SAP HANA system.
b. Rename an SAP HANA system.
c. Add an additional SAP HANA system.
d. Copy an SAP HANA system.
e. Change the SAP HANA license type.
Source: The SAP HANA lifecycle manager (HLM) tool is documented in the SAP HANA Update and Configuration Guide (SPS 07). See above, question 3, for more information about this guide and for a tutorial video about what you can do with this tool.
You can uninstall SAP HANA from the command line with the HANA Lifecycle Management (HCM) tool hdbuninst but not with the lifecycle manager. Significant changes were made to HCM and HLM with SPS 08 and I expect this to happen again with SPS 09.
How to copy a SAP HANA database is documented in the Administration Guide, chapter Availability and Scalability, section Backup and Recovery. A database copy is only possible using file-based backups or storage snapshots. There is no lifecycle management tool for this.
You can view and edit the SAP HANA License in HANA Studio by selecting a system in the Systems View and select Properties and then License. A license can be of type Permanent or Temporary.
To do: same advice as above with question 3.
Uninstall an SAP HANA system
Change the SAP HANA license type
10. What is the maximum number of master name servers that you can define in a distributed landscape?
Source: This is documented in the the SAP HANA Administration Guide, chapter System Administration, section Monitoring SAP HANA systems:
When you install a distributed system, up to three hosts are automatically configured as master name servers. The configured namesever role of these hosts is MASTER 1, MASTER 2, and MASTER 3. Additional hosts in your system are configured as slave name servers. The configured nameserver role of these hosts is SLAVE.
A configuration table showing a typical configuration for a distributed system is documented in the LCM Tools Reference Guide, chapter SAP HANA System Types.
To Do: Although this is documented in the monitoring section, this probably would be considered an architecture question. In HA200 this is discussed in Unit 3 Installation, Performing a Distributed Installation. To get a good picture of the SAP HANA architecture, the SAP HANA Master Guide is a good start.
SAP HANA Master Guide on HANA architecture
Unfortunately, but very reasonably, those that have passed the exam are not allowed to share the questions with anyone else, so I can't share any particular question with you here. However, a close study of the mentioned resources should provide you with enough knowledge to successfully pass the exam.
Did you succeed?
Feel free to post a comment about how the exam went. If there is any information missing, please let me know.