70-432_143題庫+9拖曳題_共152題

Number: 070-432
Passing Score: 900
Time Limit: 100 min
File Version: 1.0
Exam A

QUESTION 1
???
You administer a Microsoft SQL Server 2008 database for an order-processing application. Users report
intermittent poor performance.
You identify the cause as large reports being run by members of the Reporting group.
The following Transact-SQL statements have been run against the database:
CREATE TABLE UserClassification
(GroupName SYSNAME
UserName SYSNAME );
INSERT into UserClassification VALUES
(’ProductionGroup’, ‘User1’),
(‘ProductionGroup’, ‘User2’),
(‘Reporting Group’, ‘User3’),
(‘Reporting Group’, ‘User4’);
CREATE FUNCTION UserClassifier ()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @group SYSNAME
SELECT TOP 1
@group = [Link]
FROM
[Link] uc
WHERE
[Link] = SUSER_SNAME ()
If (@Group IS NOT NULL)
BEGIN
RETURN @Group
END
RETURN N’default’
END;
You need to ensure that members of the reporting group can use only up to 35 percent of the CPU and up
to 45 percent of the available RAM.

Select and Place:

Correct Answer:
Section: (none)
Explanation

Explanation/Reference:

Feedback:
...Because it is the REPORTING group that generates the large reports that can now use only up to 35%
CPU and 45% RAM.
------------------------------------------------------------------------------------------------
[Link]
%3A%2F%[Link]%2Fdownload%2FD%2FB%2FD%2FDBDE7972-1EB9-470A-BA18-
58849DB3EB3B%2FResourceGov.
docx&ei=1pjcULqtD47BswbE9YCoAQ&usg=AFQjCNEESyvR6ppfxi02wgN9EWGKnBbVcA&bvm=bv.1355
534169,[Link]
[Link]
[Link]

QUESTION 2
???
You administer a Microsoft SQL Server 2008 R2 database instance. The service account used by SQL
Server services must not have administrative permissions.
You configure a new SQL Server Agent job to run every night. One of the steps in the job runs a
Powershell (Operating System - CmdExec) step. The job continuously fails on this step and throws the
following error message:
"The process could not be created for step 1 of job (reason: A required privilege is not held by the client).
The step failed."
You need to ensure that the SQL Server Agent Job executes successfully.
Which four actions should you perform in sequence? (To answer, move the appropriate actions from the
list of actions to the answer area and arrange them in the correct order.)

Select and Place:

Correct Answer:
Section: (none)
Explanation

Explanation/Reference:

QUESTION 3
???
You administer a Microsoft SQL Server 2008 R2 database instance.
Occasionally, the following problems occur:
The SQL Server service fails to start.
A query from the application runs slower than expected.
You need to be able to diagnose each of these problems.
Which tool or tools should you use? (To answer, drag the appropriate problem to the correct tool in the
answer area. Each problem may be used once, more than once, or not at all. Additionally, you may need to
drag the split bar between panes or scroll to view content.)
[Click "Select and Place"; Click 'RESET' to correct your answer choices.]

Select and Place:

Correct Answer:

Section: (none)
Explanation

Explanation/Reference:
[Link]
not-starting-what-do-i-do.
Feedback and dispute:
If the SQL Server service fails to start, i think the correct tool is the Windows Application Log and no more.
Dynamic Management Views, SQL Server Profiler, Activity Monitor and SQL Server ErrorLOG depends on
SQL Server service, so, I think the correct tool to find information is the Windows Application Log.
I think the corrects tools for the other question are: SQL Server Profiler, Activity monitor and Dynamic
Management Views.

QUESTION 4
???
You administer a Microsoft SQL Server 2008 R2 instance by using a database named
AdventureWorks2008R2.
You need to implement transparent data encryption for a database hosted by the server.
Which four Transaction-SQL statements should you use? (To answer, move the appropriate SQL
statements from the list of statements to the answer area and arrange them in the correct order.)

Select and Place:

Correct Answer:
Section: (none)
Explanation

Explanation/Reference:
QUESTION 5
You administer a Microsoft SQL Server 2008 R2 database installed along with the default settings.
You want to migrate a database from a SQL Server 2000 server that is being decommissioned. The
application executes a number of Windows command-line calls from stored procedures in the database.
You need to configure SQL Server 2008 R2 to allow command-line calls from the database.
Which three actions should you perform in sequence? (To answer, move the appropriate actions (steps)
from the list of actions to the answer area and arrange them in the correct order.)
[Note: TO ANSWER, Click "SELECT and PLACE". Press "Enter" when done.]

Select and Place:

Correct Answer:
Section: (none)
Explanation

Explanation/Reference:

QUESTION 6
You administer a Microsoft SQL Server 2008 R2 database instance. The instance has a Products table
that is currently in the DevUser schema. The Appusers database is assigned Read and Update
permissions to the table.
The DevUser schema is owned by a database login that has been renamed.
You need to move the Products table from the DevUser schema to the dbo schema.
Which two actions should you perform in sequence? (To answer, move the appropriate actions from the
list of actions to the answer area and arrange them in the correct order.)

Select and Place:

Correct Answer:

Section: (none)
Explanation

Explanation/Reference:
Feedback:
The way I remember this is as follows:
ALTER SCHEMA [TO NEW <SCHEMA> TO BE MODIFIED] TRANSFER [FROM OLD
<SCHEMA.
TABLE> to be moved into the new schema] within the same database.
So following my above formula, the correct answer would be:
ALTER SCHEMA dbo TRANSFER [Link]
To change the schema of a table or view by using SQL Server Management Studio, in Object Explorer,
right-click the table or view and then click Design. Press F4 to open the Properties window. In the Schema
box, select a new schema from the drop down arrow.

QUESTION 7
You administer a Microsoft SQL Server 2008 database.
Backups are performed according to the following schedule:
Full database backup at 00:00 hours daily.
Transaction log backups at 06:00 hours, 12:00 hours, and 18:00 hours daily.
You discover that the database failed at 10:18 hours. You also discover that the database is no longer
accessible due to a failure of the hard drive that contains the database data file.
You need to restore the database. You also need to ensure that data loss is minimal.
Which four actions should you perform in sequence? (To answer, move the appropriate actions from the
list of actions to the answer area and arrange them in the correct order.)

Select and Place:

Correct Answer:
Section: (none)
Explanation

Explanation/Reference:

QUESTION 8
You administer a Microsoft SQL Server 2008 R2 database instance that supports a sales management
application.
Occasionally, users of the application report that they receive a deadlock error. You plan to troubleshoot
this error.
You need to ensure that the instance is configured to capture troubleshooting information that will create
an XML document detailing the deadlock condition.
Which two actions should you perform in sequence? (To answer, move the appropriate actions from the
list of actions to the answer area and arrange them in the correct order.)

Select and Place:

Correct Answer:
Section: (none)
Explanation

Explanation/Reference:

QUESTION 9
You administer a Microsoft SQL Server 2008 R2 database.
You configure the disk drives according to the following table:

You discover that the Windows application log is being filled with entries from a SQL Server Audit process
named DatabaseAudit. The volume of these events is causing older events to be removed from the log.
You need to ensure that the following requirements are met:
SQL Server Audit information is stored in the
D:\AuditLogs folder.
No data is currently lost in the Windows application log.
Which four actions should you perform in sequence? (To answer, move the appropriate actions from the
list of actions to the answer area and arrange them in the correct order.)

Select and Place:

Correct Answer:

Section: (none)
Explanation

Explanation/Reference:
CORRECT STEPS:
1. Create a folder named AuditLogs on drive D.
2. Open Microsoft SQL Server Management Studio and connect to the server.
3. Browse to the Security\Audits folder and double-click the DatabaseAudit object.
4. Change the Audit Destination to File and specify D\:AuditLogs as the file path.

QUESTION 10
You administer a Microsoft SQL Server 2008 R2 server that hosts two databases named NorthwindOLAP
and NorthwindOLTP. Cross database ownership chaining is currently disabled for all databases. A stored
procedure in the NorthwindOLTP database writes data to a table in the NorthwindOLAP database. The
public role in both databases has only Read and Execute permissions assigned to all objects. All objects
are located in the DBO schema, Guest access has been disabled for all databases on the server. You
need to ensure that the stored procedure can execute successfully. Which two actions should you perform
in sequence? (To answer: move the appropriate actions from the list of actions to the answer area and
arrange them in the correct order)

A. Enable Guest access to the NorthwindOLTP database.

B. Enable Guest access to the NorthwindOLAP database.
C. Enable Cross database ownership chaining for the Server instance
D. Enable Cross database ownership chaining for the NorthwindOLTP database.
E. Enable Cross database ownership chaining for the NorthwindOLAP database.
F. Disable Cross database ownership chaining for the Server instance

Correct Answer: CE
Section: (none)
Explanation

Explanation/Reference:
CDOC(Cross database ownership chaining) allows you to transfer execution authority across databases.
When enabled, the owner of the database containing the object being called effectively cedes control to
another database owner. 先啟動Server CDOC再啟動授權出去的Database. / CH11. Designing SQL Server
Security

QUESTION 11
Note:This question is part of a series of questions that use the same or similar answer [Link] answer
choice may be correct for more than one question in the [Link] question is independent of the other
questions in this [Link] and details provided in a question apply only to that [Link]
administer a Microsoft SQL Server 2008 R2 database that contains an Orderltems table. The table has the
following definition:CREATE TABLE[Orderltems](OderID INT NOT [Link] DATETIME NOT
NULL,OrderLine INT NOT NULL,ProductID INT NOT NULL,Quantity INT NOT NULL,PriceEach MONEY
NOT NULL.);Currently,the table is partitioned by year with each year in its own [Link] need to move
the data from the oldest year into a new table in a different Schema to archive the data. What should you
do ?

A. Use the ALTER PARTITION FUNCTION...SPLIT RANGE statement.

B. Run the following statemnet:EXECUTE sp_tableoption@TableNamePattern = 'OrderltemsName =
'PartitionByYear',@OptionValue="true",
C. Create a new partition [Link] a new partition [Link] a clustered index to place the data
onto the partition scheme.
D. Use the ALTER TABLE statement to remove the COLLAE option.
E. Execute the DBCC CLEANTABLEcommand on the Orderltems table.
F. Remove the clustered index from the table.
G. Create a new [Link] a new database File. Use the ALTER PARTITION SCHEME statement
along with the NEXT USED [Link] the ALTER PARTITION FUNCTION statement along with the
SPLIT RANGE clause.
H. Run the following statement:CREATE PARTITION SCHEME SEC_FGAS PARTITION FUNC_FGALL
TO (SECONDARY);
I. Create a new [Link] a new database [Link] the ALTER PARTITION SCHEME statement
along with the NEXT USED [Link] ALTER INDEX REORGANIZE statement.
J. Create a new table. Use the ALTER TABLE statement along with SWITCH PARTITION [Link] the
ALTER PARTITION FUNCTION statemnet along with the MERGE RANGE clause.

Correct Answer: J
Section: (none)
Explanation

Explanation/Reference:
1. 認識資料庫檔案規劃Filegroup Based /Ch2
2. 了解Parttition 的目的與作法 / Ch6
3. 學習Partition management: Split & Merge, Partition Switch /Ch6

QUESTION 12
Note:This question is part of a series of questions that use the same or similar answer [Link] answer
choice may be correct for more than one question in the [Link] question is independent of the other
questions in this [Link] and details provided in a question apply only to that [Link]
administer a Microsoft SQL Server 2008 R2 database that contains an Orderltems table. The table has the
following definition:CREATE TABLE[Orderltems](OderID INT NOT [Link] DATETIME NOT
NULL,OrderLine INT NOT NULL,ProductID INT NOT NULL,Quantity INT NOT NULL,PriceEach MONEY
NOT NULL.); Data is grouped into quarterly [Link] need to configure the groupings into 12 monthly
partitions. What should you do ?

A. Use the ALTER PARTITION FUNCTION...SPLIT RANGE statement.

B. Create a new [Link] a new database [Link] the ALTER PARTITION SCHEME statement
along with the NEXT USED [Link] ALTER INDEX REORGANIZE statement.
C. Use the ALTER TABLE statement to remove the COLLAE option.
D. Create a new [Link] a new database File. Use the ALTER PARTITION SCHEME statement
along with the NEXT USED [Link] the ALTER PARTITION FUNCTION statement along with the
SPLIT RANGE clause.
E. Remove the clustered index from the table.
F. Run the following statemnet:EXECUTE sp_tableoption@TableNamePattern = 'OrderltemsName =
'PartitionByYear',@OptionValue="true",
G. Execute the DBCC CLEANTABLEcommand on the Orderltems table.
H. Create a new partition [Link] a new partition [Link] a clustered index to place the data
onto the partition scheme.
I. Create a new table. Use the ALTER TABLE statement along with SWITCH PARTITION [Link] the
ALTER PARTITION FUNCTION statemnet along with the MERGE RANGE clause.
J. Run the following statement:CREATE PARTITION SCHEME SEC_FGAS PARTITION FUNC_FGALL
TO (SECONDARY);

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:
1.了解Parttition 的目的與作法 / Ch6
2. 學習Partition management: Split & Merge / Ch6
3. 參考 Q11

QUESTION 13
You maintain an instance of Microsoft SQL Server 2008, The instance contains a database named
Finance, The recovery model of the Finance database is set to Full, Afull database backup of all the user
databases is performed at 02:00 hours daily. The transaction log backup occurs every 15 minutes, A
differential backup is performed every 4 hours. You plan to perform a full backup of the Finance database
at 11:00 hours. You need to ensure that the backup is performed without affecting the overall backup and
restore procedures for the Finance database, You also need to ensure that the backup files are restored in
proper sequence. Which Transact-SQL statement should you use?

A. BACKUP DATABASE FINANCE TO DISK = 't:\backups\[Link]' WITH COPY_ONLY;

B. BACKUP LOG FINANCE TO DISK = 't:\backups\[Link]'
C. BACKUP DATABASE FINANCE TO DISK = 't:\backups\[Link]' WITH DIFFERENTIAL;
D. BACKUP DATABASE FINANCE TO DISK = 't:\backups\[Link]' WITH NOUNLOAD,

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:
Backup & Restore / Ch9
1. 認識Recovery model: Full, Bulk-looged, Simple /Ch2
2. 認識三種Backup: Full, Differential, Transaction
3. 本提強調新增加的BCKUP不影響原有備份還原計畫, 所以要採用with copy_only

QUESTION 14
You administer a SQL Server 2008 instance. You need to dentify the network protocol used by the current
connection . What should you do ?

A. Use the T-SQL template in Profiler

B. Use a Dynamic Management View
C. View the SQL error log
D. Set a trace flag

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:
1. Dynamic management views (DMVs) and dynamic management functions (DMFs) provide the
instrumentation infrastructure that allows database administrators to retrieve system information as well as
monitor, diagnose, and fix problems. / Ch13
2. Sys.dm_exec_connections contains one row for each connection to the instance. Within this view, you
can find out when the connection was made along with connection properties and encryption settings. This
view also tells you the total number of reads and writes for the connection, as well as the last time a read
or write was executed.

QUESTION 15
You administer a SQL Server 2008 instance, You join two tables on a column named CompanyName by
using the following query: SELECT s.*. i.*FROM SensitiveTblAS slNNER JOIN lnsensitiveTbl AS iON
iCompanyName When you execute the query, the following error is returned. "Msg 468, Level 16, State 9,
Line 17Cannot resolve the collation conflict between 'SQL_Latin1_General_CP1_CS_AS‘ and
'SQL_Latin1_General_CP1_Cl_AS' in the equal to operation," You need to modify the ON clause of the
query to successfully perform a case-sensitive join. Which ON clause should you use?

A. ON LOWER([Link]) = LOWER([Link])
B. ON UPPER([Link]) = UPPER([Link])
C. ON [Link] = [Link] COLLATE SQL_Latin1_Generel_CP1_CI_AS
D. ON [Link] = [Link] COLLATE SQL_Latin1_Generel_CP1_CS_AS

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:
1. SQL Server allows you to define the rules for comparison, sorting, case sensitivity, and accent sensitivity
through the specification of a collation sequence.
2. Collation sequences can be specified at the instance, database, table, and column levels.
3. COLLATE SQL_Latin1_General_CP1_CS_AS CS 是case sensitive
4. 本題合併的兩個資料表collate sequence不同無法比較，所以必須設定成一致

QUESTION 16
You install a Microsoft SQL Server 2008 R2 instance that uses default settings. You want to add a storage
area network(SAN)array. The SAN has two mapped drives:D and L. You need to ensure that all new
database meet the following requirements:Data files are stored on drive D in the SQLData folder. Log Files
are stored on drive L in the SQLLogs folder. Which three actions should you perform in sequence?(To
answer,move the appropriate actions from the list of actions to the answer area and arrange them in the
correct order.)

A. Restart the SQL Server service

B. Restart the SQL Browser service
C. Run the following command : EXEC xp_instance_regwrite N"HKEY_LOCAL_MACHINE',N'Software
\Microsoft\MSSQL Server\MSSQL Server',N'DefaultData',REG_SZ,N'D:\SQLData'
D. Run the following command : EXEC xp_instance_regwrite N"HKEY_LOCAL_MACHINE',N'Software
\Microsoft\MSSQL Server\MSSQL Server',N'DefaultData',REG_SZ,N'L:\SQLLogs'
E. Run the following command : EXEC sp_serveroption @ optname='LogFileLocation',@optvalue='L:
\SQLLogs'
F. Run the following command : EXEC sp_serveroption @ optname='DatabaseFileLocation',
@optvalue='D:\SQLData'
G. Configure SQL Server to start with the following options:-d ' \D:SQLData'-l'L:\SQLLogs'

Correct Answer: ACD

Section: (none)
Explanation

Explanation/Reference:
1. EXEC xp_instance_regwrite: SQL Startup setting the Registry Key
2. Only specifying the Registry Key for your instance, and the data paths you want. Then all new database
files will be created here by default.

QUESTION 17
You maintain a SQL Server 2008 instance . You use the Resource Governor to manage processor
resources on the server for a newly deployed application named App1, App1 is assigned to a custom
workload group named myApp .You need to monitor processor resources. What should you do?

A. Use the request_max_cpu_time_sec column of the sys.resource_governor_workload_groups datalog

view.
B. Ues the sys.dm_os_performance_counters to monitor the CPU usage % counter of the SQLServer:
Workload Group Stats performance object for the default workload group.
C. Ues the sys.dm_os_performance_counters to monitor the CPU usage % counter of the SQLServer:
Workload Group Stats performance object for the MyApp workload group.
D. Ues the sys.dm_resource_governor_workload_groups dynamic managment view to monitor the CPU
usage for the default workload group.

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:
1. 認識 resource governor: 對各連線的資源(CPU & Memory)分配管理
2. 學習如何利用system monitor, 監視各種資訊,
3. 本題用到 SQLServer:Workload Group Stats performance object

QUESTION 18
Note This question is part of a series of questions that use the same or similar answer choices. An answer
choice may be correct for more than one question in the series. Each question is independent of the other
question in this [Link] and details provided in a question apply only to that [Link]
administer a Microsoft SQL Server 2008 R2 [Link] database has been marked as [Link]
need to place the database into an EMERGENCY state for troubleshooting. What should you do?

A. Use the RESTORE DATABASEstatment along with the [Link] any differential and log
[Link] a new log backup and then restore the new log backup.
B. Use the RESTORE DATABASEstatment along with the [Link] a new log [Link]
all differential and log [Link] the most recent backup.
C. Execute sp_helpfile.
D. Restore the data from the most recent full [Link] any differential and log backups.
E. Use the ALTER DATABASE statement along with the SET EMERGENCY clause.
F. Examine the msdb.suspect_pagestable.
G. Examine DBCC CHECKEB.
H. Examine DBCC CHECKEBalong with the REPAIR_FAST clause.
I. Examine DBCC CHECKEBalong with the REPAIR_REBUILD clause.

Correct Answer: E
Section: (none)
Explanation

Explanation/Reference:
1. ALTER DATABASE database_name SET EMERGENCY ON
2. EMERGENCY 主要用於進行疑難排解。例如，由於記錄檔損毀而被標示有疑問的資料庫可以設為
EMERGENCY 狀態。在這個情況下，系統管理員可以進行資料庫的唯讀存取。只有系統管理員 (sysadmin)
固定伺服器角色的成員，才能將資料庫的狀態設為 EMERGENCY。

QUESTION 19
[Link] administer a Microsoft SQL Sever 2008 R2 [Link] has been configured between two
[Link] need to ensure that automatic failover will occur when the principal sever [Link] should you
do?

A. Create a witness server to monitor the mirroring [Link] the principal server, use the ALTER
DATABASE statement along with the SET WITNESS clause.
B. Create a witness server to monitor the mirroring [Link] both partner servers, use the ALTER
DATABASE statement along with the SET WITNESS clause.
C. Create a witness server to monitor the mirroring [Link] the principal server, use Microsoft SQL
Server Management Studio to define the witness server and start mirroring.
D. Implement Policy Based [Link] the Server Performance management facet.
E. Create a witness server to monitor the mirroring session. On both partner server,use Microsoft SQL
Server Management Studio to define the witness server and start mirroring.
F. Use Service Broker to queue requests from the principal server.
G. Create a Microsoft SQL Server Integration Services package on the partner server to monitor the
principal server.
H. Create a server-trigger for the ON_SHUTDOWN event that fails over the mirror if the server is shut
down.

Correct Answer: AC
Section: (none)
Explanation

Explanation/Reference:
1. 請參閱PPT或PDF認識databse mirroring
2. automatic failover 需要witness server
3. 選項A使用指令，選項C使用工具設定

QUESTION 20
You administer two SQL Server 2008 instances named lnstance1 and Instance2. A database named
AdventureWorks resides on Instance1. You move the AdventureWorks database from lnstance1 to
Instance2. A SQL Server login named Mary with the password "Re#99$45" is used by a user to access the
database on Instance1. You create the same SQL Server login on Instance2. The user attempts to access
the AdventureWorks database on Instance2 by using the SQL Sewer login Mary. However, the user
receives an error message which indicates that the access to the AdventureWorks database is denied.
You need to ensure that the user can access the AdventureWorks database Which Transact-SOL
statements should you execute on Instance2?
A. USE AdventureWorks;ALTER LOGIN Mary WITH DEFAULT_DATABASE AdventureWorks;
B. USE AdventureWorks;ALTER LOGIN Mary ENABLE;
C. USE AdventureWorks;ALTER USER Mary WITH LOGIN = Mary;
D. USE AdventureWorks;ALTER LOGIN Mary WITH PASSWORD = 'Re#99$45' UNLOCK.

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:
1. 了解server 登入者與DB 的區隔以及對應關係
2. If you haven’t explicitly been granted permission, you cannot perform an action. You grant access to a
database by adding a login to the database as a user

QUESTION 21
You maintain a SQL Server 2008 instance, The SQL Server instance is configured to use the -T1222 and -
T1204 trace flags during startup. You need to ensure that your failure recovery plan backs up the use of
the trace flags. What should you do?

A. Backup the master database

B. Backup the [Link] file,
C. Backup the SQL Server registry hive.
D. Backup the resource database

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:
1. 認識SQL Server Profiler 以及Trace
2. 認識Trace Flags

QUESTION 22
You maintain several SQL Server 2008 instances, You need to ensure that the names of all user-defined
stored procedures contain the on all instances, You also need to ensure that you cannot create stored
procedures that do not contain this prefix. What should you do?

A. Create a condition that targets the name of stored procedure that is evaluated on demand
B. Create a policy that targets the name of the stored procedure that is evaluated on change
C. Create a condition that targets the name of the stored procedure that is evaluated on change
D. Create a policy that targets the name of the Stored procedure that is evaluated on demand

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:
1. 認識policy(原則管理)
2. Facet, Condition, Policy
3. Evaluation Models(評估模式)

QUESTION 23
You administer a Microsoft SQL Server 2008 R2 database installed along with the default settings. You
want to migrate a database from a SQL Server 2000 server that is being decommissioned. The application
instantiates ActiveX objects from stored procedures in the database by using sp_OACreate. You need to
configure SQL Server 2008 R2 to allow command-line calls from this database. Which three actions should
you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer
area and arrange them in the correct order.)

A. Use Microsoft SQL Server Management Studio to connect to the new SQL Server by using an account
 that has standard permissions.
B. Use Microsoft SQL Server Management Studio to connect to the new SQL Server by using an account
that has administrative permissions.
C. Create a new SQL Server Agent job and add a new operating system step to the job. Move the legacy
stored procedures to the new SQL Server Agent job.
D. Browse to the SQL Server Agent in Microsoft SQL Server Management Studio, right-click, and select
Facets. Select the Surface Area Configuration facet, and set the XPCmdShellEnabled property to True.
E. Run the following statement: EXEC sp_configure 'enable command line', 1RECONFIGUREGO
F. Run the following statement: EXEC sp_configure 'show advanced options', 1RECONFIGUREGO
G. Run the following statement: EXEC sp_configure Ole Automation Procedures, 1RECONFIGUREGO
H. Run the following statement: EXEC sp_configure 'compatibility mode', 1RECONFIGUREGO

Correct Answer: BFG

Section: (none)
Explanation

Explanation/Reference:
1. 認識如何啟用OLE Automation
2. 認識Ole Automation Procedure參數的用途與設定方式

QUESTION 24
Note This question is part of a series of questions that use the same or similar answer choices. An anser
choice may be correct for more than one question in the series. Each question is independent of the other
questions in this series. Information and details provided in a question apply only to that question. You
administer a Microsoft SQL Server 2008 R2 instance. The instance has a database named
CustomerOrders. The database is backed up by using the weekly scheduled SQL Server Agent jobs. The
backup schedule is shown in the following table: A drive fails and the CustomerOrders database goes into
suspect mode on Friday at 09:00 hours. You need to restore the backup. What should you do?

A. Restore the backups taken on Wednesday and Thursday at 09:00 hours.

B. Restore the backups taken on Wednesday and Friday.
C. Restore the backup taken on Wednesday only.
D. Restore the backups taken on Sunday and Monday.
E. Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.
F. Restore the backups taken on Wednesday, Thursday, and Friday.
G. Restore the backups taken Wednesday, Friday, and Saturday.
H. Restor the backup taken on Sunday only.

Correct Answer: E
Section: (none)
Explanation

Explanation/Reference:
Sorry! No schedule table!

QUESTION 25
You administer a SQL Server 2008 instance, You need to allow users on the SQL Server instance to query
remote data sources by using the OPENROWSET() function, Which sp_configure configuration option
should you configure?

A. remote access
B. remote proc trans
C. Agent XPS
D. Ad Hoc Distributed Queries

Correct Answer: D
Section: (none)
Explanation
Explanation/Reference:
Configuring the SQL Server Surface Area / Ch11-Lesson2

QUESTION 26
You administer a Microsoft SQL Server 2008 R2 database that contains customer invoice accounts.
Backups are performed according to the following schedule: Full
database backup at 00:00 hours daily. Transaction log backups at 06:00 hours, 14:00 hours, and 20:00
hours daily. You discover that the database failed at 11:59 hours. You also discover that the database is
no longer accessible due to a failure of the hard disk drive that contains the database data file. You need to
restore the database. You also need to ensure that data loss in minimal. Which four actions should you
perform in sequence? (To answer,move the appropriate actions from the list of actions to the answer area
and arrange them in the correct order.

A. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option.
B. Restore the most recent full database backup by using the WITH NORECOVERY option.
C. Back up the transaction log by using the WITH NO_TRUNCATE option on the BACKUP LOG
statement.
D. Restore the transaction log backup from 06:00 hours by using the WITH RECOVERY option.
E. Restore the last transaction log backup by using the WITH RECOVERY option.
F. Restore the most recent full database backup by using the WITH RECOVERY option.

Correct Answer: ABCE

Section: (none)
Explanation

Explanation/Reference:

QUESTION 27
[Link] administer a SQL Server 2008 R2 database instance that supports a sales management
application. Occasionally, users of the application report that they receive a deadlock error. You plan to
troubleshoot this error. You need to ensure that the instance is configured to capture troubleshooting
information that will create an XML document detailing the deadlock condition. Which two actions should
you perform in sequence?(To answer, move appropriate actions form the list of actions to the answer area
and arrange them in correct order.)

A. Restart the SQL Server service

B. Use the SQL Server Configuration Manager tool to enable the Shared Memory protocol.
C. Use Microsoft SQL Server Configuration Management Studio to the OleAutomationEnabled
management facet.
D. Use Microsoft SQL Server Configuration Management Studio to the OleAutomationEnabled
management facet.
E. Use the SQL Server Configuration Manager tool to enable Trace Flag 1222 when the server starts.

Correct Answer: AE
Section: (none)
Explanation

Explanation/Reference:

QUESTION 28
You maintain a SQL Server 2008 instance that contains a database named DB1 stores customer data for
the company. The customers use a Web application to access their profile data. You need to protect the
customer data such that data log files, and subsequent backups are as secure as possible even if the
backup media is lost, Your solution must not affect the Web application or impact performance. What
should you do?

A. Encrypt the customer data at the folder level by using Encrypted File System (EFS) and then back up
 the transaction logs
B. Encrypt the customer data at the cell level and then back up DB1
C. Enable Transparent Database Encryption for DB1 and then back up the transaction logs
D. Configure access to DB1 to only use stored procedures and functions

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 29
You migrate an application named App1 from Microsoft SQL Server 2000 to SQL Server 2008. You plan
to monitor the SQL Server instance. You need to capture the use of features that will be discontinued.
What should you do?

A. Run the SQL Server Profiler and capture the Exception event class
B. Create a SQL server-side trace that captures the Deprecation Announcement and Deprecation Final
Support event classes
C. Run the SQL Sewer 2008 Upgrade Advisor
D. Create a Data Collector Set that captures the Microsoft-WindowsCOMRuntime event trace provider

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 30
Note This question is part of a series of questions that use the same or similar answer choices. An anser
choice may be correct for more than one question in the series. Each question is independent of the other
questions in this series. Information and details provided in a question apply only to that question. You
administer a Microsoft SQL Server 2008 R2 instance. The instance has a database named
CustomerOrders. The database is backed up by using the weekly scheduled SQL Server Agent jobs. The
backup schedule is shown in the following table: A drive fails and the CustomerOrders database goes into
suspect mode on Thursday at 17:00 hours. You need to restore the backup. What should you do?

A. Restore the backups taken on Wednesday, Thursday, and Friday.

B. Restore the backups taken on Wednesday and Friday.
C. Restore the backups taken on Wednesday and Thursday at 09:00 hours.
D. Restore the backup taken on Sunday only.
E. Restore the backup taken on Wednesday only.
F. Restore the backups taken on Sunday and Monday.
G. Restore the backups taken Wednesday, Friday and Saturday.
H. Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 31
You administer a SQL Server 2008 cluster. You plan to test the failover response of the cluster. You need
to implement a manual failover on the cluster. Which two actions should you perform?(Each correct
answer present a complete solution. Choose two.)
A. Restore a backup to the active node.
B. Remove the shared array from the active node.
C. Umplug the network cable(s) from the active node.
D. Use Encrypting File System (EFS) to encrypt the contents of a shared folder on the active node.

Correct Answer: BC
Section: (none)
Explanation

Explanation/Reference:

QUESTION 32
You administer a SQL Sever 2008 instance that contains a database named Sales. The Sales database
contains a table named Customers that has a column named CompanyName. The current database
collation is SQL_Latin1_General_Cp1_Cl_AS. You need to ensure that the indexes which include the
CompanyName column are case-sensitive. You also need to ensure that other case-insensitive queries
are unaffected. What should you?

A. Use the CAST function in the filter criteria on all queries that filter on the CompanyName column.
B. Use the UPPER function in the filter criteria on all queries that filter on the CompanyName column.
C. Drop all indexes on the Sales [Link] the database to specify
SQL_Latin1_General_Cp1_CS_AS as the default [Link] all the indexes.
D. Drop all indexes on the Customer table that include the CompanyName column in the index [Link]
the column to specify the SQL_Latin1_General_Cp1_CS_AS [Link] the companyName
indexes on the Customer table.

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 33
You maintain a SQL Server 2008 instance. Your failure recovery plan requires you to back up the default
trace. You need to backup the subdirectory that contains the default trace. Which SQL Server database
engine subdirectory should you back up?

A. INSTALL
B. DATA
C. LOG
D. BINN

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 34
You maintain a SQL Server 2008 instance that contains a database named OrderEntry. The OrderEntry
database contains a table named OrderDelivery, You add a column named DeliveryPoint to the
OrderDelivery table. The DeliveryPoint column is of the Geography data type. The OrderDelivery table
does not contain indexes You need to create a spatial index on the DelrveryPoint column, What should you
do first?

A. Ensure that the DeliveryPoint column does not allow NULL values
B. Create a clustered index for the OrderDelivery table.
C. Define a primary key for the OrderDelivery table.
D. Copy the OrderDeIivery data to a temporary table and truncate the existing OrderDelivery table.

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 35
You administer multiple SQL Server 2008 instances. You plan to install a SQL Server 2008 mission-critical
cluster on two cluster-ready nodes The mission-critical cluster requires constant availability. You need to
configure the SQL Server cluster to failover by ensuring that the service disruption is minimized. Which
failover option should you use?

A. Allow failback only during business hours

B. Allow fallback only during non-business hours
C. Prevent automatic failback
D. Immediately allow failback

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 36
You maintain a SQL Server 2008 instance that contains a database named Finance. The Finance
database currently contains 100 GB of data. On an average, 1 GB of data is modified or inserted daily.
The recovery model of the Finance database is setto Simple Business requirements specify that data loss
of more than two hours worth of transactions during business hours is unacceptable. You need to select a
backup strategy that uses the least amount of disk space by adhering to the business requirements. What
should you do?

A. Perform a full database backup once daily. Perform a differential backup every two hours during
business hours
B. Perform a full database backup once daily
C. Perfonn a full database backup once daily. Perform a transaction log backup every two hours during
business hours
D. Perform a full database backup once every week. Perform a differential backup once daily. Perform a
transaction log backup every two hours during business hours

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 37
You maintain a SQL Server 2008 instance that contains a database named Finance. The data and the
transaction log file are located on the E: drive. The E: drive has only 5 percent available space. You need
to move both files to the V: drive. Which procedure should you use?

A. Run the following Transact-SQL statement; ALTER DATABASE Finance SET RESTRICTED_USER
WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run
the following Transact-SQL statements: ALTER DATABASE Finance MODIFY FILE(NAME =
 Finance_Data, FILENAME = 'v:\SQ: Server\Finance_Data.mdf'); ALTER DATABASE Finance SET
MULTI_USER;
B. Stop the SQL Server service. Move the data to the new location. Start the SQL Server service. Run
the following Transact-SQL statement: EXEC sp_attach_single_f||e_db @dbname = N'Finance',
@physname = N'v:\SQL Server\Finance_Data.mdf';
C. Run the following Transact-SQL statement: ALTER DATABASE Finance SET OFFLINE WITH
ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the
following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE(NAME =
Finance_Data, FILENAME = 'v:\SQLServer\Finance_Data.mdf); ALTER DATABASE Finance MODIFY
FILE(NAME Finance_Log. FILENAME = 'v;\SQLServer\Finance_Log,ldf'); ALTER DATABASE Finance
SET ONLINE;
D. Stop the SQL Server Service. Move the data file and trancaction log file to the new location. Start the
SQL Server service. Run the following Trancact-SQL statement. DXEC sp_attach_db@dbname =
N'Finance',@filename1 = N'v:\SQLServer\Finance_Data.mdf',@filename2 - N'v:\SQLServer
\Finance_Log.ldf'.

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 38
You maintain a SQL Server 2008 instance that contains a database named Finance. In your absence, a
user with a login named Rob will maintain the database snapshots. You need to grant the appropriate
permissions to the user to delete the database snapshots for the Finance database, Which database
permission should you grant?

A. ALTER ANY DATASPACE

B. DROP DATABASE
C. DELETE
D. CONTROL

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 39
You administer a Microsoft SQL Server 2008 instance that contains two database named Work and
WorkHistory. Work uses the Latin1_General_CS_AS collation. WorkHistory uses the
Latin1_General_100_Cl_Al collation. Each database has an Orders table that has the following definition:
CREATE TABLE[dbo].[Oders]([OrderID][int]IDENTITY(1,1)NOT NULL,[OrderAmt][money]NULL,
[OrderDate][datetime]NULL,[OrderTID][char](10)NULL,[OrderUID][uniqueidentifier]NULL)ON[PRIMARY];
You need to return records from [Link] that exist in [Link] Transact-
SQL query should you use?

A. SELECT [Link],[Link],[Link],[Link] [Link]

whoINNER JOIN [Link] woON [Link]+[Link]=
B. SELECT [Link],[Link],[Link],[Link] [Link]
whoWHERE [Link] COLLATE Latin1_General_CS_AS IN (SELECT OrderTID FROM Work.
[Link])ORDER BY [Link]
C. SELECT [Link],[Link],[Link],[Link] [Link]
whoINNER JOIN [Link] woON [Link] =[Link] COLLATE
Latin1_General_CS_AS
D. SELECT [Link],[Link],[Link],[Link] [Link]
whoINNER JOIN [Link] woON [Link] =[Link] COLLATE
 Latin1_General_CS_AS
E. SELECT [Link],[Link],[Link],[Link] [Link]
whoWHERE [Link] IN (SELECT OrderID COLLATE Latin1_General_CS_AS FROM [Link].
Irders)ORDER BY [Link]
F. SELECT [Link],[Link],[Link],[Link] [Link]
whoCROSS Apply(SELECT OrderID FROM [Link])wo(OrdelID)WHERE [Link] =who.
OrderID COLLATE Latin1_General_CS_ASORDER BY [Link]
G. SELECT [Link],[Link],[Link],[Link] [Link]
whoCROSS Apply (SELECT OrderTID FROM [Link])wo(OrderTID)WHERE wo.
OrderTID=[Link] BY [Link]
H. SELECT [Link],[Link],[Link],[Link] [Link]
whoINNER JOIN [Link] woON [Link] = [Link] COLLATE
Latin1_General_100_CI_AI

Correct Answer: BH
Section: (none)
Explanation

Explanation/Reference:

QUESTION 40
You maintain multiple SQL Server 2008 instances. You develop a failure recovery strategy. You need to
find out the edition and the bulid of SQL Server that is deployed in your environment. What should you use
to find the required information?

A. SELECT@@VERSION
B. SELECT*FROM sys.dm_os_sys_info
C. DBCC HELP
D. EXEC xp_msver

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 41
You manage a SQL Server 2008 instance. You need to verify whether a database integrity check (DBCC
CHECKDB) was run for a particular database. Which log file should you examine?

A. [Link]
B. SQLAGENT
C. ERRORLOG
D. [Link]

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 42
You administer a Microsoft SQL Server 2008 R2 database that hosts an order-processing application. You
need to ensure that the database allows full-text searches on the Customers table. You also need to
ensure that the full-text index is ready for use by the users. You execute a Transact-SQL statement to
create the full-text index on the Customers table by using the CHANGE_TRACKING OFF and NO
POPULATION clauses in the CREATE FULL TEXT INDEX statement. You execute an ALTER FULLTEXT
INDEX. Which command or commands should you use next?

A. DROP FULLTEXT INDEX

B. ALTER INDEX Customers REORGANIE
C. ALTER FULLTEXT INDEX on Customers ENABLE
D. ALTER FULLTEXT INDEX on Customers START FULL POPULATION
E. ALTER FULLTEXT INDEX on Customers START INCREMENTAL POPULATION
F. ALTER FULLTEXT INDEX on Customers SET STOPLIST SYSTEM
G. SELECT*FROM sys.dm_fts_active_catalogs
H. SELECT*FROM FREETEXTTABKE(Customers.*.'Enable')

Correct Answer: DE
Section: (none)
Explanation

Explanation/Reference:

QUESTION 43
You maintain multiple SQL Server 2008 instance that contains a database named Finance. Minimally
logged operations are performed on the Finance database. You need to verify that the database can be
restored to a specific point in time. What should you do?

A. Execute DBCC CHECKDBalong with the REPAIR_REBUILDclause

B. Use the RESTORE DATABASEstatement along with the PAGESclause. Apply any differential and log
backups. Create a new log backup and then restore the new log backup.
C. Execute sp_helpfile
D. Directory management duplication separate design separate
E. Examine the msdb..suspect_pagestable.
F. Restore the database from the most recent full backup. Apply any differential and log backups.
G. Use the RESTORE DATABASEstatement along with the PAGESclause. Create a new log backup.
Apply all differential and log backups,including the most recent backup.
H. Execute DBCC CHECKDBalong with the REPAIR__FASTclause.
I. Use the ALTER DATABASEstatement along with the SET EMERGENCYclause.

Correct Answer: E
Section: (none)
Explanation

Explanation/Reference:

QUESTION 44
You administer a SQL Server 2008 instance that contains a database named Adventurework. You are log
shipping the Adventureworks database to a remote SQL Server 2008 instance. During the weekend, the
primary SQL Server instance required a restart. After the weekend, you discover that log shipping has
stopped working. You need to troubleshoot log shipping of the Adventureworks database. What should
you do?

A. Verify whether the SQL Server Volume Shadow Copy Service (VSS) Writer is started on the primary
server
B. Verify whether the SQL Server Agent is started on the primary server
C. Execute a DBCC CHECKDB statement on the AdventureWorks database by using the
EXTENDED_LOGICAL_CHECKS option
D. Verify whether the AdventureWorks database uses the Simple recovery model

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 45
You maintain a SQL Server 2008 instance that runs on a computer that hosts several applications. You
configure the SQL Server Agent service to run by using the SERVER1\AGENT account, You create a job
named MailingList that requires a file to be written to a file server, The job fails to run because it does not
have appropriate access to the file server. You plan to configure the SQL Server Agent service. You need
to perform the configuration such that only the SQL Server Agent service has read and write access to the
server, Which account type should you use?

A. Domain account
B. Network Service account
C. Local Service account
D. Local System account

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 46
You maintain a SQL Server 2008 instance that contains a database named Finance. Minimally logged
operations are performed on the Finance database. You need to verify that the database can be restored
to a specific point in time. What should you do?

A. Verify that the database uses the checksum page verify option;
B. Verify that the database uses the full recovery model;
C. Verify that the database uses the simple recovery model
D. Verify that the database uses the bulk-logged recovery model.

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 47
You administer a SQL Server 2008 instance. A database developer named User1 needs to view the
definitions of all database objects in a database and read data from all user-defined tables, views, and
table-valued functions. You need to set the required permissions for User1. Which Transact-SQL
statements should you execute?

A. CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addro|emember

'Developers', 'User1'
B. CREATE ROLE Developers;EXEC sp_addrolemember 'sp_dbdatareader','Developers';EXEC
sp_addrolemember 'sp_dbddladmin','Developers';EXEC sp_addrolemember 'Developers','User1'
C. CREATE ROLE Developers;GRANT EXECUTE TO Developers;EXEC sp_addrolemember
'Cevelopers','User1'
D. CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO
Developers;EXEC sp_addrolemember 'Developers','User1'

Correct Answer: D
Section: (none)
Explanation
Explanation/Reference:

QUESTION 48
You administer a SQL Server 2008 instance. You have a stored procedure that implements a database
maintenance process. You need to create a SQL Server Agent job that runs the stored procedure. You
also need to ensure that the job is removed after successful completion. What should you do?

A. Create a job that is assigned to the Database Maintenance category.

B. Create a job that is scheduled to run once
C. Create an Alert that will be raised when the job completes. This Alert will run another job to delete the
maintenance job
D. Create a job that uses the Automatically delete job option

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 49
You administer a Microsoft SQL Server 2008 R2 database. You configure the disk drives according to the
following table:You discover that the Windows application log is being filled with entries from a SQL Server
Audit process named [Link] volume of these events is causing older events to be removed
from the log. You need to ensure that the following requirements are met: SQL Server Audit information is
stored in the D:\AuditLogs folder. No data is currently lost in the Windows application log. What should you
do?

A. Open the Windows Server Manager. Browse to the Diagnostics\Event Viewer\Windows Logs folder.
Right-click Application Log,and then click Properties. Enable the clear Log option.
B. Open the Windows Server Manager. Browse to the Diagnostics\Event Viewer\Windows Logs folder.
Right-click Application Log,and then sekect filter Current [Link] all Event Sources except
MSSQLSERVER.
C. Open the Windows Server Manager. Browse to the Diagnostics\Event Viewer\Windows Logs folder.
Right-click Application Log,and then click Prioerties. Enable the Do not overwrite events(clear logs
manually)option.
D. Create a folder named AuditLogs on drive D. Open the Windows Server Manager. Browse to the
Diagnostics\Event Viewer\Windows Logs [Link]-click Application Log,and then click Properties.
Change the Log path to D:\AuditLogs\[Link].
E. Create a folder named AuditLogs on drive D. Open Microsoft SQL Server Management Studio and
connect to the server. Browse to the Security\Audits folder and double-click the DatabaseAudit object.
Change the Audit Destination to File and specify D:\AuditLogs as the file path.
F. Open Microsoft SQL Server Management Studio and connect to the server. Browse to the Security
\Audits folder. Right-click the DatabaseAudit object,and then select Disable Audit. Continue to
troubleshoot the issue. When the issue is resolved,browse to the Security\Audits folder. Right-click the
DatabaseAudit object,and then select Enable Audit.
G. Create a floder named AuditLogs on drive D. Run the following Transact-SQL statement:ALTER
SERVER AUDIT DatabaseAuditTO FILE(FILEPATH='D:\auditLogs')
H. Create a lolder named AuditLogs on drive D. Run the following Transact-SQL [Link]
DATABASE DatabaseAuditADD LOG FILE(NAME = DatabaseAuditLog,FILENAME ='D:\AuditLogs
\[Link]')

Correct Answer: EG
Section: (none)
Explanation

Explanation/Reference:
QUESTION 50
You maintain multiple SQL Server 2008 instance that has TCP/IP enabled You need to verify the port that
the SQL Server instance listens on. What should you do?

A. Open SQL Sever Configuration Manager Expand the SQL Native Client 10.0 Configuration service.
Select the Client Protocols service. Select the Properties option.
B. Open SQL Sever Configuration Manager Expand the SQL Server Network Configuration serice. Select
the Protocols for(InstanceName)service. In the right panel,right-click TCP/IP,select the Properties
option, and then select the IP Addresses tab.
C. Open SQL Sever Configuration Manager Select the SQL Server Services service. In the right panel,
right-click SQL Server(InstanceNAME),right-click Properties,and then select the Advanced tab.
D. Open SQL Sever Configuration Manager Select the SQL Server Services service. In the right panel,
right-click Properties,and then select the Advanced tab.

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 51
You upgrade an instance of Microsoft SQL Server 2000 to SQL Server 2008. The SQL Server Agent
service is configured to use the LocalSystem account. A job uses a CMDExec step to access a file located
on a remote network share. After the upgrade, the job step fails to complete execution. You need to
configure the job step so that it can successfully complete execution, What should you do?

A. Configure the job step to use a proxy account

B. Configure a certificate
C. Conflgure the SQL Server Agent service to use a local Windows account
D. Configure the SQL Server Agent service to use the NetworkServlce account

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 52
You administer a SQL Server 2008 instance that contains a database named AdventureWorks. You plan to
use data comperssion to conserve disk space. You comperss a table named VeryLarge that has a clusterd
index named index1 and a non-clustered index named index2. Both indexes are partitioned. You execute
the following Transact-SQL statement. ALTER INDEX index2 ON VeryLarge REBUILD WITH
(DATA_COMPRESSION = ROW);You need to ensure that all the VeryLarge table are compressed by
using row-level compression. You need to accomplish this goal by using minimum resources. Which
Transact-SQL statement should you execute?

A. ALTER INDEX index1 ON VeryLarge REBUILD WITH(DATA_COMPRESSION = ROW).

B. ALTER INDEX ALL ON VeryLarge REBUILD PARTITION = ALL WITH(DATA_COMPRESSION =
ROW).
C. ALTER INDEX ALL ON VeryLarge REBUILD WITH (DATA_COMPRESSION = ROW).
D. ALTER INDEX index1 ON VeryLarge REORGANIE WITH (LOB_COMPACTION = ON).

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:
QUESTION 53
You administer several SQL Server 2008 instances. You need to ensure that all SQL Server instances are
consistently configured for the following aspects: Naming conventions, Security settings, Force index
creation, lAvoidance of data fragmentation. What should you do?

A. Use the Database Engine Tuning Advisor

B. Use the SQL Server Configuration Managen
C. Create a maintenance plan in Microsoft SQL Server Management Studio
D. Create a policy in Microsoft SQL Server Management Studio

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 54
You administer a SQL Server 2008 instance that contains a database named Finance. You suspect that
three large tables in the Finance database are corrupt. You plan to execute the DBCC CHECKTABLE
statement concurrently on the three tables to minimize downtime. You need to set the database option
prevent users from accessing the database during the execution of the statement. Which database option
should you turn on?

A. OFFINE
B. READ_ONLY
C. RESTRICTED_USER
D. SINGLE_USER

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 55
You administer a SQL Server 2008 instance that contains a database named DB1. A table named Sales.
Table1 exists in the Sales schema. You need to move the Sales.Table1 table to a new schema named
Billing. Which Transact-SQL statement should you execute?

A. ALTER TABLE Sales.Table1 SWITCH TO Billing.Table1

B. ALTER AUTHORIZATION ON Sales.Table1 TO Billing
C. ALTER USER Sales WITH DEFAULT_SCHEMA = Billing
D. ALTER SCHEMA Billing TRANSFER Sales.table1

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 56
You maintain a SQL Server 2008 instance that contains a database named CustomerDB. The
CustomerDB database stores customer data for the company. The customers use a Web application to
access their profile data. You need to protect the customer data such that data files, log files, and
subsequent backups are as secure as possible even if the backup media is lost. You want to achieve this
goal without affecting the Web application, What should you do?

A. Encrypt the sensitive data at the cell level by using the encryption functions,
B. Enable Transparent Database Encryption for both the CustomerDB database and the master database
C. Make the CustomerDB database accessible only through stored procedures and functions
D. Enable Transparent Database Encryption for the CustomerDB database and back up the transaction
log

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:
Similar to Q28

QUESTION 57
You administer a SQL Server 2008 instance that contains a database named DB1.DB1 contains a table
named [Link] need to obtain the effective permissions of a user named User1 to access the
Sales.Table1 table. Which Transact-SQL statement(s)should you execute in the DB1 database?

A. EXECUTE AS USER ='User1';SELECT*FROM sys.fn_my_permissions('Sales.Table1','OBJECT')

B. SELECT*FROM [Link] uid = USER_ID('User1')AND id = OBJECT_ID('Sales.Table1')
C. EXEC sp_helprotect 'Sales.Table1','User1'
D. SELECT*FROM sys.database_permissionsWHERE class_desc ='OBJECT_OR_COLUMN'AND
major_id = OBJECT_ID('Sales.Table1')AND grantee_principal_id = USER_ID('User1")

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 58
You administer a database for an online ordering system. You plan to create a disaster recovery plan that
uses transaction log backups by using onlt Microsoft SQL Server native tools. You need to ensure that the
transaction log backup schedule meets the following requirements. Log backups occur every 15 minutes
between 09:00 hours and 17:00 hours. Log backups occur every hour between 17:00 hours and 09:00
hours .What should you do?

A. Insert a new row to dm_os_schedulers.

B. Create a single SQL Agent job by using two schedules.
C. Create two SQL Agent jobs by using a single schedule.
D. Create a single SQL Agent job. Use the [Link] tool to execute the job.
E. Create a database maintenance plan by using the Database Maintenance Wizard.
F. Create a single SQL Server Integration Services (SSIS)packages by usingtwo schedules.
G. Create two SQL Server Integration Services (SSIS)packages by using a single schedule.
H. Execute a stored procedure from a Windows scheduled task by using multiple schedules.

Correct Answer: BE
Section: (none)
Explanation

Explanation/Reference:

QUESTION 59
You administer a SQL Server 2008 instance named CorpPub that contains a database named
SalesSupport. The SalesSupport database contains the Products table. You plan to create a Replication
topology to replicate the Products table to the SOL Server instances installed in portable computers.
When the portable computers reconnect to the corporate network, the Products table is updated from
CorpPub. The Products table on the CorpPub instance is frequently updated between reconnections. You
need to successfully implement the Replication topology along with the Subscription type by ensuring that
bandwidth usage is minimized. What should you do?

A. Implement the Transactional Replication topology along with a Pull Subscription

B. Implement the Snapshot Replication topology along with a Pull Subscription
C. Implement the Snapshot Replication topology along with a Push Subscription
D. Implement the Merge Replication topology along with a Pull Subscription

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 60
You administer a Microsoft SQL Server 2008 R2 database instance. The instance has a Products table
that is currently in the DevUser schema. The Appusers database role is assigned Read and Update
permissions to the table. The DevUser schema is owned by a database login that has been deleted. You
need to move the Products table From the DevUser schema to the dbo schema. Which two actions should
you perform in sequence?(To answer. move the appropriate actions from the list of actions to the answer
area and arrange them in the correct order.)

A. Drop the DevUser schema

B. Drop and re-create the Products table.
C. Run the following Transact-SQL command:ALTER SCHEMA dbo TRANSFER [Link].
D. Run the following Transact-SQL command:GRANT SELECT,UPDATE ON [Link] to Appusers;
E. Run the following Transact-SQL command:REVOKE SELECT,UPDATE ON DevUser. Products FROM
Appusers.
F. Run the following Transact-SQL command:ALTER SCHEMA DevUser TRANSFER [Link];

Correct Answer: CD
Section: (none)
Explanation

Explanation/Reference:

QUESTION 61
You administer three SQL Server 2008 instances named lnstance1, lnstance2, and Instance3. Each ofthe
three instances runs on a separate server. A mission-critical database is mirrored between lnstance1 and
lnstance2. lnstance3 is configured as the witness. lnstance1 ls configured as the Principal. You plan to
apply an update to the servers that run lnstance1 and lnstance2, The update requires a restart. You need
to ensure that the servers meet the following requirements during the update process. Complete the
update process in the least amount of time possible. The database must be available on one server during
the update of another server. The update process must not cause an automatic failover of the database.
What should you do?

A. Suspend the mirroring session. Install the update on both servers

B. Suspend the mirroring session. Install the update on the server that runs Instance2. Resume the
mirroring session. Perform a manual failover of the mirroring session. Install the update on the server
that runs Instance1
C. Remove the mirroring session. Run the SQL Server 2008 Upgrade Advisor. Install the update on both
servers. Re-establish the mirroring session
D. Install the update on the server that runs Instance2. Perform a manual failover of the mirroring session.
lnstall the update on the server that runs Instance1
E. Remove the mirroring session. lInstall the update on both servers. Re-establish the mirroring session

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 62
You administer a SQL Server 2008 instance that contains a database named insightDB. The InsightDB
database is used by an application that is continuously connected. The appliation uses the INSERT
command extensively. The application uses triggers to populate multiple tables. You need to ensure that
the reports generated by the application using the InsightDB database return current information with
minimal negative effects on the application [Link] should you do?

A. Set the isolation level to Read Committed Snapshot for the InsightDB database,
B. Set the isoiation level to Read Uncommitted for the InsightDB database.
C. Use the database replication feature and configure the application to use the replicated database.
D. Use the database snapshot feature and configure and configure the application to use the snapshot
database.

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 63
You manage several SQL Server 2008 instances. You plan to collect performance data periodically on all
instances by using the data collector. All collected data must be stored in the same database hosted on a
single instance. You need to collect and load performance data rn the management data warehouse every
6 hours. Which data collection process should you implement?

A. Create two different SQL Agent jobs that are scheduled at the same tlme. One job creates a data
collection and the other job uploads the data collection
B. Create a scheduled non-cached data collection
C. Create an On-demand non-cached data collection
D. Use a certificate to protect the data encryption key. Export the certificate to a file

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 64
You administer a SQL Server 2008 instance. A payroll application is used to store sensitive data in a
database named PayrollDB. You plan to implement the following security measurel. All the data files, log
filesv and backup files of the database are automatically encrypted. You need to devise a method to
encrypt data. What should you include ln your solution?

A. Use a symmetric key provisioned through Extensible Key Management (EKM)

B. Use Transparent Data Encryption (TDE) for the PayrolIDB database
C. Use cell-level encryption to protect sensitive data
D. Use a certificate to protect the data encryption key. Export the certificate to a file.

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 65
You administer a remote SQL Server 2008 instance that contains a database named lnsightDB. The
lnsightDB database is used by an application that is continuously connected. The application uses the
INSERT command extensively and triggers the population of multiple tables. Users of the application
report that the application performance is poor. You suspect that the performance issues are related to
blocking. You need to monitor the state of the instance at regular intervals without affecting the application
performance further. Which tool should you use?

A. Dynamic Management Views

B. SQL Server Profller
C. Windows System Monitor
D. SQL Server Resource Governor

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 66
You administer a SQL Server 2008 instance for a company named Contoso Ltd. The instance contains a
database named DB1. A Windows group named CONTOSO\Managers can access the DB1 database.
CONTOSO\Managers is a member of the db_owner role inthe DB1 database. A Wlndows user named
User1 is a member of the CONTOSO\Managers group. You need to ensure that User1 is unable to
access the SQL Server instance. Which Transact-SQL statement(s) should you execute in the DB1
database?

A. EXEC dbo.sp_revokedbaccess 'CONTOSO\User1';EXEC dbo.sp_revokelogin 'CONTOSO\User1'

B. EXEC dbo.sp_droprolemember 'db_owner','CONTOSO\User1'
C. CREATE LOGIN "CONTOSO\User1"FROM Windows;DENY CONNECT SQL TO "CONTOSO\User1"
D. DROP LOGIN "CONTOSO\User1"

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 67
Note:This question is part of a series of questions that use the same or similar answer [Link] answer
choice may be correct for more than one question in the series Each question is independent of the other
questions in this series. Information and details provided in a question apply only to that question. You
administer a Microsoft SQL Server 2008 R2 data base that contains an Orderltems [Link] table has the
following definition:CREATE TABLE 【OrderItems】(OrderID INT NOT NULL,OrderDate DATETIME NOT
NULL,OrderLine INT NOT NULL,Quantity INT NOT NULL ,PriceEach MONEY NOT NULL);The following
DDL has been run on the database:CREATE PARTITION FUNCTION FUNC_FG(INT)AS RANGE LEFT
FOR VALUES (1,100,1000);You need to create a partition scheme that will place all data to the
SECONDARY [Link] should you do?

A. Create a new [Link] the ALTER TABLE statement along with the SWITCH PARTITION [Link]
the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause
B. Use the ALTER TABLEstatement to remove the COLLATEopption.
C. Create a new [Link] a new database file. Use the ALTER PARTITION SCHEME statement
along with the NEXT USED [Link] ALER INDEX REORGANIZE statement
D. Run the following statement:EXECUTE sp_tableoption@TableNamePattern='OrderItems',
@OptionName='PartitionByYear',@OptionVaule='true';
E. Create a new [Link] a new database File . Use the ALTER PARTITION SCHEME statement
along with the NEXT USED clause. Use the ALTER PARTITION FUNCTION statement along with the
SPLIT RANGE clause.
F. Execute the DBCC CLEANTABLEcommand on the Orderltems table.
G. Remove the clustered index from the table
H. Run the following [Link] PARTITION SCHEME SEC_FGAS PARTITION FUNC_FGALL TO
(SECONDARY)
I. Create a new partion. Create a new partiton scheme Add a clustered index to place the data onto the
partition scheme
J. Use the ALTER PARTITION FUNCTION... SPLIT RANGEstatement.

Correct Answer: H
Section: (none)
Explanation

Explanation/Reference:

QUESTION 68
You administer a SQL Server 2008 instance that contains a database named DB1. The DB1 database
contains the following stored procedure. (Line numbers are included for reference only) 01 CREATE
PROCEDURE SaIes. Procedure102 AS03 IF OBJECT_ID('SaIes.TabIe1') IS NOT NULL04 DROP TABLE
SaIes. Table1;05 06 CREATE TABLE Sales.Table1 (07 ld int PRIMARY KEY CLUSTERED,O8 Name
varchar(100); 09 ),10 11...12 GO When a user named User1 attempts to invoke Procedure1, the following
exception is raised: "Msg 262, Level 14, State 1, Procedure Procedure1, Line 5CREATE TABLE
permission denied in database'DB1'. You need to provide User1 access to execute Procedure1 by
allocating only the required permissions. What should you do?

A. Insert the WITH EXECUTE AS 'dbo' clause between lines 01 and 02

B. Grant the CREATE TABLE permission and allow User1 to drop the Sales.Table1 table
C. Insert the EXECUTE AS USER statement between lines 02 and 03
D. Grant the ALTER permission on the Sales schema to User1

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 69
You administer a Microsoft SQL Server 2008 R2 instance. You need to attach a database named
Northwind to the server. Which Transact-SQL command should you use?

A. EXECUTE sp_oledb_database
B. EXECUTE sp_publishdb@dbname='Northwind',@value='attach'
C. Complexity relating to the separation of users and and resources in
D. EXECUTE sp_attach_single_file_db@dbname='Northwind',@Physname='E:\MSSQL\LOG
\[Link]'
E. REstore DATABASE NorthwindFROM DISK ='D:\MSSQL\DATA\[Link]'
F. RESTORE DATABASE NorthwindFROM DISK ='D:\MSSQL\DATA\[Link]','E;\MSSQL
\LOG\Northwind2008R2_log.ldf'
G. CREATE DATABASE[Northwind]ON9FILENAME = N'D:\MSSQL\DATA\Northwind2008R2mdf'),
(FILENAME = N'E:\MSSQL\LOG\Northwind2008R2_log.ldf')FOR ATTACHGO
H. BULK INSERT Northwind FROM N'D\MSSQL\DATA\[Link]'

Correct Answer: DG
Section: (none)
Explanation

Explanation/Reference:

QUESTION 70
You manage a SQL Server 2008 instance. You plan to maintain a management data warehouse that
collects performance data by using the data collector. You need to implement a process that routinely
gathers and uploads data in the management data warehouse on different schedules. What data
collection process should you implement?

A. Create a scheduled non-cached data collection

B. Create an on-demand non-cached data collection
C. Create two different SQL Agent jobs that are scheduled at the same time. One job creates a data
collection and the other job uploads the data collection
D. Create a cached data collection

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 71
You administer three SQL Server 2008 instances on sepatate servers named Server1,Server2,and
Server3. The AdventureWorks database is configured for mirroring between the instances. In the mirroring
session,Server1 and Servers2 act as partners and Server3 as the witness. you have the following
requirements:ALL three servers run a maintenance process that requires occasional restart of the
computer. The Sql Server service on each servers is shut down only during the actual restart of the
computer. After completion of the maintenance process, re-enable automatic failover and synchronize the
mirroring session in minmun possible time. You need to ensure that automatic failover is disabled during
the maintenance process. What should you do first?

A. Remove the witness server from the mirroring session

B. Configure the mirroring session with the SAFETY option set to ON
C. Suspend the mirroring session
D. Remove mirroring from the database

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 72
You maintain a SQL Server 2008 instance. You plan to deploy an instance of SQL Server Reporting
Services (SSRS) to the same machine. You need to ensure that by default, SSRS maintains version
histories of all deployed reports. What should you do?

A. Configure the SSRS database to use Native mode

B. Configure the SSRS database to use SharePoint integrated model
C. Install the SSRS instance by using the lntemet Information Services default Web site
D. Install the SSRS instance by using the [Link] listener
Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 73
You administer a SQL Server 2008 instance. The instance contains a database named DB1. You plan to
allow all the application developers to use SQL Server Profiler to capture traces to troubleshoot the
application that uses the database DB1. You need to grant the minimum necessary permission to the
application developers. What should you do?

A. Add all the database users of the application developers to a fixed database role
B. Add all the SQL Server logins of the application developers to a flxed server role
C. Grant the appropriate server-leve| permissions to all SQL Server logins of the application developers
D. Grant the appropriate database-level permissions to all database users of the application developers

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 74
You administer a Microsoft SQL Server2008 R2 database instance. You plan to automate maintenance
tasks. What task types are included in a SQL Maintenance Plan? (Choose all that apply.)

A. Copy databases between servers

B. Update database statistics
C. Rebuild indexes
D. Defrag hard disks
E. Back up databases

Correct Answer: BCE

Section: (none)
Explanation

Explanation/Reference:

QUESTION 75
You administer a Microsoft SQL Server 2008 R2 database instance. The instance has a Products table
that is currently in the DevUser schema. The Appusers database role is assigned Read and Update
permissions to the table. The DevUser schema is owned by a database login that has been renamed, You
need to move the Products table from the DevUser schema to the dbo schema. Which two actions should
you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer
area and arrange them in the correct order.)

A. Drop the DevUser schema.

B. Drop and re-create the Rroducts table.
C. Run the following Transact-SQLcommand: ALTER SCHEMA dbo TRANSFER [Link];
D. Run the following Transact-SQLcommand: GRANT SELECT,UPDATE ON [Link] to Appusers;
E. Run the following Transact-SQLcommand: REVOKE SELECT, UPDATE ON [Link] FROM
Appusers;
F. Run the following Transact-SQLcommand: ALTER SCHEMA DevUser TRAMSFER [Link];

Correct Answer: CD
Section: (none)
Explanation

Explanation/Reference:

QUESTION 76
You administer a SQL Server 2008 instance, You use an internal application based on SQL Sewer 2008,
The application uses Analysis Services and Reporting Services Company security policy requires that the
surface area for all the deployed components of SQL Server 2008 be configured. You need to implement
the security policy and devise a method to evaluate the security policy against other database servers.
What should you do?

A. Use the SQL Server Best Practices Analyzer (BPA) to analyze your database servers. Implement the
recommendations of the BPA
B. Edit the [Link] configuration file. Distribute the file to all database servers that need to
be configured
C. Create a Transact-SQL script based on the sp_configure stored procedure. Use a configuration server
to run the script against a server group that includes the database servers that need to be configured
D. Create policies based on the appropriate facets. Apply the policies against a server group that includes
the database servers that need to be configured

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 77
You administer a SQL Server 2008 instance that contains a database named DBl, DB1 contains a table
named Table1. The DB1 database includes a stored procedure named Procedure1. Procedure1 uses a
sp_executesql Transact-`SQL statement to select data from Tablel1. According to business requirements,
users are not allowed to access tables directly in any database. When a user executes Procedure1, the
following exception is raised: "Msg 229, Level 14, State 5, Line 1The SELECT permission was denied on
the object 'Table1‘, database 'DB1', schema 'dbo'. You need to ensure that the user can successfully
execute Procedure1 without violating the business requirements. What should you do?

A. Alter Procedure1 and add the EXECUTE AS USER = 'dbo' option immedlately before the call to the
sp_executesql stored procedure
B. Execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement
C. Execute the GRANT SELECT ON dbo.Table1 TO User1 Transact-SQL statement
D. Alter Procedure1 and add the WITH EXECUTE AS OWNER option to its header

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 78
You maintain multiple SQL Server 2008 instances on the same physical computer, Users can connect to
the default instance but are unable to connect to the named instances. You verify that currently all SQL
Server instances run. You need to start the service required to connect to the named instances. Which
Service should you start

A. Server
B. SQL Server Agent
C. SQL Active Directory Helper
D. SQL Server Browser

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 79
You administer a SQL Server 2008 instance. The instance contains an On-Line Analytical Processing
(OLAP) database along with a dimension table named Customers, The data of the Customers table is
updated every thirty minutes. The Customers table contains redundant data. You need to conserve the
disk space used to store the Customers table. What should you do?

A. Defragment the hard disk that contains the instance

B. Implement page compression
C. Compress the hard disk that contains the instance
D. Implement row compression

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 80
You administer a SQL Server 2008 instance that runs on a Windows Server 2003 computer. The instance
uses mixed authentication mode. You need to ensure that the SQL Server 2008 authenticated logins
follow the same password complexity rules that are enforced by Windows Sewer 2003. You also need to
ensure that the password complexity rules continue to be enforced. Which two actions should you
perform? (Each correct answer presents part of the solution. Choose two)

A. Use the ALTER LOGIN...HASHED statement to modify all logins

B. Use the ALTER LOGIN...CHECK_POLICY = ON statement to modlfy all logins
C. Use the ALTER LOGIN...CHECK_EXPIRATION = ON statement to modify all logins
D. Use Policy-Based Management to create a policy that prevents any violation of these rules
E. Create an SQL Server Agent job that runs periodically to raise an alert if a rule violation is detected

Correct Answer: BD
Section: (none)
Explanation

Explanation/Reference:

QUESTION 81
You maintain a SQL Server 2008 instance that contains a database named Finance. You need to
configure SQL Server to automatically send an e-mail message when any file in the Finance database
increases. Which technology should you include in your solution?

A. Query notifications
B. SQL Server Profiler
C. Trace flag
D. Event notifications

Correct Answer: D
Section: (none)
Explanation
Explanation/Reference:

QUESTION 82
[Link] administer a Microsoft SQL Server 2008 R2 instance that contains two databases named
Northwind2008R2 database uses the SQL_Latin1_General_CP1_CI_AS collation and the
AdventureWorks database uses the SQL_Latin_General_CP1_CS_AS collation. You need to write a query
that returns all customers having the same name in each database. You also need to ensure that the query
is not case-sensitive. Which Transact-SQL query should you use?

A. SELECT*[Link] adv COLLATE AQL_Latin1_General_CP1_CI_AS

INNER [Link] nrt ON [Link] = [Link]
B. SELECT*[Link] adv INNER [Link] nrt
ON [Link] = [Link] COLLATE AQL_Latin1_General_CP1_CI_AS;
C. SELECT*[Link] adv INNER [Link] nrt
ON [Link] = [Link] COLLATE AQL_Latin1_General_CP1_CS_AS;
D. SELECT*[Link] adv INNER [Link] nrt
ON UPPER([Link]) = UPPER([Link])
E. SELECT*[Link] adv INNER [Link] nrt
ON [Link] COLLATE SQL _Latin1_General_CP1_CI_AS = [Link]
COLLATE SQL_Latin1_General_CP1_CS_AS;
F. SELECT*[Link] adv INNER [Link] nrt
ON [Link] COLLATE SQL_Latin1_General_CP1_CI_AS = [Link]
G. SET COLLATE SQL_Latin1_General_CP1_CI_AS;SELECT*[Link] adv
INNER [Link] nrt ON [Link]=[Link];
H. SELECT*[Link] adv INNER [Link] nrt
ON [Link]=[Link] USING COLLATION SQL_Latin1_General_CP1_CI_AS;

Correct Answer: BF
Section: (none)
Explanation

Explanation/Reference:

QUESTION 83
You have a server that contains a default SQL Server 2005 instance. You need to install a SQL Server
2008 instance for a new application on the same server. The new application requires SQL Server 2008
functionality. You need to ensure that both database instances are available for their respective certified
third-party applications. The existing application environments remain unchanged, What should you do?

A. Upgrade the SQL Server 2005 instance to a SQL Server 2008 instance
B. Install SQL Server 2008 as a named instance
C. Install SQL Server 2008 as the default instance
D. Upgrade the SQL Server 2005 application to use SQL Server 2008

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 84
You administer a SQL Server 2008 instance. The security policy permits members of a Windows group
named CONTOSO\Sales to establish new connections to the SQL Server instance only during business
hours between 07:00 hours and 19:00 hours. Other users may connect to the SQL Server instance any
time, You write the following Transact-SQL statements. CREATE TABLE [Link]
(ld int NOT NULL IDENTlTY(1,1),GroupName sysname NOT NULL,RestrictedTimeStart time NOT NULL,
RestrictedTimeStop time NOT NULL,CONSTRAINT RestrictedLogonHours _pk PRIMARY KEY
CLUSTERED(|d)); INSERT INTO [Link] (GroupName,RestrictedTimeStart,
RestrictedTimeStop)VALUES('CONTOSO\Sa|es‘,CAST('07:00' AS time),CAST('19:00' AS time); You need
to implement the company security policy that is stored in the RestrlctedLogonHours table. What should
you do?

A. Create a SQL Server Agent job that causes the SQL Server Windows service to pause during non-
business hours
B. Create a po|icy that uses a condition based on the Server Audit facet. Use the policy to deny the
connection to the CONTOSO\Sales group during non-business hours.
C. Create a logon trigger that denies the connection to the CONTOSO\Sales group during non-business
hours
D. Create a SQL Server Agent job that periodically looks for and kills connections made by the CONTOSO
\Sales group durlng non-business hours

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 85
You administer a remote SQL Server 2008 instance. Users report that the performance of the application
is poor. You use SQL Profiler to capture a workload of the remote instance to a trace table on the remote
SQL Server instance. You need to analyze the workload of the remote SQL Server instance on a local
SQL Server instance by using the Database Engine Tuning Advisor, What should you do?

A. Use SQL Profiler to recapture the workload to a trace file

B. Enable the XP_MSVER stored procedure on the remote server
C. Enable the XP_MSVER stored procedure on the local server
D. Use the data collector to recapture the workload

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 86
[Link]:This question is part of a series of quesions that use the same or similar answer [Link]
answer chioce may be correct for more than one question in the [Link] question is independrnt of the
other questions in this series. lnformation and deails provided in a question apply only to that [Link]
administer a Microsoft SQL Server 2008 R2 instance. A disk drive that contains the files for the database is
lost. The drive has been replaced, but the data files have been irrevocably lost. You need to restore the
database. What should you do?

A. Execute DBCC CHECKDBalong with the REPAIR_FASTclause.

B. Execute the msdb_suspeect_pagestadle.
C. Execute sp_helpfile.
D. Restore the database from the most recent full [Link] any differential and log backups.
E. Use the ALTER DABASEstatement alomg with SET EMERGENCYclause.
F. Execute DBCC CHECKDB.
G. Use the RESTORE DABASEstatement alomg with [Link] a new log [Link] a
new log [Link] all differential ahd log [Link] the most recent backup.
H. Use the RESTORE DABASEstatement alomg with [Link] any differential and log
[Link] a new log backup and then restore the new log backup.
I. Execute DBCC CHECKDBalong with the REPAIR_REBUILDclause.
Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 87
[Link]:This question is part of a series of quesions that use the same or similar answer [Link]
answer chioce may be correct for more than one question in the [Link] question is independrnt of the
other questions in this series. lnformation and deails provided in a question apply only to that [Link]
administer a Microsoft SQL Server 2008 R2 instance. A disk drive that contains the files for the database is
lost. The drive has been replaced, but the data files have been irrevocably lost. You need to restore the
database. You need to rectify a damaged index without losing any data. What should you do?

A. Use the RESTORE DABASEstatement alomg with [Link] a new log [Link] all
differential ahd log [Link] the most recent backup.
B. Restore the database from the most recent full [Link] any differential ahd log backups.
C. Use the RESTORE DABASEstatement alomg with [Link] any differential ahd log
[Link] a new log backup and then restore the new log backup.
D. Examine the msdb..suspect_pagestable.
E. Execute DBCC CHECKDB along with the REPAIR_REBUILD clause.
F. Execute sp_helpfile.
G. Use the ALTER DABASEstatement alomg with the SET EMERGENCYclause.
H. Execute DBCC CHECKDBalong with the REPAIR_FASTclause.
I. Execute DBCC CHECKDB.

Correct Answer: E
Section: (none)
Explanation

Explanation/Reference:

QUESTION 88
You maintain a SQL Server 2008 instance. You use SQL Server Database Mail to distribute multiple
reprots that are scheduled and generated every night. You need to
enforce a mail retention policy to delete copies of automatically generated mails after 60 days. Which
process should you use?

A. Set the MailProfile property of a Management Policy on the Server facet to delete mails after 60 days.
B. Set the Subscription retention interval to delete mails after 60 days.
C. Create a SQL Agent job to use the sysmail_delete_mailitems_sp stored procedure to delete mails older
than 60 days.
D. Create a SQL Agent job to use the sysmail_delete_log_sp stored procedure to delete mails older than
60 days.

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 89
You administer a SQL Server 2008 instance. You configure a single Database Mail profile that is used to
send reports to all the employees of your organization. The sp_send_dbmail stored procedure is used to
send a large volume of reports during business hours. The reports to the manager take a long time to be
delivered. You need to ensure that the reports to the manager are sent as quickly as possible. What
should you do?

A. Create a separate Database Mail profile for the reports

B. Modify the parameters of the email that contains the reports
C. Configure the security settings of the Database Mail system
D. Modify the SMTP relay settings

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 90
You manage two SQL Server 2008 instances on separate servers names Server1 and Server2. The
servers are located in different cities. You plan to implement database mirroring between the two
instances. You need to set up the mirroring session to support the high volume of write transactions to the
database. You do not want small errors to impact the principal server. What should you do?

A. Configure the mirroring session with the SAFETY option set to FULL and the WITNESS option set to
OFF.
B. Configure the mirroring session with the SAFETY option set to OFF and the WITNESS option set to
OFF.
C. Configure the mirroring session for high-safety mode with automatic failover.
D. Configure the mirroring session to add a witness.

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 91
You maintain a SQL Server 2008 instance that contains a database named Finance, A small percentage of
data in the database is modified daily. You need to create a read only copy of the database for reporting
purposes at the beginning of each day by ensuring that minimal disk space is used. Which strategy should
you use?

A. Use database snapshots

B. Use database backup and restore along with compression
C. Use database backup and restore
D. Use schema-bound views in a read-only database that resides on the same instance

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 92
You administer a SQL Server 2008 instance.c A user named Mary reports that she is waiting for a query to
complete. You need to ascertain whether the query is blocked. Which tool should you use?

A. The Database Engine Tuning Advisor tool

B. The Job Activity Monitor tool in Microsoft SQL Server Management Studio
C. The Windows System Monitor tool
D. The Activity Monitor tool in Microsoft SQL Server Management Studio

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 93
You administer two SQL Server 2008 instances on separate servers named Server1 and Server2. The
AdventureWorks database is set up for synchronous mirroring
between the two instances. You need to configure the database to support automatic failover. What should
you do ?

A. Configure the mirroring session with the SAFETY option set to OFF.
B. Configure the mirroring session with the SAFETY option set to FULL.
C. Configure the mirroring session by using the FAILOVER option.
D. Configure the mirroring session by adding a witness server to it.

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 94
You manage a SQL Server 2008 instance. You upgrade a SQL Server 7.0 database to the SQL Server
instance. You need to ensure the early detection of suspect pages in the database. What should you do?

A. Set the database compatibility level option to 10 for the database

B. Set the PAGE_VERIFY database option to TORN_PAGE_DETECTION for the model database
C. Turn on the TRUSTWORTHY database option for the database
D. Set the PAGE_VERIFY database option to CHECKSUM for the database

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 95
You administer a SQL Server 2008 instance. A SQL Server Agent job executes every 2 minutes. The job
logs information to a text named [Link] located on the E:drive. Users report that the server is
unresponsive. You discover the following problems: The SQL Server Agent job no longer functions. The
SQL Server Agent service does not run. You need to find out the cause of the SQL Server Agent service
failure. Which log should you examine?

A. [Link]
B. ERRORLOG
C. [Link]
D. log_xx.trc

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:
QUESTION 96
You install a 64-bit version of a SQL Server 2008 instance by using the default setup and configuration
settings. You receive the following error message."You SQL Server installation completed with failuress."
You close the error window before noting the location where additional information about the error is
[Link] need to
locate the additional information . Where should you search?

A. The Event Viewer System log file

B. The Event Viewer Application log file
C. The C:\Program Files\Microsoft SQL Server\MSSQL\ LOG folder
D. The C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Folder

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 97
You administer a Microsoft SQL Server 2008 R2 database that contains a schema named Reporting. The
Reporting schema contains stored proceduress,tables,and views Stored procedures and views in the
Reporting schema access objects that are not contained in the schema. You need to ensure that the
DOMAIN\BusinessReporting Active Directory group meets the followingrequirement. Users are able to
access objects in the Reporting schema through existing stored procedures. Users are unable to modify
tables and views in the database. What should you grant the DOMAIN\BussinessReporting group?

A. Access to the sysadmin server role

B. Access to the serveradmin server role
C. Access to the db_owner role in the database
D. Access to the db_datareader role in the database
E. The EXECUTE permissions to the database
F. The EXECUTE permissions on the Reproting schema
G. The EXECUTE permissions on each individual stored procedure in the Reporting schema
H. The EXECUTE permissions to each schema that is queried by the stored procedures and views in the
Reporting schem

Correct Answer: FG
Section: (none)
Explanation

Explanation/Reference:

QUESTION 98
You administer a SQL Server 2008 instance and a SQL Server 2005 instance. Both instances run on the
same computer. The SQL Server 2008 instance contains a database named WebApplicationDB that uses
the Fulltext indexes. Several records that include the word "root" are added to the WebApplicationDB
database, When the Fulltext index is queried for the word "root," an empty resultset is returned. You need
to ensure that records that contain the word "root" are returned by the query, What should you do?

A. Rebuild the full-text index

B. Add the word "root" to the stop list
C. Stop and restart the MSFTESQL service
D. Add the word "root" to the thesaurus file

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 99
You maintain a defautt SQL Server 2008 instance. You plan to configure FILESTREAM data to meet the
following requirements: Enable FILESTREAM for I/O streaming access. Allow remote client computers to
have streaming access to FILESTREAM data. You need to ensure that FILESTREAM data is enabled.
What should you do?

A. Modify the Background Intelligent Transfer Service settings

B. Configure the SQL Server service
C. Configure the SQL Server Full Text service
D. Modify the Volume Shadow Copy Settings

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 100
Note: This question is part of a series of questions that use the same or similar answer [Link] answer
choice may be correct for more than one question in the series. Each question is independent of the other
question in this [Link] and details provided in question apply only to that [Link]
administer a Microsoft SQL Sever 2008 R2 [Link] instance has a database named
[Link] database is backed up by using the weekly scheduled SQL Server Agent [Link]
backup schedule is shown in the following table: A drive fails and the CustomerOrders database goes into
suspect mode on Saturday at 10:00 [Link] need to restore the [Link] should you do ?

A. Restore the backups taken on Wednesday and Friday.

B. Restore the backups taken on Wednesday, Thursday,and Friday.
C. Restore the backups taken on Wednesday only.
D. Restore the backups taken on Sunday and Monday.
E. Restore the backups taken on Sunday only.
F. Restore the backups taken on Wednesday and Thursday at 09:00 hours,and Thursday at 18:00 hours.
G. Restore the backups taken on Wednesday and Thursday at 09:00 hours.
H. Restore the backups taken Wednesday ,Friday,and Saturday.

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 101
You administer a SQL Server 2008 instance. The server contains a very large database that is used by an
application that is constantly available. Users report that the server performance has degraded. You need
to use the Database Engine Tuning Advisor to improve the performance of the application. You also need
to ensure that the performance of the production server is unaffected while analyzing the workload, What
should you do?

A. Enable the XP_MSVER Stored procedure on the remote server

B. Configure a test server that has a similar hardware configuration. Use the [Link] utility on the test
server along with an XML input file
C. Enable the XP_MSVER stored procedure on the local server
D. Use the [Link] utility on the production server along with an XML input file

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 102
You administer a SQL Server 2008 database. Backups are performed according to the following schedule:
Full database backup at 00:00 hours daily. Transaction log backups at 06:00 hours, 12:00 hours,and 18:00
hours daily. You discover that the database failed at 10:18 hours. You also discover that the database is
no longer accessible due to a failure of the hard disk drive that contains the database data [Link] need to
restore the [Link] also need to ensure that data loss is [Link] four actions should you
perform in sequence?(To answer,move the appropriate actions from the list of actions to the answer area
and arrange them in the correct order.)

A. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option.
B. Restore the most recent full database backup by using the WITH NORECOVERY option.
C. Back up the transaction log by using the WITH NO_TRUNCATE option on the BACKUP LOG
statement.
D. Restore the transaction log backup from 06:00 hours by using the WITH RECOVERY option.
E. Restore the last transaction log backup by using the WITH RECOVERY option.
F. Restore the most recent full database backup by using WITH RECOVERY option.

Correct Answer: ABCE

Section: (none)
Explanation

Explanation/Reference:

QUESTION 103
You maintain a SQL Server 2008 instance that contains a database named Finance, The backup strategy
for the Finance database specifies the following requirements: Full database backup to a file named
[Link] is performed at 08:00 hours daily. A transaction log backup lo a file named finance_HHMM.
trm is performed every 15 mlnutes starting at 08:15 hours. The Finance database is being used in sing|e-
user mode. At 08:40 hours, a user reports that some important data was accidentally deleted by a query
that was executed at 08:23 hours. You need to restore the database to its original state. Which Transact-
SQL Statement(s) should you use?

A. RESTORE DATABASE FinanceFROM DISK = 't:\backups\[Link]'WITH NORECOVERY;

RESTORE LOG FinanceFROM't:\backups\finance_0815.trn'WITH NORECOVERY;RESTORE LOG
FinanceFROM ‘t:\backups\finance_0830.trn'WlTH RECOVERY, STOPAT = 'Mar 17, 2008 08:23 AM'
B. RESTORE DATABASE FinanceFROM DISK 't:\backups\financebak'WlTH NORECOVERY,RESTORE
LOG FinanceFROM't:\backups\finance_0830.trn'WITH RECOVERY, STOPAT = 'Mar 17, 2008 08:23
AM'
C. RESTORE DATABASE FinanceFROM DISK = 't:\backups\[Link]';RESTORE LOG
FinanceFROM't:\backups\finance_0815.trn'WITH NORECOVERY;RESTORE LOG FinanceFROM 't:
\backups\finance_0830.trn'WITH RECOVERY;
D. RESTORE DATABASE FinanceFROM DISK = 't:\backups\[Link]'RESTORE LOG FinanceFROM
't:\backups\finance_0815.trn'WITH NORECOVERY;RESTORE LOG FinanceFROM 't:\backups
\finance_0815.trn'WITH RECOVERY,STOPAT = 'Mar 17, 2008 08:23 AM'

Correct Answer: A
Section: (none)
Explanation
Explanation/Reference:

QUESTION 104
You maintain a SQL Server 2008 instance that contains a database named AdventureWorks. The
AdventureWorks database contains a table named OrderSpecs. The OrderSpecs table has a clustered
primary key named Orderld on the Orderld column. The OrderSpecs table has a single XML column
named ProductSpecs that stores XML data, and there is an XML index on the same XML column. You
add a new column named Productld to the OrderSpecs table. You need to include Productld in the primary
key, What should you do? _

A. Alter the XML index and set the ALLOW_ROW_LOCKS = OFF option. Alter the primary key and set
the ALLOW_ROW_LOCKS = ON option
B. Move the XML data to a temporary table. Clear the XML data from the original table by setting the
ProductSpecs column to NULL, Modify the primary key, Repopulate the ProductSpecs column
C. Disable the XML index on the ProductSpecs column. Modify the primary key. Enable the XML index
on the ProductSpecs column
D. Drop the XML index on the table. Modify the primary key, Recreate the XML index

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 105
You maintain a SQL Server 2008 instance that contains a database named Finance, The recovery model
of the Finance database is set to Full, The backup strategy for the Finance database is as shown in the
following table. The system databases, user database data, and log files are stored on separate disks.
The hard disks that contain the user database data files are reported to have failed at 15:00 hours on
Wednesday. You need to restore the Finance database from the database backups by ensuring minimal
data loss in minimum possible time. What should you do?

A. Back up the transaction log, Restore the full backup taken on Sunday, Restore all transaction log
backups
B. Restore the full backup taken on Sunday. Restore the differential backup taken on Wednesday
C. Restore the full backup taken on Sunday. Restore the differenual backup taken on Monday and
Tuesday. Restore the transaction log backups taken at 08:00 hours and at 12:00 hours on Wednesday
D. Back up the transaction log. Restore the full backup taken on Sunday. Restore the differential backup
taken on Tuesday. Restore all transaction log backups taken during Wednesday

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 106
You administer a Microsoft SQL Server 2008 R2 database instance. You need identify queries or stord
procedures that are consuming server resurce. You also need
to vies the query plan that is that is being used by each of these queries or [Link] Dynamic
Management Views(DMVs) should you use to find details about specific queries?(Choose all that apply.)

A. dm_exec_query_plan
B. dm_exec_sql_text
C. dm_io_virtual_file_stats
D. dm_exec_query_stats
E. dm_exec_connections
Correct Answer: ABD
Section: (none)
Explanation

Explanation/Reference:

QUESTION 107
You maintain a SQL Server 2008 instance. You discover that the database named CityPowerLightDB
presents data integrity problems. You perform a full database backup at 22:00 hours, The SQL Server
instance experiences a power failure at 05:59 hours, No data modifications have occurred after the
backup. When the database is online, you discover that some table data pages are corrupt, You need to
completely repair the database in the minimum amount of time. What should you do?

A. Restore the database from the full database backup.

B. Use the DBCC CHECKTABLE command along with the PHYSICAL_ONLY option
C. Restore the corrupt pages from the full database backup
D. Use the DBCC CHECKDB command along with the REPAIR_ALLOW__DATA_LOSS option

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 108
You administer a SQL Server 2008 instance. A stored procedure is used as the data source for a report
that is frequently executed during business hours. Users report that the data returned by the report is
inconsistent, You discover that the inconsistencies are caused by phantom reads. You need to ensure
that the report returns consistent results without affecting other users. What should you do?

A. Configure the database for Read Committed Snapshot isolation

B. Configure the database for Auto Update Statistics asynchronously
C. Modify the stored procedure to use the Snapshot isolation level
D. Modify the stored procedure to use the Repeatable Read isolation level

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 109
Note:This question is part of a series of questions that use the same or similar answer [Link] answer
choice may be correct for more than one question in the [Link] question is independent of the other
questions in this [Link] and details provided in a question apply only to that [Link]
administer a Microsoft SQL Server 2008 R2 database that contains an Orderltems table. The table has the
following definition:CREATE TABLE[Orderltems](OderID INT NOT [Link] DATETIME NOT
NULL,OrderLine INT NOT NULL,ProductID INT NOT NULL,Quantity INT NOT NULL,PriceEach MONEY
NOT NULL.);Currently,the table is partitioned by year with each year in its own [Link] need to add a
new partition for the upcoming [Link] should you do ?

A. Create a new [Link] a new database File. Use the ALTER PARTITION SCHEME statement
along with the NEXT USED [Link] the ALTER PARTITION FUNCTION statement along with the
SPLIT RANGE clause.
B. Create a new partition [Link] a new partition [Link] a clustered index to place the data
onto the partition scheme.
C. Use the ALTER PARTITION FUNCTION...SPLIT RANGE statement.
D. Execute the DBCC CLEANTABLEcommand on the Orderltems table.
E. Run the following statement:CREATE PARTITION SCHEME SEC_FGAS PARTITION FUNC_FGALL
TO (SECONDARY);
F. Create a new [Link] a new database [Link] the ALTER PARTITION SCHEME statement
along with the NEXT USED [Link] ALTER INDEX REORGANIZE statement.
G. Use the ALTER TABLE statement to remove the COLLAE option.
H. Run the following statemnet:EXECUTE sp_tableoption@TableNamePattern = 'OrderltemsName =
'PartitionByYear',@OptionValue="true",
I. Remove the clustered index from the table.
J. Create a new table. Use the ALTER TABLE statement along with SWITCH PARTITION [Link] the
ALTER PARTITION FUNCTION statemnet along with the MERGE RANGE clause.

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 110
You maintain a SQL Server 2008 instance that contains a database named Finance. The most recent full
backup was taken at 13:00 hours, a differential backup was taken at 15:00 hours, and database snapshots
were created at 16:00 hours and at 17:00 hours, The backups and the database snapshots are stored on a
different disk from the database tiles. The hard disk that contains the database files fails at 17:02 hours.
You need to restore the Finance database by ensuring minimal data loss. What should you do?

A. Restore the full backup and the differential backup

B. Restore the database snapshot from 16:00 hours
C. Restore the database snapshot from 17:00 hours
D. Restore the full backup

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 111
You administer a Microsoft SQL Server 2008 R2 instance. You need to configure mirroring between two
servers in high-performance mode. What should you do?

A. Create mirroring endpoints on both partner servers. On the principal server, use the ALTER
DATABASE statement along with the PARTNER SAFETY OFF clause
B. Create mirroring endpoints on both partner servers. Use the ALTER DATABASE statement along with
the PARTNER SAFETY clause on the principal server
C. Create mirroring endpoints on both partner servers. Create a witness server. Use the ALTER
DATABASE statement along with the PARTNER SAFETY OFF clause on the principal server
D. Create mirroring endpoints on both partner [Link] Microsoft SQL Server Management Studio on
the prinicpal server to select the High Performance option, and start mirroring
E. Create mirroring endpoints on both partner servers. .Use Microsoft SQL Server Management Studio on
both partner servers to select the High Performance option, and start mirroring
F. Create mirroring endpoints on both partner servers. Use the ALTER ENDPOINT statement along with
the ENCRYPTION=DISABLED clause
G. Create mirroring endpoints on both partner servers. Execute the following stored procedure:
sp_configure 'Lightweight Pooling', 1
H. Create mirroring endpoints on both partner servers. Use the ALTER DATABASE statement along with
 the PARTNER SAFETY OFF clause on the principal server. Define the Service Broker queue that must
be used to hold requests.

Correct Answer: AD
Section: (none)
Explanation

Explanation/Reference:

QUESTION 112
You maintain a default SQL Server 2008 instance, You plan to configure FILESTREAM data to meet the
following requirements: Enable FILESTREAM for I/O streaming access. Allow remote client computers to
have streaming access to FILESTREAM data, You need to ensure that FILESTREAM data is enabled.
Which service should you configure?

A. SQL Server Full Text

B. SQL Server
C. SQL Server VSS Writer
D. Distributed File System

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 113
You administer a SQL Server 2008 instance .A user named Julie repeated failed connections because of
incorrect password usage for the login name [Link] has been able to successfully login using this
account in the [Link] then recalls the correct [Link],her connection is still [Link]
need to enable the SQL Server [Link] Transact-SQL statement should you use?

A. ALTER LOGIN srpc8521 WITH NO CREDENTIAL;

B. ALTER LOGIN srpc8521 WITH CHECK_ECPIRATION=OFF;
C. ALTER LOGIN srpc8521 UNLOCK;
D. ALTER LOGIN srpc8521 ENABLE;

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 114
You troubleshoot the performance of a SQL Server 2008 instance. You need to identify the longest-
running common language runtime (CLR) queries. Which dynamic management view should you use?

A. sys.dm_exec_query_stats
B. sys.dm_exec_sessions
C. sys.dm_os_wait_stats
D. sys.dm_exec_requests

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:
QUESTION 115
You maintain a SQL Server 2008 instance, You create a new SWL Agent job that includes a Windows
PowerSheII job step. The PowerShell job step uses the SQLCmd utllity to transfer data between servers.
You need to ensure that an operator named ResponseTeam is notified by an e-mail message lf the job
fails. What should you do?

A. Create the ResponseTeam operator. Configure the job step proxy account to use the ResponseTeam
operator account. Select the proxy account for the e-mail profile
B. Enable Notifications. Create the ResponseTeam operator, Direct the Notification to the
ResponseTeam operator on job failure
C. Enable Notifications. Create the ResponseTeam operator. Direct the Notification to the
ResponseTearn operator on failure of the Powersheil job step
D. Create the ResponseTeam operator. Assign the ResponseTeam operator as the fail-safe operator.
Enable the job

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 116
You administer a SQL Server 2008 instance that contains a database named SpatialDB, The SpatialDB
database includes spatial data types, You need to perform a database consistency check on SpatiaIDB to
include the spatial indexes, You also need to ensure that the effect on the database concurrency is
minimized. Which Transact-SQL statement should you execute?

A. DBCC CHECKALLOC (SpatialDB) WITH TABLOCK

B. DBCC CHECKCATALOG (SpatialDB)
C. DBCC CHECKDB (SpatialDB) WITH TABLOCK, PHYSICAL_ONLY
D. DBCC CHECKDB (SpatialDB) WITH EXTENDED_LOGICAL_CHECKS

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 117
You administer a SQL Server 2008 instance that contains a database named Sales. A SQL Server login
named SalesAppLogin maps to a database user named SalesAppUser in the Sales database. You need
to ensure that the database user can perform only the following tasks. Execute all stored procedures that
currently exist in the Sales database. Execute all stored procedures that will be created in the Sales
database. What should you do?

A. Grant the appropriate database-level permissions to SalesAppUser

B. Grant the appropriate object-level permissions to SalesAppUser
C. Grant the appropriate server-level permissions to SalesAppLogin
D. Add SalesAppUser to the appropriate fixed database roles

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:
QUESTION 118
[Link] This question is part of a series of questions that use the same or similar answer choices. An
anser choice may be correct for more than one question in the series. Each question is independent of the
other questions in this series. Information and details provided in a question apply only to that question.
You administer a Microsoft SQL Server 2008 R2 instance. You need to check the physical consistency of
the database. What should you do?

A. Execute DBCC CHECKDB

B. Use the ALTER DATABASEstatement along with SET EMERGENCYclause.
C. Execute sp_helpfile.
D. Execute DBCC CHECKDB along with the REPAIR_REBUILDclause.
E. Use the RESTORE DATABASEstatement along with the PAGESclause. Create a new log backup.
Apply all differential and log backups, including the most recent backup.
F. Restore the database from the most recent full backup. Apply any differential and log backuups.
G. Use the RESTORE DATAVASEstatement along with the PAGESclause. Apply any differential and log
backups. Create a new log backup and then restore the new log backup.
H. Examine the msdb..suspect_pagestable.
I. Execute DBCC CHECKBalong with the REPAIR_FASTclause.

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 119
[Link] administer a SQL Server 2008 instance that contains a very large database named FinancDB. You
plan to create a maintenance plan that meets the following objectives for the FinanceDB database: It
executes the DBCC CHECHDB sataement. -It rebuilds all the indexes. It updates all index statistics. You
need to ensure that the maintenance plan is executed in the minimun amount of time. Which two actions
should you perform? (Each correct answer presents part of the solution. Choose two.)

A. Use the Rebuild Index task.

B. Use the Shrink Database task.
C. Use the Update Statistics task.
D. Use the Reorganize Index task.
E. Use the Check Database Integrity task.

Correct Answer: AE
Section: (none)
Explanation

Explanation/Reference:

QUESTION 120
[Link] administer a Microsoft SQL Server database that hosts an order-processing application.
Employees in the order entry group perform real-time data entry for customers. Employees in the reporting
group run historical reports. Employees in the order entry group report of poor performance when the
reporting group runs report. You need to limit the amount of CPU and RAM used by the reporting group.
What should you do?

A. Create a set of views in a new schema, and then alter the reporting group to use these views.
B. Implement Policy Based Management by using the Server Performance facet.
C. Implement Resource Governor by using a Resource Pool assigned to the users of the reporting group
to limit the amount of CPU/RAM they can use.
D. Creat a maintenance plan that must be run every 10 seconds to find queries of the reporting group
 users and to kill these queries if they are using too many resources.
E. Implement Resource Governor by using a Resource Pool assigned to the users of the data entry group
to ensure that the amount of available CPU/RAM is always sufficient.
F. Implement Policy Based Management by using the Database Performance facet.
G. Use sp_configure, 'min memory per query (kb)' to set the amount of CPU/RAM the reporting gourp can
consume.
H. Use sp_dbmmonitoraddmonitoring to set up a monitoring process to kill the reporting gtoup's queries
that pass a given threshold.

Correct Answer: CE
Section: (none)
Explanation

Explanation/Reference:

QUESTION 121
You maintain a SQL Server 2008 instance. You find a SQL Server Agent job is failing, When you review
the job history information you notice that the job history information is incomplete and appears to be
truncated. You need to ensure that all information produced by a job is available for viewing. What should
you do?

A. Include execution trace messages in the SQL Agent Error log

B. Enable notifications to the Windows application event log when the job completes
C. Enable all job steps to send the output to a file
D. Enable write OEM file

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 122
[Link] a Microsoft SQL Server2008 R2 database instance that supports a sales management
[Link] ,users of the application report that they receive a deadlock [Link] plan to
troubleshoot this [Link] need to ensure that the instance is configured to capture troubleshooting
information that will allow you to isolate the cause of the error .Which two actions should you perform in
sequence?(To answer,move the appropriate actions from the list of actions to the answer area and
arrange them in the correct order.)

A. Restart the SQL Server service.

B. Use the SQL Qerver Configuration Manager tool to enable the Shared Memory Protocol.
C. Use Microsoft SQL Qerver Management Studio to enable the RemotoDacEnabled management facet.
D. Use Microsoft SQL Qerver Management Studio to enable the OleAutomationEnabled management
facet.
E. Use the SQL Server Configuration Manager tool to enable Trace Flag 1204 when the server starts.

Correct Answer: AE
Section: (none)
Explanation

Explanation/Reference:

QUESTION 123
You administer a SQL Server 2008 instance. You use the SQL Server Import and Export Wizard to export
data from SQL Server to a Microsoft Excel file. You save the package definition to a file, You need to
reexecute the package from the command line. Which utility should you use?
A. [Link]
B. [Link]
C. [Link]
D. [Link]

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 124
You administer a SQL Server 2008 instance that contains a database named AdventureWorks. The
AdventureWorks database contains the Products table. You create a Merge Replication topology and a
Publication to replicate the Products table to the SQL Server instances at remote locations. The
Publication has a 21-day retention period. When a user returns from a one-month vacation, she discovers
that her database does not contain the most recent data. The Windows Event log states the following error
message: "Replication: expired subscription dropped." You need to obtain the most recent data in the
database of the user. You also need to ensure that future data changes are appropriately repllcated.
What should you do?

A. Upload unsynchronized changes, and then reinitialize the publication

B. Upload unsynchronized changes
C. Recreate the publication
D. Reinitialize the publication and immediately generate a new snapshot

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 125
[Link] administer a SQL Sever 2008 instance named SQL1.SQL1 contains a database naamed [Link]
create a new user named User1 in the DB1 database No additional permissions have been assigned to
User1 or the public role.User1 will interact with database objects stored in a new schema maned
[Link] need to great the minimum necessary permission to User1 to create tables and stored
procedures in the Reporting [Link] also need to ensure that User1 cannot alter any of the objects in
the Operations schema Which Transact-SQL statements should you execute?

A. GRANT CREATE TABLE,CREATE PROCEDURE TO User1;DENY CONTROL ON SCHEMA::

Operations TO User1;
B. ALTER AUTHORIZATION ON SCHEMA::Reporting TO User1;
C. GRANT CONTROL ON SCHEMA::Reporting TO User1;DENY CONTROL ON SCHEMA::Operations
TO User1;
D. GRANT CREATE TABLE,CREATE PROCEDURE ON SCHEMA::Reporting TO User1

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 126
Note This question is part of a senries of questions that use the samer or similar answer choices. An
answer choice may be correct for more than one question in the series. Each question is independent of
the other questions in this series. Information and details provided in a question apply only to that question.
You administer a Microsoft SQL Server 2008 R2 database that contains an Orderltems table. The table
has the following definition: CREATE TABLE[Orderltems](OrderID INT NOT NULL, OrderDate DATETIME
NOT NULL, OrderLine INT NOT NULL, ProductID INT NOT NULL, Quantity INT NOT NULL, PriceEach
MONEY NOT NULL,); Currently, the table is partitioned by Quarter. You need to make the table
unpartitioned. What should you do?

A. Create a new Filegroup. Create a new database File. Use the ALTER PARITION SCHEME statement
along with the NEXT USED clause. Use the ALTER PARTITION FUNCTION statement along with the
SPLIT RANGE clause.
B. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use the ALTER PARTITION FUNCTION statement along with the MERGE clause.
C. Remove the clustered index from the table.
D. Execute the DBCC CLEANTABLE command on the Orderltems table.
E. Run the following statement: EXWCUTE sp_tableiption@TableName Pattern ='Orderltems',
@OptionName='PartitionByYear',@OptionValue='ture';
F. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
G. Run the following statement: CREATE PARTTION SCHEME SEC_FGAS PARTITION FUNC_FGALL
TO (SECONDARY );
H. Use the ALTER PARTITION FUNCTION... SPLIT RANGE statement.
I. Use the ALTER TABLEstatement to remove the COLLATEoption.
J. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data
onto the partition schene.

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 127
You administer a SQL Server 2008 instance. Users report that applications that run on the server perform
poorly. You suspect that the performance issues are related to table scans. You need to use an
appropriate Windows System Monitor object to capture the appropriate information. Which performance
object should you use?

A. SQLServer:Databases
B. SQLServer:Access Methods
C. SOLServer:BufferManager
D. SQLServe:Memory Manager

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 128
You are reviewing and configuring the security of a SQL Server 2008 instance that contains database DB1.
The security audit policy specifies the following requirements: Only successful and failed logon attempts
are recorded in log files. The SQL Server instance is shutdown if records cannot be written to the log files.
You need to configure the SQL Server instance to comply with the security audit policy. What should you
do?

A. Configure Change Data Capture

B. Enable c2 auditing
C. Create a server audit specification
D. Enable common criteria compliance

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 129
You design a maintenance plan for a SQL Server 2008 instance that contains a database named SaIesDB.
The SalesDB database includes spatial indexes to support queries on spatial data. You need to perform
physical consistency checks on SalesDB. You also need to ensure that the perfomance effect on the
SalesDB database is minimized. Which Transact-SQL statement should you execute?

A. DBCC REPAIRDB(SalesDB)
B. DBCC SQLPERF(SalesDB)
C. DBCC SYS_CHECK(SalesDB)
D. DBCC CHECKDB(SalesDB)

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 130
Note:This question is part of a series of questions that use the same or similar answer [Link] answer
choice may be correct for more than one question in the series Each question is independent of the other
questions in this [Link] and details provided in a question qpply only to that [Link]
administer a Microsoft SQL Server 2008 R2 instanec. The instance has a database named
[Link] database is backed up by using the weekly scheduled SQL Server Agent [Link]
backup shcedule is shown in the following table:A drive fails and the CustomerOrders database goes into
suspect mode on Monday at 09:00 [Link] need to restore the [Link] should you do ?

A. Restore the backup taken on Wedensday only.

B. Restore the backup taken Wennesday,Friday,and Saturday.
C. Restore the backup taken on Sunday and Monday.
D. Restore the backup taken on Wennesday,Thursday at 09:00 hours,an Thursday at 18:00 hours.
E. Restore the backup taken on Wennesday and Thursday at 09:00 hours.
F. Restore the backup taken on Wennesday and Friday.
G. Restore the backup taken on Sunday only.
H. Restore the backup taken on Wennesday,Thursday and Friday.

Correct Answer: G
Section: (none)
Explanation

Explanation/Reference:

QUESTION 131
You plan to install a SQL Server 2008 instance for a new application on an existing server that contains a
default SQL Sewer 2005 instance. You need to ensure that both database instances are available for their
applications. Your solution must meet the following requirements: Minimize administrative effort. Prevent
changes to the existing application environments. What should you do?
A. Configure both applications to use SQL Server 2008
B. Configure both instances to use SQL Server 2008
C. Configure the new application to use the SQL Server 2008 default instance
D. Configure the new application to use a SQL Server 2008 named instance

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 132
You administer three SQL Server 2008 instances named lnstance1, lnstance2, and Instance3. Each of the
three instances runs on a separate server. A mission-critical database is mirrored between lnstance1 and
lnstance2. lnstance3 acts as the witness. lnstance1 currently acts as the Principal. You plan to apply a
patch to both servers. The patch requires a restart of the server. You need to find out the sequence of
steps necessary to ensure that the following requirements are met: The patching process is completed in
the shortest possible time. The database is online on the partner that currently does not apply the patch.
The database does not failover to the other partner during this time. What should you do?

A. Suspend the mirroring session. Apply the patch to the server that runs Instance2. Resume the
mirroring session. Manually failover the mirroring session. Apply the patch to the server that runs
lnstance1
B. Apply the patch to the server that runs lnstance2. Manually failover the mirroring session. Apply the
patch to the server that runs lnstance1
C. Remove the mirroring session. Apply the patch to the server that runs Instance2. Apply the patch to
the server that runs Instance1. Re-establish the mirroring session
D. Apply the patch to the server that runs |nstance2. Apply the patch to the server that runs lnstance1

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 133
You administer two SQL Server 2008 instances named Instance1 and |nstance2. A database named
Customers resides on |nstance1. You move the Customers database from Instance1 to |nstance2. A SQL
Server login named User1 that has the password "111999" is used by a user to access the database on |
nstance1. You create the same SQL Server login on Instance2. The user attempts to access the
Customers database on |nstance2 by using the SQL Server login User1. However, the user receives an
error message which indicates that the access to the Customers database is denied. You need to ensure
that User1 can access the Customers database. Which Transact-SQL statements should you execute on
Instance2?

A. USE Customers;ALTER USER User1 ENABLE

B. USE Customers;ALTER LOGIN USerI WITH PASSWORD = '111999' UNLOCK
C. USE Customers;ALTER USER User1 WITH LOGIN = User1
D. USE Customers;ALTER LOGIN User1 WITH DEFAULT_DATABASE = Customers

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 134
Note:This question is part of a series of questions that use the same or similar answer choices. An answer
choice may be correct for more than one question in the series. Eash question is independent of the other
questions in this series. Information and details provided in a question apply only to that question. You
administer a Microsoft SQL Server 2008 R2 instance. The instance has a database named
CustomerOrders. The database is backed up by using the weekly scheduled SQL Server Agent jobs. The
backup schedule is shown in the following table: A drive fails and the CustomerOrders database goes into
suspect mode on Tuesday at 09:00 hours. You need to restore the backup. What should you do?

A. Restore the backups taken on Wednesday and Thursday at 09:00 hours.

B. Restore the backups taken on Wednesday,Thursday,and Friday.
C. Restore the backups taken on Wednesday and Friday.
D. Restore the backups taken on Wednesday only.
E. Restore the backups taken on Wednesday, Friday, and Saturday.
F. Restore the backups taken on Sunday only.
G. Restore the backups taken on Wednesday, Thursday at 09:00 hour, and Thursday at 18:00 hours.
H. Restore the backups taken on Sunday and Monday.

Correct Answer: H
Section: (none)
Explanation

Explanation/Reference:

QUESTION 135
You administer a SQL Sewrvr 2008 instance. You contigure a single Database Mail profile that is used to
send reports to all the employees of your organization. The sp_send_dbmail stored procedure is used to
send a large volume of reports during business hours. The reports to the manager take a long time to be
delivered. You need to ensure that the reports to the manager are sent as quickly as possible. What
should you do?

A. Add another SMTP account to the existing Database Mail profile

B. Use the @importance = highparameter when you send the reports to the manager
C. Configure a new Database Mail profile to be used for the reports to the manager
D. Change the Account Retry Attempts option of the Database Mail system properties

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 136
You administer a SQL Server 2008 cluster in a high security environment, You plan to configure and use
encrypted connections for the clustered virtual SQL Server. You need to install the certificate that will be
used for encryption. What should you do?

A. Install the encryption certificate in the SQL Server shared disk

B. Install the encryption certificate on each individual node
C. Install the encryption in the cluster quorum drive
D. Install the encryption certificate in the cluster group

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:
QUESTION 137
You migrate an application from Microsoft SQL Server 2000 to Microsoft SQL Server 2008, You need to
monitor the SQL Server instance to record the use of features that will be discontinued, What should you
do?

A. Use a SQL server-side trace that captures the SQL:BatchCompleted and Exception event classes
B. Use the SQL Serer Profiler that captures the SQL:BatchCompleted and Exception event classes
C. Use a SQL serer-side trace that captures the Deprecation Announcement and Deprecation Final
Support event classes
D. Use the SQL Serer 2008 Upgrade Advisor

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 138
You administer a SQL Server 2008 instance, You need to find out the number of users who access the
SQL Server instance by using Windows System Monitor, Which performance object should you capture?

A. SQLServer:Exec Statistics
B. SQLServer:Access Methods
C. SQLServerL:General Statistics
D. SOLServer:Buffer Manager

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 139
You administer a SQL Server 2008 instance that contains the AdventureWorks database. The
AdventureWorks database uses the bulk-logged recovery mode. You log ship the AdventureWorks
database across a WAN link. There is a scheduled job that rebuilds the indexes of the AdventureWorks
database. You discover that the job increases the size of the log backups for log shipping, You need to
minimize the size of the log backups used by log shipping, What should you do?

A. Alter the AdventureWorks database to use the Full recovery mode

B. Change the job to drop the indexes, and then recreate the indexes
C. Alter the AdventureWorks database to use the Simple recovery mode
D. Compress the log file backups

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 140
Note: This question is part of a series of questions that use same or similar answer choices. An answer
choice may be correct for more than one question in the series. Each question is independent of the other
questions in this series. Information and details provided in a question apply only to that question. You
administer a Microsoft SQL Server 2008 R2 instance. The instance has a database named
CustomerOrders. The database is backed up by using the weekly scheduled SQL Server Agent jobs. The
backup schedule is shown in the following table: A drive fails and the CustomerOrders database goes into
suspect mode on Sunday at 09:00 hours. You need to restore the backup . What should you do ?

A. Restore the backup taken on Wednesday,Thursday, and Friday.

B. Restore the backup taken on Wednesday,Thursday at 09:00 hours, and Thursday at 18:00 hours.
C. Restore the backup taken on Wednesday, Friday, and Saturday.
D. Restore the backup taken on Sunday and Monday.
E. Restore the backup taken on Sunday only.
F. Restore the backup taken on Wednesday and Friday.
G. Restore the backup taken on Wednesday only.
H. Restore the backup taken on Wednesday and Thursday at 09:00 hours.

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 141
You administer a SQL Server 2008 instance that contains a database named AdventureWork. The
AdventureWork database contains a table named Orders. The Orders table has several indexes and a
very large number of rows. The database supports an enterprise Web environment that is constantly used.
Thirty percent of the indexes on the Orders table is fragmented. You need to defragment the indexes by
ensuring that the effect on database availability is minimized. Which option should you include in the
ALTER INDEX statement ?

A. Pad index
B. Sort in tempdb
C. Fill factor
D. Online

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 142
You administer a SQL Sewer 2008 instance. You need to configure the instance to use a single thread for
queries that have an estimated execution cost less than 3. Which sp_configure configuration option
should you set?

A. max worker threads

B. precompute rank
C. query governor cost limit
D. priority boost
E. cost threshold for paralleism

Correct Answer: E
Section: (none)
Explanation

Explanation/Reference:

QUESTION 143
You troubleshoot a SQL Server 2008 instance. You suspect that an application uses an inefficient locking
strategy and causes concurrency problem. You need to identify the following . The sessions that wait for a
resource. The resource needed. The sessions that block the resource. Which dynamic management view
should you use?

A. sys.dm_tran_active_transactions
B. sys.dm_exec_requests
C. sys.dm_os_waiting_tasks
D. sys.dm_os_wait_stats

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference:

QUESTION 144
You administer a Microsoft SQL Server database. You plan to deploy a new database application. You
want to use SQL Server common language runtime (CLR) integration . You need to ensurs that CLR
assemblies can be used. What should you do?

A. Open Microsoft SQL Server Management Studio and connect to the server. Right-click the server and
select Properties. Browse to the Advanced tab. Configure the Filestream Access Level option to Full
access enabled.
B. Open Microsoft SQL Server Management Studio and connect to the server. Right-click the server and
select Properties. Browse to the Advanced tab. Configure the CLR Integration property to True.
C. Open Microsoft SQL Server Management Studio and connect to the server. Right-click the server and
select Facets. Choose the Server Configuration [Link] the AweEnabled property to True.
D. Open Microsoft SQL Server Management Studio and connect to the server. Right-click the server and
select Facets. Choose the Surface Area Configuration facet. Configure the CLR Integration property to
True.
E. Open Microsoft SQL Server Management Studio and connect to the server. Right-click the server and
select Facets. Choose the Server Security facet. Configure the XPCmdShellEnabled property to True.
F. Run the following statement: EXEC sp_configure 'show advanced options',1RECONFIGUREGOEXEC
sp_configure 'clr enabled', 1RECONFIGUREGO.
G. Run the following statement: EXEC sp_configure 'show advanced options',1RECONFIGUREGOEXEC
sp_configure 'enable extended procedures',1RECONFIGUREGO.
H. Run the following statement: EXEC sp_configure 'show advanced options',1RECONFIGUREGOEXEC
sp_configure 'xp_cmdshell',1RECONFIGUREGO.

Correct Answer: DF
Section: (none)
Explanation

Explanation/Reference:

QUESTION 145
You maintain a SQL Server 2008 instance, You have an existing database maintenance plan that performs
the following tasks: Rebuilds indexes. Checks database integrity. Writes a report to a text file. You need
to ensure that an e-mail message is sent to the operators when the maintenance plan fails to execute
successfully. What should you modify?

A. The instance
B. The SQL Server Agent job
C. The database maintenance plan
D. The SQL Server service

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 146
You maintain multiple SQL Server 2008 instances. You are designing a consolidated repository of
performance data. You need to ensure that the following requirements are met: he data collector is used
to gather performance information. A single database stores performance information for all instances.
Performance information that is older than 14 days is deleted. Administrative effon to manage
performance data is minimized. What should you do?

A. Create a SQL Agentjob process on each instance to store and delete performance data in a single
database for all instances
B. Configure a management data warehouse process on each instance to store and delete performance
data in a single database for all instances
C. Configure an automated server-side trace process on each instance to store and delete performance
data in a single database for all instances
D. Create and schedule a single Microsoft SQL Service Integration Services (SSIS) package process to
store and delete performance data in a single database for instances

Correct Answer: B
Section: (none)
Explanation

Explanation/Reference:

QUESTION 147
You configure a SQL Server 2008 instance that contains a database named Customers. The Customers
database experiences deadlock problems. You need to capture the deadlock informatlon to the SQL
Server error log. What should you do?

A. Configure trace flags and then restart the SQL Server instance
B. Configure a Data Collector Set and then restart the SQL Server instance
C. Enable the Windows Event Collector service
D. Enable file system auditing

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 148
You administer a SQL Server 2008 instance. The production server hosts databases for several mission-
critical applications. You plan to use the Resource Governor to limit the effect of queries that are executed
by Microsoft SQL Server Management Studio. You need to ensure that the following requirements are
met: Queries initiated through
SQL Server Management Studio do not exceed 20 percent of CPU utilization. Queries initiated by the
mission-critical applications are allowed to consume 100 percent of CPU utilization when required. What
should you do?

A. Atter the default resource pool and set the MAX_CPU_PERCENT option to 20, Assign this resource
pool to the workload group used by the mission-critical applications
B. Alter the default resource pool and set the MAX_CPU_PERCENT option to 20. Assign this resource
pool to the workload group used by SQL Server Management Studio
C. Create a new resource pool and set the MAX_CPU_PERCENT option to 20, Assign this resource pool
 to the workload group used by the mission-critical applications
D. Create a new resource pool and set the MAX_CPU__PERCENT option to 20. Assign this resource
pool to the workload group used by SQL Server Management Studio

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 149
You configure a SQL Sewer 2008 instance that contains a database named AdventureWorks. The
AdventureWorks database experiences deadlock problems. You need to capture the deadlock information
to the SQL Server error log. What should you do?

A. Configure a SQL Profiler trace to capture the deadlock graphs

B. Configure the data collector to capture the deadlock graphs
C. Enable Server Auditing for the AdventureWorks database
D. Set the appropriate trace flags as a startup parameter and restart the SQL Server instance

Correct Answer: D
Section: (none)
Explanation

Explanation/Reference:

QUESTION 150
You maintain an instance of Microsoft SQL Server 2008. The instance contains a database named
Finance. The recovery model of the Finance database is setto Full, You deploy a new process that
modifles 10,000 records from the Accounts table at 19:00 hours daily. You need to ensure that any
modification to the data can be reverted without the database going offline, Which strategy should you
implement?

A. Database snapshots
B. Differential backup
C. Transaction log backup
D. Primary filegroup backup

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 151
Note: This question is part of a series of questions that use the same or similar answer choices. An answer
choice may be correct for more than one question in the series. Each question is independent of the other
questions in this series. Information and details provided in a question apply only to that question. You
administer a Microsoft SQL Server 2008 R2 database that contains an Orderltems table has the following
definition: CREATE TABLE [Orderltems](OrderID INT NOT NULL, OrderData DATETIME NOT NULL,
OrderLine INT NOT NULL, ProductID INT NOT NULL , Quantity INT NOT NULL, PriceEach MONEY NOT
NULL,);Currently, the table is not partitioned and contains no [Link] need to partition the table by
year. What should you do?

A. Create a new partition [Link] a new partition [Link] a clustered index to place the data
onto the partition scheme.
B. Use the ALTER PARTITION FUNCTION...SPLIT RANGEstatement.
C. Run the following statement :EXECUTE sp_tableoption@TableNamePattern='Orderltems'.
@OptionName='PartitionByYear',@OptionValue='true'
D. Create a new filegroup .Create a new database [Link] the ALTER PARTITION SCHEME statement
along with the NEXT USED clause. Use ALTER INDEX REORGANIZE statement.
E. Create a new [Link] a new database [Link] the ALTER PARTITION SCHEME statement
along with the NEXT USED [Link] the ALTER PARTITION FUNCTION statement along with the
SPLIT RANGE clause.
F. Execute the DBCC CLEANTABLE command on the OrderItemstable.
G. Run the following statement:CREATE PARTITION SCHEME SEC_FGAS PARTITION FUNC_FGALL
TO (SECONDARY).
H. Remove the clusterd index from the table
I. Use the ALTER TABLE statement to remove the COLLATE option.
J. Create a new table. Use the ALTER TABLE statment along with the SWITCH PARTITION clause. Use
the ALTER PARTITION FUNCTION staement along with the MERGE RANGE clause.

Correct Answer: A
Section: (none)
Explanation

Explanation/Reference:

QUESTION 152
You administer a SQL Server 2008 instance. The instance contains an On-Line Analytical Processing
(OLAP) database along with a dimension table named Customers, The data of the Customers table is
updated every hour. The Customers table contains redundant data. You need to conserve the disk space
used to store the Customers table. Which compression technology should you use?

A. Row compression
B. Windows NTFS file system compression
C. Page compression
D. Backup compression

Correct Answer: C
Section: (none)
Explanation

Explanation/Reference: