Exam A
QUESTION 1
You maintain a SQL Server 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 sys.dm_os_performance_counters to monitor the CPU usage % counter of the SQLServer: Workload Group Stats performance object for the MyApp workload group.
B. Use the request_max_cpu_time_sec column of the sys.resource_governor_workload_groupscatalog view.
C. Use the sys.dm_resource_governor_workoad_groups dynamic management view to monitor the CPU usage for the default workload group.
D. Use 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.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 2
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 = uc.GroupName FROM dbo.UserClassification uc WHERE uc.UserName = 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.
Which four Transact-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:
Feedback:
...Because it is the REPORTING group that generates the large reports that can now use only up to 35% CPU and 45% RAM.
https://www.google.es/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4&cad=rja&ved=0CFkQFjAD&url=http %3A%2F%2Fdownload.microsoft.com%2Fdownload%2FD%2FB%2FD%2FDBDE7972-1EB9-470A-BA18-58849DB3EB3B%2FResourceGov. docx&ei=1pjcULqtD47BswbE9YCoAQ&usg=AFQjCNEESyvR6ppfxi02wgN9EWGKnBbVcA&bvm=bv.1355 534169,d.Yms http://msdn.microsoft.com/en-us/library/bb933866(v=sql.105).aspx http://msdn.microsoft.com/en-us/library/cc645892(v=sql.105).aspx
QUESTION 3
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 4 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 instance.
You need to check the physical consistency of the database.
What should you do?
A. Execute sp_helpfile.
B. Execute DBCC CHECKDB.
C. Examine the msdb...suspect_pages table.
D. Execute DBCC CHECKDB along with the REPAIR_FAST clause.
E. Execute DBCC CHECKDB along with the REPAIR_REBUILD clause.
F. Restore the database from the most recent full backup. Apply any differential and log backups.
G. Use the ALTER DATABASE statement along with the SET EMERGENCY clause.
H. Use the RESTORE DATABASE statement along with the PAGE clause. Create a new log backup. Apply all differential and log backups, including the most recent backup.
I. Use the RESTORE DATABASE statement along with the PAGE clause. Apply any differential and log backups. Create a new log backup and then restore the new log backup.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 5 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 instance.
You need to rectify a damaged index without losing any data.
What should you do?
A. Execute sp_helpfile.
B. Execute DBCC CHECKDB.
C. Examine the msdb...suspect_pages table.
D. Execute DBCC CHECKDB along with the REPAIR_FAST clause.
E. Execute DBCC CHECKDB along with the REPAIR_REBUILD clause.
F. Restore the database from the most recent full backup. Apply any differential and log backups.
G. Use the ALTER DATABASE statement along with the SET EMERGENCY clause.
H. Use the RESTORE DATABASE statement along with the PAGE clause. Create a new log backup. Apply all differential and log backups, including the most recent backup.
I. Use the RESTORE DATABASE statement along with the PAGE clause. Apply any differential and log backups. Create a new log backup and then restore the new log backup.
Correct Answer: E Section: (none) Explanation
Explanation/Reference:
QUESTION 6 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 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 sp_helpfile.
B. Execute DBCC CHECKDB.
C. Examine the msdb...suspect_pages table.
D. Execute DBCC CHECKDB along with the REPAIR_FAST clause.
E. Execute DBCC CHECKDB along with the REPAIR_REBUILD clause.
F. Restore the database from the most recent full backup. Apply any differential and log backups.
G. Use the ALTER DATABASE statement along with the SET EMERGENCY clause.
H. Use the RESTORE DATABASE statement along with the PAGE clause. Create a new log backup. Apply all differential and log backups, including the most recent backup.
I. Use the RESTORE DATABASE statement along with the PAGE clause. Apply any differential and log backups. Create a new log backup and then restore the new log backup.
Correct Answer: F Section: (none) Explanation
Explanation/Reference:
QUESTION 7
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:
http://blogs.msdn.com/b/sqlserverfaq/archive/2011/05/11/inf-hey-my-sql-server-service-is-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 8
You administer three SQL Server 2008 instances on separate servers named Server1, Server2 and Server3.
The AdventureWorks database is configured for mirroring between the instances. In the mirroring session, Sever1 and Server2 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 server 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 minimum possible time.
You need to ensure that automatic failover is disabled during the maintenance process.
What should you do first?
A. Suspend the mirroring session.
B. Configure the mirroring session with the SAFETY option set to ON.
C. Remove the witness server from the mirroring session.
D. Remove mirroring from the database.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
Feedback:
Because the need of ensure that automatic failover is disabled.
http://msdn.microsoft.com/en-us/library/ms189590(v=sql.105).aspx
At any time during a database mirroring session, the database owner can turn off (remove) the witness for a database mirroring session.
Turning off the witness changes the operating mode in accordance with the transaction-safety setting:
If transaction safety is set to FULL (the default), the session uses high-safety, synchronous mode without automatic failover. For more information, see Synchronous Database Mirroring (High-Safety Mode).
If transaction safety is set to OFF, the session operates asynchronously (in high-performance mode) without requiring quorum. Whenever transaction safety is turned off, we strongly recommend also turning the witness off. For more information, see Asynchronous Database Mirroring (High-Performance Mode).
The transaction safety setting of the database is recorded on each partner in the sys.database_mirroring catalog view in the mirroring_safety_level and mirroring_safety_level_desc columns. For more information, see sys.database_mirroring (Transact-SQL).
To turn off (remove) the witness
Connect to either partner.
Issue the following statement:
ALTER DATABASE
SET WITNESS OFF
where is the name of the mirrored database.
QUESTION 9
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 by using the FAILOVER option.
B. Configure the mirroring session by adding a witness server to it.
C. Configure the mirroring session with the SAFETY option set to OFF.
D. Configure the mirroring session with the SAFETY option set to FULL.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Correct answer is: Configure the mirroring session by adding a witness server to it.
---From Rob:
By default a session is set to full transaction safety (SAFETY is set to FULL), which starts the session in
synchronous, high-safety mode without automatic failover. You can reconfigure the session to run in high-safety mode with automatic failover or in asynchronous, high-performance mode, as follows:
.High-safety mode with automatic failover
If you want a high-safety mode session to support automatic failover, add a witness server instance.
Source:http://msdn.microsoft.com/en-us/library/ms190941.aspx
QUESTION 10
You administer a Microsoft SQL Server 2008 R2 instance that contains two databases named Northwind2008R2 and AdventureWorks.
The Northwind2008R2 database uses the SQL_Latin1_General_CP1_CI_AS collation and the AdventureWorks database uses the SQL_Latin1_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 *
FROM AdventureWorks.dbo.customer adv INNER JOIN Northwind2008R2.dbo.customer nrt ON adv.CustomerName = nrt.CustomerName COLLATE
SQL_Latin1_General_CP1_CI_AS;
B. SELECT *
FROM
AdventureWorks.dbo.customer adv INNER JOIN
Northwind2008R2.dbo.customer nrt ON adv.CustomerName = nrt.CustomerName
USING COLLATION SQL_Latin1_General_CP1_CI_AS;
C. SET COLLATE SQL_Latin1_General_CP1_CI_AS; SELECT *
FROM
AdventureWorks.dbo.customer adv INNER JOIN
Northwind2008R2.dbo.customer nrt ON adv.CustomerName = nrt.CustomerName
D. SELECT *
FROM AdventureWorks.dbo.customer adv INNER JOIN Northwind2008R2.dbo.customer nrt ON adv.CustomerName = nrt.CustomerName COLLATE
SQL_Latin1_General_CP1_CS_AS;
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
Disputed that this is the correct answer:
SELECT
* FROM
AdventureWorks.dbo.customer adv INNER JOIN
Northwind2008R2.dbo.customer nrt ON adv.CustomerName = nrt.CustomerName COLLATE SQL_Latin1_General_CP1_CS_AS;
QUESTION 11
You administer a Microsoft 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 Server Configuration Manager
Expand the SQL Server Network Configuration services. 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.
B.
Open SQL Server Configuration Manager
Expand the SQL Native Client 10.0 Configuration service.
Select the Client Protocols service. Set the Properties option.
C.
Open SQL Server Configuration Manager
Select the SQL Server Service service.
In the right panel, right click SQL Server (InstanceName), right-click Properties and then select the Advanced tab.
D.
Open SQL Server Configuration Manager
Select the SQL Server Service service.
In the right panel, right click SQL Server Browser, right-click Properties, and then select the Advanced tab.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 12
You administer a database for an online ordering system.
You plan to create a disaster recovery plan that uses transaction log backups by using only 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 9:00 hours and 17:00 hours.
Log backups occur every hour between 17:00 hours and 9:00 hours.
What should you do?
A. Create two SQL Server Integration Services (SSIS) packages by using a single schedule.
B. Create a single SQL Server Integration Services (SSIS) package by using two schedules.
C. Insert a new row to dm_os_schedulers.
D. Create a database maintenance plan by using the Database Maintenance Wizard.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 13
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:
Feedback:
QUESTION 14
You administer two SQL Server 2008 instance that contains a database named DB1. DB1 contains a table named Sales.Table1.
You 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. EXEC sp_helprotect 'Sales.Table1', 'User1';
B. EXECUTE AS USER = 'User1'; SELECT * FROM sys.fn_my_permissions('Sales.Table1', 'OBJECT');
C. SELECT * FROM sys.database_permissions WHERE class_desc = OBJECT_ID('Sales.Table1') AND grantee_principal_id = USER_ID('User1');
D. SELECT * FROM sys.sysprotects WHERE uid = USER_ID('User1') AND id = OBJECT_ID('Sales.Table1');
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms176097(v=sql.105).aspx
QUESTION 15
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 16
You administer two SQL Server 2008 instances named Instance1 and Instance2.
A database named Customers resides on Instance1. You move the Customers database from Instance1 to
Instance2.
The user attempts to access the Customers database on Instance2 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 the 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 User1 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:
From MSDN: http://msdn.microsoft.com/en-us/library/ms176060.aspx
ALTER USER : Renames a database user or changes its default schema.
ALTER USER userName
WITH [ ,...n ]
::=
NAME = newUserName | DEFAULT_SCHEMA = schemaName | LOGIN = loginName |
PASSWORD = 'password' [ OLD_PASSWORD = 'oldpassword' ]
Arguments:
userName
Specifies the name by which the user is identified inside this database.
LOGIN = loginName
Re-maps a user to another login by changing the user��s Security Identifier (SID) to match the login��s SID.
NAME = newUserName
Specifies the new name for this user. newUserName must not already occur in the current database.
DEFAULT_SCHEMA = schemaName
Specifies the first schema that will be searched by the server when it resolves the names of objects for this user. Renames a database user or changes its default schema.
PASSWORD = 'password'
Specifies the password for the user that is being changed. Passwords are case-sensitive. Note: This option is available only for contained users. See Contained Databases and sp_migrate_user_to_contained (Transact-SQL) for more information.
OLD_PASSWORD ='oldpassword'
The current user password that will be replaced by 'password'. Passwords are case-sensitive. OLD_PASSWORD is required to change a password, unless you have ALTER ANY USER permission. Requiring OLD_PASSWORD prevents users with IMPERSONATION permission from changing the password. Note: This option is available only for contained users.
Remarks:
If DEFAULT_SCHEMA is left undefined, the user will have dbo as its default schema.
DEFAULT_SCHEMA can be set to a schema that does not currently occur in the database. Therefore, you
can assign a DEFAULT_SCHEMA to a user before that schema is created. DEFAULT_SCHEMA cannot
be specified for a user who is mapped to a Windows group, a certificate, or an asymmetric key.
Important:
The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All
members of the sysadmin fixed server role have a default schema of dbo.
You can change the name of a user who is mapped to a Windows login or group only when the SID of the new user name matches the SID that is recorded in the database. This check helps prevent spoofing of Windows logins in the database.
The WITH LOGIN clause enables the remapping of a user to a different login. Users without a login, users mapped to a certificate, or users mapped to an asymmetric key cannot be re-mapped with this clause. Only SQL users and Windows users (or groups) can be remapped. The WITH LOGIN clause cannot be used to change the type of user, such as changing a Windows account to a SQL Server login.
The name of the user will be automatically renamed to the login name if the following conditions are true.
The user is a Windows user.
The name is a Windows name (contains a backslash).
No new name was specified.
The current name differs from the login name.
Otherwise, the user will not be renamed unless the caller additionally invokes the NAME clause.
QUESTION 17 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 OrderItems table.
The table has the following definition:
CREATE TABLE [OrderItems]
(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 year with each in its own filegroup.
You need to add a new partition for the upcoming year.
What should you do?
A. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION �� SPLIT RANGE statement.
C. Use the ALTER TABLE statement to remove the COLLATE option.
D. Execute the DBCC CLEANTABLE command on the OrderItems table.
E.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause.
Use ALTER INDEX REORGANIZE statement.
F.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause
Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause
G.
Create a new table.
Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H.
Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
I. Run the following statement:
CREATE PARTITION SCHEME SEC_FG
AS PARTITION FUNC_FG
ALL TO ( SECONDARY );
J. Run the following statement:
EXECUTE sp_tableoption
@TableNamePattern ='OrderItems',
@OptionName= 'PartitionByYear',
@OptionValue= 'true';.
Correct Answer: F Section: (none) Explanation
Explanation/Reference:
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 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 OrderItems table.
The table has the following definition:
CREATE TABLE [OrderItems]
(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 not partitioned and contains no indexes.
You need to partition the table by year.
What should you do?
A. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION �� SPLIT RANGE statement.
C. Use the ALTER TABLE statement to remove the COLLATE option.
D. Execute the DBCC CLEANTABLE command on the OrderItems table.
E.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause
Use ALTER INDEX REORGANIZE statement.
F.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause
Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause
G.
Create a new table.
Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H.
Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
I. Run the following statement:
CREATE PARTITION SCHEME SEC_FG
AS PARTITION FUNC_FG
ALL TO ( SECONDARY );
J. Run the following statement:
EXECUTE sp_tableoption
@TableNamePattern ='OrderItems',
@OptionName= 'PartitionByYear',
@OptionValue= 'true';.
Correct Answer: H Section: (none) Explanation
Explanation/Reference:
Original answer was:
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement
Use ALTER INDEX REORGANIZE statement.
Original answer disputed:
From my point of view the best choice is:
Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
http://sqlblogcasts.com/blogs/martinbell/archive/2009/07/22/Partitioning-existing-tables.aspx http://www.kodyaz.com/articles/how-to-partition-table-non-partitioned-table-sql-server-2008.aspx http://forums.asp.net/t/1581091.aspx/1
QUESTION 19 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 OrderItems table.
The table has the following definition:
CREATE TABLE [OrderItems]
(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. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION �� SPLIT RANGE statement.
C. Use the ALTER TABLE statement to remove the COLLATE option.
D. Execute the DBCC CLEANTABLE command on the OrderItems table.
E.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause.
Use ALTER INDEX REORGANIZE statement.
F.
Create a new filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause.
Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
G.
Create a new table.
Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H.
Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
I. Run the following statement:
CREATE PARTITION SCHEME SEC_FG
AS PARTITION FUNC_FG
ALL TO ( SECONDARY );
J. Run the following statement:
EXECUTE sp_tableoption
@TableNamePattern ='OrderItems',
@OptionName= 'PartitionByYear',
@OptionValue= 'true';.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
Feedback by Rafe:
If the table has a partitioned clustered index applied to it, you can obtain the same result by dropping the
index and rebuilding it as a nonpartitioned index.
You can do this by using the Transact-SQL CREATE INDEX command with the DROP EXISTING = ON
clause.
QUESTION 20 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 OrderItems table.
The table has the following definition:
CREATE TABLE [OrderItems] (OrderID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderLine INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
PriceEach MONEY NOT NULL,
);
Data is grouped into quarterly partitions.
You need to configure the groupings into 12 monthly partitions.
What should you do?
A. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION �� SPLIT RANGE statement.
C. Use the ALTER TABLE statement to remove the COLLATE option.
D. Execute the DBCC CLEANTABLE command on the OrderItems table.
E.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause
Use ALTER INDEX REORGANIZE statement.
F.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause
Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause
G.
Create a new table.
Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H.
Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
I. Run the following statement:
CREATE PARTITION SCHEME SEC_FG
AS PARTITION FUNC_FG
ALL TO ( SECONDARY );
J. Run the following statement:
EXECUTE sp_tableoption
@TableNamePattern ='OrderItems',
@OptionName= 'PartitionByYear',
@OptionValue= 'true';.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Feedback:
A. Splitting a partition of a partitioned table or index into two partitions
The following example creates a partition function to partition a table or index into four partitions. ALTER
PARTITION FUNCTION
splits one of the partitions into two to create a total of five partitions.
Transact-SQL
IF EXISTS (SELECT * FROM sys.partition_functions
WHERE name = 'myRangePF1')
DROP PARTITION FUNCTION myRangePF1;
GO CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES ( 1, 100, 1000 ); GO --Split the partition between boundary_values 100 and 1000 --to create two partitions between boundary_values 100 and 500 --and between boundary_values 500 and 1000.
ALTER PARTITION FUNCTION myRangePF1 () SPLIT RANGE (500)
QUESTION 21 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 OrderItems table.
The table has the following definition:
CREATE TABLE [OrderItems]
(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 year with each year in its own filegroup.
You 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. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION �� SPLIT RANGE statement.
C. Use the ALTER TABLE statement to remove the COLLATE option.
D. Execute the DBCC CLEANTABLE command on the OrderItems table.
E.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause
Use ALTER INDEX REORGANIZE statement.
F.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause
Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause
G.
Create a new table.
Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H.
Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
I. Run the following statement:
CREATE PARTITION SCHEME SEC_FG
AS PARTITION FUNC_FG
ALL TO ( SECONDARY );
J. Run the following statement:
EXECUTE sp_tableoption
@TableNamePattern ='OrderItems',
@OptionName= 'PartitionByYear',
@OptionValue= 'true';.
Correct Answer: G Section: (none) Explanation
Explanation/Reference:
Feedback by Rafe:
Create an empty historical table on the same filegroup where the obsolete data resides, perhaps called
Filegroup_1989.
Switch data from the fact table to the historical table.
Merge the first partition thereby removing the boundary point for 1989.
Move data from the historical table to a different database. Then drop the historical table.
http://sqlserverpedia.com/wiki/Switching_Partitions_-_Example
QUESTION 22
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 TO BE MODIFIED] TRANSFER [FROM OLD 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 DevUser.Products
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 23
You administer a SQL Server 2008 instance.
You need to identify 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. Set a trace flag.
D. View the SQL error log.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
To retrieve your current connection properties you'd run the DMV:
select * from sys.dm_exec_connectionswhere session_id = @@SPID
The field values depend on the protocol used to connect (shared memory, named pipes or tcp) but all contain information about authentication method used, protocol and client net address.
QUESTION 24
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:
"Your SQL Server installation completed with failures."
You close the error window before noting the location where additional information about the error is recorded. You need to located the additional information.
Where would you search?
A. The Event Viewer System log file
B. The C:\Program Files\Microsoft SQL Server\MSSQL\LOG folder
C. The C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\ folder
D. The Event Viewer Application log file
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
Look at the Configuration log in C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\[date and time of installation]\Detail.txt
QUESTION 25
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 FULLTEXT INDEX statement. You execute an ALTER FULLTEXT INDEX.
Which command or commands should you use next?
A. ALTER INDEX Customers REORGANIZE
B. ALTER FULLTEXT INDEX on Customers START FULL POPULATION
C. DROP FULLTEXT INDEX
D. ALTER FULLTEXT INDEX on Customers ENABLE
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 26
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. Modify the SMTP relay settings.
B. Modify the parameters of the email that contains the reports.
C. Create a separate Database Mail profile for the reports.
D. Configure the security settings of the Database Mail system.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
A Database Mail profile is a collection of Database Mail accounts.
Profiles improve reliability in cases where an e-mail server becomes unreachable, by providing alternative
Database Mail accounts. At least one Database Mail account is required.
QUESTION 27
You administer a SQL Server 2008 instance.
You need to configure the instance to use a single thread for queries that have an estimated execution cost of less than 3. Which sp_configure configuration option should you set?
A. cost threshold for parallelism
B. precompute rank
C. max worker threads
D. priority boost
E. query governor cost limit
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 28
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 OrderID on the OrderID 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 ProductID to the OrderSpecs table.
You need to include ProductId 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.
Disable the XML index on the ProductSpecs column.
Modify the primary key.
Enable the XML index on the ProductSpecs column.
C.
Drop the XML index on the table.
Modify the primary key.
Recreate the XML index.
D.
Move the XML data to a temporary.
Create the XML data from the original table by setting the ProductsSpecs column to NULL.
Modify the primary key.
Repopulate the ProductsSpecs column.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 29
You maintain a SQL Server 2008 instance that contains a database named Sales. The Sales database contains a table named CompanyName.
The current database collation is SQL_Latin1_General_Cp1_CI_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 do?
A.
Drop all indexes on the Customer table that include the CompanyName column in the index key.
Modify the column to specify the SQL_Latin1_General_Cp1_CS_AS collation.
Recreate the CompanyName indexes on the Customer table.
B. Use the UPPER function in the filter criteria on all queries that filter on the CompanyName column.
C. Use the CAST function in the filter criteria on all queries that filter on the CompanyName column.
D.
Drop all indexes on the Sales database.
Modify the column to specify the SQL_Latin1_General_Cp1_CS_AS collation.
Rebuild all the indexes.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 30
You maintain a SQL Server 2008 instance that contains a database named AdventureWorks.
You plan to use data compression to conserve disk space. You compress a table named VeryLarge that has a clustered 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 indexes of 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 Index1 ON VeryLarge REORGANIZE WITH (LOB_COMPACTION = ON);
D. ALTER INDEX ALL ON VeryLarge REBUILD WITH (DATA_COMPRESSION = ROW);
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 31
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 uses the Automatically delete job option.
B. Create an Alert that will be raised when the job completes. This Alert will run another job to delete the maintenance job.
C. Create a job that is assigned to the Database Maintenance category.
D. Create a job that is scheduled to run once.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 32 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 OrderItems table.
The table has the following definition:
CREATE TABLE [OrderItems]
(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. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION �� SPLIT RANGE statement.
C. Use the ALTER TABLE statement to remove the COLLATE option.
D. Execute the DBCC CLEANTABLE command on the OrderItems table.
E.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause
Use ALTER INDEX REORGANIZE statement.
F.
Create a new Filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with NEXT USED clause
Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause
G.
Create a new table.
Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H.
Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
I. Run the following statement:
CREATE PARTITION SCHEME SEC_FG
AS PARTITION FUNC_FG
ALL TO ( SECONDARY );
J. Run the following statement:
EXECUTE sp_tableoption
@TableNamePattern ='OrderItems',
@OptionName= 'PartitionByYear',
@OptionValue= 'true';.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 33
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 in the management data warehouse every 6 hours.
Which data collection process should you implement?
A. Create a scheduled non-cache data collection.
B. 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.
C. Create a cached data collection.
D. Create an on-demand non-cache data collection.
Correct Answer: A Section: (none)
Explanation Explanation/Reference:
QUESTION 34
You administer a Microsoft SQL Server 2008 R2 database instance.
You need to identify queries or stored procedures that are consuming server resources. You also need to view the query plan that is being used by each of these queries or procedures.
Which Dynamic Management Views (DMVs) should you use to find details about specific queries? (Choose all that apply.)
A. dm_io_virtual_file_stats
B. dm_exec_query_stats
C. dm_exec_connections
D. dm_exec_query_plan
E. dm_exec_sql_text
Correct Answer: BD Section: (none) Explanation
Explanation/Reference:
http://www.docstoc.com/docs/46104087/How-To-Determine-and-Identify-Resource-Consuming-Queries-in-SQL-Server-2005
http://msdn.microsoft.com/es-es/library/ms189747(v=sql.105).aspx
QUESTION 35
You troubleshoot a SQL Server 2008 instance.
You suspect that an application uses an inefficient locking strategy and causes concurrency problems.
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_os_wait_stats
B.
sys.dm_tran_active_transactions
C.
sys.dm_exec_requests
D.
sys.dm_os_waiting_tasks
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
http://msdn.microsoft.com/es-es/library/ms177648(v=sql.105).aspx
QUESTION 36
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 Management Studio do not exceed 20 percent of CPU utilization.
Queries initiated by the mission-critical applications are allowed to consume 100 percent utilization when required.
What should you do?
A. 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.
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 the mission-critical applications.
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 SQL Server Management Studio.
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 the mission-critical applications.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
First, you should create a new resource pool and set the MAX_CPU_PERCENT option to 20. Then assign this resource pool to the workload group used by SQL Server Management Studio.
Explanation: You can use Resource Governor in a variety of ways to monitor and manage the workloads on your SQL Server system. This topic provides an overview of how to configure Resource Governor and illustrates how Resource Governor can be used. The scenarios that are provided include Transact-SQL code examples for creating and changing workload groups and resource pools. Configuring Resource Governor After you install SQL Server 2008, Resource Governor is available for use but is not enabled. The internal and default workload groups and their corresponding resource pools exist. To create and use your own resource pools and workload groups, you must complete the following steps:
1.
Create a resource pool that has the limits you specify.
2.
Create a workload group that has the limits and policies that you specify, and identify the resource pool
to which the workload group belongs.
3.
Define and activate a classifier function that you want to use for incoming requests.
When the preceding steps are completed, you can see the active Resource Governor configuration and
the state of all active requests that are classified.
QUESTION 37
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:Memory Manager
C. SQLServer:Access Methods
D. SQLServer:Buffer Manager
Correct Answer: C Section: (none) Explanation Explanation/Reference:
The Access Methods object in SQL Server provides counters to monitor how the logical data within the database is accessed.
QUESTION 38
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 the SQL Server 2008 Upgrade Advisor
B. Use a SQL server-side trace that captures the Deprecation Announcement and Deprecation Final Support event classes.
C. Use the SQL Server Profiler that captures the SQL:BatchCompleted and Exception event classes.
D. Use a SQL server-side trace that captures the SQL:BatchCompleted and Exception event classes.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 39
You maintain a SQL Server 2008 instance.
You create a new SQL Agent job that includes a Windows Powershell job step. The Powershell job step uses the SQLCmd utility to transfer data between servers.
You need to ensure that an operator named ResponseTeam is notified by e-mail message if the job fails.
What should you do?
A.
Enable Notifications.
Create the ResponseTeam operator.
Direct the Notification to the ResponseTeam operator on failure of the Powershell job step.
B.
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.
C.
Enable Notifications.
Create the ResponseTeam operator.
Direct the Notification to the ResponseTeam operator on job failure.
D.
Create the ResponseTeam operator.
Assign the ResponseTeam operator as the fail-safe operator.
Enable the job.
Correct Answer: C 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 build of SQL Server that is deployed in your environment. What should you use to find the required information?
A. DBCC HELP
B. EXEC xp_msver
C. SELECT @@VERSION
D. SELECT * FROM sys.dm_os_sys_info
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
SELECT @@VERSION returns the edition as well as the build of SQL Server, eg:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 30 2011 19:25:40 Copyright (c)
Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 76XX: Service Pack 1)
EXEC xp_msver does not return an 'edition'.
QUESTION 41
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. Event notifications
B. Trace flag
C. SQL Server Profiler
D. Query notifications
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms175854(v=sql.105).aspx http://msdn.microsoft.com/en-us/library/ms130764(v=sql.100).aspx
QUESTION 42
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 43
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.
What failover option should you use?
A. Prevent automatic failback
B. Immediately allow failback
C. Allow failback only during business hours
D. Allow failback only during non-business hours
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 44
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.
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.
B.
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.
C.
Create mirroring endpoints on both partner servers.
Use the ALTER ENDPOINT statement along with the ENCRYPTION=DISABLED clause.
D.
Create mirroring endpoints on both partner servers.
On the principal, use the ALTER DATABASE statement along with the PARTNER SAFETY OFF clause.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
-- This is 'No witness mirroring, manual failover' (high performance).
http://msdn.microsoft.com/en-us/library/ms191456(v=sql.105).aspx
How Transaction Safety and Witness State Affect the Operating Mode
The operating mode of a session is determined by the combination of its transaction safety setting and the state of the witness. At any time, the database owner can change the transaction safety level, and can add or remove the witness.
Transaction Safety
Transaction safety is a mirroring-specific database property that determines whether a database mirroring session operates synchronously or asynchronously. There are two safety levels: FULL and OFF.
SAFETY FULL Full transaction safety causes the session to operate synchronously in high-safety mode. If a witness is present, a session supports automatic failover.
When you establish a session using ALTER DATABASE statements, the session begins with the SAFETY property set to FULL; that is, the session begins in high-safety mode. After the session begins, you can add a witness.
For more information, see Synchronous Database Mirroring (High-Safety Mode).
SAFETY OFF Turning off transaction safety causes the session to operate asynchronously, in high-performance mode. If the SAFETY property is set to OFF, the WITNESS property should also be set to OFF (the default). For information about the impact of the witness in high-performance mode, see "The State of the Witness," later in this topic. For more information about running with transaction safety turned off, see Asynchronous Database Mirroring (High-Performance Mode). The transaction safety setting of the database is recorded on each partner in the sys.database_mirroring catalog view in the mirroring_safety_level and mirroring_safety_level_desc columns. For more information, see sys.database_mirroring (Transact-SQL).
QUESTION 45
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 replicated.
What should you do?
A. Upload unsynchronized changes.
B. Reinitialize the publication and immediately generate a new snapshot.
C. Recreate the publication.
D. Upload unsynchronized changes, and then reinitialize the publication.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 46
You administer three SQL Server 2008 instances named Instance1, Instance2, and Instance3. Each of the three instances runs on a separate server. A mission-critical database is mirrored between Instance1 and Instance2. Instance3 is configured as the witness. Instance1 is configured as the Principal.
You plan to apply an update to the servers that run Instance1 and Instance2. 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.
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.
Remove the mirroring session.
Run the SQL Server 2008 Upgrade Advisor
Install the update on both servers.
Re-establish the mirroring session.
B.
Suspend the mirroring session
Install the update on both servers.
C.
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.
D.
Install the update on the server that runs Instance2.
Perform a manual failover of the mirroring session.
Install the update on the server that runs Instance1.
E.
Remove the mirroring session
Install the update on both servers.
Re-establish the mirroring session.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 47
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 full recovery model.
B. Verify that the database uses the checksum page_verify option
C. Verify that the database uses the bulk-logged recovery model.
D. Verify that the database uses the simple recovery model.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 48
You design a maintenance plan for a SQL Server 2008 instance that contains a database named SalesDB.
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
performance effect on the SalesDB database is minimized.
Which Transact-SQL statement should you execute?
A. DBCC SYS_CHECK (SalesDB);
B. DBCC CHECKDB (SalesDB);
C. DBCC SQLPERF (SalesDB);
D. DBCC REPAIRDB (SalesDB);
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
DBCC CHECKDB:
Checks the logical and physical integrity of all the objects in the specified database by performing the
following operations:
Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
Validates the Service Broker data in the database.
The DBCC offers two repair modes: REPAIR_REBUILD: This performs a repair that does not lead to any data loss. REPAIR_ALLOW_DATA_LOSS: This performs a repair and fixes to the corrupted database
structures, and also results in data loss.
DBCC SYS_CHECK:
Database system tables are checked during this phase.
DBCC SQLPERF:
Provides transaction log space usage statistics for all databases. It can also be used to reset wait and
latch statistics.
DBCC REPAIRDB: No such command/statement/clause.
QUESTION 49
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. CONTROL
B. DROP DATABASE
C. DELETE
D. ALTER ANY DATASPACE
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 50 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 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 backup taken on Sunday only.
B. Restore the backups taken on Sunday and Monday.
C. Restore the backup taken on Wednesday only.
D. Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.
E. Restore the backups taken on Wednesday and Thursday at 09:00 hours.
F. Restore the backups taken on Wednesday, Thursday, and Friday.
G. Restore the backups taken on Wednesday and Friday.
H. Restore the backup taken Wednesday, Friday, and Saturday.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 51 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 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 Monday at 09:00 hours.
You need to restore the backup.
What should you do?
A. Restore the backup taken on Sunday only.
B. Restore the backups taken on Sunday and Monday.
C. Restore the backup taken on Wednesday only.
D. Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.
E. Restore the backups taken on Wednesday and Thursday at 09:00 hours.
F. Restore the backups taken on Wednesday, Thursday, and Friday.
G. Restore the backups taken on Wednesday and Friday.
H. Restore the backup taken Wednesday, Friday, and Saturday.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 52 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 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 Sunday only.
B. Restore the backups taken on Sunday and Monday.
C. Restore the backup taken on Wednesday only.
D. Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.
E. Restore the backups taken on Wednesday and Thursday at 09:00 hours.
F. Restore the backups taken on Wednesday, Thursday, and Friday.
G. Restore the backups taken on Wednesday and Friday.
H. Restore the backup taken Wednesday, Friday, and Saturday.
Correct Answer: H Section: (none) Explanation
Explanation/Reference:
QUESTION 53
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 54
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 execute.
You need to configure the job step so that it can successfully complete execution.
What should you do?
A. Configure the SQL Server Agent service to use a local Windows account.
B. Configure a certificate.
C. Configure the job step to use a proxy account.
D. Configure the SQL Server Agent service to use the NetworkService account.
Correct Answer: C Section: (none)
Explanation Explanation/Reference:
QUESTION 55
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 information to the SQL Server error log.
What should you do?
A. Enable the Windows Event Collector service.
B. Enable file system auditing.
C. Configure a Data Collector Set and then restart the SQL Server instance.
D. Configure trace flags and then restart the SQL Server instance.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 56
You manage a SQL Server 2008 instance.
You need to verify whether or not a database integrity check (DBCC CHECKDB) was run for a particular
database.
Which log file should you examine?
A. ERRORLOG
B. log.trc
C. SQLAGENT
D. default.trc
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 57
A SQL Server login named Mary with the password "Me#43&21" 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 Server 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-SQL statements should you execute on Instance2?
A. USE AdventureWorks; ALTER LOGIN Mary ENABLE;
B. USE AdventureWorks;
ALTER LOGIN Mary WITH PASSWORD = "Me#43&21" UNLOCK;
C. USE AdventureWorks; ALTER USER Mary WITH LOGIN = Mary;
D. USE AdventureWorks; ALTER LOGIN Mary WITH DEFAULT_DATABASE = AdventureWorks;
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 58
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.
What should you do?
A.
Create a folder named AuditLogs on drive D.
Open the Windows Server Manager.
Browse to the Diagnostics\Event Viewer\Windows Logs folder
Right-Click Application Log, and then click Properties.
Change the Log path to D:\AuditLogs\Application.evtx.
B.
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.
C.
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.
D.
Open the Windows Server Manager.
Browse to the Diagnostic\Event Viewer\Windows Log folder.
Right-Click Application Log, and then click Properties.
Enable the Clear Log option.
Correct Answer: B Section: (none) Explanation Explanation/Reference:
QUESTION 59
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 instance.
Which Service should you start?
A. Server
B. SQL Active Directory Helper
C. SQL Server Agent
D. SQL Server Browser
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 60
You administer a SQL Server 2008 instance named SQL1. SQL1 contains a database named DB1.
You 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 named Reporting. The other objects in the DB1 database are owned by the dbo and are contained in a schema named Operations.
You need to grant the minimum necessary permissions to User1 to create tables and stored procedures in the Reporting schema. You 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: A Section: (none) Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/dd283095(v=sql.100).aspx
GRANT:---(...grant the minimum necessary permissions) ---Feedback: Chuck-
To create procedures, you must have CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created. For CLR stored procedures, you must either own the assembly referenced in , or have REFERENCES permission on that assembly.
DENY:---(...cannot alter any of the objects)
http://msdn.microsoft.com/en-us/library/ms176128.aspx
QUESTION 61
You administer a SQL Server 2008 instance named DB1. 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 Table1.
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 1
The 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. Execute the GRANT SELECT ON dbo.Table1 TO User1 Transact-SQL statement.
B. Execute the GRANT SELECT ON dbo.Procedure1 TO User1 Transact-SQL statement.
C. Alter Procedure1 and add the WITH EXECUTE AS OWNER option to its header.
D. Alter Procedure1 and add the EXECUTE AS USER = 'dbo' option immediately before the call to the sp_executesql procedure.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 62
You administer a SQL Server 2008 instance. You use an internal application based on SQL Server 2008. The application uses Analysis Services and Reporting Services.
Company security 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.
Create policies based on the appropriate facets.
Apply the policies against a server group that includes the database servers that need to be configured.
B.
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.
C.
Use the SQL Server Best Practices Analyzer (BPA) to analyze your database servers.
Implement the recommendations of the BPA.
D.
Edit the RSReportServer.config configuration file.
Distribute the file to all database servers that need to be configured.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 63
You administer a SQL Server 2008 instance that runs a Windows Server 2003 computer. That instance uses mixed authentication mode.
You need to ensure that the SQL Server 2008 authentication logins follow the same password complexity rules that are enforced by Windows Server 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. Create a SQL Server Agent job that runs periodically to raise an alert if a rule violation is detected.
B. Use Policy-Based Management to create a policy that prevents any violation of these rules.
C. Use the ALTER LOGIN ...CHECK_EXPIRATION = ON statement to modify all logins.
D. Use the ALTER LOGIN ... HASHED statement to modify all logins.
E. Use the ALTER LOGIN ...CHECK_POLICY = ON statement to modify all logins.
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
First, the alter login will change all the created logins to use the policy CHECK_POLICY = { ON | OFF }
Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.
Next, create one policy that prevents subsequent logins from being created without the option CHECK_POLICY = ON.
QUESTION 64
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. Make the CustomerDB database accessible only through stored procedures and functions.
B. Enable Transparent Database Encryption for both the CustomerDB database and the master database.
C. Enable Transparent Database Encryption for the CustomerDB database and back up the transaction log.
D. Encrypt the sensitive data at the cell level by using the built-in encryption functions.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 65
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 in the DB1 database.
A Windows 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. CREATE LOGIN "CONTOSO\User1" FROM Windows; DENY CONNECT SQL TO "CONTOSO\User1";
B. DROP LOGIN "CONTOSO\User1";
C. EXEC dbo.sp_droprolemember 'db_owner', 'CONTOSO\User1';
D. EXEC dbo.sp_revokedbaccess 'CONTOSO\User1'; EXEC dbo.sp_revokelogin 'CONTOSO\User1';
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms182763.aspx
Denying CONNECT SQL permission to a SQL Server login and principals to which the login has regranted it
The following example denies CONNECT SQL permission to the SQL Server login Annika and to the principals to which she has granted the permission.
USE master;
DENY CONNECT SQL TO Annika CASCADE;
GO
DROP LOGIN: Removes a SQL Server login account that pre-exists.
sp_denylogin (NOT CORRECT): Removes the login entries from SQL Server for a Windows user or group created by using CREATE LOGIN, sp_grantlogin, or sp_denylogin. HOWEVER -- Per MSDN: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
QUESTION 66
You administer a SQL Server 2008 instance that contains a very large database named FinanceDB.
You plan to create a maintenance plan that meets the following objectives for the FinanceDB database:
It executes the DBCC CHECKDB statement.
It rebuilds all the indexes.
It updates all the index statistics.
A.
Use the Rebuild Index task.
B.
Use the Update Statistics task.
C.
Use the Shrink Database task.
D.
Use the Reorganize Index task.
E.
Use the Check Database Integrity task.
You need to ensure that the maintenance plan is executed in the minimum amount of time.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose TWO.)
Correct Answer: AE Section: (none) Explanation
Explanation/Reference:
QUESTION 67
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 PAGE_VERIFY database option to CHECKSUM for the database.
B. Set the PAGE_VERIFY database option to TORN_PAGE_DETECTION for the model database.
C. Set the database compatibility level option to 10 for the database.
D. Turn on the TRUSTWORTHY database option for the database.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 68
You maintain a SQL Server instance.
You use the Resource Governor to manage processor resources on the server for a newly deployed application named NewApp. NewApp is assigned to a custom workload group named OurApp.
You need to monitor processor resources.
What should you do?
A. Use 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.
B. Use the request_max_cpu_time_sec column of the sys.resource_governor_workload_groupscatalog view.
C. Use the sys.dm_resource_workload_groups dynamic management view to monitor the CPU usage for the default workload group.
D. Use the sys.dm_os_performance_counters to monitor the CPU usage % counter of the SQLServer: Workload Group Stats performance object for the OurApp workload group.
Correct Answer: D Section: (none) Explanation Explanation/Reference:
QUESTION 69
You are the administrator of a SQL Server 2008 instance named Instance01.
Instance01 contains a database which is named SellingHelp. There isthe Products table in the SellingHelp database.
Now according to the company requirement, the Products table has to be replicated to the SQL Server instances installed in laptops computers. You have to create a Replication topology to perform this.
The Products table is updated from Instance01 when the laptops reconnect to the corporate network. The Products table on Instance01 is frequently updated between reconnections.
You have to make sure that you can implement the Replication topology along with the Subscription type successfully and meanwhile reduce the bandwidth usage to the least.
Which action should you perform to achieve this goal?
A. You should implement the Merge Replication topology along with a Pull Subscription.
B. You should implement the Snapshot Replication topology along with a Push Subscription.
C. You should implement the Transactional Replication topology along with a Pull Subscription.
D. You should implement the Snapshot Replication topology along with a Pull Subscription.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
Merge replication is typically used in server-to-client environments. Like transactional replication, typically, a merge replication starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.
QUESTION 70
You are the administrator of a SQL Server 2008 instance.
You make sure that all SQL Server instances are consistently configured for naming conventions, security settings, force index creation and avoidance of data fragmentation.
Which action should you perform to achieve this goal?
A. You should use the Database Engine Tuning Advisor.
B. In Microsoft SQL Server Management Studio, you should create a maintenance plan.
C. You should use the SQL Server Configuration Manager.
D. In Microsoft SQL Server Management Studio, you should create a policy.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
Policy-Based Management lets you monitor best practices for the SQL Server Database Engine.
QUESTION 71
You are the administrator in charge of two instances both of which run on the same computer. One is a SQL Server 2008 instance; another is a SQL Server 2005 instance.
There is a database named DB1 in the SQL Server 2008 instance. DB1 uses the Fulltext indexes. Several records that include the word ��root�� are added to DB1. An empty resultset is returned when the Fulltext index is queried for the word ��root.��
You have to make sure the query can return records that contain the word ��root��.
What should you do?
A. You should terminate and restart the MSFTESQL service.
B. You should rebuild the full-text index.
C. The word ��root�� should be added to the stop list.
D. The word ��root�� should be added to the thesaurus file.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 72
You are the administrator of a remote SQL Server 2008 instance.
You notice that the application has a poor performance. On the remote SQL Server instance, you capture a workload of the remote instance to a trace table by using SQL Profiler.
You have to use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local SQL Server instance.
Which action should you perform to achieve this goal?
A. You should enable the XP_MSVER stored procedure on the local server.
B. You should recapture the workload by using the data collector.
C. You should enable the XP_MSVER stored procedure on the remote server.
D. You should recapture the workload to a trace file by using SQL Profiler.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
You should recapture the workload to a trace file by using SQL Profiler.
Explanation by Tiamin:
In order to use the Database Engine Tuning Advisor, you need one trace file, here in the question, there is
a trace table available (on the remote server), which is not good (on the local server). We have then to
restart one trace to a file (on the local server).
QUESTION 73
You are the administrator of a SQL Server 2008 instance with a database named DB1.
DB1 has two filegroups: one is a filegroup named History that contains historical data stored in the G:
volume;
the other is the PRIMARY filegroup that contains the On-Line Transaction Processing (OLTP) data stored
in the F: volume.
The backup strategy of DB1 has three requirements:
The History filegroup should be backed up at the end of each working day;
The PRIMARY filegroup should be backed up five times a day;
The transaction log should be backed up every quarter.
You get a report at 13:00 saying that the volume which contains the History filegroup fails.
As the technical support, you have to restore the database, and you must reduce the data loss to the least as soon as possible.
What should you do?
A. You should restore the most recent backup of the History filegroup.
B. You should restore the most recent backup of the History filegroup. Restore all the transaction log backups after the most recent backup of the History filegroup
C. You should backup the transaction log. Then restore the most recent backup of the PRIMARY filegroup and restore the most recent backup of the History filegroup. After the most recent backup of the History filegroup, restore all the transaction log backups
D. You should backup the transaction log. Then restore the most recent backup of the History filegroup and restore all the transaction log backups after the most recent backup of the History filegroup.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 74
You are the administrator of a SQL Server 2008 instance with an On-Line Analytical Processing (OLAP) database.
The database contains a dimension table named Clients.
Ever hour backup of data of the Clients table is performed. But the Clients table contains redundant data.
You have to keep the disk space used to store the Clients table.
Which compression technology should you use?
A. You should use backup compression
B. You should use row compression
C. You should use page compression
D. You should use windows NTFS file system compression
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
Explanation:
Page Compression
This is a most vital compression method and is useful when there is a lot of frequently occurring data. Page compression allows common data to be shared between rows for a given page. Its uses the following techniques to compress data:
Row compression.
The Row Compression feature applies to zeros and null values and optimizes their space in SQL Server.
In fact, due to the Row Compression feature, SQL Server does not take any disk space for zero or null
values. For, all numeric datatypes (decimal, datetime, money, int etc), if they are storing zero or null values
in their rows, the Row Compression feature does not store them in the database at all. To apply row level compression the whole table needs to be REBUILDt with the Data_Compression = ROW clause enabled. The following command is used to rebuild a row compressed table:
ALTER TABLE REBUILD
WITH (DATA_COMPRESSION = ROW)
Prefix Compression.
For every column in a page duplicate prefixes are identified. These prefixes are saved in compression
information headers (CI) which resides after the page header. A reference number is assigned to these
prefixes and that reference number is replaced where ever those prefixes are being used.
Dictionary Compression.
Dictionary compression searches for duplicate values throughout the page and stores them in CI. The
main difference between prefix and dictionary compression is that 'prefix' is only restricted to one column
while dictionary is applicable to the complete page.
Because of the Prefix Compression and Dictionary Compression, duplicate values are shared between
rows in a given page... Which lead us to reduced disk space used.
Read the following about Considerations for When You Use Row and Page Compression:
http://msdn.microsoft.com/en-us/library/cc280449(v=sql.105).aspx
QUESTION 75
You are the administrator of a SQL Server 2008 instance.
A SQL Server Agent job is failing. You find that the job history information is incomplete and seems to be truncated after you review it.
All information produced by a job must be available for viewing.
Which action should you perform to achieve this goal?
A. You should enable notifications to the Windows application event log when the job completes.
B. You should enable all job steps, making them send the output to a file
C. You should enable write OEM file
D. You should include execution trace messages in the SQL Agent Error log
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Ans: You should enable all job steps, making them send the output to a file
Feedback:
The output file sets the file to use as the output from each job step.
For the job steps, in Management Studio, go to the option panel and select Output file to get all the info from the step. Note that you can append to or override the file; all job steps should be adapted with the option.
QUESTION 76
You are the administrator of a SQL Server 2008 instance.
There is a text file which is named SQL01.log on the F: drive. A SQL Server Agent job executes every 2 minutes and logs information to the text file.
You get a report from users saying that the sever does not respond.
As the technical support, you check and find that the SQL Server Agent service is not running and the SQL Server Agent job no longer functions.
You have to identify the reason why the SQL Server Agent service is unresponsive.
Which log should you check?
A. SQL1.log
B. SQLAGENT.OUT
C. log_xx.trc
D. ERRORLOG
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
You should check SQLAGENT.OUT log
Explanation:
All the information about SQL Server Agent errors can be found in the SQLAGENT.OUT file. The default in
my case is C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log
\SQLAGENT.OUT.
The primary interface to access the SQL Server Error Logs is via the Log File Viewer.
How do you access the Log File Viewer?
Launch SQL Server Management Studio.
Navigate to your SQL Server 2008 instance.
Expand the instance, then expand the Management folder followed by the SQL Server Logs folder.
Double click on any of the log files.
More info: http://www.mssqltips.com/sqlservertip/1021/sql-server-2005-log-file-viewer/
QUESTION 77
You are the administrator of a SQL Server 2008 instance.
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 Windows System Monitor tool
B. The Database Engine Tuning Advisor tool
C. The Activity Monitor tool in Microsoft SQL Server Management Studio
D. The Job Activity Monitor tool in Microsoft SQL Server Management Studio
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
Use Activity Monitor to obtain information about SQL Server processes and how these processes affect
the current instance of SQL Server.
Activity Monitor is a tabbed document window that has the following expandable and collapsible panes:
Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries. When
any pane is expanded, Activity Monitor is querying the instance for information. When a pane is collapsed,
all querying activity stops for that pane. You can also expand one or more panes at the same time to view
different kinds of activity on the instance.
http://msdn.microsoft.com/en-us/library/cc879320(v=sql.105).aspx
You can use the Job Activity Monitor in SQL Server Agent to view the current state of jobs. If the SQL Server Agent service unexpectedly terminates, you can refer to the sysjobactivity table to see which jobs were being executed when the service terminated.
http://msdn.microsoft.com/en-us/library/ms187449.aspx
QUESTION 78
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 79
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008.
You are the administrator of the company database. Now you are in charge of a SQL Server 2008
instance. There is a computer which hosts several applications in your company.
The instance runs on the computer. A job named DeliveryList is created by you. This job requires a file to
be written to a file server.
But because the job cannot access the file server, it fails to run. You intend to configure the SQL Server
Agent service so that only the SQL Server Agent service has read and write
access to the file server.
Which account type should you use?
A. You should use local Service account
B. You should use network Service account
C. You should use domain account
D. You should use local System account
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
In order to access file shares, the account that runs the job should have access rights to the remote object.
In this case, the user that runs the job is "sa". As it is "sa", the real user that runs the job is SQL Server
Agent user.
So, use one domain account for the SQL Server Agent and grant the correct rights to this user on the file
server.
QUESTION 80
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.
On DB1, quite few logged operations are performed.
Now according to the requirement of the company CIO, you must validate that the database can be
restored to a specific point in time.
So what action should you perform to achieve this goal?
A. You should verify that the simple recovery model is used by the database
B. You should verify that the full recovery model is used by the database.
C. You should verify that the checksum page verify option is used by the database
D. You should verify that the bulk-logged recovery model is used by the database
Correct Answer: B Section: (none) Explanation
Explanation/Reference: Full recovery model
Provides the normal database maintenance model for databases where durability of transactions is necessary. Log backups are required. This model fully logs all transactions and retains the transaction log records until after they are backed up. The full recovery model allows a database to be recovered to the point of failure, assuming that the tail of the log can be backed up after the failure. The full recovery model also supports restoring individual data pages.
QUESTION 81
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a SQL Server 7.0 database. You upgrade the database to the SQL Server instance.
According to the company requirement, you must make sure that suspect pages can be detected in the
database.
So what action should you perform to achieve this goal?
A. For the database, you should turn on the TRUSTWORTHY database option
B. For the database, the PAGE_VERIFY database option should be set to CHECKSUM
C. For the database, the database compatibility level option should be set to 10.
D. For the model database, the PAGE_VERIFY database option should be set to TORN_PAGE_DETECTION.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
When CHECKSUM is specified, the Database Engine calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. If the values do not match, error message 824 (indicating a checksum failure) is reported to both the SQL Server error log and the Windows event log. A checksum failure indicates an I/O path problem. To determine the root cause requires investigation of the hardware, firmware drivers, BIOS, filter drivers (such as virus software), and other I/O path components.
QUESTION 82
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. You configure the SQL Server instance to use the -
T1222 and -T1204 trace flags during startup.
You must make sure that your failure recovery plan performs backup of the use of the trace flags.
So what action should you perform to achieve this goal?
A. You should backup the master database
B. You should backup the default.trc file
C. You should backup the SQL Server registry hive.
D. You should backup the resource database
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
SQL Server Configuration Manager writes startup parameters to the registry. They take effect upon
the next startup of the Database Engine.
You can override the default startup options temporarily and start an instance of SQL Server by using the
following additional startup options.
-T trace#
Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect.
Trace flags are used to start the server with nonstandard behavior.
QUESTION 83
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. According to the company requirement, the names
of all user-defined stored procedures must contain the prefix usp_ on all instances.
Besides this, you must make sure that stored procedures that do not contain this prefix cannot be created
by you.
What should you do?
A. A policy should be created. The policy targets the name of the stored procedure that is evaluated on demand.
B. A policy should be created. The policy targets the name of the stored procedure that is evaluated on change.
C. A condition should be created. The condition targets the name of the stored procedure that is evaluated on change
D. A condition should be created. The condition targets the name of stored procedure that is evaluated on demand.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Policy-Based Management is a system for managing one or more instances of SQL Server 2008. When
SQL Server policy administrators use Policy-Based Management, they use SQL Server Management
Studio to create policies to manage entities on the server, such as the instance of SQL Server, databases,
or other SQL Server objects.
Policy-Based Management has three components:
Policy management
Policy administrators create policies.
Explicit administration
Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.
Evaluation modes
There are four evaluation modes, three of which can be automated:
On demand. This mode evaluates the policy when directly specified by the user.
On change: prevent. This automated mode uses DDL triggers to prevent policy violations.
Important:
If the nested triggers server configuration option is disabled, On change: prevent will not work
correctly. Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that
do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL
triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.
On change: log only. This automated mode uses event notification to evaluate a policy when a relevant
change is made.
On schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy.
When automated policies are not enabled, Policy-Based Management will not affect system performance.
QUESTION 84
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is an Internet Information Services
application.
This application will use anonymous access to access the SSAS instance. You must make sure that the
application can access the SSAS instance.
What should you do?
A. The Security\RequireClientAuthentication server configuration should be set to False
B. The Security\RequireClientAuthentication server configuration should be set to True.
C. The NTLM Security Support Provider Interface (SSPI) provider should be added to the Security \SecurityPackageList server configuration.
D. The Kerberos Security Support Provider Interface (SSPI) provider should be added to the Security \SecurityPackageList server configuration
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
Microsoft SQL Server Analysis Services supports the security server properties listed in the following table. To view or change these properties for an instance of Analysis Services, in SQL Server Management Studio, right-click the Analysis Services instance, and then click Properties. Properties RequireClientAuthentication
A Boolean property that indicates whether client authentication is required. The default value for this property is True, which indicates that client authentication is required. In this case, the authentication should be anonymous, thus the property should be set to False
QUESTION 85
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now for a new application, a SQL Server 2008 instance has to be installed on an existing server.
The server contains a default SQL Server 2005 instance. Your company CIO wants their corresponding
certified third-party applications can access to both database instances.
The company assigns this task to you. You must achieve this by using as little database administrative
effort as possible, and never change the existing application
environments.
What should you do?
A. You should install SQL Server 2008 as the default instance, and configure the new application to use the default instance
B. You should upgrade the SQL Server 2005 application to use SQL Server 2008
C. You should upgrade the SQL Server 2005 instance to a SQL Server 2008 instance
D. You should install SQL Server 2008 as a named instance, and make the new application use the new instance.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
SQL Server supports multiple instances of the Database Engine, Analysis Services, and Reporting
Services on the same computer. You can also upgrade earlier versions of SQL Server, or install SQL
Server on a computer where earlier SQL Server versions are already installed.
If a default instance already exists on the computer, SQL Server must be installed as a named instance.
The following table shows side-by-side support for SQL Server 2008:
Existing instance of SQL Server 2008 Side-by-side support
SQL Server 2008 (32-bit) SQL Server 2000 (32-bit) SQL Server 2000 (64-bit) x64 SQL Server 2005 (32-bit) SQL Server 2005 (64-bit) x64 SQL Server 2008 (64-bit) IA64 SQL Server 2005 (64-bit) IA64 SQL Server 2008 (64-bit) x64 SQL Server 2000 (32-bit) SQL Server 2000 (64-bit) x64 SQL Server 2005 (32-bit) SQL Server 2005 (64-bit) x64
In this case, there is already one default instance SQL Server 2005, thus, we have to install one named instance.
QUESTION 86
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. Now your company CIO assigns a task to you.
The company CIO wants you to configure FILESTREAM data and the two requirements below must be
met: you must enable FILESTREAM for file I/O streaming access; remote client computers must be able to
have streaming access to FILESTREAM data.
You must make sure that FILESTREAM data is enabled.
Which service should you configure?
A. You should configure SQL Server VSS Writer
B. You should configure Distributed File System
C. You should configure SQL Server
D. You should configure SQL Server Full Text
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL
Server Database Engine.
This topic describes how to enable FILESTREAM by using SQL Server Configuration Manager.
To enable and change FILESTREAM settings
On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to
Configuration Tools, and then click SQL Server Configuration Manager.
In the list of services, right-click SQL Server Services, and then click Open.
In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you
want to enable FILESTREAM.
Right-click the instance, and then click Properties.
In the SQL Server Properties dialog box, click the FILESTREAM tab.
Select the Enable FILESTREAM for Transact-SQL access check box.
If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O
streaming access. Enter the name of the Windows share in the Windows Share Name box.
If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote
clients to have streaming access to FILESTREAM data.
Click Apply.
In SQL Server Management Studio, click New Query to display the Query Editor.
In Query Editor, enter the following Transact-SQL code:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
QUESTION 87
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of two instances both of which run on the same computer. One is a SQL Server
2008 instance; another is a SQL Server 2005 instance.
There is a database named DB1 in the SQL Server 2008 instance. DB1 uses the Fulltext indexes. Several
records that include the word "root" are added to DB1. An empty resultset is returned when the Fulltext
index is queried for the word "root."
You must make sure the query can return records that contain the word "root".
What should you do?
A. You should terminate and restart the MSFTESQL service
B. You should rebuild the full-text index
C. The word "root" should be added to the stop list
D. The word "root" should be added to the thesaurus file
Correct Answer: B Section: (none) Explanation
Explanation/Reference: Updating a Full-Text Index
Like regular SQL Server indexes, full-text indexes can be automatically updated as data is modified in the associated tables. This is the default behavior. Alternatively, you can keep your full-text indexes up-to-date manually or at specified scheduled intervals. Populating a full-text index can be time-consuming and resource-intensive, therefore, index updating is usually performed as an asynchronous process that runs in the background and keeps the full-text index up to date after modifications in the base table. Updating a full-text index immediately after each change in the base table can be resource-intensive. Therefore, if you have a very high update/insert/delete rate, you might experience some degradation in query performance. If this occurs, consider scheduling manual change tracking updates to keep up with the numerous changes from time to time, rather than competing with queries for resources. To monitor the population status, use either the FULLTEXTCATALOGPROPERTY or OBJECTPROPERTYEX functions. To get the catalog population status, run the following statement: SELECT FULLTEXTCATALOGPROPERTY('AdvWksDocFTCat', 'Populatestatus'); Typically, if a full population is in progress, the result returned is 1.
QUESTION 88
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. You must make sure that all SQL Server instances are consistently configured for naming conventions, security settings, force index creation and avoidance of data fragmentation.
So what action should you perform to achieve this goal?
A. You should use the Database Engine Tuning Advisor.
B. In Microsoft SQL Server Management Studio, you should create a maintenance plan
C. You should use the SQL Server Configuration Manager
D. In Microsoft SQL Server Management Studio, you should create a policy
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
Policy-Based Management lets you monitor best practices for the SQL Server Database Engine. SQL Server 2008 provides a set of policy files that you can import as best practice policies, and then evaluate the policies against a target set that includes instances, instance objects, databases, or database objects. In parallel, you can create your own set of policy to inforce configuration, security settings and more. You can evaluate policies manually, set policies to evaluate a target set according to a schedule, or set policies to evaluate a target set according to an event.
QUESTION 89
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named Dworks in the
instance.
The Dworks database has a table named Orderthings. The Orderthings table is partitioned on the OrderId
column. The first partition contains integer values greater than 100,000, while the second partition contains
integer values between 1 and 100,000.
You have to add a new partition. The new partition should contain integer values greater than 200,000.
What should you do?
A. A new partition function should be created
B. You should change the existing partition scheme
C. You should use a Merge clause to change the existing partition function
D. You should use a Split clause to change the existing partition function
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
Alters a partition function by splitting or merging its boundary values. By executing ALTER PARTITION FUNCTION, one partition of any table or index that uses the partition function can be split into two partitions, or two partitions can be merged into one less partition. ALTER PARTITION FUNCTION partition_function_name() { SPLIT RANGE ( boundary_value ) | MERGE RANGE ( boundary_value ) }[;] SPLIT RANGE ( boundary_value ) Adds one partition to the partition function. boundary_value determines the range of the new partition, and must differ from the existing boundary ranges of the partition function. Based on boundary_value, the Database Engine splits one of the existing ranges into two. Of these two, the one where the new boundary_value resides is considered the new partition. Important: A filegroup must exist online and be marked by the partition scheme that uses the partition function as NEXT USED to hold the new partition. Filegroups are allocated to partitions in a CREATE PARTITION SCHEME statement. If a CREATE PARTITION SCHEME statement allocates more filegroups than necessary (fewer partitions are created in the CREATE PARTITION FUNCTION statement than filegroups to hold them), then there are unassigned filegroups, and one of them is marked NEXT USED by the partition scheme. This filegroup will hold the new partition. If there are no filegroups marked NEXT USED by the partition scheme, you must use ALTER PARTITION SCHEME to either add a filegroup, or designate an existing one, to hold the new partition. A filegroup that already holds partitions can be designated to hold additional partitions. Because a partition function can participate in more than one partition scheme, all the partition schemes that use the partition function to which you are adding partitions must have a NEXT USED filegroup. Otherwise, ALTER PARTITION FUNCTION fails with an error that displays the partition scheme or schemes that lack a NEXT USED filegroup.
QUESTION 90
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named Dworks in the
instance.
Now you get a report from users saying that DB1 meets deadlock problems. As the technical support, you
have to capture the deadlock information to the SQL Server error log.
What should you do?
A. For the AdventureWorks database, enable Server Auditing
B. First you should set the appropriate trace flags as a startup parameter, and then restart the SQL Server instance
C. You should configure the data collector and make it capture the deadlock graphs
D. You should configure a SQL Profiler trace, and make it capture the deadlock graphs
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
To identify a deadlock, you must first obtain log information. If you suspect a deadlock, you must gather information about the (SPIDs) and the resources that are involved in the deadlock. To do this, add the -T1204 and the -T3605 startup parameters to SQL Server. The startup parameters will take effect when SQL Server is stopped and then re-started. The -T1204 startup parameter collects information about the process and the resources when the deadlock detection algorithm encounters a deadlock. The -T3605 startup parameter writes this information to the SQL Server error logs.
If you do use the -T1205 startup parameter, the following is a sample of the output that will be in the SQL Server error log: 2003-05-14 11:46:26.76 spid4 Starting deadlock search 1 2003-05-14 11:46:26.76 spid4 Target Resource Owner: 2003-05-14 11:46:26.76 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340 2003-05-14 11:46:26.76 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340 2003-05-14 11:46:26.76 spid4 2003-05-14 11:46:26.76 spid4 End deadlock search 1 ... a deadlock was not found. 2003-05-14 11:46:26.76 spid4 ----------------------------------2003-05-14 11:46:31.76 spid4 ----------------------------------2003-05-14 11:46:31.76 spid4 Starting deadlock search 2 SOURCE: http://support.microsoft.com/kb/832524/en-us
QUESTION 91
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named Dworks in the
instance.
The Dworks database has a table named Orderthings. According to the company requirement, you have to
export all data from the Orderthings table to a file.
During the export, you must make sure that the data export process is saved for reuse and a Microsoft
Office Open XML document format is used.
What should you do?
A. You should run the bulk copy program utility along with an output file and no format file
B. You should run the SQLCmd utility and save the output to a file
C. You should run the SQL Import and Export Data Wizard and save the output to a file.
D. You should run the bulk copy program utility along with a format file and an output file.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
The SQL Server Import and Export Wizard provides the simplest method of copying data between data sources and of constructing basic packages.
Save and run a package.
If the wizard is started from SQL Server Management Studio or the command prompt, the package can run
immediately. You can optionally save the package to the SQL Server msdb database or to the file system.
For more information about the msdb database, see Managing Packages.
When you save the package you can set the package protection level, and if the protection level uses a
password, provide the password.
QUESTION 92
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database which is named Sellings in the
instance.
The Sellings database contains a table named Productions. The table is used to stores information about
all types of products.
The Productions table is often queried by users on the basis of the RadioSize column. The RadioSize
column contains the NULL value for all products other than Radios. Currently no index exists on the
RadionSize column.
According to the requirement of the company CIO, you have to optimize the query performance and
reduce the effect on the disk space to the least.
So what action should you perform to achieve this goal?
A. On the Products table, you should create a view.
B. On the RadioSize column, you should create a clustered index
C. On the RadioSize column, you should create a filtered index
D. On the RadioSize column, you should create a unique clustered index
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. Filtered indexes can provide the following advantages over full-table indexes:
Improved query performance and plan quality Reduced index maintenance costs Reduced index storage costs
Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.
QUESTION 93
You work in a Organization which is named Wiikigo Corp. The Organization uses SQL Server 2008. You
are the administrator of the Organization database.
Now you are in charge of a SQL Server 2008 instance.
Look at the following query:
SELECT s.*, i.*
FROM SensitiveTbl AS s
INNER JOIN InsensitiveTbl AS i
ON i.OrganizationName = s.OrganizationName
You use the above query to join two tables on a column named OrganizationName.
The following error is returned when you execute the query.
"Msg 468, Level 16, State 9, Line 17 Cannot resolve the collation conflict between
'SQL_Latin1_General_CP1_CS_AS' and 'SQL_Latin1_General_CP1_CI_AS' in the equal to operation."
The ON clause of the query has to be modified so that it can perform a case-sensitive joinsuccessfully.
What should you do?
A. ON LOWER(i.OrganizationName) = LOWER(s.OrganizationName)
B. ON UPPER(i.OrganizationName) = UPPER(s.OrganizationName)
C. ON i.OrganizationName = s.OrganizationName COLLATE SQL_Latin1_General_CP1_CS_AS
D. ON i.OrganizationName = s.OrganizationName COLLATE SQL_Latin1_General_CP1_CI_AS
Correct Answer: C Section: (none) Explanation Explanation/Reference:
COLLATE is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast. Casting the collation of an expression. You can use the COLLATE clause to cast a character expression to a certain collation. Character literals and variables are assigned the default collation of the current database. Column references are assigned the definition collation of the column. For the collation of an expression, see Collation Precedence.
Here, one table is Case Sensitive and the other is Case Insensitive. You should use the most "restrictive" collation (SQL_Latin1_General_CP1_CS_AS) to make the comparison or you should have the same error message
QUESTION 94
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named OrderIn in the
instance. The OrderIn database contains a table which is named OrdeSend.
A column named SendSite which is of the Geography data type has been added to the OrdeSend. The
OrderSend table contains no indexes.
On the SentSite point, you have to create a spatial index.
What should you do first?
A. You must make sure that the SendSite column does not allow NULL values.
B. You should define a primary key for the OrderSend table
C. For the OrderSend table, you should create a clustered index
D. First, you should copy the OrderSend data to a temporary table, then you should truncate the existing OrderSend table.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Restrictions on Spatial Indexes
A spatial index can be created only on a column of type geometry or geography.
Spatial indexes can be defined only on a table that has a primary key. The maximum
number of primary key columns on the table is 15.
The maximum size of index key records is 895 bytes. Larger sizes raise an error.
Note:
Primary key metadata cannot be changed while a spatial index is defined on a table. Spatial Indexes cannot be specified on indexed views.
You can create up to 249 spatial indexes on any of the spatial columns in a supported table. Creating
more than one spatial index on the same spatial column can be useful, for example, to index different
tessellation parameters in a single column.
You can create only one spatial index at a time. For more information, see CREATE SPATIAL INDEX
(Transact-SQL) or How to: Create a Spatial Index (SQL Server Management Studio).
An index build cannot make use of available process parallelism.
QUESTION 95
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. The server has a very large database named DB1.
An application which is constantly available uses DB1.
Now you get a report from users, in the report users complain that server has a poor performance. As the
technical support, you have to improve the performance of the application by using the Database Engine
Tuning Advisor.
Besides this, you must make sure that the action of analyzing the workload will not affect the performance
of the production server.
So what should you do?
A. On the local server, enable the XP_MSVER stored procedure
B. On the remote server, enable the XP_MSVER stored procedure.
C. On the production server, use the dta.exe utility along with an XML input file.
D. You should configure a test server that has a similar hardware configuration. Use the dta.exe utility on the test server along with an XML input file
Correct Answer: D Section: (none) Explanation
Explanation/Reference: The dta utility provides a command prompt executable file that you can use to tune databases. It enables you to use Database Engine Tuning Advisor functionality in batch files and scripts. The dta utility takes trace files, trace tables, and Transact-SQL scripts as workloads. It also takes XML input that conforms to the Database Engine Tuning Advisor XML schema, which is available at this Microsoft Web site. Consider the following before you begin tuning a workload with the dta utility: When using a trace table as a workload, that table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, then move it to the server that Database Engine Tuning Advisor is tuning. Make sure that tracing has stopped before using a trace table as a workload for Database Engine Tuning Advisor. Database Engine Tuning Advisor does not support using a trace table to which trace events are still being written as a workload. If a tuning session continues running longer than you had anticipated it would run, you can press CTRL +C to stop the tuning session and generate recommendations based on the analysis dta has completed up to this point. You will be prompted to decide whether you want to generate recommendations or not. Press CTRL+C again to stop the tuning session without generating recommendations. Here, we don't want to affect the production server. Thus, we will run the dat.exe utility on the test server.
QUESTION 96
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. Today you notice that applications that run on the
server have poor performances. You doubt that this has something to do with table scans.
You have to capture the appropriate information by using an appropriate Windows System Monitor object.
In the options below, which performance object should you use?
A. You should use SQLServer:Buffer Manager
B. You should use SQLServer:Memory Manager
C. You should use SQLServer:Databases
D. You should use SQLServer:Access Methods
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
SQL Server, Access Methods Object The Access Methods object in SQL Server provides counters to monitor how the logical data within the database is accessed. SQL Server Access Methods counters
Full Scans/sec
Number of unrestricted full scans per second. These can be either base-table or full-index scans.
QUESTION 97
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. According to the company requirement, you
migrate an application from Microsoft SQL Server 2000 to Microsoft SQL Server 2008.
You have to monitor the SQL Server instance, and record the use of features which will be discontinued. What should you do?
A. The SQL Server 2008 Upgrade Advisor should be used
B. You should use a SQL server-side trace. The trace captures the Deprecation Announcement and Deprecation Final Support event classes
C. You should use the SQL Server Profiler. The profiler captures the SQL:BatchCompleted and Exception event classes
D. You should use a SQL server-side trace. The trace captures the SQL:BatchCompleted and Exception event classes.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
The Deprecation Final Support event class occurs when you use a feature that will be removed from the
next major release of SQL Server. For greatest longevity of your applications, do not use features that
cause the Deprecation Final Support event class or the Deprecation Announcement event class.
Modify applications that use final deprecation features as soon as possible.
QUESTION 98
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. The server hosts databases for several missioncritical
applications.
Microsoft SQL Server Management Studio executes queries and it has some effect. Now you intend to
limit the effect by using the Resource Governor.
You must make sure that queries initiated through SQL Server Management Studio is less than 20 percent
of CPU utilization, besides this, you must make sure that queries initiated by the mission-critical
applications can consume 100 percent of CPU utilization when required.
So what action should you perform to achieve this goal?
A. You should alter the default resource pool and set the MAX_CPU_PERCENT option to 80. Then assign this resource pool to the workload group used by SQL Server Management Studio.
B. First, you should create a new resource pool and set the MAX_CPU_PERCENT option to 20. Then assign this resource pool to the workload group used by SQL Server Management Studio.
C. You should alter the default resource pool and set the MAX_CPU_PERCENT option to 20. Then assign this resource pool to the workload group used by the mission-critical applications.
D. First, you should create a new resource pool and set the MAX_CPU_PERCENT option to 80. Then assign this resource pool to the workload group used by the mission-critical applications.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
You can use Resource Governor in a variety of ways to monitor and manage the workloads on your SQL Server system. This topic provides an overview of how to configure Resource Governor and illustrates how Resource Governor can be used. The scenarios that are provided include Transact-SQL code examples for creating and changing workload groups and resource pools.
Configuring Resource Governor
After you install SQL Server 2008, Resource Governor is available for use but is not enabled. The internal
and default workload groups and their corresponding resource pools exist.
To create and use your own resource pools and workload groups, you must complete the following steps:
1.
Create a resource pool that has the limits you specify.
2.
Create a workload group that has the limits and policies that you specify, and identify the resource pool
to which the workload group belongs.
3.
Define and activate a classifier function that you want to use for incoming requests.
When the preceding steps are completed, you can see the active Resource Governor configuration and
the state of all active requests that are classified.
QUESTION 99
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. You are going to use the data collector to gather
performance data periodically on all instances.
You must store all collected data in the same database. This database is hosted on a single instance.
Every five hours, you have to collect and load performance data in the management data warehouse.
Which data collection process should you implement?
A. You should create a cached data collection
B. You should create an on-demand non-cached data collection
C. You should create a scheduled non-cached data collection.
D. You should create two different SQL Agent jobs. The two jobs are scheduled at the same time. One job uploads the data collection and the other job creates a data collection.
Correct Answer: C Section: (none) Explanation Explanation/Reference:
Data collection and upload
Specifies how data is collected and uploaded to the management data warehouse. Pick one of the
following options.
Non-cached. Collection and data upload on the same schedule.
When selected, specify one of the following:
On-demand. Data is collected and uploaded on demand.
Schedule. Data is collected and uploaded according to a schedule. Click Pick to select from a predefined list of schedules, or click New to create a new schedule.
QUESTION 100
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. Now according to the company requirement, you
are designing a consolidated repository of performance data.
You must make sure that the four requirements below are met:
1.
the data collector is used to gather performance information
2.
a single database stores performance information for all instances
3.
performance information that is older than 15 days is deleted
4.
reduce the administrative effort to manage performance to the least. So what action should you perform to achieve this goal?
A. You should create a SQL Agent job process on each instance to store and delete performance data in a single database for all instances.
B. You should configure a management data warehouse process on each instance, then use this process to store and delete performance data in a single database for all instances.
C. You should configure an automated server-side trace process on each instance, then use this process to store and delete performance data in a single database for all instances.
D. You should create and schedule a single Microsoft SQL Service Integration Services (SSIS) package process, then use this process to store and delete performance data in a single database for all instances.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
The data collector is a component installed on a SQL Server server, running all the time or on a userdefined schedule, and collecting different sets of data. The data collector then stores the collected data in a relational database (solve point 2) known as the management data warehouse.
The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. (solve point 4) This collection point can obtain data from a variety of sources and is not limited solely to performance data (solve point 1), unlike SQL Trace. The data collector enables you to adjust the scope of data collection to suit your test and production environments. The data collector also uses a data warehouse, a relational database that enables you to manage the data you collect by setting different retention periods for your data (solve point 3). The data collector supports dynamic tuning for data collection and is extensible through its API.
QUESTION 101
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a report which is often executed during
business time. There is a stored procedure that is used as the data source for the report.
You get a report from users saying that they receive the data returned by the report but the data is
inconsistent. As the technical support, you check and find that phantom reads cause this problem.
You must make sure that consistent data is returned by the report while not affecting other users.
So what action should you perform to achieve this goal?
A. You should configure the database for Read Committed Snapshot isolation.
B. You should modify the stored procedure to use the Snapshot isolation level
C. You should configure the database for Auto Update Statistics asynchronously
D. You should modify the stored procedure to use the Repeatable Read isolation level
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Phantom Reads occur when one transaction accesses a range of data more than once and a second
transaction inserts or deletes rows that fall within that range between the first transaction's read attempts.
This can cause "phantom" rows to appear or disappear from the first transaction's perspective.
SNAPSHOT
Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.
Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data. During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted. The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database. A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts the first time it accesses data. A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.
QUESTION 102
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. On two cluster-ready nodes, you intend to perform
an installation of a SQL Server 2008 mission-critical cluster. Constant availability is needed by the
missioncritical
cluster.
You have to configure the SQL Server cluster to failover and reduce the service disruption to the least.
Which failover option should you use?
A. You should allow failback only during non-business hours
B. You should immediately allow failback
C. You should prevent automatic failback
D. You should allow failback only during business hours
Correct Answer: C Section: (none) Explanation
Explanation/Reference: Failover/Failback Strategies
An overall cluster failover/failback policy is recommended. Failovers can be controlled in terms of a threshold, meaning that after a certain point, a resource will not be failed over. There are two levels of thresholds: resource and cluster. Depending on how the resource is configured, it can affect the group failing over to another node. In the event of a failover, the cluster group containing the SQL Server resources can be configured to fail back to the primary node when and if it becomes available again. By default, this option is set to off because usually there is no problem with continuing on the secondary node. This setting provides an opportunity to analyze and repair the problem on the failed node. It will also minimize the service disruption.
QUESTION 103
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named Dworks in the
instance. You are log shipping the Adventureworks database to a remote SQL Server 2008 instance.
The primary SQL Server instance required a restart during the weekend. But you notice that log shipping
has stopped working after the weekend. You have to check log shipping of the Adventureworks database
and find out the problem.
What should you do?
A. You should use the EXTENDED_LOGICAL_CHECKS option to execute a DBCC CHECKDB statement on the AdventureWorks database by.
B. You should identify whether the SQL Server Agent is started on the primary server.
C. You should identify whether the AdventureWorks database uses the Simple recovery model.
D. You should identify whether the SQL Server Volume Shadow Copy Service (VSS) Writer is started on the primary server
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
The logshipping is base on a set of SQL Server Agent jobs.
One on the primary: Bckup transaction log
Two on the secondary: Copy the transaction backup and Restore the transaction log.
So, verify the SQL Server Agent Service state.
QUESTION 104
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named Dworks in the
instance. The Dworks database has the Products table.
Now you have to replicate the Products table to the SQL Server instances at remote locations, so you
create a Merge Replication topology and a Publication to do this.
The Publication has a 21-day retention period. There is a user who goes on a business trip, 30 days later
he comes back and finds that data in his database is not the latest.
The Windows Event log states the following error message:
"Replication: expired subscription dropped."
You must get the latest data in the user's database and make sure that future data changes are
appropriately replicated.
So what action should you perform to achieve this goal?
A. You should recreate the publication
B. You should upload unsynchronized changes.
C. You should reinitialize the publication and generate a new snapshot at once.
D. You should upload unsynchronized changes, and then reinitialize the publication
Correct Answer: C Section: (none) Explanation
Explanation/Reference: Merge replication uses the publication retention period (the @retention and @retention_period_unit parameters of sp_addmergepublication (Transact-SQL)). When a subscription expires, it must be reinitialized, because metadata for the subscription is removed. Subscriptions that are not reinitialized are dropped by the Expired subscription clean up job that runs on the Publisher. By default, this job runs daily; it removes all push subscriptions that have not synchronized for double the length of the publication retention period. For example: If a publication has a retention period of 14 days, a subscription can expire if it has not synchronized within 14 days. If the Publisher is running SQL Server 2005 or a later version and the agent for the subscription is from SQL Server 2005 or a later version, a subscription only expires if there have been changes to the data in that subscription's partition. For example, suppose a Subscriber receives customer data only for customers in Germany. If the retention period is set to 14 days, the subscription expires on day 14 only if there have been changes to the German customer data in the last 14 days. From 14 days to 27 days after the last synchronization, the subscription can be reinitialized. At 28 days after the last synchronization, the subscription is dropped by the Expired subscription clean up job. If a push subscription expires, it is completely removed, but pull subscriptions are not. You must clean up pull subscriptions at the Subscriber. For more information, see How to: Delete a Pull Subscription (Replication Transact-SQL Programming).
QUESTION 105
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now in a high security environment, you manage a SQL Server 2008 cluster. You intend to configure and
use encrypted connections for the clustered virtual SQL Server.
You have to install a certificate. The cetification will be used for encryption.
What should you do?
A. In the cluster quorum drive, install the encryption certificate
B. In the SQL Server shared disk, install the encryption certificate
C. in the cluster group, install the encryption certificate.
D. On each individual node, install the encryption certificate
Correct Answer: D Section: (none) Explanation
Explanation/Reference: Encryption on a Cluster
If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named test1.your company. com and test2. your company.com and a failover clustered instance of SQL Server named fcisql, you must obtain a certificate for fcisql.your company.com and install the certificate on both nodes. To configure the failover cluster for encryption, you can then select the ForceEncryption check box on the Protocols for property box of SQL Server Network Configuration.
QUESTION 106
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.
The company stores clients data in DB1. Clients can access their profile data by using a Web application.
You must ensure the security of the customer data, you must ensure that even if the backup media is lost,
data files, log files and subsequent backups and so on are quite safe.
So what action should you perform to achieve this goal?
A. For both the CustomerDB database and the master database, enable Transparent Database Encryption.
B. You should use the built-in encryption functions to encrypt the sensitive data at the cell level.
C. You should make the CustomerDB database accessible only through stored procedures and functions.
D. For the CustomerDB database, enable Transparent Database Encryption and back up the transaction log.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.
QUESTION 107
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is an internal application which uses
Analysis Services and Reporting Services. You use the application on the basis of SQL Server 2008.
According to the requirement of company security policy, the surface area for all the deployed components
of SQL Server 2008 should be configured. You have to implement the security policy and devise a method
to evaluate the security policy against other database servers.
What should you do?
A. You should create policies based on the appropriate facets. Apply the policies against a server group. The server group includes the database servers that need to be configured
B. You should 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
C. You should edit the RSReportServer.config configuration file. Distribute the file to all database servers that need to be configured D. You should analyze your database servers by using the SQL Server Best Practices Analyzer (BPA). Implement the recommendations of the BPA
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
The Enterprise Policy Management (EPM) Framework leverages and extends the new Microsoft SQL
Server 2008 Policy-Based Management feature across an entire SQL Server enterprise, including downlevel
instances of SQL Server such as SQL Server 2000 and SQL Server 2005.
In addition, the EPM Framework can be used to:
Automate the evaluation of policies against a defined set of SQL Server instances, including SQL
Server 2000 and SQL Server 2005.
Centralize the policy evaluation history to a single source for enterprise policy reporting.
Define best practices for implementing policy evaluation in extremely large enterprise environments.
In order to manage multiple servers, we have the ability in SQL Server 2008 to create server groups and
apply the policies to this groups
QUESTION 108
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named Service in the
instance. There is a database user who is named ServiceGreatUser.
A SQL Server login named ServiceGreatLogin maps to ServiceGreatUser in the Service database.
According to the requirement of the company CIO, the database user can only perform the two tasks
below:
the database user executes all stored procedures that currently exist in the Sales database
executes all stored procedures that will be created in the Sales database.
So what should you do to ensure this?
A. ServiceGreatUser should be added to the appropriate fixed database roles.
B. You should assign the appropriate object-level permissions to ServiceGreatUser
C. You should assign the appropriate server-level permissions to ServiceGreatLogin
D. You should assign the appropriate database-level permissions to ServiceGreatUser
Correct Answer: D Section: (none) Explanation Explanation/Reference:
As the user ServiceGreatLogin is mapped to ServiceGreatUser in the database, all the correct rights and/ or roles should be assigned to ServiceGreatUser
QUESTION 109
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a Windows group named Wiikigo\Service.
According to the security policy, members of this group are allowed to set up new connections to the SQL
Server instance. But this is can only be done during office time from 8:00-20:00. Other users may connect
to the SQL Server instance any time.
You write the following Transact-SQL statements.
CREATE TABLE Security.RestrictedLogonHours
(
Id int NOT NULL IDENTITY(1,1),
GroupName sysname NOT NULL,
RestrictedTimeStart time NOT NULL,
RestrictedTimeStop time NOT NULL,
CONSTRAINT RestrictedLogonHours_pk
PRIMARY KEY CLUSTERED(Id)
);
INSERT INTO Security.RestrictedLogonHours (
GroupName,
RestrictedTimeStart,
RestrictedTimeStop
)
VALUES (
'WIIKIGO\Service',
CAST('07:00' AS time),
CAST('19:00' AS time)
);
You need to implement the company security policy which is stored in the RestrictedLogonHours table.
What should you do?
A. You should create a SQL Server Agent job. The job causes the SQL Server Windows service to pause during non-business hours.
B. You should create a logon trigger. The trigger disallows the connection to the WIIKIGO\Service group during non-business hours
C. You should create a SQL Server Agent job. The job periodically looks for and kills connections made by the WIIKIGO\Service group during non-business hours.
D. You should create a policy. The policy uses a condition based on the Server Audit facet and disallows the connection to the WIIKIGO\Service group during non-business hours
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Logon triggers fire stored procedures in response to a LOGON event.
This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire
after the authentication phase of logging in finishes, but before the user session is actually established.
Therefore, all messages originating inside the trigger that would typically reach the user, such as error
messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon
triggers do not fire if authentication fails.
You can use logon triggers to audit and control server sessions, such as by tracking login activity,
restricting logins to SQL Server, or limiting the number of sessions for a specific login.
QUESTION 110
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database developer who is named
UserJack.
UserJack views the definitions of all database objects in a database to read data from all user-defined
tables, views, and table-valued functions. For UserJack, you have to assign the required permissions.
Besides this, you must make sure that other developers can also be given the same permissions, but this
should be achieved by executing as little Transact-SQL statements as possible.
In the options below, which Transact-SQL statements should you execute?
A. GRANT VIEW ANY DEFINITION TO UserJack;EXEC sp_addrolemember 'db_datareader', 'UserJack';
B. CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addrolemember 'Developers', 'UserJack';
C. CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;EXEC sp_addrolemember 'Developers', 'UserJack';
D. CREATE ROLE Developers;EXEC sp_addrolemember 'sp_dbdatareader', 'Developers';EXEC sp_addrolemember 'sp_dbddladmin', 'Developers';EXEC sp_addrolemember 'Developers', 'UserJack';
Correct Answer: C Section: (none) Explanation Explanation/Reference:
1.
Create ROLE: Roles are database-level securables. After you create a role, configure the databaselevel permissions of the role by using GRANT, DENY, and REVOKE. To add members to a database role, use the sp_addrolemember stored procedure.
2.
VIEW DEFINITION:The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.
3.
GRANT SELECT: Grants permissions on a securable to a principal.
QUESTION 111
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.
Now you are checking and configuring the security of the instance.
According to the requirement of the security audit policy, only successful and failed logon attempts are
recorded in log files and if records cannot be written to the log files, the SQL Server instance is shut down.
You must configure the SQL Server instance making it not violate the security audit policy.
In the options below, which Transact-SQL statements should you run?
A. sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'default trace enabled', 1; GO RECONFIGURE GO
B. sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'common criteria compliance enabled', 1; GO RECONFIGURE GO
C. CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH ='\\MAIN_SERVER\Audit\' ) WITH (ON_FAILURE = SHUTDOWN);
GO
CREATE SERVER AUDIT SPECIFICATION Audit_Specification FOR SERVER AUDIT Srv_Audit
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP)
WITH (STATE=ON);
GO
ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON);
GO
D. CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH ='\\MAIN_SERVER\Audit\' ) WITH
(ON_FAILURE = SHUTDOWN);
GO
CREATE DATABASE AUDIT SPECIFICATION Audit_Specification FOR SERVER AUDIT Srv_Audit
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP)
WITH (STATE=ON);
GO
ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON);
GO
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. You can use several methods of auditing for SQL Server, as described in Auditing (Database Engine). Beginning in SQL Server 2008 Enterprise, you can also set up automatic auditing by using SQL Server Audit. There are several levels of auditing for SQL Server, depending on government or standards requirements for your installation. SQL Server Audit provides the tools and processes you must have to enable, store, and view audits on various server and database objects. You can record server audit action groups per-instance, and either database audit action groups or database audit actions per database. The audit event will occur every time that the auditable action is encountered.
SQL Server Audit Components
An audit is the combination of several elements into a single package for a specific group of server actions
or database actions. The components of SQL Server Audit combine to produce an output that is called an
audit, just as a report definition combined with graphics and data elements produces a report.
SQL Server Audit uses Extended Events to help create an audit.
SQL Server Audit
The SQL Server Audit object collects a single instance of server or database-level actions and
groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple
audits per SQL Server instance.
When you define an audit, you specify the location for the output of the results. This is the audit
destination. The audit is created in a disabled state, and does not automatically audit any actions.
After the audit is enabled, the audit destination receives data from the audit.
Server Audit Specification
The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope. The server audit specification collects many server-level action groups raised by the Extended Events feature. You can include audit action groups in a server audit specification. Audit action groups are predefined groups of actions, which are the atomic events exposed by the Database Engine. These actions are sent to the audit, which records them in the target. Here only the answer C create Server audit for logins.
QUESTION 112
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a text file which is named SQL01.log on the E: drive. A SQL Server Agent job executes every 2
minutes and logs information to the text file.
You get a report from users saying that the sever doesn't make response. As the technical support, you
check and find that the SQL Server Agent service does not run and the SQL Server Agent job no longer
functions. You have to identify the reason why the SQL Server Agent service is unresponsive.
In the options below, which log should you check?
A. You should check SQL1.log
B. You should check SQLAGENT.OUT
C. You should check log_xx.trc
D. You should check ERRORLOG
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
The file SQLAGENT.OUT stores the SQL Agent running log. All the information about the latest run of the Agent is there. You can also find information into the event viewer.
QUESTION 113
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.
DB1 has a table which is named Table01 and a stored procedure named Procedure01.
Procedure01 choose data from Table01 by using a sp_executesql Transact-SQL statement.
You company security rules forbid users to access tables directly in any database.
Look at the exception below:
"Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'Table01', database
'DB1', schema 'dbo'."
The exception is raised when Procedure01 is executed by users. You must make sure that e user can
successfully execute Procedure1 complying with the company rules.
So what action should you perform to achieve this goal?
A. You should execute the GRANT SELECT ON dbo.Table01 TO User1 Transact-SQL statement.
B. You should execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement.
C. You should alter Procedure01 and add the WITH EXECUTE AS OWNER option to its header
D. You should alter Procedure01 and add the EXECUTE AS USER = 'dbo' option immediately before the call to the sp_executesql stored procedure.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
EXECUTE AS OWNER specifies the statements inside the module executes in the context of the current owner of the module. If the module does not have a specified owner, the owner of the schema of the module is used. Use EXECUTE AS OWNER in the following scenario: You want to be able to change owner of the module without having to modify the module itself. That is, OWNER automatically maps to the current owner of the module at run time. OWNER is the explicit owner of the module or, if there is not an explicit owner, the owner of the schema of the module at the time the module is executed. OWNER must be a singleton account and not a group or role. The ownership of the module cannot be changed to a group or role when the module specifies EXECUTE AS OWNER and has an explicit owner. The ownership of a schema cannot be changed to a role or group when it contains a module that specifies EXECUTE AS OWNER and the modules does not have an explicit owner. Functions (except inline table-valued functions), Stored Procedures, and DML Triggers { EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' } DDL Triggers with Database Scope
{ EXEC | EXECUTE } AS { CALLER | SELF | 'user_name' }
DDL Triggers with Server Scope and logon triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | 'login_name' }
Queues
{ EXEC | EXECUTE } AS { SELF | OWNER | 'user_name' }
QUESTION 114
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.
DB1 is available to a Windows group which is named WIIKIGO\Supervisors.
A Windows user named User1 is a member of the WIIKIGO\Supervisors group. WIIKIGO\Supervisors is a
member of the db_owner role in the DB1 database. You must make sure that the SQL Server instance is
(not?) available to User1.
In the DB1 database, which Transact-SQL statement(s) should you execute?
A. DROP LOGIN "WIIKIGO\User1";
B. EXEC dbo.sp_droprolemember 'db_owner', 'WIIKIGO\User1';
C. EXEC dbo.sp_revokedbaccess 'WIIKIGO\User1'; EXEC dbo.sp_revokelogin 'WIIKIGO\User1';
D. CREATE LOGIN "WIIKIGO\User1" FROM Windows;DENY CONNECT SQL TO "WIIKIGO\User1";
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
If I understand well, The group WIIKIGO\Supervisors is dbo of DB1. WIIKIGO\User1 is part of the group and should only have deny on the database.. This user will inherit rights from the group. Or we don't want him to connect to the instance, so we have to create one login for this user and specify one DENY CONNECT to this user.
QUESTION 115
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There are queries that have an estimated execution cost less than 3. You intend to configure the instance,
make it use a single thread for these queries.
In the options below, which sp_configure configuration option should you set?
A. You should set query governor cost limit
B. You should set cost threshold for parallelism
C. You should set priority boost
D. You should set precompute rank
Correct Answer: B Section: (none) Explanation
Explanation/Reference: Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. Only set cost threshold for parallelism on symmetric multiprocessors. Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.
QUESTION 116
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
According to the company requirement, you intend to perform an installation of an instance of SQL Server
Reporting Services (SSRS) to the same machine.
Now your company CIO wants version histories of all deployed reports can be kept.
As a technical support, what should you do to ensure this?
A. You should use the http.sys listener to install the SSRS instance.
B. You should configure the SSRS database, make it use Native mode
C. You should configure the SSRS database, make it use SharePoint integrated mode
D. You should use the Internet Information Services default Web site to install the SSRS instance
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
When created for SharePoint integrated mode, the report server database stores server properties, report execution snapshots, report history, subscription definitions, and schedules. It stores a secondary copy of reports, report models, shared data sources, and resources to improve processing performance on the server. Primary storage for report documents is in the SharePoint content databases.
QUESTION 117
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
In your company there is a server which contains a default SQL Server 2005 instance.
For a new application, you have to install a SQL Server 2008 instance on the server. Your company CIO
assigns a task to you.
You must make sure that the respective certified third-party applications have access to both database
instances. You have to achieve this goal without changing the existing application environments.
So what should you do to achieve this by using as little database administrative effort as possible?
A. You should install SQL Server 2008 as a named instance, and make the new application to use the new instance.
B. You should install SQL Server 2008 as the default instance, and make the new application to use the default instance.
C. You should upgrade the SQL Server 2005 application to use SQL Server 2008.
D. You should upgrade the SQL Server 2005 instance to a SQL Server 2008 instance.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
SQL Server supports multiple instances of the Database Engine, Analysis Services, and Reporting
Services on the same computer. You can also upgrade earlier versions of SQL Server, or install SQL
Server on a computer where earlier SQL Server versions are already installed.
If a default instance already exists on the computer, SQL Server must be installed as a named instance.
The following table shows side-by-side support for SQL Server 2008:
Existing instance of SQL Server 2008 Side-by-side support
SQL Server 2008 (32-bit) SQL Server 2000 (32-bit) SQL Server 2000 (64-bit) x64 SQL Server 2005 (32-bit) SQL Server 2005 (64-bit) x64 SQL Server 2008 (64-bit) IA64 SQL Server 2005 (64-bit) IA64 SQL Server 2008 (64-bit) x64 SQL Server 2000 (32-bit) SQL Server 2000 (64-bit) x64 SQL Server 2005 (32-bit) SQL Server 2005 (64-bit) x64
In this case, there is already one default instance SQL Server 2005, thus, we have to install one named instance.
QUESTION 118
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
You intend to configure FILESTREAM data, enabling FILESTREAM for file I/O streaming access and
allowing remote client computers to have streaming access to FILESTREAM data.
You must make sure that FILESTREAM data is enabled.
In the options below, which service should you configure?
A. You should configure Distributed File System
B. You should configure SQL Server
C. You should configure SQL Server Full Text
D. You should configure SQL Server VSS Writer
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL
Server Database Engine.
This topic describes how to enable FILESTREAM by using SQL Server Configuration Manager.
To enable and change FILESTREAM settings
On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to
Configuration Tools, and then click SQL Server Configuration Manager.
In the list of services, right-click SQL Server Services, and then click Open.
In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you
want to enable FILESTREAM.
Right-click the instance, and then click Properties.
In the SQL Server Properties dialog box, click the FILESTREAM tab.
Select the Enable FILESTREAM for Transact-SQL access check box.
If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O
streaming access. Enter the name of the Windows share in the Windows Share Name box.
If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote
clients to have streaming access to FILESTREAM data.
Click Apply.
In SQL Server Management Studio, click New Query to display the Query Editor.
In Query Editor, enter the following Transact-SQL code:
EXEC sp_configure filestream_access_level, 2 RECONFIGURE
QUESTION 119
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a single Database Mail profile. Reports are sent to all the company employees by using the Mail
profile. During office hours, a large volume of reports are sent by the sp_send_dbmail stored procedure.
Now you notice that it takes a long time for the reports to be sent to the company manager. As the
technical support, you have to speed up the delivery.
So what action should you perform to achieve this goal?
A. Another SMTP account should be added to the existing Database Mail profile
B. When you send the reports to the manager, you should use the @importance = high parameter.
C. You should change the Account Retry Attempts option of the Database Mail system properties
D. You should configure a new Database Mail profile. The new one will be used for sending the reports to the manager.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
A Database Mail profile is a collection of Database Mail accounts.
Profiles improve reliability in cases where an e-mail server becomes unreachable, by providing alternative
Database Mail accounts.
At least one Database Mail account is required.
QUESTION 120
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. You put forward a database maintenance plan. The
maintenance plan rebuilds indexes, checks database integrity, writes a report to a text file.
Now the company requires that when the maintenance plan executes abnormally, the operators can be
notified by an e-mail message.
You've been assigned this task. So you have to make modifications on the SQL Server instance.
What should you do?
A. For the SQL Server Agent, you should enable a fail-safe operator
B. You should modify the database maintenance plan, making it send an e-mail to the appropriate operators
C. You should modify the database maintenance plan, making it include a Notify Operator task that emails the appropriate operators.
D. You should modify the SQL Server Agent job. The job executes the database maintenance plan to notify the appropriate operators.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
In this case, you should add send job status to the operator via the following procedure:
This topic describes how to set notification options so Microsoft SQL Server Agent can send notifications
to operators about jobs.
To notify an operator of job status
1.
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that
instance.
2.
Expand SQL Server Agent, expand Jobs, right-click the job you want to edit, and select Properties.
3.
In the Job Properties dialog box, select the Notifications page.
4.
If you want to notify an operator by e-mail, check E-mail, select an operator from the list, and then
select one of the following:
When the job succeeds to notify the operator when the job completes successfully.
When the job fails to notify the operator when the job completes unsuccessfully.
When the job completes to notify the operator regardless of completion status.
5.
If you want to notify an operator by pager, check Page, select an operator from the list, and then select
one of the following:
When the job succeeds to notify the operator when the job completes successfully.
When the job fails to notify the operator when the job completes unsuccessfully.
When the job completes to notify the operator regardless of completion status.
6.
If you want to notify an operator by net send, check Net send, select an operator from the list, and then
select one of the following:
When the job succeeds to notify the operator when the job completes successfully.
When the job fails to notify the operator when the job completes unsuccessfully.
When the job completes to notify the operator regardless of completion status.
QUESTION 121
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. You have set the recovery model of DB1 to Full. You
deploy a new process. The process modifies 10,000 records from the Accounts table at 18:00 every day.
Any modification made by the process to the data must be reverted and database keeps online. You must
make sure of this.
So which strategy should you implement?
A. You should implement Differential backup
B. You should implement Database snapshots
C. You should implement Primary filegroup backup
D. You should implement Transaction log backup
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
A database snapshot is a read-only, static view of a database (called the source database). Each database snapshot is transactionally consistent with the source database at the moment of the snapshot's creation. When you create a database snapshot, the source database will typically have open transactions. Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transactionally consistent. Clients can query a database snapshot, which makes it useful for writing reports based on the data at the time of snapshot creation. Also, if the source database later becomes damaged, you can revert the source database to the state it was in when the snapshot was created. Reasons to take database snapshots include: Maintaining historical data for report generation. Because a database snapshot provides a static view of a database, a snapshot can extend access to data from a particular point in time. For example, you can create a database snapshot at the end of a given time period (such as a financial quarter) for later reporting. You can then run end-of-period reports on the snapshot. If disk space permits, you can also maintain end-of-period snapshots indefinitely, allowing queries against the results from these periods; for example, to investigate organizational performance. Using a mirror database that you are maintaining for availability purposes to offload reporting. Using database snapshots with database mirroring permits you to make the data on the mirror server accessible for reporting. Additionally, running queries on the mirror database can free up resources on the principal. For more information, see Database Mirroring and Database Snapshots. Safeguarding data against administrative error.
Before doing major updates, such as a bulk update or a schema change, create a database snapshot on the database protects data. If you make a mistake, you can use the snapshot to recover by reverting the database to the snapshot. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.
QUESTION 122
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. There is a database named DB1 in the instance.
The backup strategy for DB1 has the following requirements: every day at 9:00 full database backup to a
file named cash.bak is performed; at 9:15 a transaction log backup to a file named cash_HHMM.trn is
performed every 15 minutes.
DB1 is being used in single-user mode.
Today a user reports that a query deleted some data by accident, the query was executed at 9: 25. Since
the deleted data is quite important, the data has to be restored to its original state. You must achieve this
goal.
So what action should you perform to achieve this goal?
A. RESTORE DATABASE Cash
FROM DISK = 't:\backups\cash.bak'
WITH NORECOVERY;
RESTORE LOG Cash
FROM 't:\backups\cash_0930.trn'
WITH RECOVERY,
STOPAT = 'Mar 17, 2008 9:25AM';
B. RESTORE DATABASE Cash
FROM DISK = 't:\backups\cash.bak';
RESTORE LOG Cash FROM 't:\backups\cash_0915.trn'
WITH NORECOVERY;
RESTORE LOG Cash FROM 't:\backups\cash_0930.trn'
WITH RECOVERY,
STOPAT = 'Mar 17, 2008 9:25AM';
C. RESTORE DATABASE Cash
FROM DISK = 't:\backups\cash.bak';
RESTORE LOG Cash
FROM 't:\backups\cash_0915.trn'
WITH NORECOVERY;
RESTORE LOG Cash FROM 't:\backups\cash_0930.trn'
WITH RECOVERY;
D. RESTORE DATABASE Cash
FROM DISK = 't:\backups\cash.bak'
WITH NORECOVERY;
RESTORE LOG Cash
FROM 't:\backups\cash_0915.trn'
WITH NORECOVERY;
RESTORE LOG Cash
FROM 't:\backups\cash_0930.trn'
WITH RECOVERY,
STOPAT = 'Mar 17, 2008 9:25AM';
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
To restore this database to 9:25 AM, you have to :
1.
Restore the full backup with the NORECOVERY option.
2.
Restore the log backup of 9:15 AM with the NORECOVERY option.
3.
Restore the log backup of 9:30 AM with the RECOVERY and STOPAT = 'Mar 17, 2008 9:25AM' option.
QUESTION 123
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance. At
present about 90 GB of data is stored in DB1.
You made some rough calculations that every day about 1 GB of data is modified or inserted. You set the
recovery model of DB1 to Simple. According to the company requirements, during business hours, data
loss of more than 120 minutes worth of transactions is unacceptable.
You have to choose a backup strategy which must comply with the following two requirements:
Using as little disk space as possible
Complying with the company requirements.
So what action should you perform to achieve this goal?
A. A full database backup should be performed once daily. During business hour, a transaction log backup should be performed every two hours.
B. A full database backup should be performed once daily
C. A full database backup should be performed once daily. During business hours, a differential backup should be performed every two hours
D. Perform a full database backup should be performed once every week. A differential backup should be performed once daily. During business hours a transaction log backup should be performed every two hours.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
From the question, we could have the following information:
4.
As the database recovery model is set to SIMPLE, you cannot create log backups.
5.
Data loss is 120 min, thus two hours
6.
Full backup every two hours will lead us to missuse of disk space.
7.
Differential database backup will keep track of all the changes in the database since the latest full
backup. So the latest differential backup will have a maximal size of 1GB.
Thus, the backup strategy will be: one full backup daily and one differential backup every two hours.
QUESTION 124
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. The latest differential backup is performed at 15:30, the
full back up was performed at 13:30, and database snapshots were created at 16:30 and at 17:30.
The backups and the database snapshots are stored on a different disk from the database files. At 17:05,
the hard disk containing the database files fails at 17:02 hours.
You must restore DB1 while reduce data loss to the least.
So what action should you perform to achieve this goal?
A. You should restore the full backup
B. You should restore the database snapshot from 16:30 hours.
C. You should restore the database snapshot from 17:30 hours
D. You should restore both the full backup and the differential backup.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
You can't revert from the database snapshot because a snashot is a view of the db and stores only the
pages that where changed since the snapshot was taken.
Thus, the only way to get back the database is to restore the database full backup of 13:30 and the
differential backup of 15:30.
There will be a gap of 1 hour 32 minutes.
QUESTION 125
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. There are data file and the transaction log file on the E:
drive. Now you find that there is only 6% spare space. Both files have to be moved to the V: drive.
Which procedure should you
use?
A. You should run the following Transact-SQL statement.
ALTER DATABASE DB1 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 DB1 MODIFY FILE(NAME = DB1_Data, FILENAME = 'v:\SQLServer\DB1_Data.
mdf');
ALTER DATABASE DB1 SET MULTI_USER;
B. You should terminate the SQL Server Service.
Then move the data file and transaction log file to the new location.
Start the SQL Server service.
Run the following Transact-SQL statement.
EXEC sp_attach_DB @DBname = N'DB1',@filename1 = N'v:\SQLServer\DB1_Data.mdf',@filename2
= N'v:\SQLServer\DB1_Log.ldf';
C. You should run the following Transact-SQL statementALTER DATABASE DB1 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 DB1 MODIFY FILE(NAME = DB1_Data, FILENAME = 'v:\SQLServer\DB1_Data.
mdf');
ALTER DATABASE DB1 MODIFY FILE(NAME = DB1_Log, FILENAME = 'v:\SQLServer\DB1_Log.
ldf');
ALTER DATABASE DB1 SET ONLINE;
D. You should terminate the SQL Server service.
Then move the data file to the new location.
Start the SQL Server service.
Run the following Transact-SQL statement.
EXEC sp_attach_single_file_DB @DBname = N'DB1',@physname = N'v:\SQLServer\DB1_Data.mdf';
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
The two answers with the attach database are wrong because there is NO detach action before the attach statement.
QUESTION A is wrong because it moves only the data file (mdf). Or we should move the mdf and ldf files. So, answer C is correct, because the two files are correctly moved.
QUESTION 126
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 Enterprise Edition instance.
There is a database named DB1 in the instance. A backup of DB1 is performed every day. You have to
minimize the size of the full database backup files of DB1.
In the options below, which Transact-SQL statement should you use?
A. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak';
B. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COMPRESSION;
C. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH DIFFERENTIAL;
D. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COMPRESSION, DIFFERENTIAL;
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
We will use the compression option to minimize the FULL backup size. So, answer D is giving us one differential backup COMPRESSION: Backup compression was introduced in SQL Server 2008 Enterprise. This topic discusses the basics of backup compression, including the performance trade-off of compressing backups.
Note:
Though creating compressed backups is supported only in SQL Server 2008 Enterprise and later, every SQL Server 2008 or later edition can restore a compressed backup.
Restrictions
The following restrictions apply to compressed backups:
Compressed and uncompressed backups cannot co-exist in a media set.
Previous versions of SQL Server cannot read compressed backups.
NTbackups cannot share a tape with compressed SQL Server backups.
QUESTION 127
You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. When you are absent, a user will use a login named Mary
to log in and maintain the database snapshots. The user has to delete the database snapshots for DB1, so
you have to give the appropriate permissions to the user.
So which database permission should you give the user?
A. DELETE
B. CONTROL
C. DROP DATABASE
D. ALTER ANY DATASPACE
Correct Answer: C Section: (none) Explanation
Explanation/Reference: Dropping a Database Snapshot
Dropping a database snapshot deletes the database snapshot from an instance of SQL Server and deletes the physical NTFS File System sparse files used by the snapshot. For information about using sparse files by database snapshots, see How Database Snapshots Work. Dropping a database snapshot clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval.
Permissions
To execute DROP DATABASE, at a minimum, a user must have CONTROL permission on the database. So, for me, the answer is B, not C
QUESTION 128
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. The DB1 database includes spatial data types. On DB1
database, you have to perform a database consistency check to include the spatial indexes.
Besides this, you must make sure that you can reduce the effect on the database concurrency to the
least.
So which Transact-SQL statement should you run?
A. DBCC CHECKCATALOG (DB1);
B. DBCC CHECKALLOC (DB1) WITH TABLOCK;
C. DBCC CHECKDB (DB1) WITH TABLOCK, PHYSICAL_ONLY;
D. DBCC CHECKDB (DB1) WITH EXTENDED_LOGICAL_CHECKS;
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
DBCC CHECKDB Checks the logical and physical integrity of all the objects in the specified database.
EXTENDED_LOGICAL_CHECKS
If the compatibility level is 100 (SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.
QUESTION 129
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
A SQL Server Agent job is failing. You find that the job history information is incomplete and seems to be
truncated after you review it. All information produced by a job must be available for viewing.
So what action should you perform to achieve this goal?
A. You should enable notifications to the Windows application event log when the job completes.
B. You should enable write OEM file
C. You should enable all job steps, making them send the output to a file
D. You should include execution trace messages in the SQL Agent Error log
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
In all the jobsteps, go to the option panel and select Output file to get all info from the step. Note that you
can append or overide the file. All job steps should be adapted with the option.
Output file Sets the file to use for output from the job step.
QUESTION 130
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
The instance contains a database named DB1. An application which is continuously connected uses DB1.
The application extensively uses the INSERT command and triggers the population of multiple tables. Now
you notice that the application has a poor performance. You doubt that this problem has something to do
with blocking.
You have to monitor the state of the instance at regular intervals while not affecting the application
performance further.
Which tool should you use?
A. You should use Dynamic Management Views
B. You should use SQL Server Resource Governor
C. You should use SQL Server Profiler
D. You should use Windows System Monitor
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
Dynamic management views and functions return server state information that can be used to monitor the
health of a server instance, diagnose problems, and tune performance.
Important: Dynamic management views and functions return internal, implementation-specific state data.
Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic
management views and functions in future releases may not be compatible with the dynamic management
views and functions in SQL Server 2008.
There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions. These require VIEW SERVER STATE
permission on the server.
Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE
permission on the database.
QUESTION 131
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2000 instance.
You upgrade this instance to SQL Server 2008. You configure the Agent service to use the LocalSystem
account. There is a file named Orderdata on a remote network share. A job accesses this Orderdata file by
using a CMDExec step. The job step fails to complete execution after the upgrade.
You must make sure that the job can complete execution, so you have to configure the job step.
What action should you perform?
A. You should configure a certificate
B. You should configure the SQL Server Agent service to use the NetworkService account.
C. You should configure the job step to use a proxy account
D. You should configure the SQL Server Agent service to use a local Windows account
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
Because job steps run in the context of a specific Microsoft Windows user, that user must have the
permissions and configuration necessary for the job step to execute.
For example, if you create a job that requires a drive letter or a Universal Naming Convention (UNC) path,
the job steps may run under your Windows user account while testing the tasks.
However, the Windows user for the job step must also have the necessary permissions, drive letter
configurations, or access to the required drive.
Otherwise, the job step fails. To prevent this problem, ensure that the proxy for each job step has the
necessary permissions for the task that the job step performs.
QUESTION 132
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
A user reports that when he runs a query, it takes a quite long time and it is still unfinished. As the IT
support, you have to verify whether the query is blocked.
Which tool should you use?
A. You should use the Database Engine Tuning Advisor tool
B. You should use the Windows System Monitor tool
C. You should use the Job Activity Monitor tool in Microsoft SQL Server Management Studio
D. You should use the Activity Monitor tool in Microsoft SQL Server Management Studio
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
The Activity Monitor in SQL Server Management Studio graphically displays information about:
Processes running on an instance of SQL Server.
Blocked processes.
Locks.
User activity.
This is useful for ad hoc views of current activity.
QUESTION 133
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
You create a SQL Server Agent job that execute every 2 minutes. There is a text file named SQL.log on
the E: drive. The job logs information to the text file.
You get a report from users saying that the server doesn't make response. You check and find that the
SQL Server Agent service does not run and the Agent job doesn't function any longer.
As the IT support, you have to identify the reason for the failure of the SQL Server Agent service.
Which log should you check?
A. You should check ERRORLOG
B. You should check QL1.log
C. You should check SQLAGENT.OUT
D. You should check log_xx.trc
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
The file SQLAGENT.OUT stores the SQL Agent running log. All the information about the latest run of the Agent is there. You can also find information into the event viewer.
QUESTION 134
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.
You have set the recovery model of DB1 to Full. At 2:00 There is a full database backup of all the user
databases every day. Every quarter the transaction log backup is performed. Every 4 hours a differential
backup is occurred. You are going to perform a full backup of DB1 at 11:00.
When you perform this back up, you must make sure that the overall backup will not be affected. What's
more, you have to restore procedures for DB1. You must make sure that the backup files are restored in
proper sequence.
In the options below, which Transact-SQL statement should you use?
A. BACKUP LOG DB1 TO DISK = 't:\backups\db1.trn';
B. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH DIFFERENTIAL;
C. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH NOUNLOAD;
D. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COPY_ONLY;
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
COPY_ONLY Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database. Copy-only backups were introduced in SQL Server 2005 for use in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted. When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copyonly backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base.
Important:
If DIFFERENTIAL and COPY_ONLY are used together, COPY_ONLY is ignored, and a differential backup is created. When used with BACKUP LOG, the COPY_ONLY option creates a copy-only log backup, which does not truncate the transaction log. The copy-only log backup has no effect on the log chain, and other log backups behave as if the copy-only backup does not exist.
QUESTION 135
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
Now you get an order from your company CIO, you have to identify whether a database integrity check
(DBCC CHECKDB) was run for a particular database.
Which log file should you examine?
A. log.trc
B. default.trc
C. ERRORLOG
D. SQLAGENT
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
View the SQL Server error log to ensure that processes have completed successfully (for example, backup and restore operations, batch commands, or other scripts and processes). This can be helpful to detect any current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, or other server-level error messages.
QUESTION 136
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance on the same physical computer.
Now users report that they are unable to connect to the named instances. You check and verify that they
can only connect to the default instance. You also ensure that all SQL Server instances run normally.
You have to start the service which is required to connect to the named instances.
Which service should you start?
A. Server
B. SQL Server Agent
C. SQL Server Browser
D. SQL Active Directory Helper
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions: Browsing a list of available servers Connecting to the correct server instance Connecting to dedicated administrator connection (DAC) endpoints
QUESTION 137
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There are user-defined stored procedures. Now you have to make sure two things, first, the names of all
user-defined stored procedures must contain the prefix usp_ on all instances; second, stored procedures
that do not contain this prefix cannot be created by you.
So what should you do to ensure this two?
A. You should create a policy that targets the name of the stored procedure that is evaluated on change.
B. You should create a policy that targets the name of the stored procedure that is evaluated on demand.
C. You should create a condition that targets the name of stored procedure that is evaluated on demand.
D. You should create a condition that targets the name of the stored procedure that is evaluated on change.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
Policy-Based Management is a system for managing one or more instances of SQL Server 2008. When
SQL Server policy administrators use Policy-Based Management, they use SQL Server Management
Studio to create policies to manage entities on the server, such as the instance of SQL Server, databases,
or other SQL Server objects.
Policy-Based Management has three components:
Policy management
Policy administrators create policies.
Explicit administration
Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.
Evaluation modes
There are four evaluation modes, three of which can be automated:
On demand. This mode evaluates the policy when directly specified by the user.
On change: prevent. This automated mode uses DDL triggers to prevent policy violations.
Important:
If the nested triggers server configuration option is disabled, On change: prevent will not work
correctly. Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that
do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL
triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.
On change: log only. This automated mode uses event notification to evaluate a policy when a relevant
change is made.
On schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy.
When automated policies are not enabled, Policy-Based Management will not affect system performance.
QUESTION 138
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
A new SQL Agent job has been created by you. The job includes a Windows PowerShell job step which
transfer data between servers by using the SQLCmd utility. If the job fails, you must make sure that an
operator named ReactGroup is informed by receiving an e-mail.
So what action should you perform to achieve this goal?
A. First you should enable Notifications. Direct the Notification to the ReactGroup operator on job failure after you create the ReactGroup operator.
B. First you should enable Notifications. Direct the Notification to the ReactGroup operator on failure of the Powershell job step after you create the ReactGroup operator.
C. First you should assign the ReactGroup operator as the failsafe operator. Then enable the job after you create the ReactGroup operator.
D. First you should configure the job step proxy account to use the ReactGroup operator account after you create the ReactGroup operator. At last choose the proxy account for the e-mail profile.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
In this case, you should add send job status to the operator via the following procedure:
This topic describes how to set notification options so Microsoft SQL Server Agent can send notifications
to operators about jobs.
To notify an operator of job status
1.
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that
instance.
2.
Expand SQL Server Agent, expand Jobs, right-click the job you want to edit, and select Properties.
3.
In the Job Properties dialog box, select the Notifications page.
4.
If you want to notify an operator by e-mail, check E-mail, select an operator from the list, and then
select one of the following:
When the job succeeds to notify the operator when the job completes successfully.
When the job fails to notify the operator when the job completes unsuccessfully.
When the job completes to notify the operator regardless of completion status.
5.
If you want to notify an operator by pager, check Page, select an operator from the list, and then select
one of the following:
When the job succeeds to notify the operator when the job completes successfully.
When the job fails to notify the operator when the job completes unsuccessfully.
When the job completes to notify the operator regardless of completion status.
6.
If you want to notify an operator by net send, check Net send, select an operator from the list, and then
select one of the following:
When the job succeeds to notify the operator when the job completes successfully.
When the job fails to notify the operator when the job completes unsuccessfully.
When the job completes to notify the operator regardless of completion status.
QUESTION 139
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a stored procedure. The stored procedure implements a database maintenance process.
A SQL Server Agent job should be created to that runs the stored procedure. Besides this, you must make
sure that after it is completed successfully, the job is removed.
What should you do?
A. You should create a job which is assigned to the Database Maintenance category
B. You should create an Alert which will run another to delete the maintenance job.
C. You should create a job. You schedule the job to run only once.
D. You should create a job. The job uses the Automatically delete job option
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
This topic describes how to configure Microsoft SQL Server Agent to automatically delete jobs when they
succeed, fail, or complete.
To automatically delete a job
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that
instance.
Expand SQL Server Agent, expand Jobs, right-click the job you want to edit, and then click Properties.
Select the Notifications page.
Check Automatically delete job, and choose one of the following:
Click When the job succeeds to delete the job status when it has completed successfully.
Click When the job fails to delete the job when it has completed unsuccessfully.
Click When the job completes to delete the job regardless of completion status.
QUESTION 140
You work in a company which uses SQL Server 2008. You are the administrator of the company
database.
Now you are in charge of a SQL Server 2008 instance which contains multiple Agent jobs. The jobs run
each job on each day of the week by using seven shared schedules.
You must make sure that a job named Job4 doesn't run Wednesday.
So what action should you perform to achieve this goal?
A. You should remove the schedule for Wednesday from Job4.
B. You should add a new Job4 schedule for Wednesday and disable it.
C. You should delete the schedule for Wednesday.
D. You should disable the schedule for Wednesday.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
SQL Server Agent Job runs at determined time via the Schedules that are in the "Schedules" tab. Here we have one schedule for each days of the week (7 schedules in total). So, to pevent the job to run on Wednesday, we have to remove this schedule.
QUESTION 141
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
You have to perform the backup of the default trace according to the requirement of your failure recovery
plan. The default trace is contained in the subdirectory. You have to backup the subdirectory.
Which SQL Server database engine subdirectory should be backed up?
A. DATA
B. INSTALL
C. LOG
D. BINN
Correct Answer: C Section: (none) Explanation
Explanation/Reference: Use the default trace enabled option to enable or disable the default trace log files. The default trace functionality provides a rich, persistent log of activity and changes primarily related to the configuration options. Purpose Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur. Viewing The default trace logs can be opened and examined by SQL Server Profiler or queried with Transact-SQL by using the fn_trace_gettable system function. SQL Server Profiler can open the default trace log files just as it does normal trace output files. The default trace log is stored by default in the \MSSQL\LOG directory using a rollover trace file. The base file name for the default trace log file is log.trc. In a typical installation of SQL Server, the default trace is enabled and thus becomes TraceID 1. If enabled after installation and after creating other traces, the TraceID can become a larger number.
QUESTION 142
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a management data warehouse that uses the data collector to collect performance data. You
intend to maintain the management data. You want to implement a data collection process. The process
can gather and upload data in the management data warehouse on different schedules.
What process should you implement?
A. You should create a cached data collection
B. You should create an on-demand non-cached data collection
C. You should create a scheduled non-cached data collection
D. You should create two different SQL Agent jobs that are scheduled stimultaneously
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
Data collection and upload
Specifies how data is collected and uploaded to the management data warehouse. Pick one of the
following options.
Cached. Collection and data upload not on the same schedule.
QUESTION 143
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a remote SQL Server 2008 instance.
You notice that the application has a poor performance. On the remote SQL Server instance, you capture
a workload of the remote instance to a trace table by using SQL Profiler. Now you have to use the
Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local
SQL Server instance.
So what action should you perform to achieve this goal?
A. You should recapture the workload by using the data collector.
B. You should enable the XP_MSVER stored procedure on the local server.
C. You should enable the XP_MSVER stored procedure on the remote server
D. You should recapture the workload to a trace file by using SQL Profiler.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
In order to use the Database Engine Tuning Advisor, you need one trace file, here in the question, there is a trace table availlable, which is not good. We have then to restart one trace to a file.
QUESTION 144
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance which contains a database.
According to the company requirement, you have to move the application from Microsoft SQL Server 2000
to Microsoft SQL Server 2008. You have to monitor the SQL Server instance to record the
use of features.
These features will be discontinued. What should you do?
A. The SQL Server 2008 Upgrade Advisor should be used.
B. The SQL Server Profiler which captures the SQL:BatchCompleted and Exception event classes should be used.
C. A SQL server-side trace that captures the Deprecation Announcement and Deprecation Final Support event classes should be used
D. A SQL server-side trace that captures the SQL:BatchCompleted and Exception event classes should be used.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
The Deprecation Final Support event class occurs when you use a feature that will be removed from the
next major release of SQL Server. For greatest longevity of your applications, do not use features that
cause the Deprecation Final Support event class or the Deprecation Announcement event class.
Modify applications that use final deprecation features as soon as possible.
QUESTION 145
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.
DB1 has two filegroups: one is a filegroup named History that contains historical data stored in the G:
volume; the other is the PRIMARY filegroup that contains the On-Line Transaction Processing (OLTP)
data stored in the F: volume.
The backup strategy of DB1 has three requirements:
first, the History filegroup should be backed up at the end of each working day;
second, the PRIMARY filegroup should be backed up five times a day;
third, the transaction log should be backed up every quarter.
You get a report at 13:00 saying that the volume which contains the History filegroup fails.
As the technical support, you have to restore the database, and you must reduce the data loss to the least
as soon as possible.
What should you do?
A. You should restore the most recent backup of the History filegroup.
B. You should restore the most recent backup of the History filegroup. Restore all the transaction log backups after the most recent backup of the History filegroup
C. You should backup the transaction log. Then restore the most recent backup of the PRIMARY filegroup and restore the most recent backup of the History filegroup. After the most recent backup of the History filegroup, restore all the transaction log backups
D. You should backup the transaction log. Then restore the most recent backup of the History filegroup and restore all the transaction log backups after the most recent backup of the History filegroup.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
As you have made one filegroup backup of the History filegroup, you can restore it and apply all the
transaction log to this restored filegroup.
Or, you need to reduce the the data lost to the least. In this case, you have to first backup the transaction
log and then make the restore.
QUESTION 146
You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance which contains a database. Now your company CIO assigns a task to you. You have to check the performance of the SQL Server 2008 instance. You have to find out which common language runtime (CLR) queries takes the longest-running time. So in the options below, which dynamic management view should you choose to use?
A. sys.dm_os_wait_stats
B. sys.dm_exec_requests
C. sys.dm_exec_sessions
D. sys.dm_exec_query_stats
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 147
You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. The instance has databases for a finance application and a manufacturing application. You want to put some limitation on the maximum CPU query time allowed by the applications, so you configure the Resource Governor to achieve this. But you notice that certain reports do not execute successfully any longer on the manufacturing application. You have to allow the manufacturing application to consume more CPU time, so you have to change the Resource Governor configuration. Which Resource Governor component should you configure?
A. The workload group which is used by default
B. The workload group which is used by the finance application
C. The classifier function which identifies the finance application
D. The workload group which is used by the manufacturing application
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 148
You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. For some reason, now the company wants to view the number of users who access the SQL Server instance. The company CIO assigns this task to you. You have to use Windows System Monitor to find out the concrete number. Which performance object should you capture?
A. SQLServer:Buffer Manager
B. SQLServer:Access Methods
C. SQLServer:General Statistics D. SQLServer:Exec Statistics
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 149
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named Dworks in the instance. The Dworks database has a table named Bills which
contains several indexes and a great amount of rows. An enterprise Web environment is supported by the
database. On the Bills table 28% of the indexes have been fragmented.
You have to defragment the indexes, making sure that you reduce the effect on database availability to the
least.
In the ALTER INDEX statement, which option should be included?
A. Sort in tempdb
B. Online
C. Fill factor
D. Pad index
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
ONLINE = { ON |OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification
during the index operation. The default is OFF.
For an XML index or spatial index, only ONLINE = OFF is supported, and if ONLINE is set to ON an error
is raised.
Note:
Online index operations are available only in SQL Server Enterprise. ON Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table. OFF Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements. Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions: Disabled indexes XML indexes Indexes on local temp tables Partitioned indexes Clustered indexes if the underlying table contains LOB data types Nonclustered indexes that are defined with LOB data type columns Nonclustered indexes can be rebuilt online if the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey columns.
QUESTION 150
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named named Dworks in the instance. The table below shows the two tables of the
Dworks database:
There are two partitions in the BillHistory table. Partition1 is empty while partition2 contains all data.
Now you intend to move data from partition2 to the appropriate partition of the BillHistory table. Partions2
contains data for the oldest month of the Bills table.
You are going to develop a process toachieve this.
You must make sure that at the end of every month the process can be repeated.
A. 1. Alter the partition function of the BillHistory table by using the split option.
2.
Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table.
3.
Alter the BillHistory table by using the split option.
B. 1. Alter the partition function of the Bills table by using the split option.
2. Alter the Billstable by using the merge option.
C. 1. Alter the partition function of the BillHistory table by using the split option.
2.
Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table.
3.
Alter the BillHistory table by using the merge option.
4.
Alter the Bills table by using the merge option.
D. 1. Alter the partition function of the Bills table by using the split option.
2.
Alter the BillHistory table by using the split option.
3.
Alter the partition function of the BillHistory table by using the merge option.
4.
Alter the Bill stable by using the merge option.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
1.
split the boundaries of the partition function of the target table
2.
switch the appropriate partition of source table to the target table
3.
merge the boundaries of the partition function of the target table
4.
merge the boundaries of the partition function of the source table
Theory
Alters a partition function by splitting or merging its boundary values. By executing ALTER PARTITION FUNCTION, one partition of any table or index that uses the partition function can be split into two partitions, or two partitions can be merged into one less partition. ALTER PARTITION FUNCTION partition_function_name() { SPLIT RANGE ( boundary_value ) | MERGE RANGE ( boundary_value ) }[;] SPLIT RANGE ( boundary_value ) Adds one partition to the partition function. boundary_value determines the range of the new partition, and must differ from the existing boundary ranges of the partition function. Based on boundary_value, the Database Engine splits one of the existing ranges into two. Of these two, the one where the new boundary_value resides is considered the new partition. Important: A filegroup must exist online and be marked by the partition scheme that uses the partition function as NEXT USED to hold the new partition. Filegroups are allocated to partitions in a CREATE PARTITION SCHEME statement. If a CREATE PARTITION SCHEME statement allocates more filegroups than necessary (fewer partitions are created in the CREATE PARTITION FUNCTION statement than filegroups to hold them), then there are unassigned filegroups, and one of them is marked NEXT USED by the partition scheme. This filegroup will hold the new partition. If there are no filegroups marked NEXT USED by the partition scheme, you must use ALTER PARTITION SCHEME to either add a filegroup, or designate an existing one, to hold the new partition. A filegroup that already holds partitions can be designated to hold additional partitions. Because a partition function can participate in more than one partition scheme, all the partition schemes that use the partition function to which you are adding partitions must have a NEXT USED filegroup. Otherwise, ALTER PARTITION FUNCTION fails with an error that displays the partition scheme or schemes that lack a NEXT USED filegroup. MERGE [ RANGE ( boundary_value)] Drops a partition and merges any values that exist in the partition into one of the remaining partitions. RANGE (boundary_value) must be an existing boundary value, into which the values from the dropped partition are merged. The filegroup that originally held boundary_value is removed from the partition scheme unless it is used by a remaining partition, or is marked with the NEXT USED property. The merged partition resides in the filegroup that originally did not hold boundary_value. boundary_value is a constant expression that can reference variables (including user-defined type variables) or functions (including userdefined functions). It cannot reference a Transact-SQL expression. boundary_value must either match or be implicitly convertible to the data type of its corresponding partitioning column, and cannot be truncated during implicit conversion in a way that the size and scale of the value does not match that of its corresponding input_parameter_type. SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_ partition_number_expression ] Switches a block of data in one of the following ways: Reassigns all data of a table as a partition to an already-existing partitioned table. Switches a partition from one partitioned table to another. Reassigns all data in one partition of a partitioned table to an existing non-partitioned table. If table is a partitioned table, source_partition_number_expression must be specified. If target_table is partitioned, target_partition_number_expression must be specified. If reassigning a table's data as a partition to an already-existing partitioned table, or switching a partition from one partitioned table to another, the target partition must exist and it must be empty. If reassigning one partition's data to form a single table, the target table must already be created and it must be empty. Both the source table or partition, and the target table or partition, must reside in the same filegroup. The corresponding indexes, or index partitions, must also reside in the same filegroup. Many additional restrictions apply to switching partitions. For more information, see Transferring Data Efficiently by Using Partition Switching. table and target_table cannot be the same. target_table can be a multi-part identifier. source_partition_number_expression and target_partition_number_expression are constant expressions that can reference variables and functions. These include user-defined type variables and user-defined functions. They cannot reference Transact-SQL expressions.
Note:
You cannot use the SWITCH statement on replicated tables.
QUESTION 151
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
The company wants to export data from SQL Server to a Microsoft Excel file. The company assigns this
task to you. You perform this by using the SQL Server Import and Export Wizard.
After this, you save the package definition to a file. Now you have to reexecute the package from the
command line.
In the options below, which utility should you choose to use?
A. bcp.exe
B. dta.exe
C. dtexec.exe
D. sqlmaint.exe
Correct Answer: C Section: (none) Explanation
Explanation/Reference: The dtexec command prompt utility is used to configure and execute SQL Server Integration Services packages. The dtexec utility provides access to all the package configuration and execution features, such as connections, properties, variables, logging, and progress indicators. The dtexec utility lets you load packages from three sources: a Microsoft SQL Server database, the SSIS service, and the file system.
Note: On a 64-bit computer, Integration Services installs a 64-bit version of the dtexec utility (dtexec.exe). If you have to run certain packages in 32-bit mode, you will have to install the 32-bit version of the dtexec utility. To install the 32-bit version of the dtexec utility, you must select either Client Tools or Business Intelligence Development Studio during setup.
Note: When you use the version of the dtexec utility that comes with SQL Server 2008 to run a SQL Server 2005 Integration Services (SSIS) package, Integration Services temporarily upgrades the package to SQL Server 2008 Integration Services (SSIS). However, you cannot use the dtexec utility to save these upgraded changes. For more information about how to permanently upgrade a SQL Server 2005 package to SQL Server 2008, see Upgrading Integration Services Packages.
QUESTION 152
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named named Dworks in the instance. The Dworks table has a table named
BillDetails which has a clustered primary key named BillId on the BillId column.
The BillDetails table has a single XML column named GoodsDetails. The XML column has an XML index
and XML data are stored in this XML column.
A new column named GoodsID is added to the BillDetails table. GoodsID must be included in the primary
key.
So what action should you perform to achieve this goal?
A. You should drop the XML index on the table. Modify the primary key. Recreate the XML index
B. You should alter the XML index and set the ALLOW_ROW_LOCKS = OFF option. Alter the primary key and set the ALLOW_ROW_LOCKS = ON option.
C. You should move the XML data to a temporary table. Clear the XML data from the original table by setting the GoodsDetails column to NULL. Modify the primary key. Repopulate the ProductSpecs column.
D. You should disable the XML index on the GoodsDetails column. Modify the primary key.Enable the XML index on the ProductSpecs column.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
1.
In order to change a primary key, you have to drop it, or, A PRIMARY KEY constraint cannot be deleted
if the following exist:
If it is referenced by a FOREIGN KEY constraint in another table; the FOREIGN KEY constraint must
be deleted first.
The table has a PRIMARY XML index applied on it.
2.
XML indexes fall into the following categories:
Primary XML index
Secondary XML index
The first index on the xml type column must be the primary XML index. Using the primary XML index,
the following types of secondary indexes are supported: PATH, VALUE, and PROPERTY. Depending on
the type of queries, these secondary indexes might help improve query performance.
Thus, you have first to drop the XML index as it is the primary index and this forbit the primary key to be
dropped.
QUESTION 153
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a n On-Line Analytical Processing (OLAP) database named in the instance. The database
contains a dimension table named Clients. Ever hour backup of data of the Clients table is performed. But
the Clinets table contains redundant data. You must keep the disk space used to store the Clients table.
In the options below, which compression technology should you use?
A. You should use row compression
B. You should use page compression
C. You should use backup compression D. You should use windows NTFS file system compression
Correct Answer: B Section: (none) Explanation
Explanation/Reference: Page Compression
The second and the most vital compression method is page compression. Page compression allows common data to be shared between rows for a given page. Its uses the following techniques to compress data: Row compression. (Already discussed above) Prefix Compression. For every column in a page duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which resides after page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used. Dictionary Compression. Dictionary compression searches for duplicate values through out the page and stores them in CI. The main difference between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page. Because of the Prefix Compression and Dictionary Compression, duplicate values are shared between rows in a given page. Which lead us to reduce the disk space used.
QUESTION 154
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. According to the requirements of the security audit policy,
only successful and failed logon attempts are recorded in log files; if records cannot be written to the log
files, the SQL Server instance is shut down.
You have to make the SQL Server instance comply with the security audit policy by performing the
configuration.
Which Transact-SQL statements should you run?
A. sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'default trace enabled', 1; GO RECONFIGURE GO
B. sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'common criteria compliance enabled', 1;
GO
RECONFIGURE
GO
C. CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH ='\\MAIN_SERVER\Audit\' ) WITH
(ON_FAILURE = SHUTDOWN);
GO
CREATE DATABASE AUDIT SPECIFICATION Audit_Specification FOR SERVER AUDIT Srv_Audit
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP)
WITH (STATE=ON);
GO
ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON);
GO
D. CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH ='\\MAIN_SERVER\Audit\' ) WITH
(ON_FAILURE = SHUTDOWN);
GO
CREATE SERVER AUDIT SPECIFICATION Audit_Specification FOR SERVER AUDIT Srv_Audit
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP)
WITH (STATE=ON);
GO
ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON);
GO
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. You can use several methods of auditing for SQL Server, as described in Auditing (Database Engine). Beginning in SQL Server 2008 Enterprise, you can also set up automatic auditing by using SQL Server Audit. There are several levels of auditing for SQL Server, depending on government or standards requirements for your installation. SQL Server Audit provides the tools and processes you must have to enable, store,
and view audits on various server and database objects.
You can record server audit action groups per-instance, and either database audit action groups or
database audit actions per database. The audit event will occur every time that the auditable action is
encountered.
SQL Server Audit Components
An audit is the combination of several elements into a single package for a specific group of server actions
or database actions. The components of SQL Server Audit combine to produce an output that is called an
audit, just as a report definition combined with graphics and data elements produces a report.
SQL Server Audit uses Extended Events to help create an audit.
SQL Server Audit
The SQL Server Audit object collects a single instance of server or database-level actions and
groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple
audits per SQL Server instance.
When you define an audit, you specify the location for the output of the results. This is the audit
destination. The audit is created in a disabled state, and does not automatically audit any actions.
After the audit is enabled, the audit destination receives data from the audit.
Server Audit Specification
The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope. The server audit specification collects many server-level action groups raised by the Extended Events feature. You can include audit action groups in a server audit specification. Audit action groups are predefined groups of actions, which are the atomic events exposed by the Database Engine. These actions are sent to the audit, which records them in the target. Here only the answer C create Server audit for logins.
QUESTION 155
You work in a company which uses SQL Server 2008. You are the administrator of the company
database.
Now you are in charge of a SQL Server 2008 instance which uses mixed authentication mode. The
instance runs on a computer which has Windows Server 2003 installed. You must make sure that the SQL
Server 2008 authenticated logins follow the same password complexity rules that are enforced by
Windows Server 2003.
Besides this, you must make sure that the password complexity rules continue to be enforced.
So what action should you perform to achieve this goal? (choose more than one)
A. You should create an SQL Server Agent job. If a rule violation is detected, the job runs periodically to raise an alert
B. You should modify all logins by using the ALTER LOGIN ... HASHED statement.
C. You should modify all logins by using the ALTER LOGIN ... CHECK_POLICY = ON statement to.
D. You should modify all logins by using the ALTER LOGIN ... CHECK_EXPIRATION = ON statement.
E. You should create a policy by using Policy-Based Management. The policy prevents any violation of these rules
Correct Answer: CE Section: (none) Explanation
Explanation/Reference:
First, the alter login will change all the created logins to use the policy CHECK_POLICY = { ON | OFF } Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON. Create one policy that prevent the next logins to be created without the option CHECK_POLICY = ON
QUESTION 156
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.
You have set the recovery model of DB1 to Full. The table below shows the backup strategy for DB1.
Your company stores the system databases, user database data, and log files on separate disks. A user
report that the hard disks which store the user database data files have failed at 15:00 on Wednesday.
According to the requirement of the company CIO, you have to restore DB1 from the database backups,
and you must reduce the data loss as soon as possible.
So what action should you perform to achieve this goal?
A. First you should restore the full backup taken on Sunday. Then you restore the differential backup taken on Wednesday.
B. First you should back up the transaction log. Then restore the full backup taken on Sunday. At last restore all transaction log backups
C. First you should back up the transaction log. Second, restore the full backup taken on Sunday. Third, restore the differential backup taken on Tuesday and restore all transaction log backups taken during Wednesday.
D. First you should restore the full backup taken on Sunday. Then restore the differential backup taken on Monday and Tuesday and restore the transaction log backups taken at 08:00 hours and at 12:00 hours on Wednesday.
Correct Answer: C Section: (none) Explanation Explanation/Reference:
1.
As the transaction log is on a separate disk and this disk is still healthy, you can take a backup of the transaction log. This backup will contain all the commited transactions that have been applied to the database since the latest transaction log backup at 12:00.
2.
Restore the full backup of Sunday with NORECOVERY
3.
Restore the differential backup of Tuesday with NORECOVERY
4.
restore all the transactions log backup with NORECOVERY except for the latest you took. For this backup you will use the RECOVERY option.
QUESTION 157
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
Now according to the requirement of the company CIO, users on the SQL Server instance must be able to
use the OPENROWSET() function to query remote data source.
In the options below, which sp_configure configuration option should you configure?
A. Agent XPs
B. remote access
C. remote proc trans
D. Ad Hoc Distributed Queries
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access. Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. For any data sources that will be accessed more than several times, define a linked server.
QUESTION 158
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. The DB1 database contains the following stored
procedure. (Line numbers are useded for reference only.)
01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID('Service.Table') IS NOT NULL
04 DROP TABLE Service.Table;
06 CREATE TABLE Service.Table (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
11 ...
12 GO
The following exception is raised when a user tries to invoke Procedure1, "Msg 262, Level 14, State 1,
Procedure Procedure1, Line 5 CREATE TABLE permission denied in database 'DB1'."
You should grant the user access to execute Procedure1, you must assign only the required permissions.
What action should you do perform?
A. Between lines 01 and 02, you should insert the WITH EXECUTE AS 'dbo' clause.
B. Between lines 01 and 02, you should insert the EXECUTE AS USER = 'dbo' statement.
C. You should give the user the ALTER permission on the Service schema
D. You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[{ @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
][ ,...n ]
[ WITH [ ,...n ]]
[ FOR REPLICATION ]
AS { [;][ ...n ] | }
[;]
::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
::=
{ [ BEGIN ] statements [ END ] }
::=
EXTERNAL NAME assembly_name.class_name.method_name
EXECUTE AS Specifies the security context under which to execute the stored procedure. In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers. By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between userdefined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module. EXECUTE } AS { CALLER | SELF | 'user_name' ' user_name ' Specifies the statements inside the module execute in the context of the user specified in user_name. Permissions for any objects within the module are verified against user_name. user_name cannot be specified for DDL triggers with server scope or logon triggers. Use login_name instead. user_name must exist in the current database and must be a singleton account. user_name cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY \NetworkService, or NT AUTHORITY\LocalSystem.
QUESTION 159
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. In the Services schema, There is a table named Service.
Table. The Services.Table has to be moved from the Sales schema to a new schema named Costs.
In the options below, which Transact-SQL statement should you execute?
A. ALTER TABLE Service.Table SWITCH TO Costs.Table1;
B. ALTER SCHEMA CostsTRANSFER Service.Table;
C. ALTER USER Service WITH DEFAULT_SCHEMA = Costs;
D. ALTER AUTHORIZATION ON Service.Table1 TO Costs;
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
ALTER SCHEMA : Transfers a securable between schemas. ALTER SCHEMA schema_name TRANSFER securable_name schema_name Is the name of a schema in the current database, into which the securable will be moved. Cannot be SYS or INFORMATION_SCHEMA.
securable_name
Is the one-part or two-part name of a schema-contained securable to be moved into the schema. ALTER SCHEMA can only be used to move securables between schemas in the same database. To change or drop a securable within a schema, use the ALTER or DROP statement specific to that securable.
QUESTION 160
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of two SQL Server 2008 instance.
The two instances are respectively named InstanceA and InstanceB. There is a database named Dworks
on InstanceA. You move the Dworks database to InstanceB. A user use a SQL Server login named Mary
to access the database on InstanceA with the password "Tk@87#34". On InstanceB, you create the same
SQL Server login.
The user tries to use the SQL Server login Mark to access the Dworks database on InstanceB. But the
user gets an error message, according to the indication of the message, the access to the Dworks
database has been denied.
You must make sure that the user can access the Dworks database on InstanceB successfully.
So on InstanceB, which Transact-SQL statements should you execute?
A. USE AdventureWorks;ALTER USER Mary WITH LOGIN = Mary;
B. USE AdventureWorks;ALTER LOGIN Mary ENABLE;
C. USE AdventureWorks;ALTER LOGIN Mary WITH PASSWORD = "Tk@87#34" UNLOCK;
D. USE AdventureWorks;ALTER LOGIN Mary WITH DEFAULT_DATABASE = AdventureWorks;
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
ALTER USER : Renames a database user or changes its default schema. ALTER USER userName WITH [ ,...n ] ::= NAME = newUserName | DEFAULT_SCHEMA = schemaName | LOGIN = loginName Arguments
userName
Specifies the name by which the user is identified inside this database.
LOGIN = loginName
Re-maps a user to another login by changing the user's Security Identifier (SID) to match the login's SID.
NAME = newUserName
Specifies the new name for this user. newUserName must not already occur in the current database.
DEFAULT_SCHEMA = schemaName
Specifies the first schema that will be searched by the server when it resolves the names of objects for this
user.
Renames a database user or changes its default schema.
Remarks
If DEFAULT_SCHEMA is left undefined, the user will have dbo as its default schema.
DEFAULT_SCHEMA can be set to a schema that does not currently occur in the database. Therefore, you
can assign a DEFAULT_SCHEMA to a user before that schema is created. DEFAULT_SCHEMA cannot
be specified for a user who is mapped to a Windows group, a certificate, or an asymmetric key.
Important:
The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role.
All members of the sysadmin fixed server role have a default schema of dbo.
You can change the name of a user who is mapped to a Windows login or group only when the SID of the
new user name matches the SID that is recorded in the database. This check helps prevent spoofing of
Windows logins in the database.
The WITH LOGIN clause enables the remapping of a user to a different login. Users without a login, users
mapped to a certificate, or users mapped to an asymmetric key cannot be re-mapped with this clause.
Only SQL users and Windows users (or groups) can be remapped. The WITH LOGIN clause cannot be
used to change the type of user, such as changing a Windows account to a SQL Server login.
The name of the user will be automatically renamed to the login name if the following conditions are true.
The user is a Windows user.
The name is a Windows name (contains a backslash).
No new name was specified.
The current name differs from the login name.
Otherwise, the user will not be renamed unless the caller additionally invokes the NAME clause.
QUESTION 161
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB1 in the instance. VIP customer data of the company are stored in DB1.
The customers access their profile data by using a Web application.
You must ensure the security of the customer data, that is to say, even if the backup media is lost, you
must guarantee that data files, log files and subsequent backups and so on are as safe as possible.
So what action should you perform to achieve this goal?
A. For both the CustomerDB database and the master database, enable Transparent Database Encryption
B. You should use the built-in encryption functions to encrypt the sensitive data at the cell level
C. You should make the CustomerDB database accessible only through stored procedures and functions.
D. For the CustomerDB database, enable Transparent Database Encryption and back up the transaction log.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.
QUESTION 162
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named DB01 in the instance. The company CIO wants all the application developers
can capture traces to troubleshoot the application that uses the database DB1, and they can perform this
by using SQL Server Profiler.
But they have to be given as little permission as possible. The company CIO assigns this task to you.
So what action should you perform to achieve this goal?
A. All the SQL Server logins of the application developers have to be added to a fixed server role.
B. All the database users of the application developers have to be added to a fixed database role
C. All SQL Server logins of the application developers should be given the appropriate server-level permissions.
D. All database users of the application developers should be given the appropriate database-level permissions.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
SQL Server 2005 introduced a new concept to SQL Server security and permissions: securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the server.
ALTER TRACE
Grants or denies the ability to execute a server-side or SQL Server Profiler trace. Without this permission, the only logins capable of running traces are members of the sysadmin fixed server role. http://www.mssqltips.com/tip.asp?tip=1714
QUESTION 163
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 cluster.
According to the company requirement, the failover response of the cluster has to be tested. The company
assigns this task to you. On the cluster, you have to implement a manual failover.
What should you do? (choose more than one)
A. The contents of a shared folder on the active node should be encrypted by using Encrypting File System (EFS)
B. You should restore a backup to the active node
C. You should get rid of the shared array from the active node
D. From the active node, you have to uplug the network cable(s)
Correct Answer: CD Section: (none) Explanation
Explanation/Reference:
To implement a manual failover on one MSCS, you have to interrupt the correct run of the hardware. To do that, you have to break one of the following thinks on the active node: Power plug Heartbeat / Public network cables Storage links (shared array)
QUESTION 164
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance which is named Instance01.
Instance01 contains a database which is named Dworks. The bulk-logged recovery mode is used by the
Dworks database. You log ship the Dworks database through a WAN link. A scheduled job rebuilds the
indexes of the Dworks database. You find that the size of the log backups for log shipping has been
enlarged by the job.
Now you have to reduce the size of the log backup.
So what action should you perform to achieve this goal?
A. You should recreate the indexes.
B. You should compress the log file backups
C. You should make the Dworks database use the Full recovery mode
D. You should make the Dworks database use the Simple recovery mode.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Use Compression option for the backup log. This will reduce the size of the database log backups COMPRESSION In SQL Server 2008 Enterprise and later versions only, specifies whether backup compression is performed on this backup, overriding the server-level default. At installation, the default behavior is no backup compression. But this default can be changed by setting the backup compression default server configuration option. For information about viewing the current value of this option, see How to: View Server Properties (SQL Server Management Studio). COMPRESSION Explicitly enables backup compression.
Note:
By default, when a backup is compressed, checksums are performed to detect media corruptions.
QUESTION 165
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance which is named Instance01.
Instance01 contains a database which is named SellingHelp. There isthe Products table in the SellingHelp
database. Now according to the company requirement, the Products table has to be replicated to the SQL
Server instances installed in laptops computers. You have to create a Replication topology to perform this.
The Products table is updated from Instance01 when the laptops reconnect to the corporate network.
The Products table on Instance01 is frequently updated between reconnections.
You must make sure that you can implement the Replication topology along with the Subscription type
successfully and meanwhile reduce the bandwidth usage to the least.
So what action should you perform to achieve this goal?
A. You should implement the Snapshot Replication topology along with a Push Subscription.
B. You should implement the Merge Replication topology along with a Pull Subscription.
C. You should implement the Snapshot Replication topology along with a Pull Subscription.
D. You should implement the Transactional Replication topology along with a Pull Subscription
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred. Merge replication is typically used in server-to-client environments.
QUESTION 166
You work for a company which uses SQL Server 2008. You are the administrator of the company
database and in charge of three SQL Server 2008 instances.
All the instances run on their independent server. The three instances are respectively named InstanceA,
InstanceB, and InstanceC. Between InstanceA and InstanceB, you configure a mirroring mission-critcal
database.
InstanceA currently acts as the Principal and InstanceC acts as the witness.
You intend to apply a patch to both servers as soon as possible, the patch needs to restart the server.
You must make sure that the database is online on the partner that currently does not apply the patch and
during this time the database does not failover to the other partner.
So what action should you perform to achieve this goal?
A. First, you should stop the mirroring session.
Second you apply the patch to the server that runs InstanceB.
Third, you should resume the mirroring session.
Fourth, you should manually failover the mirroring session.
At last, you should apply the patch to the server that runs InstanceA.
B. First, you should apply the patch to the server that runs InstanceB.
Second you apply the patch to the server that runs InstanceA.
C. First, you should apply the patch to the server that runs InstanceB.
Second you failover the mirroring session manually.
At last apply the patch to the server that runs InstanceA
D. First, you should remove the mirroring session.
Second you apply the patch to the server that runs InstanceB.
Third, you should apply the patch to the server that runs InstanceA.
At last re-establish the mirroring session.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
1.
Status= SUSPENDED: The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.
2.
Patch the target server
3.
resume the mirrorin, in order to have the SYNCHRONIZED status
4.
Make a failover from the source Instance A to InstanceB, the already patched server.
5.
Apply the patch to the Instance A
QUESTION 167
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance. In the database, a small percentage of data is modified everyday. Ever day in the morning, you have to create a read-only copy of the database for reporting purposes and make sure that you use as little disk space as possible. In the options below, which strategy should you use?
A. You should use database snapshots
B. You should use database backup and restore
C. You should use database backup and restore along with compression
D. You should use schema-bound views in a read-only database. The database resides on the same instance.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
This topic describes the database snapshot feature, which was new in Microsoft SQL Server 2005. Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots. A database snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A snapshot persists until it is explicitly dropped by the database owner. Unlike default behavior for user databases, a database snapshot is created with the ALLOW_SNAPSHOT_ISOLATION database option set ON regardless of the setting of this option on the primary database or the model system database. Snapshots can be used for reporting purposes. Also, in the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.
QUESTION 168
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database. Now you are in charge of a SQL Server 2008 instance.
There is a text file which is named SQL01.log on the E: drive. A SQL Server Agent job executes every 2
minutes and logs information to the text file.
You get a report from users saying that the sever doesn't make response. As the technical support, you
check and find that the SQL Server Agent service does not run and the SQL Server Agent job no longer
functions.
You have to identify the reason why the SQL Server Agent service is unresponsive.
In the options below, which log should you check?
A. You should check SQL1.log
B. You should check SQLAGENT.OUT
C. You should check log_xx.trc
D. You should check ERRORLOG
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
All the informations about SQL Server Agent error could be found in the SQLAGENT.OUT file.
QUESTION 169
You work in a company which uses SQL Server 2008. You are the administrator of the company
database.
Now you are in charge of a SQL Server 2008 instance which contains a database.
According to the company requirement, you have to move the application from Microsoft SQL Server 2000
to Microsoft SQL Server 2008.
You have to monitor the SQL Server instance to record the use of features. These features will be
discontinued.
What should you do?
A. The SQL Server 2008 Upgrade Advisor should be used.
B. The SQL Server Profiler which captures the SQL:BatchCompleted and Exception event classes should be used.
C. A SQL server-side trace that captures the Deprecation Announcement and Deprecation Final Support event classes should be used
D. A SQL server-side trace that captures the SQL:BatchCompleted and Exception event classes should be used.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
The Deprecation Final Support event class occurs when you use a feature that will be removed from the
next major release of SQL Server. For greatest longevity of your applications, do not use features that
cause the Deprecation Final Support event class or the Deprecation Announcement event class.
Modify applications that use final deprecation features as soon as possible.
QUESTION 170
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 cluster. According to the company
requirement, the failover response of the cluster has to be tested. The company assigns this task to you.
On the cluster, you have to implement a manual failover. What should you do? (choose
more than one)
A. The contents of a shared folder on the active node should be encrypted by using Encrypting File System (EFS)
B. You should restore a backup to the active node
C. You should get rid of the shared array from the active node
D. From the active node, you have to uplug the network cable(s)
Correct Answer: CD Section: (none) Explanation
Explanation/Reference:
To implement a manual failover on one MSCS, you have to interrupt the correct run of the hardware. To do that, you have to break one of the following thinks on the active node: Power plug Heartbeat / Public network cables Storage links (shared array)
QUESTION 171
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is an Internet Information Services
application. This application will use anonymous access to access the SSAS instance.
You must make sure that the application can access the SSAS instance.
What should you do?
A. The Security\RequireClientAuthentication server configuration should be set to False
B. The Security\RequireClientAuthentication server configuration should be set to True.
C. The NTLM Security Support Provider Interface (SSPI) provider should be added to the Security \SecurityPackageList server configuration.
D. The Kerberos Security Support Provider Interface (SSPI) provider should be added to the Security \SecurityPackageList server configuration
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
Microsoft SQL Server Analysis Services supports the security server properties listed in the following table. To view or change these properties for an instance of Analysis Services, in SQL Server Management Studio, right-click the Analysis Services instance, and then click Properties. Properties RequireClientAuthentication
A Boolean property that indicates whether client authentication is required. The default value for this property is True, which indicates that client authentication is required. In this case, the authentication should be anonymous, thus the property should be set to False
QUESTION 172
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
A SQL Server Agent job is failing.
You find that the job history information is incomplete and seems to be truncated after you review it. All
information produced by a job must be available for viewing.
So what action should you perform to achieve this goal?
A. You should enable notifications to the Windows application event log when the job completes.
B. You should enable write OEM file
C. You should enable all job steps, making them send the output to a file
D. You should include execution trace messages in the SQL Agent Error log
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
In all the jobsteps, go to the option panel and select Output file to get all info from the step. Note that you
can append or overide the file. All job steps should be adapted with the option.
Output file Sets the file to use for output from the job step.
QUESTION 173
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 SQL 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 Merge 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 Transactional Replication topology along with a Pull Subscription.
Correct Answer: A Section: (none) Explanation Explanation/Reference:
QUESTION 174
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 replicated.
What should you do?
A. Recreate the publication.
B. Upload unsynchronized changes.
C. Upload unsynchronized changes, and then reinitialize the publication.
D. Reinitialize the publication and immediately generate a new snapshot.
Correct Answer: Section: (none) Explanation
Explanation/Reference:
QUESTION 175
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 presents a complete solution.
Choose two.)
A. Restore a backup to the active node.
B. Remove the shared array from the active node.
C. Unplug 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: BD Section: (none) Explanation
Explanation/Reference:
QUESTION 176
You administer three SQL Server 2008 instances named Instance1, Instance2, and Instance3.
Each of the three instances runs on a separate server. A mission-critical database is mirrored between
Instance1 and Instance2. Instance3 acts as the witness. Instance1 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:
1.
The patching process is completed in the shortest possible time.
2.
The database is online on the partner that currently does not apply the patch.
3.
The database does not failover to the other partner during this time.
What should you do?
A. Apply the patch to the server that runs Instance2.
Apply the patch to the server that runs Instance1.
B. Apply the patch to the server that runs Instance2.
Manually failover the mirroring session.
Apply the patch to the server that runs Instance1.
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. 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 Instance1.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 177
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:
1.
The data collector is used to gather performance information.
2.
A single database stores performance information for all instances.
3.
Performance information that is older than 14 days is deleted.
4.
Administrative effort to manage performance data is minimized. What should you do?
A. Create and schedule a single Microsoft SQL Service Integration Services (SSIS) package process to store and delete performance data in a single database for all instances.
B. Create a SQL Agent job process on each instance to store and delete performance data in a single database for all instances.
C. Configure a management data warehouse process on each instance to store and delete performance data in a single database for all instances.
D. Configure an automated server-side trace process on each instance to store and delete performance data in a single database for all instances.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 178
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 cached data collection.
B. Create a scheduled non-cached data collection.
C. Create an on-demand non-cached data collection.
D. 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.
Correct Answer: A Section: (none) Explanation
Explanation/Reference: QUESTION 179
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 the data collector to recapture the workload.
B. Use SQL Profiler to recapture the workload to a trace file.
C. Enable the XP_MSVER stored procedure on the local server.
D. Enable the XP_MSVER stored procedure on the remote server.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 180
You administer a SQL Server 2008 instance.
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 Windows System Monitor tool
B. The Database Engine Tuning Advisor tool
C. The Activity Monitor tool in Microsoft SQL Server Management Studio
D. The Job Activity Monitor tool in Microsoft SQL Server Management Studio
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 181
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 file server.
Which account type should you use?
A. Domain account
B. Local System account
C. Local Service account
D. Network Service account
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 182
You administer a SQL Server 2008 instance that contains a database named Sales.
The Sales database has a table named Products that stores information about all types of products.
Users frequently query the Products table based on the TelevisionSize column. The TelevisionSize column
has the NULL value for all products other than Televisions. There is currently no index on the
TelevisionSize column.
You need to improve the query performance by ensuring that the effect on the disk space is minimized.
What should you do?
A. Create a filtered index on the TelevisionSize column.
B. Create a clustered index on the TelevisionSize column.
C. Create a unique clustered index on the TelevisionSize column.
D. Create a view on the Products table by filtering on the TelevisionSize column.
Correct Answer: A Section: (none) Explanation
Explanation/Reference: QUESTION 183
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. Page compression
C. Backup compression
D. Windows NTFS file system compression
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 184
You maintain a SQL Server 2008 instance that contains a database named AdventureWorks. The AdventureWorks database contains two tables as shown in the following table:
1.
The Orders table contains a table partition for each month.
2.
The OrderHistory table contains two table partitions; one partition contains all data and the other
partition is empty.
3.
You plan to develop a process to move data from the partition that contains data for the oldest month of
the Orders table to the appropriate partition of the OrderHistory table.
4.
You need to ensure that the process can be repeated at the end of every month.
What should you do?
A. Alter the partition function of the Orders table by using the split option.
Alter the Orders table by using the merge option.
B. Alter the partition function of the OrderHistory table by using the split option.
Switch the appropriate partition of the Orders table to the appropriate partition of the
OrdersHistory table.
Alter the OrderHistory table by using the split option.
C. Alter the partition function of the Orders table by using the split option.
Alter the OrderHistory table by using the split option.
Alter the partition function of the OrderHistory table by using the merge option
Alter the Orders table by using the merge option.
D. Alter the partition function of the OrderHistory table by using the split option.
Switch the appropriate partition of the Orders table to the appropriate partition of the OrdersHistory
table.
Alter the OrderHistory table by using the merge option.
Alter the Orders table by using the merge option.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 185
You maintain a SQL Server 2008 instance that contains a database named Finance.
A smallpercentage 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.
C. Use database backup and restore along with compression.
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 186
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 full recovery model
B. Verify that the database uses the simple recovery model.
C. Verify that the database uses the bulk-logged recovery model.
D. Verify that the database uses the checksum page verify option.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 187
You maintain a SQL Server 2008 instance that contains a database named Finance.
TheFinance 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 set to 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.
B. Perform a full database backup once daily.
Perform a differential backup every two hours during business hours.
C. Perform 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: B Section: (none) Explanation
Explanation/Reference:
QUESTION 188
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.
You deploy a new process that modifies 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: Section: (none) Explanation
Explanation/Reference:
QUESTION 189
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. Agent XPs
B. Remote access
C. Remote proc trans
D. Ad Hoc Distributed Queries
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 190
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 SCHEMA Billing TRANSFER Sales.Table1;
B. ALTER USER Sales WITH DEFAULT_SCHEMA = Billing;
C. ALTER AUTHORIZATION ON Sales.Table1 TO Billing;
D. ALTER TABLE Sales.Table1 SWITCH TO Billing.Table1;
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 191
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 Sales.Procedure1
02 AS
03 IF OBJECT_ID('Sales.Table1') IS NOT NULL
04 DROP TABLE Sales.Table1;
05
06 CREATE TABLE Sales.Table1 (
07 Id int PRIMARY KEY CLUSTERED,
08 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 5
CREATE 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. Grant the ALTER permission on the Sales schema to User1.
B. Grant the CREATE TABLE permission and allow User1 to drop the Sales.Table1 table.
C. Insert the WITH EXECUTE AS 'dbo' clause between lines 01 and 02.
D. Insert the EXECUTE AS USER = 'dbo' statement between lines 02 and 03.
Correct Answer: C Section: (none) Explanation
Explanation/Reference: QUESTION 192
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 Server 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 modify 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 193
You maintain a SQL Server 2008 instance.
You create a new SQL Agent job that includes a Windows PowerShell job step. The PowerShell job step
uses the SQLCmd utility to transfer data between servers.
You need to ensure that an operator named ResponseTeam is notified by an e-mail message if the job
fails.
What should you do?
A. Enable Notifications.
Create the ResponseTeam operator.
Direct the Notification to the ResponseTeam operator on job failure.
B. Enable Notifications.
Create the ResponseTeam operator.
Direct the Notification to the ResponseTeam operator on failure of the Powershell job step.
C. Create the ResponseTeam operator.
Assign the ResponseTeam operator as the fail safe operator.
Enable the job.
D. 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.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 194
You work for a company which uses SQL Server 2008. You are the administrator of the company database and in charge of three SQL Server 2008 instances. All the instances run on their independent server. The three instances are respectively named InstanceA, InstanceB, and InstanceC. Between InstanceA and InstanceB, you configure a mirroring mission-critcal database. InstanceA currently acts as the Principal and InstanceC acts as the witness. You intend to apply a patch to both servers as soon as possible, the patch needs to restart the server. You must make sure that the database is online on the partner that currently does not apply the patch and during this time the database does not failover to the other partner. So what action should you perform to achieve this goal?
A. First, you should stop the mirroring session. Second you apply the patch to the server that runs InstanceB. Third, you should resume the mirroring session. Fourth, you should manually failover the mirroring session. At last, you should apply the patch to the server that runs Instance1.
B. First, you should apply the patch to the server that runs InstanceB. Second you apply the patch to the server that runs InstanceA.
C. First, you should apply the patch to the server that runs InstanceB. Second you failover the mirroring session manually. At last apply the patch to the server that runs InstanceA
D. First, you should remove the mirroring session. Second you apply the patch to the server that runs InstanceB. Third, you should apply the patch to the server that runs Instance1. At last re-establish the mirroring session.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 195
You work in a company which uses SQL Server 2008. You are the administrator of the company
database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance. In
the Services schema, There is a table named Service.Table. The Services.Table has to be moved from the
Sales schema to a new schema named Costs.
In the options below, which Transact-SQL statement should you execute?
A. ALTER TABLE Service.Table SWITCH TO Costs.Table1;
B. ALTER SCHEMA CostsTRANSFER Service.Table;
C. ALTER USER Service WITH DEFAULT_SCHEMA = Costs;
D. ALTER AUTHORIZATION ON Service.Table1 TO Costs;
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 196
You work in a company which uses SQL Server 2008. You are the administrator of the company
database.
Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.
The DB1 database contains the following stored procedure.
(Line numbers are useded for reference only.)
01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID('Service.Table') IS NOT NULL
04 DROP TABLE Service.Table;
05
06 CREATE TABLE Service.Table (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
10
11 ...
12 GO
The following exception is raised when a user tries to invoke Procedure1,
"Msg 262, Level 14, State 1, Procedure Procedure1, Line 5
CREATE TABLE permission denied in database 'DB1'."
You should grant the user access to execute Procedure1, you must assign only the required permissions.
What action should you do perform?
A. Between lines 01 and 02, you should insert the WITH EXECUTE AS 'dbo' clause.
B. Between lines 01 and 02, you should insert the EXECUTE AS USER = 'dbo' statement.
C. You should give the user the ALTER permission on the Service schema
D. You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 197
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named named Dworks in the instance. The table below shows the two tables of the
Dworks database: There are two partitions in the BillHistory table. Partition1 is empty while partition2
contains all data. Now you intend to move data from partition2 to the appropriate partition of the BillHistory
table. Partions2 contains data for the oldest month of the Bills table.
You are going to develop a process to achieve this.
You must make sure that at the end of every month the process can be repeated.
A. Alter the partition function of the BillHistory table by using the split option.
Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table.
Alter the BillHistory table by using the split option.
B. Alter the partition function of the Bills table by using the split option.
Alter the Billstable by using the merge option.
C. Alter the partition function of the BillHistory table by using the split option.
Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table.
Alter the BillHistory table by using the merge option. Alter the Bills table by using the merge option.
D. Alter the partition function of the Bills table by using the split option.
Alter the BillHistory table by using the split option.
Alter the partition function of the BillHistory table by using the merge option.
Alter the Bill stable by using the merge option.
Correct Answer: C Section: (none) Explanation
Explanation/Reference: QUESTION 198
You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a remote SQL Server 2008 instance.
You notice that the application has a poor performance. On the remote SQL Server instance, you capture
a workload of the remote instance to a trace table by using SQL Profiler. Now you have to use the
Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local
SQL Server instance.
So what action should you perform to achieve this goal?
A. You should recapture the workload by using the data collector.
B. You should enable the XP_MSVER stored procedure on the local server.
C. You should enable the XP_MSVER stored procedure on the remote server
D. You should recapture the workload to a trace file by using SQL Profiler.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 199
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. Now your company CIO assigns a task to you.
The company CIO wants you to configure FILESTREAM data and the two requirements below must be
met: you must enable FILESTREAM for file I/O streaming access; remote client computers must be able to
have streaming access to FILESTREAM data.
You must make sure that FILESTREAM data is enabled.
Which service should you configure?
A. You should configure SQL Server VSS Writer
B. You should configure Distributed File System
C. You should configure SQL Server
D. You should configure SQL Server Full Text
Correct Answer: C Section: (none) Explanation Explanation/Reference:
Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL
Server Database Engine.
This topic describes how to enable FILESTREAM by using SQL Server Configuration Manager.
To enable and change FILESTREAM settings
On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to
Configuration Tools, and then click SQL Server Configuration Manager.
In the list of services, right-click SQL Server Services, and then click Open.
In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you
want to enable FILESTREAM.
Right-click the instance, and then click Properties.
In the SQL Server Properties dialog box, click the FILESTREAM tab.
Select the Enable FILESTREAM for Transact-SQL access check box.
If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O
streaming access. Enter the name of the Windows share in the Windows Share Name box.
If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote
clients to have streaming access to FILESTREAM data.
Click Apply.
In SQL Server Management Studio, click New Query to display the Query Editor.
In Query Editor, enter the following Transact-SQL code:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
QUESTION 200
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database named Dworks in the
instance. The Dworks database has the Products table.
Now you have to replicate the Products table to the SQL Server instances at remote locations, so you
create a Merge Replication topology and a Publication to do this.
The Publication has a 21-day retention period. There is a user who goes on a business trip, 30 days later
he comes back and finds that data in his database is not the latest.
The Windows Event log states the following error message:
"Replication: expired subscription dropped."
You must get the latest data in the user's database and make sure that future data changes are
appropriately replicated.
So what action should you perform to achieve this goal?
A. You should recreate the publication
B. You should upload unsynchronized changes.
C. You should reinitialize the publication and generate a new snapshot at once.
D. You should upload unsynchronized changes, and then reinitialize the publication
Correct Answer: C Section: (none) Explanation
Explanation/Reference: Merge replication uses the publication retention period (the @retention and @retention_period_unit parameters of sp_addmergepublication (Transact-SQL)). When a subscription expires, it must be reinitialized, because metadata for the subscription is removed. Subscriptions that are not reinitialized are dropped by the Expired subscription clean up job that runs on the Publisher. By default, this job runs daily; it removes all push subscriptions that have not synchronized for double the length of the publication retention period. For example: If a publication has a retention period of 14 days, a subscription can expire if it has not synchronized within 14 days. If the Publisher is running SQL Server 2005 or a later version and the agent for the subscription is from SQL Server 2005 or a later version, a subscription only expires if there have been changes to the data in that subscription's partition. For example, suppose a Subscriber receives customer data only for customers in Germany. If the retention period is set to 14 days, the subscription expires on day 14 only if there have been changes to the German customer data in the last 14 days. From 14 days to 27 days after the last synchronization, the subscription can be reinitialized. At 28 days after the last synchronization, the subscription is dropped by the Expired subscription clean up job. If a push subscription expires, it is completely removed, but pull subscriptions are not. You must clean up pull subscriptions at the Subscriber. For more information, see How to: Delete a Pull Subscription (Replication Transact-SQL Programming).
QUESTION 201
You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the
administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. There is a database developer who is named
UserJack.
UserJack views the definitions of all database objects in a database to read data from all user-defined
tables, views, and table-valued functions. For UserJack, you have to assign the required permissions.
Besides this, you must make sure that other developers can also be given the same permissions, but this
should be achieved by executing as little Transact-SQL statements as possible.
In the options below, which Transact-SQL statements should you execute?
A. GRANT VIEW ANY DEFINITION TO UserJack;EXEC sp_addrolemember 'db_datareader', 'UserJack';
B. CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addrolemember 'Developers', 'UserJack';
C. CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;EXEC sp_addrolemember 'Developers', 'UserJack';
D. CREATE ROLE Developers;EXEC sp_addrolemember 'sp_dbdatareader', 'Developers';EXEC sp_addrolemember 'sp_dbddladmin', 'Developers';EXEC sp_addrolemember 'Developers', 'UserJack';
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
1.
Create ROLE: Roles are database-level securables. After you create a role, configure the databaselevel permissions of the role by using GRANT, DENY, and REVOKE. To add members to a database role, use the sp_addrolemember stored procedure.
2.
VIEW DEFINITION:The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.
3.
GRANT SELECT: Grants permissions on a securable to a principal.
QUESTION 202
You work as a database administrator at ABC.com. ABC.com is planning to migrate a SQL Server 2000
application to SQL Server 2008 R2.
After the migration, you want to keep an eye on the performance of the SQL Server 2008 R2 instance. You
then configure a SQL server-side trace that captures the Deprecation Announcement and Deprecation
Final Support event classes.
Which two of the following best describes these two event classes respectively? (Choose two.)
A. The Deprecation Final Support event class reveals that a feature will be removed in the next important release of SQL Server.
B. The Deprecation Announcement event class reveals that a feature will be removed in the next important release of SQL Server.
C. The Deprecation Announcement event class reveals that a feature will be removed in an upcoming edition of SQL Server.
D. The Deprecation Final Support event class reveals that a feature will be removed in an upcoming edition of SQL Server.
Correct Answer: AC Section: (none) Explanation
Explanation/Reference:
QUESTION 203
You work as a database administrator at ABC.com. ABC.com makes use of a SQL Server 2008 R2
instance that includes an On-Line Analytical Processing (OLAP) database.
The OLAP database hosts a dimension table, named ABCClients, which has hourly information updates
configured. As a result, ABCClients has a large quantity of information that is no longer used.
You have been instructed to make sure that the amount of space used by the ABCClients table is reduced
with as little administrative effort as possible.
Which of the following actions should you take?
A. You should consider manually deleting the information that is no longer used.
B. You should consider making use of page compression.
C. You should consider making use of the Disk Cleanup Wizard.
D. You should consider making use of folder compression.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 204
You work as a database administrator at ABC.com. ABC.com is making use of SQL Server 2008 R2.
ABC.com has a server running an instance of SQL Server 2008 R2, which hosts a database named
ABC_Clients.
When ABC_Clients encounter numerous deadlock issues, you configure the instance��s startup parameters
with the necessary trace flags and reboot the instance.
Which of the following is TRUE regarding this configuration?
A. It allows for the capturing of deadlock data in the SQL Server error log.
B. It allows for the capturing of deadlock data in the SQL Server event log.
C. It allows for the capturing of deadlock data in the SQL Server trace log.
D. It allows for the capturing of deadlock data in the SQL Server transaction log.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 205
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 R2 instance.
The SQL Server 2008 R2 instance contains a database named ABC_DB.
You have employed the SQL Server service to configure the FILESTREAM data container in the ABC_DB
database.
Which of the following options are TRUE with regards to FILESTREAM data? (Choose all that apply.)
A. FILESTREAM data is not encrypted even though you have enabled transparent data encryption.
B. FILESTREAM data is only encrypted when you have transparent data encryption enabled.
C. FILESTREAM data must be stored in FILESTREAM filegroups contains file system directories instead of the files themselves.
D. FILESTREAM data must be stored in a stored procedure.
Correct Answer: AC Section: (none) Explanation
Explanation/Reference:
QUESTION 206
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 R2 instance.
You have been instructed to make sure that all ABC.com��s users, including managers, receive daily
reports via Database Mail. You are then informed that your solution should allow for managers to receive
the report as soon as possible.
Which of the following actions should you take?
A. You should consider configuring two separate Database Mail profiles for users and managers.
B. You should configure a Database Mail profile for users, and personally send each manager the report.
C. You should consider configuring two separate Database Mail accounts for users and managers.
D. You should consider configuring a single Database Mail profile, and sending the reports as high priority.
Correct Answer: B Section: (none) Explanation Explanation/Reference:
Database Mail allows you to create multiple profiles within a SQL Server instance. Optionally, you can
choose the profile that Database Mail uses when you send a message.
By using different profiles you can prioritize among the messages that are being sent.
QUESTION 207
You work as a database administrator at ABC.com.ABC.com makes use of SQL Server 2008 R2. ABC.com has three database servers, named ABC-SR13, ABC-SR14, and ABC-SR15, which are each running a different instance of SQL Server 2008. You have configured a database mirroring session in high-safety mode between ABC-SR13 and ABC-SR14 for an important ABC.com database. ABC-SR13 is presently configured as the principal server, while ABC-SR14 is configured as the mirror server. ABCSR15 is configured as the witness server. Which of the following is TRUE with regards to database mirroring? (Choose all that apply.)
A. You are able to mirror any database.
B. Mirroring of the master, msdb, tempdb, or model databases is not supported.
C. Database mirroring is not supported by the simple and bulk-logged recovery models.
D. The full, simple, and bulk-logged recovery models support database mirroring.
Correct Answer: BC Section: (none) Explanation
Explanation/Reference:
QUESTION 208
You work as a database administrator at ABC.com. ABC.com makes use of a SQL Server 2008 R2.
A database is relocated from one instance of SQL Server 2008 R2 to a different instance. A ABC.com user
previously accessed the database using specific credentials, and would like to make use of those same
credentials to access the database.
Which of the following actions should you take?
A. You should consider making use of the ALTER USER Transact-SQL statement that includes the WITH LOGIN argument.
B. You should consider making use of ALTER DATABASE Transact-SQL statement that includes the WITH LOGIN argument.
C. You should consider making use of the CREATE LOGIN Transact-SQL statement.
D. You should consider making use of the CREATE USER Transact-SQL statement.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 209
You work as a database administrator at ABC.com. ABC.com makes use of a SQL Server 2008 R2
instance.
You have previously devised a maintenance strategy that allows for the reconstruction of indexes, as well
as database integrity verification. The strategy is also configured to generate reports in the form of a text
file.
You have now been instructed to configure the strategy to forward e-mails to ABC.com operators
whenever the strategy is unsuccessfully implemented.
Which of the following actions should you take?
A. You should consider altering the Event log.
B. You should consider altering the SQL Active Directory Helper.
C. You should consider altering the SQL Server Agent job.
D. You should consider altering the SQL Server Browser.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 210
You work as a database administrator at ABC.com. ABC.com makes use of SQL Server 2000 instance.
A current specific job is configured to retrieve a file remotely from a shared network folder via a CMDExec
step. The use of the LocalSystem account has been configured for the SQL Server Agent service.
You receive an instruction to upgrade the current SQL Server instance to SQL Server 2008 R2.
Subsequent to the upgrade, you notice that the job step is unable to finish its process.
It is imperative that this issue be rectified.
Which of the following actions should you take?
A. You should consider reconfiguring the job step to make use of a remote System account.
B. You should consider reconfiguring the job step to make use of a Local Service account.
C. You should consider reconfiguring the job step to make use of a proxy account.
D. You should consider reconfiguring the job step to make use of a Domain account.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 211
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 R2 instance that
includes a database named ABCSales.
Minimal changes to the information in ABCSales take place on a daily basis. You have been instructed to
devise a solution that produces for a read-only copy of the database. Furthermore, the solution has to run
every morning and should consume as little disk resources as possible.
Which of the following actions should you take?
A. You should consider configuring the use of database snapshots.
B. You should consider configuring the use of transaction log backups.
C. You should consider configuring the use of differential backups.
D. You should consider configuring the use of incremental backups.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 212
You work as a database administrator at ABC.com. You are responsible for managing a SQL Server 2008
R2 instance.
ABC.com has an Internet Information Services (IIS) application that should be able to log on to the SQL
Server Analysis Services (SSAS) instance anonymously.
Which of the following actions should you take?
A. You should consider having the Security\RequireClientAuthentication server configuration property disabled.
B. You should consider having the Security\RequireClientAuthentication server configuration property enabled.
C. You should consider configuring the Security\RequireServerAuthenticationserver configuration property with the default value.
D. You should consider configuring the Security\RequireClientAuthentication server configuration property with the default value.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 213
You work as a database administrator at ABC.com. ABC.com makes use of a SQL Server 2008 R2
instance.
You have been instructed to make sure that all stored procedures that are defined by ABC.com��s users
make use of the prefix usp_. You are also instructed to make sure that administrators are prevented from
creating stored procedures that do not make use of the prefix usp_.
Which of the following actions should you take? (Choose all that apply.)
A. You should consider generating a policy that targets the name of the stored procedure, which is evaluated on change.
B. You should consider generating a condition that targets the name of the stored procedure, which is evaluated on change.
C. You should consider assigning minimum permissions to all administrators.
D. You should consider creating a trigger.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 214
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 R2 instance that
contains a database named ABCSales.
Minimal changes to the information in ABCSales take place on a daily basis. You have been instructed to
devise a solution that minimizes administrative overhead for the transaction log of ABCSales.
Which of the following actions should you take?
A. You should consider making having the full recovery model configured for ABCSales.
B. You should consider having the simple recovery model is configured for ABCSales.
C. You should consider running the SQL Server Profiler on ABCSales.
D. You should consider configuring ABCSales to make use of the checksum page verify option.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 215
You work as a database administrator at ABC.com.ABC.com is making use SQL Server 2008 R2 in their
environment.
ABC.com has a server, named ABC-SR07, which runs an instance of SQL Server 2008 R2 and has
multiple applications installed.
You have configured the SQL Server Agent service to make use of the ABC-SR07/ACCOUNT. You then
configured a job that must be written to a database server. You now need to make sure that only the SQL
Server Agent service has read and write permissions to the database server.
Which of the following actions should you take?
A. You should consider configuring the SQL Server Agent service to make use of a global Administrator account.
B. You should consider configuring the SQL Server Agent service to make use of a proxy account.
C. You should consider configuring the SQL Server Agent service to make use of a Local Service account.
D. You should consider configuring the SQL Server Agent service to make use of a remote System account.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 216
You work as a database administrator at ABC.com.ABC.com is making use of a SQL Server 2008 R2
instance that contains a database named ABC_Prod1.
You have been instructed to devise a strategy that produces a read-only duplication of ABC_Prod1 at the
start of each business day. This duplication will be used as a reporting tool. Your strategy should also have
a minimal effect on ABC_Prod1.
Which of the following actions should you take?
A. You should consider configuring the use of the simple recovery model.
B. You should consider frequent backups of the event log.
C. You should consider making use of the database snapshots.
D. You should consider configuring the use of transaction log backups that runs frequently.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 217
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 R2 instance that
contains a database named ABCSales.
Minimal changes to the information in ABCSales take place on a daily basis. You have been instructed to
devise a solution that fully logs all transactions and keeps the transaction log records until after they are
backed up. The solution should also allow for ABCSales to be restored to the point of failure.
Which of the following actions should you take?
A. You should consider having the full recovery model configured for ABCSales.
B. You should consider having the simple recovery model is configured for ABCSales.
C. You should consider running the SQL Server Profiler on ABCSales.
D. You should consider configuring ABCSales to make use of the checksum page verify option.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 218
You work as a database administrator at ABC.com. ABC.com has recently upgraded to SQL Server 2008
R2.
You have been instructed to have an existing SQL Server 6.0 database, named ABCSales, upgraded to an
instance of SQL Server 2008 R2. You have to make sure that any questionable pages in ABCSales can be
identified at an early stage.
Which of the following actions should you take?
A. You should consider configuring ABCSales to make use of the checksum page verify option.
B. You should consider running the SQL Server Profiler on ABCSales.
C. You should consider having the simple recovery model is configured for ABCSales.
D. You should consider configuring ABCSales to make use of the torn page detection page verify option.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 219
You work as a database administrator at ABC.com.ABC.com is making use of a SQL Server 2008 R2
instance.
You monitor the transaction log of the instance on a daily basis. On this particular day you are presented
with information regarding torn page errors in the database.
You have been instructed to rectify these errors as soon as possible.
Which of the following actions should you take? (Choose two.)
A. You should consider restoring only the torn pages from the most recent full database backup.
B. You should consider restoring the full database from the most recent full database backup.
C. You should consider restoring the most recent snapshot backups.
D. You should consider restoring the transaction logs that was created since the full backup.
Correct Answer: AD Section: (none) Explanation
Explanation/Reference:
QUESTION 220
You work as a database administrator at ABC.com.ABC.com is making use of a SQL Server 2008 R2
instance.
You have previously configured a SQL Server 2008 R2 cluster. You then disconnect the network cable(s)
from the active node, as well as delete the shared array from the active node.
Which of the following describes a reason for doing this?
A. To induce an automatic failover.
B. To induce a manual failover.
C. To improve cluster performance.
D. Remove mirroring from the database.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 221
You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2 instance. The SQL Server 2008 R2 instance includes a database that has a table, named ABC_Tbl, which is partitioned on the ProID column. The partitioning characteristics are as follows:
.
Partition 1 includes integer values between 5 and 30,000.
.
Partition 2 includes integer values in excess of 30,000.
Due to the growth in business operations, ABC.com foresees the need to have a third partition in the table.
You receive instructions to create a third partition on the table. This partition must be able to include the
integer values in excess of 60,000.
You decide to make use of an ALTER PARTITION FUNCTION Transact-SQL statement.
Which of the following is TRUE with regards to this Transact-SQL statement? (Choose all that apply.)
A.
It can only be used for splitting one partition into two, or merging two partitions into one.
B.
It adds a filegroup to a partition scheme or alters the designation of the NEXT USED filegroup for the
partition scheme.
C.
Filegroups that are affected by ALTER PARITITION FUNCTION does not have to be online.
D.
ALTER PARTITION FUNCTION continues to process, even if there is a disabled clustered index on
any tables that use the partition function.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 222
You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2 instance that
contains a database named ABC_Prod.
You have been instructed to join two tables on a column called ABC_Common. You have to ensure that
the values in the columns being joined are compared using a comparison operator.
Which of the following actions should you take?
A. You should consider making use of a full outer join
B. You should consider making use of an right outer join
C. You should consider making use of a cross join
D. You should consider making use of an inner join.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 223
You work as a database administrator at ABC.com.ABC.com is making use of a SQL Server 2008 R2
instance to manage and maintain its databases.
The SQL Server 2008 instance includes a database that has a table, named ABC_Tbl. You then configure
ABC_Tbl to include a Geography data type column, named ABC_Colmn.
You have been instructed to devise a solution that makes allowance for ABC_Colmn to include a spatial
index.
Which of the following actions should you take?
A. You should consider configuring ABC_Colmn with a primary key.
B. You should consider configuring ABC_Tbl with a primary key.
C. You should consider configuring ABC_Tbl with a clustered index.
D. You should consider configuring ABC_Colmn with a clustered index.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 224
You work as a database administrator at ABC.com.ABC.com is making use of a SQL Server 2008 R2
instance.
ABC.com includes a database that is used by an application that is integral to daily operations. You are
then informed by a ABC.com user that the server is not running as it should be.
You need to make use of the Database Engine Tuning Advisor to enhance the application��s execution,
while preventing other users from being affected by server performance.
You start by setting up a test server, named ABCTEST-SR01 that has the same hardware setting as the
server with the performance issue.
Which of the following actions should you take NEXT? (Choose all that apply.)
A. You should consider running the dtexec.exe utility on ABCTEST-SR01.
B. You should consider running the bcp.exe utility on ABCTEST-SR01.
C. You should consider running the dta.exe utility on ABCTEST-SR01.
D. You should consider running the sqlmaint.exe utility on ABCTEST-SR01.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 225
You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2 instance.
After receiving a complaint from a ABC.com user regarding the sluggish execution of the applications on
the server, you decide not to make use of the table scans to obtain additional data as a result of the poor
execution.
You have to gather the necessary data in another way.
Which of the following actions should you take?
A. You should consider making use of the SQL Server:Memory Manager Object utility via the Windows System Monitor.
B. You should consider making use of the SQLServer:Disk Manager utility via the Windows System Monitor.
C. You should consider making use of the SQL Server:Database Mirroring Object utility via the Windows System Monitor.
D. You should consider making use of the SQLServer:Access Methods utility via the Windows System Monitor.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 226
You work as a database administrator at ABC.com.ABC.com makes use of a SQL Server 2008 R2 instance. ABC.com contains quite a few databases that are utilized by applications. You are planning to make use of the Resource Governor to cut the result of searches. These searches are run via SQL Server Management Studio. You have been instructed to make sure that the CPU utilization for searches done by the SQL Server
Management Studio should not be more than 40 %. You also have to make sure that in the event of the
applications requiring all of the CPU resources, they are permitted to do so when necessary.
Which of the following actions should you take FIRST?
A. You should consider developing a new resource pool.
B. You should consider setting up the MAX_CPU_PERCENT selection to 40.
C. You should consider assigning the resource pool to the workload group.
D. You should consider setting up the MAX_CPU_PERCENT selection to 100.
E. You should consider assigning the resource pool to the administrators group.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 227
You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2 instance that
contains a database, named ABCBooks.
ABCBooks includes the BookTitle, EANNumber, SellingPrice and IssueDate columns. A clustered index is
configured for the BookTitle column and a non-clustered index, which includes the EANNumber and
SellingPrice columns, is configured for the IssueDate column.
A Transact-SQL query, which includes a query hint, searches the ABCBooks table for BookTitle,
EANNumber, SellingPrice, and IssueDate data. During monitoring you notice that the query��s performance
is slow. You then run the sys.dm_db_index_physical_stats dynamic management function (DMF) and find
that the avg_fragmentation_in_percent is in excess of 25%.
You have been instructed to find a solution to improve the query��s performance.
Which of the following actions should you take?
A. You should consider having the table hint inserted.
B. You should consider having the query hint removed from the query.
C. You should consider having the primary key modified.
D. You should consider having a filtered index created.
Correct Answer: B Section: (none) Explanation
Explanation/Reference: QUESTION 228
You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2 instance.
The SQL Server 2008 R2 instance includes a database named ABC_DB that makes use of the bulklogging
recovery model. You have employed a WAN connection for log shipping ABC_DB. You have also
configured ABC_DB��s indexes to be reproduced by a scheduled job that results in the log backups growing
in size.
You have been instructed to make sure that the log backups employed by log shipping is reduced in size.
Which of the following actions should you take?
A. You should consider making use of Database snapshots.
B. You should consider making use of the half recovery mode on ABC_DB.
C. You should consider making use of stored procedures.
D. You should consider having the log file backups compressed.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 229
You work as a database administrator at ABC.com.ABC.com makes use of multiple SQL Server 2008 R2 instances. One of the SQL Server 2008 R2 instances includes a database named ABC_DB. You have been instructed to devise a data collection solution that makes use of the data collector to gather performance information of all the instances. Your solution should allow for the gathered performance information to be reserved in ABC_DB. Your solution should also allow for function data to be gathered and loaded in the management data warehouse every 12 hours. Which of the following actions should you take?
A. You should consider configuring the Event log.
B. You should consider configuring the Trace log.
C. You should consider developing an on-demand cached data collection set.
D. You should consider developing a scheduled non-cached data collection set.
Correct Answer: D Section: (none) Explanation
Explanation/Reference: QUESTION 230
You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2 to
administrate and maintain its database.
You have received instruction from management to create a strategy that makes use of the data collector
is utilized to collect performance information. Your strategy should also allow for 15 day old information to
be removed, and also reduce the amount of effort required to administer the performance information.
Which of the following actions should you take?
A. You should consider creating a single SQL Server 2008 R2 Analysis Services (SSAS) package process to reserve and remove performance information.
B. You should consider setting a management data warehouse process on every instance to reserve and remove performance information in one database for all the instances.
C. You should consider creating a single SQL Service Integration Services (SSIS) package process to reserve and remove performance information.
D. You should consider creating a single SQL Server 2008 R2 Reporting Services (SSRS).
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 231
You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2 instance. Subsequent to installing an important cluster, which necessitates continuous accessibility, on a pair of cluster-ready nodes, you are instructed to make sure that the disruption of the service is reduced. Which of the following actions should you take?
A. You should consider configuring failover with the Prevent automatic failback option.
B. You should consider configuring failover with the Prevent manual failback option.
C. You should consider configuring failover with the Allow automatic failback option.
D. You should consider configuring failover with the Prevent failback during business hours option.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 232
You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2 instance.
The SQL Server 2008 R2 instance contains a database, named ABC_Prod, which are log shipped to a
remote SQL Server 2008 R2 server. Subsequent to rebooting the primary SQL Server instance, you notice
that the log shipping is no longer functioning.
You have been tasked with finding the reason for the log shipping not functioning.
Which of the following actions should you take?
A. You should make sure that the SQL Server Agent has been started on the main server.
B. You should consider making use of the EXTENDED_LOGICAL_CHECKS option to run a DBCC INDEXDEFRAG statement on ABC_Prod.
C. You should make sure that the SQL Server Surface Area Configuration has been started on the main server.
D. You should make sure that the SQL Server Browser service has been started on the main server.
Correct Answer: A