QUESTION 81
You are a professional level SQL Sever 2008 Database Administrator.
A large financial database is hosted by the instance. The backup strategies listed below are contained by the database:
* First, the performance of a full database backup is implemented once a week.
* Secondly, the performance of a differential backup is implemented every day.
* Thirdly, the performance of a transaction log backup is implemented every hour.
An end-of-year batch process should be implemented, and the process takes two hours to run. About five percent of data within the database will be changed by the batch process. Since you are the technical support, you are required to make sure that the batch operation can be rolled back in the shortest time, if the batch process is not approved by the Finance department.
What should you do first before start the batch process?

A.    You should perform a differential backup.
B.    You should create a marked transaction. And then you should perform a transaction log backup.
C.    You should create a database snapshot.
D.    You should record the time before the batch operation. And then you should perform a transaction log

Answer: C

QUESTION 82
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 50GB 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 83
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 84
You are a professional level SQL Sever 2008 Database Administrator.
A database is hosted by the instance, and sensitive data is included in the database. A database backup strategy will be implemented for the database. You need to have all backups of the database encrypted. The backups should not be accessed by unauthorized users.
Which action will you perform?

A.    Transparent database encryption should be utilized.
B.    Windows BitLocker Drive Encryption technology should be utilized.
C.    The BACKUP statement should be utilized along with the PASSWORD option.
D.    The BACKUP statement should be utilized along with the MEDIAPASSWORD option.

Answer: A

QUESTION 85
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 a full database backup is implemented at 02:00 hr every Monday. Secondly, the performance of a differential backup is implemented at 23:00 hr every day. Thirdly, the performance of a transaction log backup is implemented on an hourly basis.
A reboot is caused on the SQL Server 2008 server by a power failure at 09:15 hr, Thursday.
Fifteen minutes after the reboot of the server, it is reported that certain queries that access customer data cannot be executed. You find that the customer data remains unvaried after the power failure.
When the DBCC CHECKDB command is executed on the database, the following error message is received.
Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID 82134587923221126 (type In-row data): Page (3421:169) could not be processed. See other errors for details.
Table error: Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID 82134587923221126 (type In-row data), page (3421:169). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 16928422 and -8.CHECKDB found 0 allocation errors and 2 consistency errors in table ‘tbl_Customer’ (object ID 2814307832).
When the sp_help ‘tbl_customer’ stored procedure is executed, the following result set is received:
index_name index_description
PK clustered located on PRIMARY NCIX nonclustered located on PRIMARY
Since you are the technical support, you are required to make sure that the data should be accessible in the shortest time. And the impact on users should be cut to the least.
Which action will you perform to finish the task?

A.    To finish the task, the PK index should be dropped and recreated.
B.    To finish the task, the latest full database backup should be recovered. And all transaction log backups should be recovered from the latest full database backup.
C.    To finish the task, the latest full database backup should be recovered. And then, the latest differential backup should be recovered. At last, all transaction log backups should be recovered from the latest differential backup.
D.    To finish the task, the NCIX index should be dropped and recreated.

Answer: D

QUESTION 86
You are a professional level SQL Sever 2008 Database Administrator.
A new database will be deployed to the instance.
Regular inserts and updates should be subjected by the database. There will be multiple schemas on the database. A great number of read-only reference data should be contained by one of the schemas.
The physical database structure should be designed for optimal backup performance.
Which action should you perform?

A.    You should utilize a single log file and multiple filegroups to create the database.
B.    You should utilize a single data file and a single log file to create the database.
C.    You should utilize a single data file and multiple log files to create the database.
D.    You should utilize a single log file and a filegroup that has multiple data files to create the database.

Answer: A

QUESTION 87
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 88
You are a professional level SQL Sever 2008 Database Administrator.
A large database is hosted by the instance. High volumes of queries and transactions that areprocessor intensive are experienced by the database. An incremental backup strategy should be designed for the database. In addition, backup compression will be utilized.
Since you are the technical support of the company, you are required to reduce the impact from the backup jobs on the performance of the server to the least.
Which action will you perform to achieve the goal?

A.    To achieve the goal, database snapshots should be utilized.
B.    To achieve the goal, the Affinity I/O Mask configuration option should be reconfigured.
C.    To achieve the goal, the database backup should be spread across multiple backup devices.
D.    To achieve the goal, the Resource Governor should be utilized.

Answer: D

QUESTION 89
You are a professional level SQL Sever 2008 Database Administrator.
An authentication method should be designed for SSRS reports that SQL Server data connections are utilized.
Since you are the technical support of the company, you are required to make sure that all SSRS reports utilize the Kerberos authentication protocol to communicate to the SQL Server instance.
Which action should you perform?

A.    The default Web site should be set on the instance to utilize the Integrated Security type.
B.    The application pool utilized by the default Web site should be set to utilize a domain account.
C.    A service principal name (SPN) should be registered in the Active Directory directory service for the SSRS instance.
D.    The default Web site on the instance should be set to utilize the Secure Sockets Layer (SSL) protocol.

Answer: C

QUESTION 90
You are a professional level SQL Sever 2008 Database Administrator.
Two SQL Server 2008 instances are managed by you, and they are respectively called Ins01 and Ins02. The Sales database is included in Ins01, while the Accounts database is contained by Ins02. A transaction is started by A procedure in the Sales database. And then, the Sales.dbo.Order table and the Accounts.dbo. OrderHistory table are updated by the procedure through a linked server.
Since you are the technical support of the company, you are required to make sure that a two-phase commit is utilized by the transaction.
Which action will you perform to finish the task?

A.    To finish the task, a Service Broker should be configured to have the proper transaction control enabled.
B.    To finish the task, the linked server to utilize distributed transactions
C.    To finish the task, the linked server should be properly configured for delegation.
D.    To finish the task, the linked server should be properly configured for impersonation.

Answer: B

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