Landscapes

The landscape of a computer system refers to environmental factors, such as language and locale settings, the network the system uses, the number of users, and the number and type of servers. Because you might install SQL Server systems into multiple environments, treat them as separate exercises and then see where the systems might benefit from overlap or consolidation.

In many organizations, there are multiple landscapes, and sometimes they fit in-between categories. The purpose of getting landscape information is more concerned with the network, layout, budget, and size of the systems to help explain the level of user activity. The number of users in a landscape is less important than what activity they cause.

It is important to keep in mind that five heavy-activity users can generate more traffic than dozens of Web users. For that reason, the categories discussed here are more of a guide than an absolute part of your overall formula.

Farm

The simplest type of environment is the farm, which treats each application environment separately. You will see this quite often in Web hosting sites, where security, settings, and possibly even patch levels need to be kept independent based on the particular requirements for the application. Most of the time, the only thing these landscapes share is security information. You can think of even a single installation as a simple farm landscape.

The number of users in a farm for a given database can range from the single digits to several hundred logins, but normally have a small impact, generating no more than 50K per transaction.

You have several configuration options in this area. You can have an independent installation of SQL Server, often the Workgroup or Express Edition, to suit the needs of the application. Another option is to install a more powerful edition and set up instances for each environment. An instance is an additional installation of SQL Server on the same system and allows for a completely separate environment for security and configuration. The instances are named separately but are operated on the same hardware. The system shares much of the program files (also called binaries) but not the database and configuration files.

One advantage of using an instance is its ease of maintenance. You have less hardware to track because SQL Server will control the management of the instances from a single point of view (instead of having multiple copies of the software). The other advantage is licensing. You do not have to maintain a separate license for multiple instances; you do, however, if you install multiple copies of a licensed edition on separate hardware.

The most important thing to keep in mind when using instances is that you need to ensure that they are still considered independently from a hardware standpoint, so that you provide enough resources on the server to handle them all.

Department

The most common landscape most DBAs work with is the department. In this configuration, you move out of the Express Edition as your bottom line into at least the Workgroup Edition. More often than not, you will want to set the baseline for this level of landscape at the Standard Edition, especially if your application requires more features than the smaller editions provide. It is also at this level that you need to investigate using multiple processors on your server hardware. The licensing costs are higher when you get into multiple processors, but you need to evaluate how CPU heavy the application is and plan accordingly.

The number of users in a department ranges from ten to fewer than a hundred, and users generate between 20K and 500K of data per transaction. They generate less if a middle tier is used and more if the application is designed in the client/server architecture.

In the department landscape, you want at least the Workgroup Edition for most applications. For larger footprints, use the Standard Edition. In this area, you will begin to consider options such as replication, perhaps with the organization or enterprise servers. At this level, you also have higher reporting loads, so ensure the hardware you are using can handle the additional traffic.

Organization

Depending on the size of the organization, you might group several departments' needs into a single landscape. In this configuration, consider using clustering technology, which enables you to group two or more SQL Servers for backup. I cover clustering in depth later in the book.

Another factor that comes into play in an organization landscape is that you will probably need at least one additional server for the reporting system. The reporting system can also be federated into multiple servers, but this is not always necessary unless the reporting load is heavy.

The number of users in an organization ranges from the low hundreds to fewer than a couple of thousand, and users generate between 20K to 500K of data per transaction, less if a middle tier is used and more if the application is designed in the client/server architecture.

The organization landscape often involves multiple facilities. Having more locations affects SQL Server in considerations regarding replication, failover, and multiple-use setups.

In an organization landscape, you definitely want to start with Standard Edition, and the reporting instance normally ranges from the Express Edition for small reporting loads. If you require Reporting Services (which comes bundled with SQL Server), you need the Standard Edition for that server, too. When you break out Reporting Services, you need to factor in Web traffic patterns found in the distributed architecture type (in addition to whatever other architectures you have).

Enterprise

The enterprise landscape involves one or more database back ends that supply larger, integrated applications' data. You will often see SQL Server servicing an SAP or Siebel ERP application. Enterprise databases are often located across a large geographical area, sometimes even across the globe. When I worked at the Space Center in Florida, our network included two ships at sea that relayed information when they picked up the booster rockets from the shuttle. It took quite a bit of planning to engineer that architecture.

Using the Unicode page set becomes a consideration in an enterprise, because sites might be located in multiple countries. Unicode takes more space than a single collation set, so double the space needs when you plan.

Because of its size, many teams in an enterprise are responsible for various regions, sites, or parts of a system, and you must consider standardization as one of your primary duties at headquarters. Standardization includes configuration, patch procedures, and monitoring. Enterprise systems also change more slowly than smaller systems and require an intense amount of coordination and planning. In this endeavor, ensure that formal system planning takes place.

In a large enterprise, or any system with high reporting activity, you need to add a separate reporting system, not just another server. You may also need to consider a reporting data server as part of that sub-landscape. The reporting data can be refreshed with many technologies, from database snapshots to full replication, and I cover those in Chapter 9, "Reporting Services." You can also use third-party solutions to create reporting databases. Either way, you will most likely need at least two more servers for the reporting function, one for data and another for report presentation.

An enterprise has thousands of users and potentially thousands of connections to any given application. Because of this mix of users, implementing and tracking security becomes more complex. You may have to implement SQL Server security in addition to Windows authentication so that you can allow clients, vendors, or outside partners to access data in the system. I cover security strategies in Chapter 4, "Security."

Depending on the application architecture, all users may need to access a central instance of the database. In this case, you need to consider 64-bit systems and clustering, so you will want to use the Enterprise Edition.

Another situation occurs when an application is broken into separate parts. Perhaps the application runs individually at each region or site. If your applications implement this method, you might need to use replication to consolidate the data. You will still want to consider 64 bit or clustering, because even separate installations will probably be large. Enterprise Edition is usually still the best edition to use.

With a picture of the application architecture in mind, you can measure the expected load the application creates. Load measures the number of users, but more important, it defines what effects they have on the hardware resources. For instance, in an enterprise accounting system, there are several types of users, and each type produces a different number of bytes for the operations they perform. They also perform those operations in differing frequencies. In short, it is not enough to know how many users there are to understand the load on a system. The vendor or developer should be able to help you understand these user profiles.