Examples of SQL Server Implementations
- 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
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.