70-431 Hints and Tips

I passed 70-431 (TS: Microsoft SQL Server 2005 - Implementation and Maintenance) a while ago and it’s shamefully taken months to get round to blogging the tips. As ever, the general tips are read a book, do some sample exams, fill the gaps from MSDN, do the exam and good luck. More specific tips are

The Book
As I’ve said before the Microsoft Press books are of an extremely variable quality. In this case the book is pretty good and covers the right stuff. You’ll still need to plug the gaps from MSDN.

The Simulation
This is the big tip. Unlike every other MS exam I’ve done this one has a simulation of some typical database tasks at the end. The simulation mocks up the Database Management console and asks you to click around to complete the different tasks. Make sure you can do the simple stuff such as

1. Perform a full backup.
2. Perform a differential backup.
3. Perform a transaction log backup
4. Restore a database
5. Create a login and user for a database
6. Access and run a profile

This list isn’t exhaustive but is the type of things that could be on the sim.

The Links
Below are the links that I used to supplement the Microsoft books. Some of these are pretty dry reading and again this isn’t exhaustive.

Installing and Configuring SQL Server 2005
Returning the login name associated with a security identification number (SID)
Ownership Chains

Maintaining Databases
Backup T-SQL
DBCC CHECKDB
Creating full DB backup
RESTORE T-SQL
Dedicated Administrator Connection

Creating and Implementing Database Objects
Creating Certificates
Rollup T-SQL
Xml Indexes
Auto Mode for Xml Queries
User Defined Types T-SQL
Indexed Views

Monitoring and Troubleshooting SQL Server Performance
Sp_addmessage

Supporting Data Consumers
SQL Server Browser Service
Server Broker Contract
Service Broker Endpoints
Service Broker Endpoints with Kerebos
Endpoint authentication

And of course best of luck if you’re taking the exam.