Microsoft PRO:MS SQL Serv 08,Design,Optimize, and Maintain DB Admin Solu Sample Questions:

1. You are a professional level SQL Sever 2008 Database Administrator.
A thirdparty database application will be deployed to the instance. Stored procedures are utilized by the application, and the procedures are developed by utilizing SQL CLR integration. You should set the application to have
the
EXTERNAL_ACCESS code access security setting enabled. Since you are the technical support of the
company,
you should deploy the application to the instance with no loss of functionality.
Which action will you perform first?

A) The peverify.exe PEVerify tool should be utilized to confirm whether the type-safety requirements are satisfied by the code.
B) The regasm.exe assembly registration tool should be utilized to have the assembly registered on the server before deployment.
C) Readwrite non-static fields in the code should be replaced with static fields.
D) Read-only static fields in the code should be replaced with readwrite static fields.

2. 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 Performance Monitor (SYSMON).
D) To finish the task, you should utilize Extended Events.

3. You administer a SQL Server 2008 infrastructure.
You plan to design a solution to obtain hardware configurations, such as the number of processors on a computer and the processor type of all SQL Server 2008 computers.
The solution must meet the following requirements:
It is hosted on the central computer.
It can verify hardware configurations for multiple servers.
You need to select a technology that meets the requirements by using the minimum amount of development effort.
What should you do?

A) Use the Windows Management Instrumentation (WMI) provider for the server events.
B) Define policies based on conditions by using the ExecuteWQL function.
C) Use the Invoke-Sqlcmd cmdlet in SQL Server PowerShell cmdlet.
D) Define policies based on conditions by using the ExecuteSql function.

4. 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) Two-node 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) 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.

5. Your SQL Server 2008 database contains two tables named Customer and Order inside the Sales schema.
The Sales schema is defined as follows:
It has a stored procedure named SetCustomerStatus that reads from Order and modifies Customer.
It is owned by a user named SalesUser.
Another user named MarketingUser wants to be able to call SetCustomerStatus and change the status of Customer.
You need to assign the least permissions necessary to MarketingUser.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A) Perform the following tasks: Set Execute on SetCustomerStatus.
B) Perform the following tasks: Set Select on Customer.
C) Set only Execute on SetCustomerStatus.
D) Set only Take Ownership on Customer and Order.

Solutions: