# Monday, 11 February 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, 11 February 2008 23:05:42 (W. Europe Standard Time, UTC+01:00)
# Wednesday, 06 February 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, 06 February 2008 17:52:41 (W. Europe Standard Time, UTC+01:00)
# Tuesday, 22 January 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, 22 January 2008 16:12:30 (W. Europe Standard Time, UTC+01:00)
# Thursday, 17 January 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, 17 January 2008 18:40:39 (W. Europe Standard Time, UTC+01:00)
# Tuesday, 15 January 2008

A while back I wrote about Overridable IDENTITY or the order of things, offering a solution to store data in natural order. This solution worked form me in a couple of projects, until I recently had to reload a table... so the basic procedure would be to;

  • Create a new table based on the same definition
  • Do an INSERT new_table SELECT ... FROM old_table
  • DROP TABLE old_table
  • sp_rename 'new_table', 'old_table'

But this does not produce the proper result, as the most recent inserted identity prior to the insert will be applied for the default.

The trick is to make every insert an independent action, so the IDENTITY_CURRENT is properly set for every row; make it run in a cursor. Yeah, I know.... performance, but that pain is largely taken away by making it a FAST_FORWARD. Besides, if anyone has a better suggestion, I'm all ears. So instead of a INSERT ... SELECT, the CURSOR is opened for the selection and the new table is inserted based on the fetched rows.

Altogether in the attached script;

  • Create the soon to be table.
  • Populate the table.
  • Create the new table, pay attention to the default.
  • Use the cursor to fill the new table.
  • Drop the original table and rename the new table to the name of the original table, don't forget to recreate the default.
Tuesday, 15 January 2008 21:53:29 (W. Europe Standard Time, UTC+01:00)
# Monday, 14 January 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, 14 January 2008 20:49:23 (W. Europe Standard Time, UTC+01:00)
# Thursday, 06 December 2007

One of the things that surface in module 2 of the 2780B course are the predefined reports you can use to get more info on what your system is doing. Good thing is, you can run these directly from Management Studio without Reporting Services. Bad thing, you don’t know what the reports are showing exactly (as in, you don’t know the report definition). That is, we can find out what the reports show since Microsoft released the report definitions, so you can reproduce what the report is showing. Also in Service Pack 2, Microsoft introduced the option to create your own custom reports… and that is just what one of the Microsoft Support Engineers did (performance dashboard). One of the community members also noticed and blogged about this new option and created his own DBA_Dashboard full of useful metrics. Thanks Greg, the effort is much appreciated, do keep up the good work for the next version.

Technorati tags: ,
Thursday, 06 December 2007 10:56:58 (W. Europe Standard Time, UTC+01:00)
# Thursday, 22 November 2007

About half a year ago I attended a webcast about ICE, today I can update that post with a link to the white paper. Maybe more on it later... when I found the time to actually read it.

Technorati tags: ,
Thursday, 22 November 2007 23:46:47 (W. Europe Standard Time, UTC+01:00)
# Tuesday, 13 November 2007

Bah, for the forth time in a week I encounter a star in the select-list of a view. So if no-one told you yet; when creating views, don’t do a SELECT * FROM source, it can (and in time will) hurt you badly. Let’s show by creating a table and two views based on that table;

CREATE TABLE tblSource(
    Col1 nchar(10),
    Col2 nchar(10))
GO
CREATE VIEW vwBad
AS
SELECT * FROM tblSource
GO
CREATE VIEW vwGood
AS
SELECT Col1, Col2 FROM tblSource
GO
INSERT tblSource (Col1, Col2) VALUES ('Column 1', 'Column 2')
GO

So far, so good. Selecting from both views shows you the expected results:

SELECT 'vwGood' view_name, * FROM vwGood
GO
SELECT 'vwBad' view_name, * FROM vwBad
GO

Now add a column to the table, and add some data for that column;

ALTER TABLE tblSource
    ADD Col3 tinyint
GO
UPDATE tblSource
SET Col3 = 3
GO

SELECT 'vwGood' view_name, * FROM vwGood
GO
SELECT 'vwBad' view_name, * FROM vwBad
GO

vwGood still behaves as expected, but vwBad doesn’t return ALL columns as you may have expected based on the *. Now let's see what happens if a column is removed from the table;

ALTER TABLE tblSource
    DROP COLUMN Col2
GO

SELECT 'vwGood' view_name, * FROM vwGood
GO
SELECT 'vwBad' view_name, * FROM vwBad
GO

You will notice that vwGood throws an error (rightfully so, it is instructed to select from a non-existing column), but vwBad doesn’t… it pretends Col3 is Col2. Based on the data, we know better. However this might not be so obvious if based on this change the purchase price is shown as sales price due to changes in the data model and your company starts losing money.

I bet now you want to know which views contain * so you can code them properly, try this query if you're on SQL Server 2005:

SELECT v.name view_name
    , s.name schema_name
    , u.name user_name
    , v.create_date
    , v.modify_date
    , sc.text
FROM sys.views v
    INNER JOIN sys.syscomments sc ON v.object_id = sc.id
    INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
    INNER JOIN sys.sysusers u ON s.principal_id = u.uid
WHERE sc.text LIKE '%*%'

And this one for SQL Server 2000:

SELECT so.name view_name
    , u.name user_name
    , so.crdate create_date
    , sc.text
FROM dbo.sysobjects so
    INNER JOIN dbo.syscomments sc ON so.uid = sc.uid
    INNER JOIN dbo.sysusers u ON so.uid = u.uid
WHERE sc.text LIKE '%*%' AND v.type = 'V'

And don't forget to clean up the example:

DROP VIEW vwGood
GO
DROP VIEW vwBad
GO
DROP TABLE tblSource
GO

Technorati tags: ,
Tuesday, 13 November 2007 12:35:23 (W. Europe Standard Time, UTC+01:00)
# Friday, 09 November 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, 09 November 2007 20:05:57 (W. Europe Standard Time, UTC+01:00)