Docstoc

ADM520_620_Col32_FV - BASIS

Document Sample
ADM520_620_Col32_FV - BASIS Powered By Docstoc
					FS310 Inkasso/Exkasso
ADM520




                      Diese Seite wird von Andrea für euch noch erstell!




           ADM520
                            <leave this slide blank>

            Database Administration MS SQL Server




            THE BEST-RUN BUSINESSES RUN SAP

     SAP AG© SAP AG 2003
            2003




SAP Web AS 6.20
2003/Q2
50062304
   Copyright



        Copyright 2003 SAP AG. All rights reserved.
        No part of this publication may be reproduced or transmitted in
        any form or for any purpose without the express permission of
        SAP AG. The information contained herein may be changed
        without prior notice.

        All rights reserved.




         SAP AG 2003




Trademarks:
  Some software products marketed by SAP AG and its distributors contain proprietary software components of
  other software vendors.
  Microsoft®, WINDOWS®, NT®, EXCEL®, Word®, PowerPoint® and SQL Server® are registered trademarks
  of Microsoft Corporation.
  IBM®, DB2®, DB2 Universal Database, OS/2®, Parallel Sysplex®, MVS/ESA, AIX®, S/390®, AS/400®,
  OS/390®, OS/400®, iSeries, pSeries, xSeries, zSeries, z/OS, AFP, Intelligent Miner, WebSphere®, Netfinity®,
  Tivoli®, Informix and Informix® Dynamic ServerTM are trademarks of IBM Corporation in USA and/or other
  countries.
  ORACLE® is a registered trademark of ORACLE Corporation.
  UNIX®, X/Open®, OSF/1®, and Motif® are registered trademarks of the Open Group.
  Citrix®, the Citrix logo, ICA®, Program Neighborhood®, MetaFrame®, WinFrame®, VideoFrame®, MultiWin®
  and other Citrix product names referenced herein are trademarks of Citrix Systems, Inc.
  HTML, DHTML, XML, XHTML are trademarks or registered trademarks of W3C®, World Wide Web
  Consortium, Massachusetts Institute of Technology.
  JAVA® is a registered trademark of Sun Microsystems, Inc.
  JAVASCRIPT® is a registered trademark of Sun Microsystems, Inc., used under license for technology invented
  and implemented by Netscape.
  MarketSet and Enterprise Buyer are jointly owned trademarks of SAP AG and Commerce One.
  SAP, SAP Logo, R/2, R/3, mySAP, mySAP.com, and other SAP products and services mentioned herein as well as
  their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other
  countries all over the world. All other product and service names mentioned are the trademarks of their respective
  companies.
  Target Audience

      This course is intended for the following audiences:
             Database administrators
             SAP system administrators
             Project team members


      Duration: 3 days




        SAP AG 2002




User notes

  These training materials are not a teach-yourself program. They complement the explanations
  provided by your course instructor. Space is provided on each page for you to note down additional
  information.

  There may not be sufficient time during the course to complete all the exercises. The exercises provide
  additional examples that are covered during the course. You can also work through these examples in
  your own time to increase your understanding of the topics.
Course Prerequisites

  Required Knowledge:
      SAP50 (mySAP.com Technical Fundamentals) or
      SAPTEC (mySAP Technology Solution Fundamentals)
      Working knowledge of the MS SQL Server 2000 database
      and the operating system Windows NT or Windows 2000


  Recommended Knowledge:
      For the administration of SAP systems the courses BCtcc
      (Technical Core Competence) or ADM100 (mySAP
      Technology Administration) are necessary




   SAP AG 2002
 SQL Server Architecture




       1    Introduction


      2 SQL Server Architecture
       3    How the SAP System uses SQL Server

       4    Performance Monitoring and Tuning

       5    Database Backup

       6    Database Restore

       7    Regular Maintenance and Error Handling




     SAP AG 2002




© SAP AG                                 ADM520      1-1
  SQL Server Architecture



                     Contents
                        Architecture
                            Database Server and Instances
                            Authentication
                            Databases and files
                            Database objects
                        Logging
                        Locking

                     Objectives
                     At the end of this unit, you will be able to:
                        Describe the main features of the SQL Server architecture




      SAP AG 2002




© SAP AG                                        ADM520                              1-2
  Database Server


                                                                 Database server, a process
                                                                 running the database system




                                                                                    SQL Server


                                                                                 Windows server




                 Database 1      Database 2



       SAP AG 2002




  The term "database server" is often used to describe the physical computer on which the database
  runs.
  For the purpose of this explanation of SQL Server architecture, the term "database server" will refer
  to the operating system process on Windows that represents the active, programmed side of the
  database system. All management of the database system is performed through and by the server,
  and all communication with the database systems goes through the server.
  When the database server is running, the database system is up and you can connect to the databases.
  When the database server is not running, the databases are down.




© SAP AG                                       ADM520                                               1-3
  Services

           SQL Server Enterprise Manager - [Console P

            Console    Window    Help

           Action      View     Tools



           Console Root
               Microsoft SQL Servers

                     SQL Server Group
                          sapprod [Windows NT]                            Services viewed in the Enterprise



                                                             }
                              Distributed Transaction Co
                                                                          Manager
         Destination
                              SQL Server Agent


                    R3DUMPO


                                                                                 Services viewed in Windows



                                Distributed Transaction ..   Coordinates Transac…          Manual      Local System
                                MSSQLSERVER                                      Started   Automatic   PRDadm
                                MSSQLServerAdHelper                                        Manual      Local System
                                SQLSERVERAGENT                                   Started   Automatic   PRDadm




       SAP AG 2002




  Under Windows, a program can be started as a service. A service runs in the background and has no
  graphical user interface. To automatically or manually start and stop services at system startup, use
  the Control Panel Services.
  Microsoft SQL Server has the following services:
    MSSQLServer: Database server
    SQLServerAgent: Automatic task execution and SQL Server alert handling
    Distributed Transaction Coordinator: distributes transactions over multiple SQL Servers
    MSSQLServerAdHelper: Adds and removes objects used to register instances of SQL Server and
    ensures that the Windows account under which an SQL Server service is running has permissions
    to update all of the Active Directory objects for the instance, as well as any replication
    publications and databases for that instance.
  To start and stop these services, use the SQL Service Manager or the Enterprise Manager. The
  service MSSQLServer can also be started remotely from another computer. Starting SQL Server is
  the same as starting the service MSSQLServer.
  The SQLServerAgent service must be started automatically together with MSSQLServer.




© SAP AG                                                         ADM520                                               1-4
  Trace Flags




             Trace Flags can be set using
                  Command Prompt

                  Startup parameters in the Enterprise Manager




       SAP AG 2002




  SQL Server can be started and stopped manually using the command prompt by typing:
  net start mssqlserver or sqlservr, or net start SQLServerAgent or by running SQLSERVR.EXE.
  By specifying the –T, SQL Server should be started with a specified trace flag in effect. Trace flags
  are used to start servers with nonstandard behavior. This way a better understanding of SQL Server
  or even a change in the way SQL Server will react on specific conditions is provided.
  SQL Server can also be started with trace flags using the Startup dialog box in the Enterprise
  Manager.




© SAP AG                                       ADM520                                                 1-5
  Instances of SQL Server



                            SQL Server default instance              MSSQLSERVR
                            sapprod                                  SQLSERVERAGENT




                            master   msdb     tempdb   model




                            SQL Server named instance               MSSQL$sapprod\SQLServer
                            sapprod\SQLServer                       SQLAgent$sapprod\SQLServer




                            master   msdb     tempdb   model

                                                                                 Windows server




       SAP AG 2002




  SQL Server 2000 supports multiple instances of the SQL Server database engine running
  concurrently on the same computer. Each instance of the SQL Server database engine has its own set
  of system and user databases that are not shared between instances.
  There are two types of instances of SQL Server:
    Default Instances: The default instance is identified by the name of the computer on which the
    instance is running. When a client program such as the Query Analyzer specifies only the
    computer name in its request to connect to SQL Server, a connection to the default instance of the
    database engine on that computer is established. There can only be one default instance on any
    computer, the default instance can be of any version of SQL Server.
    Named Instances: All instances of the database engine other than the default instance are identified
    by an instance name specified during installation of the instance. Clients must provide both the
    computer name and the instance name of any named instance to which they are attempting to
    connect. The computer name and instance name are specified in the format
    computer_name\instance_name.
  There can be multiple named instances running on a computer. Only database engines from version
  SQL Server 2000 can operate as a named instance.




© SAP AG                                       ADM520                                                1-6
  Client/Server


                Workstation A                               Workstation B

                  Client X                                   Client Y


                 ODBC / OLE DB / DB Library                 ODBC / OLE DB / DB Library
                             Named pipes                                TCP/IP sockets



                             TCP/IP     IPX/SPX                              TCP/IP


                             Named pipes                            TCP/IP sockets
                                 ODS (Open Data Services)

                 SQL Server



                                                                           Windows server




       SAP AG 2002




  A database server is an application that runs as a system process on a computer. Client tools (for
  example, Query Analyzer) communicate with the database server through network protocols such as
  TCP/IP or NW Link IPX/SPX Compatible Transport, the native protocol of Novell NetWare
  networks.
  The network protocol used by the database server determines the method used for inter-process
  communication, for example, named pipes or TCP/IP sockets. These methods are installed as net
  libraries (DLLs) using the program SQL Setup. Only one net library is active on a client. To
  configure the library, use the program Client Network Utility.
  Several application programming interfaces (APIs) are available. For example, OLE DB and DBLib.
  Other client applications can use the ODBC interface.




© SAP AG                                          ADM520                                         1-7
  Processes and Threads




                                                 .. n      ...
                 Thread 1     Thread 2 Fiber 1 2                 Thread N   Process




                        Windows server




       SAP AG 2002




  A process is a program that is currently being executed. Under Windows, to keep processes separate
  from each other and prevent interference, each process has its own address space. The process is also
  the owner of all the program resources, such as file handles and access tokens.
  Windows 2000 allows a process to manage a 4GB range of linear addresses. An application process
  is able to control the lower portion of this space (either 2GB or 3GB). The upper portion is managed
  by system code for that particular process.
  The basic unit of activity in Windows is the thread. Each process has at least one thread, and may
  have multiple threads. Windows assigns time slices on physical processors to threads. All threads
  can run concurrently. Since there is no address space switch involved, switching between threads
  within the same process is much faster than a conventional process switch in operating systems that
  run without the thread.
  SQL Server has an internal layer that implements an environment similar to an operating system for
  scheduling and synchronizing concurrent tasks without having to call the Windows kernel. This
  internal layer can schedule fibers as effectively as it works with threads. A fiber is a unit of
  execution. Fibers run in the context of the threads that schedule them. Each thread can schedule
  multiple fibers.
  The server configuration lightweight pooling parameter controls whether SQL Server uses threads or
  fibers. The default is 0, in which case SQL Server schedules a thread per concurrent user command.
  If lightweight pooling is set to 1, then SQL Server uses fibers instead of threads. For more
  information on the lightweight pooling parameter, please refer to the section SQL Server
  Configuration in the Unit 6: Regular Maintenance and Error Handling.




© SAP AG                                       ADM520                                               1-8
  Processes and Threads (2)


                Workstation A                                  Workstation B

                 Client                                        Client
                 process X                                     process Y

                         OLE DB                                         OLE DB
                       Named pipes                                   TCP/IP sockets



                       TCP/IP       IPX/SPX                                    TCP/IP

                       Named pipes                                   TCP/IP sockets
                                ODS (Open Data Services)

                                                                               SQLSERVR
                  Thread 1      Thread 2      Thread 3   ...     Thread N
                                                                                process


                 Windows server




       SAP AG 2002




  SQL Server runs in an operating system process called SQLSERVR. This operating system process
  contains threads for the operating system and threads for clients logged on to the server.
  Every connection between a client and SQL Server uses one of these threads. SQL Server maintains
  a pool of either threads or fibers for user connections. The maximum size of this pool is controlled
  by the max worker threads server configuration parameter. One thread can serve several connections.
  For more information, please refer to the section SQL Server Configuration in the Unit 6: Regular
  Maintenance and Error Handling.




© SAP AG                                         ADM520                                            1-9
  Special Threads




              Lazy Writer

              Lock Manager

              Log Writer

              Checkpoint Manager

              Background Task




        SAP AG 2002




  The Lazy Writer thread scans the data cache to write dirty pages to disk. The Lazy Writer thread
  sleeps for an interval of time. When it is restarted, it checks the size of the free buffer list. If the free
  buffer list is below a certain point, the Lazy Writer thread scans the buffer cache to write dirty pages
  to disk. Most of the work writing dirty pages is done by the user threads and the Lazy Writer thread
  typically finds little to do.
  The Lock Manager dynamically adjusts the resources it uses for larger databases, eliminating the
  need to adjust the locks server configuration parameter manually. Details on the locking mechanism
  are explained in later slides.
  The Log Writer records are written asynchronously by a log writer thread, except when:
     A commit forces all pending records for a transaction to disk
     A checkpoint forces all pending records for all transactions to disk
  The Checkpoint thread scans the buffer cache for dirty pages and writes to disk any buffer page that
  are marked as dirty. Checkpoints typically find few dirty pages to write to disk because most dirty
  pages get written to disk by the worker threads or Lazy Writer thread in the period between two
  checkpoints.
  The Background task checks every 30 minutes if the database or the transaction log files can be
  shrunk, in case the database option autoshrink is set. It also starts every 5 seconds and checks on 20
  data pages if they contain ghost records. These are records which are deleted logically but not
  physically.




© SAP AG                                           ADM520                                                  1-10
  Security Modes




           SQL Server supports security modes for authentication
                SQL Server security
                Trusted security
                Mixed security




       SAP AG 2002




  SQL Server must verify that the login id supplied on each connection request is authorized to access
  the database server. This process is called authentication.authentication.
  SQL Server supports three security modes for authentication:
    SQL Server security: a connection to SQL Server is established through a SQL Server login and
    password, e.g. using login id sa
    Trusted security: a connection to SQL Server is established using the Windows user account.
    When a user connects to SQL Server through a Windows user account, SQL Server verifies that
    the account name and password were validated when the user logged on to the operating system.
    The SQL Server client then requests a trusted connection to SQL Server. The properties of a
    trusted connection include the Windows group and user accounts of the client that opened the
    connection.
    A member of the SQL Server sysadmin fixed server role, for example login id sa, must first
    specify to SQL Server all the Windows accounts or groups that can connect to SQL Server.
    Mixed security: Mixed security supports both, SQL Server security and trusted security.




© SAP AG                                       ADM520                                              1-11
  Databases and Files



  Databases


                       master               msdb               tempdb               model




  Files


                             .ldf                 .ldf                .ldf                .ldf
                      .mdf                 .mdf                .mdf                .mdf




       SAP AG 2002




  The system databases are:
     master holds all the system level information for a SQL Server system. It stores all login accounts
     and all system configuration parameters.
     model is used as the template for all databases created on SQL Server.
     msdb is used by SQL Server Agent for scheduling alerts and jobs.
     tempdb holds all temporary tables and temporary stored procedures for all users connected to SQL
     Server. It also fills any other temporary storage needs such as work tables generated by SQL
     Server. Every time SQL Server is started, tempdb is re-initialized. The initialization is recorded as
     “clearing tempdb” in the error log.
  Northwind and pubs are sample databases that are provided as learning tools.
  SQL Server maps a database over at least two operating system files. Data and log information are
  never contained in the same file, and individual files are used only by one database.
  The primary file (.mdf) contains the startup information and system tables for the database and is
  used to store data. Every database has one primary file. Secondary files (.ndf) hold data that does not
  fit in the primary data file. Databases do not require any secondary data files if the primary file is
  large enough to hold all the data in the database. Transaction log files (.ldf) contain the log
  information used to recover the database. There must be at least one log file for each database.




© SAP AG                                        ADM520                                                1-12
  Files and Filegroups



         Objects                                                         Filegroups

  System tables and                          RAID 5
  other user tables                                                 PRIMARY
                                             RAID 1
  Transaction log                                                  Log files

                                                                         RAID systems




       SAP AG 2002




  Data files are assigned to one filegroup. A single table can use space in several different files within
  the same filegroup.
  The PRIMARY filegroup is created when SQL Server is installed.




© SAP AG                                         ADM520                                                1-13
  Automatic Growth


  Database files

                                Volume             File


                                               +             :           ...
                             (Autogrow ONLY for out-of-space failure)




  Log files

                                               +          ....




        : Free files
        SAP AG 2002




  Database files only grow automatically in a filegroup if the option autogrow is set and no further
  space is available on any of the database files in the filegroup.
  Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written
  to the filegroup, SQL Server writes an amount proportional to the free space in the file to each file
  within the filegroup, rather than writing all the data to the first file until full and then writing to the
  next file. For example, if File1 has 100 MB free and File2 has 200 MB free, one extent is allocated
  from File1, two extents from File2, and so on. This way both files become full at about the same
  time and simple striping is achieved.
  Transaction log files, however, cannot be part of a filegroup; they are separate from one another. As
  the transaction log grows, the first log file fills, then the second, and so on, using a fill-and-go
  strategy rather than a proportional fill strategy. Therefore, when a log file is added, it may not be
  used by the transaction log until the other files have been filled first.




© SAP AG                                           ADM520                                                1-14
  Space Allocation



                               Page                         File

      Tables X               Page 8
                                                                                 Extents
       Row 1                 Row 1                0     1   2    3   4   5   6     7     1
       Row 2
                             Row 2                8    9    10 11 12 13 14 15            2
       Row 3

                             Page 17              16 17 18 19 20 21 22 23                3

                             Row 3                              8 Pages = 1 Extent




       SAP AG 2002




  The page is the unit of data storage in SQL Server. Pages are 8 KB in size. SQL Server allocates
  pages to objects and reuses space freed up by deleted rows. These operations are internal to the
  system and use data structures not visible to users.
  Extents are the basic unit in which space is allocated to tables and indexes. An extent consists of
  8 contiguous pages, or 64KB. A new table or index allocates first pages from mixed extents. That
  means extents can contain pages from different objects. Extents are called uniform, when a table or
  index allocates all eight pages.
  Log files do not contain pages, they contain a series of log records, allocated on virtual log files.




© SAP AG                                       ADM520                                                1-15
  Space Allocation (2)


          First extent on each file
          8 contiguous pages = 1 extent (64 KB)
            File                                                                                                              Boot
           header           PFS           GAM          SGAM                                       BCM           DCM           page        ...
             0               1             2             3                                         6             7              8

             File header                    Contains information about the attributes of the file

             PFS                            Page allocation / free space availability (in %)
                                            Page Free Space


             (S)GAM                         Usage of extents (free, mixed, full)
                                            (Secondary) Global Allocation Map

             BCM                            Changes of extents when in Bulked_logged recovery mode
                                            Bulk Changed Map

             DCM                            Changes of extents since last database backup
                                            Differential Changed Map


             Boot page                      Contains information about the attributes of the database
                                            exists only in the primary data file


         SAP AG 2002



  File Header is a special page that contains information about the file.

  Page Free Space (PFS) pages record whether an individual page has been allocated, and the amount of space free on each page. Each PFS
  page covers 8000 pages. For each page, the PFS has a bitmap recording whether the page is empty, 1-50% full, 51-80% full, 81-95% full,
  or 96-100% full. Once an extent has been allocated to an object, SQL Server uses the PFS pages to record which pages in the extent are
  allocated or free, and how much free space is available for use.

  Global Allocation Map (GAM) pages record which extents have been allocated. Each GAM covers 64000 extents, or nearly 4 GB of data.
  The GAM has one bit for each extent. If the bit is 1, the extent is free; if the bit is 0, the extent is allocated.

  Shared Global Allocation Map (SGAM) pages record which extents are currently used as mixed extents and have at least one unused
  page. Each SGAM covers 64000 extents, or nearly 4 GB of data. The SGAM has one bit for each extent it covers. If the bit is 1, the
  extent is being used as a mixed extent and has free pages; if the bit is 0, the extent is not being used as a mixed extent, or it is a mixed
  extent whose pages are all in use.

  Index Allocation Map (IAM) pages map the extents in a database file used by a heap or index. Each heap or index has one or more IAM
  pages recording all the extents allocated to the object. A heap or index has at least one IAM for each file on which it has extents. A heap
  or index may have more than one IAM for a file if the range of the extents for the heap or index on that file exceeds the range that an
  IAM can record.

  Bulk Changed Map (BCM) pages record the extents which have been changed by bulk operations such as CREATE INDEX since the last
  transaction log backup. If the bit is 1, the extent has been changed by a bulk operation, if the bit is 0, the extent has not been changed.
  BCM pages are only relevant when the recovery model of the database is set to bulk_logged.

  Differential Changed Map (DCM) pages record which extent has changed since the last execution of BACKUP DATABASE. Each DCM
  covers 64000 extents, or nearly 4 GB of data. If the bit for one extent is 1, the extent has been changed since the last BACKUP
  DATABASE, if the bit is 0, the extent has not been changed. See also Unit 4: Database Backup.

  The Boot page is the ninth page in the database file, that is, the first page of the second extent. It is stored in the primary database file and
  in the first transaction log file. The boot page contains attributes of the database. It records attributes which are needed for an automatic
  recovery. See also Unit 5: Database Restore.

© SAP AG                                                            ADM520                                                                      1-16
  Free Space


                                                                                          PRD
           Database

            Owner:                         prdadm
            Date created:                  02/22/2002 05:55:54
            Size:                          65.000,00 MB
            Space available:               15.664,99 MB
            Database options:              normal
            Number of users:               2

           Maintenance

            Last database backup:          10/22/2002 06:00:54
            Last differential backup:      None
            Last transaction log backup:   10/22/2002 10:00:54
            Maintenance plans:             None


            Space Allocated

            Data:
                        PRDdata1:           20000 MB      15655,59 MB              4344,31 MB

                        PRDData2:           20000 MB      15888,19 MB              4111,81 MB

                        PRDdata3:           20000 MB      15823,69 MB              4176,31 MB

            Transaction Log space:          4999,99 MB   73,53 MB     4926,46

                                            Total                   Used        Free

       SAP AG 2002




  The log and data usage of a database can be displayed using the taskpad view in the Microsoft
  Management Console of the Enterprise Manager. The space used for each data file and the
  transaction log is shown.
  In the first section the size of the database is shown as the size of the data and the transaction log.
  The space available shows the space available for logging information and for data.
  The information returned by transact-SQL command DBCC SQLPERF(LOGSPACE) can be used to
  monitor the amount of log space used and indicates when to back up the transaction log.
  System stored procedure sp_spaceused computes the amount of disk space used for data and indexes.
  When updateusage is specified, SQL Server scans the data pages in the database and makes any
  necessary corrections to the sysindexes table regarding the storage space used by each table. There
  are some situations, for example, after an index is dropped, when the sysindexes information for the
  table may not be current. This process can take some time to run on large tables or databases. The
  command DBCC UPDATEUSAGE can be run separately.




© SAP AG                                        ADM520                                               1-17
  Database Objects


    Logical Database Objects
                 Name    xyz     abc



                                                      §
                        Tables         Views     Constraints        Stored         Indexes
                                                                    Procedures




                                               Name   xyz     abc
    System
    Representation
                                               System Tables




    Physical
    Memory
                         Pages                        Pages                           Pages



       SAP AG 2002




  An SQL Server database consists of tables that contain data and other objects such as views, indexes,
  and stored procedures defined to support the activities performed on the data.
  These database objects are stored on pages in physical memory, along with other data objects
  defined in the system tables.




© SAP AG                                        ADM520                                             1-18
  Non-clustered Index (on a table with no clustered
  index)

    Level n                                                       Page pointer
    Root                                                                                           Index
                                                 Atlanta
                                                                                                   Pages
    Level 1                                      New York                                          indid >= 2
    Intermediate nodes
                         Atlanta                                    New York
                         Berlin                                     Paris
                         Heidelberg                                 Walldorf
                         L.A.                                       Washington
    Level 0              London
    Leaf nodes                                                        nonclustered
                         Manchester   rid
                                                                       key values
                                                                                     rid




                                                                                                    Data
       Heidelberg,          London,              Berlin,              New York,                     Pages
       Germany              United King.         Germany              USA                           indid = 0
       L.A.,                Manchester,          Washington,
                                                                                           Rid (row identifier):
       USA                  United King.         USA
                                                                                           - File ID,
       Atlanta,             Paris,               Walldorf,
                                                                                           - Page number,
       USA                  France               Germany
                                                                                           - Row number




       SAP AG 2002




  Indexes are used to speed up searching for records in tables. Indexes can be created for a frequently
  used search field or a combination of search fields.
  SQL Server has two types of indexes:
    Non-clustered index
    Clustered index
  A non-clustered index is a B tree, which is searched from the highest level to the data pages. A
  search through the index requires the following page accesses:
    One page for each index level, and one access on each data page(s).
  This graphic displays data and index pages in a table. The search fields are named for City and
  Country and contain a non-clustered index in the field City. With a non-clustered index, index and
  data pages are divided, so that you can create as many non-clustered indexes for a table as needed.
  The location of a data row is contained in each leaf level of the non-clustered index and is identified
  by a record identifier (RID) comprised of the file number, page number, and slot number of the row.




© SAP AG                                        ADM520                                                          1-19
  Clustered Index


                                              France,
                                              Paris                                       Index
                                              Germany,                                    Pages
     Level 1                                  Walldorf.
     (indid=1)
                                              USA,
                                              Atlanta
                                              USA,       key
                                              Washington          Page pointer




    Level 0                                                                               Data
    (indid=0)    France,           Germany,           USA,                USA,            Pages
                 Paris,            Walldorf,          Atlanta,            Washington,
                 Europe            Europe             North America       North America
                 Germany,          United King.,      USA,
                 Berlin,           London,            L.A.,
                 Europe            Europe             North America
                 Germany           United King.,      USA,
                                                                             clustered
                 Heidelberg,       Manchester,        New York,
                                                                                   key
                 Europe            Europe             North America




        SAP AG 2002




  A clustered index dictates the physical storage order of the data in the table. That means a table can
  contain only one clustered index. All inserts made fit in the ordering sequence of the clustered index
  key.
  A clustered index is organized as a B tree. Each page in a clustered index holds a page header
  followed by index rows. Each clustered index row contains a key value and a pointer to either a page
  or a data row. Each page in a clustered index is called an index node. The top node of the B tree is
  called the root node. The bottom nodes in the clustered index are called the leaf nodes. In a clustered
  index, the data pages make up the leaf nodes. Index levels between the root and the leaves are known
  as intermediate levels.
  For a clustered index, root points to the top of the clustered index. SQL Server navigates down the
  clustered index to find the row corresponding to a clustered index key. To find a range of keys, SQL
  Server navigates through the clustered index to find the starting key value in the range, and then
  scans through the data pages. To find the first page in the chain of data pages, SQL Server follows
  the leftmost pointers from the leaf node of the clustered index.




© SAP AG                                       ADM520                                                1-20
  Non-clustered Index with a Clustered Index

   Non-clustered Index
                                                                                                         Index
                                                                                                   Index Pages
                        USA,                      Atlanta                                  USA,          Pages
      Atlanta                                                             New York
                        Atlanta                   New York                                 New York
                        Germany,                                                           France,
      Berlin                                                              Paris
                        Berlin                    ...                                      Paris
                        Germany,                                                           Germany,
      Heidelberg                                                          Walldorf
                        Heidelberg                                                         Walldorf
                        USA,                                                               USA,
      L.A.                                                                Washington
                        L.A.                                                               Washington
                        United King.,
      London
                        London
                        United King.,                                     clustered key     clustered key
      Manchester
                        Manchester                                           values            values


   Clustered Index                             search via clustered index key (country, city)

     root                                                                                                   Index
                                                                                                            Pages
    Level 1 (indid 1)

    Level 0 (indid 0)
    leaf nodes                                                                                              Data
                                                                                                            Pages



        SAP AG 2002




  If a table has a clustered index and a clustering key, the leaf nodes of all non-clustered indexes use
  the clustering key as the row locator rather than the physical record identifier (RID). If a table does
  not have a clustered index, non-clustered indexes continue to use the RID to point to the data pages.
  In both cases, the row locator is stable. When a leaf node of a clustered index is split (data page), the
  non-clustered indexes do not need to be updated because the row locators are still valid. If a table
  does not have a clustered index, page splits do not occur.




© SAP AG                                         ADM520                                                             1-21
  Logging

                                        SQL Server Query Analyzer
                                          detail stats.




                                       BEGIN TRAN
    SQL command                        UPDATE



        Cache




        Files
                                          Data                           Log




       SAP AG 2002




  A database transaction is a series of SQL commands that are completed, or not executed at all. A
  transaction is started with the command BEGIN TRAN. All commands within one transaction are
  handled as one atomic command by SQL Server .
  Each SQL Server database has a transaction log that records data modifications made in the
  database. The log records the start and end of every transaction and associates each modification
  with a transaction. SQL Server stores the information in the log to either redo (roll forward) or undo
  (roll back) the data modifications that make up a transaction. Each record in the log is identified by a
  unique log sequence number (LSN).




© SAP AG                                                  ADM520                                      1-22
  Commit

                                      SQL Server Query Analyzer
                                        detail stats.




    SQL command                                 COMMIT TRAN



        Cache
                                                                  .....




        Files
                                             Data                      Log




       SAP AG 2002




  A transaction is ended with the command COMMIT TRAN or ROLLBACK TRAN. SQL Server
  then writes all logging information from this transaction to the log file and sends a message to the
  application program confirming the COMMIT TRAN. This ensures that all confirmed changes from
  SQL Server are logged onto a physical disk. Records to be changed are first stored in a cache.
  Sometimes the records to be changed are only up to date in the cache.




© SAP AG                                                ADM520                                     1-23
  Checkpoints

                                     SQL Server Query Analyzer
                                       detail stats.




    SQL command                                CHECKPOINT



        Cache
                                                                       .....




        Files
                                                       Data               Log




       SAP AG 2002




  During a checkpoint, all dirty data and log pages in the cache are written to the files.
  There are 2 kinds of checkpoints:
     Manual checkpoint (transact-SQL command Checkpoint)
     Automatic checkpoint
  SQL Server configuration parameter recovery interval controls when SQL Server issues a checkpoint
  in each database. Checkpoints are done on a per database basis. The recovery interval sets the
  maximum number of minutes per database that SQL Server needs to recover databases. The default
  is 0, indicating automatic configuration by SQL Server. This means a recovery time of less than one
  minute and a checkpoint approximately every one minute for each database.
  When a database is set to use the simple recovery model, logging information from the log is erased
  at each automatic checkpoint. This option is useful for databases that require many changes, and
  have minimum security requirements, for example database msdb and tempdb. If this mode is set,
  the restore cannot apply the transaction logs written later than the checkpoint. Perform a full backup
  immediately after disabling this option.




© SAP AG                                                      ADM520                                1-24
  Checkpoints: Logical Sequence Number

                                                                 SQL Server Query Analyzer
                                                                   detail stats.




      Boot page                        SQL Server Query Analyzer
                                         detail stats.


                                                             ISQL/w
                                                        Begin Transaction
                                                        Update MONI set KEY = ‘4711’ where KEY= ‘0000’;
                                      Begin Transaction
                                                        Commit
                                      Update MARA set MATNR = ‘4711’ where MATNR=
      MinLSN                          ‘0000’
                                      Insert into KUNR values (‘9087’, ‘Microsoft’,
      303                             ‘Redmond’)
                                      Select * from KUNR where KNR = ‘5679’




                                                                                                          Transaction log

        LSN           LSN       LSN                      LSN                       LSN       LSN           LSN
        301           302       303                      304                       305       306           307

        Begin Tran1 Update      Begin Tran2 Update                                 Commit    Checkpoint    Begin Tran3
                    MONI                    MARA                                   Tran1
                    Tran1                   Tran2




       SAP AG 2002




  During a checkpoint, the log sequence number (LSN) of the first log record at which a system-wide
  recovery must start is written to the database boot page. This LSN is called the Minimum Recovery
  LSN (MinLSN) and is the lowest of the three following values:
    The LSN of the checkpoint
    The LSN of the oldest recorded dirty data page
    The LSN of the start of the oldest active transaction
  The portion of the log file from the MinLSN to the end of the log is called the active portion of the
  log. This is the portion of the log required for a full recovery of the database. No part of the active
  log can ever be truncated. All log truncation must be done from the parts of the log before the
  MinLSN.
  The automatic recovery process is explained in detail in Unit 5: Database Restore.




© SAP AG                                                       ADM520                                                       1-25
  Lock Modes


      detail stats.     detail stats.             detail stats.   detail stats.         detail stats.




      Select           Select                    Update           Update                Select




                                        Shared                                    Exclusive             ....




       SAP AG 2002




  An RDBMS processes a large number of transactions simultaneously. A lock synchronizes
  simultaneous accesses to an object. When a transaction accesses an object, the object is temporarily
  locked to prevent other transactions from accessing it simultaneously.
  The type of lock determines which operations from other transactions can be executed on the locked
  object. Types of locks are:
    Shared (S)
    Used for operations that do not change or update data (read-only operations), such as a SELECT
    statement.
    Exclusive (X)
    Used for data-modification operations, such as UPDATE, INSERT, or DELETE. This type of lock
    ensures that multiple updates cannot be made to the same resource at the same time.
    Update (U)
    Used on resources that can be updated. This type of lock prevents a common form of deadlock that
    occurs when multiple sessions are reading, locking, and potentially updating resources later.
    Intent (I)
    Used to establish a lock hierarchy.
    Schema (Sch)
    Used when an operation dependent on the schema of a table is being executed.
    The two types of schema lock are: Schema stability (Sch-S), Schema modification (Sch-M)




© SAP AG                                         ADM520                                                        1-26
  Lock Resources



                                              Database (single user)
                         Table
                                                   Page


           Index rows                                            Key range



                                                                             Extent

                                                           Row
           Data rows
                                                                      .... 8 pages




       SAP AG 2002




  SQL Server has multi-granular locking that allows different types of resources to be locked by a
  transaction. These resources are locked at a level appropriate to the task by using a dynamic locking
  strategy to determine the most cost-effective locks.
  SQL Server can lock the following resources, which are listed in order of increasing granularity:
  RID: Row identifier, used to individually lock a single row within a table
     KEY: Row lock within an index, used to protect key ranges in serializable transactions
     PAG: 8KB data or index page
     EXT: Extent, contiguous group of eight data or index pages
     TAB: Entire table, including all data and indexes
     DB: Database
  The finer the lock granularity, the more locks are needed. For example, when accessing a table with
  100,000 pages, you can use 100,000 page locks or only one table lock. More locks require more
  administration time.
  If the lock granularity is coarse, other transactions must wait longer until the lock is released.
  To display locks currently in use, use the stored procedure sp_lock or use the Current Activity View
  in the Enterprise Manager.




© SAP AG                                       ADM520                                               1-27
  Lock Escalation



                                Minimize cost of locking
           Transaction 1

                  Table         3 Intent exclusive (IX)           5       Exclusive (X)

                                2 Intent exclusive (IX)                 Coarse-grain locks
                Page
                 Row            1 Exclusive (X)
                                                                                    4

                  Row                 Lock escalation
                                      ( large number of rows              Fine-grain locks
                                      or pages -> table lock )




       SAP AG 2002




  SQL Server may dynamically escalate or deescalate the granularity or type of locks. Lock escalation
  is the process of converting many fine-grain locks into fewer coarse-grain locks, thus reducing
  system overhead.
  In this example, a transaction requests rows from a table for update purpose. SQL Server
  automatically acquires locks on those rows affected (1) and places higher level intent locks on the
  pages (2) or index, that contain those rows. The table which contains the rows also receives an intent
  exclusive lock (3). When the number of locks held by the transaction exceeds a threshold, SQL
  Server attempts to change the intent lock on the page to a stronger lock, e.g. an intent exclusive
  would change to an exclusive lock (4). After acquiring the stronger lock, all row level locks held by
  the transaction on the page are released, reducing lock overhead (5).
  SQL Server may choose to use both row and page locking for the same query, for example, by
  placing page locks on the index (if enough contiguous keys in a non-clustered index node are
  selected to satisfy the query) and row locks on the data. This reduces the likelihood of lock
  escalation.
  SQL Server rarely needs to escalate locks; the query optimizer usually chooses the correct lock
  granularity at the time the execution plan is compiled.
  Lock escalation thresholds are determined dynamically by SQL Server and do not require
  configuration.




© SAP AG                                       ADM520                                               1-28
  Transaction Isolation Level (1)

  Dirty          T1   update X                       rollback
                                                                                 changing command
  read
                  X=5              X=7               X=7           X=5
                                                                                 reading command

                 T2                      select X          X=7                  X=7 data read

   Non-               T1     select X,Y X=7,Y=2              select X,Y         X=4,Y unknown
   repeatable
   read                 X=7              X=7               X=4                    X=4
                        Y=2              Y=2
                      T2                 update X, delete Y


   Phantom              T1    select <10       Y=7                 select <10    X=5,Y=8, Z=3

                                                             X=5                  X=5
                           Y=7           Y=7                 Y=8                  Y=8
                           Z=12          Z=12                Z=3                  Z=3

                        T2               insert X, update Y, Z


       SAP AG 2002




  Several users running concurrent transactions can cause inconsistencies in the data read by other
  users. The following situations may occur:
    Dirty read: Transaction T1 updates data X. Another transaction T2 then selects X before T1
    performs a COMMIT. T1 then performs a ROLLBACK. So T2 has read a value for X that never
    existed in the database as consistent (committed) data.
    Non-repeatable read: Transaction T1 selects data X, Y. Another transaction T2 then updates X
    and deletes Y and commits. T1 then selects X, Y again. It reads a modified value for X and
    discovers that Y does not exist.
    Phantom data: Transaction T1 selects all the data that satisfies the condition < 10. Only X is
    returned. Transaction T2 then creates data Z and updates Y so as to satisfy the condition < 10. T2
    commits. T1 then again selects all the data < 10. Now X, Y, and Z are returned. So new data
    appeared (phantom data).




© SAP AG                                         ADM520                                             1-29
    Transaction Isolation Level (2)

   Read              select                           Read               select              commit
   uncommitted                            commit      committed




                                                                         lock     lock

   Repeatable       select                                              commit            Data without lock
   read

                                                                                          Another process
                                                                                          holds a shared
                                                                                          lock on this data
                              lock            lock          hold lock                     Another process
                                                                                          holds an exclusive
   Serializable     select                                              commit            lock on this data

                                                                                          Data is read

                                                                                          Select waits until
                                                                                          lock is released
                         key range lock
                                               lock         hold lock
          SAP AG 2002




  The isolation level determines to what degree one transaction is isolated from other transactions. A
  lower isolation level increases concurrency but at the expense of data correctness. A higher isolation
  level ensures that data is correct, but can negatively affect concurrency. The isolation level set by an
  application determines the locking behavior used by SQL Server.
  To define the isolation level for a connection to the database, use the transact-SQL command SET
  TRANSACTION ISOLATION LEVEL.
  SQL-92 defines four isolation levels, all of which are supported by SQL Server:
    Read uncommitted accepts all dirty reads, non-repeatable reads and phantoms. No shared locks
    are issued and no exclusive locks are honored.
    Read committed avoids dirty reads. To achieve this shared locks are held while data is being
    read. But the data can be changed before the end of the transaction, resulting in non-repeatable
    reads or phantom data. This option is the SQL Server default.
    Repeatable read avoids both dirty and non-repeatable reads. It sets locks on all data used in a
    query and prevents other users from updating the data. Phantom data can occur.
     Serializable avoids all dirty reads, non-repeatable reads and phantoms. It sets a range lock on the
     data range selected. Other users cannot update or insert rows into that data range until the
     transaction is complete.




© SAP AG                                        ADM520                                                   1-30
  Summary of this Unit



               Now you are able to:


                     Describe the main features of the SQL Server
                     architecture
                     Explain the different authentication modes
                     Understand the different index types
                     Name the system databases and their main functions




      SAP AG 2002




© SAP AG                               ADM520                             1-31
  Further Documentation




                     Inside Microsoft Sql Server 2000
                     Microsoft Press, ISBN: 0-7356-0998-5




      SAP AG 2002




© SAP AG                       ADM520                       1-32
  How the SAP System uses SQL Server




           1   Introduction

           2   SQL Server Architecture


       3 How the SAP System uses SQL Server
        4      Performance Monitoring and Tuning

        5      Database Backup

        6      Database Restore

        7      Regular Maintenance and Error Handling




      SAP AG 2002




© SAP AG                                  ADM520        2-1
  How the SAP System uses SQL Server



                     Contents
                        Client/Server architecture
                        Database access and security
                        Databases used in the SAP System




                     Objectives
                     At the end of this unit, you will be able to:
                        Describe the architectural specifics under SQL Server and the SAP
                        System
                        Name the databases used in the SAP environment and explain their
                        functionality




      SAP AG 2002




© SAP AG                                        ADM520                                      2-2
  Architecture of the SAP System (1)




    Presentation
    Server
                            SAP GUI
                            SAP GUI                      SAP GUI
                                                         SAP GUI

                                       SAP Instance
    SAP                    SAP
    Application            Instance    SAP               SAP                    SAP
    Server                            Work-           Dispatcher                Work-
                                    SAP
                                     Prozess                              SAP Prozess
                                   Work-                                 Work-
                                  Prozess                               Prozess


    Database
    Server                      SQL Server




                           Databases
       SAP AG 2002




  The SAP System has a 3-tier architecture that includes the following:
    Presentation server
    Application server
    Database server
  The presentation server consists of a graphical user interface on the front end. Each SAP System user
  runs an SAP GUI on a dedicated front-end computer.
  The application server is used primarily for data processing. An SAP instance that runs on an
  application server combines special SAP services such as dispatching requests. The dispatcher, a
  special process on each SAP instance, communicates with the front end SAP GUIs and distributes
  work requests to the work processes. The application logic runs in the work processes, which are
  connected to the database server.
  The database server is the computer where the database management system (DBMS) is located. The
  DBMS (SQL Server) controls the databases and the database objects that perform all the database
  activities.
  Note that an SAP GUI on a presentation server does not connect directly with the database; only the
  work processes do this.




© SAP AG                                       ADM520                                               2-3
  Architecture of the SAP System (2)




   Presentation
   Server
                     SAP GUI
                     SAP GUI                      SAP GUI
                                                  SAP GUI


   SAP                    SAP Instance
   Application       SAP Instance
   Server                                       SAP
                            SAP              Dispatcher       SAP
                           Work-                             Work-
                          Prozess                           Prozess


   Database
   Server
                         SQL Server



                        Databases


      SAP AG 2002




© SAP AG                                 ADM520                       2-4
  R/3 Database Interface



   SAP                      SAP Instance
   Application
   Server                            SAP Work Process
                                      Database Access Agent



                                       R/3 DB IF


                                       DBSL IF




   Database
   Server                       SQL Server



                               Databases



       SAP AG 2002




  SQL Server accesses objects stored in databases on the database server and accepts all requests from
  the SAP System through the database interface from the database access agent.
  The database access agent in the work processes handles database requests, and consists of several
  subcomponents. One of these subcomponent is the database vendor-independent R/3 database
  interface (R/3 DB IF), which handles accesses to table and dictionary buffers.
  The database access agent also provides the Database SQL Library Interface (DBSL IF), which is
  database vendor-specific. All components on the SAP System side of this interface are independent
  of the database used. On the database side of the interface, only system components provided by the
  database vendor are used.
  With SQL Server, components on the database side of the DBSL IF are implemented using a
  Microsoft product. The main task of the DBSL IF is the mapping of ABAP Open SQL statements to
  the database vendor-specific SQL language.




© SAP AG                                      ADM520                                                2-5
  Database Connections



   SAP
   Application
                                       SAP Work-                         SAP Work-
   Server
                                        Prozess                           Prozess

                                                         OLEDB
                                                    TCP/IP sockets


                                                           TCP/IP


   Database
   Server                                             TCP/IP sockets

                                         ODS                     (Open data services)


                                            SQL Server




       SAP AG 2002




  Each SAP work process is connected to the database server through several connections that are used
  for executing database commands. The DBSL IF is implemented using the Microsoft OLE DB.
  If the SAP work processes have to connect to a computer other than the database server, the
  communication protocol will be TCP/IP, otherwise Named Pipes is chosen.
  Open Data Services (ODS) is the component that manages all packets coming from the server net
  libraries, for example TCP/IP sockets. The database server processes all requests passed to it by ODS
  and returns the results to the client.




© SAP AG                                       ADM520                                               2-6
  Client Network Utility


                                                         SAP
                                                         Central
                  SAP       SAP                                       SAP       SAP
                                                         Server
                  Work      Work                                      Work      Work
   SAP
                 Process   Process
                                       ...                           Process   Process
                                                                                         ...
   Application
   Server             TCP/IP sockets                                     Named Pipes




     TCP/IP                                                          Named
                                                                     Pipes




                      TCP/IP sockets
                                                                         Named Pipes
   Database
   Server
                        SQL Server
                                                                          SQL Server



       SAP AG 2002




  In the SAP environment the network protocols should be activated as follows:
    SAP Application Server connecting over the network: On every SAP Application Server the
    default network protocol must be TCP/IP. In the client network utility, set TCP/IP at the top of the
    list.
    Cluster server with one node running SAP connecting to an SQL Server running on another node:
    TCP/IP should be activated on the node where the SAP system runs.
    Central system connecting within the same machine: If the central database server is also running
    the SAP system the protocol must be set to Named Pipes. Named Pipes is more efficient than
    TCP/IP on the local system, because the data transfer occurs in main memory only.
  You can activate a protocol using the SQL Server Client Network Utility for each client, and the
  Server Network Utility for the server. Only one protocol can be active on any one SAP Application
  Server. To start the SQL Server Client Network Utility choose Programs/Microsoft SQL Server, and
  then click Client Network Utility.




© SAP AG                                       ADM520                                                2-7
  Database Connections to SAP Work Processes




                                                  SAP                             SAP
                                                                                 SAP
  0   Consistent transactions   SAP               Work                           Work
                                                                              Work process
  1   Stored proc.              Application      Process                          Process
      Create/Select single      Server
  2..N Dirty read selects



                                              01234567                        01234567


           READ UNCOMMITTED     Database
           READ COMMITTED       Server
                                               SQL Server




       SAP AG 2002




  Several connections are used for each SAP work process, with the following isolation levels:
    Connection 0: Committed read connection, used for consistent transactions involving inserts,
    deletes, updates, and select for update or database cursor usage.
    Connection 1: Uncommitted read connection, used for all DDL transactions, creating stored
    procedures, and to execute single selects and dirty read cursors.
    Connection 2...N: Uncommitted read connection, used for dirty read selects.
  A maximum number of 40 connections are established for each work process, other than connections
  0 and connection 1. These connections are opened as needed but only closed when shutting down the
  instance or restarting the work process. If the select is nested in too many surrounding selects (more
  than 40), cursors are used in connection 1.
  Each connection to the database uses approximately 50KB of memory.




© SAP AG                                       ADM520                                                2-8
  SAP Process Monitor




   sapprd   5465   12   sleepi. EARLY.. SAPLTHB      0     48.890   8.856   2.048 AWAITING COM.   0
                   56   sleepi. EARLY.. SAPLTHB      0   801.570    1.234    456 AWAITING COM.    0
                   78   sleepi. EARLY.. SAPLTHB      0     90.346    456      90 AWAITING COM.    0
                   42   sleepi. EARLY.. SAPLTHB      0      9.457    150      12 AWAITING COM.    0
            1809   89   sleepi.                      0   501.290    1.390   7.903 AWAITING COM.   0
                   67   sleepi.                      0     78.401    901    1.509 AWAITING COM.   0
                   59   sleepi.                      0       809     855      56 AWAITING COM.    0
                   43   sleepi.                      0     55.331    401       0 AWAITING COM.    0
            7901   90   sleepi. KERBE..              0   620.601    9.023   9.450 AWAITING COM.   0
                   87   runn.     KERBE..            0     88.345   5.998   8.345 SELECT          0
                   14   sleepi. KERBE..              0     32.901   7.901   6.012 AWAITING COM.   0
                   63   sleepi. KERBE..              0     45.234    967     690 AWAITING COM.    0
                   39   sleepi. KERBE..              0     12.902    235     401 AWAITING COM.    0
            9011   41   sleepi.                      0   734.993    9.345      0 AWAITING COM.    0



        SAP AG 2002




  To call the SAP Process Monitor, choose Tools →Administration →Monitor →Performance
  →Database→(ST04) Activity. Choose button Detail analysis menu then button SQL processes. By
  default, the output is sorted by the CPU time and only connections used by the SAP System are
  shown. To display the SQL processes sorted by the host process id (pid), choose button Group
  by/Raw Display or F8.
  The Appl. Server column displays the host name of the database client. For all connections
  established by the SAP System, this is the name of the SAP application server to which the SAP user
  is connected.
  The host pid shows the process id of the SAP work process. Each SAP work process opens a number
  of database connections, each of which is identified by its SQL Server process id (spid) and labeled
  by the Application program name R3<T><nn>(<mm>)<type>:
       <T>: Work process identifier (D: Dialog, B: Background, S: Spool, U: Update, E: Enqueue, 2:
       Update2, ‘ ‘:external tool such as saplicense or tp)
       <nn>: Work process number
       <mm>: Number of connection
       <type>: Connection context controlled by the DBIF
         • comm rd: Committed read
         • sp create, single select: DDL, creation of stored procedures and single selects
         • unc rd: Dirty read selects
  Note: The Application program name is not shown in the default layout. Choose another layout to
  hide or display other columns.
  To display the command that is currently being executed for a connection, double-click a row.
  Choose Goto → All DB in the SAP menu to display connections to all databases run on the database
  server.




© SAP AG                                          ADM520                                              2-9
  SQL Server Process Monitor




   Process Info 97 items
   Proce. User                    Database        Status        Open Tran Command           Application

      1     system                no database cont background   0        LAZY WRITER
      3     system                master          sleeping      0        SIGNAL HANDLER
     10     sapprod\SAPServicePRD PRD             sleeping      0        AWAITING COMMAND   R3D00(1)sp create, single select OLEDB
     11     sapprod\ SAPServicePRD PRD            sleeping      0        AWAITING COMMAND   R3D01(1)sp create, single select OLEDB
     12     sapprod\ SAPServicePRD PRD            sleeping      0        AWAITING COMMAND   R3D00(0)comm rd OLEDB
     13     sapprod\ SAPServicePRD PRD            sleeping      0        AWAITING COMMAND   R3D00(2)unc rd OLEDB
     15     sapprod\ SAPServicePRD PRD            sleeping      0        AWAITING COMMAND   R3D01(2)unc rd OLEDB
     16     sapprod\ SAPServicePRD PRD            sleeping      0        AWAITING COMMAND   R3D02(2)unc rd OLEDB
      4     system                no database cont background   0        LOCK MONITOR
     20     sapprod\SAPServicePRD PRD             sleeping      0        AWAITING COMMAND   R3B11(1)sp create, single select OLEDB
     21     sapprod\ SAPServicePRD PRD            sleeping      0        AWAITING COMMAND   R3D02(1)sp create, single select OLEDB
     22     sapprod\ SAPServicePRD PRD            sleeping      0        AWAITING COMMAND   R3S12(0)comm rd OLEDB
     23     sapprod\ SAPServicePRD PRD            sleeping      0        AWAITING COMMAND   R3S12(2)unc rd OLEDB
      2     system                no database cont background   0        LOG WRITER
      5     system                master          background    0        TASK MANAGER




           SAP AG 2002




  You can also display the SQL processes by choosing Enterprise Manager →Management →Current
  Activity →Process Info. The Process Info view displays all SQL processes sorted by their login id. A
  colored globe indicates an active process. All inactive processes are marked by gray globes.
  The information displayed is read from the table sysprocesses, which contains information about the
  client and system processes running on SQL Server. Table sysprocesses is logically found in
  database master. It is built dynamically each time it is read, and therefore does not exist physically.




© SAP AG                                                            ADM520                                                           2-10
  Data Select from ABAP: Table Buffers




                                      select * from T100
                                           where ...
                      SAP Instance    endselect.



                                                   SAP Work Process


    SAP                       Database Access Agent
    Application                                                               Shared
    Server                                                                    buffers



                                           DBSL IF




       SAP AG 2002




  This section explains how the SAP system accesses the database when executing ABAP programs.
  In ABAP, you access the database by using Open SQL commands. The ABAP program and its Open
  SQL commands are independent of the database system.
  An Open SQL command is converted into a standard form and is passed to the Database Access
  Agent. The Database Access Agent checks whether the accessed table is buffered in an SAP table
  buffer. If the table is buffered, the data is retrieved from the SAP buffers and results are supplied
  without accessing the database.




© SAP AG                                       ADM520                                              2-11
  Data Select from ABAP: PREPARE

                       Permanent                             Direct SQL Statements
                       Stored Procedures



                                                                             select * from
                                      select * from TBTCO                   TBATG into table
                      SAP Instance         where ...                        I_TBATG for all
                                          endselect.                       entries … where ...
                                                                               endselect.
                                        SAP Work Process                   SAP Work Process


                          Database Access Agent                Database Access Agent
  SAP                       DBSL IF                             DBSL IF
  Application
  Server                                                        SELECT OBJECT, TABNAME,
                                                                INDNAME, TGORDER, FCT,
                                                                EXECMODE, SEVERITY,
                                                                GDATE, GUSER FROM TBATG
                            create                              WHERE FCT IN (@P1, @P2,
                            Y2R6GH78J676ABC0000TBTCO            @P3, @P4, ...)




       SAP AG 2002




  If the requested data is not found in the SAP buffers, the DBSL IF translates the Open SQL
  command to one or more stored procedures or to a direct SQL statement:
       Stored procedures are reusable collections of SQL commands, compiled in a single execution
       plan. Permanent stored procedures are stored in the SAP database and are not deleted after
       restarting SQL Server or the SAP instance. The DBSL IF creates a unique stored procedure
       name for each Open SQL command.
       Direct statements come from dynamic Open SQL statements, for example FOR ALL ENTRIES.
       Direct SQL statements are created directly on the SAP database without creating a stored
       procedure in prior.
  When an Open SQL statement is send to the database access agent, the operation is called the
  PREPARE operation. The following steps are performed:
       The statement is analyzed by the database access agent and its origin is classified.
       The open SQL statement is converted into a native SQL statement.
       For permanent stored procedures a unique stored procedure name is created by the DBSL agent.
       A set of parameters is generated from the WHERE condition for the statement text. A stored
       procedure text is generated consisting of the stored procedure name, the parameters and the text.
       The stored procedure is created on the database.
       For direct SQL statements the native SQL statement is executed on the database and cached in
       the procedure cache.




© SAP AG                                           ADM520                                           2-12
  Data Select from ABAP: OPEN and FETCH

                       Permanent                               Direct SQL Statements
                       Stored Procedures



                                                                               select * from
                                      select * from TBTCO                     TBATG into table
                      SAP Instance         where ...                          I_TBATG for all
                                          endselect.                         entries … where ...
                                                                                 endselect.
                                        SAP Work Process                    SAP Work Process


                          Database Access Agent                  Database Access Agent
   SAP                      DBSL IF                               DBSL IF
   Application
   Server                                                         Sp_executesql(SELECT
                                                                  OBJECT, TABNAME,
                                                                  INDNAME, TGORDER, FCT,
                                                                  EXECMODE, SEVERITY,
                            execute                               GDATE, GUSER FROM TBATG
                            Y2R6GH78J676ABC0000TBTCO              WHERE FCT IN (@P1, @P2,
                                                                  @P3, @P4, ...))




       SAP AG 2002




  When a permanent stored procedure is executed, the following operations are performed:
       The DBSL agent passes the command to execute a stored procedure including the parameters to
       SQL Server.
       The SQL statements in the stored procedure are compiled and optimized and an execution plan
       is created. If the stored procedure already exists and an execution plan is still in procedure cache,
       the execution plan is reused.
       SQL Server executes the stored procedure.
  Direct SQL statements are executed using system stored procedure sp_execute in the OLE DB
  interface. This stored procedure executes a transact-SQL statement that can be reused many times, or
  that has been built dynamically. The transact-SQL statement can contain embedded parameters.
  The execution of the stored procedure or the direct SQL statement starts with the OPEN operation.
  The group of records returned when a stored procedure or a direct SQL statement is executed is
  called a result set. While the stored procedure or direct SQL statement is executed, the result set is
  transferred from SQL Server to the requesting SAP work process. This is called the FETCH
  operation.




© SAP AG                                           ADM520                                              2-13
  Stored Procedure Name Cache


                      SAP Instance

                                                                     SAP Work Process     Stored
                                                                                        procedure
                         Database Access Agent                                            name
                                                                                          cache
                           DBSL IF            In
                            YR...     yes    name     no
                                             cache
                                               ?

   SAP                       exec in cache
                                   Y2R6...
   Application                                                     In
                                                           no              yes
                                                                sysobjects
   Server
                                                                    ?


                                exec YR...   create Y2R6…
                                                                     exec Y2R6...
                                             exec Y2R6...




       SAP AG 2002




  Before creating a permanent stored procedure, the DBSL IF checks if the unique name already exists
  in a stored procedure name cache. If a stored procedure name is not found in the stored procedure
  name cache, the DBSL IF checks whether it exists in the sysobjects table. If it is not found in the
  sysobjects table it is created on the database before it is executed. If the stored procedure is found in
  the name cache, it is executed directly.
  The names of permanent stored procedures are stored in a cache defined by the SAP instance profile
  parameter dbs/mss/pn_cache_size. This value gives the number of permanent stored procedure
  names in the name cache. The default value is 10000 for kernel releases 6.20; for later kernel
  releases the default value is 20000.
  Stored procedure names are stored physically in the sysobjects table, stored procedure texts are
  stored physically in the syscomments table in the SAP database.




© SAP AG                                             ADM520                                            2-14
  Stored Procedure Names




                            Y2R10000000BB1H1911DD03L
                            Y2A000001D9C7VK3725RSCROSS10i0o5ns
                            Y2E000011F2CBC50301REPOLOAD
                            Y2R65000168C73K0943ALPERFDBi11o18ns
                            Y2C00000453CBC21832DBSYNSEQ
                            Y2A0000032FCAUK4623SAPLSEUwCOMPONENTut01i6o1ns
     <SID>                  Y2R10000000C5GG0516TBTCP
                            Y2K00002B2DCBC21130DOKCLUi5o9ns
                            Y2R20000001C5GG1657TST01
                            . . .




       SAP AG 2002




  Naming conventions for permanent stored procedures:
  Y2<ModuleType><StatementId><Timestamp><ModuleName><Suffix>
     <ModuleType> identifies the type of module the statement comes from, for example, A for
     ABAP report, C for a C-program, R for a table name
     <StatementId> is usually the same as the statement id in the SAP System; statement Ids uniquely
     identify an SQL-DML statement in the SAP environment
     <Timestamp> usually specifies the generation time of an object, for example, the generation
     time of an ABAP report
     <ModuleName> is derived from the module name given in the statement id, for example the
     table name or report name
     <Suffix> is optional and indicates for example single selects (ut01 means up to one row)




© SAP AG                                     ADM520                                             2-15
  Windows Authentication


                 Presentation               SAP Application     Database
                 server                     server              server

                      SAP GUI                                                        <SID>
                                        sapusr
                  SAP user:     .....            SAP System            MSSQL
                  PW:           .....                                  server
                                                                                  pubs

                                                         <DOMAIN>\                            §
                                                         SAP_<SID>_
                                                         GlobalAdmin


                Windows user                SAP user            Login Id        Server role

 Example:       Joe                         SAPUSR              <DOMAIN>\       System
                                                                SAP_<SID>_      Administrator,
                                                                GlobalAdmin     default DB <SID>

 Authori-       Logon at                    Access to           Logon at        Access to
 zation:        workstation                 SAP objects         SQL Server      DB objects

       SAP AG 2002




  From Release 4.5A, the SAP System uses trusted connections when running with SQL Server
  exclusively. With this method, the SQL Server login id sapr3 is not used for SAP work process
  connections. The Windows user running the SAP service (SAPService<SID>) connects to the
  database server.
  Access to SQL Server is controlled by the Windows NT account or group, which is checked when
  logging on to the operating system on the application server. When SAP work processes connect to
  SQL Server, they request a Windows trusted connection to SQL Server. Windows does not open a
  trusted connection unless the SAP application server has successfully logged on using a valid
  Windows account. In this case SQL Server does not have to check the account. SQL Server gets the
  user account information from the trusted connection properties and matches them against the
  Windows accounts defined as valid SQL Server logins. If SQL Server finds a match, it accepts the
  connection.
  Refer to the SAP Security Guide Volume II Microsoft SQL Server under Windows NT or Windows
  2000 for detailed information.




© SAP AG                                              ADM520                                      2-16
  Databases in an SAP SQL Server System



  Databases


                      master               msdb               tempdb              <SID>




  Files


                             .ldf                .ldf                .ldf                .ldf
                      .mdf                .mdf                .mdf                .mdf




       SAP AG 2002




  During SQL Server installation, databases master, tempdb, and msdb are created.
       master: Records all of the system level information for a SQL Server system, that is, all login
       accounts, system configuration settings, existence of all other databases and the location of the
       primary files.
       msdb: Used by SQL Server Agent for scheduling alerts and jobs, e.g. regular backups.
       tempdb: Contains all temporary tables and is re-created every time SQL Server is started.
       Temporary tables are automatically dropped at disconnect, and no connections are active when
       the system is shut down.
  In addition to the standard databases created in every SQL Server installation, a database is created
  for the SAP System. This database receives a 3-letter identification (for example, PRD, TST).
  In this course, the SAP database is referred to as <SID>.




© SAP AG                                        ADM520                                                2-17
  SAP Database Files


 SAP Database                                   <SID>


                        <SID>DATA1
                         <SID>DATA2
                           <SID>DATA3                                <SID>LOG1



                                  <SID>DATAn                                 <SID>LOGm
 Files
                          ...


 Filegroup            PRIMARY



 File System          <drive>:\<SID>DATA1\<SID>DATA1.mdf             <drive>:\<SID>LOG1\<SID>LOG1.ldf

                       <drive>:\<SID>DATA2\<SID>DATA2.ndf            ...   <drive>:\<SID>LOGm\<SID>LOGm.ldf
                         <drive>:\<SID>DATA3\<SID>DATA3.ndf

                         ...    <drive>:\<SID>DATAn\<SID>DATAn.ndf


       SAP AG 2002




  Each database has two logical parts: data (data files) and transaction log (log files). When SQL
  Server and the SAP System are installed, the data files of the <SID> database are created in the
  directories <drive>:\<SID>DATA1\<SID>DATA1.mdf and
  <drive>:\<SID>DATAn\<SID>DATAn.ndf, where n is the number of the file. The data files may
  reside on different physical drives. SAP recommends storing the data files using RAID5. The
  standard installation creates 3 data files. This makes it easier to expand the database. See Chapter 6
  ‘Regular Maintenance and Error Handling'.
  The transaction log file is created in the directories <drive>:\<SID>LOG1\<SID>LOG1.ldf and
  <drive>:\<SID>LOGm\<SID>LOGm.ldf, where m is the number of the file. The log files must be
  mirrored. Hardware mirroring using RAID1 is strongly recommended. The standard installation
  creates one log file.
  After a standard installation, all SAP data files reside in the special filegroup PRIMARY.
  Stored procedure sp_helpfile returns the physical names and attributes of files associated with the
  current database. Use this stored procedure to determine the names of files attached to one database.




© SAP AG                                              ADM520                                                  2-18
  Summary



                     Now you are able to:

                        Explain the architecture of SQL Server and the SAP System
                        Name the different databases used under SAP, and explain
                        their function
                        Describe the authentication modes used under SAP
                        Explain how ABAP SQL statements are executed on the SAP
                        database




      SAP AG 2002




© SAP AG                               ADM520                                   2-19
  Further Documentation




                          Books Online
                          SAP Installation Guide
                          SAP Database Administration with
                          Microsoft SQL Server 2000
                          SAP Press, ISBN: 1-59229-005-1
                          SAP Security Guide Volume II
                          Microsoft SQL Server under Windows
                          NT or Windows 2000




      SAP AG 2002




© SAP AG                  ADM520                             2-20
  Unit Actions




                     ?    Exercises




                          Solutions




      SAP AG 2002




© SAP AG                 ADM520       2-21
           How the SAP System uses SQL Server Exercises

                   Unit: How the SAP System uses SQL Server
                   Topic: Database Access and Security

                   At the conclusion of this exercise, you will be able to:
                   • Change the password of Windows user <Domain
                     name>\SAPService<SID>
                   • Understand the way the SAP System opens connections to the database and
                      executes SQL statements on the database
                   For security reasons the customer wants to change the password of the
                   user who runs the SAP System.
                   The customer observes a high activity in one SAP work process and
                   wants to find out, what this SAP work process executes on the database.


   1-1     Changing the password of Windows user <Domain name>\SAPService<SID>
           1-1-1 Change the password of Windows user <Domain
                 name>\SAPService<SID>. Stop the SAP System using the SAP Microsoft
                 Management Console and stop Windows service SAP<SID>_<instance
                 number>. Restart the service and the SAP System.
                 What do you observe?
           1-1-2 Change the password of Windows user <Domain
                 name>\SAPService<SID> back to the old password and restart the service
                 and the SAP System. What happens?
           1-1-3 How do you correctly change the password of Windows user <Domain
                 name>\SAPService<SID>? What steps do you have to perform?

   1-2     Uncommitted read connections to the database
           1-2-1 Kill all uncommitted read connections from SAP work process 0, which
                 have been opened to SQL Server.
                 How do you determine the SQL Server Process IDs (spid)?
                 Which statement or tool do you use to kill SQL Server connections?
           1-2-2 Run program ZADM520NEST using transaction SE38. Observe the SQL
                 processes and their activities using transaction ST05.
                 Find out which SAP work process runs program ZADM520NEST.
           1-2-3 Did the SAP work process open new connections to the database?
                 What is the maximum number of uncommitted read connections?




© SAP AG                                   ADM520                                          2-22
           How the SAP System uses SQL Server Solutions

                   Unit: How the SAP System uses SQL Server
                   Topic: Database Access and Security


   1-1     Changing the password of Windows user <Domain name>\SAPService<SID>
           1-1-1 Stop the SAP System.
                 To change the password of <Domain name>\SAPService<SID>, use the
                 Windows User Manager under Start → Programs → Administrative Tools
                 → Computer Management. Under folder Local Users and Groups select
                 folder Users and mark user SAPService<SID>, click the right-mouse button
                 and choose Set password.
                 Stop the SAP System using the SAP System Management Console and stop
                 service SAP<SID>_<instance number> using the Computer Management
                 tool. When restarting the service error message

                  Could not start the SAP<SID>_<instance number> service on Local
                  Computer. Error 1069: The service did not start due to a Logon Failure.

                  occurs. The service cannot be restarted because the authorization is not
                  correct. Windows authorization is checked for service
                  SAP<SID>_<instance number>, but not when the work process is started.
                  The login attempt of the work process connections to SQL Server fails.

           1-1-2 Change the password back using the Windows User Manager.
                 Start the service SAP<SID>_<instance number> again.
                 Start the SAP System.
           1-1-3 Stop the SAP System and service SAP<SID>_<instance number>. Change
                 the password of user <Domain name>\SAPService<SID>. Specify the new
                 password in the service used for the SAP System. Select service
                 SAP<SID>_<instance number> in the Computer Management tool, click
                 the right mouse button and select Properties. In the Log On tab change the
                 password. Start the service and the SAP System again.

   1-2     Uncommitted read connections to the database
           1-2-1 To display the connections each SAP work process has opened to the
                 database, call monitor Database Performance Analysis: SQL Server
                 Database Overview (Transaction ST04). Choose Detail Analysis Menu →
                 SQL processes.
                 Choose layout /4ADM520 to sort the connections by their host process IDs
                 (Host PID). Check only processes opened by the SAP System, i.e. processes
                 where the login ID <Domain name>\SAPService<SID> is displayed in
                 column SQL Server Login.
                 The column Application program shows names starting with: R3D00(x),
                 where D is the work process type (dialog), 00 the number of the SAP work
© SAP AG                                  ADM520                                            2-23
                  process and x is the number of the connection. The uncommitted read
                  connections are identified by unc rd OLEDB in the program name. The spid
                  is displayed in column SPID.
                  Alternatively, use the SAP Process Overview (transaction SM50) to display
                  the Host PID belonging to SAP work process 0.

                  To kill a SQL Server connection use transact-SQL statement KILL or
                  choose the Enterprise Manager, mark the connection in the SQL Server
                  Process Monitor, click the right-mouse button and choose Kill Process.
           1-1-2 In a new window, call transaction ST05 and start a SQL trace.
                 Call transaction SE38, and execute program ZADM520NEST.
                 Call the SAP Process Monitor, use transaction code ST04, and choose Detail
                 Analysis Menu → SQL processes. To group the output, select Group
                 by/Raw display.
                 To find out which SAP work process runs ZADM520NEST, look for column
                 Report.
           1-1-3 To find out the connection numbers used by the work process:
                 Display the trace by choosing button Display trace in transaction ST05.
                 Choose Trace List → Display Extended Trace List, and look for program
                 ZADM520NEST in the column Program. In the comment in column
                 Statement the stored procedure name and the connection number are
                 displayed.
                 The connection number is also displayed in the Database Process Monitor in
                 the column Application program as R3D00 (X).

                  ZADM520NEST executes nested selects on database table DD32S. For each
                  nested select, a new uncommitted read connection to the database is opened.

                  A maximum of 40 dirty read connections will be opened.




© SAP AG                                  ADM520                                           2-24
  Performance Monitoring and Tuning




           1   Introduction

           2   SQL Server Architecture


           3   How the SAP System uses SQL Server


       4 Performance Monitoring and Tuning
        5      Database Backup

        6      Database Restore

        7      Regular Maintenance and Error Handling




      SAP AG 2002




© SAP AG                                  ADM520        3-1
  Performance Monitoring and Tuning



                     Contents
                        Database configuration
                                Cache size tuning / CPU utilization / Parameter tuning / Disk
                                layout
                        Application problems
                                Lockwaits / Expensive statements



                     Objectives
                     At the end of this unit, you will be able to:
                        Monitor the performance of SQL Server
                        Identify the source of database performance problems
                        Find solutions to the performance problems detected




      SAP AG 2002




© SAP AG                                        ADM520                                          3-2
  Sources of Database Performance Problems




                                     Performance problems
                                            due to:

                                    1                               2




                      Poor configuration                       Inefficient applications




         Poor database                   Poor hardware
          configuration                   configuration


       SAP AG 2002




  Poor database performance normally results from problems with database configuration, application
  coding problems, or hardware capacity. This section explains how to identify and correct these
  problems.
  The first step is normally to tune the database and hardware configuration. In the Database Monitor,
  you:
     Check the general database configuration
     Use the database tools to verify the efficiency of the application coding
  Note: Tuning inefficient application coding may allow you to avoid buying new hardware.
  Changing the database configuration may affect the amount of hardware required. This unit therefore
  also explains how to check if your current hardware configuration allows the necessary database
  configuration changes.




© SAP AG                                      ADM520                                               3-3
  Database Configuration




                                       Poor configuration




               Poor database                                          Poor hardware
                configuration                                         configuration



 Data cache                             Disk              Main
                                                         memory                            Disks
                                       layout
                      Parameter                                             CPU
                       settings



       SAP AG 2002




  This section discusses the following areas of database configuration:
     Data cache
     SQL Server configuration parameters
     Disk layout
  To detect data cache problems, the cache hit ratio is the main indicator.
  An improper separation of several logical database portions and other frequently accessed files may
  lead to disk hot spots, which results in high disk response times.




© SAP AG                                      ADM520                                                3-4
  Cache Usage


                              RDBMS       Memory
                                          Area                      Data cache




         SELECT *
           FROM MARA
             WHERE ...                     SQL connections          Procedure cache


     SAP Work                                                           parsed SQL
      Process                               create proc               statements and
                                                                      execution plan



                              Database
                                            Tables        Indexes      Procedure Texts
                                                           MARA_1
                                             MARA                      Y2R0000064B69RC5522MARA


                                                          MARA_0




       SAP AG 2002




  An Open SQL statement coming from an ABAP program is executed as a stored procedure or as a
  direct statement in SQL Server.
  For each table, there is a set of common statements such as INSERT, UPDATE and DELETE
  operations. A permanent stored procedure exists for each of these statements. These stored
  procedures can be reused.
  To shorten access times, the execution plan of a stored procedure is stored in the procedure cache.
  SQL Server automatically allocates the necessary space from the available memory, and dynamically
  adjusts the size of the procedure cache.
  For a detailed description of this stored procedure mechanism, see Unit 2: How the SAP System uses
  SQL Server.




© SAP AG                                      ADM520                                              3-5
  Statement Execution (1)


                                RDBMS       Memory
                                            Area                      Data cache




         SELECT *
           FROM MARA
             WHERE ...                       SQL connections          Procedure cache

     SAP Work                                                             parsed SQL
      Process                                  load proc                statements and
                                                                        execution plan



                                Database
                                              Tables       Indexes        Procedure Texts
                                                             MARA_1
                                               MARA                      Y2R0000064B69RC5522MARA


                                                             MARA_0




       SAP AG 2002




  If the stored procedure plan does not exist in the procedure cache, the procedure text in the database
  is used to generate the execution plan.




© SAP AG                                        ADM520                                                3-6
  Statement Execution (2)


                                RDBMS        Memory
                                             Area                          Data cache




                                                                2
         SELECT *                                                                  2
           FROM MARA
             WHERE ...                        SQL connections              Procedure cache

     SAP Work
      Process                                   exec proc

                                                                       1

                                Database
                                               Tables          Indexes        Procedure Texts
                                                              MARA_1
                                                MARA                          Y2R0000064B69RC5522MARA


                                                              MARA_0




       SAP AG 2002




  The execution plan determines which indexes are to be used for table access (step 1).
  Once the execution plan of the stored procedure is in the procedure cache, the procedure can be
  executed.
  The required parts of the used indexes and tables are transported from the disk into the data cache for
  further processing (step 2). It is helpful if the pages required are already stored in the data cache from
  previous use.
  The Query Processor selects the data required, and returns it through the database interface to the
  SAP work process.




© SAP AG                                         ADM520                                                  3-7
  Cache Usage Analysis




                                                                                           1 > 6h


           3
           4
           5                                                                               2 > 98



                                       1   > 500




       SAP AG 2002




  To display the most important performance parameters of the database, call Transaction ST04, or
  choose Tools → Administration → Monitor → Performance → Database → Activity. An analysis is
  only meaningful if the database has been running for several hours with a typical workload. To
  ensure a significant database workload, we recommend a minimum of 500 CPU busy seconds. Note:
  The default values displayed in section Server Engine are relative values. To display the absolute
  values, press button Absolute values. Check the values in (1).
  The cache hit ratio (2), which is the main performance indicator for the data cache, shows the
  average percentage of requested data pages found in the cache. This is the average value since
  startup. The value should always be above 98 (even during heavy workload). If it is significantly
  below 98, the data cache could be too small. To check the history of these values, use Transaction
  ST04 and choose Detail analysis menu →Performance database. A snapshot is collected every 2
  hours.
  The current size of the data cache (3) is displayed along with its target server memory, which is the
  total amount of dynamic memory the server can consume since server start (4). Memory setting (5)
  shows the memory allocation strategy used, and shows the following:
     FIXED: SQL Server has a constant amount of memory allocated, which is set by SQL Server
     configuration parameters min server memory (MB) = max server memory (MB).
     RANGE: SQL Server dynamically allocates memory between min server memory (MB) < > max
     server memory (MB).
     AUTO: SQL Server dynamically allocates memory between 4 MB and 2 PB, which is set by min
     server memory (MB) = 0 and max server memory (MB) = 2147483647.
     FIXED-AWE: SQL Server has a constant amount of memory allocated, which is set by min server
     memory (MB) = max server memory (MB). In addition the address windowing extension
     functionality of Windows 2000 is enabled.
  A detailed description of the memory parameters is found in the following slides.

© SAP AG                                       ADM520                                               3-8
  Memory Settings

           Windows Startup Parameters
                /3GB

                /pae
           SQL Server Memory Parameters
                max server memory
                min server memory
                set working set size
                awe enabled
           Size of physical and virtual memory




       SAP AG 2002




  Managing SQL Server memory involves consideration of the Windows startup parameters
    /3GB
    /pae
 and the SQL Server configuration parameters
    max server memory
    min server memory
    set working set size
    awe enabled
  How memory should be configured for SQL Server further depends on:
    the coexistence of other SAP components on one physical machine (i.e. either a Central Instance
    or an Update Instance on the same machine as SQL Server runs)
    the amount of available RAM and virtual memory
  Recommended memory settings are detailed in SAP note 327494.




© SAP AG                                      ADM520                                              3-9
    Windows Startup Parameters


    Memory Address Space                                  Memory Address Space
                                  boot.ini
        0                            /pae                  0
                                                                                 to allow 1GB system
                                                                                 address space

     1 GB                                               1 GB
                                                                             and

     2 GB                                               2 GB
                                                                                 2-3 GB
                                                                                 user process space

     3 GB                    to allow one process the   3 GB
                             use of more than 4GB
                             Physical memory
                                                                                   boot.ini
     4 GB                                               4 GB
                                                                                      /3gb
       …                                                   …



   64 GB                                                16 GB




          SAP AG 2002




  Standard Windows 2000 Server provides support for up to 4 processors and 4 GB of RAM.
  Windows 2000 Advanced Server supports up to 8 processors in an Symmetric Multi Processing
  (SMP) system and, with the Address Windowing Extensions (AWE), can support up to 8 GB of
  memory. Windows 2000 Datacenter supports up to 32-processor SMP systems and a maximum
  physical memory of 64 GB.
  AWE lets applications such as SQL Server Enterprise Edition use addresses above 4 GB to cache
  data in memory. AWE maps up to 64 GB of physical memory into a 32-bit (4 GB) virtual address
  space in 4 KB pages. AWE extends the Windows page tables from 20 bits to 24 bits. The extra four
  bits enable the use of 36-bit physical addresses. To enable the Physical Address Extension (PAE)
  add /pae in the boot.ini file.
  The /3GB startup parameter is a switch informing the operating system to use only 1 GB of memory
  for system addresses which then permits an increase in the amount of user process space from the
  default of 2 GB to 3 GB. This startup parameter is only available with the Advanced Server or
  Datacenter Windows 2000 products. This feature is enabled by adding /3GB in the boot.ini file.
  When the /3GB switch is used in conjunction with the /PAE switch, the operating system does not
  use any memory in excess of 16 GB.




© SAP AG                                       ADM520                                                 3-10
  SQL Server Memory Parameters


  Memory Address Space                                            Memory Address Space

                               max server memory (MB)                                           awe enabled = 1
                               min server memory (MB)
                                                                                                Used to access up to 8 GB of
   SQL Server                  used to dynamically adjust          SQL Server                   memory
   memory                      memory that SQL Server needs        memory                       on Windows 2000 Advanced
                                                                                                Server and
                                                                                                64 GB on Windows 2000 Data
                                                                                                Center



             SQL Server Query Analyzer
               detail stats.




                                                                           SQL Server Query Analyzer
                                                                                detail stats.




             sp_configure
                                                                           sp_configure
             ‘max server memory
                                                                           ‘awe enabled’, ‘1'
             (MB)’, ‘2048'
                                                                           reconfigure with
             reconfigure with
                                                                           override
             override




       SAP AG 2002




 SQL Server is able to dynamically adjust the amount of memory that it needs for optimal performance. With
 the use of the max server memory and min server memory parameters the default behavior of automatic
 memory adjustment can be overridden. This can help in situations where the SAP System runs as a combined
 instance on the same machine.
 Parameter awe enabled allows SQL Server to be able to use the AWE (Address Windowing Extension) feature
 of Windows 2000. By default, SQL Server can use a maximum of 3 GB of RAM. With Windows 2000,
 applications can use the AWE API to address more RAM. In Windows 2000 Advanced Server up to 8 GB of
 RAM can be used, in Windows 2000 Datacenter Server up to 64 GB of RAM can be used. Using AWE in SQL
 Server implies the following:
     SQL Server no longer dynamically manages the size of the address space.
     All memory is acquired at startup of SQL Server until shut down.
     Memory pages using AWE will not be paged out.
   When parameter awe enabled is set, SQL Server memory must be fix. Additionally set working set size must
    be disabled, or network problems may occur when under stress. Windows must be enabled to run AWE by
    the boot.ini startup parameter /pae.
 In order to enable AWE use by SQL Server, SQL Server must be run by an account with the permission "Lock
 Page in Memory". Additionally, there must be at least 3GB of free memory available on the computer.
 When awe enabled is set a message is recorded in the SQL Server errorlog "Address Windowing Extension
 enabled".
 Setting set working set size to 1 means that Windows will not swap out SQL Server pages. If the values of max
 and min server memory are different, then the set working set size value should be set to 0. Even though set
 working set size can be set = 1 when memory has been fixed it is still recommended to set it equal to zero.
 SQL Server parameters are set using stored procedure sp_configure. See Books Online for more details.


© SAP AG                                                      ADM520                                                           3-11
  CPU Utilization Analysis




    1




    2
    3




         SAP AG 2002




  Transaction ST04 also shows the CPU usage of SQL Server. The header section of the ST04 main
  screen displays the number of CPUs installed in the computer where SQL Server is running, as well
  as the number of CPUs that can be used by SQL Server (1). Available CPUs are determined using a
  bit mask given in the SQL Server parameter affinity mask.
  In the section Server Engine, the percentage of CPU seconds used by SQL Server is displayed in the
  field CPU busy (2). The field CPU idle (3) displays the percentage of seconds where the CPUs that
  were available for SQL Server were not used. The value IO busy, which is part of CPU busy, shows
  the percentage of seconds that was used for IO operations issued by SQL Server.
  To display absolute values, press button Absolute values.
  Note: Each CPU counts separately, that is, if 3 CPUs are used in 2 seconds, CPU busy = 6. If 1 CPU
  was used and 2 were unused in 2 seconds, CPU idle = 4.
  Usually, the numbers displayed have accumulated since SQL Server startup. These numbers are not
  very informative as the large amount of idle time at night covers up potential bottlenecks during peak
  times. Therefore, you should use the Reset and Since Reset functions to get the times during a
  representative time frame. The amount of elapsed time since the last Reset is also displayed. Initially
  and after a Refresh, this shows how much time has elapsed since SQL Server started.
  The value for CPU busy (2) should always be approximately < 70%, that is, CPU idle > CPU busy /
  2.
  To check the history of the CPU utilization, use Transaction ST04 and choose Detail analysis menu
  → Performance database.




© SAP AG                                       ADM520                                                3-12
  Cache and CPU Tuning

                                                              ?


                                               Poor SQL           Yes                  Tune poor
                                              statements?                              statements

                                                    No

    Increase
   SQL Server
     memory                                   Yes       Yes         2 * CPU idle              Increase
                            Cache hit ratio
     and set                   > 98%?               +               > CPU busy?               CPUs for
  memory mode                                                                                SQL Server

                              No                                            No

                  No                                              All CPUs available       No
                             OS paging?
                                                                   for SQL Server?


    Increase               Yes                                                Yes               Add CPU(s)
   server main                                                                                   to server
     memory


       SAP AG 2002




  Before tuning memory or CPU for SQL Server, you should make sure that poor SQL statements are
  already tuned since they can significantly affect memory or CPU utilization. (How to detect and tune
  poor SQL statements is discussed later in this unit).
  Before increasing SQL Server memory, you must check if there is sufficient main memory available.
  Operating system paging is a sign of insufficient main memory; especially on the database server.
  Call Transaction ST06 and choose Detail analysis menu →Previous hours →Memory. The amount
  of page in per hour should not exceed 20% of the available physical memory. For optimal
  performance, the value should be 0.
  The SQL Server memory parameters should be set as described in SAP note 327494.
  Before increasing the number of CPUs on the server, check the usage of the CPUs for other
  applications that could be moved to other servers (for example, SAP work processes). Also check the
  CPU utilization history of the Windows processes. Use transaction ST06, choose Detail analysis
  menu, then Top CPU processes. Alternatively, use the Windows Task-Manager.




© SAP AG                                       ADM520                                                     3-13
  Other SQL Server Parameter Settings




                        Parameter name                      Value
                        affinity mask                       0
                        allow updates                       0
                        awe enabled                         0
                        c2 audit mode                       0
                        cost threshold for parallelism      5
                        default language                    0
                        fill factor (%)                     0
                        index create memory (KB)            0
                        lightweight pooling                 0
                        locks                               0
                        max degree of parallelism           1
                        max server memory (MB)              2147483647
                        max worker thread                   255
                        media retention                     28
                        min memory per query (KB)           1024
                        min server memory (MB)              0
                        network packet size (B)             8192
                        open objects                        0
                        priority boost                      1
                        recovery interval (min)             0
                        set working set size                0
                        show advanced options               1
                        user connections                    0
                        user options                        0

       SAP AG 2002




  SQL Server configuration parameters can be checked using transaction ST04 → Detail Analysis
  Menu → SQL Server Parameters.
  A value 0 for SQL Server configuration parameters locks, open objects, index create memory (KB),
  and user connections indicates that memory is dynamically allocated for these objects as required.
  The amount of memory available for the data cache decreases if the number of these objects
  increases.
  SQL Server can execute queries using more than one CPU. This is called parallel execution. SQL
  Server executes queries using only one CPU until the estimated execution time is below the value (in
  seconds) of Cost threshold for parallelism. A value of 0 for cost threshold for parallelism means that
  no queries are executed in parallel. This parameter is ignored if only 1 CPU is available to SQL
  Server or if max degree of parallelism is set to 1. The value max degree of parallelism shows the
  number of CPUs SQL Server can use for parallel statement execution (0 = all available CPUs, 1 = no
  parallel executions).
  See Books Online for a detailed description on all SQL Server configuration parameters and SAP
  note 327494 for current recommendations.




© SAP AG                                                 ADM520                                     3-14
  Optimal Disk Layout (Data Separation)




      paging file(s)          SQL Server              <SID>
                               tempdb             Transaction log



                                                                                     <SID>
                                                                                    data files




     1-4 GB              min. 300 MB               min. 1 GB                   min. 20 GB


                              Suggested disk requirements

       SAP AG 2002




  Disk I/O is the most time-consuming operation for the database system. Therefore, you can
  significantly improve performance by:
     Using fast disks and I/O controllers
     Physically separating files with high I/O activity so that they can operate concurrently
  Maximum throughput can be achieved by placing the following four types of files on separate
  physical disks (or disk systems/controllers):
     paging file(s)
     SQL Server tempdb file(s)
     <SID> transaction log file(s)
     <SID> data files
  To check the hardware resources refer to the SAP Installation Guide found under
  http://services.sap.com/instguides. Above the minimum requirements for a small SAP system
  installation (application server and database server) are given. Depending on the amount of data
  involved, the requirements will change.




© SAP AG                                       ADM520                                                3-15
  RAID Technology


           RAID 0




           RAID 1




           RAID 5




           RAID 0+1
           (= RAID 10)



       SAP AG 2002




  RAID technology provides two basic functions:
     Higher I/O performance by striping data over multiple physical disks, and therefore achieving an
     even I/O distribution over all disks
     Fault tolerance by mirroring disks or adding parity information
  RAID levels 2, 3, and 4 are not as efficient and are not commonly used. For more information about
  these levels, refer to SQL Server Books Online.
  RAID 0 is called disk striping. All read/write operations are split into slices (usually 16-128 KB) that
  are spread across all disks in the RAID array. There is no redundancy added. Read and write
  performance is improved by equally distributing workload on all participating disks. This level
  provides the highest performance of all RAID levels.
  In RAID 1, every physical disk is mirrored. All write operations are written to the original disk and
  to the disk mirror. Write performance may be a little lower as both disks’ writes must be
  synchronized. With mirroring, one disk may fail without data loss.
  In RAID 0+1 (also called RAID 10), every disk in the disk stripe set is mirrored. It provides nearly
  the same performance as RAID 0 but adds redundancy at the cost of doubling the number of disks.
  RAID 5 combines disk striping with parity. One physical disk is added to hold the parity
  information. The parity information is also striped along all disks. Every write operation needs 2
  physical reads and 2 physical writes (read data + parity and write new data + new parity). It offers
  less fault tolerance, as only one disk in the whole array may fail without data loss. The total disk
  storage is not available to store data.. There needs to be enough free space available for parity
  information to equal the size of the smallest disk in the array.




© SAP AG                                        ADM520                                                3-16
  Optimal Disk Layout (Using RAID Technology)




                                                       <SID>
                                                   Transaction log




                                                                                   RA
                                                                                     ID
                                                                                      5
       paging file(s)        SQL Server
                              tempdb           RAID 0+1

        RAID 0                RAID 0               Transaction log
                                                                                        <SID>
                                                                                       data files
                                                       <SID>
     (RAID 0+1 for         (RAID 0+1 for
    high availability)    high availability)

                                                                              (or RAID 0+1)

        1-4 GB           min. 300 MB               min. 1 GB                   min. 20 GB


                              Suggested disk requirements
       SAP AG 2002




  The paging files and the SQL Server tempdb files are temporary data files that do not require
  redundancy to ensure data security. For high availability, RAID 0+1 should be used. Since these files
  are write-intensive, they should not be placed on RAID 5 systems. If they are small enough to fit on
  one disk, RAID technology is not required.
  Transaction log files must be placed on hardware mirrored disks (RAID 1) to ensure data security. If
  more than one disk is used for transaction log files, additional striping should be used (RAID 0+1).
  Data files must be placed in a RAID 5 system to ensure data security (RAID 0+1 would be faster but
  more expensive).




© SAP AG                                       ADM520                                               3-17
  Analyzing Disk I/O Problems: SQL Server I/O
  Statistics




           1                           > 10




           2
           1




       SAP AG 2002




  SQL Server collects an I/O statistic for the data and log files for all the databases in the SQL Server.
  You can use these I/O statistics to compare the activity for each data file, and to determine whether
  activity is evenly distributed over the data files.
  The I/O statistics are included in the SAP SQL Server database monitor ST04 → Detail Analysis
  menu → IO per File. You can choose between tempdb and the SAP database.
  The column 'ms/IO' displays the average wait time for one operation in ms and is calculated from the
  output of SQL Server system function fn_virtualfilestats. For acceptable performance 'ms/IO' should
  be below 10ms for all data files (1). For the transaction log files the average wait time is much
  smaller than for data files (2).
  fn_virtualfilestats counts the statistics since SQL Server start.
  For more information, see SQL Server Books Online and SAP note 521750.




© SAP AG                                        ADM520                                                3-18
  Analyzing Disk I/O Problems: Performance Monitor




                                                100% Disk time

                                                   Disk queue
                                                  length up to
                                                     45 >> 4
                                                    (2 disks)




       SAP AG 2002




  Disk I/O performance can be measured using the Performance Monitor (from Start, choose Settings
  → Control Panel Administrative Tools →Performance Monitor).
  Note: Windows does not show the physical disks in the RAID system. Therefore, you first need to
  divide the performance counters by the number of physical disks. Then, for RAID 1 (and RAID
  0+1), multiply the number of I/O write requests by 2. For RAID 5, multiply the writes by 4 to get the
  number of physical disk I/O requests.
  When ”% Disk Time = 100” you must check I/O performance. The disk queue length (read + write
  queue) per physical disk should not significantly exceed 2, otherwise there is an I/O bottleneck.
  To get disk performance data, enable the disk performance object using command diskperf -y (a
  Windows reboot is required afterwards).
  For long term monitoring, use the Performance Monitor to create a log file with snapshot data from
  the specified update intervals.
  For more information about using the Performance Monitor, see SAP note 110529 and Windows
  Online Help.




© SAP AG                                       ADM520                                              3-19
  I/O System Tuning
                                             ?
                                                       Check all logical
                            Slow RAID        No         Disks with the
                            identified?                 Performance
                                                           Monitor
                                   Yes


                       SQL Server I/O stats
                      show avg. wait time for
                         one disk > 10 ms



                                   Yes

                         Avg. disk queue
                        length > 2 * phys.
                          Disks in RAID


                                   Yes
                      Change hardware
                       configuration



       SAP AG 2002




  To find the peak throughput for a whole RAID system or the whole I/O bus, measure disk I/O
  performance on all physical disks.
  If the SQL Server I/O statistics show an average wait time of more than 10 ms during peak
  workload, check the I/O system using the Windows performance monitor. The average disk queue
  length per physical disk should not significantly exceed 2.
  If you detect an I/O bottleneck, ask your hardware partner to check your hardware configuration.




© SAP AG                                          ADM520                                         3-20
  Database Configuration: Summary



                                        Poor configuration


               Poor database                                        Hardware
                configuration                                      configuration



 Data cache                              Disk           Main
                                                       memory                         Disks
                                        layout
                      Parameter                                         CPU
                       settings
    Cache                                              Operating
                                        High I/O                                      Disk
   hit ratio                                            system
                                         times                                     response
    > 98%                                               paging          CPU          times
                   SQL Server           Select 1 row
                  CPU utilization                        Page in    utilization
                                        via prim. key                              Wait queue
                                                      < 100 MB / h 2 * idle > busy and low
                      2 * idle > busy   < 10 ms
                                                                        (total)   transfer rate
                       (SQL Server)
       SAP AG 2002




© SAP AG                                      ADM520                                          3-21
  Application Problems




                                 Performance problems
                                        due to:
                  Poor configuration




           Poor database
           configuration
                              Poor hardware
                              configuration
                                              Inefficient applications




                                                 Too many                    Poorly
                           Lockwaits           unnecessary                  qualified
                                                statements                 statements




       SAP AG 2002




  Application problems can be classified into three groups:
   Lockwaits
   - These are caused by long running transactions that are holding locks, which are blocking other
   applications that want to get the same locks.
   Too many unnecessary statements
   - Poor coding causes many small statements to be read in a loop or results in data being read twice.
   Poorly qualified statements, where...
    - Insufficient selection criteria is given
    - The database optimizer has no efficient access path to the requested data (for example, in the
   case of missing indexes)
   - The database optimizer does not find the best access path (due to an inappropriate execution
   plan)




© SAP AG                                        ADM520                                             3-22
  Exclusive Lockwaits



  Frontend
  users




   SAP Application Server

                                                                    If many users are waiting
  SAP Work processes                                                for a specific lock, they may
                                                                    block all other users.



  Database

                                                          data
                                                          row




       SAP AG 2002




  In this example, a large number of SAP user requests are channeled into a smaller number of SAP
  work processes on the application server.
  A user holding a lock occupies an SAP work process. Other users trying to apply the same lock will
  have to wait and at the same time occupy their own SAP work process.
  As the number of lockwaits increases, fewer and fewer SAP user requests can be processed by the
  available SAP work processes.
  In the worst case (lockwaits = number of SAP work processes), a small number of users can cause
  the entire SAP System to freeze.




© SAP AG                                      ADM520                                             3-23
  Lockwait Situations



   SAP                        Update Requests                         WAITING ...
   Work-                      MARA MARA Lock
 Process 4


  SAP                       Update Requests             WAITING!           Acquires
                                                                                    Working...
  Work-                     MARA MARA Lock                                MARA Lock
Process 3


   SAP                Update Requests WAITING! Acquires
   Work-                                                         Working... Commit
                      MARA MARA Lock          MARA Lock
 Process 2


  SAP         Update      Acquires   A long period
  Work-                                            Commit
              MARA       MARA Lock   of processing
Process 3


                                                                                                Time
                 Locked                WP 1                        WP 2               WP 3
             MARA by:

       SAP AG 2002




  When updates are programmed, the time that the program holds a lock should be kept as short as
  possible.
  This diagram illustrates how the wait time to acquire a lock increases when several work processes
  are waiting to acquire a lock on the same object, and a long time is needed to process the update.
  In order to update table MARA, work process 1 acquires a lock. Then, work process 2 requests a
  lock on the same object, but has to wait for work process 1 to release its lock. Work process 1 takes a
  long time processing before it performs a commit and releases the lock.
  While work process 2 is waiting to acquire the lock, a lock request from work process 3 is pending.
  Work process 3 has to wait until work process 2 performs a commit. Work process 3 has to wait
  even longer than work process 2 because it has to wait for work process 1 and 2 to complete their
  commit.




© SAP AG                                       ADM520                                                3-24
  Monitoring Lockwaits (1)




                                            ANJA                      SAPPROD
                                            EARLYW                    SAPPROD
                                                                      SAPPROD
                                                                      SAPPROD
                                            EARLYW                    SAPPROD
                                                                      SAPPROD
                                                                      SAPPROD
                                                                      SAPPROD
                                            ANJA




       SAP AG 2002




  Exclusive lockwait situations due to database locks are shown in the SAP Database Monitor (Call
  Transaction ST04 and choose Detail analysis menu →Exclusive Lockwaits).
  The Host PID is the process ID of an SAP work process. The owner of the lock is shown in a blue
  line and holds the status granted shown by a green signal in the first column. All blocked processes
  are shown below and have the status waiting indicated by the red signal.




© SAP AG                                       ADM520                                               3-25
  Monitoring Lockwaits (2)


       SQL Server Enterprise Manager - [Console Root\Microsoft SQL Servers\SQL Server Group\207.105.67.113 [Windows NT]\Management\Cu...     _      x
    Console    Window    Help                                                                                                                _      x
     Action   View   Tools

                                                                           51 Items

        Console Root
              Microsoft SQL Servers
                  SQL Server Group                                            spid 1     spid 10    spid 11 spid 179 spid 13       spid 14    spid 15
                                                                                                          [blocked by 177]
                        SAPPROD [Windows NT]
                             Databases
                             Data Transformation Services                    spid 16     spid 17    spid 18   spid 19   spid 20   spid 21    spid 22
                             Management
                                SQL Server Agent
                                 Backup                                       spid 23    spid 24    spid 25   spid 26   spid 27 spid 109      spid 29
                                 Current Activity - 14.12.2002 11:35:10                                                       [blocked by 179]
                                      Process Info
                                      Locks / Process ID
                                      Locks / Object                          spid 50    spid 51   spid 52    spid 53 spid 54      spid 55   spid 56
                                 Database Maintenance Plans                         [blocked by 109]                [blocked by 109]
                                 SQL Server Logs
                             Replication
                             Security                                         spid 57    spid 58    spid 59   spid 60   spid 61   spid 62    spid 63
                             Support Services

                                                                              spid 64    spid 65    spid 66   spid 67   spid 68   spid 69    spid 70



                                                                             spid 177    spid 178
                                                                            [blocking]



        SAP AG 2002




  You can also view the current lockwait situation using the Enterprise Manager.
  On the database server, choose Management →Current Activity →Locks/Process ID. All the SQL
  process IDs are displayed.
  Blocking is written under the icons representing the SQL process IDs that have a blocking lock
  holder.
  Blocked By <n> is written under the icons that have a lock waiter that is waiting for a lock held by a
  SQL process ID. Blocked SQL processes are additionally marked with a red square in their icon.
  The head of a locking chain is marked with a red exclamation mark (spid 177 in the above example).




© SAP AG                                                                  ADM520                                                                        3-26
  Too Many Statements


                              RDBMS       Memory
                                          Area                     Data Cache


    WHILE ...
      SELECT *
      FROM MARA
      WHERE ...
    ENDWHILE.
                                           SQL Connections         Procedure Cache

                SAP Work
                 Process



                              Database
                                            Tables         Indexes     Procedure Plans
                                                          MARA_1
                                                                      Y2R0000064B69RC5522MARA
                                             MARA
             SQL Trace
                                                          MARA_0




       SAP AG 2002




  In this example, the same SELECT statement may be executed several times. If the conditions in the
  WHERE clause do not change, the same data is read several times from the database.
  To find this kind of duplication, you can run an SQL Trace (Transaction ST05). This logs the
  communication between the SAP work processes and the database for a particular group of SAP
  users.




© SAP AG                                      ADM520                                             3-27
  SQL Trace




                            OPEN            SELECT WHERE „SPRSL“ = @P000 AND „ARBGB“ = @P001 /* R3:
                            FETCH
                            OPEN            SELECT WHERE „SPRSL“ = @P000 AND „ARBGB“ = @P001 /* R3:
                            FETCH
                            OPEN            SELECT WHERE „SPRSL“ = @P000 AND „ARBGB“ = @P001 /* R3:
                            FETCH




       SAP AG 2002




  To start, stop, and display the SQL Trace, call Transaction ST05 or choose Tools →Administration
  →Monitor →Traces →Performance Trace.
  To see the time stamps and program names, select button Extended List. All statements that were
  sent from the SAP work processes to the database are displayed. Identical statements are probably
  caused by one statement being executed repeatedly. To directly access the corresponding ABAP
  program line, choose button Display Call Position in ABAP Program.
  The column Records shows the number of read or manipulated rows. The column Return code (RC)
  shows the result value of the database operation. The duration (execution time) of the statement is
  shown in column Duration. The time is specified as: seconds.milliseconds.microseconds
  The column Statement shows the statement executed on the database which is either the contents of
  the stored procedure or a direct statement. In case the content of a stored procedure is shown, the
  name of the stored procedure is appended to the statement.




© SAP AG                                       ADM520                                                 3-28
  Poorly Qualified Statements (1)




              139.271   MARA   PREPARE       SELECT WHERE „MANDT“ = @P000 AND „BRGEW“ = @P001 /* R3:
              147.068   MARA   OPEN
                2.809   MARA   FETCH     0
                  145   MARA   FETCH     1
                  290   MARA   FETCH     1
                1.794   MARA   FETCH     0




                         High execution time + returning few records
       SAP AG 2002




  The runtime (duration) of the statement is highlighted in red if it exceeds a given threshold value of
  100.000 microseconds. If a statement returns few rows and has a long execution time, the statement
  must be improved.
  An inefficient statement can have several consequences:
    The database is kept busy processing many data blocks
    CPU load is increased on the database server
    An SAP work process is blocked by the report and there are high wait times for other processes
    Several pages are displaced from the database buffer
    The cache hit rate for other SQL statements suffers
    Performance can be harmed system-wide by expensive statements




© SAP AG                                        ADM520                                                 3-29
  Poorly Qualified Statements (2)


                                RDBMS       Memory
                                            Area                      Data cache


  SELECT *
   FROM MARA
   WHERE MANDT = 001
    AND BISMT IN (...)
                                             SQL connections            Optimizer

                SAP Work
                 Process



       Explain:                 Database
    Execution path                            Tables          Indexes            Statistics
                                                             MARA_1
      Used index                               MARA

    Table definition                                         MARA_0
           ...

       SAP AG 2002




  The Database Optimizer is a cost-based optimizer that minimizes the number of pages to be read. It
  estimates the costs for each index used and compares these to the cost of a table scan for each table
  to be accessed.
  For this cost estimation, the Optimizer uses statistical information about the data distribution, which
  is stored for each index in a statistics page.
  There are some common reasons for long execution times:
     No index exists and therefore the whole table is read sequentially in a full table scan
     The index used is not very selective, so a large range has to be read in an index range scan
     The Optimizer re-uses the execution plan which keeps an unsuitable strategy
     The statement is badly formulated and selects much more data than is actually necessary




© SAP AG                                        ADM520                                                3-30
  Getting More Information




                                                                        Jump to ABAP source:
                                                                        • Show ABAP statement


   Information from SAP Data Dictionary:
   • Table fields                                         Explanation of Query:
   • Indexes and index fields                             • Which index is used
                                                          • Access method used
                                                          • JOIN sequence



       SAP AG 2002




  To determine why a statement runs slowly, you can request more details from the SQL Trace. Place
  the cursor on the desired line and choose:
    Button Display Details to show:
         - The complete statement
         - The name of the stored procedure used if a permanent stored procedure was created
         - If a database cursor was used for the execution
         - The parameter values and types used
    Button DDIC Information to show:
         - The table structure according to the SAP Data Dictionary (field definitions)
         - The indexes defined on the table(s) used, as written in the SAP Data Dictionary
    Button Explain to show:
         - The Optimizer decisions (for the time of the explanation). That is, the indexes used and the
         JOIN sequence
    Button Display Call Position in ABAP Program to show:
         - The related ABAP statement (if executed from ABAP)




© SAP AG                                       ADM520                                               3-31
  Understanding Explain SQL


       Filter conditions applied afterwards                    Table and index name




  Explain tree nodes        Strategy chosen by the Optimizer        Column values used for Seek


       SAP AG 2002




  The output of the optimizer explanation consists of the complete statement followed by the explain
  tree. In the explain tree, the root node shows the statement type (for example, SELECT). All other
  nodes are shown as PLAN_ROW. Except the root node, each node represents a working step
  necessary for the statement execution. The execution works from the bottom to the top, to produce
  the results for the statement. Everything belonging to the same node has the same indentation level.
  Every PLAN_ROW node shows the corresponding step below the estimated execution costs for
  processing. It also shows the estimated number of rows returned and the estimated cost for I/O and
  CPU resources (these numbers have a virtual unit and are only used for comparing different
  execution plans).
  Above the PLAN_ROW identifier, you can see the strategy used for this step (for example, Filter
  and Clustered Index Seek) followed by the parameters for the strategy (for example, filter values,
  index names, index fields, operators, and values).
  In this example, two steps are required to process the SELECT statement (2 * PLAN_ROW):
     First, a Clustered Index Seek on index MARA~0 of table MARA is used at the leaf level to search
     the index field MANDT for the value ”000”. The output is sorted by the clustered index key. The
     estimated number of rows returned from this step is 211.
     The results of this step are then fed into the next step, which filters the rows with the condition
     BISMT = ”2” OR BISMT = ”1”. The estimated number of rows returned by this step is 1. This is
     also the result of the whole statement (parent node is the SELECT).




© SAP AG                                       ADM520                                                3-32
  Getting Table and Index Information




                                                  1
                                                  2
                                                  1
                                                  2

                                         6                           5        1 1
                                                                              4 3




                      7




       SAP AG 2002




  To display detailed information on a table and its indexes, use Transaction DB02 and choose Detail
  analysis, then enter the table name.
  Total rows (1) displays the current number of rows in the table.
  Row modification counter (2) shows the number of changed rows since the last update statistics. An
  update statistics operation resets this counter to 0.
  If Auto stats (3) is set and the database option Auto update statistics is also set, SQL Server
  automatically performs an update statistics when a certain percentage of the table rows have been
  changed (according to the row modification counter).
  Index depth (4) shows the longest path from the root page to a leaf page of the index. A clustered
  index always has one level less than the same index created as a nonclustered index because the data
  pages are stored as the lowest level of the index. The index height gives the number of pages to be
  read for an index seek operation in the index. If a clustered index exists, the accesses through the
  nonclustered indexes are followed by a clustered index access, because the cluster key is stored in
  the nonclustered indexes instead of the row ID.
  Last update statistics (5) displays the timestamp of the most recent update statistics operation.
  Index columns (6) displays the list of the index fields.
  If the database option Auto create statistics is set, column statistics (7) are created automatically by
  SQL Server. These automatically created histograms have a name starting with _WA_Sys_.




© SAP AG                                        ADM520                                                3-33
  Index Statistics




                                                           Total number of rows in table



                                                                     Date of last update for
                                                                     these statistics

                                                                     Density of given
                                                                     columns combination




                                Histogram of the first index field
       SAP AG 2002




  To display the statistical information stored in the SAP System for each index, call Transaction
  DB02 and choose Button Detailed analysis, then enter the table name. In the screen displayed, select
  the requested index and choose button Show statistics.
  Only a contiguous list of columns of each index is usable for a selection where the first column is
  included. The index shown here can only be used if JOBNAME or JOBNAME and JOBCOUNT are
  in the WHERE clause. For JOBCOUNT alone it is not useful.
  The All density value is calculated by dividing 1 by the number of different values or value
  combinations for the field or fields. The index is best if the combined useable columns have a very
  low density and only a few duplicates exist for a given combination.
  The section below shows a list of samples taken for the values of the first index column (histogram).
  The total number of samples taken is displayed in column Steps in the top line. The histogram
  provides statistical information about the frequency of any single value in the table. Statistics are
  comprised in the following information:
    RANGE_HI_KEY: Upper bound value of a histogram range (step).
    RANGE_ROWS: Number of rows from the sample that fall within the range, excluding the upper
    bound.
    EQ_ROWS: Number of rows from the sample that are equal in value to the upper bound of the
    histogram step.
    DISTINCT_RANGE_ROWS: Number of distinct values within a range, excluding the upper
    bound.
    AVG_RANGE_ROWS: Average number of duplicate values within a histogram step, excluding
    the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for
    DISTINCT_RANGE_ROWS > 0).
    The value RANGE_ROWS is equal to AVG_RANGE_ROWS multiplied by
    DISTINCT_RANGE_ROWS.

© SAP AG                                       ADM520                                              3-34
  Finding Expensive Statements




     SAP Instance                                                      Database system
                       Stored procedure
                       name cache
                      Y2R10000000BB1H1911DD03L
                      Y2A000001D9C7VK3725RSCROSS1
                      Y2E000011F2CBC50301REPOLOAD                      SQL connections
                      Y2R65000168C73K0943ALPERFDB
                      Y2C00000453CBC21832DBSYNSEQ
                      Y2A0000032FCAUK4623SAPLSEUw
    SAP Work          Y2R10000000C5GG0516TBTCP
     Process          Y2K00002B2DCBC21130DOKCLUi5                       exec proc

                                             Stats info

                                                               Read
                               Switch
                                                               stats
                               On / Off



                                                             tats
                                                      Sp s

       SAP AG 2002




  The stored procedure name cache of each SAP instance provides some additional space for storing
  statistical information about the execution of stored procedures, such as the:
    Duration of the slowest and average execution (in ms)
    Number of executions (since the statistics were turned on)
    Number of rows returned during the fastest, slowest, and average running execution
    Parameter values for the execution that returned the maximum number of rows
    Name of the stored procedure
  To view the stored procedure statistics, call Transaction ST04 and choose Detail analysis menu
  →SAP stats on SPs. By default, the stored procedure statistics is switched on (SAP instance profile
  parameter dbs/mss/stats_on = 1). The statistics can be displayed for a single application server or for
  all the servers. You can check the contents of a specific stored procedure or display a list of the
  selected stored procedures.




© SAP AG                                                  ADM520                                      3-35
  Stored Procedure Name Cache Statistics




       SAP AG 2002




  Two times are measured for every stored procedure execution; the duration without fetches and the
  duration with fetches, which may be significantly higher if a larger number of rows are returned.
  The number of pages read or written is not displayed as this information is not known at the SAP
  System level.
  To check which procedures are responsible for the most database load, sort by column ∑ ms(+fetch).
  The top procedures are either slow or executed very often.
  To find the slow statements that are targets for optimization, sort by column ∑ Max ms(+fetch).
  Statements used for changes, such as UPDATE, DELETE, INSERT, and SELECT FOR UPDATE,
  may have a high maximum time due to lockwait situations. Also, the execution times may vary
  because of different parameter values. Therefore, you should sort by column Avg. ms (+fetch). The
  top statements are the statements that are slow on every execution.
  You can select a line and choose Button SQL statement to get the procedure text (the statements
  executed within the stored procedure) and the parameter list of the call that returned the largest
  number of rows (if available). You can then use the function Explain SQL to display a new explain
  plan with actual parameters. You can also check the Explain SP of the precompiled procedure in the
  SQL Server procedure cache to see if the stored execution plan is identical.




© SAP AG                                     ADM520                                             3-36
  How to Tune an Expensive SQL Statement


                     Poor
                  statement
                                                 SQL                           Where
     DDIC                                       Explain                       used list
     info

                      Is there a      Yes                     Good
                                                                            Yes          Inefficient
                       suitable                             Optimizer
     No                 index?                              decision?                     coding?

Create or change                                            No
secondary index                               Statistics                                     Yes
  in DDIC + DB                                  page

                      Yes                                     Index         Yes           Re-code
     Update                        Autoupdate       No      statistics
    statistics                      stats on?                 up to
                                                              date?
                                         No                              Re-code by
                                                                        specifying an
                                Switch on
                                                                        optimizer hint
                              auto updstats
       SAP AG 2002




  In this example, we assume that no suitable index exists and that the fields in the WHERE clause are
  MANDT and BISMT:
  SELECT * FROM MARA WHERE MANDT = xxx AND BISMT = yyy
  If you were to tune this expensive statement, you might consider creating a secondary index.
  However, creating a secondary index does not solve all problems, in fact it may have its own
  consequences. For example, if you increase the number of indexes on a table, the duration of
  INSERTS, DELETES and UPDATES also increases.
  Not every field has to be contained in the index. If you were to use the Explain SQL function for this
  example, you could see that the clustered index on MANDT and MATNR was used, but it was not
  very efficient. This means that the MANDT field was not helpful and should not have been selected
  for the index. Since there was only one row returned, the BISMT field is probably the selective one.
  Now you must determine how to create a useful index.




© SAP AG                                           ADM520                                              3-37
  How to Create a Useful Index




           A good index
                Is used in many statements
                Is used often
                Contains only selective fields
                Is small and possibly covered




       SAP AG 2002




  A good index should be used in many statements.
  If an index is used often, the database request time and the database load are reduced.
  A selective field is a field that has:
     Many different values in the real data in the table
     A small number of data rows with identical values, compared to the total number of rows within
     the table
  If an index is small, many index rows fit on one index page. This is especially useful for index row
  scans that may be triggered, for example, by comparison operators.
  A covered index contains all selected fields plus all the fields in the WHERE clause. It prevents
  accesses to the data pages.




© SAP AG                                       ADM520                                               3-38
  Determine Selective Field Combinations




                                                    184239
                                                    184239
                                                    100%




                                                        100% selectivity is fine




       SAP AG 2002




  To obtain the current selectivity of each combination of columns for a table, call Transaction DB02
  and choose Detail analysis →Table name →Selectivity. You can select any columns that can be
  indexed, other than image or text fields.
  From the screen displayed you can check the:
     Number of distinct entries for your selection
     Total number of rows in the table
     Selectivity (in percent)
  If you enter a combination of fields, you cannot tell if only one of the given fields is selective or if
  the combination gives a high selectivity. Therefore, you should always check the sub selections.
  A more accurate (but more costly) way to get the selectivity of field combinations is to use the
  density function. The result is weighted according to the number of occurrences for each distinct
  value.




© SAP AG                                         ADM520                                                 3-39
  How to Tune an Expensive SQL Statement (2)


                     Poor
                  statement
                                                 SQL                            Where
     DDIC                                       Explain                        used list
     info
                      Is there a       Yes                   Good           Yes           Inefficient
                       suitable                            Optimizer
     No                                                                                    coding?
                        index?                             decision?

 Create or change                                           No
 secondary index                              Statistics                                     Yes
   in DDIC + DB                                 page

                                                              Index                        Re-code
                       Yes                          No                       Yes
      Update                       Autoupdate               statistics
     statistics                     stats on?                 up to
                                                              date?
                                         No                               Re-code by
                                                                         specifying an
                                 Switch on
                                                                         optimizer hint
                               auto updstats
       SAP AG 2002




  In this example, we assume that a suitable index exists and that the optimizer has chosen this index,
  as shown by SQL Explain.
  This means you must check the coding to find the poor statement.
  It is possible to jump to the ABAP coding directly from the Stored Procedure Name Cache.




© SAP AG                                          ADM520                                                3-40
  Example of an Expensive SELECT (in ABAP)




   Poor
               SELECT * FROM zlips WHERE werks = int_lips-werks
                          AND lgort = int_lips-lgort.
                CHECK zlips-matnr IN sl_matnr.
                ...
               ENDSELECT.


   Good
               SELECT * FROM zlips WHERE werks = int_lips-werks
                                     AND lgort = int_lips-lgort
                                     AND matnr IN sl_matnr.
                ...
               ENDSELECT.




       SAP AG 2002




  In the first example, many data rows will be read unnecessarily from the database and filtered
  subsequently on the SAP System level.
  The second example already gives all filter conditions to the database and therefore returns only the
  rows really needed to the SAP System. Finding the data on the database can also be faster because an
  additional field can be used in an index seek.




© SAP AG                                       ADM520                                              3-41
  How to tune an expensive SQL statement (3)


                    Poor
                  Statement
                                                 SQL                          Where
     DDIC                                       Explain                      used list
     Info
                      Is there a       Yes                    Good          Yes           Inefficient
                       suitable                             Optimizer
     No                                                                                    coding?
                        index?                              decision?

Create or change                                            No
secondary index                               Statistics                                     Yes
  in DDIC + DB                                  page

                                                              Index                       Re-code
     Update            Yes         Autoupdate       No      statistics       Yes
    statistics                      stats on?                 up to
                                                              date?
                                         No
                                                                          Re-code by
                                 Switch on
                                                                         specifying an
                               auto updstats
       SAP AG 2002
                                                                         optimizer hint




  In this example, the optimizer chooses an index which was appropriate when the statement was
  executed with different parameters. In this case the optimizer has to be notified to recompile the
  statement or to choose a specific index. This can be done by giving optimizer hints.




© SAP AG                                           ADM520                                               3-42
  Recompilation and Optimizer Hints

  SELECT *
   FROM ZMARA                  RDBMS       Memory
                                           area
   WHERE MANDT = 001
                                             SQL connections
                                                                    Optimizer
                SAP Work
                 Process


   SELECT *
    FROM ZMARA
    WHERE MANDT = 002                                               Procedure cache


                                                                       execution plan:
                 SAP Work
                                                                    clustered index seek
                  Process




                                                Execution plan is reused.
                                                High execution time because
                                                of inappropriate strategy

       SAP AG 2002




  The SQL Server determines the execution plan with the first calling of a stored procedure. All further
  calls use the same execution plan until the stored procedure is compiled again (for example by the
  automatic update statistics). When first executing a stored procedure, the query optimizer decides to
  read the data following a certain strategy. When executing the stored procedure a second time, the
  strategy might not be appropriate anymore because the stored procedure uses different values now. A
  recompilation of the stored procedure forces the optimizer to create a new execution plan using a
  different strategy.
  Since R/3 4.6A, optimizer hints in the ABAP for SQL Server (see SAP Notes 129385 and 133381)
  have been introduced. Appending the following hint to a SELECT in the ABAP has the effect that
  the SQL Server creates a new execution plan every time the SELECT is executed:
    %_hints mssqlnt '&REPARSE&'
  This makes sense when the selectivity of an individual or a few SELECTs is extremely different.




© SAP AG                                       ADM520                                               3-43
  Expensive SQL Statements: Summary


           An expensive SQL statement needs many reads and can
           cause system-wide performance problems
           Analyzing an expensive SQL statement
                Statistical records → high database request time
                SQL Trace → statements with long response times
                Stored procedure name cache statistics → high execution times
                Process overview → report and table name
                Where used list → table
           Tuning an expensive SQL statement
                Create/change an index
                Check if Auto update statistics is on
                Understand the DB Optimizer
                Rewrite poor coding
                Use optimizer hints


       SAP AG 2002




  An expensive SQL statement needs many reads and can cause system-wide performance problems.
  When you analyze an expensive SQL statement, you must check the:
   Statistical records for the high database request time
   SQL Trace for the statements with long response times
   Stored procedure name cache statistics for high execution times
   Process overview for the report and table name
   Where used list for the table
  To tune an expensive SQL statement, SAP recommends that you:
   Create or change an index
   Use Automatic update statistics (DB option + index option) and Auto create statistics (DB option)
   Understand the DB Optimizer
   Rewrite poor coding
   Use optimizer hints




© SAP AG                                        ADM520                                           3-44
  Application Problems: Summary


                         Performance problems
                                due to:
          Poor configuration




   Poor database
   configuration
                      Poor hardware
                      configuration
                                        Inefficient applications




                                          Too many                    Poorly
                   Lockwaits            unnecessary                  qualified
                                         statements                 statements



                   Exclusive          Procedure stats,          Procedure stats,
                   lockwaits             SQL Trace                 SQL Trace


         SAP AG 2002




  Application problems typically involve either lock contention or inefficient SQL coding.
  SQL problems are normally caused by statements that are being executed more frequently than
  necessary or individual statements that are very expensive.
  A statement can be very expensive because of poor WHERE clauses, missing indexes, or poor
  optimizer decisions due to outdated statistics.
  If you determine that both the database configuration and the application have been adequately tuned
  and you still have performance problems, SAP recommends that you consult your hardware partner
  about additional hardware. This course does not cover a hardware analysis.




© SAP AG                                      ADM520                                              3-45
  Summary of this Unit



               Now you are able to:

                     Analyze the performance of your SQL Server database
                     system running with the SAP System
                     Identify the source of database performance problems
                     Improve database performance




      SAP AG 2002




© SAP AG                                ADM520                              3-46
  Further Documentation




                          Books Online
                          SAP Installation Guide
                          SAP Database Administration with
                          Microsoft SQL Server 2000
                          SAP Press, ISBN: 1-59229-005-1
                          SAP Notes
                          Windows Online Help




      SAP AG 2002




© SAP AG                  ADM520                             3-47
  Unit Actions




                     ?    Exercises




                          Solutions




      SAP AG 2002




© SAP AG                 ADM520       3-48
           Performance Monitoring and Tuning Exercises

                   Unit: Performance Monitoring and Tuning
                   Topic: Application Problems

                   At the conclusion of this exercise, you will be able to:
                   • Work with the SQL trace tool (transaction ST05)
                   •         Detect an expensive SQL statement, analyze its cause and find
                       an appropriate solution
                   The customer detects a slow running program and decides to trace the
                   program using the SQL trace tool (transaction ST05). He further detects a
                   slow running statement, which he analyzes and tunes.




   1-1     SQL Trace
           1-1-1 Report ZADM520MARA1 selects a record from table ZADM520MARA,
                 which is a copy of master material table MARA. Call ST05 to turn on the
                 trace, execute report ZADM520MARA1 (in transaction SE38). In ST05,
                 turn off the trace, and display the trace results.
                 Look at the access times to table ZADM520MARA. Check for the slow
                 statement for the OPEN operation. Which index was used for the slow
                 statement?
           1-1-2 Apply the appropriate change to speed up the execution. Repeat the steps
                 above and check the results.




© SAP AG                                   ADM520                                           3-49
           Performance Monitoring and Tuning Solutions

                   Unit: Performance Monitoring and Tuning
                   Topic: Application Problems


   1-1     SQL Trace
           1-1-1 Display the trace by choosing button Display Trace in transaction ST05.
                 Position your cursor on the slow statement, and choose Explain.
                 The clustered index ZADM520MARA~0 is used. SEEK was only
                 performed for field MANDT. For BISMT, the program read the entire table.
                 Performance could be improved by creating a secondary index on column
                 BISMT.
                 Use transaction SE11. Display table ZADM520MARA. Choose Button
                 Indexes .., and enter an ID for the new index.
                 Trace the program again and check if the new index is used.




© SAP AG                                 ADM520                                       3-50
  Database Backup




           1   Introduction

           2   SQL Server Architecture

           3   How the SAP System uses SQL Server

        4      Performance Monitoring and Tuning


       5 Database Backup
        6      Database Restore

        7      Regular Maintenance and Error Handling




      SAP AG 2002




© SAP AG                                  ADM520        4-1
  Database Backup



                     Contents
                        Backup Types
                        Performing Backups
                        Verifying Backups
                        Backup Strategies




                     Objectives
                     At the end of this unit, you will be able to:
                        Describe and perform the different backup types which are
                        supported by SQL Server using the SAP Planning Calendar and
                        SQL Server tools
                        Explain the technical implementations of the described backup
                        types and name the involved system tables
                        Verify the backups
                        Define a backup strategy that is suitable for your company
      SAP AG 2002




© SAP AG                                        ADM520                                  4-2
  Importance of Database Backups


                                     User errors                       Logical errors
                                 (Such as a deleted table)            (Such as a corruption)




        External factors                                                                         Physical errors
        (Such as fire or water
                                                                                               (Such as a media failure)
              damage)

                                                             Data
                                                             loss




                   To prevent data loss, a valid backup is necessary
       SAP AG 2002




  External factors, physical errors, and logical errors can all lead to data loss and system downtime.
  An effective backup strategy and recovery plan is essential in minimizing system downtime and data
  loss.
  To ensure the availability of your SAP System, the backup strategy developed by your database
  administrator must be carefully tested.




© SAP AG                                                     ADM520                                                        4-3
  Backup Types




      Database Backup                                    <SID>   master    msdb




      Transaction Log Backup                             <SID>




                                                         <SID>
      Differential Backup



      Complete System Backup




       SAP AG 2002




  Before a database backup and restore strategy can be defined, the different backup types of SQL
  Server are explained and described in detail.
  SQL Server provides different backup types, which are supported by SAP:
    Database backup
    Transaction log backup
    Differential backup
  In addition to the above backup types SQL Server provides filegroup and file backups, which are not
  used in the SAP environment.
  In addition to backups performed by SQL Server, the Windows operating system provides a backup
  tool to save files in the file system on a backup medium.




© SAP AG                                      ADM520                                              4-4
  Database Backup



                                       Data
                                         Andre      Vasser Hil       Frenzen
   X0        X1        X2       X3       X4         X5       X6      X7


   Kojak     MagnumDerrick Marple
   X8        X9        X10      X11      X12        X13      X14     X15


   Landis
   X16
             Wolf
             X17
                       Wang
                       X18
                                Kerber Kania
                                X19      X20
                                                    Thomas
                                                    X21      X22
                                                                     Merdes
                                                                     X23
                                                                                               1   Copy all used data pages to the backup media
   Mozart
   X24       X25
                       Bach
                       X26      X27
                                      Transaction Log
                                Strauss Wagner Beeth.
                                         X28        X29      X30     X31


                   begin     update begin      insert     commit   chkp    insert   rollback
                   LSN0      LSN1     LSN2     LSN3       LSN4     LSN5    LSN6     LSN7


                   delete
                   LSN8      LSN9     LSN10    LSN11 LSN12         LSN13 LSN14 LSN15

                                                                                                    2   Copy all used log pages to the backup media
                   LSN16 LSN17 LSN18           LSN19 LSN20         LSN21 LSN22 LSN23



                   LSN24 LSN25 LSN26           LSN27 LSN28         LSN29 LSN30 LSN31


                                                                                                        Set the timestamp of the backup to the time
                                                                                                    3
                                                                                                        when the backup has finished




             SAP AG 2002




  In a database backup, all user-defined objects, system tables and data are copied to a backup
  medium. The database backup is done while the database is online and thus has a slight performance
  impact.
  Therefore the database should be backed up at times when the workload is minimal.
  In addition to backing up the data pages, the transaction log is stored. Restoring the database from
  this backup means that the status of the time when the backup of the database ended is reached. This
  backup includes transactions that were performed during the backup. However, some operations are
  not allowed during a database backup:
     Creating or deleting database files
     Shrinking either the database (automatically or manually) or the database files
  The transaction log is not truncated when you perform a full database backup.




© SAP AG                                                                                       ADM520                                                 4-5
  Transaction Log Backup


                    Transaction Log
    begin1 update begin2    insert   commit2 chkp      insert   begin3
    LSN0     LSN1   LSN2    LSN3     LSN4     LSN5     LSN6     LSN7


    delete   dump   commit1 chkp     insert   insert   delete   delete
    LSN8     LSN9   LSN10   LSN11 LSN12       LSN13 LSN14 LSN15


    insert                                                               1   Copy all used log pages to the backup media
    LSN16 LSN17 LSN18       LSN19 LSN20       LSN21 LSN22 LSN23



    LSN24 LSN25 LSN26       LSN27 LSN28       LSN29 LSN30 LSN31




                    Transaction Log
    begin1 update begin2    insert   commit2 chkp      insert   begin3
    LSN0     LSN1   LSN2    LSN3     LSN4     LSN5     LSN6     LSN7


    delete   dump   commit1 chkp     insert   insert   delete   delete
    LSN8     LSN9   LSN10   LSN11 LSN12       LSN13 LSN14 LSN15


    insert                                                               2   Truncate the inactive portion of the transaction log
    LSN16 LSN17 LSN18       LSN19 LSN20       LSN21 LSN22 LSN23



    LSN24 LSN25 LSN26       LSN27 LSN28       LSN29 LSN30 LSN31




         SAP AG 2002




  When the transaction log is backed up, all used pages from the transaction log are backed up (1).
  Next the transaction log is truncated, that is, the inactive part is deleted (2).
  By creating transaction log backups, a database can be restored to any point in time contained within
  the sequence of transaction logs, right up to the point of failure. When creating a transaction log
  backup, the starting point of the backup is where the previous transaction log backup ended (if a
  transaction log backup has been created). In this example with the log sequence number 0, because
  no transaction log backup has been created since the full database backup. A subsequent transaction
  log backup would start with log sequence number 17.
  Although part of the transaction log is backed up with a database backup this is not taken into
  account for the transaction log backup.
  The transaction log cannot be backed up:
     If the recovery model is set to simple. Read more about the recovery models in the following
     slides.
  Remember that a transaction log backup can only be created after a preceding full database or a
  differential backup.




© SAP AG                                                                 ADM520                                                 4-6
  Truncating the Transaction Log



        Transaction Log                                                                     1
                                                                                            Determine the
    Virtual Log 1      Virtual Log 2       Virtual Log 3   Virtual Log 4    Virtual Log 5
                                                                                            MinLSN before the
                                                                                            Transaction Log is
                                                                                            truncated
                                                                               unused




   Start of logical log     MinLSN                             End of logical log



        Transaction Log                                                                     2
                                                                                            Truncate the log
    Virtual Log 1      Virtual Log 2       Virtual Log 3   Virtual Log 4   Virtual Log 5
                                                                                            before the start of
                                                                                            the Virtual Log
                                                                                            containing the
                                                                              unused
                                                                                            MinLSN


                          Start of logical log                 End of logical log



        SAP AG 2002




  If log records were never deleted from the transaction log, the log would keep growing until it filled
  all the available space on the disks holding the log files. Therefore old log records that are no longer
  needed for recovering or restoring a database must be deleted to make space for new log records. The
  process of deleting these log records is called truncating the log.
  The active part of the transaction log can never be truncated. The active part of the log is needed to
  recover the database at any time. It must always be present in the database. In case the server fails
  the database must be recovered when the server is restarted. The record at the start of the active
  portion of the log is identified by the minimum recovery log sequence number (MinLSN). See also
  Unit 1: SQL Server Architecture.
  Transaction logs are divided internally into sections called virtual log files. Virtual log files are the
  unit of truncation. When a transaction log is truncated, all log records before the start of the virtual
  log file containing the MinLSN are deleted.
  Truncating does not mean physically deleting. It simply marks the virtual log file as inactive.
  When the unused space at the end of the last virtual log is used up, the logging of the transactions
  starts again at the beginning of the first virtual log.




© SAP AG                                                   ADM520                                                 4-7
  Recovery Model

           Full                                                                            Transaction Log
             301       302          303          304         305            306      307        308

             Insert    Begin Tran1 Begin Tran2 Create index Update          Commit   Delete A001 Checkpoint
             MARD …                            Page x       A002 set ...             Where ...
                       Create index Insert
                       ZMARA_1 ... MARA ….
             Tran3                             Tran1        Tran2           Tran1    Tran2


   Bulked_logged                                                                           Transaction Log
                             Extent x
             301       302          303          304         305            306      307        308

             Insert    Begin Tran1 Begin Tran2               Update         Commit   Delete A001 Checkpoint
   BCM       MARD …                                          A002 set ...            Where ...
                       Create index Insert
                       ZMARA_1 ... MARA ….
             Tran3                                           Tran2          Tran1    Tran2

        Simple
                                                                                           Transaction Log
             301       302          303          304         305            306      307        308

             Insert    Begin Tran1 Begin Tran2               Update         Commit   Delete A001 Checkpoint
             MARD …                                          A002 set ...            Where ...
                       Create index Insert
                       ZMARA_1 ... MARA ….
             Tran3                                           Tran2          Tran1    Tran2

       SAP AG 2002




  In SQL Server you can use predefined recovery model to support planning your backup and recovery
  strategy. Recovery Models offer different levels of system performance and data security
  requirements.
  SQL Server provides the following three recovery models:
     Full: Full Recovery provides the ability to recover the database to the point of failure or to a
     specific point in time. All operations, including bulk operations such as CREATE INDEX, and
     bulk loading data, are fully logged in the transaction log. Every page of the newly created index
     tree gets written into the transaction log. When creating a clustered index the data pages will also
     be written into the log. The transaction log is truncated only by a transaction log backup.
     Bulk logged: The Bulk Logged Recovery model provides protection against media failure
     combined with the best performance and minimal log space usage. Extents changed by bulk load
     operations such as CREATE INDEX are marked in the Bulk Changed Map (BCM), but not
     logged. All other database operations are fully logged. When the transaction log is backed up, both
     the transaction log data and the marked extents are backed up. Log backups generated can only be
     completely restored again.
     Simple: Simple Recovery requires the least administration. In this model, data is recoverable only
     to the most recent full database or differential backup. Transaction log backups are not used, and
     minimal transaction log space is used. Bulk inserts and the creation of new indexes are not logged.
     A log truncation occurs every time a checkpoint is processed.
  You can set the recovery models for each database using the SQL Server Enterprise Manager or the
  ALTER DATABASE statement. See Books Online for more details.
  Check to see which model is active, using the DATABASEPROPERTYEX function, the SAP
  Database Allocation Monitor (transaction code DB02) or the SQL Server Enterprise Manager. In a
  productive SAP System, it is recommended that the Full Recovery Model is set for the SAP
  database. Only in exceptional cases, the recovery model full can be changed to bulk logged.

© SAP AG                                          ADM520                                                      4-8
  Differential Database Backup


               DCM
                DCM                                                                         1       Read the DCM pages to determine
                 DCM                                                                                which extents have been changed
                                                                                                    since the last full database backup


                                   Data
                                  Andre        Vasser         Schu
    X0        X1        X2        X3           X4             X5      X6


    Kojak     Rex       Derrick    Marple                                                           2     Copy all modified extents since the last
    X7        X8        X9        X10              X11        X12     X13
                                                                                                          full database backup to the backup media

    Dilg      Wolf       Wang      Kerber          Kania      Thom
    X14       X15        X16
                               Transaction Log
                                   X17             X18        X19     X20




                                Transaction Log
              begin    update begin       insert     commit    chkp   insert   rollback
              LSN0     LSN1     LSN2      LSN3       LSN4      LSN5   LSN6     LSN7


              delete
                                                                                                                Copy all used log pages
              LSN8     LSN9     LSN10     LSN11 LSN12          LSN13 LSN14 LSN15
                                                                                                           3    to the backup media
              LSN16 LSN17 LSN18           LSN19 LSN20          LSN21 LSN22 LSN23



              LSN24 LSN25 LSN26           LSN27 LSN28          LSN29 LSN30 LSN31

                                                                                                        Set the timestamp of the backup to the time
                                                                                                4       when the backup has finished
            SAP AG 2002




  A database backup can be supplemented by differential database backups that record only the
  changes made to the database after the last full database backup. Each subsequent differential
  database backup records all the changes made to a database after the last full database backup. If a
  database backup is created, followed by multiple differential database backups, only the database
  backup and the last differential database backup created must be restored.
  Differential backups are used primarily in heavily used systems where a failed database must be
  brought up as quickly as possible. Differential backups are smaller than full database backups, so a
  restore from a differential backup takes less time, because only the extents that contain modifications
  are restored. These extents are recorded in the Differential Changed Map (DCM), which has been
  introduced in Unit 1: SQL Server Architecture.
  During a differential backup, SQL Server backs up the transaction log to produce a consistent
  database when the database is restored.




© SAP AG                                                                                  ADM520                                                      4-9
  File System Backup

     Directory                 Files
            X:\<SID>DATA1      Primary data file
            X:\<SID>DATA2      Secondary data file
            X:\<SID>DATA3      Secondary data file

            Y:\<SID>LOG1       Transaction log file

            Z:\Tempdb          data and log files of the tempdb

            C:\Program Files\Microsoft SQL Server\8.0\
                     COM       application programming interfaces
                                                                            Copy all SAP and SQL Server
                     Tools     tools such as Books Online               1   files to the backup media
            C:\Program Files\Microsoft SQL Server\MSSQL\
                     Backup Default Backup directory
                     Binn      MS SQL Server executables
                     Data      System and sample database files             Create a document containing
                     Install   Installation scripts and logs
                                                                        2   the file structure
                     Jobs      Temporary job output files
                     Log       Errorlogs and Joblogs                              File System
                     Repldata Working directory for replication tasks             Structure
                     Trace     Trace files created by the SQLProfiler
                     Upgrade Files used for upgrade

            D:\usr\sap\<SID>   SAP executables
                      trans    Transport directory
            D:\WINNT           Windows System directory


       SAP AG 2002




  Perform a complete system backup of the SAP file tree, the operating system files, as well as the
  database data files, the transaction log files, the SQL Server executables and system database files
  onto the backup media.
  To ensure that the correct actions are performed in case of a system crash, you should create a
  document containing the file structure of the whole system. This document must be available and
  understood by the person who performs the database restore.




© SAP AG                                              ADM520                                         4-10
  How to Perform a Backup


                  unplanned                                          regular



                                                              Tue
                                                        Mon   Tue Wed Thu Fri
                                                              Tue               Sat   Sun

                                                        Mon Tue Wed Thu Fri     Sat   Sun

                                                        Mon Tue Wed Thu Fri     Sat   Sun
                        Query Analyzer
                                                        Mon Tue Wed Thu Fri     Sat   Sun
      Enterprise Manager
                                                              SAP Planning Calendar
                                                                 DB13 or DB13C

                                       Monthly
                                       Backup
                                        Cycle




       SAP AG 2002




  There are three ways to perform database backups :
    Using the SAP Planning Calendar (transaction code DB13) or the central SAP Planning Calendar
    (transaction code DB13C)
    On the database level, using the Enterprise Manager tool, select: Tools → Backup Database.
    On the database level, using the transact-SQL statement BACKUP.
  Other backup tools can also be used, but are not covered in this course.
  Course ADM100 provides a description of how to use the SAP Planning Calendar.
  Do NOT use Windows Backup to perform backups of the SAP database. Windows Backup should
  be used to perform a complete offline system backup.




© SAP AG                                    ADM520                                           4-11
  Backup Devices




                                                           TAPE
                              Single backup on tape
                                                                                       R3DUMP0


                              Parallel backup on                                       R3DUMP0
                                                           TAPE
                              more than one tape
                                                                                       R3DUMP1
                                                                                       R3DUMP2

      Database X              Backup on local
                                                            DISK
                              or network disk device
                                                                            Windows
                                                                            Backup
                              Backup with third party
                                                            PIPE
                              software using named pipes
                                                                                       Third party
                                                                                       software




       SAP AG 2002




  You can use several different backup devices for a SQL Server backup:
     One or more tape backup devices (for example, devices R3DUMP0, R3DUMP1, …; created
     during the installation of the SAP System)
     Disk device (local or network)
     Named pipe device, used by third party software
  For small SAP databases (up to around 60 GB), backups should be performed on a single tape
  device, for example a DLT tape.
  Larger SAP databases can be backed up using one device and several tapes, written sequentially. If
  more than one tape device is available (directly connected to the computer), tapes can be written in
  parallel onto these devices. A parallel backup is recommended if the time frame is small.
  Backup to tape, whether to a single tape, or to multiple tapes using the sequential or parallel backup
  procedure, is supported by the SAP Planning Calendar (Transaction DB13).
  To manage the backup of the SAP database into a disk backup device, use SQL Server Backup.
  Afterwards, use Windows Backup to back up the disk dump files onto a backup media. For this type
  of database backup, use Windows Backup to restore the database backup file on disk, then use the
  database restore facility with the backup file. The advantage of this method is that the backup to disk
  is quite fast and another computer may then be used to perform the Windows Backup.
  Third party software can also be used to perform backups. Data is transferred using named pipes.
  Third party software can also be used to backup to tape devices located on a different computer than
  the one where SQL Server is installed.




© SAP AG                                        ADM520                                               4-12
  SAP Planning Calendar - Concept

                            Mon 12     Tue 13     Wed 14     Thu 15      Fri 16     Sat 17   Sun 18
                            success    success    success    success      0:00


                            Mon 19     Tue 20     Wed 21     Thu 22      Fri 23     Sat 24   Sun 25
                            success     success
                                       success    success    success    2:00
                                                                        error


                            Mon 26      Tue 27     Wed 28    Thu 29      Fri 30     Sat 31   Sun 01
                            0:00 DB    0:00 DB    0:00 DB    0:00 DB    0:00 DB
                            9:00 Log   9:00 Log   9:00 Log   9:00 Log   9:00 Log


                            Mon 02      Tue 03     Wed 04    Thu 05      Fri 06     Sat 07   Sun 08
                            0:00 DB    0:00 DB    0:00 DB    0:00 DB    0:00 DB
                            9:00 Log   9:00 Log   9:00 Log   9:00 Log   9:00 Log




                                                    SAP Layer
       DB Interface
                                                     Database
                                                      Layer

                      Job Scheduling Engine,                                          Logs (msdb
                         SQLServerAgent                                            Tables, SAP Tables)



       SAP AG 2002




  When creating a backup job using the SAP Planning Calendar (transaction code DB13 or DB13C)
  the database interface creates a SQL command. This command defines and schedules a job using the
  SQL Servers Job Scheduling Engine. These jobs call stored procedures sap_backup_databases,
  sap_backup_diff_databases and sap_backup_log to back up the databases and the transaction log.
  A job created by the SAP Planning Calendar can be defined to run immediately or on a recurring
  schedule, for example, once a week. Once you have created a job, you can view the job definition by
  double-clicking the job name or by choosing button Parameters. If the requirements of a task
  change, you can modify the task by double-clicking its name and choosing Edit. If the task is no
  longer needed, simply delete it by choosing button Delete.
  Each scheduled job to be executed by SQL Server Agent is stored in table sysjobs on database msdb.
  Their names start with SAP CCMS and can be viewed using the SQL Enterprise Manager.




© SAP AG                                                 ADM520                                          4-13
  Scheduling Backups



                                 CCMS Planning Calendar
                            Planning Goto Listing Help System



                                MON         TUE
                                                  -   New Plan for Tue 17.12.
                                                        WED         THU       FRI       SAT       SUN
      BACKUP                                          Start time   18:00:00   Period:   1
                                                      Action
                                                      Full database backup
                                MON         TUE         WED         THU       FRI       SAT       SUN
                                                      Differential database backup
                                                      Transaction log backup
       Data files                                     Check database consistency
                                MON         TUE         WED         THU       FRI       SAT       SUN

      BACKUP                                      Database(s) selection                       Backup device selection

                                MON         TUE         WED         THU       FRI       SAT       SUN
                                             Database to be dumped
                                                                                              Select Backup devices
     Transaction
         log                                          master
                                                      msdb                                        R3DUMP0          Tape
                                                      PRD                                         R3DUMP1          Tape
                                                                                                  R3DUMP2          Tape




       SAP AG 2002




  The SAP Planning Calendar supports the following backup types out of the SAP System:
       Full database backup
       Differential database backup
       Transaction log backup
  These actions can be scheduled to run on their own; once or repeatedly. Alternatively, they can be
  scheduled to run in combination with other tasks by selecting a predefined Planning pattern. A
  Planning pattern specifies a combination of tasks that are executed at predefined intervals and can
  be scheduled together. The Calendar offers the Planning pattern: 5 workdays, DB backup + hourly
  log backup (8am - 6pm) + Monthly Check DB
  To schedule the pattern, you simply need to choose it and specify a time. The SAP System
  automatically assigns values to the parameters required for the execution.
  To schedule a single task double-click the day on which you want a backup scheduled. In the
  selection screens, mark:
       Backup type required
       Database(s) you want to back up
       One (or more) backup device
   For detailed information on how to use the SAP Planning Calendar, see the SAP Online Help.




© SAP AG                                                  ADM520                                                          4-14
  Media Names



                                             Device names
                                             Device names




   Database
   Database                Type
                           Type                       Number
                                                      Number                 Tapecount
                                                                             Tapecount

        R = SAP DB
        R = SAP DB                D = database
                                  D = database              01 -- 31 =
                                                             01 31 =               S = sequential
                                                                                   S = sequential
                                                            day of
                                                             day of
                                                            the
                                                             the
        M = msdb
        M = msdb                  + = differential
                                  + = differential                                 P = parallel
                                                                                   P = parallel
                                                            month
                                                             month

        S = master
        S = master                L = trans. log
                                  L = trans. log

        II = combination SAP DB included
           = combination SAP DB included

        C = combination SAP DB not included
        C = combination SAP DB not included




       SAP AG 2002




  The media on which the backup is performed is identified by a label. This label identifies the
  contents by indicating the type and day of the backup and is given automatically when scheduling
  backups using the SAP Planning Calendar. The media name is put together as follows:
  The first character shows the type of data on the media:
       R SAP database
       S master Database
       M msdb database
       I More than one database is backed up on the tape and the SAP database is included
       C More than one database is backed up on the tape and the SAP database is not included
  The second character shows the type of backup:
       D Full database backup,
       L Transaction log backup
       + Differential database backup
  The next two characters (numbers) show the day of the month.
  The last character on the label indicates whether it is a parallel or a sequential backup




© SAP AG                                       ADM520                                             4-15
  Media Names (2)




                      Stick name label
                      on tape cartridge




           ID15S


           ID15S =    One tape of a sequential SAP, master, and msdb (I) database backup
                      (D) on day 15 of the month


           RD05P =    One tape of a parallel (P) SAP (R) database backup (D) on
                      day 5 of the month


       SAP AG 2002




  Name all tapes used for backups. Type in a tape name when creating an SQL Server backup task and
  stick a label with the same name on the tape medium.
  The first tape label in this example has the name ID15S. Following the naming convention outlined,
  previously, this label denotes an SAP database, master, and msdb full database backup, performed on
  the 15th day of the month.
  The second example is RD05P. This is an SAP database backup on the tape. The backup was
  performed on day 05 of the month. The character P indicates a parallel backup was performed.




© SAP AG                                      ADM520                                             4-16
  Database and Log Backup Options


                                                                           For the last Backup only
                                               For the first Backup only

   Backup                                             Backup
   Database                                           Transaction
                                                      Log
    <SID>
                                                         Log
     master

        msdb




       SAP AG 2002




  For a full database backup, use the following options:
       Unload tape           Rewinds the tape after the backup, set this option for a database backup
       Initialize tape       Existing backups are overwritten, if the expiration period is over, and the
      tape name corresponds to the day the backup is performed.
       Verify backup         Checks backup is to see if all data on tape is readable
       Expiration period Set to 27 days
  For transaction log backups, set the following options as indicated:
       Unload tape           Set for the last transaction-log backup of the day stored on this tape
       Initialize tape       Set for the first transaction-log backup on the tape when the tape is reused
       Verify backup         Set for all backups
       Expiration period Set to 27 days
  When using the option Format tape, the media name and all data on the tape are overwritten, no
  matter what the media retention or expiration period. Use Format tape only when new tapes are
  used.
  The option Initialize tape allows you to overwrite backups on a tape if the expiration period is past.
  The media name is not overwritten and can be used for identifying the tape.
  The option Verify executes transact-SQL command RESTORE VERIFYONLY on the chosen
  backup medium after having performed the backup. We recommend that you set this option for each
  backup performed. It checks that the backup set is complete and that all volumes are readable.
  However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained
  in the backup volumes. If the backup is valid, SQL Server returns the message: ”The backup set is
  valid.” This message will be displayed in the pop-up window after double-clicking on the task. If it is
  not valid, you should repeat the backup on a new medium.




© SAP AG                                       ADM520                                                 4-17
  Database Backup with Enterprise Manager

   SQL Server Backup - PRD                                                                   x

    General    Options                                            SQL Server Backup - PRD                                                          x

               Database:      PRD                                     General   Options
               Name:          PRD full database backup
                                                                       Backup
               Description:   PRD Backup Database
                                                                                Verifying the backup will read the entire backup and check for
      Backup
                                                                                media integrity. Checking the identity and expiration of the
                    Database - Complete                                         media prevents accidental overwrites.
                    Database - Differential
                                                                                    Verify backup upon completion
                    Transaction log
                                                                                    Eject tape after backup
                    File and filegroup:
                                                                                    Remove inactive entries from transaction log
      Destination
                                                                                    Check media set name and backup set expiration
                                                                                    Check media set name and backup set expiration
               Backup To:       Tape      Disk
                                                   Add...                           Media set name:       RD25S
               \\ \Tape0
                                                 Remove...                          Backup set will expire:                   27
                                                                                                                     After:         days
                                                 Contents...                                                         On:      Fri 01/24/2003
      Overwrite                                                        Media Set Labels:
                                                                                Initializing tape or disk media set erases the previous contents
                    Append to media
                                                                                of the media and labels the media set with a name and
                    Overwrite existing media                                    description.
      Schedule                                                                      Initialize and label media
                    Schedule:                                                       Media set name:              RD25S

                                                                                    Media set description

                                       OK           Cancel     Help

                                                                                                           OK            Cancel          Help


        SAP AG 2002




  To back up the database using the Enterprise Manager, choose Tools → Backup Database. Select the
  database to back up, and select Database - Complete. Select the destination and specify the option to
  overwrite the existing media.
  In the Option folder, different options can be set. When the backup is completed, you should verify
  the media integrity of the backup. An expiration period must be set to prevent the media to be
  overwritten within 27 days. A media name must be specified. Use the SAP naming conventions
  explained earlier in this chapter.
  You can execute the backup immediately, or you can schedule it for execution by choosing Schedule
  and specifying the appropriate information in the Schedule Backup dialog box that is displayed.
  The database can be backed up using the Transact SQL command BACKUP DATABASE which is
  described in detail in the SQL Server Books Online.




© SAP AG                                                         ADM520                                                                                4-18
  Log Backup with Enterprise Manager

   SQL Server Backup - PRD                                                                    x

    General    Options                                             SQL Server Backup - PRD                                                          x

               Database:      PRD                                      General   Options
               Name:          PRD transaction log backup
                                                                        Backup
               Description:   PRD Transaction Log
                                                                                 Verifying the backup will read the entire backup and check for
      Backup
                                                                                 media integrity. Checking the identity and expiration of the
                     Database - Complete                                         media prevents accidental overwrites.
                     Database - Differential
                                                                                     Verify backup upon completion
                     Transaction log
                                                                                     Eject tape after backup
                     File and filegroup:
                                                                                     Remove inactive entries from transaction log
      Destination
                                                                                     Check media set name and backup set expiration
               Backup To:        Tape      Disk
                                                    Add...                           Media set name:       RL25S
               \\ \Tape0
                                                  Remove...                          Backup set will expire:                   27
                                                                                                                      After:         days
                                                  Contents...                                                         On:      Fri 01/24/2003
      Overwrite                                                         Media Set Labels:
                                                                                 Initializing tape or disk media set erases the previous contents
                     Append to media
                                                                                 of the media and labels the media set with a name and
                     Overwrite existing media                                    description.
      Schedule                                                                       Initialize and label media
                     Schedule:                                                       Media set name:              RL25S

                                                                                     Media set description

                                        OK           Cancel     Help

                                                                                                            OK            Cancel          Help


        SAP AG 2002




  To back up the transaction log using the Enterprise Manager, choose Tools → Backup Database.
  Select the database you want to back up, then select Transaction log. Select the destination. Specify
  whether you want to overwrite an existing medium or whether you want to append the backup to the
  backup sets already on the medium.
  In the Option folder different options can be set. When the backup is completed, you should verify
  the media integrity of the backup. An expiration period must be set to prevent the media from being
  overwritten within 27 days. A media name must be specified. The inactive portion of the transaction
  log should be removed. Use the SAP naming conventions explained earlier in this chapter.
  You can execute the backup immediately, or you can schedule it for execution by choosing Schedule
  and specifying the appropriate information in the Schedule Backup dialog box that is displayed.
  The database can be backed up using the Transact SQL command BACKUP LOG which is
  described in detail in the SQL Server Books Online.




© SAP AG                                                          ADM520                                                                                4-19
  Tape Options




       SAP AG 2002




  When the SQLServerAgent service is started, it queries the system tables in the msdb database to
  determine what jobs to start. If a backup job is scheduled, SQL Server Agent will pass the backup
  command to SQL Server. If the tape device is not ready at the specified time, SQL Server will
  normally cancel the job and write an error to the error log. The following timeout values are
  available to overcome such a problem:
       Wait indefinitely: Wait indefinitely for SQL Server to respond.
       Try once then quit: Try once and then time out when waiting for SQL Server to respond.
       Try for minute(s): Specify the time (in minutes) to try before timing out when waiting for SQL
       Server to respond.
  We recommend trying for 10 minutes and then send a timeout message.




© SAP AG                                      ADM520                                              4-20
  File System Backup using the Windows Backup Tool



                       Distributed Transaction ..   Coordinates Transac…   Manual      Local System
                       MSSQLSERVER                                         Automatic   PRDadm
                       MSSQLServerAdHelper                                 Manual      Local System
                       SQLSERVERAGENT                                      Automatic   PRDadm
                       SAPPRD_00                                           Automatic   SAPServicePRD
                       SAPOSCOL                                            Automatic   SAPServicePRD




       SAP AG 2002




  The entire system, including all Windows, SAP and SQL Server files must be backed up regularly
  using the Windows Backup utility. The backup of these files is necessary for a restore operation
  when the disk on which SQL Server and SAP executables are located crashes. In addition, it serves
  as an additional backup that may play a vital role in dealing with emergency situations where other
  routine backups have been damaged. Choose Start → Programs → Accessories → System Tools →
  Backup to open the Windows Backup tool. For detailed information see the Windows Online Help.
   Windows, SAP and SQL Server files need to be backed up:
       After installing a Windows or SQL Server Service pack
       Before special actions such as an SAP Upgrade
  Windows Backup Tool only backs up closed files. Therefore the services MSSQLServer,
  SQLSERVERAGENT, SAP<SID>_<InstanceNo> and the SAPOSCol must be stopped.
  To ensure that the whole system is backed up, you should install an additional Windows system
  (Help Windows), which is only used when backing up the system. For information on how to
  proceed, refer to the Windows documentation.You must ensure that you do not back up the local
  registry. If an additional Windows is not available, you must also back up the local registry.
  Never use Windows backup for regular database backups that are part of a predefined backup and
  restore strategy. When you restore such a Windows backup, the database and transaction log files are
  restored to the state they were at the time you ran the Windows Backup. No subsequent differential
  or log backups can be applied. Therefore you can not do a point-in-time database recovery. This
  means that the changes you made to the database since the last Windows Backup are lost and cannot
  be redone. Instead, always use SQL Server-based backups for regular database and log backups.




© SAP AG                                               ADM520                                          4-21
  Backup Requirements and Costs


       Backup time                                           Long
                                                                             ?
                                                                                      Short


       Restore time                                          Long
                                                                             ?
                                                                                      Short


       High availability                                     No
                                                                             ?
                                                                                        Yes


       Training                                              Short
                                                                             ?
                                                                                       Long


       Acquisition costs                                     Low
                                                                             ?
                                                                                       High


       Administrative workload                               Low
                                                                             ?
                                                                                       High




       SAP AG 2002




  Requirements. Depending on your situation, you may need one, or a combination of several,
  advanced backup scenarios. When you define your backup scenario, you must consider:
       How long it takes to perform a backup
       The maximum time required for a complete database restore
       To what extent your backup strategy provides additional security in case of a hardware failure
  Costs. Your backup scenario will also depend on:
       How much training the administrator requires
       The amount of database administration required for implementation and production operation
       The acquisition costs
  Now that you are familiar with the different backup types supported by SAP and Microsoft tools, we
  can introduce various advanced backup scenarios. The following slides introduce various advanced
  backup scenarios supported by SAP tools, ranked according to the above criteria using a ”smiley
  matrix.” This ranking is intended as a rough guideline only.




© SAP AG                                      ADM520                                             4-22
  Single DB and Transaction Log Backups


       Database Backup                              Transaction Log Backup



                            msdb
                         master                          <SID>
                      <SID>                               <SID>
                                                            <SID>




     DB13:                                   DB13:                                 Backup
     Full daily Database Backup              Transaction Log Backup                time
     of master, msdb and <SID>               of <SID> to one tape                  Restore
     to one tape                             each hour                             time
                                                                                   High
                                                                                   availability

                                                                                   Training
                                                                                   Acquisition
               ID01S                                  RL01S                        costs
                                                                                   Administrative
                                                                                   workload
       SAP AG 2002




  This scenario is the reference point for ranking the scenarios.
  A full database backup of the SAP database should be performed daily on a permanent storage
  media, for example, a data tape. This backup type records the complete state of the data in the
  database at the time the backup operation completes. The master database and the msdb database
  must also be backed up daily.
  The transaction log of the SAP database must also be backed up in order to restore the database to a
  specific point in time. The transaction log contains information on database changes, which is used
  for roll forward and roll back operations. SAP recommends scheduling log backups every 30-60
  minutes. The number of transaction log backups depends on the size of the transaction log (normally
  1.5 GB), and the transaction rate of your system.
  Transaction log backups are much smaller in size than database backups, as they only contain the
  changes since the last transaction log backup. Therefore, a transaction log backup has a low impact
  on system performance, if performed during normal operation.
  A transaction log must be backed up before the transaction log file is full. If the transaction log is
  full, no further database transactions can be performed. See Unit 6: Regular Maintenance and Error
  Handling on a solution on how to handle a full transaction log. After the transaction log backup, all
  log information which is not active is truncated out of the transaction log file.
  The databases and transaction log backups must not be written to the same backup device.
  SAP recommends this strategy because it meets the needs of most customers.




© SAP AG                                       ADM520                                               4-23
  Parallel Tape Support


       Database Backup                                   Transaction Log Backup


                       msdb
                                                          <SID>
                  master                                   <SID>
               <SID>                                         <SID>




       DB13:                                      DB13:
       Full daily Database Backup                 Transaction Log Backup             Backup
       of master, msdb and <SID>                  of <SID> to more than              time
       to more than one tape                      one tape each hour                 Restore
                                                                                     time
                                                                                     High
                                                                                     availability

                                                                                     Training
                      ID01P
                                                      RL01S
                                                                                     Acquisition
                                                                                     costs
           ID01P                                                        RL01S
                                ID01P                                                Administrative
                                                                                     workload
       SAP AG 2002




  To reduce the backup time, as well as the time required for restoring the database and the transaction
  log, transaction DB13 supports the parallel use of multiple devices. This strategy can also be used if
  all databases or the volume of all transactions logs to be backed up will not fit on one single tape and
  an automatic or manual device changing is not possible. A disadvantage may be that if one device
  cannot be read, the whole backup is not restorable. Therefore we recommend that you always verify
  the structure of the backup on the device as explained in previous slides.




© SAP AG                                        ADM520                                                4-24
  Two-Step Disk Backup




                         Fast 1st step DB13:
                         Full daily Database Backup
             msdb        of master, msdb and <SID>           DISK_DEV1
        master           to DISK_DEV1
     <SID>



                                   DISK_DEV2
                                                               “Slow” 2nd step
         <SID>                                                 Windows Backup       Backup
          <SID>                                                                     time
            <SID>
                                                                                    Restore
                                                                                    time
       Fast 1st step DB13:
                                                                                    High
       Transaction Log Backup of                                                    availability
                                          “Slow” 2nd step
       <SID> to DISK_DEV2 each
                                          Windows Backup
       hour                                                                         Training
                                                                                    Acquisition
                                                                                    costs
                                         RL01S                           ID01S      Administrative
                                                                                    workload
       SAP AG 2002




  A two-step disk backup is performed as follows: First, a backup is made to disk. Typically, this is
  faster than a backup to tape. Second, the files are backed up from disk to tape. The advantages of this
  method are that it:
       Reduces backup time
       Reduces restore time (if the required files are still available on disk)
       Backups can be saved to remote disks, through shared folders
  First, additional backup devices on disk must be created for the database backups and the transaction
  log backups. Enough disk space to hold these backups must be available. Note that the size of a
  database backup corresponds roughly to the used size of the database because only used pages are
  copied to the backup media.
  Second, the files are copied to tape using Windows Backup or another third-party tool.
  If the backups on disk are still available when a restore is necessary, the restore time is reduced. If
  the backups are no longer available, the restore will need to be performed from tape.
  The advantage of this strategy is that backups that are performed to a remote disk are physically
  separated from the server. In case of fire or flood, the backup on the remote disk may still be
  available.




© SAP AG                                         ADM520                                              4-25
  Supplementary Differential Backups

           Sun                           Mon        ...       Fri




                 msdb
                                       <SID>
          master                                             <SID>
                                                              <SID>
       <SID>                                                    <SID>




   DB13:                     DB13:                        DB13:
   Full weekly Database      Differential daily           Transaction Log Backup    Backup
   Backup of master, msdb    Database Backup of           of <SID> to one tape      time
   and <SID> to one tape     <SID> to one tape            each hour                 Restore
                                                                                    time
                                                                                    High
                                                                                    availability

                                                                                    Training
                                                                                    Acquisition
            ID01S                      R+02S                        RL02S           costs
                                                                                    Administrative
                                                                                    workload
       SAP AG 2002




  Using a full database, differential database, and transaction log backups together can reduce the time
  needed to restore a database back to any point in time after the database backup was created.
  Additionally, creating both differential database and transaction log backups can increase the
  robustness of backup procedures in the event that either a transaction log backup or differential
  database backup becomes unavailable, for example, due to media failure. Differential backups
  reduces the runtime of the backup itself because during the backup procedure the DCM page is read
  to determine which extents have changed.
  Typically combined database, differential database, and transaction log backups involve creating
  database backups at longer intervals, differential database backups at medium intervals, and
  transaction log backups at shorter intervals. For example, create database backups weekly,
  differential database backups daily, and transaction log backups hourly.




© SAP AG                                       ADM520                                                4-26
  Hot-Standby Server



             Production server                                           Standby server


                         Full Database Backup
                         of master, msdb and <SID>
                                                             DISK_DEV1
              msdb       to DISK_DEV1 once                                                msdb
         master          DB13:                                                     master
                         Full Database Backup of
      <SID>              <SID>, master and msdb
                                                                                <SID>
                         to tape

                         DB13:
                         Transaction Log Backup of           DISK_DEV2
                         <SID> to DISK_DEV2 each                                                         Backup
           <SID>         hour                                                         <SID>              time
            <SID>                                                                      <SID>             Restore
              <SID>                                                                      <SID>
                                                                                                         time
                                                     Daily Windows Backup of   Daily Windows Backup of   High
                                                     DISK_DEV1                 DISK_DEV2
                                                     to tape                   to tape
                                                                                                         availability

                                                                                                         Training
                                                                                                         Acquisition
                                                                                                         costs
                                                                ID02S                   RL02S            Admin.
                                                                                                         workload
       SAP AG 2002




  A standby server is a second server that can be brought online in the event that the production server
  fails. The standby server contains a copy of the databases on the production server, including the
  system databases. This copy is maintained by initially backing up the databases on the production
  server and restoring them once on the standby server. Periodically, transaction log backups from the
  databases on the production server are applied to the standby server to ensure that the standby server
  is synchronized with the production server. If the production server fails, the standby database can be
  opened, and can take on the role of the production database.
  The transaction log backups from the production server are applied on the standby database. This
  allows a delayed restore on the standby server in case of a possible user error. On the standby server,
  Windows backups to tape are performed.
  NOTE: Certain structural changes on the production database are not automatically replicated on the
  standby database. In this case, the recovery is stopped, and the database administrator must resolve
  the situation manually. The creation and deletion of datafiles on the primary server are not
  automatically replicated on the standby server.
  This scenario is highly dependent on the implementation, and you must therefore plan the
  environment in detail. See Books Online for a detailed description.




© SAP AG                                                ADM520                                                          4-27
  Monitoring Backups


                                                                 Tue
                                                           Mon   Tue Wed Thu Fri
                                                                 Tue               Sat   Sun

                                                           Mon Tue Wed Thu Fri     Sat   Sun
                              !       i
                                  ?                        Mon Tue Wed Thu Fri     Sat   Sun

                                                           Mon Tue Wed Thu Fri     Sat   Sun

                           Event Viewer                    SAP Planning Calendar
                                                           (DB13 or DB13C)
      Enterprise Manager


                                          Monthly                       Database backups
                                          Backup
                                           Cycle
                                                                       CCMS Database Backup
                                                                       History (DB12)


              Explorer




       SAP AG 2002




  You must check regularly whether the backup tasks were completed successfully. Always check
  that:
       The most recent backup has run successfully
       All the backups in the backup cycle are being executed according to the schedule. Gaps in a
       backup sequence can have serious consequences in a subsequent attempt to restore the database.
       The backup and restore process works successfully and enables you to restore your database to a
       correct and consistent state.
  There are different ways of monitoring single backups of databases and transaction logs:
       Use the SAP Planning Calendar (transaction DB13 or DB13C)
       If the SAP System is unavailable, use the Enterprise Manager and the Event Viewer to check
       backup task execution.
       To display the SQL Server error log, use the Explorer or the Enterprise Manager. Using the
       Explorer, select the drive and the MSSQL\LOG\ path. The current error log is in file
       ERRORLOG. All recent error logs can be found in files ERRORLOG.1 to ERRORLOG.6. By
       default SQL Server maintains up to seven error logs. The number of error logs can be configured
       in the Enterprise Manager.
       To display the SQL Server Agent error log, use the Explorer or the Enterprise Manager. Using
       the Explorer, select the drive and the MSSQL\LOG\ path. The extension of each archived error
       log indicates the age of the error log. For example, an extension of .1 indicates the newest
       archived error log.
       The backup cycle must be checked using the SAP Planning Calendar (transaction DB13) or the
       CCMS Database Backup History (transaction DB12).




© SAP AG                                      ADM520                                              4-28
  Monitoring using the SAP Planning Calendar

                        Mon 12     Tue 13     Wed 14      Thu 15    Fri 16     Sat 17          Sun 18
                        success    success    success    success      0:00


                        Mon 19     Tue 20     Wed 21     Thu 22     Fri 23      SQL
                                                                               Sat 24 task information
                                                                                          Sun 25
                                                                               ************** Task information ******************
                        success    success    success    success    2:00       Jobname:                            SAP CCMS Log Backup of PRD
                                                                    error      Taskname:                           step1
                                                                               Type:                               TSQl
                                                                               DB-Name:                            PRD
                        Mon 26      Tue 27    Wed 28     Thu 29      Fri 30    Lastrun:                            20021118 20000
                                                                               Nextrun:                            20021125 20000
                        0:00 DB    0:00 DB    0:00 DB    0:00 DB    0:00 DB    Created:                            Nov 14 20025:18PM
                        9:00 Log   9:00 Log   9:00 Log   9:00 Log   9:00 Log   Command:                            sap_backup_log @r3db="EW1",@
                                                                                                                   @bdev="R3DUMP0",@expdays=28
                                                                               Description:                        SAP CCMS JOB

                        Mon 02     Tue 03     Wed 04     Thu 05      Fri 06
                                                                               *************Task history information **********
                        0:00 DB    0:00 DB    0:00 DB    0:00 DB    0:00 DB    For run:                            20021118 020000
                        9:00 Log   9:00 Log   9:00 Log   9:00 Log   9:00 Log   Status:                          1                      (success)
                                                                               Message:                         4029
                                                                               Severity:                        10
                                                                               Duration:                        0 hours, 56 minutes, 29 seconds
                                                                               Last message:                    DBCC execution completed. If DBCC printed

                                              SAP Layer                                                         error messages, contact your system admin




       DB Interface
                                      Database Layer

                      Job Scheduling Engine
                                                                                             msdb tables
                        SQL ServerAgent




       SAP AG 2002




  You can check whether your most recent database and transaction log backups are usable in the SAP
  Planning Calendar. It is best to check the backup immediately after the backup has finished, before
  you remove the tape.
  You should check whether the transaction log backups are completed successfully on a daily basis.
  Omitting such a check could have serious consequences. In the event of a hardware crash and a
  subsequent restore operation, this may result in the loss of several hours of data.
  The status of a finished task is marked red if the task failed, or green if the task was completed
  successfully. To display more information on a completed task, select the task and double-click.
  Each task not run successfully should be checked immediately.
  The primary attributes of a backup job scheduled using the SAP Planning Calendar are:
       Jobname: The name of the job. Backups scheduled using the SAP Planning Calendar always
       begin with SAP CCMS [Log | Database ] of <database list> [timestamp]. The timestamp
       specifies the scheduling time and is always unique.
       Job Step: A job step is an action that the job takes on a database or a server. The job step is
       defined by the transact-SQL statement (TSQL) sap_backup_databases,
       sap_backup_diff_databases or sap_backup_log.
  The task history section returns information on the job and the job step execution. This information
  is read from msdb table sysjobsteps, which contains the information for each step in a job to be
  executed. Table sysjobhistory contains information about the execution of scheduled jobs.
  If the option verify has been chosen, the runtime of the scheduled is longer even though the backup
  itself is already finished. The verification of the backup is done after the backup is completed and
  belongs to the job, but not to the backup.




© SAP AG                                                   ADM520                                                                                           4-29
  Monitoring using the Database Backup History




       SAP AG 2002




  Checking individual backups is not sufficient to ensure that your backup strategy is being
  implemented successfully. You should also periodically check all of your database backups together
  to make sure that your strategy is being adhered to and there are no gaps in the sequence.
  In the SAP System, the Database Backup History (transaction DB12) offers an overview of all
  database backups and all transaction log backups scheduled in the SAP Planning Calendar.
  Choose Backup History and then All Backups. A result list appears showing technical details about
  all backups that have been executed for the last 30 days (default). Select a backup and choose
  History for additional details.
  If a backup failed diagnose exactly what happened. To do so, it is necessary to also look at the SQL
  Server error logs.
  The Backup Device List displays all the defined backup devices.




© SAP AG                                       ADM520                                              4-30
  Verification Using SQL Enterprise Manager




    Name                                  Enabled Runnable Scheduled Status    Last Run Status      Next Run Date

     SAP CCMS Blocking Lockstats              Yes   Yes    Yes       not running succeeded (10/30/20 10/30/2002 10:05:00 AM
   X SAP CCMS Full DB Backup of PRD, master   Yes   Yes    Yes       not running failed              11/03/1998 10:00:00 PM
     SAP CCMS Full DB Backup of PRD, master   Yes   Yes    Yes       not running succeeded (10/28/20 11/04/2002 10:00:00 PM
     SAP CCMS Full DB Backup of PRD, master   Yes   Yes    Yes       not running succeeded (10/29/20 11/05/2002 10:00:00 PM
     SAP CCMS Full DB Backup of PRD, master   Yes   Yes    Yes       not running succeeded (10/30/20 11/06/2002 10:00:00 PM
     SAP CCMS Full DB Backup of PRD, master   Yes   Yes    Yes       not running succeeded (10/31/20 11/07/2002 10:00:00 PM
   X SAP CCMS Log Backup of PRD               Yes   Yes    Yes       not running failed              11/03/2002 10:00:00 AM
     SAP CCMS Log Backup of PRD               Yes   Yes    Yes       not running succeeded (10/27/20 11/03/2002 11:00:00 AM
     SAP CCMS Log Backup of PRD               Yes   Yes    Yes       not running succeeded (10/27/20 11/03/2002 12:00:00 AM
     SAP CCMS Log Backup of PRD               Yes   Yes    Yes       not running succeeded (10/27/20 11/03/2002 01:00:00 PM
   X SAP CCMS Log Backup of PRD               Yes   Yes    Yes       running     failed              11/03/2002 02:00:00 PM
     SAP CCMS Log Backup of PRD               Yes   Yes    Yes       not running Unknown             11/03/2002 03:00:00 PM
     SAP CCMS Log Backup of PRD               Yes   Yes    Yes       not running succeeded (10/27/20 11/03/2002 04:00:00 PM




       SAP AG 2002




  To display the job history in the Enterprise Manager, select the folder Management, SQL Server
  Agent, item Jobs.
  All the jobs scheduled from the SAP System begin with 'SAP‘. For example, 'SAP CCMS DB
  backup of <Databases>'. Jobs whose status is ‘failed' should be analyzed in detail. Double-click the
  job to retrieve more information. Do NOT use the Enterprise Manager to change the attributes of
  jobs coming from the SAP System. They should always be modified using the SAP Planning
  Calendar.
  SQL Server keeps the history data of backups and jobs in system tables in database msdb. Some
  system tables were mentioned in previous slides. Tables that contain information about backups
  include:
       backupfile: contains one row for each database and log backup made
       backupset: contains one row for each backup set




© SAP AG                                                  ADM520                                                              4-31
  Backup Errors - Additional Information




       SAP AG 2002




  If a backup error occurs, you can find additional information in the Event Viewer and in the database
  error log. Further notification in case of a failure of the scheduled job can be defined using the
  Enterprise Manager.
  Select Start → Settings → Control Panel → Administrative Tools → Event Viewer to open the Event
  Viewer.




© SAP AG                                       ADM520                                              4-32
  Summary of this Unit



              Now you are able to:

                     Describe and perform the different backup types which
                     are supported by SQL Server
                     Explain the technical implementations of the described
                     backup types and name the involved system tables
                     Define a backup strategy that is suitable for your
                     company and implement the scenario
                     Verify the backups




      SAP AG 2002




© SAP AG                                   ADM520                             4-33
  Further Documentation




                      ADM100 mySAP Technology Administration

                      SQL Server Books Online


                      SAP Online Help


                      Windows Online Help




      SAP AG 2002




© SAP AG                     ADM520                            4-34
  Unit Actions




                     ?    Exercises




                          Solutions




      SAP AG 2002




© SAP AG                 ADM520       4-35
                                              Database Backup Exercises

                   Unit: Database Backup
                   Topic: Performing Backups

                   At the conclusion of this exercise, you will be able to:
                   • Perform and verify database and transaction log backups using the
                     SAP Planning Calendar, the SQL Server Enterprise Manager and
                     transact-SQL commands


                   The customer chooses a suitable backup strategy that meets the
                   requirements for his company and implements the backup strategy using
                   the SAP Planning Calendar and SQL Server tools.




   1-1     Back up the database using the SAP Calendar. Perform a full database backup using
           the SAP Planning Calendar, as described in Unit 4: Database Backup.
           1-1-1 Call the SAP Planning Calendar. Schedule a full database backup of all
                 three databases recurring weekly at 10.00 p.m. to backup device
                 R3DUMP4.
                 Which options do you have to set?
           1-1-2 Schedule a Transaction Log Backup for the next day at 12.00 p.m. to
                 backup device R3DUMP5.
                 Which options do you have to set?
           1-1-3 Check the parameters of your scheduled tasks.

   1-2     Back up the database using the SQL Enterprise Manager. Perform an immediate
           full database backup of databases test and master and backup the transaction log of
           database test using the SQL Enterprise Manager.
           1-2-1 Open the SQL Enterprise Manager. On your local SQL Server perform a
                 scheduled immediate full database backup of databases test and master to
                 backup devices R3DUMP1 and R3DUMP2.
                 What are the naming conventions for the media set name, assuming that test
                 is the SAP database?
                 What other options do you set?
           1-2-2 Perform a scheduled immediate backup of the transaction log of database
                 test to R3DUMP3.
                 What options will you set?
                 What are the naming conventions for the volume labels assuming that test is
                 the SAP database?
           1-2-3 Check the backups.


© SAP AG                                   ADM520                                          4-36
   1-3     Back up the database using the Query Analyzer. Make an immediate database
           backup of msdb using Query Analyzer commands. If necessary, check Books
           Online on the correct syntax on the BACKUP command.
           1-3-1 Open a Query Analyzer window. Enter the correct transact-SQL command
                 to back up database msdb to backup devices R3DUMP1 and R3DUMP2 and
                 execute it. Append the backup to the existing backups of databases test and
                 master on the two backup devices.
           1-3-2 Back up the transaction log of msdb to R3DUMP5 using the correct
                 transact-SQL command.
                 Are you able to perform a backup?
                 If not, what error message is displayed?




© SAP AG                                  ADM520                                        4-37
                                              Database Backup Solutions

                   Unit: Database Backup
                   Topic: Performing Backups


   1-1     Back up the database using the SAP Calendar. Perform a full database backup using
           the SAP Planning Calendar, as described in Unit 4: Database Backup.
           1-1-1 Call the SAP Planning Calendar under Tools → CCMS → DB
                 Administration → Planning Calendar → DB13 – Local (transaction code
                 DB13). Double-click today’s date. In the dialog box, enter Database Backup
                 and the correct time (10:00 p.m.). In the next dialog box, databases master,
                 msdb and DEV must be selected. In the Backup Device Selection dialog box
                 select backup device R3DUMP4.
                 Set the expiration period to 27 days.
                 You need to set the following options: initialize, unload tape, and verify.
           1-1-2 Call the SAP Planning Calendar under Tools → CCMS → DB
                 Administration → Planning Calendar → DB13 – Local (transaction code
                 DB13). Double-click tomorrow’s date. In the dialog box, enter Transaction
                 Log Backup and the correct time (12:00 p.m.). In the Backup Device
                 Selection dialog box, select backup device R3DUMP4.
                 Set the expiration period to 27 days.
                 Also set nounload, initialize device, and verify since it is the first backup
                 you are performing on R3DUMP5.
           1-1-3 To check the parameters, select the scheduled task and choose Parameters.

   1-2     Back up the database using the SQL Enterprise Manager. Perform an immediate
           full database backup of databases test and master and backup the transaction log of
           database test using the SQL Enterprise Manager.
           1-2-1 Open the SQL Enterprise Manager. Choose Tools → Backup Database.

                  Select the database (test) from the database list, type a name and a
                  description, and select Database - complete.

                  In the destination list, select both backup devices R3DUMP1 and
                  R3DUMP2. If only one backup device is displayed in the list, add a second
                  device by choosing button Add.

                  Choose radio button Overwrite existing media.

                  Choose Schedule. In the Edit Schedule window select One Time to run the
                  backup immediately. Choose OK when you have specified all the
                  information for the backup.




© SAP AG                                   ADM520                                           4-38
                  Under tab Options, set flag Initialize and label media and enter the media
                  set name (ID<dd>P, where <dd> is the day of the month.) and set the
                  backup to expire after 27 days.

                  Do the same for database master, but make sure that you set the Append to
                  media option. This ensures that the backup will be appended to the devices.
                  In the Option folder, you can check the media set name and expiration
                  period.
           1-2-2 Open the SQL Enterprise Manager. Choose Tools → Backup Database.

                  Select the database (test) and Transaction Log. Select backup device
                  R3DUMP3.

                  Schedule the backup for immediate execution and specify a name for the
                  task.

                  Under tab Options, select Initialize and label media and Expires after 27
                  days. Remove inactive entries from the transaction log.

                  The volume name for the test transaction log backup is RL<dd>S, where
                  <dd> stands for the day of the month.
           1-2-3 Check the run status, content and volume label of the scheduled backups by
                 choosing Management → SQL Server Agent → Jobs.

                  If a backup was completed successfully, the SQL Server error log will show
                  a message similar to this:
                  Database backed up: Database: test, creation date(time):
                  2003/02/18(18:28:24), pages dumped: 125, first LSN: 7:164:1, last LSN:
                  7:168:1, number of dump devices: 2, device information: (FILE=1,
                  TYPE=DISK, MEDIANAME='ID18P': {'R3DUMP1', 'R3DUMP2'}).

   1-3     Back up the database using the Query Analyzer. Make an immediate database
           backup of msdb using Query Analyzer commands. If necessary, check Books
           Online on the correct syntax on the BACKUP command.
           1-3-1 In the Enterprise Manager, choose Tools → SQL Server Query Analyzer.
                 Alternatively you can directly open a Query Analyzer window. Issue the
                 following command:

                  BACKUP DATABASE msdb to R3DUMP1, R3DUMP2 WITH
                  NOINIT, MEDIANAME = ‘ID<dd>P’

                  where MEDIANAME is the name used in exercise 1-2-1.

                  If the backup is successful, the following message will be returned:

                  Processed 1456 pages for database 'msdb', file 'MSDBData' on file 3.
                  Processed 1 pages for database 'msdb', file 'MSDBLog' on file 3. BACKUP
                  DATABASE successfully processed 1457 pages in 8.370 seconds (1.425
                  MB/sec).



© SAP AG                                   ADM520                                             4-39
           1-3-2 Issue the following command:

                 BACKUP LOG msdb TO R3DUMP5

                 It returns with message:

                 Server: Msg 4208, Level 16, State 1, Line 1
                 The statement BACKUP LOG is not allowed while the recovery model is
                 SIMPLE. Use BACKUP DATABASE or change the recovery model using
                 ALTER DATABASE.
                 Server: Msg 3013, Level 16, State 1, Line 1
                 BACKUP LOG is terminating abnormally.




© SAP AG                                    ADM520                                 4-40
  Database Restore




           1   Introduction

           2   SQL Server Architecture

           3   How the SAP System uses SQL Server

        4      Performance Monitoring and Tuning

        5      Database Backup


       6 Database Restore
        7      Regular Maintenance and Error Handling




      SAP AG 2002




© SAP AG                                  ADM520        5-1
  Database Restore



                     Contents
                        Restore strategy and scenarios
                        Restore methods
                        Checking restore



                     Objectives
                     At the end of this unit, you will be able to:
                        Perform a database restore using SQL Server tools
                        Describe the different restore methods
                        Check the restore




      SAP AG 2002




© SAP AG                                        ADM520                      5-2
  Goals of a Database Restore Strategy

                       Physical errors
                     (Such as a media failure)




  External factors
  (Such as fire or water
                                                 Database restore without loss of data
        damage)




                           User errors
                    (Such as a deleted table)    Restoring the database to a specific point in time


   Logical errors
  (Such as a corruption)                         Database restore in a specific time window
                                                 (for various scenarios)



                 Procedure and
                 escalation plan
                                                 Document your procedures and escalation plans




         SAP AG 2002




  If data is lost due to external factors, such as fire or water damage to your hardware, or physical
  errors, such as a hardware failure, you must restore the database up to the point in time when the
  database crashed. If a full restore is possible and the backup strategy is set up properly, only data
  from uncommitted transactions before the error will be lost.
  If data is lost due to logical errors, such as an unintentionally deleted table, you must restore the
  database up to a point in time shortly before the error occurred.
  To ensure that the correct actions are performed for each of the different scenarios, create a
  document containing organizational descriptions of procedures and an escalation plan. This
  document must be available and understood by the person who performs the database restore.




© SAP AG                                                 ADM520                                           5-3
  Principles of a Database Restore




 Availability
                  ONLINE
       Target
                               Replace hardware
       Actual                  and set up system


                                                        Apply          Apply
                           Problem         Restore                                Automatic
                                                     differential   transaction
                           analysis       database                                 recovery
                                                      backups           logs



                             OFFLINE


                                                                                              Time




       SAP AG 2002




  An appropriate backup and restore strategy ensures minimum down time when the SAP System
  becomes unavailable due to an error. SAP recommends that you plan these strategies in advance and
  test them in your test system.
  When you plan your backup strategy, consider how long you can afford to shut down the SAP
  System in different scenarios. To calculate the total time required for a full database restore, include
  the following times required for:
     Analyzing the error
     Replacing the required hardware, and setting up the operating system and file systems
     Restoring the database from the database backup
     If differential backups were made, apply the last differential database backup
     Performing a forward recovery from the backed up transaction logs
     Performing an automatic recovery at server startup
  The maximum downtime of a productive SAP System is the time from when the error is noted to the
  time when the administrator finishes checking the restored system.
  The database is restored as follows:
     The content of the database backup is copied from the backup medium to the database (Restore).
     If differential backups were made, the last differential database backup is copied.
     All transaction log files are imported from the backup medium to the database.
     The automatic recovery rolls back uncommitted transactions and rolls forward committed
     transactions.




© SAP AG                                           ADM520                                               5-4
  Automatic Recovery (1)


                                                                      Data Cache
               Begin Tran1
                                                                     x1
                              SQL Server

     User 1                                                          x2

                                                                                              Step 1
               Begin Tran2
                                                                     <SID>DATA1   <SID>LOG1
     User 2

               Commit Tran1
                                                                     x1
                                                                          dirty
                              SQL Server
                                            Begin Trans1             x2
                                                                          dirty
     User 1                                 Begin Trans2
                                            Trans1: update                                    Step 2
                                            Trans2: insert
                                            Commit Trans1    Action written to
                                                             transaction log
     User 2

      SQL Server goes down due to power outage before checkpoint occurs Step 3



       SAP AG 2002




  SQL Server stops processing for example, if an operator restarts the server while users are connected
  and working in databases or in case of a sudden power outage. An automatic recovery is then carried
  out when SQL Server is restarted. This ensures that all transactions committed before system failure
  are physically entered in the database because the corresponding modified data pages might not have
  been flushed to the data files yet. All other partially completed and thus uncommitted transactions
  are rolled back. This principle is explained in the example shown.
  Two transactions, TRAN1 and TRAN2, are started by two different users. The transactions are
  recorded in the transaction log as BEGIN TRAN1 and BEGIN TRAN2. Both transactions change
  pages in the data cache, and these pages are marked dirty.
  In the second step, user 1 ends the transaction with a COMMIT TRAN1. SQL Server writes all log
  information on Transaction TRAN1 in the transaction log. COMMIT confirms that all the affected
  log records in the cache are written to the transaction log.
  A checkpoint causes SQL Server to write the data pages marked dirty to the database. In this
  example however, SQL Server and the Windows Server go down before the checkpoint, due a power
  outage.




© SAP AG                                       ADM520                                               5-5
  Automatic Recovery (2)




      Automatic recovery
                                             Begin Trans1
                                             Begin Trans2                 Written TRAN1
                 Roll back                   Trans1: update
                                                                          to database
                                                                                                         <SID>DATA1    Step 4
                                             Trans2: insert
                                             Commit Trans1




    Date                     Source   Message

    03/01/21 08:54:07.43     spid1    Recovering of database PRD (7) is 0% complete (approximately 21 more seconds)
    03/01/21 08:54:07.44     spid1    Recovering of database PRD (7) is 100% complete (approximately 0 more seconds)
    03/01/21 08:54:07.45     spid1    Recovery is checkpointing database PRD (7).
    03/01/21 08:54:07.46     spid1    Recovering database ‘PRD’
    03/01/21 08:54:07.47     spid1    Starting up database tempdb
    03/01/21 08:54:07.48     spid1    Analysis of database tempdb is 100% complete.
    03/01/21 08:54:07.43     spid1    Recovery completed




       SAP AG 2002




  In the fourth step, the Windows Server and SQL Server are started again. Whenever SQL Server is
  started, the automatic recovery is processed in each SQL Server database:
     The LSN of the last checkpoint is read from the database boot block along with the Minimum
     Recovery LSN.
     The transaction log is scanned from the Minimum Recovery LSN to the end of the log. All
     committed dirty pages are rolled forward by redoing the logical operation recorded in the log
     record.
     SQL Server then scans backward through the log file rolling back all uncompleted transactions by
     applying the opposite of the logical operation recorded in the log records.
  This type of recovery is also done by a RESTORE statement. After restoring the copy of the
  database or log, the RESTORE statement also has to ensure that all committed log records are rolled
  forward and all uncompleted transactions are rolled back.
  Check the result of an automatic recovery in the SQL Server error log.
  SQL Server provides configuration parameter Recovery interval (min) for automatic recovery. It
  defines the maximum time in minutes needed for an automatic recovery per database. SQL Server
  uses this option to determine when a checkpoint must be released. The default is 0, indicating
  automatic configuration by SQL Server. In practice, this means a recovery time of less than one
  minute and a checkpoint approximately every one minute for each database.




© SAP AG                                             ADM520                                                                     5-6
  Restore Scenarios



              External factors                                            Physical errors
              (Such as fire or water                                    (Such as a media failure)
                    damage)




                                            Possible
                                               error
                                           situations

                                                                          Logical errors
                 User errors                                             (Such as a corruption)
             (Such as a deleted table)


                                           Hardware Move
                                           (to a newer hardware)




       SAP AG 2002




  Errors requiring a database restore are caused by one of the following:
     Hardware failure, for example when a disk crashes and affects the data or transaction log files
     A logical error resulting in a database corruption
     User error, such as the accidental deletion of a table
     A disaster that destroys existing hardware
     A move to new hardware involving the whole database system
  If any of the above situations occur, use the restore procedures described in the following slides.
  After a power failure, the database does not have to be restored. When power is cut off, the system
  shuts down abruptly and all current transactions are aborted. SQL Server has an automatic recovery
  mechanism to deal with such a situation. This mechanism is explained in later slides.




© SAP AG                                        ADM520                                              5-7
  Restore due to Media Failure


                                           Physical errors
                                         (Such as a media failure)




                                                                      Directory
             <SID>DATA3                                                   C:\Tempdb
               <SID>DATA2
                 <SID>DATA1              <SID>LOG1       <SID>LOG1
                                                                          C:\Mssql\
                                                                          C:\usr\sap\
             PRIMARY                                                      C:\WINNT




       SAP AG 2002




  The procedure required to bring the database back to its original state differs depending on how files
  are distributed on the system and which systems or disks are affected by the failure. Assuming that
  data distribution follows the recommendations in the SAP Installation Guide and as described in
  course ADM100, then the system files should be distributed across 3 volumes as follows:
    Volume 1 Log files of the SAP database
    Volume 2 Data files of the SAP database
    Volume 3 SAP executables, SQL Server executables, Windows executables
  The procedure used for a database restore depends on which volume crashed due to media failure.




© SAP AG                                           ADM520                                             5-8
  SAP Data Volume Crash


     Procedure and                  Back up recent
     escalation plan                transaction log                    <SID>


                                                                  <SID>DATA3
                                 Replace disk in RAID               <SID>DATA2
                                                                     <SID>DATA1
                                       system
                                                                  PRIMARY


                                Restore SAP database                   <SID>


                                   Apply differential                  <SID>
                                      backups


                               Restore transaction logs                <SID>



                               Check restore operation

       SAP AG 2002




  The SAP data files reside in a RAID5 system for increased data security. If a single disk fails, it must
  be replaced and the RAID5 system must be synchronized. Database operation is not interrupted.
  However, if the whole RAID5 system crashes, the SAP System comes to a standstill. An error is
  written to the SQL Server error log. In this case the database must be restored.
  The following steps must be taken to restore the database after an SAP data volume crash:
    Immediately back up the current transaction log before shutting down SQL Server
    Replace disks in the RAID5 system; the disks should be formatted and assigned the same drive
    letter as the old ones
    Restore the database by applying the most current database backup
    If differential database backups were made after the last full database backup, the most current
    differential database backup can be applied.
    Restore the transaction log by applying all transaction logs created since the most current database
    backup or the most current differential backup, then restore the transaction log created
    immediately after the volume crashed
    Check the restore
  These steps must be described in detail in a separate document explaining your procedure and
  escalation plan.
    Note: If the disks on which the database resides are damaged, work performed after the last
    transaction log backup will be lost. Back up the most current transaction log. Do this before
    shutting down SQL Server. The current transaction log can only be backed up if the disks holding
    the transaction log and the executables disk are undamaged.




© SAP AG                                        ADM520                                                 5-9
  Backing up the current transaction log



             SQL Server Query Analyzer - [Query - sapprod.master.sa - (untitled) - #1 New query]         _   x
              File Edit View Query Window       Help                                                     _   x
                                                                                          DB:      PRD




            BACKUP LOG PRD to R3DUMP0 WITH NO_TRUNCATE, FORMAT




       SAP AG 2002




  If the disks on which the SAP database resides crashes, it is important to immediately back up the
  current transaction log, otherwise the work carried out since the last transaction log backup will be
  permanently lost.
  The current transaction log can only be backed up, if the disks on which the transaction log resides is
  undamaged and the disk on which the SQL Server executables reside is undamaged.
  To back up the current transaction log, proceed as follows:
     Insert a new tape into the tape device
     Open the Query Analyser and enter the following transact-SQL statement:
     BACKUP LOG <SID> to <backup_device> WITH NO_TRUNCATE, FORMAT
     Execute the statement




© SAP AG                                                           ADM520                                        5-10
  SAP Log Volume Crash



     Procedure and
     escalation plan               Replace RAID system           <SID>LOG1    <SID>LOG1




                                   Restore SAP database                       <SID>



                                      Apply differential                     <SID>
                                         backups



                                 Restore transaction logs                    <SID>




                                  Check restore operation


       SAP AG 2002




  The transaction log files of the SAP database normally reside in a RAID1 system for reasons of
  increased security. If one disk crashes, the mirrored disk can be used to bring the database up again.
  If both disks crash, the SAP database would be marked suspect and a restore is necessary.
  If the transaction log is not available anymore, the current transaction log cannot be backed up. The
  data between the last transaction log backup and the crash of the RAID1 system is therefore lost.
  If the SAP log volume crashes, proceed as follows:
     Replace disks in the RAID1 system. The disks must be formatted and assigned the same drive
     letters as the old ones
     Restore the database by applying the most current database backup
     If differential database backups were made after the last full database backup, the most current
     differential database backup can be applied.
     Restore the transaction log by applying all transaction logs created after the most current database
     backup or the most current differential backup
     Check the restore
  These steps must be described in detail in a separate document explaining your procedure and
  escalation plan.




© SAP AG                                        ADM520                                                5-11
  Executable Volume Crash



     Procedure and                Replace disks and install
     escalation Plan                 auxiliary Windows

                                                                           Directory
                                                                             C:\Tempdb
                                   Reload lost files from                    C:\Mssql\
                                  latest Windows Backup                      C:\usr\sap\
                                                                             C:\WINNT



                                 Reboot Primary Windows



                                     Restore msdb (and
                                                                          master         msdb
                                     master) Database



                                 Check Restore Operation



       SAP AG 2002




  If the disk with all other files except the SAP data and transaction log files crashes, the Windows
  operating system, SAP executables, SQL Server executables, and the msdb and master databases are
  lost. The operating system including the SAP System and SQL Server cannot start.
  If the executable volume crashes, proceed as follows:
     Replace disks. The disks must be formatted and assigned the same drive letters as the old ones.
     Install an auxiliary Windows operating system.
     Restore all lost files using the latest Windows backup. In this scenario, the SAP data and
     transaction log files are not affected as they reside on different volumes. Do not reload these files
     from the Windows backup.
     Reboot Windows with the restored (primary) Windows system.
     Restore the msdb database using the latest backup
     You may also have to restore the master database if its contents were changed, for example, when
     SQL Server configuration parameters were changed
    Check the restore
  These steps must be described in detail in a separate document explaining your procedure and
  escalation plan.




© SAP AG                                        ADM520                                                 5-12
  Restore due to Media Failure - Summary

       One disk         RAID 1 crash :       RAID 5 crash:      System crash:       Exe disk crash:
        crash            Log files lost      Data files lost   Data + EXEs lost       EXEs lost
                        <SID> suspect        <SID> suspect     SQL Server down     SQL Server down


                                              Back up log
                                              <SID> with
                                              no_truncate


                              Shut down SQL Server

                               Replace crashed disk(s), synchronize RAID


                                                                     Windows Restore of EXEs;
                                                                          not log file(s)!


                                                                 SQL Restore of msdb (and master)

                             SQL Restore of database and transaction logs

                                                    Automatic recovery

           OK           Some data lost!           OK                  OK                  OK

       SAP AG 2002




  If a hard disk containing a database data file in a RAID5 system crashes, the situation is not serious
  unless the whole RAID5 system goes down, in which case the data files in the file system are lost
  and must be restored.
  When both RAID1 hard disks containing the transaction log files crash, a whole database restore
  must also be performed. In this type of situation, you are likely to experience some data loss because
  the current transaction log cannot be backed up.
  If the volume containing the executables of SQL Server, the SAP System, and Windows crashes,
  only these executables must be restored using Windows Restore. To bring the job history to a current
  state, you should perform an SQL Server restore of database msdb. The content of the master
  database rarely changes. If after a Windows Restore the content of the master database is not current,
  perform a database restore of the master database.
  After checking the restore, restart SQL Server. SQL Server then executes an automatic recovery to
  roll back uncommitted transactions.




© SAP AG                                       ADM520                                               5-13
  Restore due to Logical Error


                                         Logical errors
                                       (Such as a corruption)




                                          <SID>




       SAP AG 2002




  When a database corruption is detected, the problem must be diagnosed further to determine the
  extent and cause of the damage. The optimal method of restoring the physical consistency depends
  on the following considerations:
    Type of object affected, for example an index or table
    Location of the object, for example in database <SID>
    Extent of the damage
  Open a problem message using the Online Service System (OSS) and have a support engineer
  analyze the error to find the most effective solution.




© SAP AG                                        ADM520                                          5-14
  SAP Database Corruption



     Procedure and                     Back up recent
     escalation plan                                                     <SID>
                                       transaction log



                                   Restore SAP database                  <SID>



                                      Apply differential                 <SID>
                                         backups



                                 Restore transaction logs                <SID>




                                  Check restore operation



       SAP AG 2002




  To repair a corruption on the SAP database, a database restore may be necessary. In this scenario,
  you do not need to recreate the RAID5 system, since the data files stored there are not damaged.
  Perform the following steps:
    Immediately back up the current transaction log before shutting down SQL Server
    Restore the database by applying the most current database backup which is successfully checked
    for not containing corruptions
    If differential database backups were made after the last full database backup, the most current
    differential database backup can be applied.
    Restore the transaction log by applying all transaction logs written since the most current database
    backup or differential backup
    Check the restore
  These steps must be described in detail in a separate document explaining your procedure and
  escalation plan.




© SAP AG                                       ADM520                                               5-15
  Restore due to User Error


                                             User errors
                                         (Such as a deleted table)




                                              <SID>




       SAP AG 2002




  A user might accidentally delete a table, or import a faulty transport. Depending on what has
  happened, there are different ways of solving the problem.
  To cancel incorrect changes such as the accidental deletion of a table, perform a point in time
  recovery.




© SAP AG                                           ADM520                                           5-16
  Point In Time Recovery - Principles


                               Go back to the state as recorded on Wednesday at 09:30 a.m.




  Database backups

   Sunday             Monday      Tuesday       Wednesday        Thursday        Friday




                                                                                          Time

  Transaction log
            backup                                              10:00

                                                                09:00
                                                        08:00




       SAP AG 2002




  A point in time recovery enables you to reset a database to a specific date and time. Use this method
  when you need to return the database to an earlier state, due to user error.
  During a point in time recovery, all transactions completed after a given point in time are rolled
  back.
  Point in time recovery can only take place while the transaction logs are being restored, not during a
  database restore.
  In this example, we can assume that the transaction log was backed up every hour, at 8.00 a.m., 9.00
  a.m., and at 10.00 a.m. In order to return to Wednesday's 9.30 a.m. state, you need the transaction log
  backups from Wednesday 9.00 a.m. and 10.00 a.m., since these contain the transactions executed
  before 10.00 a.m. All transactions that were not completed by the specified time are rolled back. You
  can only recover data up to a point in time where there was a checkpoint in operation on the
  database. A checkpoint can also be set explicitly by a user through the transact-SQL statement
  CHECKPOINT.
  For details, see Unit 1: SQL Server Architecture.




© SAP AG                                       ADM520                                                5-17
  Restore due to External Factors / Hardware Move


                              External factors                          Hardware Move
                              (Such as fire or water
                                                                       (to a newer hardware)
                                    damage)




                                                                                               Directory
             <SID>DATA3                                                                            C:\Tempdb
                                                                                                   C:\Mssql\
               <SID>DATA2                                                                          C:\usr\sap\
                                                                                                   C:\WINNT
                 <SID>DATA1                            <SID>LOG1   <SID>LOG1
                                                                                                   Y:\<SID>LOG1
                                                                                                   X:\<SID>DATA1
             PRIMARY                                                                               X:\<SID>DATA2
                                                                                                   X:\<SID>DATA3




       SAP AG 2002




  If the system crashes due to external factors, the whole file system is lost and has to be set up newly.
  The same procedure has to be followed when your SAP environment moves to a new hardware.
       Set up all hardware including
          • RAID systems
          • Tape device
       Install Windows
       Restore SQL Server and the SAP System




© SAP AG                                                       ADM520                                              5-18
  Complete System Setup

                                   Replace hardware and
                                 install auxiliary Windows
                                                                                Directory
                                                                                    C:\Tempdb
     Procedure and               Reload executables from
                                                                                    C:\Mssql\
                                                                                    C:\usr\sap\
     escalation plan                                                                C:\WINNT
                                 latest Windows Backup                              Y:\<SID>LOG1
                                                                                    X:\<SID>DATA1
                                                                                    X:\<SID>DATA2
                                                                                    X:\<SID>DATA3

                                 Reboot primary Windows


                                    Restore msdb (and
                                     master) database                    master    msdb



                                  Restore SAP database                   <SID>

                                     Apply differential                 <SID>
                                        backups

                                 Restore transaction logs               <SID>


                                 Check restore operation

       SAP AG 2002




  If your whole system crashes, proceed as follows :
       Replace hardware
       It is best to format and assign the disks to the same drive letters as the old ones.
       Install an auxiliary Windows operating system
       Restore all executables using the latest Windows backup
       SQL Server restore operations recreate lost files. Do not reload these files from the Windows
       Backup.
       Reboot Windows with the restored (primary) Windows system and restart SQL Server
       The <SID> database will be marked suspect.
       Restore the master and msdb database using the latest backup
       Restore the SAP database and apply all proceeding differential backups (if available) and
       transaction logs from the latest backups
       Check the restore
  These steps must be described in detail in a separate document explaining your procedure and
  escalation plan.




© SAP AG                                       ADM520                                                  5-19
  Master Database Not available



     Procedure and
     escalation plan



                                                                                     Directory
                                                                                        C:\Tempdb
                                             Reload master files                        C:\Mssql\
           Rebuild master database
                                         from latest Windows backup                     C:\usr\sap\
                                                                                        C:\WINNT




                          Restore master database                 master




                          Check restore operation




       SAP AG 2002




  If the database master is corrupted, you may need to restore the database master. However, you do
  not need to recreate the data files, as they are not damaged. In this scenario SQL Server cannot start,
  as all the necessary information it reads is from the master database. You must therefore restore the
  database master from the latest Windows backup to create a functioning master database.
  Perform the following steps:
       Restore the master.mdf and mastlog.ldf files from the latest Windows backup
       Restore the master database from the latest SQL Server backup to get the current state of the
       master database
       Check the restore
  If a current Windows backup of database master is not available, master can be rebuilt using the
  Rebuild master utility. When master has been rebuilt, a current backup of master must be restored to
  create the SAP database, the backup devices, and SQL Server logins. Using Rebuild Master is not
  the recommended strategy for creating the database master, and with a proper backup strategy it is
  unnecessary.
  Before you rebuild your database master, consult a SQL Server support engineer.
  These steps must be described in detail in a separate document explaining your procedure and
  escalation plan.




© SAP AG                                        ADM520                                                5-20
  Restore Methods



                      Restore from backup history

                      Restore from device

                      Restore to a point in time




       SAP AG 2002




  Restoring a database backup returns the database to the same state it was in when the backup was
  created. Restoring a transaction log backup reapplies all completed transactions that are in the
  transaction log backup to the database. SQL Server reads through the transaction log, rolling forward
  all the transactions on the transaction log. When SQL Server reaches the end of the transaction log, it
  has recreated the exact state of the database at the time the backup operation started. The restore
  operation then rolls back all transactions that were incomplete when the backup operation started.
  The next section introduces the three different restore methods and their tools:
       Restore from history
       Restore from device
       Restore to a point in time




© SAP AG                                        ADM520                                               5-21
  Restore From Backup History

  Restore database                                                                     x

   General   Options


             Restore as Database:       PRD



      Restore:         Database             Filegroups or file             From device database
                                                                                Restore                                                                               x
      Parameters
                                                                                 General     Options
     Show backups for database:         PRD
     First backup to restore:           12/1/03 01:00: 47 AM -
                                                                                     Eject tapes (if any) after restoring each backup
          Point in time restore                                                      Prompt before restoring each backup
                                                                    ...
                                                                                     Force restore over existing database
                                                                Restore database files as:
     Restore Type Backup set date Size      Restore from Backup set na
                                                                  Original file name                                  Restore as
                 12/1/03 01:00... 960640 Kb //./Tape0
                                                                                   F:\PRDLOG1\PRDLOG1.ldf             F:\PRDLOG1\PRDLOG1.ldf
                     12/1/03 08:01...    9408 Kb        //./Tape0
                                                                                   E:\PRDDATA2\PRDDATA2.ndf           E:\PRD4DATA2\PRDDATA2.ndf
                     12/1/03 09:00...    256 Kb         //./Tape0                  E:\PRDDATA3\PRDDATA3.ndf           E:\PRDDATA3\PRDDATA3.ndf
                                                                                   E:\PRDDATA1\PRDDATA1.mdf           E:\PRDDATA1\PRDDATA1.mdf

                                           Properties

                                                                                    Recovery completion state
                                                                                    Leave database operational. No additional logs can be restored.
                                                                                    Leave database nonoperational, but able to restore additional transaction logs.

                                        OK           Cancel               Help      Leave database read-only and able to restore additional transaction logs.

                                                                                    Undo file:    f:\Program Files\Microsoft SQL Server\MSSQL\B ...


                                                                                                                             OK            Cancel           Help
        SAP AG 2002




  SQL Server stores log entries for each backup in msdb tables backupset and backupfile. For a restore
  from backup history using these msdb records, proceed as follows:
       In the Enterprise Manager, choose Tools → Database Restore. In the list, the latest database
       backup and subsequent transaction log backups are already selected. Select the following
       options:
         • Eject tapes (if any) after restoring each backup
         • Prompt before restoring each backup - If one tape device is used, you have to switch tapes
            after restoring each backup.
         • Force restore over existing database - If the database files exist, and you want to override
            the existing database, for example after a restore due to a logical error. Do not select this
            option after a restore due to media failure.
         • Leave database operational; No additional transaction logs can be restored - If all
            transaction logs are restored during this procedure.
       Confirm your entries
  During a database restore, SQL Server automatically recreates the database and its associated files,
  by copying data from the backup into the database. To change original file locations, enter a new
  name under Restore as. The default is the original file name.
  The advantage of working with the method Restore from backup history is that you do not have to
  find the latest backup.
  Note: The system administrator restoring the database backup must be the only person currently
  using the database to be restored.




© SAP AG                                                                     ADM520                                                                                       5-22
  Changing Tapes


                          1
                                                     Restore                                                                            x
                                                          Contents...
                                                              The restore operation has completed and the next restore operation is
                                      PRD                     about to proceed. To continue the restore process, press OK. Otherwise,
                                                              press Cancel.



                                                     Backup set to restore:   12/4/02 11:14:09 AM -

             ID04S                                   Media Name:              RL04S

                                                     Media Description:




                                                                                                              OK           Cancel


                                                                                       Media Set Labels:
                                                     SQL Server Enterprise Manager                                 x
             RL04S                                       Contents...

                                                       ii     Restore of database ‚PRD‘ completed successfully.
                                                                                                                         2
                                                                                  Ok


                                      PRD
                           2
        SAP AG 2002




  If one tape device is used, make sure the right tape is in the tape device. Identify the tape by its tape
  label. If you are unsure about its content, issue a RESTORE HEADERONLY FROM <device> or
  use the Enterprise Manager to display the media header.
  After a database backup is restored, a window appears asking you to insert the new tape to continue
  with the restore (1). Note that this window only appears if option Prompt before restoring each
  backup has been set. Switch the tape and confirm (2). Once the restore is complete, a confirmation
  appears.




© SAP AG                                         ADM520                                                                                     5-23
  Restore From Device

  Restore Database                                                                           x

  General      Options


               Restore Database:       PRD|



     Backup:              Database            Filegroups or files            From device

       Parameters                                                                Choose Restore Device                                                                   x
      Device                                                                                 When the backup is restored, SQL Server will attempt to restore from the
                                                                                             devices listed below.

                                                                      Select Device

                                                                                  Backup set:     ID04S
      Device        1    View Contents ...
                                                                                                     Disk            Tape
            Restore backup set
                 Database - Complete                                              Restore from:    Device name                                         Add...
                 Database - Differential                                                           \\.\Tape0                                            Edit
                 Transaction log
                 File and Filegroup:                                                                                                                 Remove

            Read backup set information and add to backup history                                                                                   Remove All

                                                                                      Media verification option

                                                                                           Only restore from media with the following name:
                                                    OK              Cancel          Help
                                                                                                 Media Name:      ID04S


                                                                                                                                                OK              Cancel

         SAP AG 2002




  If the msdb is not available, the backup history is lost. In this case, you must restore the database
  from the backup device or restore the msdb first, to use restore from history.
  When using Restore from device, in the Enterprise Manager select option From device, then select
  the device. The header of the chosen backup device, for example R3DUMP0 for the tape device is
  then read. To display what has been stored on this device, choose View Contents. Select the type of
  backup you want to restore (Database Complete, Database Differential, Transaction log, File or
  Filegroup), then confirm your entries. When the database backup has been successfully imported,
  use the same procedure to import the transaction log backups in the order in which they were backed
  up.
  To check a media name before performing the restore, specify the option Media verification.
  Alternatively, you can recreate the backup history by choosing option Read backup set information
  and add to backup history. This way the backup header is read from devices specified by you, and is
  added to the backup history tables in the msdb database. Only one backup set can be added at a time
  on one backup media. After adding all the necessary backup sets, you can use the method Restore
  from history.
  The advantage of using the method From Device is that you can restore the database even after
  losing the backup history.




© SAP AG                                                                         ADM520                                                                                      5-24
  Restore From Device Using the Query Analyzer




            SQL Server Query Analyzer - [Query - sapprod.master.sa - (untitled) - #1 New query]         _   x
             File Edit View Query Window       Help                                                     _   x
                                                                                         DB:      PRD




           RESTORE DATABASE PRD FROM R3DUMP0 WITH FILE = 1,
           MEDIANAME = ‚ID04S‘, RECOVERY



           RESTORE LOG PRD FROM R3DUMP0 WITH FILE = 1,
           MEDIANAME = ‚RL04S‘, NORECOVERY



           RESTORE DATABASE PRD FILE = ‘PRDDATA1’ FROM R3DUMP0
           WITH MEDIANAME = ‘RF04S’




       SAP AG 2002




  You can restore a database from a device using the Query Analyzer and transact-SQL statement
  RESTORE DATABASE. This statement includes the following options:
      FROM: Specifies the backup devices from which to restore the backup. If the FROM clause is
      not specified, a restore is not performed; instead, the database is recovered.
      FILE: When specified in the WITH clause, FILE identifies the backup set to be restored. For
      example, a file number of 1 indicates the first backup set on the backup medium and a file
      number of 2 indicates the second backup set.
      When specified in the file list clause, this option identifies the file or filegroup to be restored.
      MEDIANAME: Specifies the media name for the entire backup set. If provided, the media name
      must match the media name on the backup volume; otherwise, the restore operation terminates.
      If no media name is given, the check for a matching media name is not performed.
      NORECOVERY|RECOVERY: Instructs the restore operation (not) to roll back any
      uncommitted transactions. Option NORECOVERY must be specified if another transaction log
      has to be applied. Option RECOVERY is the default.
      REPLACE: Instructs SQL Server to create the specified database and its related files even if
      another database already exists with the same name. In this case, the existing database is deleted.
      When the REPLACE option is not specified, a safety check occurs, which prevents overwriting
      a different database by accident.
  For details, see SQL Server Books Online.




© SAP AG                                                            ADM520                                      5-25
  Point In Time Recovery


           Restore database                                                                        x

           General    Options


                      Restore as Database:       PRD



              Restore:           Database            Filegroups or file             From device

               Parameters

              Show backups for database:         PRD
              First backup to restore:           12/1/03 01:00: 47 AM -
                   Point in time restore
                                                 12/1/03 08:30: 47 AM -      ...

                                                                                   Point In Time Restore
                                                                                                                       Media Set Labels:                x
             Restore Type       Backup set date    Size          Restore from Backup set na
                                                                                 Contents...
                                                                                           Point in time restore will halt the restoration of transaction
                              12/1/03 01:00...    960640 Kb //./Tape0                      log entries recorded after the specified date and time.
                              12/1/03 08:01...    9408 Kb        //./Tape0
                              12/1/03 09:00...    256 Kb         //./Tape0
                                                                                           Date:         Fri 12/ 1/2003

                                                                                           Time:       08:30 AM
                                                    Properties
                                                                                                                               OK            Cancel




                                                  OK          Cancel               Help


       SAP AG 2002




  To perform a point in time recovery, you must first restore the database backup and then define the
  time to which you want to recover your database. The transaction logs are then applied only up till
  the specified point in time.
  Use the Enterprise Manager. Choose Tools → Restore Database. Select the option Point in Time
  Restore. In the dialog box, enter the time to which you wish to restore and confirm your entry.
  Point in time recovery can also be executed through the transact-SQL statement RESTORE LOG
  and the option STOPAT.
  Point in time recovery is not supported with the bulk-logged recovery model. Bulk-logged recovery
  model only allows the database to be recovered to the end of a transaction log backup when the log
  backup contains bulk changes.




© SAP AG                                                               ADM520                                                                               5-26
  Restore Strategies




             Restore from single DB and transaction log backups
             Restore from a parallel DB backup
             Restore from a two-step disk backup
             Restore from supplementary differential backups




       SAP AG 2002




  The method you choose to restore your SAP database depends on the type of error and on your
  chosen backup strategy. However, all restore scenarios start with a restore of the latest database
  backup.
  The next section explains restore strategies, which depend on the different backup strategies
  introduced in Unit 4: Database Backup.




© SAP AG                                        ADM520                                                 5-27
  Restore from Single DB and TL Backups




                  Log backups       Log backups     Log backups



       Sun, 1           Mon, 2           Tue, 3          Wed, 4           Thur, 5        Fri, 6



    Full backup       Full backup     Full backup



                                                              ID03S    1


                                                                  RL04S      2




       SAP AG 2002




  To restore an SAP database from a single database backup and subsequent transaction log backups,
  perform the following steps:
      If possible, back up the current transaction log to a new separate disk backup device, using the
      option NO_TRUNCATE.
      Set the SAP database to single-user mode.
      Restore the most current database backup (1) and all subsequent transaction log backups (2) by
      choosing one of the restore methods:
         • Restore from history or
         • Restore from device.
      Set the option Leave the database non-operational but able to restore additional transaction
      logs.
      Apply the current transaction log that you recently backed up. Set the option Leave the database
      operational no additional transaction logs can be restored. If this option is set, no more
      transaction logs can be applied.
      The database is not recovered until the current transaction log has been applied. This prevents
      any transactions from being partially rolled back. Transactions can only be rolled back at the end
      of the last restore operation, when the current log has been applied.
      Check the restore.




© SAP AG                                          ADM520                                            5-28
  Restore From Differential Backups




                  Log backups        Log backups      Log backups



       Sun, 1           Mon, 2            Tue, 3          Wed, 4           Thur, 5         Fri, 6



    Full backup       Differential     Differential



                                                               ID01S      1


                                                                       R+03S    2


                                                                               RL04S   3

       SAP AG 2002




  To restore an SAP database using supplementary differential backups, perform the following steps:
      If possible backup the current transaction log by using the NO_TRUNCATE option to a new
      separate disk backup device.
      Set the SAP database to single-user mode.
      Restore the last database backup created (1).
      Restore the last differential backup created since the database backup was created (2).
      Apply all transaction log backups, in sequence, created after the last differential backup was
      created, finishing with the transaction log backup created in the first step (3).
      Perform the restore using one of the restore methods:
         • Restore from history or
         • Restore from device.
      Check the restore.




© SAP AG                                           ADM520                                           5-29
  Restore from a Parallel Backup

   Restore database                                                                       x

    General   Options


              Restore as Database:       PRD


                                                                           Backup Set Properties                                                 x
       Restore:          Database            Filegroups or file           From device

       Parameters                                                           General

      Show backups for database:         PRD
                                                                                          Name:        PRD DB backup
      First backup to restore:           12/1/03 01:00: 47 AM -
           Point in time restore
                                                                   ...
                                                                                Description:

      Restore Type      Backup set date    Size          Restore from Backup set na

                      12/1/03 01:00...    10976244 Kb //./Tape0                   Type:                 Database
                      12/1/03 08:01...    94098 Kb         //./Tape0              Size:                 10976244 Kb
                      12/1/03 09:00...    25689 Kb         //./Tape0              Start date:           12/01/03 1:00:47 AM
                                                                                  Finish date:          12/01/03 2:55:19 AM

                                            Properties
                                            Properties                            Media Name:           ID01S
                                                                                  Media Description:


                                                                                  Restore From:
                                                                                                         //./Tape0
                                          OK          Cancel             Help                            //./Tape1
                                                                                                                                   Change...



                                                                                                                              OK        Cancel

        SAP AG 2002




  To restore an SAP database from a parallel database backup and subsequent transaction log backups,
  perform the following steps:
      If possible, back up the current transaction log to a new separate disk backup device, by using
      the option NO_TRUNCATE.
      Set the SAP database to single-user mode.
      Perform a restore as explained in the previous slide using one of the restore methods:
         •    Restore from history or
         •    Restore from device.
      If you perform a restore from history, select Properties to find out which backup devices to
      restore from.
      Put the tapes into the tape devices.
      Check the restore.




© SAP AG                                                                   ADM520                                                                    5-30
  Restore from a Two-Step Disk Backup




                  Log backups       Log backups     Log backups



       Sun, 1           Mon, 2          Tue, 3          Wed, 4          Thur, 5         Fri, 6



    Full backup       Full backup     Full backup


                                                      DISK_DEV2
                                                                    1

                                                                  DISK_DEV1
                                                                                  2



       SAP AG 2002




  To restore an SAP database from a two-step disk backup, perform the following steps:
      If possible, back up the current transaction log to a new separate disk backup device, using the
      option NO_TRUNCATE.
      Set the SAP database to single-user mode.
      If the disk where the last database backup has been performed has also crashed, provide
      additional disk space for applying the latest Windows backup. Perform a restore as explained in
      the previous slides by using one of the restore methods:
         • Restore from history or
         • Restore from device.
      Restore the latest database backup (1) and apply all subsequent transaction log backups
      including the one performed in the first step (2).
      Check the restore.




© SAP AG                                         ADM520                                            5-31
  Checking the Restore




       SAP AG 2002




  The backup and restore history is stored in the msdb database. To find the history, choose
  Administration → CCMS →DB Administration →Backup Logs then button Restoration History.
  If time permits, run some DBCC consistency checks on the newly restored database.
       Note: These checks are not absolutely required, as a consistency check should have been
       executed each month and should have guaranteed that the backup on the backup media is
       consistent.




© SAP AG                                     ADM520                                              5-32
  Summary



              Now you are able to:

                 Describe different restore strategies
                 Define an appropriate restore strategy based on the
                 backup strategy chosen by your company
                 Perform a restore using SQL Server tools and identify the
                 restore method you should use
                 Check your restore




      SAP AG 2002




© SAP AG                                 ADM520                              5-33
  Further Documentation




                      SAP Installation Guide
                      SQL Server Books Online
                      ADM100 mySAP Technology Administration




      SAP AG 2002




© SAP AG                      ADM520                           5-34
  Unit Actions




                     ?    Exercises




                          Solutions




      SAP AG 2002




© SAP AG                 ADM520       5-35
                                               Database Restore Exercises

                   Unit: Database Restore
                   Topic: Restore Methods

                   At the conclusion of this exercise, you will be able to:
                   • Restore a database under SQL Server
                   • Detect a hardware problem and determine the correct restore method
                     and strategy to be used to bring the database back to its original state


                   The customer detects a hardware problem, which makes it necessary to
                   restore the SAP database.




   1-1     Restore a user database after a media failure
           1-1-1 In this exercise you will restore the test database using the backups you
                 created in the previous exercise. Make sure that you have a valid database
                 and transaction log backup of the test database.
                 Execute stored procedure crash_me in the master database.
                 Afterwards, check whether the test database is available.
           1-1-2 Check the file system to see whether the data files TestData1.mdf,
                 TestData2.ndf, and TestLog1.ldf are located in the directory
                 F:\Mssql7db\MSSQL\Data\.
                 Which step do you have to perform immediately before you start with the
                 database restore?
           1-1-3 Restore the test database by choosing one of the restore methods introduced
                 in Unit 5: Database Restore.
                 Which method do you choose?
                 Which options do you set in the Restore Database window?
           1-1-4 Check the restore operation.




© SAP AG                                    ADM520                                              5-36
                                               Database Restore Solutions

                   Unit: Database Restore
                   Topic: Restore Methods


   1-1     Restore a user database after a media failure
           1-1-1 Close all connections to the test database.
                 Open a Query Analyzer window, and execute crash_me in the master
                 database. The stored procedure returns message:
                 output
                 --------
                 NULL
                 (1 row(s) affected)
           1-1-2 Use the Explorer to check whether the files are available.
                 Normally, you should back up the current transaction log immediately
                 before you start to restore the database. To do this use Transact SQL
                 statement:

                  backup log test to R3DUMP3 with no_truncate

                  The following error occurs:

                  Server: Msg 911, Level 16, State 1, Line 1
                  Could not locate entry in sysdatabases for database
                  'test'. No entry found with that name. Make sure
                  that the name is entered correctly.
                  Server: Msg 3013, Level 16, State 1, Line 1
                  BACKUP LOG is terminating abnormally.

                  When the primary database file is not available anymore, as in this
                  particular case, you cannot back up the current transaction log and you
                  experience some data loss.
           1-1-3 To restore the test database, choose Tools → Restore Database … in the
                 Enterprise Manager. Select the database. The backup history should show
                 one database backup and one succeeding transaction log backup. Leave
                 them all marked.

                   Under Options, select:
                   Prompt before restoring each backup
                   Force restore over existing database
                   Leave database operational. No additional logs can be restored

                   A correct restore operation returns the message:

                   Restore of database ‘test‘ completed successfully.

© SAP AG                                    ADM520                                          5-37
                 Execute transact-SQL statement on database test

                 select * from stores
           1-1-4 Call the Backup Monitor under Tools → CCMS → DB Administration →
                 DB12 – Backup Logs (transaction code DB12), and check the restoration
                 history.
                 Open the Query Analyzer and perform
                 DBCC CHECKDB (test).




© SAP AG                                ADM520                                      5-38
  Regular Maintenance and Error Handling




           1   Introduction

           2   SQL Server Architecture

           3   How the SAP System uses SQL Server

        4      Performance Monitoring and Tuning

        5      Database Backup

        6      Database Restore


       7 Regular Maintenance and
         Error Handling
      SAP AG 2002




© SAP AG                                  ADM520    6-1
  Regular Maintenance and Error Handling



                     Contents
                        Error and Alert handling
                        Space management
                        Data consistency
                        SQL Server parameter maintenance




                     Objectives
                     At the end of this unit, you will be able to:
                        Handle common errors
                        Monitor your SAP System with the help of the CCMS Alert Monitor
                        Monitor database and transaction log growth and extend the
                        database
                        Perform regular administrative tasks


      SAP AG 2002




© SAP AG                                        ADM520                                    6-2
  CCMS Alert Monitor




       SAP AG 2002




  It is vital to regularly monitor the availability, performance, correctness and security of your SAP
  System. The SAP Basis System contains a various set of tools for monitoring and performance
  analysis. These tools include a central monitoring architecture which performs the continual system
  monitoring of all hardware and software components, which is implemented in the CCMS Alert
  Monitor (transaction RZ20). In the SAP Easy Access menu choose Tools -> CCMS -> Monitoring ->
  Alert-Monitor.
  SAP provides a pre-configured collection of monitors which are integrated in the CCMS Alert
  Monitor and which can be used immediately after the installation of your SAP System. Each
  collection combines an area of interest such as the database monitor.
  The use, layout and maintenance of the CCSM Alert Monitor has been introduced in course
  ADM100 myTechnology Administration.




© SAP AG                                      ADM520                                               6-3
  Alerts in the MMC




       SAP AG 2002




  The CCMS Alert Monitor can also be viewed in the Microsoft Management Console on each SAP
  Application Server.




© SAP AG                                  ADM520                                              6-4
  Categories of Information




             Space Management
             Performance
             Backup / Restore
             R/3 Consistency
             Health




       SAP AG 2002




  Information of alerts for SQL Server in the monitoring tree are grouped in the following main
  categories:
     Space Management
     Performance
     Backup / Restore
     R/3 Consistency
     Health
  Alerts for the SQL Server indicate when defined threshold values have been exceeded. Existing
  threshold values reflect SAP recommendations, however they can be adjusted to meet individual
  system requirements.
  The categories Performance and Backup / Restore are not handled in this chapter as they were
  already discussed in earlier chapters.




© SAP AG                                     ADM520                                               6-5
  Space Management

        Autogrow option
        Current sizes
        Free space




       SAP AG 2002




  Status information and alerts related to the disk space on the database server are displayed for the
  <SID> and the tempdb database. For each physical database file the autogrow option, the current
  sizes and the free space is displayed.
  The values are refreshed every 8 hours.
  If you double click on the values, the Database Allocation Monitor (transaction DB02) is displayed.




© SAP AG                                       ADM520                                               6-6
  Monitoring Database Growth




                                   1
                                                                                3

                                      2

                        8   9    10       6 4
                                                                7       5




                                                                                    11

       SAP AG 2002




  Database growth must be monitored regularly using the Database Allocation Monitor (Transaction
  DB02). As the database grows, you may consider adding more disk space.
  The main screen of the Database Allocation Monitor shows the amount of space used by the SAP
  database (1) and its transaction log (2).
  Button DB space history (3) displays a history of the growth of the database. Performance data
  collected during the job COLLECTOR_FOR_PERFORMANCE_MONITOR (report RSCOLL00) is
  stored to table MONI once a day and evaluated for this display.
  The Files section lists the database and log files used. Ensure that the log files and database files
  reside on separate disks (4). For data files, the PRIMARY filegroup is displayed (5). The column
  Growth displays the increment by which the file can grow (6). This increment should be at least 100
  MB and the free space on the disk, displayed in column Free disk (7), should be large enough to hold
  at least two increments. The initial size of the file is displayed under Size(MB) (8). The used portion
  of this file is displayed under Used (MB) (9). A file can grow to an unrestricted or to a limited size.
  This information is displayed under Limit (10).
  The last section displays the number and sizes of tables, indexes and stored procedures. Note that the
  reserved size for tables and indexes is shown.
  If your database grows fast, you might want to find out which table is responsible for the growth.
  Choose button Space Statistics (11) and choose Top n growing tables.




© SAP AG                                        ADM520                                                6-7
  Monitoring Database Growth (2)




                                                                                       DEV

           Space Allocated

            Data:
                        DEVdata1:          3000 MB       2999,99 MB

                        DEVdata2:          3000 MB       2999,99 MB

                        DEVdata3:          3000 MB       2999,99 MB

            Transaction Log space:         4999,99 MB   73,53 MB      4926,46

                                           Total                   Used         Free




       SAP AG 2002




  SQL Server uses a proportional fill strategy across all files within filegroup PRIMARY, writing an
  amount of data proportional to the free space in the file (see Unit 1: SQL Server Architecture). As a
  result all the files in a filegroup tend to be filled at approximately the same time. This strategy
  ensures that I/O operations are distributed more evenly over the database files thus preventing
  hotspots on specific files and partitions.
  To display the allocated size of a file, use the task pad view in the Enterprise Manager.
  When SQL Server is unable to allocate space for a database object, the following message appears:
     Server: Msg 1105, Level 17, State 2, Procedure <procedure>, Line 10 Could not allocate space for
     object '<object>' in database '<SID>' because the 'PRIMARY' filegroup is full.
  When this occurs, the current transaction aborts. SAP System activity cannot continue. You should
  monitor database growth regularly and expand the database early enough to prevent this type of
  error.




© SAP AG                                       ADM520                                               6-8
  Automatic File Growth

                                                                                      DEV

         Space Allocated

         Data:
                      DEVdata1:          3000 MB        19999,99 MB

                      DEVdata2:          3000 MB        19999,99 MB
                                                                              Expand first file by 100 MB
                      DEVdata3:          3000 MB        19999,99 MB

         Transaction Log space:          4999,99 MB    73,53 MB     4926,46

                                         Total                    Used        Free




       SAP AG 2002




  If the database is full, the proportional fill mechanism is no longer used. SQL Server will then
  automatically expand one file at a time, provided that the file is set to grow automatically. If enough
  space is available on the volume used for the SAP data files, the file is expanded by one increment as
  specified in autogrow.
  Insert operations fill the expanded file first, before using another file. When using only one volume,
  this mechanism does not affect performance. If more than one volume is used, insert operations will
  not be distributed equally across the volumes. Any performance improvement that would have been
  achieved by distributing insert operations equally across all volumes is therefore lost.
  Each file can have a maximum size specified. If a maximum size is not specified, the file can
  continue to grow until it has used all available space on the volume.
  Ensure that the free space on the volume is at least twice the increment, by which the file grows. If
  less space is available, you should consider purchasing additional disk space.
  Note: Files should normally be expanded manually, and only grow automatically in unusual
  situations.




© SAP AG                                           ADM520                                               6-9
  Extending the Database


                                         Database must be extended



                                                  Can
                      Add new disk to      Yes volume be             No        Add new
                          volume               extended?                        volume



                                                                           Move one old file
                      Expand files and                <SID>DATA1            to new volume
                        set all files
                        to autogrow
                                                   PRIMARY
                                                                          Expand files on old
                                                              +            and new volume
                      <SID>DATA1                                           and set all files
                        <SID>DATA2
                           <SID>DATA3
                                                      <SID>DATA1
                                                                             to autogrow
                                                        <SID>DATA2


                  PRIMARY
                                                   PRIMARY


       SAP AG 2002




  SAP recommends that you set the database files to grow automatically. Normally, you should
  expand files manually, and allow them to grow automatically only in exceptional situations, for
  example if the database administrator is unable to react before more space is needed. If files are
  expanded automatically in production operation, system performance can be impacted.
  Once disk space is exhausted, you must extend the database.
  If the volume containing the data files can be extended, you can simply add a new disk to the
  volume. Increase the size of all files equally and leave them set to autogrow.
  Alternatively, you can extend the database by adding a new volume. Procedures for doing this are
  explained in the following slides.
  Information about the database files is stored in tables sysfiles and sysfiles1. In addition to your
  backup activities, keep a record of this information about the location of files on paper. You may
  need the information if a failure occurs and a whole system restore is required. Stored procedure
  sp_helpfile returns the physical names and attributes of files associated with the current database.




© SAP AG                                        ADM520                                               6-10
  Expanding a File Using the Enterprise Manager

     SQL Server Enterprise Manager                                                                                  _       x
   Console    Window    Help
      Console Root\Microsoft SQL Server\SQL Server Group\sapprd [Windows NT]\Databases                             _        x
   Action    View   Tools

                                                   7 Items

      Console Root
             Microsoft SQL Servers                                  PRD Properties                                                                        x
                 SQL Server Group
                                                      PRD       master model     msdb
                                                                     General Transaction Log Options Permissions
                       sapprd [Windows NT]
                            Databases                                           Name:     PRD
                               PRD
                               master
                                                    northwind   pubs Database files
                                                                      tempdb
                               model
                               msdb                                   File name            Location                Space Allocated (MB)      File group
                               Northwind                                PRDDATA1           . f:\PRDDATA1\PRD4D... 4000                      PRIMARY
                               pubs                                     PRDDATA2          ... f:\PRDDATA2\PRDD... 4000                      PRIMARY
                               tempdb                                                     ...   f:\ PRDDATA3\PRDD... 4000                   PRIMARY
                                                                        PRDDATA3
                             Data Transformation                                          ...
                             Management
                             Security
                             Support Services
                                                                      File properties:
                                                                           Automatically grow file
                                                                         File growth                           Maximum file size
                                                                            In megabytes:       100              Unrestricted filegrowth
                                                                            By percent:         10               Restrict filegrowth (MB): 2001




                                                                                                       OK          Cancel          Apply
                                                                                                                                   Apply          Help
        SAP AG 2002




  If the volume can be extended, data files on the larger volume must be expanded manually.
  To expand a file, use the Enterprise Manager. There you can specify the increase in space allocated
  to a particular file. If all other files have no free space left, all the insert operations will use the new
  space on the expanded file. You should expand all files equally and use almost all the disk space.
  Keep extra space on the disk in reserve, and ensure that files are set to grow automatically by an
  increment of at least 100 MB. If you don't want to extend the database by the whole new disk space
  at one time, expand one file first and the next file when the next expansion is necessary.
  Use these procedures only when all files are located on one volume.
  Alternatively, you can use the transact-SQL statement ALTER DATABASE to expand a file. See
  Books Online for details.




© SAP AG                                                             ADM520                                                                                   6-11
  Extending the Database on a New Volume




                                                     2
     RAID5                              <SID>DATA1       <SID>DATA2   <SID>DATA3   SAP DB data fiIes

                          PRIMARY


                                                         1



     RAID5                              <SID>DATA3       2                         SAP DB data fiIes

                          PRIMARY




       SAP AG 2002




  SAP recommends that you store the SAP database data files in a RAID5 or RAID0+1 system. A
  RAID system can grow up to a limited size. If a database grows beyond this limit and the RAID
  system cannot be further expanded, a new RAID array must be set up.
  If you use an additional volume to extend your database, one file on the existing volume must be
  moved to the new volume (1) to free up space on the old volume. Expand all files on both volumes
  (2), or create new files if there is only one file per volume. Leave some reserve space to let the files
  grow automatically. SQL Server continues to distribute the insert operations proportionally over all
  new space, and the IO load is equally distributed across the two volumes.




© SAP AG                                             ADM520                                             6-12
  Moving a Database File


                                            msdb
       1                              master                                  ID01S
                                   <SID>


                       SQL Server Query Analyzer - [Query - sappod.master.sa - (untitled)]
                            detail stats.

       2              sp_detach_db ‘PRD’, ‘true’



                       C:\WINNT\System32\cmd.exe
                             detail stats.

                      Microsoft(R) Windows NT (TM)
       3              (C) Copyright 19985-1996 Microsoft Corp.
                      C:\> mv F:\PRDDATA2\PRDDATA2.NDF G:\PRDDATA2\PRDDATA2.NDF



                       SQL Server Query Analyzer - [Query - sappod.master.sa - (untitled)]
                            detail stats.

       4              sp_attach_db PRD’,‘F:\PRDDATA1\PRDDATA1.MDF’,
                      ‘G:\PRDDATA2\PRDDATA2.NDF’


                       SQL Server Query Analyzer - [Query - sappod.master.sa - (untitled)]
                            detail stats.

       5              sp_helpfile


       SAP AG 2002




  A database file can be moved to a different volume in order to distribute I/O operations evenly over
  the existing volume. To move a database file to a new volume:
     Ensure that a recent backup of the database is available and verified. (1)
     Detach the database. (2)
     Move one database file to the new volume. (3)
     Attach the database specifying the new location of the moved file. (4)
     Check the database files. (5)
  Detaching a database removes the database from SQL Server, but leaves the data and transaction log
  files in the database intact. Use stored procedure sp_detach_db to detach a database from the server.
  Specify true as second option in the stored procedure. This prevents UPDATE STATISTICS from
  running on all tables on the database before detaching.
     Note: The database must not be used while it is being detached from the server, that is, there must
     be no connection open to the database.
  Move the file to a new volume and reattach it using stored procedure sp_attach_db. When attaching a
  database, specify the physical location of the primary file and the file being moved, along with the
  new location. Because the primary file contains the information needed to find the other files
  comprising the database, you need only specify the location of the file that has changed location.
  Check the files by running the stored procedure sp_helpfile, which returns the physical names and
  attributes of files associated with the current database.
  See also the SAP Online Help and Books Online on how to move a database file to a new disk.




© SAP AG                                              ADM520                                        6-13
  Adding a File Using the Enterprise Manager

     SQL Server Enterprise Manager                                                                                 _       x
   Console    Window    Help
      Console Root\Microsoft SQL Server\SQL Server Group\sapprd [Windows NT]\Databases                            _        x
   Action    View   Tools

                                                   7 Items

      Console Root
             Microsoft SQL Servers                                  PRD Properties                                                                         x
                 SQL Server Group
                                                      PRD       master model     msdb
                                                                     General Transaction Log Options Permissions
                       sapprd [Windows NT]
                            Databases                                           Name:     PRD
                               PRD
                               master
                                                    northwind   pubs Database files
                                                                      tempdb
                               model
                               msdb                                    File name          Location                     Space Allocated (MB)   File group
                               Northwind                              PRDDATA1             .    f:\PRDDATA1\PRDD... 2000                      PRIMARY
                               pubs                                   PRDDATA2            ...   f:\PRDDATA2\PRDD... 2000                      PRIMARY
                               tempdb                                 PRDDATA3            ...   f:\PRDDATA3\PRDD... 2000                      PRIMARY
                             Data Transformation                      PRDDATA4            ...   f:\PRDDATA4\PRDD… 2000                        PRIMARY
                             Management
                             Security
                             Support Services
                                                                      File properties:
                                                                           Automatically grow file
                                                                         File growth                          Maximum file size
                                                                            In megabytes:       100              Unrestricted filegrowth
                                                                            By percent:         10               Restrict filegrowth (MB): 2001




                                                                                                       OK         Cancel           Apply
                                                                                                                                   Apply          Help
         SAP AG 2002




  You can also use additional data and transaction log files to extend a database. When a file is added,
  the file is immediately available for use by the database.
  Use the Enterprise Manager to add a new file. Specify the size of the file, the maximum size to
  which the file should grow, the increment by which the file is to grow each time (default = 10 %),
  and the filegroup to which the file belongs. In the SAP environment, filegroup PRIMARY is used.
  When you add a new file, use the SAP naming conventions.
  When adding a new data file, keep extra space on the disk in reserve, and set the files to grow
  automatically in increments of at least 100 MB.
  You can also use the transact-SQL statement ALTER DATABASE. See Books Online on more
  details.




© SAP AG                                                              ADM520                                                                                   6-14
  Monitoring Transaction Log Growth




                                        1




                                            2                       3




       SAP AG 2002




  The transaction log grows if data in the database is changed. As the transaction log grows, the first
  log file fills, then the second, and so on, using a fill-and-go strategy.
  In the SAP environment, there is usually only one log file. The size of this log file must be
  determined carefully. Watch the growth of the transaction log using the Database Allocation Monitor
  (Transaction DB02). Observe how the transaction log fills during exceptional workload, for example
  during Batch Input or an upgrade. You can monitor this at ST04 → Detailed Analysis Menu →
  Performance History → folder DB space. These values are refreshed every 2 hours. Alternatively
  choose DB02 → DB space history to display one value per day.
  Use command DBCC SQLPERF(logspace) to display the amount of space used by the transaction
  log.
  The Database Allocation Monitor displays the following:
     File name
     Location of file
     Space allocated for the transaction log files
     Log size (1)
     Log free size (1)
  If automatic growth is configured for this log file, column Growth (2) displays the increment by
  which the transaction log can grow. The increment should be 50% of the file size, and the free space
  on the disk should be enough to handle at least one increment.
  Column Free disk (3) displays the amount of free space on the disk.




© SAP AG                                        ADM520                                             6-15
  Transaction Log Full




                                                         SQL
                                                         Server

                                                                                                Msg 9002,
                                                                          1                  The log file for
                                                                                                database
                                                                                              ‘<SID>' is full
                                                   <SID>                                                        2
                      begin1 update begin2     insert   commit2 chkp      insert   begin3
                      LSN0     LSN1   LSN2     LSN3     LSN4     LSN5     LSN6     LSN7


                      delete   dump   commit1 chkp      insert   insert   delete   delete        Backup             RL02A
                      LSN8     LSN9   LSN10    LSN11 LSN12       LSN13 LSN14 LSN15


                      insert   update begin4   rollback chkp     insert   delete   drop
                                                                                                                3
                      LSN16 LSN17 LSN18        LSN19 LSN20       LSN21 LSN22 LSN23


                      update begin5 commit3 insert      delete   chkp     insert   commit4
                      LSN24 LSN25 LSN26        LSN27 LSN28       LSN29 LSN30 LSN31




       SAP AG 2002




  If the transaction log is full, SQL Server writes the following error message to the error log file and
  interrupts the current transaction:
     Server: Msg 9002, Level 17, State 2, Procedure <procedure>, Line 12 The log file for database
     ‘<SID>' is full. Back up the transaction log for the database to free up some log space
  A similar message is written to the SAP System log.
  If you see this error message, back up the transaction log immediately using the SAP Planning
  Calendar (Transaction DB13). Use the same tape for this extra backup as used for all regularly
  planned transaction log backups. Append the backup to the existing backups on the tape. Do not
  specify the option Initialize. Remember that a transaction log backup truncates the inactive portion of
  the log and makes space for new transaction logging. See also Unit 4: Database Backup.
  Do not shut down SQL Server or the SAP System, if the transaction log is full. If you shut down
  SQL Server, log entries will be lost.
  With a good backup strategy, errors like this should not occur. However, the transaction log fills up
  quickly during periods of an exceptionally high workload, for example during Batch Input or an
  upgrade. Ensure that at least 1.5 GB space is available in the transaction log.




© SAP AG                                                                           ADM520                                   6-16
  R/3 Consistency


         Tables, Views and Indexes missing in DB




       SAP AG 2002




  In this category objects that are defined in the SAP ABAP/4 dictionary but not on the database are
  displayed.
  The values are refreshed once a day.




© SAP AG                                       ADM520                                              6-17
  Database Allocation Monitor: ABAP/4 Dictionary




       SAP AG 2002




  Objects of the ABAP/4 Dictionary which were not found in the database are displayed in the
  Database Allocation Monitor after choosing button Checks → Database ↔ ABAP/4 Dictionary.
  Indexes that exist in the SAP ABAP/4 dictionary but not on the database can be created by
  positioning the cursor on the index and click button Create on DB.




© SAP AG                                    ADM520                                             6-18
  Health

      SQL Server configuration parameters
      SQL Server and Windows Build Numbers
      Errors in the SQL Server Error Log




       SAP AG 2002




  The health of your SAP System can be judged by monitoring the effectiveness of parameter settings
  and watching out for common errors.
  The most important alerts to be checked regularly are the correct settings of the SQL Server
  configuration parameters, the correct versions of SQL Server build numbers and errors in the SQL
  Server Error Log.




© SAP AG                                     ADM520                                             6-19
  SQL Server Configuration



                                                  SQL Server Query Analyzer - [Query - sappod.master.sa -]
                                                        detail stats.


                                                name            minimum     maximum    config_value run_value
                                                ------------- -------- ----------- ------------ ---------
                                                affinity mask -2147483648 2147483647        0            0
                                                allow updates 0                1            1            1
                                                awe enabled     0              1            0            0
                                                c2 audit mode 0                1            0            0
                                                cost threshold
                                                for parallelism 0              32767        5            5
                                                cursor threshold -1            2147483647   -1           -1
                                                default full-
                                                text language       0          2147483647   1033         1033
                                                default language 0             9999         0            0
                                                fill factor (%)      0         100          0            0
                                                index create
                                                memory (KB)          704       2147483647   0            0
                                                Lightweight
                                                pooling              0         1            0            0
                                                locks                5000      2147483647   0            0




       SAP AG 2002




  The CCMS Alert Monitor shows a red alert when SQL Server Parameters have been set, but are not
  active.
  To change the configuration parameters select the server, than select Action → Properties to display
  the Properties window.
  A list of SQL Server configuration parameters can also be retrieved by running sp_configure in the
  Query Analyzer. Columns minimum and maximum display the acceptable range of values. Column
  config_value contains the active value. Column run_value contains the value that is currently set.
  After changing a configuration parameter using sp_configure, use command reconfigure with
  override to update the currently configured value of a configuration option. Because some
  configuration options require a server stop and restart to update the currently running value,
  reconfigure does not always update the currently running value. The option with override disables
  the configuration value checking.
  For dynamic configuration parameters, changes take effect immediately. For static configuration
  parameters, changes take effect after you shut down and restart SQL Server. Before restarting SQL
  Server, shut down the SAP System.




© SAP AG                                       ADM520                                                           6-20
  SQL Server does not Start



                                          Error in parameter
                                          Error in parameter
                                          configuration
                                          configuration
              SQL
              Server                                                            Books Online

                                          initconfig: Error 2
                                           initconfig: Error 2
                                          (The system cannot
                                           (The system cannot
                                          find the file
                                           find the file
                                          specified.)
                                           specified.)
                                                                               SAP Notes

                                           ...
                                                                            Open a problem
                                                                               message
       Check for
         error
       messages




       SAP AG 2002




  If SQL Server does not start, check the SQL Server error log at file system level. To get a detailed
  description of the error message, use Books Online or the SAP Notes.
  Because SQL Sever sets its resources dynamically, an incorrect setting of SQL Server configuration
  parameters sometimes causes SQL Server not to start. To start SQL Server with a minimal
  configuration, use the option -f in the sqlservr command. Reconfigure the configuration options
  using the sp_configure system stored procedure or the Enterprise Manager.
  If the master database is not available, for example due to a corrupt master file, SQL Server cannot
  start because it is unable to read the configuration information. An error starting with Initconfig
  appears in the SQL Server error log.
  If the problem cannot be solved, create a problem message immediately. Enter the SQL Server error
  message.




© SAP AG                                         ADM520                                           6-21
  Applying Service Packs

                                                            README.txt
                                                            README.txt
                                                     2
                          1
                                                    follow instructions in
                         download the                   the readme file
                                                                                    3    SAP Application
                      current Service Pack
                                                                                         Server

                                                                                 Database
                                                                                 Server
  6
                                                                                       SQL
                                  master     msdb                                      Server

                                                                                        stop the SAP System
                perform a backup
                                                                                           and SQL Server
             and check the installation


                                                                             4
                                 5
                                                                                            master   msdb
                                  install the Service
                                Pack on the Database
                                 Server and all SAP                            perform a backup if
                                 Application Servers                         no current backup exists



       SAP AG 2002




  SQL Server and Windows build data is displayed in the first two nodes under Startup parameters in
  the CCMS Alert Monitor.
  The current service pack of SQL Server is always released by SAP. SAP strongly recommends that
  the current service pack is installed immediately when it is available. Explicit exceptions are
  mentioned in SAP Notes.
  The exact SQL Server version appears in the first line of the SQL error log. The version can also be
  found out using the SQL command select @@version.
  The installation of a service pack is described in SAP note 417089:
    Download the current service pack from the Microsoft Web Server or a CD (1).
    A service pack always contains a README file from Microsoft. This file contains detailed
    installation instructions (2).
    Stop the SAP System and SQL Server (3).
    SAP recommends that you perform an Windows backup of all server files after each configuration
    change. The easiest way of running a Windows backup is to reboot it from a second Windows
    installed in parallel (4). Before installing the service pack, make sure that a current backup of the
    master and msdb model databases exist. Installing a service pack modifies the master and msdb
    databases, making them incompatible with older versions of SQL Server. These backups are
    required if you decide to reinstall SQL Server without the service pack.
    Install the service pack by opening the SETUP.BAT file (5). Install the service pack on the SAP
    application server in the same way as it is installed on the database server.
    Perform a Windows backup, back up the master and msdb databases and check the installation (6).




© SAP AG                                            ADM520                                                  6-22
  Error Handling


                                                           SQL Server error log in
                                                           SQL Server error log in
                                  SAP System Log           path X:\Microsoft SQL
                                                            path X:\Microsoft SQL
                                                             Server\MSSQL\LOG
                                                             Server\MSSQL\LOG




                                                          SQL Server Agent error
                                                           SQL Server Agent error
                                                          log in path X:\Microsoft
                                                           log in path X:\Microsoft
                                                          SQL Server\MSSQL\LOG
                                                          SQL Server\MSSQL\LOG




                                                          dev-trace files



              Check for
           error messages
                  in:                                    ABAP runtime
                                                         errors
                                          ! i
                                           ?
                                     Event Viewer
       SAP AG 2002




  When a problem occurs, begin by checking the following:
   SAP System log
   Use Transaction SM21.
   SQL Server error log
   Use the Database Monitor (Transaction ST04) or the Enterprise Manager. Alternatively, you can
   directly edit the log files located in directory X:\Microsoft SQL Server\MSSQL\LOG.
   SQL Server Agent error log
   Use the Database Monitor (Transaction ST04) or the Enterprise Manager. Alternatively, you can
   directly edit the log files located in directory X:\Microsoft SQL Server\MSSQL\LOG.
   Developer trace files (Transaction ST11) display all messages written by the SAP work processes.
   To limit the messages displayed on the database or the database interface, choose Display
   Components.
   ABAP runtime errors
   Use transaction ST22.
   Event Viewer
   Choose Start → Settings → Control Panel → Administrative Tools → Event Viewer.
  Search for related SAP Notes in SAPNet.




© SAP AG                                        ADM520                                          6-23
  Some Errors that may Occur


               You may encounter the following SQL Server errors:

                    Database file full (Error

                    Transaction log full (Error

                                                                           8928,
                    Consistency errors (Errors 601, 605, 644, 823, 2511, 2513,
                    8944, 8952, 8976,

                    Resource Problems (Error 845)

                    Performance Problems (Error 8645,

                    Deadlock (Error 1205,

                    Locking problems (Error 601,

                    Network errors (Error 11,

         SAP AG 2002




  Errors 1105 and 9002 indicate that either a data file or the log file has run out of space. What to do
  in such situations, has been discussed in previous slides.
  SQL Server as a Relational Database Management System is responsible for the physical
  consistency of the database. There is a physical inconsistency (database corruption) if there are
  errors in internal structures (page pointer, allocation ...) in the database. You can suspect this if there
  are frequent short dumps in the SAP System, in particular if the following SQL Server error
  messages appear: SQL Error 601, 605, 644, 823, 2511, 8928, 8944, 8952, 8976. See SAP note
  142731 for details.
  See SAP note 429561 for details on SQL Server error 845.
  When SQL Server is low on memory a timeout may occur while waiting for memory resources to
  execute a query. In this case SQL Server error 8645 is shown in the SQL Server error log and an
  ABAP/4 short dump occurs. See SAP note 206694 for details.
  SQL Server error 1205 indicates a deadlocks on a certain table. Known deadlocks are listed
  according to the table affected in SAP note 565710.
  SQL Server error 601 can have two different causes: either database inconsistencies or a conflict
  between two database processes that were executed at the same time. See SAP note 565708.
  Short Dumps of the type DBIF_RSQL_SQL_ERROR, as evidenced in ST22, may be observed in a
  badly configured SQL Server system. The details of the short dumps will indicate SQL error 11
  and/or SQL error 0. See SAP note 392892 for details.




© SAP AG                                         ADM520                                                  6-24
  Database Consistency Check

   Mon 12     Tue 13     Wed 14      Thu 15    Fri 16     Sat 17   Sun 18
   success    success    success    success      0:00


   Mon 19     Tue 20     Wed 21     Thu 22     Fri 23
   success    success    success    success    2:00
                                               error

   Mon 26      Tue 27    Wed 28     Thu 29      Fri 30
   0:00 DB    0:00 DB    0:00 DB    0:00 DB    0:00 DB
   9:00 Log   9:00 Log   9:00 Log   9:00 Log   9:00 Log

   Mon 02     Tue 03     Wed 04     Thu 05      Fri 06
   0:00 DB    0:00 DB    0:00 DB    0:00 DB    0:00 DB
   9:00 Log   9:00 Log   9:00 Log   9:00 Log   9:00 Log




       SQL Server Query Analyzer - [Query - sappod.master.sa - (untitled)]
            detail stats.


     DBCC CHECKDB (PRD) WITH NO_INFOMSGS



        SAP AG 2002




  Hardware problems, such as a defective hard disk controller, may cause inconsistencies in the
  database. These inconsistencies may only be detected when a table or index affected by the
  inconsistency is first accessed, or during a consistency check. To prevent problem escalation and
  possible loss of data, you must identify errors early.
  The Database Consistency Checker (DBCC) is a system administration tool that verifies pointers
  internal to a database and its structure. DBCC commands typically lock user tables, indexes, and
  system tables whenever they are run. In addition, they are very I/O intensive and should not be run
  during normal operation.
  Run periodic checks to ensure the physical consistency of your data. Use the SAP Planning Calendar
  to schedule a database consistency check at least once a month during a period of low workload, for
  example on the weekend. This job executes transact-SQL statement DBCC CHECKDB WITH
  NO_INFOMSGS on the SAP database.
  Check the run status of the consistency checks using the SAP Planning Calendar. If the job is
  highlighted in red, check for further errors in the SQL Server error log. A file which reports all
  errors found is written to X:\Program Files\Microsoft SQL
  Server\MSSQL\CCMS_CHECK_DB_HIST_<year>.txt.
  If the run status is green, no inconsistencies were found. The SQL Server error log displays
  messages:
    DBCC CHECKDB (<SID>) started at ... with Logfile: X:\ Program Files\Microsoft SQL Server\
    MSSQL\ CCMS_CHECK_DB_HIST<year>.TXT.
    DBCC CHECKDB (<SID>) executed by <user> found 0 errors and repaired 0 errors.




© SAP AG                                                            ADM520                       6-25
  Database Allocation Monitor: Consistency Check




      SAP AG 2002




  To perform a database consistency check, you can also use the Database Allocation Monitor
  (Transaction DB02). Choose button DBCC checkdb. Do not run a consistency check on the database
  while production work is carried out in the system.




© SAP AG                                   ADM520                                           6-26
  Database Allocation Monitor: Consistency Check
  on a Table




       SAP AG 2002




  The Database Allocation Monitor (Transaction DB02) also enables you to perform a consistency
  check on a single table. Choose button Detailed Analysis and specify the table to be checked. In the
  following screen, choose button Check Table.
  A DBCC CHECKTABLE WITH NO_INFOMSGS is executed on the database for the specified table.
  This checks the integrity of the data, index, text, ntext, and image pages for the specified table.




© SAP AG                                      ADM520                                              6-27
  Database Inconsistencies



                                           DBCC CHECKDB(PRD)
                                           DBCC CHECKDB(PRD)
                                           executed by <user>
                                           executed by <user>
                                           found X errors and
                                           found X errors and
                                           repaired 0 errors
                                           repaired 0 errors
                                           ...
                                           ...                                   Books Online




                                           Job 'SAP CCMS Check
                                           Job 'SAP CCMS Check                  SAP Notes
                                           Database PRD
                                           Database PRD
                                           [20021213174935]' ::
                                           [20021213174935]'
                                           Step 1, 'Step1' :: Began
                                           Step 1, 'Step1' Began
                                           Executing 12/13/02
                                           Executing 12/13/02
                                           6:49:31 PM
                                           6:49:31 PM                         Open a problem
                                           ...
                                           ...
                                                                                 message
              Check for
           error messages

                                           Error log messages


       SAP AG 2002




  If an inconsistency has been detected during the DBCC run, you must take immediate action to
  prevent further error escalation and possible data loss. Check the output of the DBCC check
  immediately after each run.
  If an inconsistency is detected, the job scheduled with the SAP Planning Calendar is highlighted in
  red. The SQL Server error log shows the following message:
    DBCC CHECKDB(<SID>) executed by <user> found X errors and repaired 0 errors
  Log file X:\ Program Files\Microsoft SQL Server\MSSQL\CCMS_CHECK_DB_HIST<year>.TXT
  reports all inconsistencies found.
  If an error occurs, create a problem message immediately. Enter the full error text found in the log
  files in the problem description. In most cases, you can correct the inconsistency without data loss.
  Depending on the type and location, you may be able to correct the error by removing an index on a
  table, or by restoring the database.
  For a detailed description of errors, see Books Online or SAP Notes.




© SAP AG                                       ADM520                                               6-28
  No Connection to SQL Server




                                           Database <SID>
              SQL                          cannot be opened
              Server                                                          Books Online




                                           Error 1105: Could                  SAP Notes
                                           not allocate space
                                           ...



                                                                           Open a problem
                                                                              message
                                          Error 8651: Could not
              Check for                   perform the requested
           error messages                 operation because ...



                                         ...
       SAP AG 2002




  If the SAP System cannot connect to SQL Server, the SAP Service Manager displays a grayed-out
  dispatcher symbol. Check the developer trace files located in directory
  X:\usr\sap\<SID>\DVEBMGS<instanceno.>\work for an error description, as follows:
     A suspect SAP database. In this case error Error 945: Database <SID> cannot be
     opened because some of the files could not be activated appears. Similar errors occur in
     the SQL Server error log and the Event Log. See Unit 5: Database Restore.
     A suspect SAP database. Error 1105: Could not allocate space for object <object> in
     database <SID> because the PRIMARY filegroup is full occurs. The status column in
     table sysdatabases is set to suspect if SQL Server is unable to complete recovery on a
     database because the disk drive no longer has free space. Execute stored procedure
     sp_resetstatus <SID>, provide additional disk space and restart SQL Server. See SAP
     note 81692 for details.
     Not enough memory. Error 8651: Could not perform the requested operation because
     the minimum query memory is not available occurs. Change SQL server configuration
     parameters as explained in Unit 3: Performance Monitoring and Tuning.
  If the problem cannot be solved, create a problem message immediately. Enter the error message
  shown in the developer trace file. Books Online or the SAP Notes may contain a detailed description
  of the error. SAP Note 98678 discusses connection problems.




© SAP AG                                       ADM520                                            6-29
  Summary of this Unit



               Now you are able to:

                     Check for errors and solve common problems
                     Monitor the growth of the database and the transaction log
                     Extend the database:
                        Add a disk to an existing volume
                        Add a new volume
                        Expand data files
                     Perform regular administrative tasks




      SAP AG 2002




© SAP AG                                    ADM520                                6-30
  Further Documentation




                          ADM100 myTechnology
                          Administration
                          SQL Server Books Online
                          SAP Notes
                          Best Practices CCMS Monitoring for
                          mySAP




      SAP AG 2002




© SAP AG                    ADM520                             6-31
  Unit Actions




                     ?    Exercises




                          Solutions




      SAP AG 2002




© SAP AG                 ADM520       6-32
  Regular Maintenance and Error Handling Exercises

                   Unit: Regular Maintenance and Error Handling
                   Topic: Space Management / Database Extension

                   At the conclusion of this exercise, you will be able to:
                   • Monitor the growth of the database and transaction log
                   • Handle a full transaction log
                   • Extent a database on a new volume
                   The customer detects that the transaction log of his SAP database fills up
                   rapidly and monitors its growth. As soon as the transaction log is full, he
                   has to resolve the situation.
                   The SAP database runs out of space on the volume it resides on. The
                   customer has to extent the database on a new volume.

   1-1     Transaction log full
           1-1-1 Open a Query Analyzer window and select the test database. Execute the
                 stored procedure fill_log. This stored procedure will fill the transaction
                 log by adding rows to the stores table.
                 Monitor the growth of the transaction log.
                 What error message is displayed when the transaction log is full?
                 Where else can you see the error message?
           1-1-2 Why did the transaction log not grow dynamically?
                 Check the properties of the transaction log and its file.
           1-1-3 What can be done to resolve the situation?
                 Are the transaction log file properties set up correctly? If not, change them.
           1-1-4 Display the log used space again, and check the correctness of the
                 transaction log backup.

   1-2     Database full, and extending the database on a new volume
           1-2-1 Set the recovery model to simple on the test database.
                 Open a Query Analyzer window and select the test database. Enter the
                 command fill_db and execute it. This stored procedure will fill the
                 database by adding rows into database table stores.
                 Monitor the growth of the database. Execute fill_db again and check the
                 growth. What do you observe?
           1-2-2 What error message is displayed when the database is full?
           1-2-3 Extend the test database on a new volume by 4 MB. Choose C:\temp as the
                 new location.
                 Which steps do you have to perform?
                 How can you extend the database and equally distribute the load across the
                 two volumes?
© SAP AG                                    ADM520                                           6-33
  Regular Maintenance and Error Handling Solutions

                   Unit: Regular Maintenance and Error Handling
                   Topic: Space Management / Database Extension


   1-1     Transaction log full
           1-1-1 Monitor the growth of the transaction log of the test database during the
                 execution of stored procedure fill_log.
                 In the Enterprise Manager expand the server and the Databases folder, then
                 mark database test. Select the Tools → Taskpad to display the space usage
                 of database test. Selecting the refresh button or the F5 key refreshes the
                 display.

                  The Query Analyzer displays the following message:

                  Server: Msg 9002, Level 17, State 2, Procedure fill_log, Line 12
                  The log file for database 'test' is full. Back up the transaction log for the
                  database to free up some log space.

                  Use command DBCC SQLPERF(LOGSPACE) to display the log used
                  space. It should display a rate of about 90%.

                  Error message 9002 is displayed in the SQL Server error log and in the
                  Event Viewer.
           1-1-2 The properties of the test database can be displayed with the Enterprise
                 Manager. Select database test, right mouse-click and choose Properties. In
                 the Transaction Log Tab, the log file is displayed, with its allocated size and
                 the file properties. The transaction log file should be set to grow
                 automatically. But the file growth is restricted.
                 Alternatively, you can use the SAP Database Allocation Monitor under
                 Tools → Administration → Monitor → Performance → Database →
                 DB02 Table/Indexes (transaction DB02) to display the properties of all the
                 databases on the database server. Choose Other DB → DB list and select
                 the test database. The column Limit displays the maximum growth.
           1-1-3 Back up the transaction log of the test database. Use either the Enterprise
                 Manager or the Query Analyzer. Make sure to append the backup to the
                 existing backups on backup medium R3DUMP3. See the exercises in Unit4:
                 Database Backup.
                 The transaction log file of the SAP database should grow by at least 100
                 MB and there should be enough space left on the disk, on which it resides.
                 Let the transaction log file of the test database grow to at least 3 MB.
                 Change the properties in the Enterprise Manager.
           1-1-4 Use command DBCC SQLPERF(LOGSPACE) to display the log used
                 space.

© SAP AG                                    ADM520                                                6-34
   1-2     Database full, and extending the database on a new volume
           1-2-1 Go to the Properties window of the test database and set the recovery model
                 to simple. Choose OK. The transaction log of the test database will now be
                 truncated every time a checkpoint occurs.
                 Monitor the growth of database test during the execution of stored
                 procedure fill_db. In the Enterprise Manager, expand the server and the
                 Databases folder, then mark database test. Choose View → taskpad to
                 display the space usage of database test. Selecting the refresh button or the
                 F5 key refreshes the display.
                 After a few executions of fill_db you see that the first data file is expanded
                 automatically. All insert operations will now take place in this first data file.
                 As soon as this data file is full the second file is expanded and insert
                 operations take place in this file. The proportional fill strategy is no longer
                 used.
           1-2-2 When the database is full, the Query Analyzer displays the following
                 message:

                  Server: Msg 1105, Level 17, State 2, Procedure fill_db, Line 15
                  Could not allocate space for object 'testtable' in database 'test' because the
                  'PRIMARY' filegroup is full.

                  The SQL Server error log shows the same error message. Transaction DB02
                  shows the sizes of the data files and the used sizes of 4MB each.
           1-2-3 The goal of extending the database is to distribute future insert operations
                 equally across the two volumes. Therefore the database has to be extended
                 on both volumes. Perform the following steps:

                  Close all connections to database test and detach the test database from the
                  server, using the transact-SQL command
                  sp_detach_db ‘test’, ‘true’

                  Move database data file F:\mssql7db\MSSQL\data\TestData2.ndf to
                  C:\temp.

                  Re-attach database test using transact-SQL command
                  sp_attach_db ‘test’,
                  ‘F:\mssql7db\MSSQL\\data\TestData1.mdf’,
                  ‘C:\temp\TestData2.ndf’

                  Execute sp_helpfile in the test database to check the result.

                  Extend both data files by 2 MB each. Use the Enterprise Manager or
                  transact-SQL commands. Make sure that you change the maximum file size
                  the data files are allowed to grow as well.

                  Execute the stored procedure fill_db again to observe how the database
                  allocated new space proportionally on both files.




© SAP AG                                    ADM520                                            6-35

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:55
posted:4/6/2012
language:German
pages:223