Exam Reviews

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
• 05/08/2006

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 exam.

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 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 passwords).

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 T-SQL syntax.

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 (see http://www.microsoft.com/technet/prodtechnol/sql/70/books/c0318260.mspx 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 scenario.

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 roles.

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 personally.

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!