23
Chapter 2: Data Architecture
2
work around, not with, the database schema; poor performance because the database
engine is dealing with improperly structured data; and an infl exible model that can’t grow
with the organization it is supposed to support. The bottom line is that good database
design makes life easier for anyone who touches the database. The database schema is the
foundation of the database project; and an elegant, simple database design outperforms a
complex database both for the development process and the fi nal performance of the data-
base application. This is the basic idea behind the Smart Database Design.Database System
A database system is a complex system, which consists of multiple components that inter-
act with one another. The performance of one component affects the performance of other
components and thus the entire system. Stated another way, the design of one component
can set up other components and the whole system to either work well together or to frus-
trate those trying to make the system work.Every database system contains four broad technologies or components: the database, the
server platform, the maintenance jobs, and the client’s data access code, as shown in Figure
2-2. Each component affects the overall performance of the database system:■ The server environment is the physical hardware confi guration (CPUs, memory, disk
spindles, and I/O bus), the operating system, and the SQL Server instance con-
fi guration, which together provide the working environment for the database. The
server environment is typically optimized by balancing the CPUs, memory, and I/O,
and identifying and eliminating bottlenecks.■ (^) The database maintenance jobs are the steps that keep the database running opti-
mally (index defragmentation, DBCC integrity checks, and maintaining index
statistics).
■ The client application is the collection of data access layers, middle tiers, front-
end applications, ETL (extract, transform, and load) scripts, report queries, or SQL
Server Integration Services (SSIS) packages that access the database. These cannot
only affect the user’s perception of database performance, but can also reduce the
overall performance of the database system.
■ (^) Finally, the database component includes everything within the data fi le: the phys-
ical schema, T-SQL code (queries, stored procedures, user-defi ned functions [UDFs],
and views), indexes, and data.
All four database components must function well together to produce a high-performance
database system; if one of the components is weak, then the database system will fail or
perform poorly.
However, of these four components, the database is the most diffi cult component to design
and the one that drives the design of the other three components. For example, the data-
base workload determines the hardware requirements. Maintenance jobs and data access
code are both designed around the database; and an overly complex database can compli-
cate both the maintenance jobs and the data access code.
c02.indd 23c02.indd 23 7/30/2012 4:07:52 PM7/30/2012 4:07:52 PM
http://www.it-ebooks.info