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.
New SQL Server 2008 Integration Services (SSIS) packages will be deployed, and the packages are created by the SQL Server Import and Export Wizard. The packages will utilize stored user names and passwords to connect to external data sources.
Since you are the technical support of your company, you are required to confirm that unauthorized users can not get access to the user names and passwords.
Which action will you perform?

A) No sensitive data should be saved together with the packages, and then the packages should be saved in the SQL Server 2008 server.
B) The SQL Server 2008 server should be utilized to store the packages, and administrate the security in the SQL Server 2008 server.
C) No other data should be saved together with the packages, and then files should be saved to the most secure directory.
D) A common password should be set to make a highest level of security for all information in the package when each package is saved.

2. Your SQL Server 2008 server has only the SQL Server service running. All other SQL Services are disabled.
You have a user named DBReportsUser. You need to restrict DBReportsUser to login only between 17:00 hours and 22:00 hours. You want to achieve this goal by using the minimum amount of administrative effort and the least impact on the Server security.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A) Use a logon trigger.
B) Enable login auditing.
C) Use SQL Profiler.
D) Create a SQL Server Agent job.

3. You administer a SQL Server 2008 instance that hosts a large financial database.
The database has the following backup strategies:
A full database backup is performed once a week.
A differential backup is performed every day.
A transaction log backup is performed every hour.
You plan to execute an end-of-year batch process that takes two hours to run. The batch process will modify about five percent of data within the database.
You need to ensure that if the Finance department does not approve the batch process, the batch operation can be rolled back in minimum possible time.
What should you do prior to starting the batch process?

A) Perform a differential backup.
B) Create a database snapshot.
C) Record the time before the batch operation. Perform a transaction log backup.
D) Create a marked transaction. Perform a transaction log backup.

4. You are a professional level SQL Sever 2008 Database Administrator.
All data changes are implemented through stored procedures, and only the INSERT, UPDATE, or DELETE statements are utilized by the procedures. A backup strategy should be implemented.
The business requirements listed below should be satisfied:
Point-in-time recovery for failure is supported by the backup strategy at any time of day.
The least amount of disk space should be utilized by the transaction log.
Which action should you perform to finish the task?

A) To finish the task, hourly database snapshots should be utilized.
B) To finish the task, the simple-recovery model along with differential backups should be utilized.
C) To finish the task, the full-recovery model along with transaction log backups should be utilized.
D) To finish the task, the full-recovery model along with differential backups should be utilized.

5. You are a professional level SQL Sever 2008 Database Administrator.
All data changes are implemented through stored procedures, and only the INSERT, UPDATE, or DELETE statements are utilized by the procedures. A backup strategy should be implemented.
The business requirements listed below should be satisfied:
Point-in-time recovery for failure is supported by the backup strategy at any time of day.
The least amount of disk space should be utilized by the transaction log.
Which action should you perform to finish the task?

A) To finish the task, hourly database snapshots should be utilized.
B) To finish the task, the simple-recovery model along with differential backups should be utilized.
C) To finish the task, the full-recovery model along with transaction log backups should be utilized.
D) To finish the task, the full-recovery model along with differential backups should be utilized.

Solutions: