Mar 2016 updated: Pass4sure Microsoft 70-450 download 61-75

70-450 Royal Pack Testengine pdf

100% Actual & Verified — 100% PASS

Unlimited access to the world's largest Dumps library! Try it Free Today!

Get it now →

Product Description:
Exam Number/Code: 70-450
Exam name: PRO:MS SQL Serv 08,Design,Optimize, & Maintain DB Admin Solu
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Free Certification Real IT 70-450 Exam pdf Collection

Question No. 61

You are a professional level SQL Sever 2008 Database Administrator. 

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 database is utilized by the instance, and the database is utilized by a Web-based application. 15,000 transactions are processed by the application every minute. A column is contained by a table in the database, and the column is utilized only by the application. Sensitive data is stored in this column. The sensitive data should be stored with the highest security level. In addition, the least amount of memory space and processor time should be utilized. 

From the following four encryption types, which one should you utilize? 

A. Asymmetric key encryption should be utilized. 

B. Certificate-based encryption should be utilized. 

C. Symmetric key encryption should be utilized. 

D. Transparent data encryption should be utilized. 

Answer: C


Question No. 62

You administer a SQL Server 2008 instance. 

You plan to deploy multiple databases to the instance. You design the security policy for the instance. 

A stored procedure verifies the partition compression settings on each database deployed to the instance. 

You plan to use a policy to execute the stored procedure. 

You need to ensure that the policy is enforced by using Policy-Based Management Framework. 

What should you do? 

A. Enable the nested triggers server configuration option. 

B. Grant EXECUTE permissions on the stored procedure to the SQLAgent service account. 

C. Grant EXECUTE permissions on the stored procedure to the ##MS_PolicyTsqlExecutionLogin## login. 

D. Grant EXECUTE permissions on the stored procedure to the ##MS_PolicyEventProcessingLogin## login. 

Answer: C


Question No. 63

You are configuring a new SQL Server installation. The SQL Server instance will be used for both online analytical processing (OLAP) and relational data. The data in the server is accessed frequently. 

You can configure the services, startup type, and accounts used by the services that run on the server according to the following table. 


You want to raise the level of security on the server. You need to minimize the surface area for potential attacks on the server. 

What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Change the Startup type property forall the services to Manual. 

B. Change the Startup type property for all the services to Disabled. 

C. Create a single non-administrative local user account and assign it to each of the services. 

D. Create three different non-administrative local user accounts and assign them to each of the services. 

Answer: D


Question No. 64

You are a professional level SQL Sever 2008 Database Administrator. 

They are respectively named Ins01, Ins02, Ins03 and Ins04. A single database application is hosted by each instance. 

All four instances should be removed to a new SQL Server failover cluster. You can see the configuration of the four instances from the table listed below: 


All the four instances are sufficiently optimized, and there are no spare CPU cycles or extra memory in the instances. 

All four databases will be hosted by the new cluster on a single virtual cluster IP address. 

Since you are the technical support, you are required to set the new cluster to handle the workload of all the database applications with the least amount of hardware resources. 

From the four cluster configuration, which one should be utilized? 

A. Four-node active/active/active/active cluster should be utilized and a minimum of 9 processors and a 25-GB memory are contained by each node. 

B. Two-node active/passive cluster should be utilized and a minimum of 18 processors and a 50-GB memory are contained by each node. 

C. Two-node active/active cluster should be utilized and a minimum of 9 processors and a 25-GB memory are contained by each node. 

D. Four-node active/active/active/passive cluster should be utilized and a minimum of 18 processors and a 50-GB memory are contained by each node. 

Answer: B


Question No. 65

You administer a SQL Server 2008 infrastructure. 

An instance runs on a computer that has eight quad-core processors and 128-GB RAM. Four different applications use the instance. 

The instance experiences a low number of CXPACKET waits. The instance also experiences a large number of lazy writer waits. 

You need to optimize the performance of the instance. 

What should you do? 

A. Configure the Resource Governor. 

B. Configure the Windows System Resource Manager. 

C. Configure software non-uniform memory access (soft-NUMA). 

D. Configure an increase in the maximum degree of parallelism option. 

Answer: C


70-450 practice test

Question No. 66

You are a professional level SQL Sever 2008 Database Administrator. 

A large database is hosted by the instance. The company utilizes the following backup strategy for the database: 

-First, the performance of full database backups is implemented weekly. -Secondly, the performance of differential backups is implemented daily. -Thirdly, the performance of transaction log backups is implemented hourly. 

According to the restoring plan, an unscheduled full backup should be performed. A full database backup should be performed, and the scheduled backup strategy should not be interrupted. Form the following four options, which one should be utilized to finish the task? 

A. To finish the task, SKIP should be utilized. 

B. To finish the task, NOINIT should be utilized. 

C. To finish the task, MIRROR TO should be utilized. 

D. To finish the task, COPY_ONLY should be utilized. 

Answer: D


Question No. 67

You administer a SQL Server 2008 database solution that is log-shipped for high-availability purposes. 

The data files of the database are located on drive D. The transaction log files of the database are located on drive E. 

You are designing a recovery test plan to meet the following requirements for the log-shipping solution: 

The secondary database is brought online as quickly as possible. 

The data loss is minimal. 

The data is in a consistent state. 

You need to identify the first step for the recovery test plan when drive D fails. 

Which step should you choose? 

A. Bring the secondary database online. 

B. Perform the tail-log backup of the primary database. 

C. Execute the DBCC CHECKDB command along with the REPAIR_REBUILD option against the primary database. 

D. Execute the DBCC CHECKDB command along with the REPAIR_ALLOW_DATA_LOSS option against the primary database. 

Answer: B


Question No. 68

You are a professional level SQL Sever 2008 Database Administrator. 

A new database named Engineering is deployed. Large documents are managed by the Engineering database, and the documents will be revised occasionally. A table structure should be designed to permit fast read access. 

The storage space requirements should be cut to the least. 

What will you do to achieve the goal? (Choose more than one.) 

A. To achieve the goal, row-level compression should be utilized on the document table. 

B. To achieve the goal, NTFS file system compression should be utilized on the volume. 

C. To achieve the goal, varbinary(MAX) data type should be utilized with FILESTREAM storage. 

D. To achieve the goal, row-level compression should be enabled on all columns that the vardecimal() data type is utilized. 

Answer: BC


Question No. 69

You administer a SQL Server 2008 instance. 

Customers report server performance degradation because of a newly implemented process. 

You use Dynamic Management Views to verify that there are no long running queries. 

You need to correlate the operating system performance data with the actual query execution trace by using minimum administrative effort. 

What should you do? 

A. Use Data Collector. 

B. Use the SQLdiag.exe utility. 

C. Use SQL Server Profiler and System Monitor. 

D. Use SQL Server Profiler and the tracerpt.exe utility. 

Answer: C


Question No. 70

You administer a SQL Server 2008 instance. 

The instance hosts a database that contains sensitive data. 

You plan to implement a database backup strategy for the database. 

You need to ensure that all backups of the database are encrypted. You also need to prevent unauthorized access of the backups. 

What should you do? 

A. Use Windows BitLocker Drive Encryption technology 

B. UseTransparent database encryption 

C. Use the BACKUP statementalong with the PASSWORD option 

D. Use the BACKUP statement along with the MEDIAPASSWORD option 

Answer: B


70-450 exam prep

Question No. 71

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 dropped. 

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

Answer: C


Question No. 72

You administer SQL Server 2008 instances at three sites. 

All sites share a mission-critical database. The business requirements specify that users at each site must be able to perform the following tasks: 

-Access and modify data on all sites with minimal latency. -Minimize data loss in the event of a server failure. 

You need to provide a high-availability solution that meets the business requirements. 

Which solution should you implement? 

A. Failover clustering 

B. Peer-to-Peer replication 

C. Asynchronous database mirroring without a witness server 

D. Log shipping to servers at two of the sites to provide read-only copies of data 

Answer: B


Question No. 73

You administer a SQL Server 2008 infrastructure. 

A SQL Server instance contains a transactional database. 

You design a plan to back up and restore a database. 

The current transactional database backup strategy has the following features: 

-A full backup performed every week on Saturday at 03:00 hr -A differential backup performed on all other days at 03:00 hr -A log backup performed every 60 minutes starting at 00:15 hr 

You simulate a complete hardware failure at 15:29 hr on Monday. The test includes inconsistent application data beginning at 15:00 hr. 

You need to ensure that you can restore the database up You need to ensure that the backup jobs have a minimal effect on the performance of the server to 15:00 hr on Monday. 

Which three steps should you perform? (Each correct answer presents part of the solution. Choose three.) 

A. Restore the differential backups from Monday. 

B. Restore the differential backups from Sunday. 

C. Restore log backups from Monday 02:15 hr to 15:15 hr by using the STANDBY option. 

D. Restore the full back up from Saturday. 

E. Restore log backups from Monday 02:15 hr to 15:15 hr by using the STOPAT Option. 

Answer: ACD


Question No. 74

You administer a SQL Server 2008 instance. The instance is using a 32-bit version on a Windows Server 2008 64-bit server. The awe enabled option is enabled. 

The instance will experience a predictable increase in query activity. 

You plan to ascertain the appropriate time when the migration of the databases to a 64-bit SQL Server 2008 server on the same hardware is beneficial. 

You need to identify a data collector type that provides the appropriate information. 

Which collector should you use? 

A. SQL Trace collector 

B. T-SQL Query collector 

C. Query Activity collector 

D. Performance Counters collector 

Answer: D


Question No. 75

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 o the MSDB database. 

Answer: D