70-431 Q & A / Study Guide

Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com

---

QUESTION 1
You work as the database administrator at CertKingdom.com. The CertKingdom.com network consists of
a single Active Directory domain named CertKingdom.com. All servers on the CertKingdom.com network
run Windows 2000 Server. The CertKingdom.com network contains a SQL Server 2000 database
server named CERTKINGDOM-DB01 that runs on Windows 2000 Server. CERTKINGDOM-DB01
contains a database named CKD_Sales.
CertKingdom.com intends migrating CERTKINGDOM-DB01 to SQL Server 2005 in the near future. To
prepare for the migration, you decide to install SQL Server 2005 Express on a server named
CERTKINGDOM-SR31. You verify that CERTKINGDOM-SR31 exceeds the minimum recommended
hardware requirements for SQL Server 2005.
What should you do next?

A. Upgrade CERTKINGDOM-SR31 to Windows Sever 2003 and install Service Pack 1.
B. Install and run the SQL Server Upgrade Advisor on CERTKINGDOM-SR31.
C. Install SQL Server 2000 on CERTKINGDOM-SR31.
D. Download and install the .NET Framework 2.0.

Answer: D

Explanation:
SQL Server 2005 requires the.NET Framework 2.0. This is automatically installed when SQL
Server 2005 is installed but is not automatically installed when SQL Server 2005 Express is installed.

Incorrect Answers:
A: You do not need to upgrade CERTKINGDOM-SR31 to Windows Sever 2003 Service Pack 1 as
SQL Server 2005 Express can be installed on Windows 2000 Server. You will need to install
Service Pack 4 for Windows 2000 Server though.
B: You should run the SQL Server Upgrade Advisor when you want to upgrade an instance of a
SQL Server 2000 database to SQL Server 2005.
C: You do not need to install SQL Server 2000 before installing SQL Server 2005 Express.

Reference: Microsoft SQL Server 2005 Books Online (2006), Index: installing SQL Server
Express, before installing

---

QUESTION 2
You work as a database administrator at CertKingdom.com. The CertKingdom.com network consists of a
single Active Directory domain named CertKingdom.com. All servers on the CertKingdom.com network
run Windows Server 2003 with the latest Service Packs and Hotfixes, while the client computers
run Windows XP Professional, Windows 98 Second Edition, and Novell NetWare 6.5.
You install SQL Server 2005 on a member server named CERTKINGDOM-SR21. CERTKINGDOMSR21
will host a database named CKD_Sales. All CertKingdom.com users must be able to connect to
CERTKINGDOM-SR21 by computer name. A CertKingdom.com network administrator named Rory Allen
must ensure that the client computers will be able to connect to CERTKINGDOM-SR21.
Which protocols can CertKingdom.com users use to CERTKINGDOM-SR21?

A. TCP/IP and Named Pipes
B. TCP/IP and IPX/SPX
C. Virtual Interface Adapter (VIA)
D. Shared Memory

Answer: A

Explanation:
By default, clients have TCP and Named Pipes as available protocols. You can manipulate the
protocol ordering by using the SQL Server Client utility. The client application uses the protocols
in the order specified on the client computer. If you are using SQL Server 2005, the protocol order
is stored in the ProtocolOrder registry entry under the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0
Incorrect Answers:
B: SQL Server 2005 does not support IPX/SPX. The newer versions of NetWare, such as
NetWare 6.5, support TCP/IP which makes it the common protocol for all clients.
C: The Virtual Interface Adapter (VIA) can be used only by VIA hardware.
D: Shared Memory can only be used on the local computer and cannot be used as a network protocol.

Reference:Microsoft SQL Server 2005 Books Online (2006), Index: client connections
[SQL Server], about client network connections, choosing a network protocol

---

QUESTION 3
You work as the database administrator at CertKingdom.com. The CertKingdom.com network consists of
a single Active Directory domain named CertKingdom.com. All servers on the CertKingdom.com network
run Windows 2000 Server. The CertKingdom.com network contains a SQL Server 2000 database
server named CERTKINGDOM-DB01 that runs on Windows 2000 Server. CERTKINGDOM-DB01
contains a database named CKD_Sales.
CertKingdom.com intends migrating CERTKINGDOM-DB01 to SQL Server 2005 in the near future. To
prepare for the migration, you decide to install SQL Server 2005 Express on a server named
CERTKINGDOM-SR23. CERTKINGDOM-SR31 was used for previously used by application developer
and contains Microsoft Visual Studio 2003, .NET Framework 1.2 and Internet Explorer 5.5. You
verify that CERTKINGDOM-SR23 exceeds the minimum recommended hardware requirements for
SQL Server 2005.
What should you do next? (Each correct answer provides part of the solution. Choose THREE.)

A. Upgrade CERTKINGDOM-SR23 to Windows Sever 2003 and install Service Pack 1 for Windows Sever 2003.
B. Install Service Pack 4 for Windows 2000 Sever on CERTKINGDOM-SR23.
C. Upgrade the .NET Framework 1.2 to .NET Framework 2.0 on CERTKINGDOM-SR23.
D. Uninstall the .NET Framework 1.2 and install the .NET Framework 2.0 on CERTKINGDOM-SR23.
E. Upgrade Internet Explorer 5.5 to Internet Explorer 6.0 and install Service Pack 1 for Internet
Explorer 6.0 on CERTKINGDOM-SR23.
F. Upgrade Microsoft Visual Studio 2003 to Microsoft Visual Studio 2005 and install Service Pack
1 for Microsoft Visual Studio 2005 on CERTKINGDOM-SR23.

Answer: B,D,E

Explanation:
SQL Server 2005 requires Service Pack 4 for Windows 2000 Sever, Internet Explorer 6.0 with
Service Pack 1 and the .NET Framework 2.0. The .NET Framework 2.0 is automatically installed
with other versions of SQL Server 2005 but is not automatically installed with SQL Server 2005
Express. You can upgrade .NET Framework 1.0 and upgrade .NET Framework 1.1 to .NET
Framework 2.0 but .NET Framework 1.2 must be uninstalled first.
Incorrect Answers:
A: You do not need to upgrade CERTKINGDOM-SR31 to Windows Sever 2003 Service Pack 1 as
SQL Server 2005 Express can be installed on a Windows 2000 Server once you install Service
Pack 4 for Windows 2000 Server.
C: You can upgrade .NET Framework 1.0 and upgrade .NET Framework 1.1 to .NET Framework
2.0 but .NET Framework 1.2 must be uninstalled first.
F: SQL Server 2005 Express does not require any version of Microsoft Visual Studio.
Reference:Microsoft SQL Server 2005 Books Online (2006), Index: installing SQL Server
Express, before installing

---

QUESTION 4
You work as a database administrator at CertKingdom.com. The CertKingdom.com network consists of a
single Active Directory domain named CertKingdom.com.
You have received instruction from the CIO to prepare a new installation of SQL Server 2005. The
new SQL Server 2005 database server is named CERTKINGDOM-DB01. A CertKingdom.com user
named Mia Hamm is a member of the Marketing department. You instruct her to identify the
protocols that the client computers might use to connect to the server. Mia Hamm needs to identify
the protocols that are needed to accomplish this goal.
What should she identify? (Choose all that apply)

A. She should use the Multiprotocol.
B. Mia Hamm should use the Named Pipes.
C. She should use the Shared Memory.
D. She should use the TCP/IP
E. Mia Hamm should use the Virtual Interface Adapter (VIA).

Answer: B,D

Explanation:
The clients have by default TCP and Named Pipes as available protocols. You can make use of
the SQL Server Client utility to manipulate the protocol ordering. The client application uses the
protocols in the order specified on the client computer. When you make use of the SQL Server
2005, the protocol order is stored in the ProtocolOrder registry entry under the following registry
subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0
Incorrect Answers:
C: Shared Memory can only be used on the local computer and cannot be used as a network
protocol.
E: The Virtual Interface Adapter (VIA) can be used only by VIA hardware.Reference:Microsoft SQL
Server 2005 Books Online (2006), Index: client connections [SQL Server], about client network
connections, choosing a network protocol

---

QUESTION 5
You work as the database administrator at CertKingdom.com. The CertKingdom.com network consists of
a single Active Directory domain named CertKingdom.com. All servers on the CertKingdom.com network
run Windows 2000 Server. The CertKingdom.com network contains a SQL Server 2000 database
server named CERTKINGDOM-DB01 that runs on Windows 2000 Server Service Pack 4.
CERTKINGDOM-DB01 contains a database named CKD_Trading.
CertKingdom.com wants to migrate CERTKINGDOM-DB01 to SQL Server 2005 but wants to retain the
CKD_Trading database with the same instance name. You decide to perform an in-place upgrade to
SQL Server 2005. You verify that CERTKINGDOM-DB01 exceeds the minimum recommended
hardware requirements for SQL Server 2005.
What should you do next?

A. Upgrade CERTKINGDOM-DB01 to Windows Sever 2003 Service Pack 1.
B. Run the SQL Server Upgrade Advisor on CERTKINGDOM-DB01.
C. Detach the CKD_Trading database on CERTKINGDOM-DB01.
D. Run the SQL Server 2005 Setup utility.

Answer: B

Explanation:
Before you perform an in-place upgrade of an existing database, you should run the SQL Server
Upgrade Advisor. The SQL Server Upgrade Advisor produces a list of items that must be
addressed before and after performing the upgrade. This list of items is specific to the existing
installation.
Incorrect Answers:
A: You do not need to upgrade CERTKINGDOM-DB01 to Windows Sever 2003 Service Pack 1 as
SQL Server 2005 can be installed on Windows 2000 Server with Service Pack 4.
C: You need to detach a database when you need to move the database to another database
instance or another database server. You do not need to detach the database before performing
an in-place upgrade.
D: You should run the SQL Server Upgrade Advisor before you run the SQL Server 2005 Setup
utility. The SQL Server Upgrade Advisor produces a list of items that must be addressed before
and after performing the upgrade. This list of items is specific to the existing
installation.Reference:Microsoft SQL Server 2005 Books Online (2006), Index: updating
databases, detaching and attaching databases

---

QUESTION 6
You work as the database administrator at CertKingdom.com. The CertKingdom.com network consists of
a single Active Directory domain named CertKingdom.com. All servers on the CertKingdom.com network
run Windows 2000 Server. The CertKingdom.com network contains a SQL Server 7.0 database server
named CERTKINGDOM-DB01 that runs on Windows 2000 Server. Service Pack 4 for Windows 2000
Server id installed on CERTKINGDOM-DB01.
CertKingdom.com wants to migrate CERTKINGDOM-DB01 to SQL Server 2005. You decide to perform
an in-place upgrade to SQL Server 2005. You verify that CERTKINGDOM-DB01 exceeds the
recommended hardware requirements for SQL Server 2005.
What should you do next?

A. Upgrade CERTKINGDOM-DB01 to Windows Sever 2003.
B. Install Service Pack 4 for SQL Server 7.0 on CERTKINGDOM-DB01.
C. Upgrade SQL Server 7.0 on CERTKINGDOM-DB01 to SQL Server 2000.
D. Run the SQL Server 2005 Setup utility on CERTKINGDOM-DB01 and upgrade directly to SQL
Server 2005.

Answer: B

Explanation:
You can upgrade from SQL Server 7.0 with Service Pack 4 directly to SQL Server 2005. Thus you
need only install Service Pack 4 for SQL Server 7.0.
Incorrect Answers:
A: You do not need to upgrade CERTKINGDOM-DB01 to Windows Sever 2003 as SQL Server 2005
can be installed on Windows 2000 Server with Service Pack 4.
C: You do not need to upgrade CERTKINGDOM-DB01 to SQL Server 200 as SQL Server 7.0 with
Service Pack 4 can be upgraded directly to SQL Server 2005. Besides, you will need to install
Service Pack 3 for SQL Server 2000 before you can upgrade it to SQL Server 2005.
D: You need to install Service Pack 4 for SQL Server 7.0 before you can upgrade it directly to SQL
Server 2005.Reference:Microsoft SQL Server 2005 Books Online (2006), Index: upgrading SQL
Server, upgrading to SQL Server 2005

---

QUESTION 7
You work as the database administrator at CertKingdom.com. The CertKingdom.com network consists of
a single Active Directory domain named CertKingdom.com. All servers on the CertKingdom.com network
run Windows Server 2003 and all client computers run Windows XP Professional. The
CertKingdom.com network contains two SQL Server 2000 database servers named CERTKINGDOMDB01
and CERTKINGDOM-DB02 that are configured for log shipping. CERTKINGDOM-DB01 is the
primary server and hosts a database named CKD_Trading. Both CERTKINGDOM-DB01 and
CERTKINGDOM-DB02 have Service Pack 3 for SQL Server 2000 installed.
CertKingdom.com wants to upgrade CERTKINGDOM-DB01 and CERTKINGDOM-DB02 to SQL Server
2005 Enterprise Edition but the CKD_Trading database must remain available during the upgrade
process. You verify that both CERTKINGDOM-DB01 and CERTKINGDOM-DB02 exceed the
recommended hardware requirements for SQL Server 2005 Enterprise Edition. You then install
Service Pack 1 for Windows Server 2003 on CERTKINGDOM-DB01 and CERTKINGDOM-DB02.
What should you do next?

A. Install Service Pack 4 for SQL Server 2000 on both database servers.
B. Enable failover in the SQL Server 2000 log shipping configuration and upgrade CERTKINGDOMDB02
to SQL Server 2005.
C. Run the Database Maintenance Plan Wizard on CERTKINGDOM-DB01 to upgrade it to SQL
Server 2005.
D. Upgrade CERTKINGDOM-DB02 to SQL Server 2005 and then upgrade CERTKINGDOM-DB01 to
SQL Server 2005.

Answer: B

Explanation:
SQL Server 2005 does not support the Database Maintenance Plan Wizard which was integral to
log shipping in SQL Server 2000. Hence, log shipping stops functioning when you upgrade a SQL
Server 2000 log shipping configuration directly to SQL Server 2005. To upgrade log shipping to
SQL Server 2005, you must migrate the database servers to SQL Server 2005. If you want the
database to remain available during the migration process, you must migrate with failover. To
migrate with failover you must enable failover and upgrade CERTKINGDOM-DB02. Then you need
to failover to CERTKINGDOM-DB02 and upgrade CERTKINGDOM-DB01. Then configure log shipping
with the CERTKINGDOM-DB02 becoming the primary server and CERTKINGDOM-DB01 becoming the
secondary server.
Incorrect Answers:
A: You do not need to install Service Pack 4 for SQL Server 2000 on the database servers as SQL
Server 2000 with Service Pack 3 can be upgraded to SQL Server 2005. However, SQL Server
2005 does not support the Database Maintenance Plan Wizard which was integral to log shipping
in SQL Server 2000. Hence, log shipping stops functioning when you upgrade a SQL Server 2000
log shipping configuration directly to SQL Server 2005.
C: SQL Server 2005 does not support the Database Maintenance Plan Wizard which was integral
to log shipping in SQL Server 2000. Hence, log shipping stops functioning when you upgrade a
SQL Server 2000 log shipping configuration directly to SQL Server 2005.
D: Removing log shipping from CERTKINGDOM-DB02 and upgrading CERTKINGDOM-DB01 to SQL
Server 2005 will not ensure that the CKD_Trading database remains available. Reference:Microsoft
SQL Server 2005 Books Online (2006), Index: log shipping [SQL Server], upgrading

---

QUESTION 8
You work as the database administrator at CertKingdom.com. The CertKingdom.com network consists of
a single Active Directory domain named CertKingdom.com. All servers on the CertKingdom.com network
run Windows Server 2003. The CertKingdom.com network contains a Domain Controller named
CERTKINGDOM-DC01 and a member server named CERTKINGDOM-SR21.
CertKingdom.com wants to implement a SQL Server 2005 database that will store sales data. All
CertKingdom.com users will require access to the Sales_DB database. You have been instructed to
install SQL Server 2005 on CERTKINGDOM-SR21 and to create a database named Sales_DB. You
need to ensure that CertKingdom.com users and applications can connect to the database instance by
using only the computer name.
What should you do during the installation of SQL Server 2005 on CERTKINGDOM-SR21?

A. Select the Named Instance and use MSSQLSERVER as the instance name.
B. Select the Named Instance and use CERTKINGDOM-DC01 as the instance name.
C. Select the Default Instance and accept the default instance name.
D. Select the Named Instance and use CERTKINGDOM-SR21 as the instance name.

Answer: C

Explanation:
To ensure that users and applications can connect to the instance using the computer name, you
should select the Default Instance and accept the default instance name, MSSQLSERVER.
Incorrect Answers:
A: You cannot use a Named Instance as users and applications will need to specify the server
name and the instance name when connection to the database.
B: You cannot use a Named Instance as users and applications will need to specify the server
name and the instance name when connection to the database.
D: You cannot use a Named Instance as users and applications will need to specify the server
name and the instance name when connection to the database.

---

QUESTION 9
You work as the database administrator at CertKingdom.com. The CertKingdom.com network consists of
a single Active Directory domain named CertKingdom.com. All servers on the CertKingdom.com network
run on Windows Server2003 while the client computers run Windows XP Professional, Windows
98 Second Edition, and Novell NetWare 6.5. All computers are members of the CertKingdom.com
Active Directory domain. The CertKingdom.com network contains a SQL Server 2005 database server
named CERTKINGDOM-DB01 that hosts a database instance named MSSQLSERVR.
CertKingdom.com wants to implement another SQL Server 2005 instance for a database named
Sales_DB. Sales_DB will store sales data for the company. All CertKingdom.com users will require
access to the Sales_DB database. You have been instructed to create the SQL Server 2005
instance for the Sales_DB database. You need to ensure that all users will be able to connect to
the Sales_DB database. You cannot make configuration changes to the client computers. Your
solution must optimize network traffic.
What should you do?

A. Install the instance as a Named Instance and configure it to support Mixed Mode
Authentication. The enable TCP/IP for the instance.
B. Install the instance as a Default Instance and configure it to support Windows Authentication.
The enable TCP/IP for the instance.
C. Install the instance as a Named Instance and configure it to support SQL Authentication. The
enable IPX/SPX for the instance.
D. Install the instance as a Default Instance and configure it to support Windows Authentication.
The enable IPX/SPX for the instance.

Answer: A

Explanation:
The default instance is named MSSQLSERVER. This instance name is already in use; therefore
you should use a Named Instance. You should use Mixed Mode Authentication to support
NetWare users.
Incorrect Answers:
B: You cannot use the Default Instance as the default instance is named MSSQLSERVER and is
already in use. Furthermore, you need to use Mixed Mode Authentication as Windows
Authentication does not support NetWare users.
C: You can use SQL Authentication, but this will require that you create SQL Logins for all users
and groups. It would be easier to use Windows Authentication for clients that user Windows XP
Professional and Windows 98 SE, and create SQL Logins for the NetWare 6.5 users. Also, SQL
Server 2005 does not support IPX/SPX. The newer versions of NetWare, such as NetWare 6.5,
support TCP/IP which makes it the common protocol for all clients.
D: You cannot use the Default Instance as the default instance is named MSSQLSERVER and is
already in use. Furthermore, you need to use Mixed Mode Authentication as Windows
Authentication does not support NetWare users. Also, SQL Server 2005 does not support
IPX/SPX. The newer versions of NetWare, such as NetWare 6.5, support TCP/IP which makes it
the common protocol for all clients.

---

QUESTION 10
You work as the database administrator at CertKingdom.com. The CertKingdom.com network consists of
a single Active Directory domain named CertKingdom.com. All servers on the CertKingdom.com network
run on Windows Server2003 and all client computers run Windows XP Professional. The
CertKingdom.com network contains two SQL Server 2005 database servers that are configured in a
cluster with two nodes named NodeA and NodeB. NodeA hosts a default database instance
named MSSQLSERVR.
CertKingdom.com wants to implement another SQL Server 2005 instance for a database named
Sales_DB. Sales_DB will store sales data for the company. All CertKingdom.com users will require
access to the Sales_DB database. You have been instructed to create the SQL Server 2005
instance for the Sales_DB database on Node
A. You need to ensure that all users will be able to
connect to the Sales_DB database.
What should you do?

A. Run the SQL Server 2005 Setup utility on NodeA and select a Named Instance for the
Sales_DB database.
B. Run the SQL Server 2005 Setup utility on NodeA and select the Default Instance for the
Sales_DB database.
C. Move the existing instance from NodeA to NodeB. Run the SQL Server 2005 Setup utility on
Node
A. Select the Default Instance and accept the default instance name
D. Run the SQL Server 2005 Setup utility on NodeB and select a Named Instance for the
Sales_DB database.

Answer: A

Explanation:
The default instance is named MSSQLSERVER. This instance name is already in use on NodeA;
therefore you should use a Named Instance.
Incorrect Answers:
B: You cannot use the Default Instance as the default instance is named MSSQLSERVER and is
already in use.
C: You do not need to move the existing instance as you can install up to 50 instances on a SQL
Server 2005 database server.
D: You need to install the instance on NodeA not NodeB. You can install a new instance on
NodeA if you use a Named Instance.

Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com