Study with 70-450 Actual Test Questions

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. 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.
• C. Two-node active/passive cluster should be utilized and a minimum of 18 processors and a 50-GB memory are contained by each node.
• D. Two-node active/active cluster should be utilized and a minimum of 9 processors and a 25-GB memory are contained by each node.

You are a professional level SQL Sever 2008 Database Administrator.
There are 30 branch offices in DoubleSecurity Insurance, and in the branch offices, customer data are stored in SQL Server 2008 databases. Customer data should be security compliant if it is stored through multiple database instances.
You intend to utilize the Policy-Based Management feature to design a strategy for custom policies. And the format of custom policies is XML format. The requirements listed below should be satisfied.
The company distributes custom policies to all instances. In addition, the company enforces the policies on all instances. A strategy should be thought out and the minimum amount of administrative effort should be utilized.
Which action should you perform to finish the task?

• A. To finish the task, the policies should be distributed by utilizing the Active Directory directory service.
• B. To finish the task, the policies should be distributed by utilizing Group Policy Objects.
• C. To finish the task, a configuration server should be utilized.
• D. To finish the task, the Distributed File System Replication service should be utilized.

You are a professional level SQL Sever 2008 Database Administrator.
It is reported by the customers that the server performance degraded due to a newly implemented process. Dynamic Management Views is utilized to confirm that no long running queries exist.
The operating system performance data should be correlated with the actual query execution trace, and the least administrative effort should be utilized.
Which action will you perform to finish the task?

• A. To finish the task, SQL Server Profiler and the tracerpt.exe utility should be utilized.
• B. To finish the task, Data Collector should be utilized.
• C. To finish the task, SQL Server Profiler and System Monitor should be utilized.
• D. To finish the task, the SQLdiag.exe utility should be utilized.

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 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.
• 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 PK index should be dropped and recreated.
• D. To finish the task, the NCIX index should be dropped and recreated.

You administer a SQL Server 2008 instance.
You plan to deploy a new database to the instance. The database will be subject to frequent inserts and
updates.
The database will have multiple schemas. One of the schemas will contain a large amount of read-only
reference data.
You need to design the physical database structure for optimal backup performance.
What should you do?

• A. Create the database by using a single log file and multiple filegroups.
• B. Create the database by using a single data file and multiple log files.
• C. Create the database by using a single data file and a single log file.
• D. Create the database by using a single log file and a filegroup that has multiple data files.