Monday, March 7, 2011

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).
Online Transaction Processing is the kind of processing that the databases of 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
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.

No comments :

Post a Comment