Latest MCTS 70-432 Dumps Real Exam Download 81-90

Ensurepass

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

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

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

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 SecurityRequireClientAuthentication server configuration should be set to False

B. The SecurityRequireClientAuthentication 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

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. PropertiesRequireClientAuthentication

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

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 haveto 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

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

Explanation/Reference:

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

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

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

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 thisinformation 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

 

Download Latest MCTS 70-432 Real Free Tests , help you to pass exam 100%.