Multiple programming methods are in use (what I call architectures), and new paradigms crop up all the time. Understanding the architecture your system uses is a vital part of your planning strategy, because the architecture determines, to a large degree, the software and hardware you need to run it.
When you ask IT management, developers, and users about the architecture of the software they are planning to purchase or develop, you will often face a little resistance in the answer. This resistance is normal, because you are making the decision to implement a more complex, and possibly even more expensive, solution. You are also giving other people work to do in gathering that information. Therefore, you must explain why you are asking the questions, and it is also helpful to explain that you will be presenting three options that have three different sets of costs and benefits. That usually helps lower the resistance, by showing everyone involved that you want to make the right decision and that you are sensitive to cost.
Whether you face resistance or not, it is important to get as clear a picture as possible before you build your environment, and that is where understanding the various application architectures comes into play. Do not be surprised if the answers you get involve multiple architectures. Treat them separately, and then determine whether they can be combined onto one or more servers.
At the risk of generalizing, the various parts of an application can be grouped into a presentation layer, composed of a user interface and its logic; a business logic layer, where the business rules are enforced; and a data layer, which controls the movement, storage, and maintenance of the data that the other two layers generate. You can generally categorize the architecture of an application by where it puts these three parts.
The simplest architecture is monolithic. In this architecture, a single computer runs all three layers of the application. This architecture is common on handheld devices, where the Mobile Edition of SQL Server 2005 is used in replication.
You will also see this architecture used in disconnected-client environments, such as a salesperson's laptop. This type of architecture is well suited for the Express Edition of SQL Server 2005. In a monolithic architecture, there is usually only a single user of the application at any one time.
Another example of the monolithic architecture is a server-based application, such as a manufacturing-monitoring or data-collection application. These applications have no human interaction and are so intense that the application needs direct access to the database. Depending on the load, you can use Express or the Workgroup Edition, unless you require the feature set found in the higher editions.
You can also find a monolithic architecture in a small Web-based application. Even though the user is located at the end of a browser, the actual client software in this arrangement is coded to run on the server. Depending on the load, you can use Express or the Workgroup Edition for this type of application, too.
The client/server architecture separates the system, grouping the presentation and business logic layers onto a client program using a high-level programming language and the data layer on the server. You might also see the presentation layer coded on the client and the business logic and data layers coded using stored procedures on the server.
The client/server architecture was the most widely used type of architecture in the 1980s and is still in wide use today. In this architecture, an application makes direct calls to the database and often stays connected through the entire session. In the past, this caused quite a few performance issues, but modern coding practices and libraries are allowing a resurgence in this method of programming. This architecture has a few implicit advantages, such as ease of coding and security.
The editions that are useful for this architecture include Express through Standard, depending on the number of users and the size of the database.
Widely used in the late 1980s through the mid-1990s, the three-tier architecture separated all three layers onto different systems. In this architecture, the presentation layer is separated out to a program that runs on the client machine, and the business logic runs on what is called the "middle-tier," which itself might be composed of more than one server. The middle-tier systems make requests from the data tier on another server using Transact-SQL and stored procedure calls, often combining and breaking apart the requests to service them quickly and efficiently.
The defining characteristic of this type of architecture is that the business logic and data layers might run on any number of servers. You might have one business logic machine talking to multiple databases, even multiple types of databases, or you might have multiple middle-tier machines talking to one database server. You might also have multiple servers of both types in the mix.
Although it is possible to use the Express or Workstation Editions in this architecture, it is more common to see the Standard or Enterprise Editions used. It is also common to see replication and clustering in this architecture, which mandates multiple SQL Servers.
The distributed computing architecture is one of the most difficult to prepare for. The reason for this is that distributed computing involves multiple resources.
There are actually two definitions for a distributed computing architecture. The first is tightly coupled, in which specific programs run on defined systems, communicating closely with each other, often assisted by operating system calls. The second is called loosely coupled, where programs communicate with each other periodically using standards such as Extensible Markup Language (XML). Loosely coupled systems often do not rely on an identical programming language or in some cases even an identical operating system or network. The World Wide Web is an example of a loosely coupled architecture.
Relational databases are most often the base storage components of these systems, and they often use multiple database platforms. For this type of environment, you need to break out each component that requires storage and decide what that part needs. You might even be able to use the Express Edition in some locations and Standard for others, depending on the activity and storage levels and features needed.
A service-oriented architecture does not fit within the previous architecture definitions, at least in that it does not provide a user interface layer. It is a set of service programs that reside on various systems that publish their capabilities on the network. Clients (which might actually be other servers) request responses from these services when they need them.
Services can interoperate but are normally kept stateless and independent. This allows the solution to be based on "best-of-breed" platforms rather than a single vendor attempting to provide an end-to-end solution. Often, you can take an older program and retrofit it to participate in a service-oriented architecture.
An example of this type of setup is a portal-driven enterprise. The portal usually has sections that are tailored to each user or classification of users. These "Web parts" might display manufacturing information, inventory or sales data, or even financial numbers. Although that kind of information can come from a single source, in the service-oriented architecture, several servers publish the type of information they can provide, and the portal, serving as the presentation layer, displays the proper data on demand. Nothing other than the primary interface code is actually stored on the portal server.
SQL Server 2005 is well positioned to provide a service-oriented architecture. In many cases, the service-oriented architecture requires a high level of features from the database platform, unless another server provides the data. For this reason, you will normally use at least the Standard Edition.