# Tuesday, February 9, 2010

If you're interested in expanding your skills in SQL Server, check out these events;

Happy learning.

Tuesday, February 9, 2010 10:43:22 AM (W. Europe Standard Time, UTC+01:00)
# Friday, December 18, 2009

Microsoft recently launched a Virtual Business Card site for Microsoft Certified Professionals. So if you ever passed one or more of those Microsoft exams in the NT 4.0 or later eras, check out the www.mcpvirtualbusinesscard.com site (Windows Live ID sign in to connect to you records in Microsoft's certification database).

I set my profile page up to show off the credentials..

Also had a look at including the transcript, but that would be a 13-page pdf, a bit of overkill if you ask me.

Friday, December 18, 2009 11:47:33 AM (W. Europe Standard Time, UTC+01:00)
# Friday, December 4, 2009

Yesterday I visited the SQL Server day 2009, organized by the Belgian SQL Server User Group SQLUG.BE, in Mechelen. Congratulations on the event guys!

After the keynote by Microsoft Belgium (I wish they had talked a little bit more about SQL Azure), I visited the session by Henk van der Valk on world record ETL... now Henk has control over some top notch iron, but that doesn't mean his tips don't apply to a modest setup. Henk also mentioned he recently joined the blogsphere at www.henkvandervalk.com.

Next I sat (sorry I have to say so) horrible sponsored session by Quest... and this has nothing to do with the FogLight product. On another occasion (an afternoon session by Quest Netherlands) I witnessed the possibilities of Foglight (for .NET, SQL Server and VMware) and I must say it's a good looking product. However we got 30 minutes of boring listing of challenges and day to day problems (as if we weren't aware of them already) and in the end got some screenshots, which were completely out of context. I would have be completely lost in the presentation if I hadn't been to the session earlier by Quest NL.

After that, I meant to sit the session "Reporting Services a DBA's tool? YES!!!", but since the agenda card was a little confusing (or better said, I didn't pay enough attention) I walked into the session by Nico Jacobs on "Handling XML in SQL Server". Funny, as there was nothing new for me in the session but still I really enjoyed it... most important because as a trainer you rarely get an opportunity to see one of your colleagues at work on a familiar subject. Thanks Nico, I really enjoyed it.

The other session I attended was on "Policy Based Management", again by Nico Jacobs. I hoped it would go deeper into the possibilities of implementing it for SQL Server 2005 and SQL Server 2000. Unfortunately that was not the case, so I'll have to dive into the Enterprise Policy Management Framework and PowerShell without a quick start. But again, it was a joy listening to Nico.

Final session and closing keynote was by Chris Webb on PowerPivot (a.k.a. Gemini). It wasn't my first glance at Gemini, but it definitely is the kind of quick start I was looking for. Sitting a session like that saves a day of looking for stuff.

All-in-all, a day well spent.

Friday, December 4, 2009 2:35:00 PM (W. Europe Standard Time, UTC+01:00)
# Tuesday, March 24, 2009

Noticed this one on Born to Learn... Microsoft has two sessions today/tomorrow on preparing for the exam 70-432

Tuesday, March 24  7:30 A.M. Pacific Time   (What time is this in my region? 2009-03-24 15:30 CET)

Tuesday, March 24  5:30 P.M. Pacific Time   (What time is this in my region? 2009-03-25 01:30 CET)

It slipped past me too, but since I can manage to squeeze the first session in my schedule, I'll attend. Follow up and link to the recording to be posted later.

My impressions of this sessions is that Rob Boek takes you through the main areas of interest for the exam, at least it aligned quite well with what I can remember from my beta-test. Good stuff if you need an impression of the most important topics of the exam to prepare for.

Updated 2009-04-02

Technorati tags: ,
Tuesday, March 24, 2009 9:46:13 AM (W. Europe Standard Time, UTC+01:00)
# Saturday, January 31, 2009

A common question for students during/after attending a Microsoft training: "Where do we get the virtual PC images we used during course?"

The answer is, you don't... Microsoft provides these images for classroom use only by Certified Partners for Learning Solutions and Microsoft Certified Trainers. Quite understandable, as these images, contain a lot of software. However, the students question is valid too, for practice and exam preparation. And you can get about the same experience you had in class, based on Microsoft's "Run IT on a Virtual Hard Disk" program. Run IT on a Virtual Hard Disk allows you to download and use a fully installed evaluation version of an installed product. So here is how you can build your own VHD for the 2779 or 2780 courses.

  1. Your PC; I recommend you use a PC with at least 1.5 GB of RAM and Windows XP.
  2. Virtual PC; download and install Virtual PC 2007 (if you want, you can use Virtual Server 2005 R2 instead). For the download and more information see the Microsoft Virtual PC site.
  3. SQL Server VHD; download the 4 files image files for the SQL Server 2005 Evaluation VHD and unpack the VHD. SQL Server 2005 is currently not listed on the Run IT on a Virtual Hard Disk site.
  4. SQL Server installation media; download the Evaluation Edition of SQL Server 2005 (180-day version), requires Windows Live ID. Some labs/practices/demonstrations require multiple instances, these are installed on the 2779 and 2780 images, but not on the Evaluation VHD. So you may need to install the SQLINSTANCE2 and SQLINSTANCE3.
  5. SQL Service Pack; the VHD for SQL Server Evaluation has no Service Pack for SQL Server applied, whereas the MOC courses 2779 and 2780 are based on SQL Server 2005 SP1. Links to the SQL Server 2005 Service Packs:
    1. SQL Server 2005 Service Pack 1
    2. SQL Server 2005 Service Pack 2
    3. SQL Server 2005 Service Pack 3
  6. Create a Virtual machine in Virtual PC based on the downloaded VHD. Set the amount of memory to at least 1024 MB, also enable undo disks.
  7. Start and log in to the Virtual PC guest, you will need the administrator password Evaluation1. You will notice that the Windows Server 2003 operating system is not activated, therefor you only have a limited period for evaluation.
  8. From the student CD that came with your courseware, run Allfiles.exe. This will extract all files required by the practices, labs and demonstrations. Note that the setup will be different from what you were used to during the course. The files in the course were on a separate VHD for each module, which was mounted as D:\. After unpacking Allfiles.exe the whole course is in one folder tree (typically C:\Program Files\Microsoft Learning\27xx\). Note that you may have to compensate for paths and server names; so when you are in 2780 module 4, a path D:\democode\SalesCert.cer should be changes to C:\Program Files\Microsoft Learning\2780\Mod04\SalesCert.cer. Likewise the name of the server is different too, so MIAMI should be changed to WIN2K3R2EE.

Tips about downloading and file interaction between the Virtual PC host, the Guest and the Internet.

  1. When you have a ISO-file on your host, you can mount this ISO as CD/DVD in the guest. You can also instruct Virtual PC guest to use the CD/DVD drive from the host.
  2. When you have normal files on your host, you can use the Virtual PC Shared Folders feature; this exposes a folder on the host as a network drive on the guest.
  3. You can use the Networking feature of Virtual PC to use Shared networking (NAT) or your hosts Network adapter to allow access to the network and to Internet, so you can download files directly into your Virtual PC guest.

Main differences between the MOC and Eval VHD's

  MOC Eval
Server name MIAMI Win2k3R2EE
SQL Server Edition Developer SP1 Enterprise no SP
Instances [default]
SQL Service Account MIAMI\SQLServer [LocalSystem]
Password Pa$$w0rd Evaluation1
Course files One VHD per module All files in a folder tree, paths have to be checked/changes.
Saturday, January 31, 2009 6:53:43 PM (W. Europe Standard Time, UTC+01:00)
# Wednesday, October 8, 2008

Just checked the Prometric site and the status for my 70-432 (71-432) and 70-448 (71-448) changed from tested to passed ;-).

Technorati tags: , ,
Wednesday, October 8, 2008 10:55:23 AM (W. Europe Daylight Time, UTC+02:00)
# Tuesday, June 17, 2008

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.

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.

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.

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.

Performing Data Management Tasks (14 percent)

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

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.

Optimizing SQL Server Performance (10 percent)

Implement Resource Governor.

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.

Technorati tags: , ,
Tuesday, June 17, 2008 6:13:20 PM (W. Europe Daylight Time, UTC+02:00)
# Tuesday, June 10, 2008

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...

Technorati tags: , ,
Tuesday, June 10, 2008 8:02:47 PM (W. Europe Daylight Time, UTC+02:00)
# Friday, June 6, 2008

Exam-stats: 180 minutes1, 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 covered2 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.

1Actually, 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.
2 At best superficially mentioned in 2796.
Friday, June 6, 2008 12:14:29 AM (W. Europe Daylight Time, UTC+02:00)
# Saturday, May 31, 2008

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.
Saturday, May 31, 2008 5:19:20 PM (W. Europe Daylight Time, UTC+02:00)
# Wednesday, March 26, 2008

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.

Wednesday, March 26, 2008 10:28:24 PM (W. Europe Standard Time, UTC+01:00)
# Monday, February 11, 2008

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.

Technorati tags: ,
Monday, February 11, 2008 11:05:42 PM (W. Europe Standard Time, UTC+01:00)
# Wednesday, February 6, 2008

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:

Wednesday, February 6, 2008 5:52:41 PM (W. Europe Standard Time, UTC+01:00)
# Tuesday, January 22, 2008

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.

Technorati tags: ,
Tuesday, January 22, 2008 4:12:30 PM (W. Europe Standard Time, UTC+01:00)
# Thursday, January 17, 2008

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.

Thursday, January 17, 2008 6:40:39 PM (W. Europe Standard Time, UTC+01:00)
# Monday, January 14, 2008

... 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:

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...

Monday, January 14, 2008 8:49:23 PM (W. Europe Standard Time, UTC+01:00)
# Friday, November 9, 2007

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.

Friday, November 9, 2007 8:05:57 PM (W. Europe Standard Time, UTC+01:00)
# Monday, November 5, 2007

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.

Technorati tags: , , ,
Monday, November 5, 2007 11:06:34 PM (W. Europe Standard Time, UTC+01:00)
# Thursday, October 4, 2007

... 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.

Thursday, October 4, 2007 10:20:32 AM (W. Europe Daylight Time, UTC+02:00)
# Thursday, August 30, 2007

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... 
  1. 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. 
  2. 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).
  3. 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...

Thursday, August 30, 2007 9:25:41 AM (W. Europe Daylight Time, UTC+02:00)
# Tuesday, August 28, 2007

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

Thanks Trika.

Tuesday, August 28, 2007 10:35:23 PM (W. Europe Daylight Time, UTC+02:00)
# Friday, August 3, 2007

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.

Friday, August 3, 2007 8:44:12 PM (W. Europe Daylight Time, UTC+02:00)

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 configuring certificates, where I was surprised to learn that appcmd.exe is could not be used for a lot of certificate related configuration tasks (Resource M_2).

This link might address two skills, as I’m not sure to what extend the words components, modules and handlers are used interchangeably (Resource M_3).

A link that (in a very simple way) satisfies three skills is this one, labeled Resource M_4.

This link will hopefully satisfy another 3 skills (well, one already covered by M_4), labeled Resource M_5.

In the configuration corner for rights, permissions and authorization, you should have gotten a pretty good impression from the second webcast, but here are the four links I think add some information. 1, 2, 3, 4 (Resource M_6).

There wasn’t information on backup. But hey, how hard can that be… check out appcmd backup /?, by now you should know the IIS team got their act pretty well together.

SMTP is another story, I haven’t looked deeper in there, other than just install it. To me it seemed nothing changed from Windows Server 2003, it even requires all the IIS 6.0 bits to be installed. Then again, the prep-guide could be hinting at configuring SMTP so your apps can send mail.

And finally UDDI, well next to nothing to be found on UDDI on the iis.net, at microsoft.com UDDI points you in various developer directions. Also Microsoft, SAP and IBM seemed to have the plugs pulled on the public UDDI business registry. This makes UDDI an enterprise niche, which will require cooperation between developer teams and corporate administrators. In other words, UDDI should have no place in a MCTS exam and I’m going to take my chances here.

All information in the resources (with exception of M_2) focuses at the underlying XML-configuration, so armed with this knowledge I started to test my skills with appcmd.exe in a VirtualLab. Unfortunately I ran into some troubles with the lab (which all by itself should take just a minute or 10 (out of 90) to complete, so I booted my own VM to play appcmd.exe a bit more. The thing I liked in the VirtualLab was the inclusion of appcmdUI.exe. Speaking of appcmdUI, life with appcmd.exe can become a lot easier; check out Kanwaljeet Singla’s appcmdUI.exe, after the exam... don't get used to it yet ;). Or use one of the other options to manage IIS7;

  • GUI administration
  • Edit the files directly with your favorite XML-editor
  • PowerShell
  • WMI
Friday, August 3, 2007 9:57:26 AM (W. Europe Daylight Time, UTC+02:00)
# Monday, July 30, 2007

Updated 2007-08-03 with added stuff on IIS, WDS and Windows Activation. 

I haven't done much in terms of blogging about my preparations the past couple of days. Mainly because I've taken a more structured approach after I caught myself reading an RFC to prepare for an MCTS exam (see part 5). To keep track of what I did, I use the table below so I can match the skills to be tested to the resources I used. So far I’ve gone about the following route;

Don’t forget you’re an MCSE. Windows Server 2008 is yet another evolution in the Windows Operating System. Your skills will evolve along with it (in other words, there’s only a little real new stuff). Or as Lukas Beeler stated: “An MCSE on 2003 could probably answer 50% of the questions without having touched WS2008”

  • Search resources (find this documented in some of the previous posts).
  • Watch the IPv6 white-paper webcast, followed by selectively reading through the white paper itself. Link (Resource A).
  • Skim / glance trough the reviewers guide (Resource B).
  • Watch the screencasts by Keith Combs (Resource C).
  • Get some hands-on experience with IPv6 (but don’t overdo it). (Resource D).
  • The E-Book, well only the chapters from Windows Server 2008 (Resource E). I haven’t looked at the PowerShell Step-By-Step chapters, as PowerShell is not on the exam.
  • Some background information on Rights Management Services from Windows Server 2003 (Resource F).
  • E-Learning 5934 collection. For a little more detail on the E-Learning and why I didn't add the last clinic, see my previous post.
    • Course 5936 Hindsight, take this after reading the EBook, the clinic is lacking in overview and seems to miss some essential bits (I toke this module before reading the EBook) (Resource G).
    • Course 5937, good clinic but not much new info after having worked with resources B, C and E (Resource H).
    • Course 5938 (Resource I).
  • Windows Deployment Service Role Step-By-Step guide (Resource J).
  • Volume Activation 2.0 Frequently Asked Questions for Windows Vista and Windows Server codenamed "Longhorn"- Beta 3 (Resource K).
  • I’m not sure if "Custom application directory partitions" means the same as in the Windows Server 2003 exams. If yes, check page 5-26 from the MCSA/MCSE Self-Paced Training Kit for Exam 70-291 (isbn: 0-7356-2288-4). Online Chapter 5 included as Resource L.
  • IIS was a story for itself, which I described in my last preparation post. For the table below, I labled this post as Resource M (which means the whole post in general) and some further qualified resources (like M_1, M_2, etc.) with specific links in that post (Resource M).
  • Get some hands-on experience with Server Core (Hindsight, not a priority. Keith’s screencast probably shows enough) (Resource Z).

Resources reviewers guide (B) and E-Book (E) have their respective chapter denoted as well, like (B_2) for chapter 2 from the reviewers guide.

What’s up with the table? First of all, these are the skills being measured from the 2007-05-25 prep-guide with a priority column and a resource column. Each time I encounter a comment on the internet about the skill being heavy tested, it receives a plus. Plusses are direct or inherited from the group, that is; if I felt a comment could be pointed to a group, that’s where the plus landed. Resources point to thing I used, did or read to cover that topic; finally comments are things I want to mention on the particular topic.

One general comment though, the exam is said to heavily focus on command-line tools. So I specifically paid to attention to the command-line tools used in the various topics.

70-649 Priority Resources  Comments
Configuring Network Access    
Configure Remote Access.   B_5, G  
Configure Network Access Protection (NAP) components. + B_5, G  
Configure Network Authentication.   B_5  
Configure data transmission protocols.   B_5, H  
Configure wireless access.   B_5, G  
Configure certificate services. + B_5, E_7, G  
Configure DHCP. + D, B_5, G  
Configure IPv4 and IPv6 Addressing. ++ A, D   
Configure Routing.   B_5  
Configuring Terminal Services +  
Configure Terminal Services Remote Programs. + B_3, I  
Configure Terminal Services Gateway. + B_3, I  
Configure Terminal Services load balancing. + B_3  
Configure resource allocation for Terminal Services. + B_3, I  
Configure Terminal Services licensing. + B_3  
Configure Terminal Services client connections. + B_3, I  
Configure Terminal Services server options. + B_3, I  
Configuring a Web Services Infrastructure +++ B_6 is heavily underpowered to cover the subjects
Configure File Transfer Protocol (FTP) Server. +++ M, M_1  
Configure backup. +++ M,  
Configure Web applications. +++ M, M_4, M_5  
Configure Application Pools. +++ M, M_5  
Configure IIS components. +++ M, M_3  
Publish IIS Web sites. +++ M, M_4  
Migrate sites and Web applications. +++ M, M_5  
Configure SMTP service. +++ M,  
Configure Universal Description, Discovery, and Integration (UDDI) service. +++ M,  
Configuring Security for Web Services +++ B_6 is heavily underpowered to cover the subjects
Configure handlers to reduce attack surface. +++ M, M_3  
Configure .NET Trust levels. +++ M,  
Configure authentication. ++++ M, M_4  
Configure rights. +++ M, M_6  
Configure permissions. +++ M, M_6  
Configure authorization. +++ M, M_6  
Configure certificates. ++++ M, M_2  
Deploying and Monitoring Servers      
Configure Windows Deployment Services (WDS). +++ B_7, J  
Capture WDS images. +++ B_7, J  
Deploy WDS images. +++ B_7, J  
Configure Windows Activation.   C, K  
Create virtual machines. +++ B_2, E_3  
Configure Virtual Server settings. +++ B_2, E_3  
Install Windows Server Enterprise.   C  
Install server core. + C, Z, B_7, E_6  
Configuring Server Roles      
Implement server roles by using Server Manager.   B_7, E_4, E_5  
Configure ADLDS. + B_5, E_7 Formerly known as ADAM (Active Directory Application Mode)
Configure ADRMS. + B_5, E_7, F  
Configure the Active Directory server core. + B_5, E_7  
Configure the read-only domain controller (RODC). +++ C, B_4, H  
Configure Active Directory Certificate Services. ++ B_5, E_7  
Configure Active Directory Federation Services (ADFS). + B_5, E_7  
Maintaining the Active Directory Environment +    
Configure backup and recovery. + B_5, B_7  
Perform offline maintenance. + B_5, E_7, H  
Configure custom application directory partitions. + L  
Configuring the Active Directory Infrastructure +    
Configure communication security for Active Directory. + B_5  
Configure the global catalog. +    

If time is less of an issue, visit the TechCenter which has lots of resources (Step-By-Step guides) to get the knowledge and Hands-On experience.

Monday, July 30, 2007 12:26:27 PM (W. Europe Daylight Time, UTC+02:00)

Review of the free E-Learning collection 5934 towards preparation of 70-649. Certainly not a catch-all resource, but the first three out of four clinics did add value in my preparation.

Clinic 5936, covers Network Access Protection (NAP). Though the E-Learning doesn’t mention it this way, NAP basically is an extension build around NPS (Network Policy Server, Microsoft’s RADIUS implementation and replacement for Win2k3 IAS). To use NAP, you need clients that are NAP-capable and can validate their Health (think Firewall, AV, Malware protection, patching) with the servers for compliance with the companies System health policy. For better results, combine the E-Learning with the Reviewers Guide sections 5.02 and 5.03. I found this clinic quite lacking in terms of providing a decent overview, but it enhances the Reviewers Guide by adding visualization. 

Clinic 5937, focuses at the branch offices. With Windows Server 2008 this means lots of RODC, but also TCP/IP improvements (for WAN), BitLocker, some administration delegation and stopping the AD Service for maintenance (rather than rebooting the server into Active Directory Restore Mode). Good and useful clinic, but also includes some topics that bear no relevance to the exam.

Clinic 5938, with Terminal Services at the core of this clinic. Listen to the intro and stop wondering why it looks like Citrix (in other words, leverage your experience with MetaFrame or Presentation Server). This clinic throws a lot of different scenarios at you, so you may want to combine it with chapter 3 from the Reviewers Guide to keep an overview. This clinic (like the 5936) adds visualization to the Reviewers Guide.

Clinic 5939, focuses at the “initialization” (initial configuration tasks and adding roles and features) and management of a server. Many topics however, aren’t relevant to the exam (PowerShell, Remote Management, Troubleshooting and Diagnostics). It is a useful clinic in getting to know some new features of Windows Server 2008, but with next to no relevance to the exam. The parts that are relevant to the exam, are already covered by Keith’s screencasts, the EBook and the Reviewers Guide.

Monday, July 30, 2007 12:16:37 PM (W. Europe Daylight Time, UTC+02:00)
# Tuesday, July 24, 2007

Today I decided my efforts to get hands-on experience with Windows Server 2008 Beta 3 were noble as well as inefficient. To be honest, I don’t really think it’s inefficient in terms of getting to know the product better, but it is in terms of time management towards the exam on August 3rd.

How did I get to that conclusion? I was playing with DHCPv6 and DNS and all of a sudden I found myself reading an RFC (3596 for those interested). What was I doing? Getting DHCPv6 to lease addresses and see those addresses being registered in DNS, both the AAAA records and the PTR records. But I had a little trouble getting the ip6.arpa zone created (and in the end it turned out just to be a matter of knowing what exactly to type in the wizard). That was the detail, but I am also preparing for (just) a MCTS-exam on a broad range of topics and skills.

Anyway, this scenario will be the last “getting my hands in the dirt” for a while. After that, I will look in to the free E-Learning and the free E-Book, probably followed by working my way through IIS7. Based on all info I found, IIS7 is topic #1 on the 70-649.

There is just one possible topic I’m uncertain of: PowerShell. The PowerShell book is recommended Microsoft Press self-paced training products on the Prep-guide. However PowerShell isn’t mentioned in the skills tested and I haven’t read any comments about PowerShell questions in the various experiences. Does any of the 71-649 veterans care to drop a word on PowerShell?

Tuesday, July 24, 2007 10:21:59 PM (W. Europe Daylight Time, UTC+02:00)
# Monday, July 23, 2007

The new VM (to be named Win2k8-Full-01) installed overnight, first things first: VM additions and a larger screen size (going graphical now). Second, not being able to do a thing with DHCP on the core-install (Win2k8-Core-01) still bugged me and while in Initial Configuration Tasks on Win2K8-Full-01 I started looking around the roles and features. There it was under features: Remote Server Administration Tools, but no remote tool for DHCP *now what*… well I just installed the DNS Server tool. Next the system wanted to REBOOT ?!? WinNT 3.51 déjà vu, I hope these kinds of reboots won’t make the final product.

The DNS Server tool on Win2K8-Full-01 threw an error “A security package specific error occurred”. I only could stop, restart or pause the DNS Service over at Win2k8-Core-01 and view the DNS Event log. I tried to see what happened if I made changes to DNS on Win2k8-Core-01 using dnscmd. Again the changes didn’t show in the DNS Server tool (although they were visible in the DNS Event log). Time for an upgrade to an Active Directory environment, I installed the role Active Directory Domain Services (reboot again), dcpromo followed by an expected reboot. After the reboot, it was apparent that the roles DNS Server and File Services were installed at Win2k8-Full-01 too.

The thing I realized after kicking off dcpromo was I didn’t look at the domain functional level. I went with the Windows Server “Longhorn” forest functional level, which made the wizards questions about domain functional levels obsolete. Hence I looked it up; Appendix of Functional Level Features a link I think will be useful when going into the Configuring Server Roles (see prep-guide), which is pretty heavy on AD-stuff.

Next step, enlist the Win2k8-Core-01 in the newly created longhorn.local domain (use NETDOM JOIN). Sounds easy, but it wasn’t. The ADSL-router (being DHCP and DNS server) complicated things, so I had to switch to manually configure DNS registration over at Win2k8-Full-01 through netsh. Once I had that setup over IPv6, the join worked.

Unfortunately after the Win2k8-Core-01 joined the domain, I was still unable to connect to its DNS Server as it kept insisting on the error “A security package specific error occurred”. In the mean time, I also found the Core Server Step-by-Step Guide. Hindsight knowledge says I should have read this paper before getting my fingers in the dirt. Anyway I think I’ve played enough with this Core server thing towards the exam.

Next stop: DHCP, DNS and AD. Win2k8-Full-01 already acting a DC, DHCPv4, DHCPv6 and DNS. Threw the Win2k8-Core-01 from the disk and now installing Win2k8-Full-02. In the mean time watching TechNet Webcast: Technical Overview of Active Directory Domain Services in Windows Server 2008.

I also want to share this link: Exam impression by Lukas Beeler.

Monday, July 23, 2007 8:27:36 PM (W. Europe Daylight Time, UTC+02:00)
# Sunday, July 22, 2007

Commenting on my blog works again; Captcha issue resolved (a.k.a. disabled).

Didn't really do much in terms of preparation yesterday, just read over the IPv6 white paper. Today I fired up my Win2k8 Core VM and started to play around with IPv6. However, before getting to play, there was Product Activation. I had seen on one of the screencasts by Keith Combs that there is this vbs-tool (which you should probably know about on the exam): slmgr. Strange part was that when I checked the expiration date, it told me I had 26 days left, even though I had auto-activation on Internet connection checked when I installed. Well, must be one of those beta-thingies, but slmgr –ato toke care of the situation. Second thing I tried was installing the Virtual Machine additions. It didn’t auto-run, but manually going for setup.exe, installing and rebooting did give me the VW-additions.

Next I went through the commands and tools mentioned on the IPv6 config page. All well, I have ipconfig, route and netsh where the interface ipv6 will be important. Since IPv6 is said to be really easy auto configuring, I tried ping and it started with timed out requests. So much for easy, but knowing my environment (dual-homed Win2k8 VM with one interface connected on the physical interface of my Vista system and also a non-IPv6 ADSL-router plus a WinXP SP2 with IPv6 installed), I started troubleshooting with IPv4. Router okay, Vista okay, WinXP not okay (turned out to be the firewall, disabled it). Next I tried pinging the WinXP system again on IPv6, twice! The first Win2k8 VM always tried first on the non-connected interface (which has ZoneID 3), and then the connected interface with ZoneID 2. Same story when pinging the Vista host. Also Win2k8 quickly forgets the interface it used to successfully connect to the two clients. Forgetting about the interface to use is quickly solved by including the (local) zone ID though, which basically represents the interface trough which the other systems can be reached. So ping fe80::5581:4002:53a2:fef1%2 or something the likes based on your environment should prevent failure (or have a properly setup infrastructure ;) ) You can view what IPv6 knows about the surrounding network via netsh interface ipv6> show neighbors.

Pinging the Win2k8 WM from the two Windows clients didn’t work; again it’s the firewall which is enabled by default on Windows Server 2008. I disabled it through netsh firewall>set opmode DISABLE and pinging the connected interface worked, naturally pinging the disconnected interface doesn’t work as the server isn’t configured as router.

All of a sudden another question popped my mind (those poor 70-431 candidates completely taken by surprise): simulations!!! I didn’t read anything about them yet, so I Googled a bit and landed at Trika’s blog (where else ;) ): Are there simulations on the upgrade exams? No.

Afterwards I installed DHCP and DNS servers on the Win2k8 Core using ocsetup (warning: case-sensitve). DNS Server Service started, DHCP Server wouldn’t. The later indicating through net start “DHCP Server” it is disabled or has no associated devices. Trough netsh dhcp> I got the impression it needed Active Directory.

Not having a graphical UI in these circumstances is no help, so I wanted to see how far I could get from Vista… not far until I gave the administrator a password (not new to Windows Server 2008, but one to remember: a user account without password is inaccessible from the network (under the default policy settings)). Not much use either; I could initially connect with computer manager now, to have the errors thrown at my head one level deeper. I guess I will need a full install, partially to be able to manage the server and to be able to setup Active Directory. Now installing the new VM…

Sunday, July 22, 2007 11:48:50 PM (W. Europe Daylight Time, UTC+02:00)
# Saturday, July 21, 2007

Another day with some hours of preparation for the 70-649. Although I have enough resources to keep me company until August 3rd, I do still spend some time looking for real gems (and keeping a tap on the buzz for this wave of beta tests). It was through Technorati and Elan Shudnow’s Blog that I learned about Keith Comb’s Blahg. This particular nerd on the grid has a series of Screencasts (currently 5, with a duration between 5 and 18 minutes) on Windows Server 2008. From the looks of his blog, he’ll frequently pour out useful info, so his feed is added to my reader (and blogroll).

What did I do besides watching Keith’s screencasts? I compared the skills being measured between 70-648 and 70-649 (and thus added some topics to the list), have been reading through the reviewers guide and watching the IPv6 white paper as downloaded webcast (sit back and relax). I can recommend the downloaded version, it's easy to pause and if needed go back a slide, very welcome as it is nearly two hours of information. Second tip is about IPv4, if you feel your IPv4 kowledge is sub-optimal, first review your IPv4 stuff. It's on the skills list too and the webcast refers quite a bit to your IPv4 knowledge. From the webcast I learned there are parts of the white paper itself I will read for further understanding.

Saturday, July 21, 2007 12:18:03 AM (W. Europe Daylight Time, UTC+02:00)
# Friday, July 20, 2007

Okay, so 70-648 is a subset of 70-649? Well almost;

  • 70-648 has more skills on "Maintaining the Active Directory Environment"
  • There is a smal difference in skill when it comes to "Configuring the Active Directory Infrastructure"
  • 70-648 has the extra skills domain of "Configuring the Domain Name System (DNS)"

See the full compare in the table below (based on the prep-guides as they were on 2007-07-20). Changes are not likely during the beta round, which by the way ends August 3rd, but I'd be surprised if there were still seats to be taken. However, should you hit this page when the exams are live, be sure to check the (then) current skills at their respective links: 70-648 & 70-649

70-649 6416A 70-648 6415A 6416A
Configuring Network Access Configuring Network Access  
Configure Remote Access. X Configure Remote Access. X  
Configure Network Access Protection (NAP) components. X Configure Network Access Protection (NAP) components. X  
Configure Network Authentication. X Configure Network Authentication. X  
Configure data transmission protocols. X Configure data transmission protocols. X  
Configure wireless access. X Configure wireless access. X  
Configure certificate services. X Configure certificate services. X  
Configure DHCP. X Configure DHCP. X  
Configure IPv4 and IPv6 Addressing. X Configure IPv4 and IPv6 addressing. X  
Configure Routing. X Configure routing. X  
Configuring Terminal Services  
Configure Terminal Services Remote Programs. X      
Configure Terminal Services Gateway. X      
Configure Terminal Services load balancing. X      
Configure resource allocation for Terminal Services. X      
Configure Terminal Services licensing. X      
Configure Terminal Services client connections. X      
Configure Terminal Services server options. X      
Configuring a Web Services Infrastructure  
Configure File Transfer Protocol (FTP) Server. X      
Configure backup. X      
Configure Web applications. X      
Configure Application Pools. O      
Configure IIS components. X      
Publish IIS Web sites. X      
Migrate sites and Web applications. X      
Configure SMTP service. X      
Configure Universal Description, Discovery, and Integration (UDDI) service. X      
Configuring Security for Web Services  
Configure handlers to reduce attack surface. X      
Configure .NET Trust levels. X      
Configure authentication. X      
Configure rights. X      
Configure permissions. X      
Configure authorization. X      
Configure certificates. X      
Deploying and Monitoring Servers Deploying Servers  
Configure Windows Deployment Services (WDS). X Configure Windows Deployment Services (WDS). X  
Capture WDS images. O Capture WDS images. X  
Deploy WDS images. O Deploy WDS images. X  
Configure Windows Activation. X Configure Windows activation. X  
Create virtual machines. X Create virtual machines. X  
Configure Virtual Server settings. X Configure Virtual Server settings. X  
Install Windows Server Enterprise. X Install Windows Server Enterprise. X  
Install server core. X Install server core. X  
Configuring Server Roles Configuring Server Roles  
Implement server roles by using Server Manager. X Implement server roles by using Server Manager. X  
Configure ADLDS. X Configure ADLDS. X  
Configure ADRMS. X Configure ADRMS. X  
Configure the Active Directory server core. X Configure the AD server core. X  
Configure the read-only domain controller (RODC). X Configure the read-only domain controller (RODC). X  
Configure Active Directory Certificate Services. X Configure AD Certificate Services and PKI. X  
Configure Active Directory Federation Services (ADFS). X Configure Active Directory Federation Services (ADFS). X  
Maintaining the Active Directory Environment Maintaining the Active Directory Environment  
Configure backup and recovery. X Configure backup and recovery. O X
Perform offline maintenance. X Perform offline maintenance. O X
Configure custom application directory partitions. X Configure custom application directory partitions. O X
    Configure AD DS auditing. O X
    Configure audit policy by using GPOs. O X
    Monitor Active Directory. O X
Configuring the Active Directory Infrastructure Configuring the Active Directory Infrastructure  
Configure communication security for Active Directory. X Configure communication security for Active Directory.   X
Configure the global catalog. X      
    Configure authentication. X  
    Configuring the Domain Name System (DNS)  
    Configure zones. X  
    Configure zone resolution. X  
    Configure DNS client settings. X  
    Configure DHCP and WINS for DNS. X  
Friday, July 20, 2007 10:24:52 PM (W. Europe Daylight Time, UTC+02:00)
# Thursday, July 19, 2007

What's up with "Configure Windows Activation"?

The reviewers guide talks about Windows Activation Service and Windows Process Activation Service as if it is one thing. Search through the guide on WAS and WPAS (search case-sensitive, was is a pretty common word ;)). Now it could be that this is important, because from what I remember from the things I read on one of the links below, IIS7 is a very major topic. However, I still feel it's that nasty Windows Product Activation that is featured under "Configure Windows Activation". This feeling is based on the location in the prep-guide and this line in the reviewers guide.

In addition, because product activation can be done within a grace period (typically 30 days), and is not critical for the initial configuration of the server, the Activate Your Server command, present on the Manage Your Server window in Windows Server 2003, has been removed from Initial Configuration Tasks.

That's however all I found on product activationin the reviewer guide (maybe that, and how to invoke activation after deployment is all you need to know on the exam).

Experiences from others on 70-649 in general:

http://www.mcseboard.de/mcse-forum-pruefungen-33/mcse-mcsa-upgrade-2008-beta-pruefungen-117512.html (German; forum which had already 3 pages in the thread when I checked)




Thursday, July 19, 2007 11:47:37 PM (W. Europe Daylight Time, UTC+02:00)

Yesterday I registered for the transitioning beta-exam for MCSE 2003 to three MCTSes for Windows Server 2008, I also started my preparations towards this exam.

First thing, create a new virtual machine with Virtual Server 2005 and install Windows Server 2008 Beta 3 on it. This takes a while, so I started hunting for resources. By looking at the prep-guide you’ll learn that 70-649 is comprised of the exams 70-640, 70-642 and 70-643 (oh yeah, for the people transitioning their MCSA 2003, leave out 70-643).

I looked at the topics and most of them already are familiar from Windows Server 2003, but there were a few where I know I have to dig in to:

  • IPv6.
  • UDDI.
  • WDS (though my first guess I’ll find a lot of similarities with RIS).
  • Windows Activation (currently I just hate it, if it is what I think it is).
  • The whole story on Server Roles.

Some resources are directly pointed at from the prep-guide, others I already know and I think it’s useful to share them here. I’ll just restrict myself to the free resources:

IPv6-stuff start’s here, has a White Paper from MS and somehow I have a gut feeling this page will be important.

Windows Activation, I’m tapping a bit in the dark currently on what this is. My first (dreadful) guess is nagging product activation. But searching the Microsoft sites I also found some references to phrases like “Windows Activation Service” and “Windows Process Activation”. If anyone could shine a bit of light on the subject, you can comment on this blog for free.

Not free, but worth mentioning: 6416A, both as Instructor Led Training ($/€ = ?, Three day course) maybe at a CPLS near you or as E-Learning ($ 319.99, Three year subscription).

That’s it for now, though I have little time left for my preparations, I will blog about them as much as I can. Including other resources I encounter and the impression and result on the exam. So stay tuned on RSS or Atom.

Thursday, July 19, 2007 9:58:11 PM (W. Europe Daylight Time, UTC+02:00)
# Wednesday, July 18, 2007

MCSA/MCSE 2003 invited to beta WS2008 transition exams...

Just scheduled mine, going to have a shot at 71-649 August 3rd (if I don't have to reschedule). The exam is called Transitioning your MCSE on Windows Server 2003 to Windows Server 2008 Technology Specialist. Smart move, not calling it upgrading, even though you get three MCTS certification from this one exam. Important to note, this exam is fully tied to your MCSE 2003 status. Don't have one? Don't go for this exam, you won't receive any credits from it.

Similar, there is a transitioning exam for MCSA 2003 as well: 71-648. It credits you for two MCTS certifications (yes a subset from the MCSE transition). Again, be MCSA, or you'll be wasting your time.

Want more details or the promo-code, go check out Trika's blog.
Meanwhile I've no time to delay building my Windows Server 2008 image Virtual Server and start looking for resources to aid me with my studies (as always, the prep guide will not be spelling out all you need to know, but I'll be checking it out anyway).


Wednesday, July 18, 2007 4:43:44 PM (W. Europe Daylight Time, UTC+02:00)
# Tuesday, July 10, 2007

Clinic 7045: What's New in Microsoft® SQL Server™ 2008

While I was over at Arlindo's Blog for VRMCplus, I couldn't resist clicking SQL Server 2008 in his tag-cloud. Next I grabbed my passport (euh LiveID) and headed to the 1.5 to 2 hour eLearning module (content will be available on your LiveID for a year).

Little topic overview:

  • A lot on Database Engine and SSRS, less on SSIS and SSAS.
  • FILESTREAM and spatial datatypes, as well as the other datatype enhancements/additions.
  • Mentioning the new management capabilities and how this fits in with MS-initiatives like DSI.
  • Mentioning the development features and integration (think ORM, EDM and LINQ).
  • New to me (hadn't noticed it before); Security Auditing for Data Protection. This will be quite useful when you can't (due to vendor support restrictions) implement auditing based on altered table definitions, check constraints and triggers. Or now don't want with SQL2k8 because it will be faster to implement ;).
  • Improved integrations with Office 2007 (and SharePoint).
Technorati tags: ,
Tuesday, July 10, 2007 1:00:15 PM (W. Europe Daylight Time, UTC+02:00)
# Wednesday, June 13, 2007

A little while ago Howard Dierking (MSFT, Certification Planner for developer stuff) shared his thoughts on doing exam development different. I liked the idea which aims to both lower the cost of building an exam and at the same time increase the quantity and quality of the questions. I added some feedback, so exams can be kept up to date, thus improving the quality of certification.

Wednesday, June 13, 2007 5:15:07 PM (W. Europe Daylight Time, UTC+02:00)
# Wednesday, May 30, 2007

...on your Windows Server 2008 upgrade certification. If you have MCSA or MCSE for Windows Server 2003 and want to continue the spree, click this link.

Wednesday, May 30, 2007 10:42:38 PM (W. Europe Daylight Time, UTC+02:00)
# Thursday, May 24, 2007

Yesterday I attended the MCP Live Meeting "Protecting the Integrity of Microsoft Certifications". There were actually two sessions (I attended the first). The "thing" that basically led to the Live Meeting is the Non-Disclosure Agreement (you know, those legal ramblings you have to agree to when you sit the exam).

The LM focused on the content of the NDA and Microsoft's efforts to protect the integrity of the exams and thereby its certifications. Naturally a lot of questions about TestKing were envisioned by the MS-people, so they included an update on the TestKing-case.

Also noteworthy was that the independent organizations co-hosting the LM (CertGuard and Mitch Garvis) provided some pointers as to why to certify and if certification would be of use to you. By the way, CertGuard is a site that may help you with a lot of questions regarding certification integrity, not just Microsoft. So if you have a question on materials or policies, you might want to click the banner below.

CertGuard: The StrongHold For Excellence In IT Certification and Exam Security

As for the rest of the LM, I'll keep an eye on Trika's blog to see when the recording and transcript are posted (and update this post).

<update date="2007-05-25">

Trika posted a summary and the link to the recording.


But I had some extra reasons to attend;

First was the recent inclusion of simulations in MCTS exam 70-431. Lots of people were caught by surprise by the (all of a sudden heavily scored) simulations. The simulations on the exams, also referred to as Performance Based Testing, provide a more real world opportunity of testing the skills of the candidate. Besides testing the skills, it also battles just learning the answers to known questions (braindumps). The downside was, that a lot of people weren't prepared and failed the exam (undoubtedly a lot of them for the right reasons; not knowing the product). Still Microsoft could have been more clear and open about the simulations (they do provide some info, tucked away on the learning-site). In the LM, Microsoft made it clear it is their desire to include simulations in all MCTS exams and other exams where applicable. So make sure you prepare and know the product.

My second reason was if I could find out how the NDA affects me as a Microsoft Certified Trainer. For one part the role of the trainer is to guide the students in their learning path, often towards a certification. Also, a trainer is required to pass the exams for the technology the trainer delivers courses. That makes the trainer knowledgeable on the exam, but to what extent does (s)he also disclose when preparing the students? To me this is where business models, my professional role and the exam NDA might conflict.

Thursday, May 24, 2007 6:05:22 PM (W. Europe Daylight Time, UTC+02:00)
# Monday, May 7, 2007

Yep, the revisions are out. Today I checked the Microsoft Learning site for the 2779 and 2780. The 2779afinal.mspx page was redirected to the 2779bfinal.mspx. Same thing for the course 2780. Both courses are now 5 instead of 3 days!

  • What's changed on the 2779b;
    • Module 5 2779a is split in two, one about data integrity with constraints and one about data integrity based on triggers and XML schemas
    • Module 8 2779a is split in two, stored procedures and functions now have their own modules
  • New content on 2779b;
    • Module on transactions and locks
    • Module on Notification Services

What's changed on the 2780b? If you look at the syllabus, you would be tempted to say nothing. But when you look inside, content is rewritten (and improved) like I mentioned for Cryptography. Also, there are no extra modules, but since it needed over 4 days to properly teach the old course, there was no need for extra content anyway.

Also be advised that Microsoft Learning is working on an Instructor Led Training for 2778 (currently only available as eLearning). I'll keep you posted on that one as well.

When you compare the course offerings to the exam 70-431, I'm missing content on the HTTP-Endpoints in 2779b. Therefor I will add a module on HTTP-Endpoints for my deliveries of the 2779.

Monday, May 7, 2007 2:55:11 PM (W. Europe Daylight Time, UTC+02:00)
# Sunday, April 29, 2007

Some useful links with personal impressions, valuable resources or important information and their date posted. Don't forget, always check the preparation guide (last update 2007-03-20), both as a road map for your preparation and just prior to taking the exam (maybe things have changed since you started).

The links prior to 2007 don't include info on the simulation questions. The first time I heard about the simulation questions was in December 2006, it seems they were running beta into March. Starting March 2007 I heard and read about failing exams because of the simulation questions, which seem to make up a very big part of the overall score for the exam.

Must read is the discussion on simulations (2007-04-12) started by Zieglers after he passed his 70-431 (2007-01-23). Some very valuable links can be found in the discussion, including this simulation example.

Also check out the database certification newsgroup microsoft.public.cert.mcdba. This newsgroup is aimed at all SQL Server exams and certifications, not just 70-431.

Other links:

If you use the MCTS Self-Paced Training Kit (Exam 70-431): Microsoft® SQL Server™ 2005—Implementation and Maintenance, check out the comments and corrections.

Please post a comment if you have other good and free resources as well.

*UPDATED 2007-05-05*

Sunday, April 29, 2007 10:38:37 AM (W. Europe Daylight Time, UTC+02:00)
# Saturday, April 28, 2007

Initially I started writing this article because of the poor quality of Module 4, Lesson 4 in the original version of the course MOC 2780. What's wrong with those 6 pages? Technically nothing, but if you've never seen what cryptography does in security systems, you're lost. Fortunately Microsoft Learning has acknowledged the course and its timetable were "suboptimal" and will be releasing the B-revision soon. From a MOC2780B (revised module 4, lesson 4) perspective, this article can be considered additional reading.

In much the same way as in MOC2780A, the entire Module 4 of the MOC 2787 is affected as well. Therefor reading this article is recommended in preparation of the exams 70-443, 70-444 and 70-447.

To be honest, I don't know how relevant the encryption stuff is towards the exam 70-431. I don't recall it from the beta exam I sat, nor have I read reports that specifically included cryptography. The preparation guide provides little guidance on it, it only says Configure encryption under Installing and Configuring SQL Server 2005, Configure SQL Server security, but that may be aimed at protocol encryption. Should you want to play at safe while preparing based on the MS Press Self-Paced Training Kit "SQL Server 2005, Implementation and Maintenance" (ISBN-10: 0-7356-2271-X), this article provides the necessary background for Chapter 2, Lesson 6. If you prepare for the exam based on the Sybex MCTS Study Guide "Microsoft SQL Server 2005 Implementation and Maintenance" (ISBN-10: 0-470-02565-4) this article is a must read, the Sybex book doesn't cover SQL Servers cryptography.


First I'll describe the tools needed for cryptographic security;

  • Cryptographic hashes (are not mentioned in courseware, but included here for completeness)
  • Key based encryption 
    • Symmetric keys
    • Asymmetric key-pairs (private key & public key)
    • Certificates
  • Passwords/passphrases

and then continue to encryption hierarchy, combining different forms of cryptography and the double protection of keys and certificates. 

Cryptographic hashes

Cryptographic hashes are in security related documentation mostly referred to as just hash. However in a database context, you may also read about hashes in the terms like hash indices and hash joins. Hash indices and hash joins are related to the query processor and have nothing to do with the security subsystem. So when you encounter hash in SQL Server documentation, verify if it is meant cryptographic or related to the query processor. In the remainder of this article, the term hash will refer to a cryptographic hash.

Now for what a hash does, a hash is based on some input of any length. This input is divided in blocks and based on these blocks, calculations are performed. These combined calculations are called the algorithm. One of the characteristics of the algorithm is that it's output has a fixed length. In SQL Server the following algorithms for hashes can be used, with the output length in bits between parentheses; MD2(128), MD4(128), MD5(128), SHA(160) aka SHA0, SHA1(160). The output of the hashing operation may be referred to as hash or as Message Digest, the input of a hash operation may also be named Message. The most important characteristic of a hash is that it is one way, so you can calculate the Message Digest of a Message, but it isn't possible to retrieve the Message if you only have the Message Digest.

You can call a hash function from SQL Server directly, see HASHBYTES for more info.

The typical application for hashes is to verify the original content, without having to store the original content. For example with passwords, where systems typically store the password hash, not the password itself. When the user types the password, it is hashed and compared to the stored hash. If they match, the user has proven to know the password. You may also encounter hashes with downloads, where the (MD5) hash of the downloadable file is posted. After you downloaded the file, you can compute the hash of the file. When it matches the posted hash, the download was successful. Check out winMd5Sum as example.

Key based encryption

When encrypting information, the original information is referred to as plaintext and the encrypted information as ciphertext (or cyphertext). During the encryption operation a key is applied to the plaintext based on an algorithm (the keys' properties must match the algorithm), resulting in unintelligible ciphertext. The only way to read the ciphertext is by decrypting it, which again involves applying a key to the ciphertext based on the corresponding algorithm.

The words plaintext and ciphertext could be a little misleading, as you might think of it as text, but plaintext and ciphertext can be binary too. Specific for SQL Server 2005, the key based encryption functions can handle the data types; char, varchar, nchar, nvarchar, binary, varbinary. Other data types should be casted as one of the afore mentioned datatypes, also the datatypes are limited to 8000 bytes (actually the encrypted data is limited to 8000 bytes, which means the plaintext usually is shorter based on the algorithm used)!

Symmetric keys

With symmetric keys, the algorithm performing the operations uses the same (symmetric) key for both the encryption and decryption operation. Symmetric keys are considered fast for cryptographic operations in comparison to asymmetric keys, naturally the real speed of the encryption and decryption depends on the encryption algorithm, the length (in bits) of the symmetric key and the available processing power. With encryption, the algorithm is bound to the symmetric key, so during creation you must specify for which algorithm (DES, DESX, Triple DES, RC2, RC4, AES 128, AES 192, AES 256) the key is created. The second requirement when creating a symmetric key is that it is encrypted. It may sound strange, encrypting a key, but in reality the key is the most vulnerable part in cryptographic systems. By encrypting the symmetric key (that may have encrypted numerous fields in the database), we prevent someone who has no access to the decryption of the symmetric key, to access the data protected by this key. At the end of this article, you will see that you can build/use a hierarchy of keys that will enable you to keep numerous secrets by guarding only a few.

Creating a symmetric key is done via CREATE SYMMETRIC KEY. After creation, the key is stored in the database and information about the key can be retrieved from the system view sys.symmetric_keys. As you may see in this view, the key has a name and a GUID (you'll also see the columns for storing the algorithm). Both the name and the GUID are important when identifying the key in encryption and decryption operations.

To make use of a symmetric key, this symmetric key should be open. This has to be done because the symmetric key is stored in an encrypted state. So you can only make use of the symmetric key when you have access to the key or know the password that was used to encrypt the symmetric key. For syntax, see OPEN SYMMETRIC KEY.

The final step is using the symmetric key to encrypt and decrypt information. This can be done using the functions EncryptByKey and DecryptByKey. Note that you can include an "authenticator", which basically is a property of the record that you can encrypt along with the data.

To see how the symmetric key based encryption should be used, please take a look at the example by Laurentiu Cristofor.

Asymmetric keys

With asymmetric keys, the algorithm performing the cryptographic operations uses a key pair. The keys of this pair (let's call them Pub and Priv right away) match in such a way that one key can undo the operation of the other key. So if you have plaintext and encrypt this with the key Pub, the resulting ciphertext can only be decrypted with Priv. The other way around, when you use Priv to encrypt some plaintext, the only way to decrypt the resulting ciphertext is using Pub.

By calling the key-pair Priv (private key) and Pub (public key), the main area of use is very clear. While a person or system can share it's public key with the whole world, the private key is kept secret. Now let's say that two people, Alice and Bob, want to exchange information, without anyone else being able to learn about the exchanged information. Both Alice and Bob have a private key (which each of them keeps a secret) and a public key (known to the both of them and the rest of the world). Now Alice wants to leave Bob a message to.... (well, it's to remain unknown to the rest of the world), so Alice encrypts the message with Bob's public key (Pub-B). With Bob being the only one who has access to Bob's private key (Priv-B), Alice knows only Bob can decrypt the message and she can safely store the encrypted message for Bob to read. After decrypting the message, Bob wants to answer Alice in an equally secure manner, so he uses Pub-A to encrypt the message. Even with everybody being able to retrieve the encrypted message, only Alice can decrypt it, for she has Priv-A.

Alice encrypts and stores:

EncryptPub-B(Plaintext_1:I am Alice) = Ciphertext_1

Bob retrieves and decrypts:

DecryptPriv-B(Ciphertext_1) = Plaintext_1:I am Alice


Bob encrypts and stores:

EncryptPub-A(Plaintext_2:I am Bob) = Ciphertext_2

Alice retrieves and decrypts:

DecryptPriv-A(Ciphertext_2) = Plaintext_2:I am Bob

Besides preventing information disclosure, asymmetric keys can also provide authentication. In the previous information exchange, Alice and Bob wanted to keep the information undisclosed. But what if Oscar wants to trick Bob and pretend he is Alice. Oscar would only have to fetch Bob's public key, encrypt the message with Pub-B and state in the message that he is Alice.

Oscar encrypts and stores:

EncryptPub-B(Plaintext_1:I am Alice) = Ciphertext_1

Bob retrieves and decrypts:

DecryptPriv-B(Ciphertext_1) = Plaintext_1:I am Alice

To prevent Oscar (or anyone else) from manipulating the flow of information, Alice and Bob agree to encrypt the message with their private keys prior to storing. So Alice has a message; "plaintext" and encrypts it with Pub-B (she now knows only Bob can decrypt it). Next she encrypts the resulting ciphertext with Priv-A and stores that message. When Bob retrieves that message, he uses Pub-A to decrypt the first stage, this verified that the message was encrypted by Alice (as only she has access to Priv-A), next he decrypts the message with Priv-B and now has access to the plaintext stored by Alice.

Alice encrypts and stores:

EncryptPriv-A(EncryptPub-B(Plaintext_1:I am Alice)) = Ciphertext_1

Bob retrieves and decrypts:

DecryptPub-A(DecryptPriv-B(Ciphertext_1)) = Plaintext_1:I am Alice


Bob encrypts and stores:

EncryptPriv-B(EncryptPub-A(Plaintext_2:I am Bob)) = Ciphertext_2

Alice retrieves and decrypts:

DecryptPub-B(DecryptPriv-A(Ciphertext_2)) = Plaintext_2:I am Bob

So it doesn't matter that Oscar has access to all public keys and can retrieve the ciphertext, because Priv-A and Priv-B are a secret held by their respective owners, Oscar can't interfere with this process. The outer encryption, where the originator uses its private key, is also referred to as signing. Also note that a message does not have to be encrypted to be signed.

Alice signs and stores:

EncryptPriv-A(Plaintext_1:I am Alice) = Ciphertext_1

Anyone retrieves and verifies:

DecryptPub-A(Ciphertext_1) = Plaintext_1:I am Alice

Like with symmetric key, asymmetric keys can be created with a T-SQL CREATE statement and viewed through a security catalog view; CREATE ASYMMETRIC KEY and sys.asymmetric_keys. Asymmetric keys are based on the RSA-algorithm with keys of 512, 1024 or 2048 bits. As you learned above, the private key is to remain secret, so in order to protect it, the private key has to be stored in encrypted form. Unlike symmetric key based encryption and decryption, with asymmetric key based operations the keys do not need to be opened. However when performing an operation that requires access to the private key, the private key must be decrypted during the operation. The following functions are performed with the public keys; EncryptByAsmKey and VerifySignedByAsmKey. The operations performed with a private key are; DecryptByAsmKey and SignByAsmKey.


Very close to the asymmetric keys are certificates. In fact, nothing changes on the side of the private key and the public key. The certificate is only used to store properties associated with X.509 v1 certificates and associate those with the public key, read Laurentiu's post for more info. Because certificates are named different from asymmetric keys, you'll get different syntax and another security catalog view; CREATE CERTIFICATE, sys.certificates, EncryptByCert, VerifySignedByCert, DecryptByCert, SignByCert. However certificates do have a major advantage over asymmetric keys; you can backup a certificate (certificates were designed with .CER files in mind); BACKUP CERTIFICATE.

Passwords and passphrases

As you may have noticed in the syntax for creating and opening symmetric keys and when creating or using the private key on asymmetric keys and certificates, there is the option to use a password (ENCRYPTION BY PASSWORD='@v3RyCo/\/\pl&xPa$suu0rD'). When a password is provided on creation of the key or certificate, that password is used as a sort of symmetric key to provide the necessary encryption. Also, passphrases can be used to encrypt data directly without the need for any keys and key handling. This can be done with the functions EncryptByPassPhrase and DecryptByPassPhrase. Passwords and passphrases are essentially the same kind of thing, though we see passwords as hard to guess character strings and passphrases as long but easy to remember phrases. Passphrases typically contain a lot of spaces, whereas passwords contain mixed case, numbers and symbols and are usually enforced by a password policy.

Encryption hierarchy

Encrypting keys and certificates with a password is one option of protecting that key or certificate, but it does involve a lot of password management. Another option is building an encryption hierarchy in SQL Server 2005. To be able to explain the hierarchy, two special symmetric keys must be introduced;


Service master key

The service master key is created when SQL Server 2005 is installed. The key is created based on and protected with the credentials of the Windows Account that is used as the SQL Server Service account. The service master key is used to encrypt password which are stored in the master database (like SQL logins and credentials for linked servers), also this key acts as the root for the SQL Server encryption hierarchy. The service master key can not be created or dropped, it can however be altered, backed up and restored. In fact, backing up the service master key is recommended right after installing the SQL Server instance. See ALTER SERVICE MASTER KEY for more information.

Database master key

The database master key can be created as a starting point for encryption in the database. This can be done with the statement CREATE MASTER KEY which must include the ENCRYPTION BY PASSWORD. This creates the master key in the database (encrypted by the password and by the service master key), also the database master key is stored in the master database where it is encrypted with the service master key. The database master key is stored in the master database to facilitate automatic decryption. It is possible to drop the copy from the master database, but then it is required to manually open a database master key prior to using it. Like the service master key, it is best practice to backup the database master key too. See CREATE MASTER KEY for more information.

Encrypting asymmetric keys and certificates

When the ENCRYPTION BY PASSWORD directive is omitted for an asymmetric key or certificate when it is created, the master key of the database where the asymmetric key or certificate will be stored is used to encrypt the private key. This way, the new key or certificate is automatically tied into the encryption hierarchy of SQL Server. Asymmetric keys and certificates can only be encrypted once, so when you alter them, you can switch between encryption by the database master key and encryption by a password, or in the later case change the encryption password. 

Multiple encryptions of symmetric keys

Normal symmetric keys and database master keys can be encrypted more than once. For the database master key, this is very convenient, as it is both possible to use cryptographic functionality transparently (based on the encryption by the service master key) and transfer the database to another instance while preserving the encryption hierarchy (based on the encryption by password). For normal symmetric keys this is very convenient as well, as multiple users may need access to the same encrypted data. The bulk of the data (think like thousands of records with an encrypted field) does not need to be encrypted multiple times and can be accessed through the same symmetric key. This symmetric key is, depending on the capabilities users/processes, accessible through one or more passwords, other symmetric keys, asymmetric keys and/or certificates.

Combining cryptographic operations

Typically symmetric and asymmetric key (or certificate) based encryptions are combined to achieve the desired security level, while still maintaining good performance. If you revisit the example, you'll notice that the data in the column is encrypted with the symmetric key (remember: symmetric key = fast). The table in the example only has 2 rows, but that same table could hold millions of rows, making the algorithm doing the encryption and decryption very important in terms of performance. Because all this data is encrypted with a single symmetric key, this key should be well protected. The example used a password to encrypt the key, but a very common approach is to secure the symmetric key with a certificate (or asymmetric keys, which essentially comes down to the same). As a non-SQL Server example, EFS takes the same route, using a symmetric key to encrypt a potentially big file and then encrypting that symmetric key with the public key for each user that should have access to the file.

Now for SQL Server 2005, Laurentiu has a great 2nd example where he uses the possibilities offered by the encryption hierarchy. A database master key is created and used to encrypt the certificates for all users (dbo and Charlie) participating in the example. The certificates are then used to encrypt the symmetric key and the symmetric key will be used to encrypt the (2, but potentially thousands) salaries in the t_employees table.

Interesting to note about the 2nd example is the function DecryptByKeyAutoCert. The main advantage of this function is that it utilizes the encryption hierarchy and transparently opens keys on demand (and closes them after the operation completed). Similar functionality is provided by the function DecryptByKeyAutoAsymKey, if the symmetric key is encrypted with an asymmetric key.

Permissions on keys and certificates

One important thing not yet mentioned about the keys and certificates, is that they are securables. So in order to use them, a principal should be granted the necessary privileges on the keys and certificates. If you look closer at the second example, you see it is no issue at first, as everything is done as dbo, so dbo automatically is owner (and in full control) of the keys and certificates created. But when Charlie makes his appearance, he must be granted the necessary privileges; naturally select on the views, but also VIEW DEFENITION on the symmetric key (to be able to use it) and CONTROL on the certificate. Alternately Charlie could have been made owner of the certificate when it was created through the AUTHORIZATION directive. Bottom line is, to use a key or certificate, you must be granted the proper access to the key or certificate and be able to decrypt it. For more information on permissions, see GRANT.

Cryptography and authentication

What this article doesn't cover is signing programmed modules and mapping users and logins to certificates. However, I will add those topics when covering impersonation (EXESUTE AS on the todo-list).

Recommended reading: Handbook of Applied Cryptography, Laurentiu Cristofor's blog, Wikipedia. Not exactly reading, but check out those 4 presentation from the 2006 PASS conference. 

Saturday, April 28, 2007 9:34:46 PM (W. Europe Daylight Time, UTC+02:00)
# Friday, April 13, 2007

If you're looking for the SQL Server 2000 perspective, go to Space Program.

Before venturing further, when was your last checkpoint? I learnt in my test setup, that if you work with a small number of operations, there is no data recoverable from the data file. It was just on the dirty pages and in the transaction log (which I deliberately crashed, to be able to advise on recovery of that event). This is an indicator that, if you lose the transaction log in a running database, you are very likely going to end up with data loss.


...continued from Recovery when the transaction log is lost, if the database was detached or you want to connect the database to another instance.

Unfortunately, after the failed attach, there is no entry for the LogCrash_Demo database in the system tables anymore. So first you have to create a new database with the same name. Next take this database offline and replace the data file of the newly created database with the datafile containing the data you want back. Also, throw away the transaction log that was just created.

Now proceed to the next section, where the database is started in emergency mode.


...continued from Recovery when the transaction log is lost, if you didn't detach the database.

Start the LogCrash_Demo database in emergency mode (switch to emergency mode has changed with SQL Server 2005, as it required direct updating to system tables, which are now hidden from the DBA).


Now you can read from the database, which is enough to script the definition of the objects (SSMS: right-click the database, Tasks --> Generate Scripts), create a new database based with those scripted objects, and transfer (BULK INSERT, SQL Server Integration Service, etc.) the data from the database in emergency mode, to the new database.

Be careful though about data loss. Data data file that was made available for select in emergency mode does not contain the dirty pages.

So should you be worried about crashing of the transaction log media, make sure you use at least disk mirroring for your transaction log. Because when you look at backup restore as in the 2780, you can rebuild your data files after a disk crash using the right backup and restore strategy without data loss, but you can't if your transaction log is the disk crash victim.


Technorati tags: , ,
Friday, April 13, 2007 2:43:23 PM (W. Europe Daylight Time, UTC+02:00)

When does SQL Server, under its default settings, write dirty pages to disk?

The answer depends on your recovery model, but let's assume Full Recovery. In that case, either when the lazy writer kicks in or when a checkpoint is issued.

-- (Relevant) Memory usage by SQL Server --

To simplify, SQL Server needs datapages in memory to work with them. To do so, SQL Server uses available memory from the system for a buffer pool. This buffer pool is filled with datapages, while SQL Server has the opportunity to do so (and when memory runs out on the system, SQL Server will release memory from its buffer pool). When one or more records are SELECTed from the datapage, the datapages containing these records are either already present in the buffer pool, or read from disk. When the datapages are modified (like in UPDATE, DELETE and INSERT), SQL Server carries these operations out on the pages in memory (and also writes the modification to the disk-bound transaction log). When the data page modification is completed, the data page stays in memory and is marked dirty (because the page in memory and the corresponding page in the datafile are different).

There are also times when the buffer pool needs to be cleaned up, either because it's full and more needs to be stuffed in (like my garage) or because you want to clean it out (no real-world example applies to me here).

-- Lazy Writer and Checkpoint --

In the first case, where more data pages need to be stuffed in, the lazy writer gathers (some of) the dirty pages and has them written to disk. That way the memory that was occupied by those dirty pages can be reused. In the second case, where you want to clean the dirty pages, you issue a checkpoint, thereby ordering SQL Server to flush the dirty pages to the data file(s) on disk.

This way issueing a checkpoint, has the positive side-effect of making memory in the buffer pool available for reuse. Side note; neither the lazy writer, nor checkpoint, free data pages. They just mark them safe for reuse.... but wait a minute, cleaning out the buffer pool (or the garage), isn't that a must do??? To be honnest, only when it's full!

So when does a checkpoint occur? When a database is closed (like when taking the database offline, stopping the service) or when a backup is made, as part of the opperation a checkpoint is issued. Besides those events, there is also the option to programatically/interactively issue a checkpoint, finally SQL Server issues checkpoints automatically, based on the recovery interval.

-- Recovery --

There is a more compelling reason (compared to buffer pool maintenance) to issue a checkpoint every now and then. It is recovery!

And why is recovery important? When a database starts up, recovery is one of the processes. During recovery, SQL Server looks up the last known checkpoint in the transaction log and then starts redoing the committed transaction from the transaction log. If the database was closed clean the last time, the checkpoint is the last thing in the transaction log, so recovery costs next to nothing. But if the database wasn't closed nicely (like when the power supply unit fails), then it may be hours (or longer) since last checkpoint. SQL Server then starts redoing from the transaction log, this may take some time, but at least you don't lose commited transaction.


YES, I DID! That is, pull the memory stick with the transaction log out, to simulate a failure of the disk. And that's where it hurts as data pages only get flushed to disk if;

  • Both the buffer pool and system memory are full (or memory is capped with max server memory). Based on a few inserts and updates... don't think so.
  • The database is closed or backed up. Not the case when you pull the stick.
  • A manual CHECKPOINT is issued. Didn't do that.
  • Recovery interval kicks in. Waited just for that to happen...

However, the recovery interval of 0 does not mean SQL Server will issue a checkpoint automatically within a minute. It says, checkpoints do occur when the database engine estimates it can process the number of log records within the interval specified during the recovery process! In other words, it may take very long between checkpoints if a system is running very few transactions... like I noticed during my tests.

Technorati tags: , ,
Friday, April 13, 2007 2:41:49 PM (W. Europe Daylight Time, UTC+02:00)
# Monday, April 9, 2007

"Let's check and see..." and we came back with no license info on SQL Server. Yet the course certainly gives an overview of the various licensing options, so does the Microsoft website. However, when installing SQL Server, you're not asked about licensing. Also the queries SELECT SERVERPROPERTY('LicenseType') and SELECT SERVERPROPERTY('NumLicenses') both return DISABLED.

By design, the product SQL Server 2005 does nothing in terms of enforcing or even storing license information. Eric Burgess mentions this on the Microsoft SQL Server Support Blog, including how to store the license information in the Windows Registry, as was done with SQL Server 2000.

Okay, that for the technical detail, but what remains puzzling is Microsoft's current strong/aggressive campaigning on licensing versus no instruments in SQL Server.

Technorati tags: , , ,
Monday, April 9, 2007 1:57:52 PM (W. Europe Daylight Time, UTC+02:00)
# Wednesday, April 4, 2007

Recently I finished teaching a couple of courses, to be more specific; the Microsoft courses 2779 and 2780. This resulted in a little todo-list for postings on the blog, either because time was lacking or because I needed to look up some details to properly answer the question.

In the next couple of days, I will post answers (if I can answer the question) on the following questions;

As the answer start appearing on the blog, I will update this post, linking to the answers. Another answer is also relevant in this context, the things that changed when doing full server recoveries.

Wednesday, April 4, 2007 7:35:02 PM (W. Europe Daylight Time, UTC+02:00)
# Saturday, March 24, 2007

Does your backup and recovery strategy take the new SQL Server 2005 mssqlsystemresource database into account? What's different for SQL Server 2000 and 2005 from backup/restore perspective.


Compared to SQL Server 2000, SQL Server 2005 has a lot of nice features aimed at minimizing downtime during "unplanned events", most notably piecemeal restore and the inclusion of Full-Text indexes in the database backup and restore. However, before being able to do database restores, a functional database server is needed. Besides being a SQL Server instance, the database server also is at least a Windows operating system and hardware (or a virtual machine if you like). The later two usually are the domain of the system administrator who will also take care of the operating systems, including installed applications, being backed up. Likewise the DBA will take care of databases.

A typical system backup will exclude certain files and directories known to be always locked, like pagefile.sys and the SQL Server \Data folder. The other SQL Server folders and registry-setting (system state), are backed up. By restoring this backup for a SQL Server 2000, you get a database server with the Service Pack level of at time of the backup. The only thing that's missing for the database to start is the master database. In SQL Server 2005 only the master database isn't enough to get you started, you need mssqlsystemresource a.k.a. Resource Database as well.

What has changed that this is an important issue in the last step of getting a SQL Server instance running (quickly). The master database in SQL Server 2000 holds all information, the configuration related to the instance (like; logins, linked servers, databases) and the definition of system objects. When applying a service pack, typically maintenance on the system object is done, so the master database changes and is therefor Service Pack dependant. In SQL Server 2005 these two types of information are split, instance configuration still goes into the master database, but the system objects are held in the Resource Database. The advantage is that master is not depending on the Service Pack and since none but Microsoft should alter system objects, the Resource Database can remain read-only and will only be changed by Microsoft when applying the Service Pack.

When disaster strikes the (original) server, in many cases, a restore of the file system and system-state is performed on a more or less comparable machine configuration. This is the quick route, compared to installing Windows, a new instance and applying the Service Pack. The quick route has two extra advantages, no access to installation media is needed and other applications are restored too. But the quick restore route does not bring back a fully functional server, both sysadmin and DBA are aware of that. Therefor for this procedure to be successful, an offline backup of master (copy master.mdf and mastlog.ldf) was done after installation and application of a Service Pack. Now the last step is to copy master.mdf and mastlog.ldf to the SQL Server \Data folder. From here the DBA can start his/her database restore sequence (starting with the latest online backup of master (2000/2005)). This approach works on SQL Server 2000.
For SQL Server 2005, you have to add include the Resource Database in this strategy. And here is a little (positive) difference, because the Resource Database is accessed read-only, it only has read locks and thus can be copied while the instance is running. So you can either copy it after the installation or when the Service Pack is applied, or you can alter the file system backup to specifically include the files mssqlsystemresource.mdf and mssqlsystemresource.ldf.


What if disaster struck your SQL Server 2005 instance and you haven't cared for the Resource Database... well then you're off to the slow route of installing the instance after the restore and applying the Service Pack, before the database restore sequence can be started.

Saturday, March 24, 2007 2:29:51 PM (W. Europe Standard Time, UTC+01:00)