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.

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 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 Database Servers with
SQL Server 2005

Reviewer's ratings
8 (10 being the most difficult)

Exam Title
70-443 PRO: Designing a Database Server Infrastructure by Using SQL Server 2005

Who should take the exam?
Core Exam for MCITP: Database Administrator

Date available
Live as of February 13, 2006

Courses (MSPress or instructor-led courses)
2786: Designing a Microsoft SQL Server 2005 Infrastructure, 2 days
2787: Designing Security for Microsoft SQL Server 2005, 2 days
2788: Designing High Availability Database Solutions Using Microsoft SQL Server 2005, 3 days
MCITP 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 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.

Ten Things to Practice
  1. 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.
  2. Simulate database environments and think about the best technology for improving the availability to do the following: mirroring, log shipping or MSCS?
  3. Play with database mirroring. I was amazed at the number of questions about this topic, despite the feature not being fully supported by Microsoft.
  4. 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.
  5. Set up a log shipping environment and test both planned and unplanned role changes.
  6. Understand what normal forms (especially the third one) are and how denormalization can be used to improve performance.
  7. 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.
  8. 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.
  9. Test connections in Dedicated Administrator Connection mode (sqlcmd.exe -A).
  10. 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 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!

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


comments powered by Disqus

Subscribe on YouTube