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

1. Your SQL Server 2008 database contains two tables named Customer and Order inside the Sales schema.
The Sales schema is defined as follows:
It has a stored procedure named SetCustomerStatus that reads from Order and modifies Customer.
It is owned by a user named SalesUser.
Another user named MarketingUser wants to be able to call SetCustomerStatus and change the status of Customer.
You need to assign the least permissions necessary to MarketingUser.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A) Perform the following tasks: Set Execute on SetCustomerStatus.
B) Set only Execute on SetCustomerStatus.
C) Perform the following tasks: Set Select on Customer.
D) Set only Take Ownership on Customer and Order.

2. You are a professional level SQL Sever 2008 Database Administrator.
A large database is hosted by the instance. High volumes of queries and transactions that are
processor intensive
are experienced by the database. An incremental backup strategy should be designed for the database.
In addition,
backup compression will be utilized.
Since you are the technical support of the company, you are required to reduce the impact from the backup
jobs
on the performance of the server to the least.
Which action will you perform to achieve the goal?

A) To achieve the goal, the Affinity I/O Mask configuration option should be reconfigured.
B) To achieve the goal, database snapshots should be utilized.
C) To achieve the goal, the database backup should be spread across multiple backup devices.
D) To achieve the goal, the Resource Governor should be utilized.

3. 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 the BACKUP statement along with the MEDIAPASSWORD option.
B) Use Windows BitLocker Drive Encryption technology.
C) Use the BACKUP statement along with the PASSWORD option.
D) Use Transparent database encryption.

4. You are a professional level SQL Sever 2008 Database Administrator.
A SQL Server 2008 instance at the New York central site is managed by you, and it is called Ins01.
There is a sales team in your company, and their task is to finish purchase orders for customer requests.
The sales team updates data regularly in a local database by utilizing their portable computers.
When the central site is connected by portable computers, a database named Sales will be synchronized
with
the local database. A duplication model will be created to duplicate the local database to the Sales
database.
The requirements listed below should be satisfied by the duplication model:
First, when the same data is updated by multiple users independently, there should be no data conflicts.
Secondly, sensitive data such as product price cannot be updated by the sales team.
Thirdly, the data should be synchronized by the sales team not only at scheduled times but also on demand.
The best model should be identified to have data duplicated, and you should be reduced the development efforts to the least.
Which action will you perform?

A) You should utilize transactional duplication along with each portable computer that is set up as a publisher.
B) You should utilize SQL Server Integration Services (SSIS) to promote the date modification and pull updates to the Sales database along with the SSIS packages, on demand.
C) You should utilize snapshot duplication along with each portable computer that is set up as a subscriber.
D) You should utilize merge duplication along with each portable computer that is set up as a subscriber.

5. You administer a SQL Server 2008 infrastructure.
A financial application uses several instances across multiple servers. The majority of queries require distributed transactions across all servers.
The application administrator reports that the application experiences excessive deadlocks.
You need to design a data collector type that provides the appropriate information for remote servers.
What collector should you use?

A) Performance Counters collector
B) A custom Query collector that uses WMI
C) T-SQL Query collector
D) Query Activity collector

Solutions: