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

1. 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 snapshot duplication along with each portable computer that is set up as a subscriber.
B) You should utilize transactional duplication along with each portable computer that is set up as a publisher.
C) You should utilize merge duplication along with each portable computer that is set up as a subscriber.
D) 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.

2. 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) T-SQL Query collector
C) Query Activity collector
D) A custom Query collector that uses WMI

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

4. You administer a SQL Server 2008 instance.
You plan to deploy multiple databases to the instance. You design the security policy for the instance.
A stored procedure verifies the partition compression settings on each database deployed to the instance.
You plan to use a policy to execute the stored procedure.
You need to ensure that the policy is enforced by using Policy-Based Management Framework.
What should you do?

A) Enable the nested triggers server configuration option.
B) Grant EXECUTE permissions on the stored procedure to the SQLAgent service account.
C) Grant EXECUTE permissions on the stored procedure to the ##MS_PolicyEventProcessingLogin## login.
D) Grant EXECUTE permissions on the stored procedure to the ##MS_PolicyTsqlExecutionLogin## login.

5. You are a professional level SQL Sever 2008 Database Administrator.
A database is hosted by the instance, and sensitive data is included in the database.
A database backup strategy will be implemented for the database.
You need to have all backups of the database encrypted.
The backups should not be accessed by unauthorized users.
Which action will you perform?

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

Solutions: