Set - 2

Question 1 :

What does a @@fetch_status of -2 mean in SQL Server 2005?

Answer :

The row being fetched is missing.
This means that the row that was being fetched from the cursor is missing.

Question 2 :

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.
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.

Question 3 :

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.
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.

Question 4 :

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.
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.

Question 5 :

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

Answer :

SMO does not support compatibility modes 60 or 65, so you would need to use DMO instead.