Online Access Free 070-450 Practice Test

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 offline should be rebuilt once a month.
• C.The clustered index of the Orders table should be excluded from scheduled reorganizing or rebuilding operations.
• D.The clustered index of the Orders table should be dropped.

You administer a SQL Server 2008 infrastructure.
Humongous Insurance has 20 branch offices that store customer data in SQL Server 2008 databases. Customer data that is stored across multiple database instances has to be security compliant.
You plan to design a strategy for custom policies by using the Policy-Based Management feature. Custom policies are in XML format.
The strategy must meet the following requirements:
Custom policies are distributed to all instances.
The policies are enforced on all instances.
You need to implement the strategy by using the least amount of administrative effort.
What should you do?

• A.Use a configuration server.
• B.Distribute the policies by using Group Policy Objects.
• C.Use the Distributed File System Replication service.
• D.Distribute the policies by using the Active Directory directory service

You are a professional level SQL Sever 2008 Database Administrator.
The security requirements should be designed for a new database application. A code segment is utilized by the application, and the code segment includes the following components:
A method that the registry is accessed on the SQL Server
A method that the file system is accessed on a network file server
A class definition that public static fields are utilized
SQL CLR integration is utilized by the code segment
The code segment is implemented as a single assembly.
Since you are the technical support, you are required to make sure that the application should be successfully deployed to the instance.
Which action will you perform?

• A.The SAFE code access security should be utilized for the assembly.
• B.All public static fields should be replaced with public static read-only fields. And then the EXTERNAL_ACCESS code access security should be utilized for the assembly.
• C.All public static fields should be replaced with public fields.
• D.All public static fields should be utilized with public static read-only fields. And then the assembly should be registered by utilizing the regasm.exe utility before deployment.

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

You are a professional level SQL Sever 2008 Database Administrator in an international corporation named
Wiikigo.
There is a team of database administrators in your company.
SQL Server 2008 Integration Services (SSIS) packages are created on the test server in a shared
project by a team of application developers. A fixed cache file is required by one of the packages.
The company will deploy the packages to the production server on completion of development.
The production server is available only to the database administrators.
Since you are the technical support, you need to confirm that the project can be deployed
successfully to the production server by the application developers.
Which action will you perform to achieve the goal?

• A.To achieve the goal, a deployment utility should be created for the SSIS project.
• B.To achieve the goal, the Import and Export Wizard should be utilized to save packages.
• C.To achieve the goal, a direct package configuration should be created for each package.
• D.To achieve the goal, an indirect package configuration should be created for all packages.