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

I keep losing these so here are the links to fresh copies... for safe keeping

Northwind and pubs Sample Databases for SQL Server 2000

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

SQL Server 2005 Samples and Sample Databases

http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

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