70-443: New Generation, New Database Designer
You'll need to come fully prepared to take on exam 70-443, both as a developer and as a database designer. This one is tough.
- By Arnaud Aubert
Besides Windows Vista, Microsoft has made a heavy marketing investment
with SQL Server 2005 and the migration to it is already taking place.
Even for those who have lots of experience with earlier versions, this
one will be a great challenge to master. Existing features have been greatly
enhanced and exciting new features have been added, making SQL Server
2005 a better and more complex solution.
With this exam, Microsoft introduces a new path to certification. The
MCDBA of old is making way for the Microsoft Certified IT Professional:
Database Administrator, of which this exam is just one core requirement.
In this review, I'll take you on a tour of the exam objectives that you'll
need to be aware of before you decide you're ready to tackle this feature-heavy
Designing a Database Server Infrastructure
This objective ensures you fully understand the available editions and
how to optimize the way you install the software. SQL is now available
in more editions than ever: Mobile (for mobile devices), Express (free,
lightweight version for databases up to 4 GB HD, 1 GB RAM, 1 CPU), Workgroup
(up to 3GB RAM and 2 CPUs), Standard (4 CPUs and native 64-bit support)
and Enterprise (full clustering and partitioning support). You can review
detailed differences at: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Experiment installing several instances of SQL Server of the same and
different versions. Using named instances, you can make your server host
several databases separately. Keep in mind that having multiple databases
managed by a single instance means sharing the security logins and running
into the same process (therefore, you can't distribute CPUs).
Also, never forget that database files and log files should never be
located on the same disk. Transaction logs are always accessed sequentially
compared to database files that are accessed randomly.
Designing Database Servers with
SQL Server 2005
8 (10 being the most difficult)
70-443 PRO: Designing a Database Server Infrastructure
by Using SQL Server 2005
Who should take the exam?
Core Exam for MCITP: Database Administrator
Live as of February 13, 2006
Courses (MSPress or instructor-led courses)
Designing a Microsoft SQL Server 2005 Infrastructure,
Designing Security for Microsoft SQL Server 2005, 2
Designing High Availability Database Solutions Using
Microsoft SQL Server 2005, 3 days
Self-Paced Training Kit: Designing a Microsoft
SQL Server 2005 Database Server Infrastructure
Link to exam objectives guide
Designing Security for a Database Server Solution
Security is usually ensured both by SQL and the Active Directory technologies
as SQL can use Kerberos as its authentication protocol. Therefore, you
need to understand the basic concepts of AD. Users (as well as other resources)
can be placed within organizational units that allow delegation. It means
that you can create a set of users, place them within an OU, and give
some permissions to a given user to manage the objects (like resetting
Organizational Units can also be used to apply Group Policy Objects.
You can group your SQL Server computer accounts within an OU where a special
security GPO is applied. This GPO could secure the server by disabling
unused services or by allowing local logon only to a given set of administrators.
AD authentication may not always be the best way to identify users. If
they are external to your organizations, you may consider using certificates
or native authentication. In case of native authentication, review the
new password policies features offered by SQL 2005 and the associated
Also, keep in mind that not all kinds of permissions can be set using
GRANT, DENY or REVOKE statements. You may need to create triggers to make
more complex authorization checking before rolling back or committing
an action. New triggers in SQL 2005 include DDL triggers which virtually
enable administrators to authorize or deny any kind of action.
With .NET integration being one of the major new features of SQL 2005,
there are many security questions around the permission sets that can
be associated with an assembly. Be sure to review the help for the CREATE
ASSEMBLY command before taking this test. Also, check the security options
that can be applied to endpoints, which you'll also find in the help for
the CREATE ENDPOINT statement.
Designing a Physical Database
To succeed in this objective, you'll need to fully understand how data
is organized both from a logical and physical point of view. For logical
view, you need to understand new concepts like schema or synonyms. Moreover,
be sure to review concepts of denormalization and the third normal form
for more information). For physical view, you need to know what kind of
access is made for each kind of object (sequential access for transaction
logs, random access for index or tables) and how you can optimize disk
activity by dispatching data in separate databases, partitions and filegroups.
Designing a Database Solution for High Availability
High-availability solutions have been extended in SQL Server 2005 by providing
database mirroring. While this feature isn't directly supported by Microsoft,
you may still encounter questions on it, so be prepared. The exam does
not expect you to know how to implement a high-availabilty solution in
detail, but you must be able to choose an appropriate high-availability
solution -- MSCS, mirroring, log shipping and replication -- for a given
Designing a Data Recovery Solution for a Database
Data recovery should really not be a difficult point in this test if you've
got any experience at all in IT. All you need to know is how the back-up
and restore process works in differential, incremental and full modes.
You may be asked to design a good back-up strategy for a given situation.
Criterias involved usually include working hours and the time it takes
for a backup to complete and to be restored. The point is that the less
time a backup takes, the more time a restore takes (because of the need
of several tapes to complete the restore). Common sense is what you need
to succeed here.
Designing a Strategy for Data Archiving
Archiving is another objective in which common sense will be your best
friend. You will have to define a good strategy for data movement between
local or remote databases. A typical scenario includes moving archive
data to a datawarehouse according to a plan that you need to develop and
establish. The test also ensures you know the basic concepts of replication
(publication of articles) and its roles: subscriber, distributor and editor
Things to Practice
- Get familiar with basic Active Directory concepts
and terminology (Kerberos, LDAP, domains): Use dcpromo,
Active Directory Users & Computers, etc. No questions
are directly relevant to that but you really need
to understand all this to be successful.
- Simulate database environments and think about
the best technology for improving the availability
to do the following: mirroring, log shipping or MSCS?
- Play with database mirroring. I was amazed at the
number of questions about this topic, despite the
feature not being fully supported by Microsoft.
- Do a few exercises with an MSCS cluster. If you
can't afford to buy a real one, try some virtualization
software. Microsoft is clearly promoting this technology.
- Set up a log shipping environment and test both
planned and unplanned role changes.
- Understand what normal forms (especially the third
one) are and how denormalization can be used to improve
- Read about how .NET assemblies integrate in the
server and how you can restrict the actions that may
be taken (CREATE ASSEMBLY instruction) from a security
point of view.
- Install several databases and several instances
of SQL Server 2005 and make them interact using linked
servers. Understand security and administrative impact
of instance separation.
- Test connections in Dedicated Administrator Connection
mode (sqlcmd.exe -A).
- If you have already played with SQL Server for
some years, thoroughly read the " What's new
in SQL 2005? " section of the books online.
Be Thorough in Your Studies
Even if XML is a great improvement in SQL 2005, this point is not covered
at all within this exam. So don't bother with that if you don't need it
Otherwise, I think you need to read thoroughly how .NET assemblies and
endpoints are secured. As well, the security objective leaves no room
for guessing. Database logical design will be an easy point if you have
previous experience with design and know what third normal form is. Also,
keep in mind that the key to performance is distributing the storage efficiently.
And finally: Know your database mirroring.
Good luck on the test!
About the Author
Arnaud Aubert, MCP, MCSD, MCSE, MCSA: Messaging, MCDBA, SCNA, SCSA, SCJP, Linux+, OCA, has more than nine years experience in the IT industry. He's working as a consultant/trainer for ib, France, a consulting and training firm. He enjoys discovering and integrating technologies from Microsoft and the open-source world. http://www.magesi.com