You administer a SQL Server 2008 infrastructure.
You administer two SQL Server 2008 instances named Instance1 and Instance2. Instance1 contains the
Sales database, and Instance2 contains the Accounts database.
A procedure in the Sales database starts a transaction. The procedure then updates the Sales.dbo.Order
table and the Accounts.dbo.OrderHistory table through a linked server.
You need to ensure that the transaction uses a two-phase commit.
What should you do?

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?

You administer a SQL Server 2008 infrastructure.
The instance contains a database required for the day-to-day business of your company. Users experience
slow response time when they execute reports.
You plan to design a performance monitoring strategy that captures and stores the following data:
Executed Transact-SQL statements and query activity.
Blocking and deadlock information.
Counters for disk, CPU, and memory.
You need to implement the monitoring process by using the minimum amount of administrative effort.
What should you do?

You administer a SQL Server 2008 instance.
You plan to design a monitoring solution for the instance to monitor object usage statistics.
The solution must identify a list of first 10 objects for each of the following components:
Most frequently executed stored procedures and functions
Long running Transact-SQL statements
You need to implement the monitoring solution to minimize performance effect by using the least amount of
administrative effort.
What should you do?

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.
Service Startup type Account
SQL Server Automatic Local System
SQL Server Agent Automatic Local System
Analysis Services Automatic Local System
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.)

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 instance is managed by you. And 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?

You administer four SQL Server 2008 instances. Each instance hosts a single database application.
You plan to migrate all four instances to a new SQL Server failover cluster.
The four instances are configured as shown in the following table.
Instance Name Configuration
Instance 1 8 processors that have 16-GB RAM
Instance 2 4 processors that have 8-GB RAM
Instance 3 2 processors that have 16-GB RAM
Instance 4 4 Processors that have 8-GB RAM
The four instances are fully optimized and have no spare CPU cycles or extra memory.
The new cluster will host all four databases on a single virtual cluster IP address.
You need to ensure that the new cluster is configured to handle the workload of all the database
applications by using the minimum amount of hardware resources.
Which cluster configuration should you use?

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?

You administer a SQL Server 2008 database solution.
All data modifications are performed through stored procedures that use only the INSERT, UPDATE, or DELETE statements.
You are designing a backup strategy.
You need to ensure that the following business requirements are met:
The backup strategy supports point-in-time recovery for failure at any time of day.
The transaction log uses the least amount of disk space.
What should you do?

You administer a SQL Server 2008 instance that hosts a large database.
The database experiences high volumes of queries and transactions that are processor-intensive.
You plan to implement an incremental backup strategy for the database. You also plan to use backup
compression.
You need to ensure that the backup jobs have a minimal effect on the performance of the server.
What should you do?