My torn data pages - Train_Cert

After running through the prep-guide (looking through a pair of SQL Server 2005 glasses), I identified a couple of topics worth giving a closer look. The topics are derived from the prep-guide, my comments about the topic added in blue italics and the bulleted list refers to (mostly) BOL-resources. This post is based on the prep-guide for 70-432 with published date June 11, 2008

Installing and Configuring SQL Server 2008 (10 percent)

Configure additional SQL Server components.
This objective may include but is not limited to: SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), replication. Not that I expect this to be really different from SQL Server 2005, but if your background is just DBA (MCTS/MCITP) it may be your first encounter with the BI-components.

• Considerations for Installing Integration Services
• Considerations for Installing Analysis Services
• Considerations for Installing Reporting Services

Maintaining SQL Server Instances (13 percent)

Implement the declarative management framework (DMF).
This objective may include but is not limited to: create a policy; verify a policy; schedule a policy compliance check; enforce a policy; create a condition.

• Administering Servers by Using Policy-Based Management Entry page, includes tutorial.
• Oh, DMF = Policy-Based Management... stupid MS-Name-Game.

Back up a SQL Server environment.
This objective may include but is not limited to: operating system-level concepts. I don't expect a lot of fireworks, but the operating system-level concepts made me curious.

• Planning for Disaster Recovery Actually, I'm still curious what is meant by operating system-level concepts. This link from BOL is actually my best shot at a document where some broader considerations are presented.

Managing SQL Server Security (15 percent)

Manage transparent data encryption.
This objective may include but is not limited to: impact of transparent data encryption on backups.

• Database Encryption in SQL Server 2008 Enterprise Edition General overview of encryption, how TDE fits in and some remarks on how backup is affected. Since the topic says "not limited to", consider the whole article to be valuable.

Maintaining a SQL Server Database (16 percent)

Back up databases.
This objective may include but is not limited to: full backups; differential backups; transaction log; compressed backups; file and filegroup backups; verifying backup. Only compressed backups is to be classified as new.

• Backup Compression (SQL Server)

Performing Data Management Tasks (14 percent)

Implement data compression.
This objective may include but is not limited to: sparse columns; page/row.

• Using Sparse Columns
• Using Column Sets

Maintain indexes.
This objective may include but is not limited to: create spatial indexes; create partitioned indexes; clustered and non-clustered indexes; XML indexes; disable and enable indexes; filtered index on sparse columns; indexes with included columns; rebuilding/reorganizing indexes; online/offline. Spatial and filtered indexes on sparse columns are of interest here, along with "is not limited to" which could be indexes on hierarchyid columns.

• Working with Spatial Indexes (Database Engine) Entry Page
• Filtered Index Design Guidelines
• Using hierarchyid Data Types (Database Engine) includes Tutorial

Optimizing SQL Server Performance (10 percent)

Implement Resource Governor.

• Managing SQL Server Workloads with Resource Governor Entry page, includes How-To

Use Performance Studio.

• Data Collection Entry page, includes How-To
• Again, Performance Studio, also an MS-Name-Game, what you're really looking for is Data Collection... and trying to get that confirmed, I found this webcast by Bill Ramos (62 minutes).

The rest, well it is all too familiar from SQL Server 2005. Sure, I'll look for some "What's new" resources, but I think the above pretty much covers what I need to familiarize my self with.

Release Candidate 0 is available for download (and downloading) and the MCTS exam 70-432 went into beta testing (and I registered). Since the beta is only running from June 9th through June 30th, I had to go for 27th as it was the only gap in my schedule. Let's see if I can find the time to blog about my preparations...

Exam-stats: 180 minutes¹, 61 question spread over 6 testlets (cases), passing score 700 points, only multiple choice questions, no simulations. I got 752, was lousy on SSAS ...and this actually was the first Microsoft exam where I really needed the time!!!  Compared to the other MCITP (70-443, 70-444 and 71-647) exams I sat, it was a lot more reading and fact-finding in the case-studies.

What surprised me on this exam, were a couple of questions targeted at the database engine. Think towards backup-requirements for filegroups (which are needed for partitioned tables), index optimization and transaction isolation levels (not mentioned in prep-guide). Unfortunately these topics aren't covered² in the courses 2794 to 2797 (or in 2791 to 2793). From the topics that are covered in the prep-guide, I'd say the number of questions was pretty balanced, only four things were really sticking out:

• Which data mining algorithm to apply in a certain scenario.
• Storage strategy for SSAS cubes.
• Slowly changing dimensions.
• Designing dimensions and hierarchies.

Useful resources for preparation.

• First of all, don't count on the Training-Kit for 70-446 from MS Press, it was announced but won't be released (MCITP BI News; some news for MCTS BI and SQL Server 2008). But if you don't take the Microsoft courses, the MCTS Training Kit for 70-445 (ISBN 9780735623415) is a useful resource.
• Prep-guide (don't go there without one).
• Peter Koller's review of 70-446 beta (and 70-445 beta).
• Jorg Klein's passing of 70-446.
• Ronald Kaijesteijns "verslag" (that is Dutch, as well as the rest of his post).

¹Actually, you get about 3 minutes per question grouped per testlet. This means for a 9 question testlet you get about 27 minutes, time left on one testlet is not added to the next. The 180 minutes should be regarded as an indication for the maximum exam length.
² At best superficially mentioned in 2796.

Just a few links where you can find more info about SQL Server 2008 Certification in general and about the separate certification tracks and exams.

Track alignment	Database Administration	Database Development	Business Intelligence
Microsoft Certified Technology Specialist (MCTS)	MCTS: SQL Server 2008, Implementation and Maintenance	MCTS: SQL Server 2008, Database Development	MCTS: SQL Server 2008, Business Intelligence Development and Maintenance
MCTS requirements	Pass: Exam 70-432 (expected availability of exam August 2008)	Pass: Exam 70-433 (expected availability of exam October 2008)	Pass: Exam 70-448 (expected availability of exam August 2008)
Microsoft Certified Information Technology Professional	MCITP: Database Administrator 2008	MCITP: Database Developer 2008	MCITP: Business Intelligence Developer 2008
MCITP requirements	Hold above MCTS certification and pass Exam 70-450 (expected availability of exam November 2008)	Hold above MCTS certification and pass Exam 70-451 (expected availability of exam January 2009)	Hold above MCTS certification and pass Exam 70-452 (expected availability of exam November 2008)
Upgrade option existing MCITP for SQL Server 2005	Existing MCITP:Database Administrators can upgrade above MCTS and MCITP by passing Exam 70-453 (expected availability of preparation guide September 2008)	Existing MCITP:Database Developers can upgrade above MCTS and MCITP by passing Exam 70-454 (expected availability of preparation guide September 2008)	Existing MCITP:Business Intelligence Developers can upgrade above MCTS and MCITP by passing Exam 70-455 (expected availability of preparation guide September 2008)

No upgrade paths exist for MCTS for SQL Server 2005 to MCTS for SQL Server 2008. Thanks Trika, for the pointer and poster.

Finally the results for the 71-646 and 71-647 are being published. I just checked the prometric site after reading Aaron's update on his scores, he passed his, I passed mine. Now all I need to do to haul in the MCITP: Enterprise Administrator is pass either the 70-620 or 70-624... I guess the 70-620 is by far the easier route.

Today I sat the 70-445 exam and completed those 53 questions with the minimal required passing score of 700 points. Now how did I get to that meager (but satisfactory) result?

First of all, I didn't allocate enough time for my preparation, so out of the initial things I mentioned, I only completed the MOC courses 2791 to 2794 (on a hindsight, the time spent with 2794 was wasted towards this exam). I also purchased the MS Press Training Kit for the 70-445 exam, but didn't really get to using it. In total, I only read the chapters 9 and 17 and ran through all 209 MeasureUP questions once. The reason for picking only chapters 9 and 17, besides time constraints, is the amount of Data Mining in the exam versus the (lack of) coverage of the subject in the MOC2791. The reason for running through the MeasureUP questions in study-mode was to get some exam-focus on the subjects and cover them all (see if there were things I missed from the MOC's). I should add that many of MeasureUP tests do not resemble the exam, like providing non-existent options or asking for trivial look-up fact... actual Microsoft exams have better quality.

Having that extra bit of exam-focus really helped, the MOC's tend to strongly focus on the development part of SSIS, SSRS and SSAS. The actual exam is more balanced between development and administration. The training kit too, seems to be more geared towards the administration part and definitely has a more task-based (or hands-on) approach than the MOC's. So in total, I think you need both for a proper preparation (or be able to compensate development or administration with in-depth practical skills).

Luckily the score report includes those scoring bars that indicate a little on your relative score. What is my experience based on the 7 topics tested;

• Managing SSAS
  • My relative score; 5th
  • Impression, 2791 definitely is shallow on this subject. And since I don't have to much real-world experience managing Analysis Services...
• Developing SSAS Solutions by Using BIDS
  • My relative score; 3rd
  • Impression, 2791 gives you all the handles you need, for the hands-on I can recommend taking a look at the tutorials in SQL Server Books On-Line. Again, no real-world experience on for me here either. Some applied MDX, nothing shocking.
• Implementing Data Mining by Using BIDS
  • My relative score; 6th
  • Impression, next to no coverage in 2791, you really need the 70-445 training kit here (mind, I just read it, didn't do the exercises). Very little basic DMX.
• Managing SSRS
  • My relative score; 2nd
  • Impression, some help from real-world experience, though I wasn't prepared for dealing with farms. Also, be prepared to modify the RSReportServer.config.
• Developing Reporting Solutions by Using SSRS
  • My relative score; 7th
  • Impression, 2793 gives you all the handles you need, but you should also to develop reports and care about how they look (questions included some beatifications of reports). Also, take a good look at URLs. If you only have this covered with the training kit, it seems to me (based on a quick glance), it's not going to be enough.
• Developing Business Intelligence Solutions by Using SSIS
  • My relative score; 4th
  • Impression, pretty well covered from the 2792... which was pretty helpful to me, as I'm used to solving a lot of stuff in the database (using views and stored procedures). Especially focus on transactions, checkpoints and logical combinations between expressions and constraints.
• Administering SSIS Packages
  • My relative score; 1st.
  • Impression, my real-world experience helped me out here, not the 2792. Focus on things like supplying configurations on run-time, securing parts of packages and the differences between storing in SQL Server vs. on the file system. Also be prepared for some dtutil and dtexec syntax.

As Ronald Kraijesteijn noted on his blog-entry (in Dutch) on the exam, it's pretty tool-oriented (like how you do something, even in which order). I felt this was particularly true on the developing with SSAS and SSRS. A couple of months experience are definitely going to prove advantageous. This was not the case for me, SSAS real-world is non-existent and SSRS already dates back a year. But hey, a 700-point pass still is a pass.

Fresh from the often delayed "SQL Server 2008 and Your Certifications", first session.

MCDBA will retire march 2009, no direct upgrade from MCDBA to a SQL Server 2008 certification.

70-446 will be superseded by 70-448 (~August 2008), 70-431 will be split in an Administration exam 70-432 (~August 2008) and 70-433 (~September 2008) for Development. So be ready for the following titles:

• 70-432, MCTS: SQL Server 2008, Implementation and Maintenance
• 70-433, MCTS: SQL Server 2008, Database Development
• 70-448, MCTS: SQL Server 2008, Business Intelligence Development and Maintenance

MCITP's can probably upgrade the MCTS and MCITP in a single upgrade exam. No timelines on the professional level exams yet.

MCA Databases is available, targeted at OLTP... Business Intelligence is under consideration.

Blogs to watch for more info on SQL Server 2008 Certifications:

• http://blogs.msdn.com/trika (here you can find the link to the recorded webcast in a day or two after this post, a written transcript will be provided later).
• http://blogs.msdn.com/gerryo

Just did my 71-647 (70-649 when it comes out of beta), without preparation... I just checked some links;

Permanent Link to 70-647 Windows Server 2008, Enterprise Administrator

Erfahrungsbericht 71-647 (German)

In general, my impression is in line with what Lucas and Noxx experienced. In terms of subjects, my exam was quite a lot of GPO, File Server, Clustering in combination with SQL Server 2005 , AD DS, AD CS, AD FS.

Should I have to take this exam again, there still is a little I would look at based on today's experience. The only subjects I would be looking for are:

• Feature overview of System Center (and a bit more specific System Center Virtual Machine Manager and SoftGrid)
• Windows System Resource Manager
• AD; what's changed from Windows Server 2003 to 2008
• AD FS

In general, being MCSE 2003, proper preparation for 70-649 and a bit of reading on the four topics above should be enough...

Oh, I added my remarks on 5 out of 71 questions that had some serious flaws or were just plain wrong.

If you're going to take the SQL Server class 2779B, there is a lot of XML in it. That is, in relation to SQL Server 2005. But do you know your XML? Well, the training implies you do, though it is not one of the published prerequisites. If you're blank on XML, or want to check on your skills, you may want to take a look at www.w3schools.com.

The available tutorials give you an overview of the general usage of XML-technologies, some of them (XML, XPath, XQuery, XSD) will return in 2779 (Modules 3 and 6) where they are applied on SQL Server 2005. A little study-guide to prepare you for the things to come.

• XML general, much of it is aimed at interaction with a browser (as is the rest of the XML-related topics). These have been omitted here, but can increase your understanding of XML. From an SQL Server perspective, the topics below focus on the data-side of XML.
  • XML HOME
  • XML Introduction
  • XML How to use
  • XML Syntax 
  • XML Elements
  • XML Attributes
  • XML Validation
  • XML Validator
  • XML Namespaces
  • XML CDATA
  • XML Encoding
• XPath.
  • XPath HOME
  • XPath Intro
  • XPath Nodes
  • XPath Syntax
  • XPath Axes
  • XPath Operators
  • XPath Examples
  • XPath Summary
• XQuery, omit the optional topics if you just want to understand what XQuery is about, include them if you seek to work with and understand the samples.
  • XQuery HOME
  • XQuery Intro
  • XQuery Example
  • XQuery FLWOR
  • XQuery HTML 
  • XQuery Terms (optional)
  • XQuery Syntax
  • XQuery Add (optional)
  • XQuery Select (optional)
  • XQuery Functions (optional)
  • XQuery Summary (optional)
• XSD, basically you only need the conceptual understanding of what a schema does and how it looks like. The other XSD-topics may be useful as well if you want to experiment with it. Also note that w3schools differentiates between the document enforcement mechanisms DTD (Document Type Definition) and XSD (XML Schema Definition). For SQL Server, you can ignore DTD's.
  • XSD HOME
  • XSD Intro
  • XSD Why Use
  • XSD How To
  • XSD schema
  • XSD Elements
  • XSD Attributes
  • XSD Restrictions
  • XSD Elements

... or at least didn't feel things were important enough to post, at least that's the excuse for not writing here for well over a month.

In the mean time, beta-season is opened again and I registered for the 71-647. However, I won't go trough the same depth of preparation as I did for the 70-649... I'll just go in and try to make it on my Windows 2003 and 70-649 prep-knowledge .

The other exam I registered for is the 70-445 and I'm planning to take the 70-446 later this year. Just to get myself started for the preparation of this exam, I collected some links to hold on to:

• Examen 70-445 Business Intelligence Verslag (in Dutch)
• Jorg Klein's Microsoft Business Intelligence Blog [Macaw]
• Microsoft BI Certifications or Why SQL is a 4-Letter Word
• BETA exam reviews 70-445 and 70-446

and I'll be using the Microsoft courseware for the courses 2791, 2792, 2793, 2794 and the MCTS Self-Paced Training Kit (Exam 70-445): Microsoft® SQL Server™ 2005 Business Intelligence—Implementation and Maintenance.

That should keep me busy for a while again...

As I wrote before, I passed my beta-exam and am well underway. Because of my experience preparing for the exam, the folks from NewLevel asked if I could do a presentation for them on Windows Server 2008... for marketing sake.

I said yes, so if you're interested, available on November 27th (13:30 - 16:00) and can be in Amersfoort:

• Overview of product features and why you might want to use them.
• How these features map to the new generation certifications and what this new generation certification could mean to you.
• Training options to prepare for the Windows Server 2008 certifications and job-roles.
• Besides all the talking, a demonstration deploying Network Access Protection in Windows Server 2008.

If you're interested, contact NewLevel by phone +31 73 599 0 150 or mail to Rein Floris at NewLevel. The presentation will be in Dutch an a small fee is charged.

Just visited the prometric-website, I've passed my 71-649. Also did a quick check on my MCP-transcript, but it's not showing there, yet! The preparation paid off.

... and other Microsoft exams. Since it's Microsoft's objective to have Performance Based Testing (a.k.a. simulations) in all MCTS exams, you'll want to have some peace of mind as to how they are scored (and about the relevance towards the total exam).

Read the full story at Trika's blog, I'm pretty sure more questions and answers will appear in the comments.

I don't make a habit of copying other people’s blogs, but after my prep-series for 70-649 / 71-649, it’s okay for me on this one. The original can be found at Trika’s blog.

Hi. You probably already heard the update on WS2008 release to manufacturing (RTM), now scheduled for 1st quarter of 2008 instead of end of year 2007. As a result...  The transition exams 70-648 and 70-649 will be available on October 29, 2007, now. They were scheduled for September 20, but the changes/slip in technology mean some items on our exams are affected, too.  If you took the beta for either of these exams (71-648 or 71-649), your result should be available no later than October 29 (or a few weeks before). The MCTS exams are still scheduled for RTM +30 days; the MCITP exams are still scheduled for RTM +60. Don't know what I'm talking about? Read about the WS2008 certification family.

Guess I have to wait for my beta-score a little longer...

It's offical and now available, passed exams stay on the transcript (thank goodness Microsoft for that)

Thanks Trika.

Well, I should say 71-649, because I sat the beta-exam. But how would I rate my preparations (1, 2, 3, 4, 5, 6, 7, 8) and the exam?

Let me start with the exam, 88 questions on:

• Windows Deployment Services, about 10 questions.
• Terminal Services, about 10 questions.
• Internet Information Services, about 20 questions.
• Active Directory, about 20 questions.
• Networking, about 10 questions.
• Virtual Server, about 5 questions.
• Not listed in the prep-guide, about 10 questions. These topics include Disk management, WSUS, Clustering, Recovering from boot errors; none of them really hard. I would say (apart from the different boot process and recovery options for Windows 6) basic knowledge is sufficient.

Mapping my preparation to the beta-exam, I can say IIS and Networking were well covered. Though there is one flaw on my IIS prep: .NET Trust Levels… I totally forgot about them.

My feeling on WDS and AD in general is okay, though I should have spend more time on Federation Services and Rights Management Services and gotten some hands-on experience with WDS. Towards Virtual Server, I can say I underestimated it a bit, thinking that my daily usage of Virtual Server 2005 for test and development would cover it. Not, you’ll need to invest in your skills to manage a production environment of legacy OS-es hosted on Virtual Server, including securing (the level of) access to specific machines and scripts.

And then there were Terminal Services, well actually my exam started with them and I was shocked (or maybe stunned) with the level of depth and detail in the questions. Maybe, like with Virtual Server, I underestimated TS. But with VS, I had at least the feeling the questions were fair, some the TS question however were IMHO based on look-up facts, not skill. If the spread of the exam will be the same as on my beta, prep deep and hard at ALL topics on Terminal Services.

That said about my preparation, but will I pass? Hard to say, first of all it’s a beta exam, so it’s also a test for the question pool (and some won’t make the cut). There were errors in at least two questions (which I commented) and I have my doubts about a couple of others (I’ll review what I can remember and answer that on Microsoft’s follow-up mail on the beta exam). Until then, I’ll anxiously await the result.

In a few hours I’m going to find out if my preparations (and expectations) match up with the exam (or should that be the other way around?).

Anyway, here is the final post on the preparations, covering IIS. On top of that, I’ve updated Preparing for 70-649, part 7 of many with the IIS stuff and some extras on activation and WDS.

IIS is huge and not only in terms of its share in the question pool (as reported in many experience reports in on the Internet). Surely I’m pointing at IIS.NET (www.iis.net), even than a sub selection is required. So let me sum up the resources I used, though I must admit I had next to no clues on what to prepare for other than a lot of command-line stuff, in other words: appcmd.exe.

First I had to get in the mood ;)… so I picked two webcasts (I had their links stored sometime when I was browsing resources).

Live From Redmond: Putting the Lego set together: Inside IIS 7.0's Componentization

There is an audio problem in the original webcast starting just after 18 minutes and lasting for about 2 minutes, nothing wrong with your PC (yes, I did restart the presentation).

Exploring the Future of Web Development and Management with Internet Information Services (IIS) 7.0 (Level 200)

I was tempted to only view the admin part of the webcast (~50 minutes), but sitting through the full webcast gives you a good view of what the modularized approach for IIS 7 means in terms of extensibility.

After the webcasts I went through the IIS 7 Resources and read all articles (1, 2, 3, 4, 5, 6, 7, 8) in “Explore IIS 7”. Just to get the complete picture.  A lot of these pages have a “Learn more … ” as their next/last page. This “Learn more” page has undoubtedly useful links, but after having clicked a few I decided to keep away from them to properly manage my time. Note that having viewed the webcasts makes the reading easier.

Basically I wanted to continue reading the rest as well, but that would present an information overflow, which would probably not be relevant to the exam. I already had my doubts if I wasn’t drilling too deep anyway. Looking at the skills in the prep guide, 14 out of 16 skills towards IIS are configuring. What I learned so far from the resources; configuration is stored in XML files machine.config, applicationHost.config and web.config. What I learned from the comments, emphasizing the importance of the command-line, appdom.exe will be the tool to edit these XML files.

I started taking up the configuration tasks with FTP, based on the 9-page guide from iis.net. In this paper the configuration is done against the bare XML for several different scenarios. In preparation terms, I’ll label this link Resource M_1.

Next was