Microsoft SQL Server Administration for SAP SQL Server with SAP

1
Microsoft SQL Server Administration for SAPSQL
Server with SAP
2
Overview

• SQL Server Architecture
• SQL Server with SAP
• Performance Monitoring and Tuning
• Administration and Troubleshooting
• Database Backup and Restore

3
R3 Architecture
Presentation server
SAPGUI
SAPGUI
SAPGUI
SAPGUI
R/3 instance
R/3 instance
R/3 Dispatcher
Application server
R/3 Work process
R/3 Work process
Database server
Databases
4
R/3 Database Interface
R/3 work process
Database Access Agent
Application server
Database server
Databases
5
Database Connections
R/3 Work process
R/3 Work process
Application server
OLEDB
TCP/IP sockets
TCP/IP sockets
ODS (Open data
services)
Database server
SQL Server
6
Database Connections to R/3 Work Processes
R/3 Work process
R/3 Work process
0 Consistent transactions 1 DDL
transactions 2 Select single 3..N Dirty read
selects
Application server
dbs/oledb/add_procs X or rsdb/mssql/add_procs
X -gt X3 connections
01234567
01234567
READ UNCOMMITTED
Database server
READ COMMITTED
7
R3 Process Monitor
8
R3 Process Monitor
9
SQL Enterprise Manager Process Info
10
Data Select from ABAP Table Buffers
select from T100 where ... endselect.
R/3 work process
Application server
Shared buffers
11
Data Select from ABAP
select from T100 where ... endselect.
R/3 work process
Application server
Shared buffers
Database Access Agent
DBSL IF
create Y7R6GH78J676ABC0000T100 exec
Y7R6GH78J676ABC0000T100
12
Stored Procedure Name Cache
R/3 Work Process
Database Access Agent
YR...
DBSL IF
In name cache
in cache
no
yes
Application server
create Y7R6 exec Y7R6...
exec Y7R6...
exec YR...
13
Stored Procedure Names
Y7DRESDENPRD0047000027100934 Y7DRESDENPRD00477
00528106935 Y7DRESDENPRD0047000027100934 Y7DRE
SDENPRD0047700528106935 Y7DRESDENPRD004700002710
0934 Y7PARISPRD0047700765528107036 Y7PARISPRD0
047000027580100939 Y7PARISPRD0047700528106000936
...
tempdb
Y7R61000068BKI3757MONI Y7R200000015BKI3757MONI Y7A
0000051787LG0732SDB1FMSS Y7A000000B9B822D0010SAPLS
TAM Y7C0000094197624847DBSYFMSS Y7R400000015BK9987
57MARA Y7A0000015BKI89H930489MARA Y7A000000016BKI8
9H93347MARD ...
ltSIDgt
14
Permanent Stored Procedures
15
Temporary Stored Procedures
16
Windows NT Authentication
Presentation server
Application server
Database server
ltDOMAINgt\ SAP_ltSIDgt_GlobalAdmin
ltSIDgt
sapusr
MSSQL server
R/3
pubs

NT user SAP user Login Id Server
role Example Joe SAPUSR ltDOMAINgt\ System
SAP_ltSIDgt_ Administrator, GlobalAdmin def
ault DB ltSIDgt Authori- Logon at Access to Logon
at Access to zation workstation SAP objects SQL
Server DB objects
17
R/3 Database Files
R/3 Database
ltSIDgt
ltSIDgtDATA1
ltSIDgtDATA2
ltSIDgtDATA3
ltSIDgtLOG1
ltSIDgtDATAn
ltSIDgtLOGm
Files
...
Filegroup
PRIMARY
File System
ltdrivegt\ltSIDgtDATA1\ltSIDgtDATA1.mdf
ltdrivegt\ltSIDgtLOG1\ltSIDgtLOG1.ldf
ltdrivegt\ltSIDgtDATA2\ltSIDgtDATA2.ndf
ltdrivegt\ltSIDgtLOGm\ltSIDgtLOGm.ldf
...
ltdrivegt\ltSIDgtDATA3\ltSIDgtDATA3.ndf
ltdrivegt\ltSIDgtDATAn\ltSIDgtDATAn.ndf
...
18
R3 Database Properties
19
R3 Database Properties
20
R3 Database Properties
21
R3 Database Properties
22
R3 Database Properties
23
R3 Table List
24
ABAP Dictionary
R/3 work process
Database Access Agent
R/3 maintains definitions of the database objects
it uses in its integrated ABAP Dictionary. The
R/3 definitions are mapped to the SQL Server
definitions.
Application server
ABAP Dictionary object ltTABLE gt
1 2

Key Data
Database server
ltSIDgt
SQL Server object ltTABLE gt
1 2

Key Data
25
Transparent Tables
ABAP Dictionary
Table definition in ABAP Dictionary
1 2
TABA

Key Data
SQL Server database ltSIDgt
1 2 1 3 1 4
TABA0 (Unique clustered index)
Table and index in SQL Server
TABA (Table)
26
Logical Tables Pool Tables
TABA
Tablepool

Key Data
TABB
DATALN
TABNAME
VARDATA
VARKEY

Key Data
Logical tables in ABAP Dictionary
Physical table on database
27
Logical Tables Cluster Tables
TABA
1 2
Tablecluster

Key Data
1 2
TABB

1 2
KEY
PAGELG
TIMESTMP

PAGENO
VARDATA
Key Data
Logical tables in ABAP Dictionary
Physical table on database
28
Lab

• Identify the SPID for the first Batch Processes
• Determine the Stored Procedures related to report
  ??
• Find the ABAP Dictionary definition for the
  following tables
• MARA, TCOLL and DOKTL
• Check the database for their existence
• Kill the SPID for the first Batch Processes

29
Summary

• Write Summary for this module