(Continued from previous part...)

You want to be sure that your Scalable Shared Database is as available as possible. Which of the following is not needed for this?

Answer
Use Database Mirroring to fail over between the old reporting database and the new one.

Explanation
The update process for a Scalable Shared database with minimal downtime involves putting out a new copy of the database, detaching the old database from each server, and then attaching the new database to each server.

What is the cost threshhold for parallelism in SQL Server 2005?

Answer
This is the number of seconds that a serialplan cannot exceed if it is to be used. A parallel plan is used if the estimate exceeds this value.

Explanation
This is the threshold at which SQL Server determines whether a serial or parallel plan is to be used. When SQL Server calculates that a serial plan exceeds the threshold, it will elect to use a parallel plan instead.

You have a Scalable Shared Database setup for reporting purposes on SQL2. You want to be able to keep a point in time view of the reporting database each month. What can you do?

Answer
Make a new copy of the production database each month and then copy that to the SAN. Attach it as a new Scalable Shared Database each month to the reporting servers.

Explanation
A Scalable Shared Database does not support database snapshots, so you would have to manually create a new database each month with the data view you need and add this as a new Scalable Shared Database to the SAN and each reporting server.

You have an old database that needs to run in compatability mode 65 on your SQL Server 2005 server. Which framework would you use to manage this database programatically?

Answer
SQL-DMO

Explanation
SMO does not support compatibility modes 60 or 65, so you would ned to use DMO insead.

You have two Service Broker instances running. One is on SQL1 with the default collation and the other is on SQL2 setup for French collation. Which collation is used for Service Broker messages sent between the instances?

Answer
Service Broker does not consider the collation, using byte-by-byte matching for names.

Explanation
Neither collation is used. Service Broker operates in a collation independent method that removes collation information from the messages.

What does the max full-text crawl range option do?

Answer
Determines the number of partitions used in an index crawl.

Explanation
This option helps optimize the full-text indexing process by specifying the number of partitions the SQL Server uses during index crawls.

Which of the following is not an allocation unit in SQL Server 2005?

Answer
TEXT_IN_ROW_DATA

Explanation
The three types of allocation units are: IN_ROW_DATA, LOB_DATA, and ROW_OVERFLOW_DATA. Each heap or index has IN_ROW_DATA which holds part of the data. LOB_DATA is used for large object data types and ROW_OVERFLOW_DATA is used for varible length data that causes a row to exceed the 8060 byte limit.

Which of the following is the best use for a Scalable Shared Database in SQL Server 2005?

Answer
A reporting database server

Explanation
A scalable shared database is a feature that allows you to setup read-only database on a separate server for reporting purposes. This database provides an identicle view of your data from another server.

You are loading 100 rows of data into a narrow table that is heavily used by your production inventory queries. It was recommended that you drop the indexes on the table before the load and then rebuild them after the load is complete. Is this something you would do?

Answer
This does not make sense.
Explanation
For such a small number of rows, it is unlikely that dropping the indexes will improve the performance of your load. If this were 100,000 rows, then it might make sense.

(Continued on next part...)