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 |
- Set up database mirroring and understand its role
as an alternative to replication or clustering.
- Explore database snapshots and know how they fit
into a disaster recovery plan.
- Define an HTTP Endpoint to practice SQL 2005's
Web integration.
- 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.
- Use BCP and the BULK INSERT statement to load text
files into base or temporary tables.
- Run a few traces with the new Profiler and note
the new templates, columns, and features.
- 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.
- DDL Triggers -- event based actions aren't just
for data modification anymore, try them out.
- Create a partitioned table using the new partitioning
function and insert a few rows in different brackets
based on the partitioned column.
- 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.