Exam 70-431 passed!
Well, I finally took the initiative to take the MCTS (Microsoft Certified Technology Specialist) 70-431 exam today and passed:) Per my confidentiality agreement I cannot give out specific test information, but will share my experience about what I think one would need to pass this exam.
The books I used to study with were the following:
MCTS Self Paced Training Kit: Microsoft SQL Server 2005-Implementation and Maintenance
Author Solid Quality Learning
Pages 992
Disk 1 Companion CD(s); 1 Evaluation DVD(s)
Level Beg/Int
Published 05/31/2006
ISBN 9780735622715
ISBN-10 0-7356-2271-X
Price(USD) $59.99
http://www.microsoft.com/mspress/books/9364.aspxMicrosoft SQL Server 2005 Administrators Pocket Consultant
http://www.microsoft.com/mspress/books/6794.aspxI used the books listed above for hands-on training and also utilized Self Test Software for practice tests. I will have to say the Self Test Software was key in preparing me for what kinds of questioning I would see on the exam.
I have to say you will need to be prepared to get your hands on SQL Server 2005 in order to pass this exam. Lots of simulation questions.
Key areas I studied and re-studied for that matter:
Linked Servers
Import/Export tools: (BCP, BULK INSERT, SSIS etc.)
Database mirroring
XML Methods (cant stress you understand XML enough....)
Views. (Understand differences with indexed views and partitioned views)
Clustered Indexes vs. Non-clustered
SQL Profiler and Activity Monitor
Database Tuning Advisor
Know dynamic mangement views and how to properly query to find db statistics etc. (dont query system db's)...etc
These are just a few of the things I used to focus on...but overall...make sure you review the Microsoft learning objectives for the exam...and it will assist you in the areas of what you n
eed to focus on etc.
-Carl (The Water Buffalo)
CF MX 7 and SQL Server 2005 data source issues...
Its been awhile since I have written in here, but I want to share an issue I have just now resolved with SQL Server 2005 and Cold Fusion MX 7 regarding data source connections.
Problem:
I was having issues connecting from a Windows Server 2003 Ent. Edition box running CF MX 7 using the Cold Fusion Admin to a clustered SQL Server 2005 named instance.
Solution:
I changed my clustered instance of SQL Server 2005 to mixed mode authentication and used the following parameters:
CF Data Source Name: your_dsn_name
Database: db_name
Server: servername\instance_name
Username: {your_username}
Password: {your_password}
I referenced this article about changing to mixed mode authentication:
http://cfpayne.wopress.com/2006/07/21/changing-authentication-in-sql-server-2005-express/Steps I took to resolve the issue:
1. Created a new login in SQL Server 2005 and mapped it to the correct default database.
2. Changed the Authentication Mode in SQL Server 2005 to Mixed Mode Authentication. (See link above.)
3. Tested connections using the general parameters listed above.
If anyone has comments or suggestions on their experiences using Windows Authentication in CF MX 7 please post as I would be interested in seeing others experiences in this arena.
-Carl (The Water Buffalo)
TOP in SQL Server 2005
There are so many new features in SQL Server 2005 that there's one that can make a significant impact in solving many different types of problems. Andy Warren takes a look at one of the features that has been enhanced and is extremely useful, albeit quirky: TOP.
Recently someone asked me about what had become my feature in SQL 2005 and if it was the feature I thought it would be. That's really a pretty good question since it's easy to get excited about the 'wow' and overlook the more mundane. Going into the release my favorite feature that I was looking forward to was the ability to modify replicated columns (if you've done replication I'm sure you share my enthusiasm for that feature!). My path has taken a twist and turn since then so that I don't use replication daily. Instead I find that out of all the new things the one I seem to use most is Top N in updates and deletes (followed closely by the output clause). No, not exciting, but definitely practical.
Assuming you've been using SQL 2000 for a while you know that you can use Top N in selects, either as a number or a percentage. For example, you might write this:
select top 100 * from sometable
What you couldn't do was parameterize it. It only worked with selects too. If you wanted to restrict the number of rows affected by a select at runtime, or restrict the rows affected by an update or delete you had to explicitly describe the rows using a join or where clause, or you could cheat a bit by using ROWCOUNT, like this:
set rowcount 1000
delete from table where somefield=1
set rowcount 0
It worked well enough (and still does in SQL 2005). The downside was a bit of a clunky syntax and it created the risk that somehow rowcount did not get reset back to 0, not a good thing since it is a session based setting. Forgetting to change it back would mean that all the statements would be restricted to 100 rows. Not the best thing if you're trying to select all employees that need a paycheck. SQL 2005 let's you use Top N in selects, plus updates and deletes - but the syntax has some quirks as you'll see in a second.
For select statements you must specify parentheses if you are passing a parameter, otherwise they are optional
Select Top (@TopN) * from employees
Select Top 10 * from employees
When doing an update or delete, you have to use the parentheses in both cases:
Delete Top (@TopN) from employees
Delete Top (10) from employees
update Top (@TopN) employees set payrate = payrate * 1.1
update Top (10) from employees set payrate = payrate * 1.1
So a simple rule to follow would be to always wrap the 'N' in parentheses!
The most common reason I've had to do restrict the number of rows has been when doing operations against millions of records. Updating millions of records can not only cause blocking, but it can bloat your transaction log even if you're in simple recovery mode (because it has to log the entire transaction). Doing the update in batches doesn't guarantee no blocking of course, but it should reduce the time the query takes to complete. It often takes some experimenting to figure out the optimum batch size. I suggest making sure that the query will complete in less than 30 seconds, as that is typically the default timeout for a database action. Even if you're in full recovery mode there is an advantage to doing chunks. If the size of your transaction won't fit into the current virtual log segment the log will grow (or you'll come to a halt!). By doing batches and running log backups you can easily keep the log from growing to a huge size.
I've shared my favorite feature and an application for it as well - how about writing about your favorite feature and submitting it for the SQL world to see?
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.
by Doug Welty
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.
