QUESTION 111
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 full-recovery model along with transaction log backups should be utilized.
C.    To finish the task, the full-recovery model along with differential backups should be utilized.
D.    To finish the task, the simple-recovery model along with differential backups should be utilized.

Answer: B

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

Answer: D

QUESTION 113
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 fields.
C.    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.
D.    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.

Answer: D

QUESTION 114
You are a professional level SQL Sever 2008 Database Administrator.
A database is included by an instance. And a large table named OrderDetails is included by the database. Only DML statements on the last three months data are executed by the application queries. Administrative audits are managed monthly on data which is longer than four months. The performance problems listed below are found by you in the database . The performance of the application queries against the OrderDetail table is poor. It takes a long time to perform the maintenance tasks against the database, index defragmentation is contained.
The performance problems should be solved with on impact on the server performance.
Which action will you perform?

A.    A database snapshot should be created for the OrderDetails table every four months. And then, the queries should be changed to utilize the present snapshot.
B.    An additional table named OrderDetailsHistory should be created for data older than four months. And then, the OrderDetails and OrderDetailsHistory tables should be partitioned in two parts by utilizing the OrderDate column. At last, a SQL Server Agent job that runs every month should be created and the ALTER TABLE…SWITCH Transact-SQL statement should be utilized to remove data that is Longer than four months to the OrderDetailsHistory table.
C.    An additional table named OrderDetailsHistory should be created for data longer than four months. And then, a SQL Server Agent job that runs the following Transact-SQL statement every month should be created. INSERT INTO OrderDetailsHistory SELECT * FROM OrderDetailsWHERE DATEDIFF (m, OrderDate,GETDATE())>4
D.    An additional table named OrderDetailsHistory should be created for data older than four months. And then, the following Transact-SQL statement should be utilized. CREATE TRIGGER trgMoveDataON OrderDetailsAFTER INSERT ASINSERT INTO OrderDetailsHistory SELECT * FROM OrderDetailsWHERE DATEDIFF(m,OrderDate,GETDATE())>4

Answer: B

QUESTION 115
You are a professional level SQL Sever 2008 Database Administrator in an international corporation named Wiikigo. You are experienced in managing databases in an enterprise-level organization,optimizing and sustaining the database life cycle. In the company, your job is to implement solutions on security, troubleshooting, deployment and optimization.
A SQL Server 2008 infrastructure is managed by you. A corporate backup and recovery strategy that has to be validated is designed.
You are required to make sure that any single database can be restored successfully from a severe failure, and during the restoration, a backup data center should not be required in a different location.
Which three actions should be contained by your solution? (Choose more than one)

A.    All SQL Server instances should be installed on a failover cluster.
B.    One list of all Windows logins and passwords should be sustained.
C.    All backup media offsite should be stored.
D.    SQL login accounts and credentials should be scripted.
E.    The administrative processes and application access requirements should be documented.

Answer: CDE

QUESTION 116
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, the Import and Export Wizard should be utilized to save packages.
B.    To achieve the goal, a direct package configuration should be created for each package.
C.    To achieve the goal, a deployment utility should be created for the SSIS project.
D.    To achieve the goal, an indirect package configuration should be created for all packages.

Answer: C

QUESTION 117
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 paused.
B.    To finish the task, the witness server should be moved from the mirroring session.
C.    To finish the task, asynchronous operating mode should be utilized.
D.    To finish the task, database mirroring should be removed.

Answer: B

QUESTION 118
You are a professional level SQL Sever 2008 Database Administrator.
An instance runs on a computer, and the computer has eight quad-core processors and 128-GB RAM. The instance is utilized by four different applications. Not only a low number of CXPACKET waits but also a large number of lazy writer waits are experienced by the instance. The performance of the instance should be optimized.
Which action will you perform to accomplish the goal?

A.    To accomplish the goal, software non-uniform memory access (soft-NUMA) should be configured.
B.    To accomplish the goal, the Resource Governor should be configured.
C.    To accomplish the goal, the Windows System Resource Manager should be configured.
D.    To accomplish the goal, an increase should be configured in the maximum degree of parallelism option.

Answer: A

QUESTION 119
You administer a SQL Server 2008 instance. The instance is one of three servers in a peer-to-peer transactional replication topology.
The publication contains a table named Orders. The Orders table contains 200 GB of data across multiple partitions. A batch process bulk loads 10 GB of data to the Orders table periodically. You need to design a replication strategy to minimize latency. What should you do?

A.    Configure the Distributor agent to replicate transactions continually.
B.    Configure the Distributor agent to change the commit batch threshold.
C.    Use the BULK INSERT command in multiple staging tables. Switch partitions into the Orders table.
D.    Disable the Distributor agent. Use the BULK INSERT command in the Orders table. Reinitialize the publication.

Answer: C

QUESTION 120
You are a professional level SQL Sever 2008 Database Administrator.
After a regular test, you find that performance degradation is experienced by an instance for the three reasons:
Excessive CPU usage, Server processes paging and Deadlocks
A monitoring solution should be implemented to provide data, monitor and troubleshoot performance issues and detailed deadlock information should be contained in the provided data.
You should utilize the least amount of administrative effort to finish the task.
Which tool will you utilize to finish the task?

A.    To finish the task, you should utilize Resource Governor.
B.    To finish the task, you should utilize Database Engine Tuning Advisor.
C.    To finish the task, you should utilize Extended Events.
D.    To finish the task, you should utilize Performance Monitor (SYSMON).

Answer: C

Real Pass4sure Microsoft 70-450 PDF & VCE Exam Dumps Free Download