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
Upcoming Events

Redmond Tech Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.