How to Pass Microsoft Certification Exam 70-451

Leave a comment (0) Go to comments

Once you clear your 70-451 exam then you become a MCITP (Microsoft Certified IT Professional) Database Developer 2008.

Recently One of my college has cleared this exam and shared his experience which I wanted to share with all my readers.

Preparation for these exams meant a ton of studying. He was pretty confident in his skills, but he realize that the happy ending was not guaranteed. So that meant he need to work on study to get this certification.

Study Guides He Used

So he created a study guide for himself earlier and what’s shown here is a straight dump of that; I include all topics as it as-is.

He started out making the study guide a list of things to do, and not a list of things to learn. (Just like homework or an assignment).

Designing a Database Strategy (13 percent)

  • Create SQL Server agent jobs, steps & schedules (through ui and sp_)
  • Set up DB Mail and use. Notify self when a job is done.
  • Create a linked server (through ui and sp_)
  • Review normalization articles
  • Create an application that uses the Entity Framework
  • Security Strategies. Put the following terms somewhere into security triples: (prinicpal, object, permission)
    • Application Roles
    • Schema Ownership
    • Execution context
    • Window vs SQL Authentication
    • Permissions and DB Roles
  • Security Strategies.
    • Create and implement a demonstration of the ideas listed above, both with the UI and with t-sql.
  • Service Broker
    • Go through tutorial, review rusanu’s asynchronous job service broker.
    • Look through following ideas for ideas
      • Services,
      • Contracts
      • Activation
      • Routes
      • Message types
      • Queues
      • Remote service binding
      • Priorities
    • For ideas not yet covered. Create and implement a demonstration of the ideas listed. Both with the UI and with t-sql

Designing Database Tables (16 percent)


  • Implement and use data types from C# application
    • Geography: Implement an app that tells you how close the nearest x is.
    • Geometry: 
    • HierarchyId: Review
      • Can you get value of current node?
    • Date, time, datetime2, datetimeoffset
      • Watch Kendra Little’s 24HOP session on dates.
    • Varbinary (max) (Wasn’t this around in 2005?)
    • Filestream …
  • Size the data types. Review each.
  • In row data blobs, Create a table that stores data in row by default, and out of row by default. (What is the unspecified default?)
  • Create some persisted computed columns.  Review Determinism, precision etc…
  • Data integrity ,Enforce some constraints the following way:
    • Dml triggers,
    • Pk
    • Fk
    • Check
    • Unique
    • Null/not null
    • Default (???)

Designing Programming Objects (17 percent)

  • Stored Procedures
    • Create procedure exercising different execution contexts (EXECUTE AS)
    • Review TVP example, Also create sproc to pass tables between tsql and sproc
    • Write sproc that returns data (using RETURN?), using OUTPUT param, using result sets. Are there others?
    • Look at interaction of RECOMPILE (table level vs. sproc level)
    • Look for Error handling examples, start with Rusanu’s asynch script. Look for TRY CATCH examples. Update sp_blockedprocess* with error handling
  • Views
    • Create views again, CTEs
    • What’s a partitioned view.
    • Review with check option again (it means updating views shouldn’t violate the view criteria)
    • Read BOL on WITH SCHEMABINDING
  • Functions
    • What permissions are there on udfs? flavors of security here: GRANT, DENY, REVOKE versus EXECUTE AS
    • What is schemabinding on udf?
    • what are the three (four?) kinds of functions
    • Review  inline table-valued functions vs. views, multi-statement table-valued functions, determinism \
  • CLR Stuff (This was my favorite part of studying if not the most useful)
    • Implement a regex “match” in CLR
    • Implement “Concatenate” aggregate in CLR
    • Implement “first” aggregate in CLR (if possible)
    • Impelement “times table” sproc in CLR (gives times table as resultset from 1 to 12)
    • Explore PERMISSION_SET.
    • Implement Regex as a clr UDType
  • Look out for Bobby Tables:
    • QUOTENAME,
    • PARSENAME,
    • too bad we can’t use sys.fn_quotefourpartname
    • Especially look out for EXEC or sp_executesql… distrust any parameter values!

Designing a Transaction and Concurrency Strategy (14 percent)

  • Locking granularity levels (Something I’ve always avoided in the past.)
    • Review the different locking granularity hints.
    • Understand memory consumption of each.
  • Implicit transactions vs explicit transactions
    • Nested transactions, using XACT ABORT, Nesting levels, (damn, where’d I put my totem). Dive deep into these. Which severity of error is enough to kick you up a level? I know how TRY CATCH behaves, is this affected by aborted transactions?
    • Save points, (review sudoku solution)
    • Use SET IMPLICIT_TRANSACTION ON setting
  • Concurrency.
    • Read Kendra’s poster
    • Review my own series.
    • How do are these affected by rowversion datatype?

Designing an XML Strategy (8 percent)

  • Watch MCM video on xml by Bob Beauchemin

Designing Queries for Performance (17 percent)

  • Optimize and Tune
    • Review what’s sargable
    • Look at temp storage (find a definitive blog post on difference between temp table and table variable.
    • Find Itzik’s posts on sqlmag.com for GROUP BY  (grouping sets, cube, rollup)
  • Execution plans
    • Review my post on table access operators
    • Look at join operators,
    • Review those fuzzy ones (table spool, parallelism, others)
  • Row based vs Set based operations
    • Enumerate as many non-set based things (cursors, WHILE),
    • Do ctes count? or subqueries in SELECT, WHERE clauses count as non-set based?
    • Look at batching methods, splitting implicit transactions (this is Rowcount(), top(x), while)

Designing Queries for Optimal Performance (15 percent)

It was at this point in creating his study guide (the last section!) that he discovered Eric Wisdahl had created his own study guide earlier this year, which you can check at http://ericwisdahl.wordpress.com/tag/70-451/. This is really a great post.

Looking forward to see you too in MCITP (DBD) community.

EOF - How to Pass Microsoft Certification Exam 70-451, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.