In 2009, I wrote an article outlining the study areas to concentrate on for the MCITP in SQL Server 2008 Database Development certification. Back then, my intention was to go straight on and complete the MCITP in SQL Server 2008 Database Administration. Unfortunately I moved to a new house in August 2009. After eighteen months of DIY I finally returned to my studies, and obtained the MCITP in Database Administration in July 2011.
This article outlines the areas you should cover when studying for the Database Administration MCITP, along with the exams involved and where you can find test questions.
The Qualifications.
There are two levels of SQL Server 2008 Database Administration certification:
To obtain the MCTS in Database Administration, you need to pass exam 70-432. To obtain the MCITP, you must pass both 70-432 and 70-450.
If you want to become a Microsoft Certified Master (MCM) in SQL Server 2008, you will need to pass both of these exams as a pre-requisite, as well as the two database development exams I have already written about.
Exams
The exams present questions in multiple-choice format. Here are the details for each exam:
70-432 is the basic exam and covers a range of general SQL Server administration areas. 70-450 is more advanced and covers all the areas of 70-432, plus higher-level DBA tasks such as high availability and infrastructure design. Be aware that all questions are multiple-choice (more on this later). However, 70-450 may include some drag and drop questions where you are asked to put a set of tasks into a particular order, e.g. restoring a piecemeal backup. I did not see any of these questions on my exam but I am assured they exist.
Two hours is how long you are actually given to answer the questions; you are allocated some extra time to answer survey questions and to optionally comment on the questions.
What You Need To Study
If you plan to take these exams, there are a couple of things you'll need:
- SQL Server 2008/2008 R2 Developer Edition (you won't be able to play with all of the high availability features if you use SQL Server Express)
- An up-to-date copy of Books Online
- The study guides for each exam from Microsoft's Web site (links provided above)
Books
You may wish to purchase a book. I've seen numerous questions on the forum asking if this book or that book will help. The truth is you do not actually need a book to study for and pass these exams. You can learn everything you need from Books Online. If you really would like to purchase a book consider buying a general SQL Server Administration book rather than a book aimed specifically at the exams. A general-purpose administration book will provide more value and will still be useful after you pass the exams.
I used the Wrox book Professional SQL Server 2008 Administration. I cannot recommend this book; it has great sections (clustering and backup/recovery for example), but many sections are poor (the Service Broker section is a mess). Your best bet is to take a look on Amazon, see what books are available, and then look at the user reviews.
Studying
The chances are that most people studying for this exam are database administrators in one form or another (even if you are what Brad McGehee calls an "accidental DBA"). The point is, you are doing some DBA work. To help you with the exam, try to use things you are learning from your exam studies in your day-to-day work. If your servers are a point of failure, try configuring high availability features like log shipping and database mirroring. You can even do this on your own machine if you install a couple of instances.
Play with the various performance counters available and the Dynamic Management Views (DMVs) and Functions (DMFs). You might be shocked at what they tell you!
Testing Yourself
It's important you try out some test questions before you take any of the exams. Test questions let you see how questions and answers are presented. In general, most questions are multiple-choice, where you have to pick one answer. Often you will come across questions where you need to pick two or three answers (or an unspecified number of answers). A typical multi-answer question may present a scenario and ask you which high availability features could be used, for example.
You can, if you wish, spend a lot of money on test questions. However, it is possible to find some for free. Here's a list:
- Pass Guaranteed (amongst the cheapest, $69 per exam - 10 questions per exam can be downloaded for free)
- Transcender ($109 for 30 days online access - 10 questions per exam can be downloaded for free)
- Self Exam Engine (discounts available and some demo questions can be downloaded)
- Self Test Software (again, demo questions available for free and various price points)
Try downloading the test questions and see what you think. Most of the question engines are very similar and ape the actual Microsoft software used for the exams themselves quite well. If you do decide to buy a dedicated book for the exams you should find numerous sample questions in there too.
Booking The Exams
Use Prometric to book your exams. It's a pretty straightforward process. Prices for all Microsoft exams went up in July 2011 - it now costs £99 GBP to take an exam.
Keep your eye out for the second shot offer advertised by Prometric from time to time - search the Web for "Microsoft certification second shot" or similar. The second shot deal usually gives you a free second attempt at the exam should you fail it the first time around (or allows you to have a second attempt at a much lower cost).
General Things To Be Aware Of For The Exams
The next two sections will outline some of the key areas you should investigate whilst studying for the exams. But as a broad overview, make sure you do the following:
- Find out which features are supported in the various editions of SQL Server
- Check out the upgrade paths for SQL Server 2005 to 2008
- The exams cover SQL Server 2008, not SQL Server 2008 R2 - so always make sure what you're reading is applicable to 2008 if you are using 2008 R2
- Read up on all the new features introduced in SQL Server 2008 - this is what many of the exam questions will cover
- Make sure you know what each system database is for
The 70-432 Exam
I've already mentioned that the 70-432 exam covers the basics. Here are the areas covered by this exam, along with their weightings:
- Installing and Configuring SQL Server 2008 (10%)
- Maintaining SQL Server Instances (13%)
- Managing SQL Server Security (15%)
- Maintaining a SQL Server Database (16%)
- Performing Data Management Tasks (14%)
- Monitoring and Troubleshooting SQL Server (13%)
- Optimizing SQL Server Performance (10%)
- Implementing High Availability (9%)
Take no real notice of the percentages; just learn everything as well as you can. Make sure you are adept with the following (see the study guide for a comprehensive list):
- Resource Governor
This also crops up in 70-450. It's a useful tool that can be used to lessen the load of backups or long-running queries on the server.
- Sp_configure
This is a system stored procedure, used to modify server-wide settings. You need to know how to use it, what settings it can change, and what each of those settings does.
- Logging
Read up on where SQL Server logs events to. There are a variety of logs and different components, such as SQL Server Agent, have their own log files.
- Policy Based Management
This is an excellent new feature and allows you to create rules on the server, such as ensuring stored procedures always begin with "usp_". Make sure you know how to put a policy together, for which areas of SQL Server you can create policies, and the various options available for policies.
- Data Collector
Another new feature that helps profile your server. Learn the basics. Once you use this you'll never stop!
- SQL Server Components
It's crucial you know what each component of SQL Server does, and also the basics of how you manage it (the settings file for SSIS, for example). Read up on things like the Database Tuning Advisor and SQL Server Configuration Manager.
- SQL Agent
At a minimum you should be comfortable creating/updating/deleting jobs and managing operators.
- Back Up/Restore
This is covered in much greater detail in 70-450, but I guarantee you will see a number of questions on this in 70-432. Learn as much as you can - this subject is key. Look into the three main backup types (Full, Differential, Log) as a baseline.
- DBCC
Research CHECKTABLE, CHECKDB, and all other DBCC commands. If you don't have a question on a particular DBCC command in 70-432, it will probably come up in 70-450 so your time won't be wasted (and everything you learn can be put into practice anyway!).
- Indexes
Indexes are the lifeblood of a well-performing SQL Server database. You need to know about every type of index there is - clustered, non-clustered, XML (primary and secondary), full-text, spatial...the list goes on and on! Learn as much as you can about creating and maintaining indexes.
- Linked Servers and Distributed Queries
Learn how to set these up and how distributed queries can be executed using OPENQUERY, OPENROWSET, and OPENDATASOURCE.
- Service Accounts
Each SQL Server service requires a service account. Learn what permissions are needed for this account and how to grant/deny privileges to it in Windows, as well as how to change the account if necessary.
- Monitoring
DMVs, Profiler, and Performance Monitor counters are important here, especially DMVs used for indexes. You will see several questions on these subjects.
- High Availability
This is studied in-depth on 70-450. For 70-432 you need to know what the various HA options are, a basic idea of how they work, and what the best option is for a given scenario
It's a big list, and the study guide presents you with even more areas to cover! But the more you learn for 70-432, the more you will already know for 70-450.
The 70-450 Exam
This is a considerably harder exam than 70-432 and requires a lot more study and in-depth knowledge. You need to know more about advanced items like FILESTREAM and encryption (these can pop up on the 70-432 exam too, but in a much simpler form). The areas covered here are:
- Designing a SQL Server Instance and a Database Solution (14%)
- Designing a Database Server Security Solution (15%)
- Designing a Database Solution for High Availability (15%)
- Designing a Backup and Recovery Solution (20%)
- Designing a Monitoring Strategy (13%)
- Designing a Strategy to Maintain and Manage Databases (14%)
- Designing a Strategy for Data Distribution (9%)
You will see a lot of questions around backup and restore on this exam, and they cover more advanced areas like mirrored media sets, piecemeal restores, and backing up individual filegroups. You'll also need to have a pretty strong grip on all of the high availability concepts as well as monitoring options.
- Backup and Restore
You could manage with the basics for 70-432, but not here. Practice different backup and restore types, including page restores. Investigate the options available to verify a backup and also make sure you know about media sets and devices. Absolutely all backup and restore areas are covered on this exam and you will see a lot of questions on the subject, so make sure you read up!
- Monitoring
Everything that applied in 70-432 is covered, but you now need to know what to use if a specific problem arises, and you should know each item in-depth. Find out more about the Data Collector, DMVs, Performance Counters, and Extended Events.
- Data Compression
You'll certainly come across this subject as you meander through your exam! Page and Row compression are covered. Consider how compression affects things like backups and how partitioning affects the whole compression thing.
- Management Automation
You do not need to be an expert in these areas, but be aware of what PowerShell, WMI, SQL Server Agent, Event Notifications and DDL Triggers can do for you.
- SSIS
Let's be clear - you do NOT need to be an SSIS developer to answer these questions. Just make sure you know the various security methods available for packages, how information is logged in a package, and the ways in which a package can be deployed.
- Replication
Replication is covered in an extremely basic manner on 70-432 - in fact, if you have a broad idea of what each replication type can do, you'll be able to answer the questions. Not the case here. Find out about the various agents used and how you can manage the replication jobs. Investigate the system stored procedures available. And make sure you are absolutely 100% clear on the features offered by each replication type.
- High Availability
The big thing to learn here is how the various HA options interface with each other and other SQL Server components. Can database mirroring be used with log shipping? Can log shipping be used if transaction backups are taking place? You need to know the answers to these questions (incidentally, the answers are yes and no). Also read up on database mirroring and clustering in detail. This is a vitally important area and one for which you will see many a question.
- Encryption
Transparent Data Encryption (TDE) and other basic questions about encryption may surface in 70-432. In 70-450 you need to know the best type of encryption for a scenario and how TDE affects other components of SQL Server.
- FILESTREAM
There are some basic 70-432 FILESTREAM questions. You need to know how clustering affects FILESTREAM and how backups/restores and encryption affect FILESTREAM for 70-450. Also be aware of the features FILESTREAM can be used with (and also the features it cannot).
- NUMA
NUMA stands for Non-Uniform Memory Access, and it is used to increase memory throughput in SQL Server. This is an advanced topic but it is vital you understand it.
- RAID Levels
70-432 asks some basic RAID questions, but the difficulty of these are ramped up in 70-450. You need to know the RAID levels inside out, along with how various combinations of them should be used.
- Recovering From Failure
The Dedicated Administrative Connection (DAC - also briefly covered in 70-432) is an essential read here, as is the ability to rebuild your master database and other system databases if necessary.
- File Placement
Make sure you know where certain files should go, e.g. the log files should not be on the same drive as the data files, and the tempdb drive should be on its own drive if possible. There are numerous questions on this subject.
Summary
So there you go - a broad overview of what you need to know for the MCITP administrative exams. I suggest printing out the study guide and ticking each item off on there as you cover it. Don't take the exam until you feel ready.
Good luck! I'm off to study for the BI exams now.
