You administer a SQL Server 2008 instance.
You plan to deploy a new database to the instance. The database will be subject to frequent inserts and
updates.
The database will have multiple schemas. One of the schemas will contain a large amount of read-only
reference data.
You need to design the physical database structure for optimal backup performance.
What should you do?

• A. Create the database by using a single data file and a single log file.
• B. Create the database by using a single log file and a filegroup that has multiple data files.
• C. Create the database by using a single log file and multiple filegroups.
• D. Create the database by using a single data file and multiple log files.

You administer a SQL Server 2008 instance that hosts a database solution in a production environment.
The database solution uses several SQL Server Agent jobs to periodically transfer data from heterogeneous data sources to the production environment. You also have a separate development environment that is used by several development teams. You design a test recovery plan for the database.
You plan to test the recovery plan by performing the following tasks:
Restoring the database on a separate SQL Server 2008 instance in a development environment Running a number of unit tests.
You need to ensure that all database dependencies are included in the recovery plan.
Which two objects should you transfer from the production environment to the development environment? (Each correct answer presents part of the solution. Choose two.)

• A. SQL Server Agent jobs
• B. Custom error messages
• C. master database
• D. Login accounts
• E. msdb database

You administer a SQL Server 2008 infrastructure.
You plan to deploy a new SQL Server 2008 multi-node failover cluster. The failover cluster uses a storage
area network (SAN)
that will use redundant array of independent disks (RAID) level 5.
You need to format the logical unit numbers (LUNs) for optimal performance of the database data files.
Which of the following is the best allocation unit size you should use?

• A. 8-KB
• B. 32-KB
• C. 156-KB
• D. 64-KB

You are a professional level SQL Sever 2008 Database.
A new database application is hosted by the instance. The security requirements should be designed for the application. A unique login to the SQL Server 2008 server is assigned to each application user. Stored procedures are included by the application database to execute stored procedures in the MSDB database. SQLAgent jobs are scheduled by the stored procedures in the MSDB database.
Since you are the technical support, you are required to confirm that the stored procedures in the MSDB database should be executed by utilizing the security context of the application user.
Which action should you perform?

• A. Each user should be added to the db_dtsltduser database role in the MSDB database.
• B. The MSDB database should be set to utilize the TRUSTWORTHY option, and then each user should be added to the MSDB database.
• C. The new database should be set to utilize the TRUSTWORTHY option, and then each user should be added to the MSDB database.
• D. Each user should be added to the public role in the MSDB database.

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 reorganized by reducing the fill factor.
• 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 dropped.