Testking Web 70-453 Exam - Transition Your MCITP SQL Server 2005 DBA to MCITP SQL Server 2008

Document Sample
Testking Web 70-453 Exam - Transition Your MCITP SQL Server 2005 DBA to MCITP SQL Server 2008 Powered By Docstoc
					                                                   Total Questions: 229




    Questions and Answers
                                          with
         Detailed Explanations and Objectives




                              Vendor: Microsoft
                            Certification: MCITP
                             Exam Code: 70-453
                    Exam Name:
  Upgrade: Transition Your MCITP SQL Server 2005
          DBA to MCITP SQL Server 2008
                            Total Questions: 229




http://www.testkingweb.com/microsoft-70-453.html            Page 1 of 190
                                                                          Total Questions: 229

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

   I      Designing a SQL Server Instance and a Database Solution

   J      Designing a Database Server Security Solution

   K      Designing a Database Solution for High Availability

   L      Designing a Backup and Recovery Solution

   M      Designing a Monitoring Strategy

   N      Designing a Strategy to Maintain and Manage Databases

   O      Designing a Strategy for Data Distribution


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




http://www.testkingweb.com/microsoft-70-453.html                                   Page 2 of 190
                                                                               Total Questions: 229

QUESTION NO: 1(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.

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



http://www.testkingweb.com/microsoft-70-453.html                                             Page 3 of 190
                                                                                 Total Questions: 229

        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:
Dedicated Administrator Connection (DAC) , SQL server Browser Services


QUESTION NO: 2(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


http://www.testkingweb.com/microsoft-70-453.html                                               Page 4 of 190
                                                                              Total Questions: 229

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.

Protocols    Connection String
             server=tcp:hostname
             A user can optionally specify a specific port number. By
TCP/IP
             default, the port is 1433.
             server=tcp:hostname, portNumber


Named        server=np:hostname
Pipes        A user can optionally specify a specific named pipe.
             server=np:\\hostname\pipe\pipeName

It is recommended that a user should 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 should 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 the e-mail servers.

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

REFERENCE:
SQL Server 2008 Books Online, Contents: SQL Server Installation


QUESTION NO: 3(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.


http://www.testkingweb.com/microsoft-70-453.html                                           Page 5 of 190
                                                                              Total Questions: 229

D. Witness Server is a mandatory role and can service a single database mirroring pair.

ANSWER: A


QUESTION NO: 4(A)
Which of the following tools is used to configure a server to accept encrypted connections?

A. SQL Server Configuration Manager
B. SQL Server Management Studio
C. SQLCMD
D. Business Intelligence Development Studio

ANSWER: A


QUESTION NO: 5(A)
You have upgraded a SQL Server 2005 server to SQL Server 2008. An operator is configured to use Net
Send messages, but they are not working. What is the likely cause and what should you do?

A. The Net Send service is not running.
B. Net send needs to be replaced with a pager operator.
C. The Messenger service is not running.
D. Net send is not supported in SQL Server 2008.

ANSWER: C

EXPLANATION:
Net send requires the Messenger service to be running on both the server hosting SQL Server and the
client where the net send message is sent.

Answer option a is incorrect. A net send service does not exist. The Messenger service handles net send
traffic but is often disabled on servers due to security risks.

Answer option b is incorrect. The pager and net send options have been deprecated and are no longer
recommended for use. If anything, the net send should be replaced with email addresses.

Answer option d is incorrect. Net send is supported for backward compatibility in SQL Server 2008.

REFERENCE:
SQL Server Books Online, Contents: Net send


QUESTION NO: 6(A)
You manage a SQL Server 2008 server that has been used for testing. The server hosts a copy of the
Sales production database. However, a developer reports that he is having problems connecting to the
Sales database.


http://www.testkingweb.com/microsoft-70-453.html                                              Page 6 of 190
                                                                                 Total Questions: 229

You open the Services applet and see the following figure:




What is the problem?

A. The SQL Server service isn't running.
B. The SQL Server Agent service isn't running.
C. The SQL Server VSS Writer service isn't running.
D. The SNMP Trap Service isn't running.

ANSWER: A

EXPLANATION:
The SQL Server service is the primary service for the SQL Server Database Services. If the SQL Server
service is not running, then SQL does not run and all connections fail. While the figure shows that the
SQL Server (MSSQLSERVER) service is set to automatically start, the status is blank, indicating it is not
running.




http://www.testkingweb.com/microsoft-70-453.html                                              Page 7 of 190
                                                                               Total Questions: 229

Answer option b is incorrect. 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 VSS Writer services are used to back up/restore Microsoft
SQL Server through the Windows VSS infrastructure. It isn't needed for normal operation of SQL Server.

Answer option d is incorrect. SNMP Trap Service is used to receive trap messages that are generated by
local or by remote SNMP agents. These messages are then forwarded to SNMP management programs
that are running on the user's computer. When SNMP Trap Service is configured for an agent, it
generates trap messages if any particular events occur. These messages are sent to a trap destination.
Trap destinations include the computer name, the IP address, or the Internetwork Packet Exchange (IPX)
address of the management system. The trap destination should be a network-enabled host that is
running the SNMP management software.
This service is not needed for establishing connectivity.

REFERENCE:
MSDN SQL Server 2008, Contents: SQL Server Services


QUESTION NO: 7(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.

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.


http://www.testkingweb.com/microsoft-70-453.html                                            Page 8 of 190
                                                                                Total Questions: 229


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: 8(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


http://www.testkingweb.com/microsoft-70-453.html                                             Page 9 of 190
                                                                               Total Questions: 229

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.


What is the Network Service account?
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 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



http://www.testkingweb.com/microsoft-70-453.html                                           Page 10 of 190
                                                                               Total Questions: 229

       Domain User account


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


QUESTION NO: 9(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:




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.


What is SSIS?
SSIS (SQL Server Integration Services) is an upgrade of DTS (Data Transformation Services), which is a


http://www.testkingweb.com/microsoft-70-453.html                                           Page 11 of 190
                                                                              Total Questions: 229

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.


What is the Network Service account?
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 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.


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:
MSDN SQL Server 2008 Books Online , Contents: Setting Up Windows Service Accounts


QUESTION NO: 10(A)
Which of the following features of SQL Server is responsible for managing SQL Server services and
protocols?

A. SQL Server Configuration Manager
B. SQL Server Enterprise Manager
C. SQL Server Buffer Manager
D. SQL Server Manager

ANSWER: A


QUESTION NO: 11(A)
You have just installed a default instance and a named instance of SQL Server 2008 on a server within
your network. Users are unable to connect to any of the SQL Server 2008 instances. The command-line


http://www.testkingweb.com/microsoft-70-453.html                                          Page 12 of 190
                                                                                 Total Questions: 229

tool Ping shows that the clients have TCP/IP connectivity. What should you do?

A. Open port 1434 on the server.
B. Open ports 1433 and 1434 on the server.
C. Configure the SQL Server Browser service.
D. Configure the SQL Server Agent service.

ANSWER: C

EXPLANATION:
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.
The SQL Server Browser service automatically listens on port 1434 for connections to any instance of
SQL Server 2008 running on a server when it has been configured to automatically start. SQL clients will
normally connect using port 1434 and the SQL Server Browser service will handle the request.

Answer option a is incorrect. If the SQL Server Browser service is not running, users must include port
1433 in the connection query to connect to the default instance. Opening port 1434 won't change the
client's connection query.

Answer option b is incorrect. If the SQL Server Browser service is not running, users must include port
1433 in the connection query to connect to the default instance. Opening ports 1433 and/or 1434 won't
change the client's connection query.

Answer option d is incorrect. The SQL Server Agent service is used for maintenance and management
tasks. It is not needed for connectivity. 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.


What are the different types of performance counters offered by SQL Server Agent?
The different types of performance counters offered by SQL Server Agent are as follows:
     Alerts: It allows the user to analyze and observe the number of alerts that have been raised
        since the beginning of the SQL Server Agent service, and the number of alerts that are raised
        each minute.
     Jobs: It allows the user to monitor information such as the job success rate and the number of
        active jobs. This counter also notifies whether the job was started on demand, by an alert, or by
        a schedule.
     Job Steps: It allows the user to monitor information such as the number of job steps that are
        waiting for their execution and the number of times a specific step has been retried.
     Statistics: It allows the user to monitor the number of times the Agent service has restarted the
        SQL Server service.




http://www.testkingweb.com/microsoft-70-453.html                                              Page 13 of 190
                                                                                 Total Questions: 229

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


QUESTION NO: 12(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?

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




http://www.testkingweb.com/microsoft-70-453.html                                              Page 14 of 190
                                                                              Total Questions: 229

QUESTION NO: 13(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


QUESTION NO: 14(A)
Which users can turn on trace flags?

A. Members of the securityadmin fixed server role
B. Members of the serveradmin fixed server role
C. Members of the setupadmin fixed server role
D. Members of the sysadmin fixed server role

ANSWER: D


QUESTION NO: 15(A)
You work as a Database Administrator for Techsoft Inc. The company has a SQL Server 2008 computer
named stSQL. The server is configured with the SQL Server and Windows Authentication mode to
authenticate users. The stSQL server is not responding due to some reasons. You are unable to connect
to the server. You have to use the Dedicated Administrator Connection (DAC) to connect to the server to
diagnose the issue. Which of the following actions will you use to accomplish the task?

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


A. To connect to the DAC, specify ADMIN: stSQL in the Connect to the Server dialog box.
B. To connect to the DAC, specify administrator: stSQL in the Connect to the Server dialog box.
C. To connect to the DAC, specify ADMIN: stSQL at the sqlcmd command-line utility with an
   administrator switch (-A) at the command prompt.
D. To connect to the DAC, specify administrator: stSQL at the sqlcmd command-line utility.

ANSWER: A AND C

EXPLANATION:
Hundreds of errors occur within a SQL Server computer. In order to diagnose and fix these errors, you
connect to the server. There are many cases when the server is very busy and cannot allocate memory
and processor resources to process requests. In this situation, the server cannot allow even an
administrator to connect. SQL Server provides a feature known as the Dedicated Administrator
Connection (DAC) to fix this type of error. This feature allows you as an administrator who is a member
of the sysadmin server role to connect to a SQL Server computer even if the server can no longer


http://www.testkingweb.com/microsoft-70-453.html                                          Page 15 of 190
                                                                                Total Questions: 229

process the requests. This feature, i.e. the DAC, is not used for all administrative operations. If you want
to connect to a SQL Server computer using the DAC, you must prefix ADMIN: to the name of the SQL
Server computer. You can also use the DAC with the sqlcmd command-line utility using an administrator
switch (-A).

The sqlcmd is a command-line utility. Using this utility, you can execute ad hoc queries, T-SQL
statements, system procedures, and script files and can automate T-SQL scripting tasks in the same
manner as you execute in the query window in SQL Server Management Studio.

Before you can start using the sqlcmd utility with the DAC, you first enter sqlcmd with an administrator
switch (-A) at the command prompt and then connect to an instance of SQL Server. After you have
connected to SQL Server, you can run T-SQL statements. The result will be displayed at the command
prompt.

In order to connect to stSQL using SQL Server Management Studio, you should click the Database Engine
Query tool on the toolbar. The Connect to Database dialog box then appears. After providing the
required information, you will be connected to the stSQL server.

REFERENCE:
SQL Server 2008 Books Online, Contents: Command Prompt Utilities: sqlcmd Utility, Using a Dedicated
Administrator Connection


QUESTION NO: 16(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:




http://www.testkingweb.com/microsoft-70-453.html                                             Page 16 of 190
                                                                                 Total Questions: 229




What should you do?

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.


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


http://www.testkingweb.com/microsoft-70-453.html                                             Page 17 of 190
                                                                                  Total Questions: 229

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: 17(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: 18(A)
Which of the following SQL Server 2008 features is used to review server attributes for local or remote
servers?

A. xp_msver extended stored procedure
B. @@VERSION stored procedure
C. SERVERPROPERTY function
D. @@ERROR function

ANSWER: A

EXPLANATION:
The xp_msver extended stored procedure is used to review server attributes for local or remote servers.



http://www.testkingweb.com/microsoft-70-453.html                                               Page 18 of 190
                                                                                Total Questions: 229

From the local server, a user can view databases, files, logins, and tools for a remote server. The
xp_msver stored procedure is used to return the following:
    Version information about Microsoft SQL Server
    Information about the actual build number of the server
    Information about the server environment

The information returned by xp_msver can be used within Transact-SQL statements, stored procedures,
batches, and so on, to enhance logic for platform-independent code.


Answer option b is incorrect. The @@VERSION stored procedure is used to return the version, build
date, processor architecture, and operating system for the current installation of SQL Server. The results
of the @@VERSION stored procedure are presented as one nvarchar string. The following example
returns the version information for the current installation.

SELECT @@VERSION AS 'SQL Server Version'


Answer option c is incorrect. The SERVERPROPERTY function is used to return property information
about the server instance. It has the following syntax:

SERVERPROPERTY (propertyname)

where propertyname is an expression that contains the information about the property to be returned
for the server. Some of the property names and their description are tabulated below:

Property         Description
Collation        It returns the name of the default collation for the server.
InstanceName     It returns the name of the instance to which the user is connected.
                 It returns the name of the Windows computer on which the server instance
MachineName
                 is running.
LCID             It returns the Windows locale identifier (LCID) of the collation.
                 It returns the version of the instance of SQL Server in the form of
ProductVersion
                 'major.minor.build'.



Answer option d is incorrect. The @@ERROR function detects errors in a Transact-SQL statement. It
returns the error number of an error that is encountered by the previous SQL statement in an
application. It returns zero if the last Transact-SQL statement is executed successfully. If the statement
generates any error, @@ERROR function returns the error number of the error.

The @@ERROR function is used in two possible ways:
     It is used immediately after a Transact-SQL statement.



http://www.testkingweb.com/microsoft-70-453.html                                             Page 19 of 190
                                                                                Total Questions: 229

       It is saved in an integer variable immediately after the completion of a Transact-SQL statement.
        The variable can be used for later use.

Configuration functions are nondeterministic functions that return the information about current
configuration option settings. Some of the configuration functions are tabulated in the following table:

Function            Return value
@@SPID              Returns the session ID of the current user process.
@@SERVERNAME Returns the name of the local server that is running SQL Server.
                    Returns version, processor architecture, build date, and operating system
@@VERSION
                    for the current installation of SQL Server.




What is an extended stored procedure?
An extended stored procedure extends Transact-SQL features by allowing users to apply logic in
functions contained in dynamic-link library (DLL) files, and then calling those functions from Transact-
SQL statements.

A DLL file should conform to the extended stored procedure API to function as an extended stored
procedure. DLL can have multiple functions identified to SQL Server as extended stored procedures.
Each function is recognized by a separate extended stored procedure name.

When an extended stored procedure is referenced by a T-SQL statement, the relational database engine
calls the function linked with the extended stored procedure name. Extended stored procedures can
connect to remote SQL Server installations or open a back connection to the SQL Server instance that
called them.

REFERENCE:
http://msdn.microsoft.com/en-us/library/ms187372.aspx


QUESTION NO: 19(A)
Which of the following editions of SQL Server 2008 is similar to Enterprise Edition, but cannot be used
on a production server?

A. Developer
B. Workgroup
C. Standard
D. Express

ANSWER: A




http://www.testkingweb.com/microsoft-70-453.html                                            Page 20 of 190
                                                                                Total Questions: 229

QUESTION NO: 20(A)
You work as a Database Administrator for Softech Inc. The company has a computer on which SQL
Server 2005 is installed. The server contains a database named Sales. The company wants to take an
advantage of the features of SQL Server 2008. You are asked to install SQL Server 2008 on the same
computer on which SQL Server 2005 is installed. What will you do to accomplish this?

A. Uninstall SQL Server 2005. Install SQL Server 2008 as a default instance and then Install SQL Server
   2005 as a named instance.
B. Uninstall SQL Server 2005. Install SQL Server 2008 as a named instance and then install SQL Server
   2005 as a default instance.
C. Install SQL Server 2008 as a named instance.
D. Install SQL Server 2008 as the default instance.

ANSWER: C

EXPLANATION:
In order to install SQL Server 2008 on the same computer on which SQL Server 2005 is installed, you will
install SQL Server 2008 as a named instance without uninstalling SQL Server 2005. You can install one
instance of SQL Server as a default instance and its multiple instances as named instances on the same
computer. If you want to install SQL Server 2008 on the same computer, which already has the previous
version of SQL Server installed, you will install SQL Server 2008 as a named instance. In the scenario, the
default instance will be SQL Server 2005, and the named instance will be SQL Server 2008.

REFERENCE:
SQL Server 2008 Books Online, Contents: Instance Name


QUESTION NO: 21(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 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.

Choose and Reorder:

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

ANSWER:


http://www.testkingweb.com/microsoft-70-453.html                                            Page 21 of 190

				
DOCUMENT INFO
Shared By:
Stats:
views:90
posted:6/23/2011
language:English
pages:21
Description: Testking Web 70-453 Exam - Transition Your MCITP SQL Server 2005 DBA to MCITP SQL Server 2008 Questions and Answers with detailed Explanations and Objectives.