A Client-Server Database in SQL Server 2005

SQL Server 2005 is a client-server database. Typically, the SQL Server 2005

database engine is installed on a server machine to which you connect anything

from a few machines to many hundreds or thousands of client machines.

A client-server architecture can handle large amounts of data better than a

desktop database such as Microsoft Access. The SQL Server instance provides

security, availability, and reliability features that are absent from databases

such as Access. A client-server architecture also can reduce network traffic.

The server side of a SQL Server installation is used for two broad categories

of data processing: Online Transaction Processing (OLTP) and Online

Analytical Processing (OLAP).

 

OLTP

Online Transaction Processing is the kind of processing that the databases of

Amazon.com or any other large online retailer needs to do. A large number of

orders come in every minute and the information from each of those orders

needs to be written to the database quickly and reliably.

With OLTP, you can group certain actions together. For example, the different

aspects of a bank transfer between accounts would be carried out together,

so that if money is moved out of one account, it is also moved into another

account. Actions such as these that must be done together are called a transaction.

In the account transfer, either both the transfers take place or neither

do. The all or nothing characteristic of a transaction ensures that the data

remains in a consistent state. An OLTP database is tuned to support high

volumes of transactions that frequently change the data in the database. SQL

Server 2005 performs well as an OLTP database management system.

The transaction log stores information about transactions and the data

changes made in transactions, which are not rolled back. The transaction log

is an important container for information about recent changes made to a

database.

OLAP

An Online Analytical Processing database is intended to process large

amounts of data that doesn't change often. For example, an online retailer

might want to store summary data about sales by month, by region, by product

category, and so on. In SQL Server 2005, the OLAP functionality is carried

out in Analysis Services. In Analysis Services, you create cubes that allow you

to examine dimensions of a cube.

OLAP often takes place in a data warehouse. Getting large amounts of data

into good shape before putting it into a data warehouse is a major task, an

important aspect of which is to maximize data quality.

After all the data is aggregated, it is unlikely to change in the future. However,

you can query it in complex ways, so an OLAP database is typically optimized

to support fast querying.