Online Access Free 070-450 Practice Test

You administer a SQL Server 2008 infrastructure . You design a corporate backup and recovery strategy that has to be validated.
You need to ensure the successful recovery of any single database from a catastrophic failure without requiring a backup data center in a different location.
Which three tasks should you include? (Each correct answer presents part of the solution. Choose three.)

• A.Maintain one list of all Windows logins and passwords.
• B.Store all backup media offsite.
• C.Document the administrative processes and application access requirements.
• D.Install all SQL Server instances on a failover cluster.
• E.Script SQL login accounts and credentials.

You are designing a new SQL Server instance that will support a Web application.
The Web application is hosted on a Web farm that contains 32 nodes.
The server has 128 GB of memory and 16 processor cores. The application contains two databases and supports both online analytical processing (OLAP) and online transaction processing (OLTP) workloads.
When testing the application, you discover that some queries run extremely slow and some queries run very fast.
You need to ensure that the server processes database queries as fast as possible . What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.Execute the following command on the server: exec_sp_configure 'maximum degree of parallelism', 8
• B.Execute the following command on the server: exec_sp_configure 'maximum degree of parallelism', 1
• C.Use SQL Profiler to identify queries that experience CXPACKET waits. Add (OPTION MAXDOP 8) to each query
• D.Use SQL Profiler to identify queries that experience CXPACKET waits. Add (OPTION MAXDOP 1) to each query

You administer a SQL Server 2008 infrastructure.
Developers in your company have rights to author policies. A test server is used to develop and test the
policies.
The Policy-Based Management feature generates SQL Server Agent alerts when a policy is violated.
The developers are able to create and modify policies, but are unable to test policy violation alerts.
You need to grant the necessary permission to the developers to test the policies. You also need to comply
with
the least privilege principle when you grant the permission.
What should you do?

• A.Grant the EXECUTE permission on the sys.sp_syspolicy_execute_policy stored procedure to the developers.
• B.Grant the ALTER TRACE permission to the developers.
• C.Add the developers to the sysadmin server role.
• D.Add the developers to the PolicyAdministratorRole role in the MSDB database.

You administer a SQL Server 2008 instance for your company. Your company has a team of database administrators.
A team of application developers create SQL Server 2008 Integration Services (SSIS) packages on the test server in a shared project. One of the packages requires a fixed cache file. On completion of development, the packages will be deployed to the production server.
Only the database administrators can access the production server.
You need to ensure that the application developers can deploy the project successfully to the production server.
What should you do?

• A.Create a direct package configuration for each package.
• B.Create a deployment utility for the SSIS project.
• C.Use the Import and Export Wizard to save packages.
• D.Create an indirect package configuration for all packages.

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