Online Access Free 070-450 Practice Test

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.The new database should be set to utilize the TRUSTWORTHY option, and then each user should be added to the MSDB database.
• B.Each user should be added to the public role in the MSDB database.
• C.Each user should be added to the db_dtsltduser database role in the MSDB database.
• D.The MSDB database should be set to utilize the TRUSTWORTHY option, and then each user should be added to the MSDB database.

You are a professional level SQL Sever 2008 Database Administrator.
A database is hosted by the instance and the database is configured by utilizing high-safety mirroring operation mode along with a witness server. A memory failure is experienced by the witness server; therefore the server will be offline for five hours. The mirroring strategy should be reset to cut the risk of database unavailability to the least level.
Which action should you perform to finish the task?

• A.To finish the task, database mirroring should be removed.
• B.To finish the task, database mirroring should be paused.
• C.To finish the task, the witness server should be moved from the mirroring session.
• D.To finish the task, asynchronous operating mode should be utilized.

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 permission 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 Select on Customer.
  Set Update on Order.
• B.Set only Execute on SetCustomerStatus.
• C.Perform the following tasks:
  Set Execute on SetCustomerStatus.
  Set Select on Customer.
  Set Update on Order.
• D.Set only Take Ownership on ustomer and Order.

You administer a SQL Server 2008 infrastructure.
Your company requires capacity planning information.
You need to design a long-term infrastructure monitoring strategy.
Which two tasks should you include in your design? (Each correct answer presents part of the solution. Choose two.)

• A.Clear the system log and the application log every hour.
• B.Create a maintenance plan that rebuilds indexes every week.
• C.Backup all databases every day.
• D.Baseline the system before you deploy production databases.
• E.Review system monitor counters on a regular basis.

You are the database administrator for a SQL Server that hosts an application defined as follows:
The database consists of 4 data files and one log file.
The physical server has 16 CPUs and 64 GB of memory.
Hardware non-uniform memory access (NUMA) is not available on this server platform.
Users of the application report that it is responding slowly. You use SQL Server Profiler to find out that the I/
O
performance issue is related to the lazywriter process.
You need to improve the performance of the application by reducing the I/O performance issue.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.Add additional RAM to the server.
• B.Configure four software non-uniform memory access (soft NUMA) nodes on the server.
• C.Configure a CPU affinity mask on the server to limit the instance of SQL Server to four CPUs
• D.Add additional data files to the database.