70-432 Implementation and Maintenance

Question 1

You are deploying a new server within Wide World Importers that will be running a SQL Server 2008 instance in support of a new application. Because of the feature support that is needed, you will be installing SQL Server 2008 Enterprise. Which operating systems will support your installation? (Choose all that apply.)

• Windows Server 2003 Enterprise SP2
• Windows Server 2003 Enterprise
• Windows 2000 Server Enterprise SP4 or higher
• Windows Server 2008 Enterprise

Answer 1

• Windows Server 2003 Enterprise SP2
• Windows Server 2008 Enterprise

Explanation:
SQL Server 2008 Enterprise is supported on Windows Server 2003 Enterprise SP2 and later or Windows Server 2008 Enterprise.

SQL Server 2008 Enterprise Edition is supported on Windows Server 2003 Enterprise SP2 and later or Windows Server 2008 Enterprise.

Question 2

You are the database administrator at your company. You need to enable the sales support team to perform fuzzy searches on product descriptions. Which actions do you need to perform to satisfy user needs with the least amount of effort? (Choose two. Each forms part of the correct answer.)

• Create a full text index on the table of product descriptions for the description column and specify NO POPULATION.
• Create a full text catalog specifying the filegroup for backup purposes and the root path to store the contents of the catalog on the file system.
• Create a full text catalog and specify the filegroup to store the contents of the catalog.
• Create a full text index on the table of product descriptions for the description column and specify CHANGE_TRACKING AUTO.

Answer 2

• Create a full text catalog and specify the filegroup to store the contents of the catalog.
• Create a full text index on the table of product descriptions for the description column and specify CHANGE_TRACKING AUTO.

Explanation:
Full text catalogs contain full text indexes and the contents of the indexes are stored within the database in SQL Server 2008.

CHANGE_TRACKING AUTO option enables SQL Server to populate the full text index upon initial creation and migrate changes automatically to underlying data into the index.

Question 3

Wide World Importers will be using the new FILESTREAM data type to store scanned images of shipping manifests. Which command must be executed against the SQL Server instance before FILESTREAM data can be stored?

• sp_filestream_configure
• sp_configure
• DBCC
• ALTER DATABASE

Answer 3

sp_filestream_configure is used to enable the FILESTREAM access level as well as configure the Windows share to be used.

Question 4

You want to configure your full text indexes such that SQL Server migrates changes into the index as quickly as possible with the minimum amount of administrator effort. Which command should you execute?

• ALTER FULLTEXT INDEX ON START INCREMENTAL POPULATION
• ALTER FULLTEXT INDEX ON START UPDATE POPULATION
• ALTER FULLTEXT INDEX ON START FULL POPULATION
• ALTER FULLTEXT INDEX ON SET CHANGE_TRACKING AUTO

Answer 4

• ALTER FULLTEXT INDEX ON SET CHANGE_TRACKING AUTO

Explanation:
When the CHANGE_TRACKING argument is set to AUTO, SQL Server automatically updates the full text index as changes to underlying data occur. In AUTO mode, no administrator intervention is required either manually or via a scheduled job.

Question 5

You want to search for two terms based on proximity within a row. Which full text predicates can be used to perform proximity searches? (Choose two. Each forms a separate answer.)

• FREETEXT
• CONTAINS
• CONTAINSTABLE
• FREETEXTTABLE

Answer 5

CONTAINS allows proximity searches by using the NEAR keyword.

CONTAINSTABLE allows proximity searches by using the NEAR keyword.

Question 6

As part of the implementation of the new Web-based booking system at Margie’s Travel, customers should receive notices when a travel booking has been successfully saved. What technologies or features can the developers at Margie’s Travel use to implement notifications? (Choose all that apply.)

• Activity Monitor
• Notification Services
• Database Mail
• Microsoft Visual Studio.NET code libraries

Answer 6

Database Mail can be used to send messages to customers.

A Visual Studio.NET application can be created to use the mail libraries available within the .NET Framework.

Question 7

Margie’s Travel decided to minimize the cost and deploy SQL Server 2008 Standard to support the new online division. After a successful launch, managers are having a hard time managing business operations and need to deploy advanced analytics. A new server running SQL Server will be installed. Which edition of SQL Server needs to be installed on the new server to support the necessary data analytics?

• SQL Server 2008 Standard
• SQL Server 2008 Enterprise
• SQL Server 2008 Express with Advanced Services
• SQL Server 2008 Workgroup

Answer 7

Only SQL Server 2008 Enterprise supports all the advanced analytics needed, such as OLAP and Data Mining.

Question 8

Margie’s Travel is opening a new division to offer online travel bookings to their customers. Managers expect the traffic volume to increase rapidly, to the point where hundreds of users will be browsing offerings and booking travel at any given time. Management would also like to synchronize multiple copies of the database of travel bookings to support both online and face-to-face operations. Which editions of SQL Server 2008 would be appropriate for Margie’s Travel to deploy for their new online presence? (Choose all that apply.)

• Enterprise
• Compact
• Standard
• Express

Answer 8

Standard can handle the user load needed and supports the replication features needed to synchronize databases.

Enterprise can scale to handle any load as well as provide any capability needed by Margie’s Travel.

Question 9

You have a list of words that should be excluded from search arguments. Which action should you perform in SQL Server 2008 to meet your requirements with the least amount of effort?

• Populate a thesaurus file and associate the thesaurus file to the full text index.
• Parse the inbound query and remove any common words from the search arguments.
• Create a stop list and associate the stop list to the full text index.
• Create a noise word file and associate the noise word file to the full text index.

Answer 9

Stop lists are created in SQL Server 2008 to exclude words from a full text index as well as search arguments. After the stop list is associated to a full text index, any queries that use the index automatically have any stop words removed from the search arguments.

Question 10

The developers at Margie’s Travel have decided to utilize Database Mail to send messages to their customers. The ability to send mail messages through a given profile needs to be restricted, but it must not require an approved user to specify a mail profile when sending messages. What settings need to be configured to meet these requirements? (Choose all that apply.)

• Set the mail profile to public.
• Set the mail profile to private.
• Designate the mail profile as the default.
• Set the mail profile to private and grant access to approved users.

Answer 10

You need to configure the mail profile to be private along with granting access to the mail profile for approved users.

Designating a mail profile as the default allows approved users to send mail using the profile without the need to specify the profile explicitly.

Question 11

You want to perform a proximity search based on a weighting value for the search arguments. Which options for the CONTAINSTABLE predicate should you use?

• FORMSOF with the THESAURUS keyword
• FORMSOF with the INFLECTIONAL keyword
• ISABOUT with the WEIGHT keyword
• ISABOUT

Answer 11

ISABOUT performs proximity searches and it also applies weighting if the WEIGHT keyword and weighting value are supplied.

Question 12

You are deploying SQL Server 2008 Express in support of a new Web-based application that will enable customers to order directly from Coho Vineyards. Which operating system does NOT support your installation?

• Windows Server 2008 Server Core
• Windows Server 2003 Enterprise SP2
• Windows XP Tablet Edition SP2
• Windows XP Home Edition SP2

Answer 12

SQL Server 2008 Express is not supported on Windows Server 2008 Server Code.

Question 13

Contoso has implemented a new policy that requires the passwords on all service accounts to be changed every 30 days. Which tool should the Contoso database administrators use to change the service account passwords so that SQL Server services comply with the new policy?

• SQL Server Management Studio
• SQL Server Surface Area Configuration Manager
• SQL Server Configuration Manager
• Windows Service Control applet

Answer 13

Only the SQL Server Configuration Manager has the code to decrypt and re-encrypt the service master key used by SQL Server services correctly when the service account or password is changed.

Question 14

Where would you look to retrieve a list of jobs that have failed?

• The job history in SSMS
• The SQL Server Agent error log
• The SQL Server error log
• The Windows event log

Answer 14

The job history within SSMS lists all the jobs that have executed, along with their execution status.

Question 15

Your Orders database crashed last night, and you have determined that the crash was caused by a data file running out of space. What tool do you use to send a notification to an administrator as well as expand the data file before it runs out of space?

• SQL Server Agent
• Network Monitor
• Event Viewer
• System Monitor

Answer 15

You can configure a SQL Server Agent alert that notifies an operator as well as execute a job that can increase the space in the data file.

Question 16

You have defined several policies that you want applied to all databases within an instance. How do you ensure that a database owner is not allowed to avoid the policy check with the least amount of administrative effort?

• Add the policy to the default policy category.
• Add the policy to a user-defined policy category and set the Mandate property.
• Create a condition that checks all databases.
• Check the policies manually against the instance.

Answer 16

Add the policy to the default policy category.

Explanation:
The default category is configured with the Mandate property enabled. In addition, you cannot disable the Mandate property.

Question 17

You are the database administrator at A. Datum Corporation. Users are complaining that applications cannot connect to the SQL Server. You have verified all the application settings and you can connect to the server from your desktop using SSMS. But the users’ applications keep returning an “Access denied” error message. What could be the problem?

• Remote connections are not enabled.
• The TCP endpoint for TSQL is DISABLED.
• Users do not have CONNECT permissions on the endpoint.
• The TCP endpoint for TSQL is STOPPED.

Answer 17

Users do not have CONNECT permissions on the endpoint.

Explanation:
If users do not have CONNECT permission on the TCP endpoint for TSQL, they receive an “Access denied” error message.

Question 18

The Human Resources (HR) director at Contoso needs to ensure that only authorized users are accessing employee pay records. What do you need to implement to satisfy these auditing needs?

• A DML trigger
• Server audit specification
• A DDL trigger
• Database audit specification

Answer 18

Database audit specification

Explanation:
You can create a database audit specification to log any SELECT, INSERT, UPDATE, DELETE, BCP, or BULK INSERT statements executed against the employee pay records table for just the group of users who have access

Question 19

The database administrators at Fabrikam have implemented log shipping for the Orders database. To ensure that log shipping cannot break, you need to prevent anyone from changing the recovery model of the database to SIMPLE. How can you accomplish this task?

• Server audit specification.
• A DML trigger.
• A DDL trigger.
• You can’t prevent the change of the recovery model.

Answer 19

You can’t prevent the change of the recovery model.

Explanation:
Because ALTER DATABASE can make changes to the file system, which is nontransactional, you cannot prevent ALTER DATABASE from executing. Therefore, the requirement to prevent the change is not possible to implement.