Demanding Data Design
The design and implementation of a database can make the difference between a well-tuned application and one that's a thorn in your side. The SQL Server 2000 Design exam tests your expertise in both.
For the last several years, Microsoft has divided the
focus of SQL Server into two areas. One is database administration,
covered by exam 70-228. This test focuses on your ability
to set up and manage SQL Server 2000 solutions at a systems
level. It’s geared toward systems administrators responsible
for the care and feeding of SQL Server installations.
(I reviewed this exam in the March
The other, 70-229, challenges your ability to design
and implement databases. This test focuses more on development
aspects, such as the logical and physical design of databases
based on business requirements and the implementation
of these designs using the features available in SQL Server.
Overall, I found the exam (which I took in its beta form)
to be fairly challenging. Microsoft expects that anyone
who’s worked with SQL Server in heterogeneous environments,
with client/server configurations of up to 5,000 users
and databases as large as two terabytes, will be able
to pass this exam. But in my opinion, if you’ve had a
year of solid database development experience involving
SQL Server, go ahead and train for the test. With that
said, let’s move on to the details of what you should
know as a Certified Database Administrator.
Server 2000 Design
“Make sure you know how and when to
use the specific features of SQL Server
2000—and how to do performance tuning—to
get through this test.”
Designing and Implementing Databases
with Microsoft SQL Server 2000 Enterprise
Live as of April 14, 2001.
Who Should Take
Core credit for MCDBA; elective credit
for MCSE and MCSD certification.
What Classes Prepare
- 2071: Querying SQL Server 2000 with
Transact-SQL. Two days.
- 2073: Programming a SQL Server 2000
Database. Five days.
- 2074: Designing and Implementing
OLAP Solutions with SQL Server 2000.
- 2091: Building XML-Enabled Applications
Using SQL Server 2000. Three days.
Not released at the time of this writing.
- 2093: Implementing Business Logic
with MDX in SQL Server 2000. Three
A Logical Data Model
A well-designed database structure’s a necessary foundation
for a well-built, data-driven business application. The
database design includes the logical separation of different
entities (such as “Employees” and “Departments”) within
the database, as well as attributes related to those entities
(“First Name” or “Employee Number”). It’s important to
understand the relationships between the various entities
that will be the basis of your application. You should
be able to answer design-related questions. For example,
can an employee belong to more than one department? Can
a department have zero employees? Must all employees be
assigned a unique “Employee Number” value? These factors
can have a large impact on how you design your database.
Entire books have been filled with rules and guidelines
related to database design. For this certification, be
sure you understand the concept of database normalization.
A highly normalized database favors the elimination of
redundancy of data and attempts to make sure all the attributes
for an entity pertain only to that entity. Multiple “normal
forms” have been defined, but it’s not necessary to know
the details for each degree of normalization. Just understand
the purpose of normalization and how you might normalize
a schema, given a specific database structure. In some
cases, you might choose to denormalize a database. One
common business reason for denormalization is for reporting
purposes. In some scenarios, the redundancy of information
is an acceptable trade-off for performance.
In addition to following normalization rules, a well-designed
database ensures the relationships between entities in
the database aren’t violated. For example, users or applications
may not be allowed to delete a department to which employees
are assigned. Deleting the department would cause “orphans”
in the Employees table (that is, it would result in employees
who aren’t attached to an existing department). This type
of data integrity is maintained through the use of referential
integrity constraints. Specifically, you can use and define
PRIMARY KEY and FOREIGN KEY relationships. New in SQL
Server 2000 is the ability to allow cascading of UPDATE
and DELETE operations once you’ve defined the proper constraints.
For example, I could instruct the database to delete all
employees related to a department when I delete the department
itself. The schema tool within Enterprise Manager can
be used to easily create and manage these types of constraints.
It’s likely you’ll want to add constraints to the types
of values that can be entered into columns. The most basic
type of column-level constraint is data type. You might
choose to use an integer data type for a zip code column
(or a character data type, if you want to allow dashes
to be entered).
You can apply a UNIQUE constraint to enforce business
rules. For example, suppose that employee numbers can’t
be duplicated within the Employees table. In this case,
a UNIQUE constraint on the “EmployeeNumber” column would
prevent duplication from happening.
Tip: It’s important to understand
how database design can affect the overall implementation
of database objects. Make sure you know how and why a
database should be normalized and be able to normalize
or denormalize a database structure, given an existing
schema. Also, be sure you have plenty of exposure to the
schema tools in SQL Server’s Enterprise Manager.
The Physical Database
Once you’ve decided how you want your database to look,
it’s time to address the task of implementing the design.
The first order of business is to create it. Although
SQL Server 2000 makes it easy for you to create databases
with a simple command or a few mouse clicks in Enterprise
Manager, you have various options for data storage implementations.
For example, you can use multiple database files to spread
information across multiple physical disks, and you can
use filegroups to simplify the administration of your
files. Important considerations include the efficient
use of physical hard disks for the placement of datafiles
and transaction log files. Given a set of database performance
characteristics, you should be able to determine the best
placement for data files. Once the database has been created,
you need to understand how to use Data Definition Language
(DDL) commands to create and alter objects such as tables.
Next, you need to understand the various objects you
can create within SQL Server 2000 databases. The basics
include tables and views. Understand how views can be
used to simplify database access. Other features include
constraints (discussed earlier), user-defined functions
(new in SQL Server 2000), stored procedures, user-defined
data types, rules, defaults and triggers.
Each type of object can be used to improve the overall
performance of your database; the trick is to know when
to use each. For example, given a specific set of requirements,
you may be able to choose between views, stored procedures
and user-defined functions for implementation. All of
those options might work, but in some situations, there’s
clearly a best choice. Make sure you know how to choose
from among them. In addition to choosing the objects,
you can create items with the “schema binding” option
and encrypt object definitions.
Finally, more advanced implementations will take advantage
of SQL Server’s replication functionality. You should
know the different types of replication possible (snapshot,
merge and transactional) and the pros and cons of each.
Microsoft uses a publisher, distributor and subscriber
metaphor for replication. You should also be familiar
with queued updating and the problems this can solve.
Another advanced feature is distributed partition views.
In large installations where performance is important,
this feature can be used to spread database load across
many servers, thereby making more efficient use of resources
and increasing scalability.
Tip: It’s a good practice
to store your transaction log on a different physical
disk from the data files for the database. This reduces
disk-related contention caused by reads and writes to
these files. Also, be sure you understand the purpose
of each type of object in SQL Server 2000, and when and
how to use them. For example, given a set of requirements,
you should be able to figure out whether a view or user-defined
function is the better choice.
Retrieving and Modifying Data
If you’re a database implementer, you can’t hide
from Transact-SQL! It’s likely that you’re familiar with
the basics of SELECT, INSERT, UPDATE and DELETE statements.
But before tackling your certification, you should probably
brush up on all of the features that Transact-SQL has
to offer. For example, you should understand the behavior
of aggregates and GROUP BY clauses in complex SELECT statements.
You get bonus points (from me, not from Microsoft!) if
you understand the purpose and function of the little-used
(but valuable) HAVING clause. Since Transact-SQL is the
language your users and applications will use to communicate
with SQL Server, be sure you’re a fluent speaker before
you attempt the exam.
Few real-world business environments exist in a vacuum.
That is, most companies have many different types of data
stores that can include simple text files, spreadsheets,
file-based databases (such as Microsoft Access), mainframes
and relational database systems. Microsoft’s ensured that
SQL Server’s a team player by providing multiple tools
for transferring information between data storage platforms.
Thus, you should be familiar with the concepts of linked
servers. Also understand how you can use system functions
such as OPENQUERY, OPENROWSET and OPENXML to read and
write data from various sources within your environment.
In SQL Server’s bag of tricks for transferring data between
systems is the bulk copy (bcp) utility, the BULK INSERT
command and Data Transformation Services (DTS). Each has
its strengths and weaknesses. Be able to choose the right
tool for the job, given a set of business requirements.
Although it’s not a cure-all for business challenges,
XML files provide several advantages to businesses that
want to transfer information between systems. New in SQL
Server 2000 is the ability for users to generate XML files
simply by using the FOR XML clause in queries. Know the
options for this clause, including RAW, AUTO and EXPLICIT,
as well as the XMLDATA and ELEMENTS specifications.
Tip: Take the time to play
around with SQL queries on a development system before
you take the exam. XML features are an important improvement
in SQL Server 2000. Be sure you practice reading from
and writing to and from XML files.
Programming Business Logic
When you’re accessing and manipulating data in
real-world relational database systems, you need to take
advantage of many of Transact-SQL’s more advanced features.
In general, the Transact-SQL language is quite powerful,
but it’s also different from many other programming languages.
Remember the main goal of SQL: retrieving and modifying
data. As an MCDBA, you should understand concepts such
as transactions (their purpose, their limitations and
how you implement them). Also, know how to take advantage
of SQL Server features such as triggers, views, user-defined
functions and stored procedures for simplifying the processes
of modifying and retrieving data. Each of these object
types can be helpful in controlling how your application
behaves, for optimizing performance and for ensuring that
data integrity is properly maintained.
In addition to knowing how to create objects for managing
business logic, be sure you understand the rules of logical
flow. For example, you can call stored procedures from
within other stored procedures, and there are ways to
find out how “deep” the calling chain is. Also, be sure
you can troubleshoot potential problems that may arise
when you have logical errors.
Tip: There’s no real shortcut
to learning how to program business logic efficiently
in relational databases. If you don’t think you’ve had
enough hands-on experience in designing a real-world business
application, get hold of some sample code and walk through
how it works. A well-designed system will take advantage
of transactions and other features for improving performance.
Tuning Data Access
Achieving high performance is generally high on
the list of priorities for all types of developers, and
relational database systems are no exception. In fact,
the efficient flow of data into and out of SQL Server
can mean the difference between the success and failure
of an application. Fortunately, many different tools are
available for monitoring and analyzing performance. Performance
monitoring and optimization can be addressed at various
If you want to monitor overall system health at the server
level, you should start with the Windows Performance tool
(or Windows NT 4.0 Performance Monitor). SQL Server includes
many objects and counters that can be used for finding
out how busy your server is and how efficiently it’s operating.
Many of the performance objects have changed in SQL Server
2000, so be sure you know which counters are useful for
finding certain types of problems. The “Explain” button
can be an excellent resource for understanding what you’re
At the level of specific databases, you can use the SQL
Profiler to monitor the queries running on your machine.
SQL Profiler can monitor hundreds of different parameters
related to the action your SQL Server’s experiencing.
You can monitor query times and various other operations
such as server logins and logouts. Furthermore, you can
save all this information to binary files or database
tables for further analysis. Microsoft has made several
changes and improvements to Profiler in SQL Server 2000,
so be sure to familiarize yourself with its features.
Most database developers have run into particularly troublesome,
slow-performing queries. In response, Microsoft has designed
many different query analysis features into the SQL Query
Analyzer tool. Make sure you understand the types of statistics
you can view and how you can use the “execution plan”
to find potential performance problems or bottlenecks
in a query. This is a great way to determine if indexing
might be needed or if other changes to the query logic
might help. In some cases, changing a few lines of SQL
code will result in orders of magnitude in improved performance.
Finally, no discussion of database performance is complete
without mentioning indexes. SQL Server allows you to create
one clustered index and many non-clustered indexes on
tables. Understand the purpose and differences between
these types of indexes, and how to choose which type to
use, given a set of requirements. You can use SQL Server’s
Index Tuning Wizard to examine queries or trace files
(generated by SQL Profiler) and provide recommendations
Tip: Become familiar with
SQL Server’s various performance monitoring and optimization
tools, including Performance Monitor objects and counters,
SQL Profiler, Query Analyzer and the Index Tuning Wizard.
You can set up these tools in a test environment on a
sample application to create troubleshooting scenarios.
Having these skills will be important in the real world
and on the exam.
A Database Security Plan
Security is a major concern for database developers
and database implementers alike. You must be sure that
only authorized users can get to specific information.
Many developers are moving away from using database-level
security in favor of application-level security; but you
should still understand permissions in SQL Server. You
should understand how logins and database users interact.
Also, know the purpose of roles and how to use them to
simplify database administration.
In addition to these types of security, you can also
implement very granular permissions on database objects
such as tables. For example, you can allow members of
your Human Resource department to modify all information
in the Employees table, whereas other users can only query
a subset of that data. Many different types of database
security strategies exist. A well-designed plan will require
minimal administrative effort while still meeting business
requirements. Good techniques include the effective use
of roles for creating groups of users based on their specific
job functions. You can nest roles and change their membership.
Make sure you have the ability to design a solid security
Tip: Be sure to experiment
with server roles, database roles and object-level permissions
before taking the exam. Start by creating some objects
(such as tables and views) and setting various permissions
on them to test the effects. In some cases, the results
might not be exactly what you’d expect!
All’s Well That Begins Well
Database design and implementation is a complicated
topic, one with a steep learning curve. The design and
implementation of a database can make the difference between
a well-tuned application and one that’s constantly a thorn
in your side! However, Microsoft has worked hard to make
SQL Server both user-friendly and powerful. Real-world
experience is probably the best prep for exam 70-229,
but studying features that you don’t often employ is just
as important. Good luck!