Chapter 3: Examples of SQL Server Implementations

Excerpt from Microsoft SQL Server 2008 R2 Unleashed.

By Ray Rankins, Paul Bertucci, Chris Gallelli, and Alex T. Silverstein

Published by Sams

ISBN-10: 0-672-33056-3

ISBN-13: 978-0-672-33056-8

Newsletters: Sign-Up & Save! Receive Special Offers, Free Chapters, Articles Reference Guide Updates, and plug into the pulse of what's happening in your corner of the industry by subscribing to InformIT newsletters! FREE coupon after sign-up!

Try Safari Books Online NOW! Access the largest fully searchable e-reference library for programmers and IT professionals!

In This Chapter

  • Distinction between different database processing applications

  • Online transaction processing (OLTP) example

  • Internet shopping cart example

  • Traditional data warehouse star schema example

  • Online analytical processing (OLAP) cube example

  • Hybrid Distributed Data example

As you will see in this chapter, companies use SQL Server for many types of applications and on most tiers now. Gone are the days when you would second guess yourself choosing to use SQL Server over a competing database engine (such as Oracle or DB2 on a UNIX platform) to ensure you got optimal transactional throughput, high availability, and the highest performance. In fact, SQL Server outnumbers both of these database vendors in installed sites globally. Microsoft SQL Server has arrived!

The SQL Server Unleashed team has gathered a few showcase SQL Server–based applications to give you an example of what is possible with SQL Server in both the online transaction processing (OLTP) world and in the decision support systems (DSS)/business intelligence (BI) realms. Each example in this chapter comes from real-life database applications running in production environments at major organizations around the world. In general, all the examples in this book come from our direct customer experiences. We often translate those real-life customer implementations into AdventureWorks2008 or bigpubs2008 database terms so that you can easily re-create them for your own use.

This chapter describes two OLTP applications: one is a traditional ERP system using SQL Server as the database layer, and the other is an online shopping system with shopping carts and both high-availability and high-performance requirements.

On the DSS/BI side, this chapter presents a traditional conformed-dimension star schema data warehouse implementation for a high-tech company and then shows you what this looks like implemented as an online analytical processing (OLAP) cube created by Analysis Services.

Under the DSS/BI examples, this chapter describes a hybrid distributed reporting example that uses multiple SQL Server technologies to get the most out of a complex application environment in the healthcare industry.

Application Terms

Online transaction processing, or OLTP, is a class of applications that facilitate and manage transaction-oriented processing, typically for data entry, complex business processes (such as order entry), and retrieval transactions. The term transaction in the context of computer or database transactions is a finite set of changes that are grouped together and can be undone together if any one piece does not complete (or fails). Often, however, we speak of transactions as a business “unit of work” that can span multiple database transactions as one logical business transaction. The term OLTP has also been used to refer to processing in which the system responds immediately to user requests. An automated teller machine (ATM) application for a bank is a classic example of this type of OLTP transaction.

In many applications, efficient OLTP applications may depend on sophisticated transaction management software and/or database optimization tactics to facilitate the processing of large numbers of concurrent users and updates to an OLTP-oriented database. In a geographic-distributed database system, OLTP brokering programs are used to distribute transaction processing among multiple computers on a network. These days, central OLTP is often underneath the covers and integrated into most service-oriented architectures (SOAs) and exposed as web services that can be easily orchestrated for different application functionality.

Decision support systems (DSS) have been around since the late 1960s, beginning with model-driven DSS and running the gamut of financial planning systems, spreadsheets, and massive multidimensional databases more recently. We speak of data warehouses, data marts, executive information systems, OLAP cubes, and business intelligence when referring to DSS. All enable complex decision support capabilities, multidimensional data analysis, online analytical processing, business intelligence, spatial DSS, and complex querying and reporting capabilities.

DSS system categories are

  • Data analysis systems that support the manipulation, aggregation, and transformation of data for a specific task or purpose

  • Pure analysis information systems that enable a series of decision-oriented databases and small models

  • Complex accounting and financial models that calculate and forecast behavior based on business events and financial results

  • Predictive models that estimate the consequences of actions on the basis of simulation models.

  • Optimization models that provide insight and possible actions a business can take by generating an optimal solution consistent with a series of constraints

Microsoft has the capability to fully address the first three types and is only now venturing into predictive and optimization modeling. The examples in this chapter illustrate a classic data warehouse (star schema/snowflake, multidimensional, measures/facts), a small distributed data mart, and an OLAP cube.

For each example in this chapter, we try to describe the overall purpose of the application, the major use cases, and the technology and architecture on which they were deployed. Where appropriate, we might showcase a data model diagram, a relational schema, or a distributed topology that gives you some insight into why the implementation was done a specific way. You are likely to recognize some use cases that may be the same in your environment and therefore possibly apply the same techniques or solutions to serve you as well.

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.


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.


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.

1 2 Page 1
Page 1 of 2
The 10 most powerful companies in enterprise networking 2022