I passed the 70-431 exam today and I now have my MCTS (certified technology specialist) title!  :)

here's more info on the exam: http://www.microsoft.com/learning/en/us/exams/70-431.aspx

1.       Identify the Principal, Mirror, and Witness 

2.       Create a certificate for authentication between the end points on the Principal/Mirror 

3.       Create the end points on the Principal/Mirror 

4.       Set the Principal and Mirror recovery models to "Full" or else you will get an error 

5.       Ensure that the Principal and Mirror are synchronized by backing up the Principal and applying the backup to the mirror using the "no recovery" option

It measures the number of packets waiting to be sent back to clients.  A value greater than 2 represents a potential network bottleneck.

source: these 2 basic sentences are repeated over and over on different sites all over the web, I'm not sure what the original source is

StoredProcedures\SP:StmtCompleted

source: http://msdn2.microsoft.com/en-us/library/ms175481.aspx

any version except for Express edition

 source: http://msdn2.microsoft.com/en-us/library/ms143761.aspx

1. Scans\Scans Started - this event is triggered whenever a table or index scan begins, if it shows as a table scan then the table is missing an index.
2. Performance\Autostats - this event is triggered whenever SQL Server has to create statistics in order to improve query performance.  Statistics are only created if there is not an existing index that can be used to boost efficiency.

books online source: http://msdn2.microsoft.com/en-us/library/ms175481.aspx

1. ALTER permission on the schema
2. CREATE TYPE permissions on the database
3. REFERENCES permission on the registered assembly being used for the user defined type.

books online source: http://msdn2.microsoft.com/en-us/library/ms175007.aspx

Use the sp_dumpdevice system stored procedure.  For example (after mounting the USB drive as "g"):

 EXEC sp_addumpdevice 'disk', 'mydiskdump', 'g:\dump\dump1.bak';

source: http://msdn2.microsoft.com/en-us/library/ms188409.aspx

The session must be configured for High Availability operating mode, which provides synchronous data transfer between the primary and secondary (principal/mirror) servers, and which also requires a third server (witness) to handle the failover process.

 books online source: http://msdn2.microsoft.com/en-us/library/ms178104.aspx

By default, the Database Engine chooses as the deadlock victim the session running the transaction that is least expensive to roll back. Alternatively, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement

books online source: http://msdn2.microsoft.com/en-us/library/ms178104.aspx

You can use system/performance monitor to define an alert, then set the alert to notify admin.

url to MS KB source article: http://support.microsoft.com/kb/324796

• you cannot return non aggregate data with pivoted columns
• you have to specifiy a derived table to pivot on
• pivot operator does not allow dynamic columns
• pivot operator does not allow multiple types of aggregation within a single operation

books online source: http://msdn2.microsoft.com/en-us/library/ms177410.aspx

1. you can use SQL Management Objects Copy Database Wizard
2. you can do an online backup and restore
3. you can manually copy the database files

books online for sp_detach_db: http://msdn2.microsoft.com/en-us/library/ms188031.aspx

books online for how to detach using management studio: http://msdn2.microsoft.com/en-us/library/ms191491.aspx

books online for copy datanase wizard: http://msdn2.microsoft.com/en-us/library/ms188664.aspx

when creating partitioned indexes in sql 2005 the ON clause must reference the partition scheme

books online URL discussing indexes: http://msdn2.microsoft.com/en-us/library/ms188783.aspx

SQL 7.0 and SQL 2000 can both be directly upgraded to SQL 2005.  SQL 6.5 cannot be directly upgraded to SQL 2005.

 books online source: http://msdn2.microsoft.com/en-us/library/ms143695.aspx

It's a measure of how much time a processor is spending executing threads.  A high % Processor Time can be an indication of performance issues, but by itself cannot definitively verify performance issues.

books online source for more info on objects and counters to monitor activity: http://msdn2.microsoft.com/en-us/library/ms190382.aspx

You can schedule a job to run:

• Whenever SQL Server Agent starts.
  
• Whenever CPU utilization of the computer is at a level you have defined as idle.
  
• One time, at a specific date and time.
  
• On a recurring schedule.

books online source: http://msdn2.microsoft.com/en-us/library/ms175138.aspx

You need to setup Backup, Copy, and Restore schedules for log shipping in SQL Server 2005.

URL to books online article "Understanding Log Shipping": http://msdn2.microsoft.com/en-us/library/ms187103.aspx

I am about to go for my first certification... the url to go to if you want to register for an exam is:

http://www.microsoft.com/learning/mcpexams/register/default.mspx