Application

3 4

A major part of your system is the application, which should be designed to perform well now and in the future. In this section, you will learn how to design an application with performance, scalability, and growth in mind.

Architecture

The basic architecture of an application can take one of many forms. The major differences between application architectures have to do with the number of systems involved in the application. This distinction is known as the number of tiers. Many of the most popular applications are advertised based on the number of tiers they comprise.

Architecture Comparison

Each database application is divided into three distinct components. These components are as follows:

  • Database services This is the back-end database server and the data that resides in the database.
  • Application services This is the application or business logic that manipulates the data that is retrieved from the database.
  • Presentation services This is the user interface. The presentation services must be able to manipulate the data into an understandable form.

The differences between one-tier, two-tier, and three-tier architectures are in how these components are divided up. In a one-tier architecture, they are all part of one program. In a two-tier architecture, these components are split into two distinct parts. In a three-tier architecture, these components are divided into three distinct parts. This is shown in Figure 4-1 and described in more detail in the sections that follow it.

click to view at full size.

Figure 4-1. Differences between architectures with one, two, or three tiers.

One-Tier Architecture

The one-tier, or single-tier, architecture is a system in which the database, application, and presentation services (the user interface) all reside on one system. This type of system does no processing external to the platform on which it is running. An example of single-tier architecture is a Microsoft Access database with local presentation services.

It is rare nowadays to find a substantial single-tier application, especially on a Windows 2000 platform. However, many smaller, single-user applications are single tier. Examples of this are Microsoft Money, Quicken, and TurboTax. These applications typically reside on the same system on which they are running. It is much harder to find an example that uses SQL Server. In fact, even though you can run Enterprise Manager on the same system that the database resides on, it isn't really a single-tier application because the application uses SQL Server networking components. The fact that you happen to be running them on the same system is irrelevant.

Two-Tier Architecture

A two-tier application is one in which the presentation services and the database reside on different systems. The presentation services (user interface) layer usually includes application logic. A good example of a two-tier application is one that uses SQL Server Enterprise Manager. For this type of application, the user interface and the application logic reside in Enterprise Manager, but all of the data that the application uses to function resides in a SQL Server database on a different system.

Two-tier applications are common. You might have worked with many of these applications already. These applications are typically created in languages that support the Windows programming APIs, such as Microsoft Visual C++ or Visual Basic. With a two-tier application, each user must have one or more connections into the SQL Server database. This architecture can be inefficient because most of those connections will be idle for most of the time.

Three-Tier Architecture

Three-tier applications separate the database layer, the application layer, and the presentation services layer into three distinct components. Typical three-tier applications use the middle layer to multiplex connections from the presentation services layer, which reduces the number of connections into SQL Server. In addition, the middle layer can perform a great deal of the business logic, leaving the database free to do what it does best: deliver data.

There is some debate over whether Web-based applications are two-tier or three-tier applications. You can use this simple test: if the data presented in the presentation services layer could just as easily use a terminal or a Web browser, the application probably has two tiers.

As you can see, the separation of the components allows you to use multiple systems. In fact, typical systems start with one database server connected to several application servers that, in turn, serve many PC clients. How you design your system depends on the number of users and the type of application you choose.

Performance and Scalability

When you develop an application and a database schema, you should keep performance and scalability in mind. You make many choices during the application design phase that can eventually affect the performance and the scalability of the system. These choices include the following:

  • The use of temporary worktables Often these tables are effective when the database is small, but as the amount of data becomes larger and larger, they cease to function properly.
  • The use of aggregate functions The use of aggregate functions such as MIN(), MAX(), and AVG() scales with the amount of data used. So be careful that your data set does not eventually become unwieldy.
  • The use of indexes As the amount of data grows, the use of indexes becomes much more important, as you will see in Chapter 17.
  • The use of transactions The use of explicit transactions is great for assuring that operations are atomic. However, as the number of concurrent users grows, it is important to reduce locking as much as possible.

As you can see, you should keep several factors in mind if you want to design a system that performs well as the workload grows. By incorporating performance optimization techniques from the design stage, you should be able to create a scalable system.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net