In late 2008, Microsoft released the certification exams for SQL Server 2008. There are three levels of qualification available:

- Microsoft Certified Technology Specialist (MCTS)
- Microsoft Certified IT Professional (MCITP)
- Microsoft Certified Master (MCM)

MCTS is the base level and can be obtained by passing one of the following exams:

• SQL Server Administration - 70-432
• SQL Server Development - 70-433
• SQL Server Business Intelligence - 70-448

MCITP is the qualification most people will be looking to obtain, and requires you to pass one of the MCTS exams and then the subsequent MCITP exam:

• SQL Server Administration - 70-450 (must pass 70-432 first)
• SQL Server Development - 70-451 (must pass 70-433 first)
• SQL Server Business Intelligence - 70-452 (must pass 70-448 first)

Finally, there is the Microsoft Certified Master certification. This is a high-level certification for experienced and highly-skilled SQL Server specialists. You must hold the MCITP qualifications in both development and administration before attempting this qualification.

Detailed information regarding the above qualifications can be found at the Microsoft SQL Server Certification site.

This article concentrates on the path you must take to attain MCITP: Database Developer 2008 status. It assumes you do not hold any prior qualifications; if you do, investigate the upgrade paths Microsoft offers.

Pre-requisites.

If you want to pass these exams, you should have the following available to you:

• A working installation of SQL Server 2008 Express Edition (if you have SQL Server 2008 Developer Edition, so much the better!)
• An up-to-date copy of Books Online
• The Microsoft preparation guide for each exam
• Time - try to allocate at least thirty minutes per day to study

Each exam has its own web site. The preparation guide for each exam can be found on its web site under the Skills Being Measured header. It's worth printing this guide out (you may need to copy it into a word processor first), and ticking off the areas covered as your study progresses.

About the Exams.

when you take each certification exam, you are scored out of possible 1000 points. The two exams are structured as follows:

• 70-433 - Around 65 questions, in three and a half hours
• 70-451 - Around 50 questions, in two and a half hours

Be aware that of the time specified above, not all of that time is assigned to answering the questions. Ten minutes or so is given for the exam introduction, and another twenty minutes at the end is given over to an optional survey.

Once you have answered all of the questions on an exam, you can review your answers. When you're finally happy (or not as the case may be!), you can end the exam. You then have the option to comment on individual questions before finally seeing your exam score. I commented on a few that I thought were not covered by the preparation guides.

You will immediately be told if you have passed or not. You must score at least 700/1000 to pass an exam.

Study Method

Everybody has a different idea about studying. Some people like to have music playing in the background, other people cannot study if there is any noise whatsoever. If you are studying for these exams, it's likely you already work with SQL Server. So try to incorporate some of your studying into your daily workload; if you are designing a new database, see if there are any areas where you can utilise newer features such as .NET assemblies or table compression. Obviously don't use exam-related features for the sake of it, but a couple of projects I worked on during studying were turned around in much faster time thanks to my improved understanding of what I could and couldn't do with the SQLCLR.

Books.

Many people like to study from books. There are a number of books available for the MCITP development exams now. If you want to buy these, great. However, my tip would be to purchase a general purpose book, or a book for the SQL Server 2005 MCITP. Alternatively, use Books Online, which contains all of the information you need to pass the exam.

A good general purpose book is Robert Vieira's Professional SQL Server 2005 Programming (US Amazon link). I don't think it's as good as his SQL Server 2000 book was (the editing is questionable in many places), but it gives a great overview and most of the chapters are relevant to the exams (the SQLCLR and XML chapters are highlights).

A SQL Server 2005 MCITP guide can be picked up for about £5/$8 from Amazon or Alibris. For example, the study guide for exam 70-441 (Microsoft SQL Server 2005 Database Solutions Design MCITP) can be picked up from Amazon UK for £4.50! This book contains several relevant sections for both 70-433 and 70-451, and also offers study questions at the end of each chapter.

One warning - if you buy a SQL Server 2005 book, ensure you consult Books Online after reading through a subject. For instance, there are only four isolation levels in SQL Server 2005; SQL Server 2008 has added the SNAPSHOT isolation level (and you will probably be asked a question about it on your exam).

Questions

Speaking of questions, there are literally tens of companies willing to sell you sets of sample exam questions. You don't need to spend a lot of money on sample questions. For starters, try the free questions on offer from the following companies:

• Pass 4 Sure (demo contains 10 questions)
• Transcender (demo contains 10 questions)
• Self Exam Engine (demo contains 10 questions)
• Self Test Software (demo contains 5 questions)

Measure Up does not actually offer any products covering 70-433 or 70-451 at the moment, but it's worth checking the site on a regular basis - they'll be appearing in the near future.

The test companies usually offer an exam simulation program containing a couple of test examinations. Often you can use study mode (where you can view the answers along with detailed explanations) or certification mode, which apes the actual exam conditions. If you find you like a company's product and the cost fits within your budget, go for it. The prices vary - some offer 30-day/45-day online access instead of a download, others offer unlimited access to all exams for a period of time for a set fee.

Another product worth considering is the Visual CertExam Suite. This is a relatively cheap way of obtaining practice questions. It works by converting formatted PDF files into question files (VCE format) that the Visual CertExam Manager application understands. A personal license costs $24.95. Free VCE files can be obtained from http://www.examcollection.com. It's unlikely you'll see any of the questions in the VCE on the exam (certainly the 70-451 file I used had maybe one or two similar questions to the exam I took), but the format is similar and the questions are presented as they would be on the exam, and cover the necessary subjects.

One big warning about VCE files! Some of the questions offer incorrect answers. A question I saw regarded a statement to create a SPARSE column. A SPARSE column can only be created if the column is declared as NULL. However, the stated answer to the question was the column definition with NOT NULL against it. Be aware of this - if you know your stuff you'll recognise incorrectly specified answers.

Guide to Exam 70-433 - MCTS: Microsoft SQL Server 2008, Database Development.

Exam 70-433 concentrates solely on database development techniques. This includes T-SQL, scripting, and ensuring code performs as expected (i.e. a basic knowledge of Profiler is required). The skills measured in the preparation guide are broken down as follows:

• Implementing Tables and Views (14%)
• Implementing Programming Objects (16%)
• Working with Query Fundamentals (21%)
• Applying Additional Query Techniques (15%)
• Working with Additional SQL Server Components (11%)
• Working with XML Data (12%)
• Gathering Performance Information (11%)

Don't concentrate on any one area; make sure you know everything in the preparation guide. Here are a few things you should be aware of:

• General T-SQL knowledge
  Make sure you are comfortable creating scripts. You should know all the basic DML and DDL commands, such as SELECT, INSERT, UPDATE, CREATE TABLE, CREATE PROCEDURE, ALTER TABLE etc. You also need to know the data types available to you (especially the new data types such as GEOGRAPHY and DATETIMEOFFSET). A good knowledge of FILESTREAM and computed columns will also prove useful. Make sure you are happy with the various types of function you can create too (table-valued functions are a particular area of interest).
• Views
  A strong knowledge of views is essential, especially the WITH options (i.e. WITH SCHEMABINDING, WITH CHECK OPTION etc). You will see at least one question involving views (probably more), and this information will also serve you well on exam 70-451.
• Triggers
  Everybody knows we can create DML triggers, but did you know you can create DDL triggers too? I didn't until I started studying for this exam, and they're great! Make sure you know about them, and how to return event data from them.
• Indexes
  Another subject that you will see on both exams. Ensure you know about the T-SQL index statements, the various types of index, filtered indexes, and included columns.
• Constraints
  It's obviously important to know about constraints, but make sure you know what things like NOCHECK do too (it specifies whether existing data in a table is validated against a new or re-enabled FOREIGN KEY or CHECK constraint).
• Managing Permissions (GRANT, DENY, REVOKE) and Execution Context
  This subject is referenced in several areas, and it's important to know how these commands work for exam 70-451 too. Learn what each command does, and have at least a basic knowledge of the kinds of objects/permissions you can manage. You must also know the difference between the EXECUTE AS levels (e.g. CALLER, OWNER, SELF, or user name).
• Partitioned Tables
  Microsoft really seems to be pushing this feature, and you will see it on both exams. It's imperative that you understand how SPLIT, SWITCH, and MERGE work. Read up on partition functions and schemes, and on how ranges are specified when creating a partition function; either RANGE LEFT or RANGE RIGHT can be utilised. Assume you have a RANGE LEFT partition function as follows:

  (10, 20, 30)

  This will create four partitions:

  <= 10
  > 10 and <= 20
  > 20 and <= 30
  > 30

  If you specify the same partition function with RANGE RIGHT the split will be:

  < 10
  >= 10 and < 20
  >= 20 and < 30
  >= 30

  You may well be asked how a split using RANGE RIGHT or RANGE LEFT will affect the partition scheme, so make sure you know it!

• SQLCLR
  At least a basic knowledge of SQLCLR stored procedures and functions is required, along with an idea of how the CREATE ASSEMBLY command works. Investigate what the various permission set levels do. This is crucial for both exams (the questions are more in-depth on 70-451).
• Error Handling & Transactions
  Both of these are separate subjects, but you can expect to see questions asking you whether putting a ROLLBACK in a CATCH block will have the necessary effect and where the correct place for a SAVEPOINT is.
• The MERGE Statement
  This is new in SQL Server 2008, and you will, without any doubt, see questions on it in both exams. Books Online contains all you need to know.
• Table & Query Hints
  This is a massive area on both 70-433 and 70-451 - make sure you know the various hint types inside out!
• Isolation Levels
  You should be familiar with the five isolation levels - and how each of them works. The SNAPSHOT isolation level is very important, as it was introduced with SQL Server 2008.
• Full-Text Search
  Another subject that crosses exam boundaries (are you seeing a pattern yet!). Read up on creating indexes and catalogs, and the various ways to search for data using FTS.
• Service Broker
  You will see some Service Broker questions on 70-433, and probably more in-depth questions on 70-451. Test out the basics of Service Broker, especially things like priorities and activation.
• XML
  Probably the biggest area on both exams. Key areas are the XML data type, OPENXML, FOR XML, XML indexes, and XML schema collections. A typical thing to be aware of is needing to specify [1] at the end of an XPATH statement when using the XML data type's VALUE method.
• Profiler
  You should be aware of how to use Profiler to identify issues with your system, especially with regards to deadlocks (thanks to Eralper's blog for this tip). The indefatigable Brad McGehee has written an excellent article on the subject - if you read this, you'll be able to answer any questions on profiling deadlocks.

Guide to Exam 70-451 - MCITP: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008.

Exam 70-451 builds on the knowledge gained from 70-433. It is a much harder exam (as you'd expect) and goes more in-depth, as can be seen from the preparation guide. The skills measured in this exam are:

• Designing a Database Strategy (13%)
• Designing Database Tables (16%)
• Designing Programming Objects (17%)
• Designing a Transaction and Concurrency Strategy (14%)
• Designing an XML Strategy (8%)
• Designing Queries for Performance (17%)
• Designing a Database for Optimal Performance (15%)

All of the points raised for exam 70-433 are relevant, plus the following;

• SQL Server Components
  Have a general idea of what things like SQL Server Agent, SQL Server Integration Services (SSIS), DB Mail, and Distributed Transaction Coordinator (DTC) do. Also have an idea of some of the basic tasks SSIS can perform, e.g. importing XML into base tables.
• Entity Framework
  You don't need to be an expert on this subject, just be aware of the terminology and practice creating a few database mappings using Visual Studio if you can. You need to know what things like Table-per-hierarchy and Table-per-type are - this Microsoft article gives a good overview.
• Security
  This looks at permissions in a more in-depth manner than 70-433, and includes being aware of how to prevent SQL injection attacks, application roles, and database schemas.
• Sizing Tables
  Page and row compression are key subjects here, as are sparse columns and column sets.
• SQLCLR
  70-433 only requires a basic knowledge of how to use the SQLCLR. But this exam also wants you to know about creating and using types, table-valued functions, aggregates, and triggers.
• Database Options
  SQL Server 2008 offers some useful database-level options; READ_COMMITTED_SNAPSHOT (determines whether row versioning is on or not), for example. Discover what this option does and read up on database options in general.
• Query Tuning
  Learn how to read a graphical execution plan, and be aware of what the various JOIN types (Nested Loop, Merge Join, Hash Join) in execution plans denote. You should be able to adequately read/understand an execution plan. Investigate how implementing batches in stored procedures can improve code efficiency. There's a useful article on Simple Talk about execution plans.
• Scaling
  A number of scalable solutions are covered by the exam. You don't necessarily need to know how to implement all of these, but you have to know how they work and the general principles involved. Key subjects are federated databases, scaling up vs scaling out (scaling up is improving your hardware, scaling out is implementing a multi-server SQL Server solution such as partitioning), distributed partitioned views, and scalable shared databases.
• Plan Guides
  Be aware of what plan guides are, the various types available, and how to implement and disable them.

Registering for an Exam.

Use Prometric to register for your exam. The process is pretty straightforward. I find the site doesn't work too well in Firefox, so use Internet Explorer to book your exam if you have problems. An exam costs £88 GBP at the time of writing, so it's important for your wallet that you pass first time! You could always ask your employer to stump up the fee however...

Summary.

If a subject in the preparation guide was introduced with SQL Server 2008, it's a fair bet it will crop up on your exam (after all, it's in Microsoft's interest to have us all using the latest features). The information given in this article will hopefully start you on the right path to passing your exams, and you should also now have a general idea of where you can go for exam resources and how to book your exam. Good luck!