Exam Reviews

Exam 70-431: Covering the Bases

Don't be fooled by the introductory nature of this SQL Server 2005; be prepared to get tested on a broad spectrum of topics.

Microsoft debuted SQL Server 2005 in November of 2005 and, a few months later, released a series of new, more specialized database certification exams. The introductory Exam 70-431 TS: SQL Server 2005-Implementation and Maintenance, which I review here, is just one among a number of exams targeted at anyone whose primary job role is working with Microsoft's latest database technology. Those who pass it earn the Microsoft Certified Technology Specialist: SQL Server 2005 and can use it toward fulfilling requirements at the next certification level, the Microsoft Certified IT Professional.

The 70-431 has a very broad target audience both as a prerequisite for the MCITP and as a stand-alone for developers and administrators who want to highlight their skills on the newest release of Microsoft's premier database server. The 70-229 is the closest analog to the 70-431 in the MCDBA series, as much of the exam deals with topics that are common to both database engines: transactions, locking, querying relational data, and recovery mode, to name a few. Because of these parallels, you can leverage your SQL Server 2000 experience in preparing for the 70-431 by conceptually breaking down Microsoft's objectives for this exam into those that are similiar under SQL 2000 and 2005 and those that are in the exclusive realm of the 2005 server. Individuals who are taking a SQL Server exam for the first time should more evenly distribute their study efforts across the exam objectives.

Installing and Configuring SQL Server 2005
It all begins with installation. It's important to know which versions can coexist on the same server, as well as the details for configuring multiple server instances. When it comes to configuration, little has changed in the way of physical files and recovery modes; but, the database mail subsystem has been rebuilt from the ground up with no dependency on MAPI or Outlook. This should come as a welcome addition to developers and administrators who need e-mail notification of job failures; but don't want to open additional ports on their dedicated SQL servers.

SQL 2005 places a strong emphasis on security with more granular permissions, encryption, and security schemas to manage the increased complexity. Securables form a hierarchy beginning with the server, extending to the database, and ending at the object level.

Linked servers have been moved from the Security node in Enterprise Manager to the more appropriate child node of Server Objects in the new Management Studio; but, little else has changed with linked servers or their dynamic OPENQUERY and OPENDATASOURCE cousins. If you understand the security model for linked servers under SQL 2000, you should be well prepared for this topic on the exam.

Tech Help—Just An
E-Mail Away

Reviewer's Rating
The 70-431 can be thought of as the 2005 version of the 70-229, in the sense, that it is a general database exam with questions on administration, development, and performance tuning. Individuals who have taken the 70-229 or have a solid background in SQL 2000 will be able to leverage much of that knowledge and should focus on SQL 2005's new features.

Exam Title
70-431: SQL Server 2005 - Implementation and Maintenance

Who Should Take It
Candidates for MCTS and as a prerequisite for those pursuing the three new MCIPT certifications

Course to prepare
2779: Implementing a SQL Server 2005 Database
2780: Maintaining a SQL Server 2005 Database

Exam Objectives
www.microsoft.com/learning/exams/70-431.asp

Implementing High Availability and Disaster Recovery
The options offered to a database administrator for high availability and disaster recovery have changed significantly in SQL 2005. You'll need to familiarize yourself with the powerful new Mirroring functionality which wasn't released for production until SP1. Mirroring topologies can include two servers (principal and mirror) or three servers (principal, mirror and witness). A witness server is necessary if you want automatic failover that is transparent to the client (ADO.NET clients only). The Express edition of SQL 2005 can only participate in Mirroring as a witness server.

New to 2005 are database Snapshots, a read only copy of a database useful for reporting and recovery of data or database objects in the event of unintended modifications. Creating snapshots is as simple as issuing a CREATE DATABASE statement with the AS SNAPSHOT OF clause and including the logical name of the source database file.

Supporting Data Consumers
The core concepts involved in the manipulation of relational data have remained relatively stable over multiple versions of the SQL engine. In fact, the relational model and the simplicity and elegance of SQL as a declarative language have had remarkable resilience. If you aren't rock solid on the core syntactic elements involved in the selection, insertion, and updating of relational data, make sure that you study and practice before the exam. If you've mastered the DML syntax in previous versions of SQL, concentrate, instead, on the few new additions such as the TABLESAMPLE for aggregates and the new PIVOT and UNPIVOT operators for transposing data.

XML has only grown in popularity since support was added for it in SQL 2000. SQL 2005 introduces a full-blown XML data type, which allows for the indexing of attributes, as well as, core TSQL language enhancements for manipulation and querying. SQL 2005 extends the functionality of XPATH with XML DML for inserts, updates, and deletes of XML data. The new XML data type also supports a .query() method that takes an XQuery argument. As one of the major enhancements to 2005, expect to see exam questions on the syntax and usage of this new data type.

As an alternative to the more traditional network based modes of communication including TCP and Named Pipes, SQL 2005 offers the option of passing SOAP documents over HTTP in the request / response model. Unlike SQLXML in 2000, HTTP Endpoints do not require IIS to be installed on the server; instead, they utilize the Windows 2003 kernel-mode HTTP driver. While they do provide a quick and simple method of exposing relational data as web services, delegating this functionality to a separate physical layer will provide maximum performance and scalability.

All of the SQL endpoints I mentioned here are synchronous in nature. In cases where reliable asynchronous messaging is needed, the Service Broker functionality of SQL 2005's engine offers an integrated solution. Service Broker allows loosely coupled applications to be built. Loosely coupled applications have the benefit of not requiring the other components of the system to be on-line or available to function correctly. The end result is similar to having a middle tier comprised of MSMQ, but Service Broker is integrated directly into the SQL engine. For purposes of the exam, familiarize yourself with syntax of the Service Broker DML for both messages and conversations.

Monitoring and Troubleshooting Performance
SQL Server 2005 adds a significant number of new system views, in addition to, maintaining the INFORMATION_SCHEMA views for ANSI and backwards compatibility. The decision management views offer either server or database scoped state information that can be used for diagnostics and troubleshooting. You will need to familiarize yourself with these for the exam, as well as refresh yourself on a few of the management procedures such as sp_who and sp_lock.

Profiler is an invaluable tool for diagnosing a host of performance related problems; and, SQL Server 2005 offers a sizable extension to the number of events available for selection. Tracing different events can determine duration and resource utilization of TSQL statements, participants in deadlock, index utilization and security information, to name a few. Profiler traces can be saved to a table or file and serve as the input to the database engine tuning adviser. If the trace is to help diagnose performance problems on the server, it is advisable to run and save the trace from a different computer so as to not add an additional burden to an already congested server.

If you do determine that a deadlock is occurring either from sp_who, Profiler or the new dm view sys.dm_exec_requests, resolving it is as simple as issuing a KILL command with the single parameter of the process ID that you want terminated.

Maintaining Databases
The skills needed to maintain databases under SQL Server 2005 are much the same as under the 2000 version. You should know the syntax and usage of a few core DBCC's including CHECKDB, INDEXDEFRAG, and SHRINKFILE.

SQL agent jobs are a vital component; but, play only a minor role in the official exam objectives. You should be familiar with creating jobs, adding steps, schedules, and notifications. The scheduling and recovery of db and log backups are always important. Understand the difference between full, differential, and log backups, as well as, how and in what order to restore them in the event of a hardware failure or data corruption.

Creating and Implementing Database Objects
Everyone aspiring to the MCTS will be required to have a basic knowledge of database objects including tables, views, functions, and procs. It's as important under SQL Server 2005 as it was in previous versions to know the difference between deterministic and nondeterministic functions, when a view ceases to be updatable, how to circumvent that constraint (in certain instances) with INSTEAD OF triggers, the difference between table variables, local and global temp tables, and their related scopes. One very useful enhancement in 2005 is the ability to EXEC the results of a stored procedure directly into a table valued variable, rather than needing a full-blown temp table. This can come in particularly handy when married with dynamic SQL, the use (and misuse) of which deserves a review in its own right.

An essential skill for both the exam and the real world is the ability to take a table definition along with a poorly performing query and be able to make a judgment as to what type of index (clustered or unclustered) and on which columns is likely to yield a performance improvement. At the risk of oversimplifying, if the table does not have a clustered index, add one on the primary key column. If a cluster does exist, a covering index (one in which all select and where clause columns can be read from the index itself) will typically result in better query performance. Keep in mind that nonclustered indexes have a negative impact on insert and update performance since they have to be maintained whenever the base table is modified.

In SQL 2000, if a database developer wished to extend the functionality of the TSQL language, they had three options: Write their own extended stored procedure in C++, instantiate and invoke methods on a COM object using sp_OACreate and the related sp_OA procs, or send instructions to the command line using xp_cmdshell. All three methods have disadvantages. Developers can now write a class in any .NET language, upload the assembly to SQL using the CREATE ASSSEMBLY statement, and, then, map methods within the assembly to stored procedures using the EXTERNAL NAME clause of the CREATE PROCEDURE statement. Extending the capabilities of TSQL using CLR integration is safer then writing an xp in C++ as there is no chance of memory leaks, it is more secure than OLE since .NET security applies, and it is more powerful then xp_cmdshell because of the sheer magnitude of the .NET framework.

It is important to understand what Microsoft has done in the way of partitioning schemas and functions in SQL 2005. SQL 2000's partitioned views were a step in the right direction for managing very large entities; but, the current approach of having multiple filegroups map to a single logical table through a partitioning schema is simpler and more powerful than having multiple physical tables map to a single logical table (view). As part of the official curriculum, be sure to familiarize yourself with this new feature if you haven't already.

10 Things to Practice
  1. Set up database mirroring and understand its role as an alternative to replication or clustering.
  2. Explore database snapshots and know how they fit into a disaster recovery plan.
  3. Define an HTTP Endpoint to practice SQL 2005's Web integration.
  4. Play with SQL's new XML data type. Go ahead and create an index or two on the XML column and familiarize yourself with the new TSQL keywords.
  5. Use BCP and the BULK INSERT statement to load text files into base or temporary tables.
  6. Run a few traces with the new Profiler and note the new templates, columns, and features.
  7. Get your hands dirty with CLR Integration. Understand the implications for current and future architectures by creating a stored proc that calls a web service, accesses the file system, or performs a related task that is equally simple in the .NET framework; but, difficult or impossible within TSQL.
  8. DDL Triggers -- event based actions aren't just for data modification anymore, try them out.
  9. Create a partitioned table using the new partitioning function and insert a few rows in different brackets based on the partitioned column.
  10. Use the Service broker to define and send messages between two server instances.

Final Exam Prep Suggestions
I recommend a mixed preparation strategy consisting of reviewing core concepts that are unchanged from SQL 2000, along with focused study and hands-on practice with the most highly advertised new features of SQL 2005. If you have passed Exam 70-229 or have comparable experience with SQL 2000, you can safely focus on the new features; otherwise, be sure that you have reviewed and are comfortable with all the objectives listed on Microsoft's website. If there is one thread that is common to exams of this type, it is that the most highly anticipated and promoted features of the current release are sure to show up in one form or another on the exam.

Since most of us will have had the chance to implement only a few of the new features at best in a production environment, try using the AdventureWorks db as a testing ground to familiarize yourself with the utility and syntax of the powerful new tools in SQL 2005's arsenal.

comments powered by Disqus

Reader Comments:

Mon, Mar 23, 2009 Anonymous London

Very good review - I'll cherish his tips.

Sun, Mar 8, 2009 Anonymous Anonymous

Great review...i was abbout to study for the 70-229 exam then i found out that its going to be retired this month..now i will study for this one and sit for the exam

Tue, Apr 29, 2008 Anonymous Anonymous

s

Fri, Apr 4, 2008 Anonymous Anonymous

thanks

Wed, Mar 19, 2008 Fabian Guanajuato

Great, wish I had read it before taking the exam

Fri, Mar 14, 2008 Sacheen Bangalore

Good to know before entering into SQL server 2005

Thu, Feb 28, 2008 Anonymous Anonymous

opened my eyes to the possibilities at hand. very well-written.

Sat, Jan 19, 2008 Anonymous Ontario,Canada

Excellent article. I didn't know where to start with 2005 since I have mostly working with SQL 2000.

Wed, Apr 25, 2007 Anonymous Anonymous

very good overview. I am taking the course

Sun, Mar 18, 2007 Anonymous Anonymous

Great article. Gives a good out line for learning the product in addition to preparing for the exam.

Thu, Feb 22, 2007 Anonymous Anonymous

excellent

Sun, Oct 8, 2006 Crito Philippatos Knoxville, TN

Author is obviously an accomplished developer and I wish I knew half of the dot-net stuff he does... HOWEVER, maintaining databases in SQL Sever 2005 has changed significantly. First and foremost, if you read BOL you'll find many of your familiar DBCC commands are being deprecated. Particularly important are the changes to index maintanance. Tried and true DBCC SHOWCONTIG and DBREINDEX are now considered the wrong way of doing things. You're supposed to use DMVs and DMFs to return the same information much more verbosely and then issue ALTER INDEX statements to correct any issues. Further complicating matters are function names like sys.dm-db-index-physical-stats (replace - with underscore, this webpage won't let me enter them) which are indisinguishable, by name anyway, from system views. Apparently system functions don't need to follow any naming convention, like a preceding fn. Anyway, some changes are much less irritating to seasoned SQL Server DBAs. Specifically, the alter index statement now takes an optional ONLINE argument which prevents some contention issues during rebuild operations. It's probably best to use it whenever possible.

In any case, hope no one takes my comments the wrong way. Just wanted to add some missing info to an otherwise outstanding article.

Mon, Sep 18, 2006 PrepLogic Anonymous

Great review for an exciting new exam. Keep an eye out for PrepLogic's full suite of training for this exam due to be released in the coming weeks.

Tue, Sep 5, 2006 pradeep sydney

very good article and definitely helps me in passing the exam

Sat, Aug 12, 2006 Anonymous Anonymous

well informed - good read

Fri, Aug 11, 2006 Mur Cleveland, OH

Awesome! Who could be cranky about having to take 70-431 with a great study plan like this!

Fri, Aug 11, 2006 Anonymous Anonymous

Quite thorough! This is the most concise overview of 70-431 I've read to date. Thanks for the effort, I'll be sure to have a printout of this article handy as I review for the exam.

Fri, Aug 11, 2006 Anonymous Anonymous

Great article

Fri, Aug 11, 2006 Rich Hungary

Excellent article, covers all of the most important issues.

Fri, Aug 11, 2006 Ken Bethlehem

Great review.... makes me want to go take the test!

Fri, Aug 11, 2006 MDW UAS

Well written, this will certainly help me prepare and pass the test. Thanks

Fri, Aug 11, 2006 David White Cleveland

Excellent review. Covered everything.

Fri, Aug 11, 2006 Jfrye cleveland

Great article - covers the test like the knowledge gained is more important than merely passing the test !

Add Your Comment Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above

Redmond Tech Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.