Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
This exam is core credit toward an MCDBA and elective credit toward an MCSE. I am finally going to finish my MCDBA, after having taken 2 years off of the tests. Here is the list of materials I am using to prepare. I hope you find it and my other lists helpful. I have been working with SQL Server for over 4 years now, so thought I would be ready without much preparation. A quick run through Transcender's practice test has sent me scrambling for study materials, as I scored only a 300 on it. If that is a good indication, there's a lot of the new-for-2000 stuff on the test -- stuff that those of us who started in version 7 or earlier haven't used very much. Those will be the items highlighted here.

Well, I passed the test with a 793, so here's the report. 44 questions, 110 minutes, passing score of 700.

Be careful of picky stuff. There are lots of code snippets with small differences between them. It's easy to overlook a difference that invalidates one of the snippets.

Know your cursors, deadlock strategies, and server options. Those were my weak areas. Also be familiar with clunky code that should be JOINed, but instead separates tables with a comma and filters them in the WHERE clause.



  • 70-229 Exam Objectives
  • Inside SQL Server 2000 by Kalen Delaney is an excellent reference. I intend to read the whole thing, but not everything in the book applies to this exam. I'm noting the chapters besidethe section(s) they cover. The trial version of SQL 2000 comes with this book.

    The software to play with

    If you pass up Delaney's book, you can download the SQL Server 2000 evaluation edition from Microsoft -- or order it on CD for just a few dollars.

    General Information & Resources for SQL Server 2000

  • Books Online (BOL) is downloadable from Microsoft. It is also available as an online, browsable resource. I will be providing links into that version.
  • SQL Server Magazine is a good source for up-to-date articles by some of the sharpest SQL Server gurus.
  • sql.2000trainers.com is a growing site of articles and exams by Mike Aubert. Thanks to Consultant on microsoft.public.cert.exam.mcse for making me aware of this resource.
  • SQL-Server-Performance.Com is dedicated to Microsoft SQL Server Performance Tuning and Optimization. Thanks to Annette West for reminding me of this excellent resource.
  • System Requirements for SQL Server 2000
  • Maximum Capacity Specs by edition -- how much RAM does Enterprise Edition support, How big a DB is supported, etc.
  • How SQL Server 2000 sees Intel Hyperthreading for licensing purposes. The short answer is that SQL Server is fine with it. For Licensing purposes, SQL Server counts only physical processors.
  • Cert Yourself offers a brief overview of information on the exam.
  • CertifyExpress offers a 16-page 229 tutorial.
    the Transcender Certification Trainer for exam 70-229


    Resources by Exam Objective

    Developing a Logical Data Model

    • Define Entities
    • Specify degrees of Normalization
      • DeveloperZone's Intro to Normalization is easy to read and goes through 3NF. The example is a little contrived, but it works.
      • The University of Texas' Overview of Normalization is a little more difficult, but it goes thorugh 5th normal form, as well as a couple variations. The example here is even more contrived than that above. Still, this is the more thorough paper.
    • Gayathri Gokul offers an overview of Constraints.

    Implementing the Physical Database

    Retrieving and Modifying Data

  • Import and Export Data
    • DTS
      • Marcin Policht offers this series on DTS. I will link to each article, because Database Journal does not provide links from one part to the next and the links under an author's name are sometimes incomplete.
        • Part 1 introduces DTS
        • Part 2 discusses DTS Wizards.
        • Part 3 introduces DTS Designer Connections.
        • Part 4 introduces the other half of the DTS equation -- DTS Designer Tasks.
          • Bulk Insert -- quick, but inflexible. No transformations possible.
          • Copy SQL Server Objects -- duplicates any objects -- tables, views, constraints, etc. Inefficient for merely copying data.
          • Execute Process -- launch any Win32 program
          • Execute SQL -- executes statements & stored procedures.
          • File Transfer Protocol -- will FTP files -- useful in automated import/exports between heterogenous or remote systems.
          • Send Mail -- requires a MAPI profile under the appropriate login.
          • Transfer Database -- Contained in Copy Database Wizard.
          • Transfer Error Messages -- to copy the sysmessages table.
          • Transfer Jobs
          • Transfer Logins
          • Transfer Master Stored Procedures
        • Part 5 covers the remaining tasks and global variables
        • Part 6 introduces Packages Workflow. Somehow, I'm not expecting to see DTS questions this detailed on the test, so I think I'll stop here for now. It is a good set of articles, though, to which I plan to return.
      • MVP Narayana Vyas Kondreddi discusses DTS Best Practices. Execellent article!
    • Bulk copy utility (bcp)
    • Bulk Insert T-SQL Reference
  • Retrieve heterogenous data
  • Retrieve, filter, group, summarize, and modify data by using Transact-SQL.
  • Extract data in XML Format
    • Retrieving and Writing XML Data is a great place to start. Good overview with just enough detail to get started.
    • OPENXML T-SQL Reference is as dry as a complex BOL page is expected to be, but very thorough.
    • Using OPENXML provides 11 pages of examples and explanation. It's probably overkill as exam prep, but a good reference when you put OPENXML to work.
    • Writing XML using OPENXML ties it all together very well. It explains the meaning of an edge table (on page 2), a term that is used in the other documents, but not defined.

    Programming Business Logic

  • Enforce procedural business logic by using stored procedures, transactions, triggers, user-defined functions, and views.
    • Specify Trigger Actions
    • Views
      • Doug Carpenter reviews the basics of views, their purposes, advantages, and limitations, before discussing indexed views. He discusses the limitations (no outer or self joins???), the performance boosts (3 - 50 times in his tests, 10 - 100 according to Microsoft), when to use them, and when not.
    • Doug Carpenter explains User-Defined Functions as well.

    Tuning and Optimizing Data Access

  • Bryan Kane of Eugene, OR highly recommends The Definitive Guide to SQL Server Performance Optimization. This 175-page ebook requires you to submit some personal information, but the book looks very good. Thanks, Bryan!.

    Designing a Database Security Plan



    If you know of more resources which should be listed here, please email a link to me. I very much appreciate other resources to study and will be sure to acknowledge you on this page.



  • Williamston Consulting is now ...

    Black Locust Software

    Technology Solutions for Business ... Large or Small
    .



    Home

    Auto Dealers' Form Software

    Software Development

    Website Development

    Website Advice

    IT Guys' Stuff:

    Certification
    Study Guides

    Programming
    Tips