This excerpt, extracted from Microsoft SQL Server 2008 R2 Unleashed describes two OLTP applications, a traditional ERP system using SQL Server as the database layer, and an online shopping system with shopping carts with both high-availability and high-performance requirements.
Excerpt from Microsoft SQL Server 2008 R2 Unleashed
By Ray Rankins, Paul Bertucci, Chris Gallelli, and Alex T. Silverstein
Published by Sams
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.
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.
SAP multitier architecture with SQL Server as the database layer.
The SAP multitiered client/server architecture is composed of three levels:
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.
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.
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
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.
DSS Application Examples
The DSS/BI examples start with a traditional star schema data warehouse deployment for a Silicon Valley high-tech company. The same data has also been deployed as an OLAP cube created by Analysis Services.
The last example, describes a hybrid distributed reporting system that, uses multiple SQL Server technologies such as data replication, database mirroring, and database snapshots to get the most out of a complex healthcare industry application environment.
DSS Example One
A Silicon Valley computer company implemented a traditional data warehouse using a star schema approach. A star schema provides multiple paths (dimensions) to the central data facts. As you can see in Figure
3.4, a decision support user can get to the Sales Units, Sales Price, and Sales Returns through Geographic, Time, and Product dimensions. This allows the user to ask questions such as “What were net sales for North America for a particular month for a specific computer product?” SQL Server Integration Services (SSIS) packages populate this data warehouse and conformed dimensions on a daily basis with deltas (new data changes only). The data warehouse is unavailable for about one hour each night as daily updates are rolled into this deployment.
Star schema data warehouse for global computer sales.
This SQL Server instance is isolated from the OLTP application where the data is sourced. There are about 500–600 data warehouse users of this data globally. This data warehouse is approaching 5TB in size.
DSS Example Two
The same Silicon Valley computer company also implemented some of the same data in a more complex Analysis Services OLAP cube for data mining purposes. The company had many things it did not know about its sales data and wanted to do complex trending and forecasting to better understand the demand for products worldwide. Figure
3.5 shows the OLAP cube built in Analysis Services for this complex business intelligence purpose. Several demand forecasting and product sales trending models were developed to allow this company to predict sales by each of its products for each geographic region.
Multidimensional OLAP cube in Analysis Services.
DSS Example Three
This last example features a multitechnology hybrid data reporting solution that provides real-time reporting along with point-in-time reporting for a major healthcare organization in the Pacific Northwest. This solution starts with real-time data replication from its online transactional systems where all hospital transactions are taking place. This includes patient events, medications administered, surgeries done, hospital charges, and so on. By distributing this data to a highly available two-node SQL Cluster, the hospital is able to realize all its real-time reporting requirements that center around providing all known information for a particular patient in the hospital at any time. Figure
3.6 shows this OLTP-to-SQL cluster real-time, continuous data replication and the real-time reporting enabled by this data distribution.
Hybrid SQL Server reporting configuration.
Another major reporting requirement for this health organization is not a real-time requirement, but rather a leisurely hourly snapshot, point-in-time reporting requirement. A much larger group of users must be served by this noncritical reporting need and cannot impact the real-time reporting environment in any way. To satisfy this point-in-time, noncritical reporting need, the health organization leveraged SQL Server database mirroring from the replicated SQL Server Health Provider DB. From the mirror, hourly database snapshots are created to satisfy all the point-in-time reporting needs of the organization. This configuration has been extremely stable since the SQL Server 2005 deployment.
This chapter described some truly interesting SQL Server–based implementations. These examples reflect how major software vendors such as SAP have utilized SQL Server as the core of their ERP data tier, how Internet-based companies rely on SQL Server to host their e-commerce applications, and how SQL Server can be used to fulfill various decision support and business intelligence needs of major corporations. We selected these examples because they are rock solid and reflect potentially similar scenarios that you may be faced with. It is this flexibility and reliability that will allow you to be successful as well.
The next chapter delves into the functionality of the SQL Server Management Studio environment.
Excerpt from Microsoft SQL Server 2008 R2 Unleashed
By Ray Rankins, Paul Bertucci, Chris Gallelli, and Alex T. Silverstein
Published by Sams