Microsoft 70-432 Exam - Microsoft SQL Server 2008, Implementation and Maintenance

Document Sample
Microsoft 70-432 Exam - Microsoft SQL Server 2008, Implementation and Maintenance Powered By Docstoc
					                                                   Total Questions: 396




    Questions and Answers
                                          with
         Detailed Explanations and Objectives




                              Vendor: Microsoft
                             Certification: MCTS
                             Exam Code: 70-432
                   Exam Name:
TS: Microsoft SQL Server 2008, Implementation and
                   Maintenance
                            Total Questions: 396




http://www.testkingweb.com/microsoft-70-432.html            Page 1 of 415
                                                                          Total Questions: 396

Objectives

   A      Installing and Configuring SQL Server 2008

   B      Maintaining SQL Server Instances

   C      Managing SQL Server Security

   D      Maintaining a SQL Server Database

   E      Performing Data Management Tasks

   F      Monitoring and Troubleshooting SQL Server

  G       Optimizing SQL Server Performance

  H       Implementing High Availability

Note: Relevant objective of each question is mentioned with Question number.




http://www.testkingweb.com/microsoft-70-432.html                                   Page 2 of 415
                                                                                Total Questions: 396

QUESTION NO: 1(A)
Which of the following stored procedures lists information about one or more mail profiles?

A.   sysmail_help_profile_sp
B.   sysmail_help_principalprofile_sp
C.   sysmail_help_profileaccount_sp
D.   sysmail_display_profile_sp

ANSWER: A

QUESTION NO: 2(A)
You are planning the installation of SQL Server 2008 on a server named Server1 within a domain named
Company.com. After the installation, SQL Server will need to interact with network services. What type
of account should you use to start the SQL Server service?

A.   The Network Service account
B.   The Local Service account
C.   A local user account created on Server1
D.   A user account created in the Company.com domain

ANSWER: D

EXPLANATION:
The SQL Server service must run under the context of an account. If the SQL Service account needs to
interact with network services and access domain resources such as file shares, or if it uses linked server
connections to other computers running SQL Server, a user should use a domain account with
appropriate privileges. Many server-to-server activities can be performed with a domain user account. A
domain user account is created in Active Directory. A user can be granted specific permissions to access
specific resources on a system. This account is recommended for the production servers having SQL
Server 2005 and later editions.

While the SQL Server Agent service isn't mentioned in this question, the same recommendations apply
to the SQL Server Agent service as the SQL Server service. For SQL Server and SQL Server Agent to run as
services in Windows, SQL Server and SQL Server Agent must be assigned a Windows user account.

Answer option A is incorrect. Microsoft specifically recommends that the Network Service account not
be used to start the SQL Server service. The Network Service account is a shareable account. Domain
user accounts that are not a Windows administrator are more appropriate for SQL Server services.

The Network Service account is a built-in account that has more access to resources and objects than
members of the Users group. A user who logs on through a network service account can access the
resources and objects of a system. These users have the same permissions as the members of the Users
group. The network resources can be accessed by a network service account through Windows
credentials. However, Microsoft specifically recommends that the Network Service account not be used
for any SQL Server services.




http://www.testkingweb.com/microsoft-70-432.html                                             Page 3 of 415
                                                                               Total Questions: 396

Answer option B is incorrect. The Local Service account is a built-in account that has the same level of
access to resources and objects as members of the Users group.

A user who logs on through a local service account can access the resources and objects of a system.
This account has the same permission as the Users group. The user who logs on through this account
also has the permission to connect to the network. The network connection does not use Windows
credentials.

Since the SQL Server service needs access to network resources, the Local Service account won't provide
the required access. Also, you should know that the Local Service account is not supported for SQL
Server Agent.

Answer option C is incorrect. If the computer is not a part of a domain, a local user account without
Windows administrator permissions is recommended. However, since the computer is part of the
domain and access to network resources is required, this wouldn't provide adequate access.

What is a “service” account?
A service account is an account under which different services start. These services start before a user
logs on to a system. These services are limited to the permissions granted to the service accounts under
which the services are started. The different types of accounts are as follows:
 Local System account
 Network Service account
 Local Service account
 Local User account
 Domain User account

Reference: MSDN SQL Server 2008 Books Online, Contents: "Setting Up Windows Service Accounts"

QUESTION NO: 3(A)
You manage a SQL Server 2008 server named SQL1 within a domain named Company.com. You begin
planning SQL Server Agent jobs for this server. The jobs will need access to different resources within
the domain. What should you do to start the SQL Server Agent service?

A. Create an account in the Company.com domain and configure the SQL Server Agent service to use
   this account.
B. Create an account on the SQL1 server and configure the SQL Server Agent.
C. Use the Network Service account.
D. Use the Local Service account.

ANSWER: A

EXPLANATION:
Since the SQL Server Agent jobs will need access to resources within the domain, a domain account is
required for the SQL Server Agent service. This account can be created in the domain and granted access
to resources just as any other domain user account.




http://www.testkingweb.com/microsoft-70-432.html                                             Page 4 of 415
                                                                               Total Questions: 396

Answer option B is incorrect. If the computer is not part of a domain, a local user account without
Windows administrator permissions is recommended. However, since the computer is part of the
domain and access to domain resources is required, this wouldn't provide adequate access.

Answer option C is incorrect. The Network Service account is a built-in account that has more access to
resources and objects than members of the Users group. A user who logs on through a network service
account can access the resources and objects of a system. These users have the same permissions as the
members of the Users group. The network resources can be accessed by a network service account
through Windows credentials. However, Microsoft specifically recommends that the Network Service
account not be used for any SQL Server services.

Answer option D is incorrect. The Local Service account is a built-in account that has the same level of
access to resources and objects as members of the Users group. A user who logs on through a local
service account can access the resources and objects of a system. This account has the same permission
as the Users group. The user who logs on through this account also has the permission to connect to the
network. The network connection does not use Windows credentials.

Since the SQL Server Agent service needs access to domain resources, the Local Service account won't
provide the required access. Additionally, the Local Service account is not supported for use with the
SQL Server Agent service.

What is a “service” account?
A service account is an account under which different services start. These services start before a user
logs on to a system. These services are limited to the permissions granted to the service accounts under
which the services are started. The different types of accounts are as follows:
 Local System account
 Network Service account
 Local Service account
 Local User account
 Domain User account

Reference: MSDN SQL Server 2008 Books Online, Contents: "Setting Up Windows Service Accounts"

QUESTION NO: 4(A)
Which of the following statements is NOT true about the FILESTREAM?

A.   Encryption is supported on FILESTREAM data.
B.   FILESTREAM data containers cannot be nested.
C.   FILESTREAM filegroups can be on compressed volumes.
D.   When a table contains a FILESTREAM column, each row must have a nonnull unique row ID.

ANSWER: A

QUESTION NO: 5(A)
Which of the following DBCC statements is used to display the last statement sent from a client to an
instance of Microsoft SQL Server?



http://www.testkingweb.com/microsoft-70-432.html                                            Page 5 of 415
                                                                              Total Questions: 396

A.   DBCC INPUTBUFFER
B.   DBCC OUTPUTBUFFER
C.   DBCC CHECKIDENT
D.   DBCC CHECKALLOC

ANSWER: A

EXPLANATION:
The DBCC INPUTBUFFER statement is used to display the last statement sent from a client to an instance
of Microsoft SQL Server. It has the following syntax:

DBCC INPUTBUFFER ( session_id [ , request_id ] )

[WITH NO_INFOMSGS]

where,
 “session_id” is the session ID connected with each active primary connection.
 “request_id” is the exact request (batch) that has to be searched within the current session.
 “WITH” allows for options to be specified.
 “NO_INFOMSGS” suppresses all informational messages whose severity level is from 0 to 10.

Answer option B is incorrect. The DBCC OUTPUTBUFFER statement is used to return the current output
buffer in ASCII and hexadecimal format for the specified session_id. It has the following syntax:

DBCC OUTPUTBUFFER ( session_id [ , request_id ] )

[ WITH NO_INFOMSGS ]

where,
 “ session_id” is the session ID connected with each active primary connection.
 “request_id” is the exact request (batch) that has to be searched within the current session.
 “WITH” allows for options to be specified.
 “NO_INFOMSGS” suppresses all informational messages whose severity level is from 0 to 10.

The following example returns the current output buffer information for an assumed session ID of 48.

DBCC OUTPUTBUFFER (48);

Answer option C is incorrect. The DBCC CHECKIDENT statement is used to check the current identity
value for the specified table. If required, this statement can also change the identity value. A new
current identity value for the identity column can be set manually with the help of this statement. The
syntax of the DBCC CHECKIDENT statement is as follows:

DBCC CHECKIDENT

(



http://www.testkingweb.com/microsoft-70-432.html                                            Page 6 of 415
                                                                            Total Questions: 396

         table_name

         [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]

)

[ WITH NO_INFOMSGS ]

where,
 “table_name” is the name of the table for which the current identity value has to be checked. The
   table specified in the syntax must contain an identity column.
 “NORESEED” indicates that the current identity values must not be changed.
 “RESEED” indicates that the current identity values must be changed.
 “new_reseed_value” is the new value that is to be used as the current value of the identity column.
 “WITH NO_INFOMSGS” is used to suppress all the informational messages.

Answer option D is incorrect. The DBCC CHECKALLOC statement is used to check the consistency of disk
space allocation structures for a specific database. Only the users having the membership in the
sysadmin fixed server role or the db_owner fixed database role can execute this statement.

The syntax of the DBCC CHECKALLOC statement is as follows:

DBCC CHECKALLOC

[

         ( database_name | database_id | 0

        [ , NOINDEX | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST }]

         )

    [ WITH

         {[ ALL_ERRORMSGS ]

             [ , NO_INFOMSGS ]

             [ , TABLOCK ]

             [ , ESTIMATEONLY ]}

    ]

]



http://www.testkingweb.com/microsoft-70-432.html                                         Page 7 of 415
                                                                                Total Questions: 396

where,
 “database_name | database_id | 0” is the name of the database or its ID for which the disk space
   allocation and page usage has to be checked. If 0 is specified, then it indicates that the current
   database is used for checking.
 “NOINDEX” indicates that the non-clustered index present in the users table must not be checked.
 “REPAIR_ALLOW_DATA_LOSS” is used to repair the error. It may cause data loss.
 “REPAIR_FAST” is used only to maintain backward compatibility.
 “ALL_ERRORMSGS” is used to display all the error messages.
 “NO_INFOMSGS” is used to suppress all the information messages and the report of space used in
   any specified database.
 “TABLOCK” allows the DBCC command to acquire an exclusive lock on the database.
 “ESTIMATE ONLY” is used to display the expected amount of tempdb space required to execute the
   DBCC CHECKALLOC statement when the rest of the options are specified.

What are DBCC statements?
The SQL Server provides Database Consistency Checker (DBCC) statements to check the physical and
logical consistency of a database. Many DBCC statements can fix detected problems. For example, the
DBCC CHECKTABLE and DBCC CHECKDB statements can detect and fix problems.

Reference: http://msdn.microsoft.com/en-us/library/ms187730.aspx

QUESTION NO: 6(A)
Which of the following trace flags disables database mirroring log compression?

A.   Trace flag 1462
B.   Trace flag 1472
C.   Trace flag 1452
D.   Trace flag 1442

ANSWER: A

QUESTION NO: 7(A)
"Indid" refers to Index ID. What does it mean when "indid" is 0 (zero)?

A.   It means that a table does not have an index and is stored by SQL Server as a heap.
B.   It means that a table has an index and is stored by SQL Server as a heap.
C.   It means that a table does not have an index and is not stored by SQL Server as a heap.
D.   It means that a table has an index and is not stored by SQL Server as a heap.

ANSWER: A

QUESTION NO: 8(A)
Which of the following database options is used for backward compatibility in SQL Server?

A. PAGE_VERIFY_TORN_PAGE_DETECTION
B. PAGE_VERIFY_TORN_PAGE_CORRECTION



http://www.testkingweb.com/microsoft-70-432.html                                               Page 8 of 415
                                                                                 Total Questions: 396

C. PAGE_VERIFY_TORN_PAGE_SPECIFICATION
D. PAGE_VERIFY_TORN_PAGE_CHECKING

ANSWER: A

QUESTION NO: 9(A)
Which of the following system caches is used by FileStream to cache the file data?

A.   NT
B.   TN
C.   TNT
D.   NTN

ANSWER: A

QUESTION NO: 10(A)
You manage a SQL Server 2008 server within a domain named Company.com. You decide to deploy an
instance of SQL Server Reporting Services (SSRS). SSRS needs to include document management and
collaboration features including alerts and version control. What should you do?

A.   Deploy SSRS using Native Mode.
B.   Deploy SSRS using SharePoint Integrated Mode.
C.   Deploy SSRS as a web site.
D.   Deploy SSRS on a separate server.

ANSWER: B

EXPLANATION:
SharePoint integrated mode enables the integration of Reporting Services with the SharePoint
databases and security model. These features become available when a user configures a report server
to run within a larger deployment of a SharePoint 3.0 product or technology. This mode requires
additional software and configuration. It also necessitates that a report server instance be dedicated for
integrated operations. The benefit of doing this is a rich level of integration that allows a user to access
and manage report server content types using the application pages and data stores of a SharePoint
Web application.

SSRS can be installed using Native Mode or SharePoint Integrated Mode. SharePoint Integrated Mode
includes many additional features, including document management and collaboration features such as
alerts and version control.

Answer option A is incorrect. Native Mode is easy to set up, but it does not support document
management and collaboration features.

Answer option C is incorrect. SSRS will function as a web site when deployed in either Native Mode or
SharePoint Integrated Mode. However, it must be deployed using SharePoint Integrated Mode to
include document management and collaboration features.



http://www.testkingweb.com/microsoft-70-432.html                                              Page 9 of 415
                                                                               Total Questions: 396

Answer option D is incorrect. It is not necessary to deploy SSRS on a separate server.

What is “SSRS”?
SSRS stands for SQL Server Reporting Services. It creates and manages Web-enabled reporting services.
It is used to create and generate reports, to retrieve data from various data sources, and to publish
reports in various formats.

Reference: http://msdn.microsoft.com/en-us/library/bb326405.aspx

QUESTION NO: 11(A)
Peter works as an Administrator for Bluewell Inc. The company has a SQL Server 2008 computer. The
server contains a database named Sales, which contains the structured and unstructured data. Peter
wants the structured data to be stored in the Sales database and the associated unstructured data to be
stored directly in the NTFS file system. What will Peter do to accomplish the task with the least amount
of administrative effort?

A.   Use a FILESTREAM.
B.   Use Simple Parameterization.
C.   Use Forced Parameterization.
D.   Use a stop-word list.

ANSWER: A

EXPLANATION:
In order to accomplish the task, Peter will use a FILESTREAM.
A FILESTREAM is a new feature in the SQL Server 2008. It allows structured data to be stored in the
database and associated unstructured data to be stored directly in the NTFS file system. Transactional
consistency is maintained at all times between the structured and unstructured data. FILESTREAM
combines the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary
large object (BLOB) data as files on the file system.

It is possible to access and manipulate the FILESTREAM data in the following two ways:
1. “Standard Transact-SQL programming model:” It can insert, update, query, search, and back up
      FILESTREAM data.
2. “Win32 streaming APIs:” It provides streaming access to the data and supports most of the DML
      operations.

It is recommended to use the FILESTREAM when any of the following conditions are met:
 When the objects that are being stored in the FILESTREAM have a size greater than 1 MB.
 When fast read access is an essential requirement.
 When an application is developed that uses a middle tier for application logic.

Answer option B is incorrect. When a Transact-SQL statement is executed without parameters, the SQL
server parameterizes the SQL statement internally to enhance the likelihood of matching it against an
existing execution plan. This process, which was previously known as auto-parameterization in SQL
Server 2000, is now known as simple parameterization.



http://www.testkingweb.com/microsoft-70-432.html                                          Page 10 of 415
                                                                              Total Questions: 396

Answer option C is incorrect. Forced parameterization is a process through which it is possible to
override the default simple parameterization behavior of SQL Server by specifying that all SELECT,
INSERT, UPDATE, and DELETE statements in a database be parameterized. It improves the database
performance by decreasing the frequency of query compilations and recompilations. It is enabled by
setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement.

Answer option D is incorrect. A stop-word list is used to exclude frequently occurring words or numbers
from a full-text search, thus increasing the speed of the search. The guidelines that must be used before
a stop-word list is created are as follows:
 A stop-word list can have only one word per line with no leading or trailing spaces. Any character
    after the first word on a line is ignored.
 Apostrophe characters (') are stripped out, and letters before and after the apostrophe character
    are concatenated to make it a single word. For example, "employee's" is equivalent to "employees".
 The hash (#) and underscore (_) characters are treated as normal characters.
 The extended chars, of ASCII range 128-255, are normalized.
 The characters such as semicolon (;), colon (:), and hyphen (-) are not permitted because these
    characters have the consequence of breaking a single word into two, which may change the
    meaning of the word and the sentence as a whole.

Reference: http://msdn.microsoft.com/en-us/library/bb933993.aspx

QUESTION NO: 12(A)
Which of the following statements is true about forced parameterization?

A. Setting the PARAMETERIZATION option is an online operation that requires no database-level
   exclusive locks.
B. Setting the PARAMETERIZATION option is an offline operation that requires no database-level
   exclusive locks.
C. Setting the PARAMETERIZATION option is an online operation that requires database-level exclusive
   locks.
D. Setting the PARAMETERIZATION option is an offline operation that requires database-level exclusive
   locks.

ANSWER: A

QUESTION NO: 13(A)
You manage a SQL Server 2008 server named Server1. Database developers have asked you to
implement Database Mail on the server. What tools can you use to enable Database Mail?

Each correct answer represents a complete solution. Choose all that apply.

A.   Database Mail Configuration Wizard
B.   Surface Area Configuration facet of Policy-Based Management
C.   sp_configure
D.   Database Mail is active by default.

ANSWER: A, B and C


http://www.testkingweb.com/microsoft-70-432.html                                          Page 11 of 415
                                                                               Total Questions: 396

EXPLANATION:
Database Mail is not active by default, but it can be enabled by using any of the following features of
SQL Server:

   “The Database Mail Configuration Wizard:”
    The Database Mail Configuration Wizard provides a suitable method to manage Database Mail
    configuration objects and enables Database Mail. To use this wizard, a user must be a member of
    the sysadmin fixed server role. To send Database Mail, a user must be a member of the
    DatabaseMailUserRole database role in the msdb database.
    “The Surface Area Configuration facet of Policy-based Management:”
    SQL Server Surface Area Configuration is a tool that is used to enable, disable, start, or stop the
    features, services, and remote connectivity of SQL Server 2005 and 2008 installations. This tool can
    be used on local and remote servers. Windows Management Instrumentation (WMI) is used by SQL
    Server Surface Area Configuration to view and change server settings.
    “The system stored procedure sp_configure:”
    The sp_configure stored procedure is used to display or change global configuration settings for the
    current server. The ALTER DATABASE statement and the SET statement are used to change
    database-level settings and settings that affect only the current user session, respectively. The
    syntax of sp_configure is as follows:

        sp_configure [ [ @configname = ] 'option_name'

          [ , [ @configvalue = ] 'value' ] ]

    where,
     “* @configname = + 'option_name'“ is the name of a configuration option. option_name is
       varchar(35), with a default of NULL. The SQL Server Database Engine identifies any unique string
       that is part of the configuration name. A complete list of options is returned if no string is
       specified.
     “* @configvalue = + 'value'“ is the new configuration setting. value is int, with a default of NULL.

What is “Policy-Based Management facet”?
Policy-Based Management facet is a collection of logical properties that model the characteristics or
behavior for certain types of managed targets. The number and characteristics of the properties are
built into the facet, and only the maker of the facet can add or remove them. A target type can
implement one or more management facets, and a management facet can be implemented by one or
more target types. Some properties of a facet can only be relevant to a particular version. For example,
the Database Mail property of the Surface Area Configuration facet only applies to SQL Server 2005 and
later versions.

Answer option D is incorrect. Database mail is not active by default. It needs to be enabled and
configured.

The Database Mail Configuration Wizard provides a suitable method to manage Database Mail
configuration objects and enables Database Mail. To use this wizard, a user must be a member of the



http://www.testkingweb.com/microsoft-70-432.html                                            Page 12 of 415
                                                                            Total Questions: 396

sysadmin fixed server role. To send Database Mail, a user must be a member of the
DatabaseMailUserRole database role in the msdb database.

Take the following steps to enable Database Mail:

1. Run SQL Server Surface Area Configuration from Start > Programs > Microsoft SQL Server 2005 >
   Configuration Tools > SQL Server Surface Area Configuration.




2. In the SQL Server 2005 Surface Area Configuration window, click the ‘Surface Area Configuration For
   Features’ link.




http://www.testkingweb.com/microsoft-70-432.html                                        Page 13 of 415
                                                                            Total Questions: 396

3. In the Surface Area Configuration for Features window, click Database Mail. Check the ‘Enable
   Database Mail stored procedures’ checkbox. Now, click the OK button.




Reference: http://msdn.microsoft.com/en-us/library/ms175887.aspx

QUESTION NO: 14(A)
You manage a SQL Server 2008 server named Server1 in the Company.com domain. The SQL Server
Agent has been configured to execute jobs, but you have discovered that when Server1 is rebooted, the
SQL Server Agent no longer executes the jobs. You launch the SQL Server Configuration Manager and
see the following figure:




http://www.testkingweb.com/microsoft-70-432.html                                        Page 14 of 415
                                                                               Total Questions: 396




What should you do?

A.   Start the SQL Server Integration Services.
B.   Configure the SQL Server Agent service to start using the Network Service account.
C.   Start the SQL Server Reporting Services.
D.   Configure the SQL Server Agent Start Mode to Automatic.

ANSWER: D

EXPLANATION:
The SQL Server Agent currently has a Start Mode of Manual. When the SQL Server is rebooted, the SQL
Server Agent will not start automatically. By changing the start mode to Automatic, the SQL Server
Agent service will start automatically when the SQL Server is rebooted.

Answer option A is incorrect. The SQL Server Integration Services could be used by the SQL Server Agent,
but modifying this service won't affect how the SQL Server Agent service runs.

SSIS (SQL Server Integration Services) is an upgrade of DTS (Data Transformation Services), which is a
feature of the previous version of SQL Server. SSIS packages can be created in BIDS (Business
Intelligence Development Studio). These can be used to merge data from heterogeneous data sources
into SQL Server. They can also be used to populate data warehouses, to clean and standardize data, and
to automate administrative tasks.

Answer option B is incorrect. The Network Service account is a built-in account that has more access to
resources and objects than members of the Users group. However, Microsoft specifically recommends
that the Network Service account not be used for any SQL Server services.

The Network Service account is a built-in account that has more access to resources and objects than
members of the Users group. A user who logs on through a network service account can access the
resources and objects of a system. These users have the same permissions as the members of the Users
group. The network resources can be accessed by a network service account through Windows
credentials. However, Microsoft specifically recommends that the Network Service account not be used
for any SQL Server services.




http://www.testkingweb.com/microsoft-70-432.html                                          Page 15 of 415
                                                                              Total Questions: 396

Answer option C is incorrect. The SQL Server Reporting Services are used for reports. However,
modifying how the SQL Server Reporting Services account starts won't affect how the SQL Server Agent
service starts.

SSRS stands for SQL Server Reporting Services. It creates and manages Web-enabled reporting services.
It is used to create and generate reports, to retrieve data from various data sources, and to publish
reports in various formats.

Reference: MSDN SQL Server 2008 Books Online , Contents: "Setting Up Windows Service Accounts"

QUESTION NO: 15(A)
Which of the following views lists information about messages that a Database mail could not sent
successfully?

A.   sysmail_faileditems
B.   sysmail_allitems
C.   sysmail_unsentitems
D.   sysmail_returneditems

ANSWER: A

QUESTION NO: 16(A)
Which of the following database options should be used to detect any structural integrity errors?

A.   PAGE_VERIFY CHECKSUM
B.   PAGE_VERIFY ERRORS
C.   PAGE_VERIFY_TORN_PAGE_DETECTION
D.   PAGE_VERIFY CHECKPARITY

ANSWER: A

QUESTION NO: 17(A)
Peter works as an Administrator for Techsoft Inc. The company has a SQL Server 2008 computer. Peter
wants to enable Database Mail on the SQL Server 2008 computer to send e-mail messages to users.
Which of the following SQL Server features can he use to accomplish the task?

Each correct answer represents a complete solution. Choose all that apply.

A.   Database Mail XPs option
B.   Database Mail Configuration Wizard
C.   SQL Server Profiler
D.   Database Engine Tuning Advisor
E.   SQL Server Configuration Manager

ANSWER: A and B

EXPLANATION:


http://www.testkingweb.com/microsoft-70-432.html                                          Page 16 of 415
                                                                                  Total Questions: 396

Peter can enable Database Mail by using any of the following features of SQL Server:
 “Database Mail XPs option:”
    The Database Mail XPs option of the sp_configure system stored procedure is used to enable
    Database Mail on the server. It has the following two possible values:
     1 indicating that the Database Mail is available.
     0 indicating that the Database Mail is not available. This is the default value.
    The setting takes effect immediately without a server stop and restart.

   “Database Mail Configuration Wizard:”
    The Database Mail Configuration Wizard provides a suitable method to manage Database Mail
    configuration objects and enables Database Mail. To use this wizard, a user must be a member of
    the sysadmin fixed server role. To send Database Mail, a user must be a member of the
    DatabaseMailUserRole database role in the msdb database.

What is “Database Mail”?
Database Mail is used for sending e-mail messages from the Microsoft SQL Server 2005 and 2008
Database Engine. It can be used by database applications to send e-mail messages to users. The
messages may include results of the query, and can also contain files from any resource on the user's
network. Database Mail is designed for reliability, scalability, security, and supportability. It provides a
robust, high-performance replacement for the most commonly requested features of SQL Mail.
Database Mail is designed to function with SMTP servers.

Answer option C is incorrect. SQL Server Profiler is a tool used to monitor and trace events. The events
generated can be used to find the slow running queries and to determine the cause of a deadlock and
that of slow running queries. The events that are captured by SQL Server Profiler are as follows:
 Login connections, failures, and disconnections
 Transact-SQL statements
 Remote procedure call
 The start or end of a stored procedure and statements within it
 The start or end of a transact-SQL batch
 An error written to the SQL Server error log
 Locks and deadlocks

Answer option D is incorrect. Database Engine Tuning Advisor is a tool that tunes a database for better
query performance by analyzing a workload that is a set of T-SQL statements. It suggests clustered
indexes, nonclustered indexes, indexed views, and partitioning. Database Engine Tuning Advisor in SQL
Server 2005 replaces the Index Tuning Wizard of SQL Server 2000. This tool provides two user interfaces:
a graphical user interface (GUI) and the dta command prompt utility.

Answer option E is incorrect. SQL Server Configuration Manager is a tool used to manage the services
associated with SQL Server. It can be used to configure network connections, network protocols, and
network libraries. SQL Server Configuration Manager is a combination of the tools that are used by SQL
Server 2000. These tools are as follows:
 SQL Server Network utility
 Client Network utility
 Service Manager



http://www.testkingweb.com/microsoft-70-432.html                                               Page 17 of 415
                                                                                 Total Questions: 396

Reference: http://msdn.microsoft.com/en-us/library/ms175887.aspx

QUESTION NO: 18(A)
You work as a database developer for Bluewell Inc. The company has a SQL Server 2008 server. You
maintain a database named Customers that stores transaction information of all customers of the
company. The customers have demanded that they receive transaction notifications through email
whenever they make transactions with the company. Which of the following tools will you use to
accomplish the task?

A.   SQL Server Agent
B.   SQL Server Configuration Manager
C.   SQL Server Profiler
D.   Database Engine Tuning Advisor

ANSWER: A

EXPLANATION:
According to the scenario, you have to send transaction notifications to customers when they make
transactions with the company. Therefore, in order to accomplish the task, you will use SQL Server
Agent.

SQL Server Agent is used to automate administrative tasks. It is used to add jobs, store job information,
run a job on a schedule, etc. By default, it is disabled at the time of installation. To enable this tool, a
user has to explicitly start it.

Answer option B is incorrect. The SQL Server Configuration Manager is a tool used to manage the
services associated with SQL Server. It can be used to configure network connections, network
protocols, and network libraries. The SQL Server Configuration Manager is a Microsoft Management
Console snap-in that is available from the Start menu. It uses Windows Management Instrumentation
(WMI) to view and change some server settings.

Answer option C is incorrect. SQL Server Profiler is a tool used to monitor and trace events. The events
generated can be used to find the slow running queries and to determine the cause of a deadlock and
that of slow running queries. The events that are captured by SQL Server Profiler are as follows:
 Login connections, failures, and disconnections
 Transact-SQL statements
 Remote procedure call
 The start or end of a stored procedure and statements within it
 The start or end of a transact-SQL batch
 An error written to the SQL Server error log
 Locks and deadlocks

Answer option D is incorrect. Database Engine Tuning Advisor is a tool that allows users to select and
create an optimal set of indexes, indexed views, and partitions without needing an expert understanding
of the structure of the database. It analyzes workload and the physical implementation of one or more
databases. A workload is a set of Transact-SQL statements that is executed against a database or


http://www.testkingweb.com/microsoft-70-432.html                                              Page 18 of 415
                                                                                 Total Questions: 396

databases that have to be tuned. It is only after analyzing a workload that Database Engine Tuning
Advisor recommends users to add, remove, or modify physical design structures in their databases. This
tool provides two user interfaces: a graphical user interface (GUI) and the dta command prompt utility.

Reference: SQL Server 2008 Books Online, Contents: "SQL Server Agent"

QUESTION NO: 19(A)
You manage a SQL Server 2008 server within a domain named Company.com. You want to configure the
SQL Server 2008 server to send email messages to users in the domain. What should you do?

A.    Enable SQLMail using sp_configure and create a MAPI profile.
B.    Configure SQLMail to use the sp_send_dbmail stored procedure.
C.    Enable Database Mail using the Database Mail Configuration Wizard and create a MAPI profile.
D.    Enable Database Mail using sp_configure and create a Database Mail profile.

ANSWER: D

EXPLANATION:
Database Mail can be enabled using either the sp_configure stored procedure or the Database Mail
Configuration Wizard. Additionally, the Database Mail Configuration Wizard can be used to create a
Database Mail profile. Once it is enabled and a profile has been created, the sp_send_dbmail stored
procedure can be used to send emails.

What is a “Database Mail profile”?
A Database Mail profile is an ordered collection of related Database Mail accounts. In order to send
mail, a user must be a member of the DatabaseMailUserRole in the msdb database, and should have
access to at least one Database Mail profile. Profiles permit database administrators to reconfigure
stored procedures and database applications that use e-mail without changing the application code. For
example, a profile can be configured with one set of e-mail accounts during application development
and testing, and then be updated with another set of accounts when the application shifts to
production. The application uses the same profile name, but sends e-mail using a different list of e-mail
servers.

What is the “sp_configure” stored procedure?
The sp_configure stored procedure is used to display or change global configuration settings for the
current server. The ALTER DATABASE statement and the SET statement are used to change database-
level settings and settings that affect only the current user session, respectively. The syntax of
sp_configure is as follows:

sp_configure [ [ @configname = ] 'option_name'

     [ , [ @configvalue = ] 'value' ] ]

where,
 “* @configname = + 'option_name'“ is the name of a configuration option. option_name is
   varchar(35), with a default of NULL. The SQL Server Database Engine identifies any unique string
   that is part of the configuration name. A complete list of options is returned if no string is specified.


http://www.testkingweb.com/microsoft-70-432.html                                              Page 19 of 415
                                                                                  Total Questions: 396

   “* @configvalue = + 'value'“ is the new configuration setting. value is int, with a default of NULL.

What is the” Database Mail Configuration Wizard”?
The Database Mail Configuration Wizard provides a suitable method to manage Database Mail
configuration objects and enables Database Mail. To use this wizard, a user must be a member of the
sysadmin fixed server role. To send Database Mail, a user must be a member of the
DatabaseMailUserRole database role in the msdb database.

The Database mail Configuration Wizard can be accessed via the SQL Server Management Studio (SSMS)
in the Management node. Right-click Database Mail and select Configure Database Mail. This wizard
allows you to configure multiple profiles, which can be used to send email using different SMTP servers.

What is the “sp_send_dbmail” stored procedure?
The sp_send_dbmail stored procedure is used to send an e-mail message to the specified recipients. The
message may include file attachments, a query result set, or both. It returns the mailitem_id of the
message when the mail is successfully placed in the Database Mail queue. This stored procedure is in
the msdb database. The execute permissions for sp_send_dbmail default to all members of the
DatabaseMailUser database role in the msdb database.

Answer option A is incorrect. SQL Mail provides a way to receive e-mail messages generated by SQL
Server. These messages can be triggered to provide the status of a job or a warning caused by an alert.
SQL Mail can include a result set in reply to e-mail messages that contain queries. SQL Mail allows SQL
Server to send and receive e-mails by establishing a client connection with a mail server. SQL Server uses
two services to handle mail, MSSQLServer and SQLAgentMail.

SQL Mail is considered deprecated. Database Mail should be used instead. While SQL Mail needed a
MAPI profile, Database Mail does not use MAPI profiles.

What is “Database Mail”?
Database Mail is used for sending e-mail messages from the Microsoft SQL Server 2005 and 2008
Database Engine. It can be used by database applications to send e-mail messages to users. The
messages may include results of the query, and can also contain files from any resource on the user's
network. Database Mail is designed for reliability, scalability, security, and supportability. It provides a
robust, high-performance replacement for the most commonly requested features of SQL Mail.
Database Mail is designed to function with SMTP servers.

Answer option B is incorrect. SQL Mail is considered deprecated. Database Mail should be used instead.
Once Database Mail is configured, the sp_send_dbmail stored procedure can be used.

Answer option C is incorrect. Database Mail does not use MAPI profiles. Previous editions of SQL Server
used MAPI profiles, but Database Mail instead is configured to send email directly to SMTP servers.

Reference: http://msdn.microsoft.com/en-us/library/ms175887.aspx

QUESTION NO: 20(A)
Which of the following stored procedures is used to grant permission for a database role or user to use a
Database Mail profile?


http://www.testkingweb.com/microsoft-70-432.html                                               Page 20 of 415
                                                                                 Total Questions: 396

A.    sysmail_add_principalprofile_sp
B.    sysmail_add_profileaccount_sp
C.    sysmail_add_profile_sp
D.    sysmail_add_account_sp

ANSWER: A

EXPLANATION:
The sysmail_add_principalprofile_sp stored procedure is used to grant permission for a database role or
user to use a Database Mail profile. Its syntax is as follows:

sysmail_add_principalprofile_sp { [ @principal_id = ] principal_id | [ @principal_name = ]
'principal_name' } ,

     { [ @profile_id = ] profile_id | [ @profile_name = ] 'profile_name' }

     [ , [ @is_default ] = 'is_default' ]

where,
 “ * @principal_id = + principal_id” is the ID of the database role or user in the msdb database for the
   association. The data type of the principal_id is int, with a default value of NULL.
 “ * @principal_name = + 'principal_name'“ is the name of the database role or user in the msdb
   database for the association. The data type of the principal_name is sysname, with a default value of
   NULL.
 “ * @profile_id = + profile_id” is the id of the profile for the association. The data type of the
   profile_id is int, with a default value of NULL.
 “ * @profile_name = + 'profile_name'“ is the name of the profile for the association. The data type of
   the profile_name is sysname, with no default.
 “ * @is_default = + is_default” specifies whether this profile is the default profile for the principal. A
   principal should have one default profile. The data type of the is_default is bit, with no default.

Answer option B is incorrect. The sysmail_add_profileaccount_sp stored procedure is used to add a
Database Mail account to a Database Mail profile. This stored procedure is created after a Database
Account is created with the sysmail_add_account_sp stored procedure, and a Database Profile is
created with the sysmail_add_profile_sp stored procedure. It has the following syntax:

sysmail_add_profileaccount_sp { [ @profile_id = ] profile_id | [ @profile_name = ] 'profile_name' } ,

     { [ @account_id = ] account_id | [ @account_name = ] 'account_name' }

     [ , [ @sequence_number = ] sequence_number ]

where,
 “ * @profile_id = + profile_id” is the profile id to add the account to. The data type of the profile_id is
   int, with a default value of NULL.



http://www.testkingweb.com/microsoft-70-432.html                                             Page 21 of 415
                                                                               Total Questions: 396

     “* @profile_name = + 'profile_name'“ is the profile name to add the account to. The data type of
      profile_name is sysname, with a default value of NULL.
     “* @account_id = + account_id” is the account id to add to the profile. The data type of the
      account_id is int, with a default value of NULL.
     “* @account_name = + 'account_name'“ is the name of the account that has to be added to the
      profile. The data type of the account_name is sysname, with a default value of NULL.
     “* @sequence_number = + sequence_number” is the sequence number of the account within the
      profile. The data type of sequence_number is int, with no default.

Answer option C is incorrect. The sysmail_add_profile_sp stored procedure is used to create a new
Database Mail profile. Its syntax is as follows:

sysmail_add_profile_sp [ @profile_name = ] 'profile_name'

    [ , [ @description = ] 'description' ]

    [ , [ @profile_id = ] new_profile_id OUTPUT ]

where,
 “* @profile_name = + 'profile_name'“ is the name for the new profile. profile_name is sysname, with
   no default.
 “* @description = + 'description'“ is the optional description for the new profile. Its data type is
   nvarchar(256), with no default.
 “* @profile_id = + new_profile_id” is the OUTPUT that returns the ID for the new profile. Its datatype
   is int, with a default value of NULL.

Answer option D is incorrect. The sysmail_add_account_sp stored procedure is used to create a new
Database Mail account holding information about an SMTP account. Its syntax is as follows:

sysmail_add_account_sp [ @account_name = ] 'account_name' ,

    [ @email_address = ] 'email_address' ,

    [ [ @display_name = ] 'display_name' , ]

    [ [ @replyto_address = ] 'replyto_address' , ]

    [ [ @description = ] 'description' , ]

    [ @mailserver_name = ] 'server_name'

    [ , [ @mailserver_type = ] 'server_type' ]

    [ , [ @port = ] port_number ]

    [ , [ @username = ] 'username' ]



http://www.testkingweb.com/microsoft-70-432.html                                           Page 22 of 415
                                                                               Total Questions: 396

  [ , [ @password = ] 'password' ]

  [ , [ @use_default_credentials = ] use_default_credentials ]

  [ , [ @enable_ssl = ] enable_ssl ]

  [ , [ @account_id = ] account_id OUTPUT ]

where,
 “* @account_name = + 'account_name'“ is the name of the account to add. The data type of the
   account_name is sysname, with no default.
 “ * @email_address = + 'email_address'“ is the e-mail address to send the message from.
 “ * @display_name = + 'display_name'“ is the display name to use on e-mail messages from this
   account. The data type of the display_name is nvarchar(128), with a default value of NULL.
 “ * @replyto_address = + 'replyto_address'“ is the address that responses to messages. The data type
   of replyto_address is nvarchar(128), with a default value of NULL.
 “ * @description = + 'description'“ is a description for the account. The data type of the description is
   nvarchar(256), with a default value of NULL.
 “ * @mailserver_name = + 'server_name'“ is the name or IP address of the SMTP mail server to use
   for this account. The data type server_name is sysname, with no default.
 “ * @mailserver_type = +'server_type'“ is the type of e-mail server. The data type of the server_type
   is sysname, with a default of 'SMTP'.
 “ * @port = + port_number” is the port number for the e-mail server. The data type of the
   port_number is int, with a default value of 25.
 “ * @username = + 'username'“ is the user name to use to log on to the e-mail server. The data type
   of the username is nvarchar(128), with a default value of NULL.
 “ * @password = + 'password'“ is the password to use to log on to the e-mail server. The data type of
   the password is nvarchar(128), with a default value of NULL.
 “ * @use_default_credentials = + use_default_credentials” specifies whether to send the mail to the
   SMTP server using the credentials of the SQL Server Database Engine. The data type of the
   use_default_credentials is bit, with a default value of 0.
 “ * @enable_ssl = + enable_ssl” specifies whether Database Mail encrypts communication using
   Secure Sockets Layer. The data type of the Enable_ssl is bit, with a default value of 0.
 “ * @account_id = + account_id OUTPUT” returns the account id for the new account. The data type
   of the account_id is int, with a default value of NULL.

What is a “stored procedure”?
A stored procedure is a group of SQL statements compiled into a single execution plan. A stored
procedure can be used to implement business logic or as a security mechanism, but it is not executed
automatically. Stored procedures provide better performance, as a fully compiled execution plan is
created and retained in the procedure cache when the stored procedure is created. Hence, the database
does not have to recompile the execution plan every time the procedure is executed.

Reference: http://msdn.microsoft.com/en-us/library/ms187911.aspx

QUESTION NO: 21(A)



http://www.testkingweb.com/microsoft-70-432.html                                            Page 23 of 415
                                                                                  Total Questions: 396

Which of the following structures should be created prior to the creation of full text index in SQL Server
2008?

A.   Full Text Catalogs
B.   Full Text Filegroup
C.   Full Text Page
D.   Full Text Metadata

ANSWER: A

QUESTION NO: 22(A)
You manage a SQL Server 2008 server named Server1. The server hosts a database named Exams. You
have recently enabled full text indexing on the Questions column of the 70-432 table within the Exams
database. After adding several questions about "SQL Server Agent", you query the full text index on
"SQL Server Agent". However, the result set is empty. What should you do?

A.   Apply the tracked changes to the full text index.
B.   Rebuild the full text index.
C.   Restart the MSFTESQL service.
D.   Ensure the Exams database is full text enabled.

ANSWER: A

EXPLANATION:
If the changes are not reflected, it could be because the tracking is set to manual or off. If tracking is set
to manual, you can right-click the full text index and select Apply the Tracked Changes to update the full
text catalog.

Answer option B is incorrect. Rebuilding the index is very processor intensive.

Answer option C is incorrect. The Microsoft Full-Text Engine for SQL Server (MSFTESQL) service is a full-
text indexing and search engine, built on Microsoft Search (MSSearch) technology. The full-text engine
runs as a service named MSFTESQL on the operating system. MSFTESQL service is installed by default
but runs only when Full-Text Search is being used. By default, SQL Server and MSFTESQL run under the
same service account. A different account can be specified by using SQL Server Configuration Manager.
SQL Server 2005 provides side-by-side installs of the full-text engine.

The MSFTESQL service was used in SQL Server 2005 but does not exist in SQL Server 2008.

Answer option D is incorrect. All databases are full text enabled in SQL Server 2008.
A full-text index is a special type of index that is built and maintained to perform the full-text searching.
This index is created with the help of SQL Server tools, such as Management Studio, and maintained by
the FullText Search service. They are stored on disk as catalogs, separate from the database. These
catalogs are used to organize the full-text index.

What are the “advantages of full-text search”?
The advantages of full-text search are as follows:


http://www.testkingweb.com/microsoft-70-432.html                                               Page 24 of 415
                                                                                  Total Questions: 396

     It allows fast, flexible ,and reliable indexing for keyword-based querying of text data present in a SQL
      Server database.
     It improves query performance when it is fired on large amount of unstructured text data.
     It can be built not only on columns that contain text data, but also against binary data.
     It is extremely extensible and can support supplementary languages for indexing and querying, as
      well as for filtering of additional document formats.

Reference: http://msdn.microsoft.com/en-us/library/cc879306.aspx

QUESTION NO: 23(A)
You work as a database developer for Softech Inc. The company has a very large database named
Customers that is hosted on a SQL Server 2008 server. The Customers database is used to store the
information of all the customers who make transactions with the company. You want to use full-text
index to search text based on a given word from the Customers database. You also want to retrieve
information about the registered word breakers. Which of the following system stored procedures will
you use to accomplish the task?

A.   sp_help_fulltext_system_components
B.   sp_fulltext_service
C.   sp_help_fulltext_tables_cursor
D.   sp_help_fulltext_tables

ANSWER: A

EXPLANATION:
According to the scenario, you have to search text based on a given word. Therefore, you will need word
breakers.

What are “word breakers and stemmers”?
Word breakers and stemmers carry out linguistic examination of all full-text indexed data. Linguistic
analysis includes finding word boundaries (word-breaking) and conjugating verbs (stemming). Different
languages have different rules for this analysis and different languages can be specified for each full-text
indexed column. Word breakers for each language enhances the accuracy of the resulting terms for that
language.

What is the “sp_help_fulltext_system_components system” stored procedure?
The sp_help_fulltext_system_components system stored procedure returns information for the
registered word-breakers, filter, and protocol handlers in a SQL Server instance. It has the following
syntax:

sp_help_fulltext_system_components

       { 'all'| [ @component_type = ] 'component_type' }

     , [ @param = ] 'param'

where,


http://www.testkingweb.com/microsoft-70-432.html                                              Page 25 of 415
                                                                                 Total Questions: 396

    “““'all'“ returns information for all full-text components.
    “* @component_type = + component_type” specifies the type of component which may be of the
     following types: wordbreaker, filter, protocol handler, or fullpath. If a full path is specified, param
     must also be specified with the full path to the component DLL, otherwise an error message is
     returned.
    “* @param = + param” may be one of the following, depending upon the component type: a locale
     identifier (LCID), the file extension with "." prefix, the full component name of the protocol handler,
     or the full path to the component DLL.

Answer option B is incorrect. The sp_fulltext_service system stored procedure is used to change the
properties of full-text search for SQL Server. It has the following syntax:

sp_fulltext_service [ [@action=] 'action'

    [ , [ @value= ] value ] ]

where,
 “* @action=+ 'action'“ is the property that has to be reset or changed.
 “* @value=+ value” is the value of the specified property. value is sql_variant and its default value is
   NULL. If the value of @value is null, sp_fulltext_service returns the current setting.

Answer option C is incorrect. The sp_help_fulltext_tables_cursor system stored procedure is used to
return a list of those tables that are registered for full-text indexing by using a cursor. It has the
following syntax:

sp_help_fulltext_tables_cursor [ @cursor_return = ] @cursor_variable OUTPUT

    [ , [ @fulltext_catalog_name = ] 'fulltext_catalog_name' ]

    [ , [ @table_name = ] 'table_name' ]

where,
 “* @cursor_return = + @cursor_variable OUTPUT” is the output variable whose type is cursor. The
   cursor is a scrollable, read only, or dynamic cursor.
 “* @fulltext_catalog_name = + 'fulltext_catalog_name'“ is the name of the full-text catalog.
   fulltext_catalog_name is sysname, with a default of NULL.
 “* @table_name =+ 'table_name'“ is the one- or two-part name of the table for which the full-text
   metadata is requested.

Answer option D is incorrect. The sp_help_fulltext_tables system stored procedure is used to return a
list of those tables that are registered for full-text indexing. It has the following syntax:

sp_help_fulltext_tables [ [ @fulltext_catalog_name = ] 'fulltext_catalog_name' ]

    [ , [ @table_name = ] 'table_name' ]




http://www.testkingweb.com/microsoft-70-432.html                                             Page 26 of 415
                                                                               Total Questions: 396

where,
 “* @fulltext_catalog_name =+ 'fulltext_catalog_name'“ is the name of the full-text catalog.
   fulltext_catalog_name is sysname, with a default of NULL. This argument may be in any of the
   following three states:
   1. If fulltext_catalog_name is Null or omitted, all full-text indexed tables associated with the
        database are returned.
   2. If fulltext_catalog_name is specified, but table_name is NULL or omitted, the full-text index
        information is retrieved for each and every full-text indexed table associated with this catalog.
   3. If both fulltext_catalog_name and table_name are specified, a row is returned if table_name is
        associated with fulltext_catalog_name, or else an error is generated.
 “* @table_name =+ 'table_name'“ is the one- or two-part name of the table for which the full-text
   metadata is required.

What is a “stored procedure”?
A stored procedure is a group of SQL statements compiled into a single execution plan. A stored
procedure can be used to implement business logic or as a security mechanism, but it is not executed
automatically. Stored procedures provide better performance, as a fully compiled execution plan is
created and retained in the procedure cache when the stored procedure is created. Hence, the database
does not have to recompile the execution plan every time the procedure is executed.

Reference: SQL Server 2008 Books Online, Contents: "sp_help_fulltext_system_components (Transact-
SQL)"

QUESTION NO: 24(A)
You work as a Database Developer for TechMart Inc. The company has a SQL Server 2008 computer. A
database named DB1 is located on the server, which contains a table called Table1. Table1 is a very large
table that is used frequently. You ascertain that the query contains the following statement:

SELECT col1, col2
FROM Table1
WHERE col3 = <value>

You need to provide maximum query performance so that Table1 must remain available to users. What
should you do to accomplish the task?

A. Use the CREATE STATISTICS statement in DB1 to create missing statistics on col3 of Table1.
B. Set the priority boost server option to 1.
C. Execute the following statement:
   USE DB1
   GO
   CREATE INDEX index1 ON Table1(col3) INCLUDE(col1, col2) WITH (ONLINE = ON)GO
D. Execute the following statement:
   USE DB1
   GO
   CREATE INDEX index1 ON Table1(col3, col2, col1)

ANSWER: C


http://www.testkingweb.com/microsoft-70-432.html                                            Page 27 of 415
                                                                                Total Questions: 396

EXPLANATION:
You want to create an index on the columns 1 to 3 in order to have quicker response when querying
data in columns 1 and 2 based on a value in column 3. Therefore, you should execute the following T-
SQL statement:

USE DB1
GO
CREATE INDEX index1 ON Table1(col3) INCLUDE(col1, col2)
WITH (ONLINE = ON)
GO

where,
 “index1” is the name of the index.
 “col3” is the column or columns on which the index is based.
 “INCLUDE(col1, col2)” specifies the non-key columns to be added to the leaf level of the
   nonclustered index. The nonclustered index can be either unique or non-unique. Column names
   cannot be repeated in the INCLUDE list and cannot be used simultaneously as both non-key and key
   columns. Nonclustered indexes always contain the clustered index columns if a clustered index is
   defined on the table.
 “WITH (ONLINE = ON)” All data types are allowed except text, ntext, and image. The index must be
   created or rebuilt offline (ONLINE = OFF) if any one of the specified non-key columns are
   nvarchar(max), varchar(max), or varbinary(max) data types.

Answer option A is incorrect. CREATE STATISTICS is used to create a histogram and associated density
groups (collections) over the supplied column or set of columns of a table or indexed view. String
summary statistics are also created on statistics built on char, varchar, varchar(max), nvarchar, nchar,
nvarchar(max), ntext and text columns. This statistics information is used by the query optimizer to
choose the most efficient plan for either retrieving or updating data. Up-to-date statistics allow the
optimizer to accurately assess the cost of different query plans, and choose a high-quality plan.

Answer option B is incorrect. The priority boost server option, an advanced option, is used to specify
whether Microsoft SQL Server should run at a higher Microsoft Windows 2000 or Windows 2003
scheduling priority than other processes on the same computer. If this option is set to 1, SQL Server runs
at a priority base of 13 in the Windows 2000 or Windows 2003 scheduler. The default value is 0, which is
a priority base of 7.

Answer option D is incorrect. The statement USE DB1

GO

CREATE INDEX index1 ON Table1(col3, col2, col1) will not accomplish the task. This statement will create
the index based on all three columns i.e. col1, col2, and col3. This is not required according to the
question.

Reference: http://msdn.microsoft.com/en-us/library/ms188783.aspx



http://www.testkingweb.com/microsoft-70-432.html                                            Page 28 of 415
                                                                                Total Questions: 396

QUESTION NO: 25(A)
Which of the following words are automatically excluded from the full-text search index?

A.   Words greater than 1023 characters
B.   Words that include only characters
C.   Words that include symbols
D.   Words that include quotes and exclamations

ANSWER: A

QUESTION NO: 26(A)
Which of the following files contain the schema definition for all the thesaurus files?

A.   tsschema.xml
B.   tsschema.txt
C.   thsschema.xml
D.   thsschema.txt

ANSWER: A

QUESTION NO: 27(A)
Andrew works as an Administrator for Bluewell Inc. The company has a SQL Server 2008 computer
named STSQL. STSQL contains a database named Sales. Andrew wants to install new word breakers and
filters on STSQL that are not registered on it. What should Andrew do to view a list of languages whose
word breakers and filters are currently registered on STSQL?

A.   Use the sys.fulltext_languages catalog view.
B.   Use the sp_help_fulltext_system_components system stored procedure.
C.   Use the sp_fulltext_service system stored procedure.
D.   Use the fulltext_languages catalog view.

ANSWER: B

EXPLANATION:
Andrew should use the sp_help_fulltext_system_components system stored procedure to view a list of
languages whose word breakers and filters are currently registered on the SQL Server.

The sp_help_fulltext_system_components system stored procedure returns information for the
registered word-breakers, filter, and protocol handlers in a SQL Server instance. It has the following
syntax:

sp_help_fulltext_system_components
      { 'all'| [ @component_type = ] 'component_type' }
  , [ @param = ] 'param'

where,
 “““'all'“ returns information for all full-text components.


http://www.testkingweb.com/microsoft-70-432.html                                            Page 29 of 415
                                                                                Total Questions: 396

   “* @component_type = + component_type” specifies the type of component which may be of the
    following types: wordbreaker, filter, protocol handler, or fullpath. If a full path is specified, param
    must also be specified with the full path to the component DLL, otherwise an error message is
    returned.
   “* @param = + param” may be one of the following, depending upon the component type: a locale
    identifier (LCID), the file extension with "." prefix, the full component name of the protocol handler,
    or the full path to the component DLL.

What is a sys.fulltext_languages view?
A sys.fulltext_languages view includes one row per language whose word breakers are cataloged with
SQL Server. The LCID and name of the language are displayed by each language. As soon as the word
breakers are registered for a language, its other linguistic resources (like stemmers, thesaurus files, and
noise words) become available to full-text querying or indexing operations. It is possible to specify the
value of the name or LCID in the full-text queries and full-text index Transact-SQL statements.

Answer option A is incorrect. A sys.fulltext_languages view includes one row per language whose word
breakers are cataloged with SQL Server. The LCID and name of the language are displayed by each
language. As soon as the word breakers are registered for a language, its other linguistic resources (like
stemmers, thesaurus files, and noise words) become available to full-text querying or indexing
operations. It is possible to specify the value of the name or LCID in the full-text queries and full-text
index Transact-SQL statements.

As the criteria mentioned in the question states that Andrew wants to view both the word breakers and
filters registered on STSQL, the sys.fulltext_languages catalog view cannot be used, as this is used to
view only the word breakers and not filters.

Answer option C is incorrect. The sp_fulltext_service system stored procedure is used to change the
server properties of full-text search for SQL Server. It has the following syntax:

sp_fulltext_service [ [@action=] 'action'
  [ , [ @value= ] value ] ]

where,
 “* @action=+ 'action' “ is the property to be changed or reset. action is nvarchar(100), with no
   default. The following properties are returned by this argument: data type, current running value,
   minimum or maximum value, and deprecation status.
 “ * @value=+ value “ is the value of the specified property. value is sql_variant, with a default value
   of NULL.

Answer option D is incorrect. There is no such catalog view as fulltext_languages.


Reference: http://msdn.microsoft.com/en-us/library/dd207002.aspx

QUESTION NO: 28(A)
Which of the following authentication modes should be enabled for SQL Server Agent services before
upgrading SQL Server 2008?


http://www.testkingweb.com/microsoft-70-432.html                                             Page 30 of 415
                                                                                Total Questions: 396

A.   Windows Authentication
B.   Mixed Mode Authentication
C.   Kerebos Authentication
D.   Default Authentication

ANSWER: A

QUESTION NO: 29(A)
Which of the following statements is NOT true about Database Mail?

A. Database Mail is fully supported on a cluster.
B. Database Mail is active by default.
C. Database Mail is not available in SQL Server Express edition.
D. A user must be a member of the DatabaseMailUserRole database role, in the msdb database, to send
Database Mail.

ANSWER: B

QUESTION NO: 30(A)
You work as an Administrator for Bluewell Inc. You want to install SQL Server 2008 in your system and
ensure that the client computers are able to connect to the server. Which of the following protocols
need to be enabled during the process of installation?

Each correct answer represents a part of the solution. Choose all that apply.

A.   TCP/IP
B.   Named Pipes
C.   SMTP (Simple Mail Transfer Protocol)
D.   VIA (Virtual Interface Adapter)

ANSWER: A and B

EXPLANATION:
The TCP/IP and Named pipes protocols have to be enabled to allow the client computers to be able to
connect to the SQL server. A user has to specify the “np: “ or “tcp: “ before the server name in the
connection string. The following table describes the connection string of these two protocols:

     Protocol                                      Connection String
                server=tcp:hostname A user can optionally specify a specific port number. By default, the
TCP/IP
                port is 1433. server=tcp:hostname, portNumber
                server=np:hostname A user can optionally specify a specific named pipe.
Named Pipes
                server=np:\\hostname\pipe\pipeName




http://www.testkingweb.com/microsoft-70-432.html                                          Page 31 of 415
                                                                              Total Questions: 396

It is recommended that a user always use (local) to specify the local machine, before specifying a
protocol. The correct method to get connected to the local machine over TCP is to specify “server =tcp: “
(local), and to use the Named Pipes on the local machine, a user should use “np:( local) “. In order to
specify a protocol when any server is referenced, it is recommended that a user use the following
command:

server = tcp:DBSERVERNAME

Answer option C is incorrect. Simple Mail Transfer Protocol (SMTP) is a protocol for sending e-mail
messages between servers. E-mailing systems use this protocol to send mails over the Internet. SMTP
works on the application layer of the TCP/IP or OSI reference model. However, e-mail clients require
POP or IMAP to retrieve mails from e-mail servers.

Answer option D is incorrect. The Virtual Interface Adapter (VIA) protocol works with VIA hardware.

Reference: SQL Server 2008 Books Online, Contents: "SQL Server Protocols"

QUESTION NO: 31(A)
Which of the following statements is true about database snapshots?

A. A single snapshot can exist on a source database and always on the same server instance as the
   database.
B. Multiple snapshots can exist on a source database and always on the same server instance as the
   database.
C. A single snapshot can exist on a source database and always on a different server instance.
D. Multiple snapshots can exist on a source database and always on a different server instance.

ANSWER: A

QUESTION NO: 32(A)
Which of the following commands will you use to enumerate a complete list of traces that are on?

A.   DBCC TRACESTATUS (1)
B.   DBCC TRACESTATUS (-1)
C.   DBCC TRACESTATUS (0)
D.   DBCC TRACESTATUS (2)

ANSWER: B

QUESTION NO: 33(A)
Which of the following services is used to generate reports and retrieve data from various data sources?

A.   SQL Server Analysis Services
B.   SQL Server Agent Service
C.   SQL Server Reporting Services
D.   SQL Server Notification Services



http://www.testkingweb.com/microsoft-70-432.html                                          Page 32 of 415
                                                                                 Total Questions: 396

ANSWER: C

QUESTION NO: 34(A)
Which of the following services is used to generate reports and retrieve data from various data sources?

A.   SQL Server Analysis Services
B.   SQL Server Agent Service
C.   SQL Server Reporting Services
D.   SQL Server Notification Services

ANSWER: C

QUESTION NO: 35(A)
You manage a SQL Server 2008 server within a domain named Company.com. You are deploying an
instance of SQL Server Reporting Services (SSRS). SSRS needs to be usable as soon as possible. Plans are
being developed to purchase and install Microsoft Office SharePoint Services (MOSS). When it is
installed, SSRS will be integrated with MOSS. What mode should you use when installing SSRS?

A.   Deploy SSRS using SharePoint Integrated Mode.
B.   Deploy SSRS using Native Mode.
C.   Install the SSRS instance and select Install, But Do Not Configure the Report Server.
D.   Wait until MOSS is deployed before installing an instance of SSRS.

ANSWER: B

EXPLANATION:
When installing SQL Server Reporting Services, a user has three choices for the reporting Services
Configuration:
 “Native Mode:” Setup will install the report server and configure it in Native using default values,
   and the report server will be usable as soon as setup is finished.
 “Integrated Mode:” Setup will create the report server database in SharePoint integrated mode, but
   the report server will not be available until a SharePoint product is installed and configured.
 “Install, but do not configure the report server:” Setup will install SSRS, but the Reporting Services
   Configuration tool must be used to configure SSRS after the installation is finished.

Since the report server will be usable as soon as Setup is finished if you choose Native Mode during the
installation, Native Mode is the best choice for the scenario.

Answer option A is incorrect. Since Microsoft Office SharePoint Services (MOSS) is not currently
installed, SSRS won't be usable if SharePoint Integrated Mode is selected. It is possible to switch modes
after MOSS is installed - the only limitation is that it is not possible to migrate data between the two
modes.

Answer option C is incorrect. If you choose Install, But Do Not Configure the Report Server during the
installation, it will take additional configuration before SSRS is ready.




http://www.testkingweb.com/microsoft-70-432.html                                             Page 33 of 415
                                                                                Total Questions: 396

Answer option D is incorrect. It is not necessary to wait until MOSS is deployed to take advantage of
SSRS. SSRS can be installed and used almost immediately.

What is “SSRS”?
SSRS stands for SQL Server Reporting Services. It creates and manages Web-enabled reporting services.
It is used to create and generate reports, to retrieve data from various data sources, and to publish
reports in various formats.

Reference: http://msdn.microsoft.com/en-us/library/bb326345.aspx

QUESTION NO: 36(A)
Which of the following services is a prerequisite to upgrade SQL Server 2008?

A.   SQL Server Agent Services
B.   SQL Server Integration Services
C.   Windows Installer Services
D.   Windows Services

ANSWER: C

QUESTION NO: 37(A)
You work as an Administrator for Softech Inc. The company has a SQL Server 2008 computer. Service
Broker is used by the company to manage data requests between SQL Server databases. You want to
make changes to objects for an existing service. You want to ensure that unless and until the changes
are completed no messages can be received by the service. What should you do to accomplish the task?

A. Create the queue with EXECUTE AS argument set to SELF. Set it to OWNER when the service is ready.
B. Create the queue with STATUS OFF. Set it to ON when the service is ready.
C. Create the queue with only the queue_name argument. Set the other parameters when the service is
   ready.
D. Create the queue with max_queue_readers = 0. Set it to an optimum value when the service is ready.

ANSWER: B

EXPLANATION:
The STATUS clause is used to determine whether the queue is enabled or not. When a queue is set to
STATUS = OFF, it does not allow any messages to be removed or added to the queue.

A queue can be created with the help of the following syntax:

CREATE QUEUE <object>
 [ WITH
   [ STATUS = { ON | OFF } [ , ] ]
   [ RETENTION = { ON | OFF } [ , ] ]
   [ ACTIVATION (
      [ STATUS = { ON | OFF } , ]
        PROCEDURE_NAME = <procedure> ,


http://www.testkingweb.com/microsoft-70-432.html                                           Page 34 of 415
                                                                                Total Questions: 396

      MAX_QUEUE_READERS = max_readers ,
      EXECUTE AS { SELF | 'user_name' | OWNER }
      )]
   ]
[ ON { filegroup | [ DEFAULT ] } ]
[;]

where,
 “schema_name (object) “ is the name of the schema to which the new queue belongs.
 “database_name (object) “ is the name of the database within which to create the new queue.
 “queue_name” is the name of the queue to create.
 “STATUS (Queue) “ specifies whether the queue is available (ON) or unavailable (OFF).
 “RETENTION” specifies the retention setting for the queue.
 “ACTIVATION” specifies information about which stored procedure you have to start to process
   messages in this queue.
 “STATUS (Activation) “ specifies whether Service Broker starts the stored procedure. When STATUS
   is ON, the queue begins the stored procedure specified with PROCEDURE_NAME when the number
   of procedures currently running is less than MAX_QUEUE_READERS and when messages reach on
   the queue faster than the stored procedures receive messages. When STATUS is OFF, the queue
   does not start the stored procedure. ON is the default value if this clause is not specified.
 “PROCEDURE_NAME = “ specifies the name of the stored procedure to start to process messages in
   this queue.
 “MAX_QUEUE_READERS = max_readers” specifies the maximum number of instances of the
   activation stored procedure that the queue starts at the same time. The value of max_readers
   should be between 0 and 32767.
 “EXECUTE AS” specifies the SQL Server database user account under which the activation stored
   procedure runs.
    “SELF” specifies that the stored procedure executes as the current user.
    “user_name” is the name of the user who the stored procedure executes as.
    “OWNER” specifies that the stored procedure executes as the owner of the queue.
 “ON filegroup | * DEFAULT +” specifies the SQL Server filegroup on which to create this queue.

What is a “queue”?
Queue is like a container that stores messages. When Service Broker receives a message for a service, it
inserts the message into the queue for that service. Each service is associated with one queue. For each
conversation, a queue returns messages in the same order in which the messages were sent by the
sender.

What is “Service Broker”?
Service Broker, a new feature introduced in SQL Server 2005, is successfully used in later versions. It is
part of the Database Engine. It provides a message-based communication platform that enables
independent application components to perform as a whole. Service Broker is used for the applications
that use a single instance or multiple instances of SQL Server 2005 and later versions across the
network. It provides reliable messaging between multiple instances of SQL Server.

What are the “advantages of service broker”?



http://www.testkingweb.com/microsoft-70-432.html                                            Page 35 of 415
                                                                             Total Questions: 396

The advantages of service broker are as follows:
 The integrated design of service broker enhances application's performance and administration.
 Service broker provides loose coupling which provides workload flexibility.
 Service broker automatically locks all the related messages in the same conversation group. This
    enables only one application instance to receive and process the messages.
 Service broker allows automatic activation of applications, which helps application to scale with the
    message volume.
 Service broker provides message ordering and coordination, which help in the development of a
    simplified application.

Reference: http://msdn.microsoft.com/en-us/library/ms190495.aspx

QUESTION NO: 38(A)
You have installed SQL Server 2008 on a server. You are able to launch SQL Server Management Studio
locally. However, when you try to connect remotely, you receive an error message indicating that SQL
Server doesn't allow remote connections. You look at the services applet as shown in the following
figure:




What should you do?




http://www.testkingweb.com/microsoft-70-432.html                                         Page 36 of 415
                                                                                Total Questions: 396

A.   Change the SQL Server Agent service to Automatic.
B.   Start the SQL Server Browser service in the Automatic Startup Type.
C.   Change the SQL Server (MSSQLSERVER) service to Manual.
D.   Click Start to start the Windows Installer service.

ANSWER: B

EXPLANATION:
The SQL Server Browser needs to be running to support remote connections. The SQL Server Browser
service currently has the startup type set to Disabled. When set to Automatic, it will start as part of the
normal server boot process.

SQL Server Browser service is a tool used to connect to an SQL Server instance if a DBA is not able to
connect to that instance through DAC. It can be configured either during the installation of SQL Server or
by using the Surface Area Configuration Manager tool. This service starts automatically by default. It
listens to incoming requests and connects to the correct instance. It also displays a list of all available
instances on the server and connects to DAC endpoints.

Answer option A is incorrect. SQL Server Agent is used to automate administrative tasks. It is used to
add jobs, store job information, run a job on a schedule, etc. By default, it is disabled at the time of
installation. To enable this tool, a user has to explicitly start it.

The SQL Server Agent service is used for maintenance and management tasks. It is not needed for
connectivity.

Answer option C is incorrect. The SQL Server (MSSQLSERVER) service is the primary service for SQL
Server and should be set to Automatic as shown in the figure.

Answer option D is incorrect. The Windows Installer service isn't related to SQL Server. Starting this
service would not affect remote connections for SQL Server.

Reference: http://msdn.microsoft.com/en-us/library/ms181087.aspx

QUESTION NO: 39(A)
James works as an Administrator for Softech Inc. The company has a SQL Server 2005 computer named
SQL1. SQL1 contains a database named Trading. James wants to migrate the Trading database from
SQL1 to SQL Server 2008. To accomplish the task, James decides to upgrade SQL1 to SQL Server 2008 by
running the SQL Server 2008 Upgrade Advisor and the SQL Server 2008 Setup utility. A report is
generated by the Upgrade Advisor on the basis of which issues that have to be fixed are identified either
before or after the upgrade to SQL Server 2008. Choose the SQL Server components that the Upgrade
Advisor analyzes before performing the upgrades to SQL Server 2008.

1.   Database Engine
2.   Reporting Services (SSRS)
3.   Notification Services
4.   Analysis Services (SSAS)
5.   Integration Services (SSIS) or Data Transformation Services (DTS)


http://www.testkingweb.com/microsoft-70-432.html                                             Page 37 of 415
                                                                                 Total Questions: 396

6. Database Engine Tuning Advisor

ANSWER:
1. Database Engine
2. Analysis Services (SSAS)
3. Reporting Services (SSRS)
4. Integration Services (SSIS) or Data Transformation Services (DTS)

EXPLANATION:
SQL Server 2008 Upgrade Advisor helps a user to prepare for upgrades to SQL Server 2008. It generates
reports after analyzing the previously installed SQL Server 2005, SQL Server 2000, or SQL Server 7.0. On
the basis of the report generated, the issues that have to be fixed are identified either before or after
the upgrade to SQL Server 2008. The Upgrade Advisor Home page appears when Upgrade Advisor is run.
The tools that could be run from the home page are as follows:
 Upgrade Advisor Analysis Wizard
 Upgrade Advisor Report Viewer
 Upgrade Advisor Help

Upgrade Advisor analyzes the following SQL Server components:
 Database Engine
 Analysis Services (SSAS)
 Reporting Services (SSRS)
 Integration Services (SSIS) or Data Transformation Services (DTS)

What is the “Database Engine”?
The Database Engine is the underlined component of a database. It is a collection of information stored
on the computer in a systematic way. It is mainly used for storing, securing, and processing data in the
database. The Database Engine ensures controlled access and rapid transaction processing to meet the
requirements of the most demanding data consuming applications in any organization. SQL Server
supports up to fifty instances of the Database Engine on one computer.

What is “SSAS”?
SSAS stands for SQL Server Analysis Services. It stores, processes, and secures data. It delivers OLAP
(Online Analytical Processing) and data mining functionalities for business intelligence applications. It is
used to design, create, and manage multi-dimensional structures that contain data aggregated from
other data sources.

What is “SSRS”?
SSRS stands for SQL Server Reporting Services. It creates and manages Web-enabled reporting services.
It is used to create and generate reports, to retrieve data from various data sources, and to publish
reports in various formats.

What is “SSIS”?
SSIS (SQL Server Integration Services) is an upgrade of DTS (Data Transformation Services), which is a
feature of the previous version of SQL Server. SSIS packages can be created in BIDS (Business
Intelligence Development Studio). These can be used to merge data from heterogeneous data sources



http://www.testkingweb.com/microsoft-70-432.html                                              Page 38 of 415
                                                                                 Total Questions: 396

into SQL Server. They can also be used to populate data warehouses, to clean and standardize data, and
to automate administrative tasks.

What is “DTS”?
Data Transformation Services (DTS) is a utility that helps in extracting, transforming, and consolidating
data from multiple sources into single or multiple destinations by using an OLE DB-based architecture.
Using DTS also helps in transferring data between multiple sources interactively or automatically on a
regular basis.

What is a “Web service”?
A Web service is a unit of application logic that can be accessible via standard protocols. A Web service
is defined by the W3C as a software system designed to support interoperable machine-to-machine
interaction over a network. Web services are frequently just Web APIs that can be accessed over a
network, such as the Internet, and executed on a remote system hosting the requested services.
Remote clients use XML for data transport and SOAP for using services.

A Web service must have the following characteristics:
1. It must be registered with a central repository so that the client can look up it for a desired service.
2. It contains a public interface for the client to invoke services.
3. It should use standard protocols for communication.
4. It should be accessible over the Web.
5. It should support loose coupling with distributed systems so that the system running on different
   technologies can cooperate with each other.

The W3C Web service definition encompasses many different systems, but in common usage, the term
refers to clients and servers that communicate over the HTTP protocol used on the Web. Such services
tend to fall into one of two camps: Big Web Services and RESTful Web Services. Big Web Services use
XML messages that follow the SOAP standard and have been popular with traditional enterprise. RESTful
Web services have been regaining popularity, particularly with Internet companies. These also meet the
W3C definition, and are often better integrated with HTTP than SOAP-based services.

Reference: http://msdn.microsoft.com/en-us/library/ms144256.aspx

QUESTION NO: 40(A)
You have installed SQL Server 2008 on a server using the default file locations. You were viewing the
current SQL Server log via SQL Server Management Studio (SSMS), but SSMS will no longer launch. What
is the path to the current error log?

A.   C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\LOG\Summary.txt
B.   C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Log\Errorlog
C.   C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Log\Errorlog.1
D.   C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Log\log

ANSWER: B

EXPLANATION:



http://www.testkingweb.com/microsoft-70-432.html                                             Page 39 of 415
                                                                                  Total Questions: 396

Logs are located in the C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log
directory. The most recent log is ErrorLog with no extension. The SQL Server Agent error log is located
here and is named SQLAgent.Out.

What is the “SQL Server Agent error log”?
SQL Server Agent generates an error log that records errors and warnings by default. Up to nine SQL
Server Agent error logs are maintained by SQL Server. Each archived log has an extension that indicates
the relative age of the log. For example, an extension of .1 indicates the newest archived error log and
an extension of .9 indicates the oldest archived error log. The following warnings and errors are
displayed in the log:
 Warning messages that give information about potential problems, such as "Job was deleted while it
    was running."
 Those error messages that usually need intervention by a system administrator, such as "Unable to
    start mail session." Error messages can be sent to a particular computer or user by net send.

By default, execution trace messages are not written to the SQL Server Agent error log, because they
can fill it. When the error log is full, the user's ability to select and analyze more difficult errors is
reduced. When SQL Server Agent is stopped, the location of the SQL Server Agent error log can be
modified. When the error log is empty, it cannot be opened.

Answer option A is incorrect. If you need to troubleshoot the installation, you can look in C:\Program
Files\Microsoft SQL Server\100\Setup Bootstrap\LOG\Summary.txt. This log includes events logged
during the install. It does not include regular SQL Server error logs.

Answer option C is incorrect. The most current log does not have a number as an extension. Past logs
are archived with a number. The log before the current one is ErrorLog.1, and the log before that one is
ErrorLog.2.

Answer option D is incorrect. SQL Profiler logs are named log, log_1, log_2, and so on.

What is “SQL Server Management Studio”?
SQL Server Management Studio (SSMS) is a combination of various graphical tools such as Query
Analyzer, Enterprise Manager, and Analysis Manager. SSMS is used to access, configure, manage,
administer, and develop all the objects and components of SQL Server.

Reference: MSDN SQL Server 2008 online, Contents: "Viewing the SQL Server Error Log"

QUESTION NO: 41(A)
Which of the following tables does not contain any index?

A.   Partitioned table
B.   System table
C.   Heap table
D.   Temporary table

ANSWER: C



http://www.testkingweb.com/microsoft-70-432.html                                              Page 40 of 415
                                                                               Total Questions: 396

QUESTION NO: 42(A)
Which of the following editions of SQL Server supports asynchronous database mirroring (high-
performance operating mode)?

A.   SQL Server Enterprise Edition
B.   SQL Server Express Edition
C.   SQL Server Standard Edition
D.   SQL Server Workgroup Edition

ANSWER: A

QUESTION NO: 43(A)
You have installed a default instance and a named instance of SQL Server 2008 on a server. You are able
to connect to both instances locally using SSMS. During testing, you realize you can connect using the
DAC to the default instance, but you can't connect to the named instance. What is the likely problem?

A.   DAC can only connect to the default instance.
B.   You need to use the enable DAC command with the sp_configure transact SQL (T-SQL) statement.
C.   The SQL Server service is not running.
D.   The SQL Server Browser service is not running.

ANSWER: D

EXPLANATION:
Dedicated Administrator Connection (DAC) is a mode of connection to SQL Server. If there is a situation
where a Database Administrator is not able to connect to SQL Server, the DAC is used to connect to SQL
Server for troubleshooting purposes. Only the DBA has the permission to connect to SQL Server through
DAC. The DBA can use SQL Server Management Studio or SQL CMD to connect to the server through
DAC. In order to run DAC, SQL Server Browser Service must be enabled.

DAC can be used to connect to SQL Server when SQL Server is not responding to standard connection
requests. DAC will connect to the default instance of SQL Server automatically using port 1434. When
connecting to a named instance, or if the default instance doesn't respond, it uses the SQL Server
Browser service to connect. If SQL Server Browser is not running, the connection request returns an
error.

What is “SQL Server Browser Service”?
SQL Server Browser service is a tool used to connect to an SQL Server instance if a DBA is not able to
connect to that instance through DAC. It can be configured either during the installation of SQL Server or
by using the Surface Area Configuration Manager tool. This service starts automatically by default. It
listens to incoming requests and connects to the correct instance. It also displays a list of all available
instances on the server and connects to DAC endpoints.

Answer option A is incorrect. DAC can be used to connect to any instance of SQL Server 2008 running on
a server. It is not restricted to only the default instance.




http://www.testkingweb.com/microsoft-70-432.html                                            Page 41 of 415
                                                                                 Total Questions: 396

Answer option B is incorrect. The sp_configure stored procedure is used to display or change global
configuration settings for the current server. The ALTER DATABASE statement and the SET statement are
used to change database-level settings and settings that affect only the current user session,
respectively. The syntax of sp_configure is as follows:

sp_configure [ [ @configname = ] 'option_name'
  [ , [ @configvalue = ] 'value' ] ]

where,
 “* @configname = + 'option_name'“ is the name of a configuration option. option_name is
   varchar(35), with a default of NULL. The SQL Server Database Engine identifies any unique string
   that is part of the configuration name. A complete list of options is returned if no string is specified.
 “* @configvalue = + 'value'“ is the new configuration setting. value is int, with a default of NULL.

The sp_configure T-SQL statement doesn't include an enable DAC command.

Answer option C is incorrect. Local connections will not be successful if the SQL Server service is not
running. However, since connections with the SQL Server Management Studio (SSMS) are successful, the
SQL Server service must be running.

What is “SQL Server Management Studio”?
SQL Server Management Studio (SSMS) is a combination of various graphical tools such as Query
Analyzer, Enterprise Manager, and Analysis Manager. SSMS is used to access, configure, manage,
administer, and develop all the objects and components of SQL Server.

What is a “default instance”?
A default instance does not require a client to specify the name of the instance to make a connection. A
default instance is identified solely by the name of the computer on which the instance is running. It
does not have a separate instance name. Clients specify only the computer name in their requests to
connect to SQL Server. There can be only one default instance on any computer, and the default
instance can be any version of SQL Server.

What is a “named instance”?
A named instance is determined by the user during Setup. It is identified by an instance name specified
during installation of SQL Server. The client must provide both the computer name and the instance
name to connect to SQL Server 2008. There can be multiple named instances running on a computer.
The user can install SQL Server as a named instance without installing the default instance first. The
default instance could be an installation of SQL Server 2000, SQL Server 2005, or SQL Server 2008. Only
one installation of SQL Server, regardless of the version, can be the default instance at one time.

Reference: http://msdn.microsoft.com/en-us/library/ms189595.aspx
           http://msdn.microsoft.com/en-us/library/ms181087.aspx

QUESTION NO: 44(A)
You are asked to troubleshoot a SQL Server 2008 server. It has a default instance of SQL Server 2008
installed and two named instances. Users were able to connect to all of the instances but currently they
can't connect to any of the instances. What is the likely problem?


http://www.testkingweb.com/microsoft-70-432.html                                              Page 42 of 415
                                                                                 Total Questions: 396

A.   The SQL Server service is not running.
B.   The SQL Server Integration Services service is not running.
C.   The SQL Server Browser service is not running.
D.   The SQL Server Browser service for each instance is not running.

ANSWER: C

EXPLANATION:
If users are unable to connect to any instance, it is likely that the SQL Server Browser service is not
running. The SQL Server Browser service is responsible for servicing connections over the network.

What is “SQL Server Browser Service”?
SQL Server Browser service is a tool used to connect to an SQL Server instance if a DBA is not able to
connect to that instance through DAC. It can be configured either during the installation of SQL Server or
by using the Surface Area Configuration Manager tool. This service starts automatically by default. It
listens to incoming requests and connects to the correct instance. It also displays a list of all available
instances on the server and connects to DAC endpoints.

Answer option A is incorrect. Each instance of SQL Server will have its own service. While it's
worthwhile to check the SQL Server service for each instance and ensure that it's running, this answer
implies there is only one SQL Server service to check.

Answer option B is incorrect. SSIS (SQL Server Integration Services) is an upgrade of DTS (Data
Transformation Services), which is a feature of the previous version of SQL Server. SSIS packages can be
created in BIDS (Business Intelligence Development Studio). These can be used to merge data from
heterogeneous data sources into SQL Server. They can also be used to populate data warehouses, to
clean and standardize data, and to automate administrative tasks.

The SQL Server Integration Services (SSIS) service is not needed for SQL Server to run or users to connect
to an instance of SQL Server.

Answer option D is incorrect. There is only one SQL Server Browser service for a SQL Server installation.
The server does not have a separate instance for each SQL Server instance installed on the server.

Reference: http://msdn.microsoft.com/en-us/library/ms181087.aspx

QUESTION NO: 45(A)
Which of the following statements is true about the witness server role in database mirroring?

A.   Witness Server is an optional role and can service multiple database mirroring pairs.
B.   Witness Server is a mandatory role and can service multiple database mirroring pairs.
C.   Witness Server is an optional role and can service a single database mirroring pair.
D.   Witness Server is a mandatory role and can service a single database mirroring pair.

ANSWER: A



http://www.testkingweb.com/microsoft-70-432.html                                              Page 43 of 415