Microsoft PRO:MS SQL Serv 08,Design,Optimize, and Maintain DB Admin Solu Sample Questions:

1. You administer a SQL Server 2008 instance.
The instance hosts a database that contains sensitive data. You plan to implement a database backup strategy for the database.
You need to ensure that all backups of the database are encrypted. You also need to prevent unauthorized access of the backups.
What should you do?

A) Use Windows BitLocker Drive Encryption technology.
B) Use Transparent database encryption.
C) Use the BACKUP statement along with the MEDIAPASSWORD option.
D) Use the BACKUP statement along with the PASSWORD option.

2. You administer two SQL Server 2008 instances named Instance1 and Instance2. Instance1 contains the Sales database, and Instance2 contains the Accounts database.
A procedure in the Sales database starts a transaction. The procedure then updates the Sales.dbo.Order table and the Accounts.dbo.OrderHistory table through a linked server.
You need to ensure that the transaction uses a two-phase commit.
What should you do?

A) Ensure that the linked server is appropriately configured for impersonation.
B) Configure a Service Broker to enable the appropriate transaction control.
C) Configure the linked server to use distributed transactions.
D) Ensure that the linked server is appropriately configured for delegation.

3. You administer a SQL Server 2008 infrastructure.
You plan to design a maintenance strategy for a mission-critical database that includes a large table named Orders. The design plan includes index maintenance operations.
You must design the strategy after considering the following facts:
The Orders table in the database is constantly accessed.
New rows are frequently added to the Orders table.
The average fragmentation for the clustered index of the Orders table is less than 2 percent.
The Orders table includes a column of the xml data type.
You need to implement the strategy so that the performance of the queries on the table is optimized.
What should you do?

A) Reorganize the clustered index of the Orders table by decreasing the fill factor.
B) Rebuild the clustered index of the Orders table offline once a month.
C) Exclude the clustered index of the Orders table from scheduled reorganizing or rebuilding operations.
D) Drop the clustered index of the Orders table.

4. You administer a SQL Server 2008 infrastructure.
An instance hosts a business-critical database that must be continuously available to the users without data loss. The database includes Filestream data.
You need to implement a high-availability solution for the site.
Which solution should you use?

A) Failover clustering
B) Database snapshot
C) Synchronous database mirroring with a witness server
D) Asynchronous database mirroring

5. You are a professional level SQL Sever 2008 Database Administrator.
A maintenance strategy should be designed for a mission-critical database, and a large table named Orders is contained by the database. Index maintenance operations are contained in the design plan. When you design the strategy, the facts listed below should be taken into consideration.
First, the users continuously access to the Orders table in the database.
Secondly, a column of the xml data type is contained by Orders table.
Thirdly, the new rows are regularly added to the Orders table.
Fourthly, the average fragmentation for the clustered index of the Orders table is no more than 2
percent.
A strategy should be designed to have the performance of the queries on the table optimized.
Which action will you perform?

A) The clustered index of the Orders table should be dropped.
B) The clustered index of the Orders table should be excluded from scheduled reorganizing or rebuilding operations.
C) The clustered index of the Orders table offline should be rebuilt once a month.
D) The clustered index of the Orders table should be reorganized by reducing the fill factor.

Solutions: