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.