- Application Terms
- OLTP Application Examples
- DSS Application Examples
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
OLTP Application Examples
The following sections describe what the major Enterprise Resource Planning (ERP) vendor SAP AG has implemented using SQL Server for its database layer.
OLTP ERP Example
SAP business solutions are composed of a comprehensive range of products that empower an enterprise with a flexible, end-to-end solution. A critical challenge in implementing an SAP solution is the selection of a data platform to deliver the advanced features and capabilities needed to support the most demanding workloads. The Microsoft SQL Server database software (either SQL Server 2008 or SQL Server 2005) is the relational database management system (RDBMS) of choice for deploying secure, reliable, highly available, high-performing, and scalable SAP installations. Plus, SQL Server high-availability features can minimize downtime for any SAP implementation.
The company's flagship applications are the NetWeaver-based SAP ERP/Business Suites and SAP R/3 industry solutions. Since 1993, SAP and Microsoft have been working together to provide a deeply integrated Microsoft platform with SAP solutions. Microsoft is currently the most selected platform for R/3 and SAP application deployments: more than 56,000 SAP application installations run on Windows, which is more than all other platforms combined. Of these, more than 23,000 SAP application installations worldwide are running with SQL Server as the RDBMS. In fact, this $11.5 billion company uses its own software for its internal ERP purposes completely deployed on the Microsoft SQL Server platform.
As you can see in Figure 3.1, SAP's ERP footprint is a three-tier architecture consisting of a variety of client types, a horizontally scalable application server tier, and a highly available, high-performance database tier.
)
Figure 3.1 SAP multitier architecture with SQL Server as the database layer.
The SAP multitiered client/server architecture is composed of three levels:
- Client/Presentation Tier—This tier supports SAP graphic user interfaces (GUIs) such as SAP GUI, SAP WebGUI, and other products that connect to the SAP NetWeaver Application Server using one of the supported interfaces. The client tier also includes applications to access SAP using Web Services. For example, applications including smart clients and Microsoft Office applications can integrate SAP data, such as when the Microsoft Excel spreadsheet software is used with Web Services. Applications that use the SAP RFC interface are also part of the presentation tier. Especially in the Microsoft world, connecting to the application tier via RFC became common with the SAP .NET connector, which offers a bandwidth of .NET classes and methods that are mapped to SAP Business Application Programming Interfaces (BAPIs) that are accessible via RFC.
- Application Tier—This tier can contain multiple SAP NetWeaver Application Server instances. However, it needs to contain at least one application instance. If multiple instances are used in one system, each application instance is typically run on separate server hardware or virtual machines. The application tier and database tier can run on the same server hardware on small-scale systems and in some very large hardware configurations. The complete processing of the business transactions and workflow is handled on the application side. No business logic is pushed down for execution into the database layer. The database tier is used for data storage only. Technically, an SAP application instance is a collection of processes called work processes in SAP terminology. Based on a configuration profile for each individual instance, these work processes fulfill different tasks and requests. To share user contexts and user data, the work processes of one instance share larger areas of memory. The business logic itself was originally programmed in a 4GL language called ABAP; it has now been supplemented by the possibility to code business logic in Java as well.
- Database Tier—This tier supports the SAP database, including the SAP Business Suite or R/3 and other SAP applications hosted on SQL Server. The database tier typically runs one database schema for each SAP product using separate server hardware. The database servers can be connected to a storage area network (SAN), Network Attached Storage (NAS), or locally attached storage.
Each SAP application process establishes two connections to SQL Server (as shown in Figure 3.2). There is no sharing of connections between the different SAP processes of an instance. SAP does not use connection pooling. Every one of the processes establishes connections at startup and keeps them until the process is shut down or restarted. SAP uses Multiple Active Result Sets (MARS) and multiple open client-side cursors that can use the same connection. Each connection is used for different purposes. One performs uncommitted reads of the data or creates stored procedures as needed. The other connection is for data modifications such as updates, inserts, deletes, and server-side cursors. The application tier has been optimized around using these connections.
)
Figure 3.2 SAP multiple connections to SQL Server.
We featured this ERP application because SAP has proven to the world how rock solid SQL Server is and that the smallest company to companies as large as SAP itself can safely and confidently deploy on SQL Server without hesitation.
OLTP Shopping Cart Example
This shopping cart example features an Internet-based e-commerce implementation for a leading health and vitamin retailer. At the center of this high-availability application is the shopping cart and a global ordering and fulfillment capability. Approximately 5,000 to 10,000 users are online concurrently at any one time, and this application supports up to 50 million hits per day. A key to this application is that it is "stateless," and all database calls are extremely simple and shallow. This web-facing application is built on JRUN but features SQL Server at the database layer, as shown in Figure 3.3.
)
Figure 3.3 An e-commerce Internet application with a SQL Server database tier.
This e-commerce application is just a part of a much larger Application Service Provider (ASP) platform. This ASP company houses hundreds of other companies' applications in multiple data centers around the globe. To ensure high availability, this ecommerce application was built on a two-node Active/Passive SQL Clustering configuration. In the four years that this application has been running, the database tier has achieved a 99.99% uptime with rolling updates at both the operating system and SQL Server levels. The OLTP database on SQL Server is approximately 10TB and utilizes log shipping to create a reasonably current disaster recovery (DR) copy (that has never been utilized!). Ninety percent of the reporting is done off the DR copy (approximately one-hour old data on average). This is fully within the service-level requirements needed by the health and vitamin company.
It's important to note here is that if availability falls below 99.99 (four 9s), the ASP company must pay fairly large financial penalties as a part of its agreement with its customers. Each physical server in the cluster is a Dell 8 CPU server with 256GB RAM on SQL Server 2008 Enterprise editions on Windows 2003 Advanced Servers. This has been a rock-solid implementation from the very start.