MSCE Interview Questions and Answers

Installing, Configuring and AdministeringMicrosoft SQL Server 2000, Enterprise Edition

QUESTION 1
You work as a system administrator at Mcse.com. All servers on the Mcse.com network run Windows 2000 Server. All client computers run either Windows 98 or Novel NetWare. Your duties include administrating a member server named Mcse -SR07.You must install SQL Server 2000 on Mcse -SR07 and create a database named CK_Data. You need to ensure that all Windows 98 and Novell NetWare client computers can connect to the CK_Data database using the Named Pipes nd IPX/IPX Net-Libraries respectively. You need to configure the client computers to connect to the CK_Data database using the minimum number of configuration changes on the client computers.
What should you do? (Each correct answer presents part of the solution. Choose TWO.)

A. Configure the new instance to use Mixed Mode Authentication.
B. Configure the new instance to use Windows Authentication.
C. Configure the new instance to use SQL Server Authentication.
D. Configure Mcse -SR07 to use the Named Pipes and IPX/SPX Net-Libraries.
E. Configure Mcse -SR07 to use the Multiprotocol Net-Library.
F. Configure Mcse -SR07 to use the Shared Memory Net-Library.

Answer: A, D

Explanation: The client computers in this scenario will be running Windows 98 and Novel NetWare. Novell NetWare clients are not supported by Windows Authentication; however SQL Server Authentication should only be used for backward compatibility. Hence you should use Mixed Mode Authentication. By using specific communication protocols, and not the Multiprotocol Net-Library, performance would be better. Every extraneous protocol adds overhead. Incorrect Answers:
B: As client computers will be running Windows 98 and Novel NetWare, you cannot use Windows Authentication as Novel NetWare systems are not supported by Windows Authentication.
C: SQL Server Authentication should only be used for backward compatibility. Hence you should use Mixed Mode Authentication.
E: The Multiprotocol Net-Library is used to support the different protocol that the clients use. This provides easy of administration. However, since unused protocols are installed, it also introduces overhead. This is not the best solution.
F: Shared Memory is only supported for local database connection on the database
server. It does not support database connections over the network.

QUESTION 2
You work as the database administrator at Mcse.com. The Mcse.com network consists of a single Windows 2000 domain named Mcse.com. All servers on the Mcse.com network run Windows 2000 Advanced Server. The Mcse.com network contains a SQL Server 2000 database server named MCSE-DB01. MCSE-DB01 is a production server and hosts a database named CK_Sales which stores sales transactions for the company. Mcse.com has database application developer named Rory Allen who uses a Windows 2000 Professional client computer named MCSE-WS228. Rory Allen needs to test a database application on MCSE-DB01. You install a separate a SQL Server 2000 instance named RA_Test on MCSE-DB01. You need to ensure that Rory Allen can connect to the RA_Test instance. What should you do?

A. Configure the RA_Test instance to use the Named Pipes Net-Library.
B. Configure the RA_Test instance to use the Multiprotocol Net-Library.
C. Configure the RA_Test instance to use the NWLink IPX/SPX Net-Library.
D. Configure the RA_Test instance to use the Shared Memory Net-Library.

Answer: A

Explanation: Windows 2000 Professional can use the TCP/IP Sockets and Named Pipes Net-Libraries by default. Incorrect Answers:
B: The Multiprotocol Net-Library can only be used to connect to a default instance of SQL Server 2000 as it does not support named instances of SQL Server 2000.

C: The NWLink IPX/SPX Net-Library is used to support Novell NetWare clients.

D: Shared Memory is only supported for local database connection on the database server. It does not support database connections over the network.

QUESTION 3
You work as the database administrator at Mcse.com. The Mcse.com network consists of a single Windows 2000 domain named Mcse.com. The Mcse.com network contains a SQL Server 7.0 database server named MCSE-DB01 that runs on Windows NT Server 4.0. MCSE-DB01 is a production server and hosts several databases for the company. You install SQL Server 2000 as the default instance on a new database server named MCSE-DB02. Certkiller -DB02 runs Windows 2000 Server.Mcse.com users in the Accounts department must have direct access to the databases on both MCSE-DB01 and MCSE-DB02. The users in the Accounts department use client computers that run Windows 2000 Professional. You need to encrypt the network communication between the Accounts department users' client computers and the database servers.What should you do?

A. Configure the client computers to use the Named Pipes Net-Library.
B. Configure the client computers to use the Multiprotocol Net-Library.
C. Configure the client computers to use the TCP/IP Sockets Net-Library.
D. Configure the client computers to use the Shared Memory Net-Library.

Answer: B

Explanation:
Windows 2000 Professional can use the TCP/IP Sockets, Multiprotocol and Named Pipes Net-Libraries by default. However, only the Multiprotocol Net-Library supports remote procedure call (RPC) encryption. RPC encryption is required because SQL Server 7.0 only supports RPC encryption. Incorrect Answers: A, C: Although the Windows 2000 Professional client computers can use the TCP/IP Sockets and Named Pipes Net-Libraries by default; and the TCP/IP Sockets and Named Pipes Net-Libraries support Secure Socket Layer (SSL), SQL Server 7.0 only supports remote procedure call (RPC) encryption which is only supported by the Multiprotocol Net-Library.D: Shared Memory is only supported for local database connection on the database server. It does not support database connections over the network. Reference:

QUESTION 4
You work as the database administrator at Mcse.com. The Mcse.com network consists of a single Windows 2000 domain named Mcse.com. All servers on the Mcse.com network run on Windows 2000 Server while the client computers run Windows 2000 Professional, Windows 98, and Novell NetWare. All computers are members of the Mcse.com domain. The Mcse.com network contains a SQL Server 2000 database server named MCSE -DB01 that hosts a default instance of SQL Server 2000. Mcse.com wants to implement another SQL Server 2000 instance for a database named SalesDB. SalesDB will store sales data for the company. All Mcse.com users will require access to the Sales_DB database. You have been instructed to create the SQL Server 2000 instance for the Sales_DB database. You need to ensure that all users will be able to connect to the SalesDB 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. Then enable the NWLink IPX/SPX Net-Library for the instance.
B. Install the instance as a default instance and configure it to support WindowsAuthentication. Then enable the TCP/IP Sockets Net-Library for the instance.
C. Install the instance as a Named Instance and configure it to support SQL ServerAuthentication. Then enable the IPX/SPX for the instance.
D. Install the instance as a default instance and configure it to support WindowsAuthentication. Then enable the TCP/IP Sockets Net-Library for the instance.

Answer: A

Explanation: The default instance is already in use; therefore you should use a Named Instance as only one instance can be the default instance. You should use Mixed Mode Authentication and enable the NWLink IPX/SPX Net-Library to support Novell NetWare users. Incorrect answers:
B: You cannot use the default instance as the default instance as it is already in use. Furthermore, you need to use Mixed Mode Authentication as Windows Authentication does not support Novell NetWare users and you need to enable the NWLink IPX/SPX Net-Library to support Novell NetWare users.
C: SQL Server Authentication is supported for backward compatibility and is not
recommended for use. You should rather use Mixed Mode Authentication.
D: You cannot use the default instance as the default instance as it is already in use.
Furthermore, you need to use Mixed Mode Authentication as Windows Authentication
does not support NetWare users. Furthermore, you need to enable the NWLink IPX/SPX
Net-Library to support Novell NetWare users.

QUESTION 5
You work as a network administrator at Mcse.com. The Mcse.com network consists of a single Windows 2000 forest. The Mcse.com forest has a single root domain named Mcse.com. All servers on the Mcse.com network run Windows 2000 Server. The Mcse.com network contains a member server named MCSE -SR12. You install SQL Server 2000 on MCSE -SR12 and configure the MSSQLServer service to run under a domain user account named SQLAdmin in the Mcse.com root domain. You add the SQLAdmin account to the Enterprise Admins group. You start MCSE -SR12 but the MSSQLServer service does not start. You need to ensure that the MSSQLServer service starts. What should you do?

A. Configure the MSSQLServer service to run under the local System account onMCSE -SR12.
B. Add the SQLAdmin user account to the Domain Admins group in the Mcse.comroot domain.
C. Add the Enterprise Admins group to the Domain Admins group in the Mcse.comroot domain.
D. Configure the MSSQLServer service and the MSSQLServerAgent service to rununder the same user account.

Answer: A

Explanation: The MSSQLServer service must have the Log on as a service right on the local computer. You have created a user account for the SQLServer service but this service does not have the Log on as a service right on the local computer. You can resolve the problem by having the MSSQLServer service use the local System account.

Incorrect answers:
B, C: Adding the SQLAdmin user account or the Enterprise Admins group to the Domain Admins group will not ensure that the SQLServer service will start. TheMSSQLServer service must have the Log on as a service right on the local computer. D: The MSSQLServer and MSSQLServerAgent services usually run under the same user account to reduce administrative overhead; however, the MSSQLServer service must have the Log on as a service right on the local computer before it will start.

QUESTION 7
You work as a network administrator at Mcse.com. The Mcse.com network consists of a single Windows NT 4.0 domain named MCSE . All servers on the Mcse.com network run Windows NT Server 4.0. The Mcse.com network contains three SQL Server 2000 database servers named MCSE -DB01,MCSE -DB02 and MCSE -DB03.You need to configure replication between the three database servers. You configure the MSSQLServer service on all three database servers to use a domain user account named SQLAdmin. You need to ensure that the SQL Server 2000 database servers continue to function properly. What should you do?

A. Assign the SQLAdmin user account the Log on as a service user right.
B. Add the SQLAdmin user account to the Enterprise Admins group in the domain.
C. Configure the MSSQLServer service to run under the local System account.
D. Reset the password for the SQLAdmin user account.

Answer: A

Explanation: The MSSQLServer service must have the Log on as a service right on the local computer. You have created a user account for the SQLServer service but this service does not have the Log on as a service right on the local computer. Incorrect answers:
B: Adding the SQLAdmin user account to the Enterprise Admins group will not ensure that the SQLServer service will start. The MSSQLServer service must have the Log on as a service right on the local computer.
C: Configuring the MSSQLServer service to run under the local System account will
allow the MSSQLServer service to start but will not allow replication between the three
servers as the local System account cannot access the network.
D: Resetting the password for the SQLAdmin user account is not required.

QUESTION 8
You work as the database administrator at Mcse.com. Mcse.com has a Windows NT 4.0 network. The Mcse.com network contains a database server named MCSE -DB01 that hosts a SQL Server 6.5 database named CKSalesand a SQL Server 7.0 database named CK_Inventory.
You need to upgrade the CK_Sales database to SQL Server 2000. You install a named instance of SQL Server 2000 on MCSE -DB01. You run the SQL Server Upgrade Wizard to upgrade the CK_Sales database to SQL Server 2000. However, the CKSales database is not listed on the Database Selection page of the SQL Server Upgrade Wizard.You need to ensure that the CK_Sales database is listed on the Database Selection page.
What should you do?

A. Upgrade SQL Server 7.0 to SQL Server 2000.
B. Install Service Pack 5 for SQL Server 6.5.
C. Install SQL Sever 2000 on a separate server.
D. Reinstall SQL Server 2000 as a default instance.

Answer: D

Explanation: To run the SQL Server Upgrade Wizard, you must have a default instance of Microsoft SQL Server 2000 installed on the server. Incorrect Answers:
A: It is not necessary to upgrade SQL Server 7.0 to SQL Server 2000. You must install SQL Server as a default instance as you cannot run the SQL Server Upgrade Wizard unless SQL Server 2000 is installed as the default instance.
B: Service Pack 5 for SQL Server 6.5 is required if you want to install SQL Server 2000 as a side-by-side installation. However, you want to upgrade SQL Server 6.5 to SQL Server 2000 sing the SQL Server Upgrade Wizard. You must install SQL Server as a default instance to use the SQL Server Upgrade Wizard.
C: Installing SQL Server 2000 on a separate server will not allow you to use the SQL
Server Upgrade Wizard.


QUESTION 9
You work as the database administrator at Mcse.com. The Mcse.com network consists of a single domain named Mcse.com. All servers on the Mcse.com network run Windows 2000 Server. The Mcse.com network contains three SQL Server 6.5 database servers named MCSE -DB01, MCSE -DB02 and MCSE -DB03. Server Service Pack 3 for SQL Server 6.5 is installed on each database server. In addition to several other databases, MCSE -DB01 hosts a database named CK_Sales which stores sales transactions for the company. Mcse.com wants to upgrade the CKSales database to SQL Server 2000. You decide to perform an in-place upgrade to SQL Server 2000. You run database consistency checks against each database on MCSE -DB01 and then backup the databases. What should you do next? (Each correct answer presents part of the solution.
Choose TWO.)

A. Change the database instances on MCSE -DB01 to named instances.
B. Install Service Pack 4 for SQL Server 6.5 on MCSE -DB01.
C. Install Service Pack 5 for SQL Server 6.5 on MCSE -DB01.
D. First upgrade MCSE -DB01 to SQL Server 7.0.
E. Install the SQL Server instance as the default instance on MCSE -DB01.

Answer: C, E

Explanation: You are only upgrading one database to SQL Server 2000; therefore you will be running SQL Server 6.5 along side SQL Server 2000. You need to install Service Pack 5 for SQL Server 6.5 to support side-by-side installations with SQL Server 2000. Also, the SQL Server 2000 installation must be the default instance as SQL Server 6.5 only recognizes default instances and not named instances. Incorrect answers:
A: SQL Server 6.5 only recognizes default instances and not named instances.
B: The minimum requirement for upgrading to SQL Server 2000 from SQL Server 6.5 is
Service Pack 5.
D: You can upgrade to SQL Server 7.0 and then to SQL Server 6.5 but this is unnecessary as you can upgrade from SQL Server 6.5 Service Pack 5 to SQL Server
2000.

QUESTION 10
You work as the database administrator at Mcse.com. The Mcse.com network consists of a single Windows 2000 domain named Mcse.com. All servers on the Mcse.com network run Windows 2000 Server. The Mcse.com departments include a Sales department, a Purchasing department, and an Accounts department. The Mcse.com network contains a SQL Server 6.5 database server named MCSE -DB01 and a SQL Server 2000 database server named MCSE -DB02. MCSE -DB02 contains a database named CK_Sales that stores sales data for the company.
The Sales department makes extensive use of the CKSales database. Mcse.com users in the Purchasing department make use of a Microsoft Access database that runs on an application server named MCSE -SR04 while users in the Accounts departments make use of a SQL Server 6.5 database that run MCSE -DB01. The manager of the Accounts department wants to be able to run distributed queries against the CK_Sales database and the databases in the Purchasing and Accounts departments. You must configure the MCSE -SR04 and MCSE -DB01 as linked servers on MCSE -DB02. You need to select the required data sources to create the linked server definitions. What should you do? (Choose all that apply.)

A. Use the OLE DB Provider for Jet as a data source.
B. Use the OLE DB Provider for SQL Server as a data source.
C. Use the OLE DB Provider for ODBC as a data source.
D. Use the OLE DB Provider for DB2 as a data source.

Answer: A, B

Explanation: The Microsoft Access database requires the OLE DB Provider for Jet to communicate with the linked server while the SQL Server 6.5 database requires
the OLE DB Provider for SQL Server.
Incorrect answers:
C: SQL Server 6.0 databases require the OLE DB Provider for ODBC but SQL Server 6.5 databases require the OLE DB Provider for SQL Server.
D: The OLE DB Provider for DB2 is required for IBM DB2 databases.

QUESTION 11
You have just been hired as the database administrator at Mcse.com. Mcse.com currently uses several Microsoft Access databases to store its business information. The Microsoft Access databases are stored on two application servers named MCSE -SR04 and MCSE -SR05.
You have been instructed to migrate some of the Microsoft Access databases on MCSE -SR04 to SQL Server 2000. You must ensure that Mcse.com users can access data in the Microsoft Access databases until all the data has been migrated to SQL Server 2000. During the migration process, Mcse.com users must be able to run distributed queries against the data in both SQL Server 2000 and Microsoft Access. You install SQL Server 2000 on a member server named MCSE -SR06. MCSE -SR06 has limited disk space. What should you do?

A. Configure MCSE -SR04 as a linked server on MCSE -SR06. Configure theOLE DB Provider for Jet as the data source.
B. Configure MCSE -SR04 as a linked server on MCSE -SR06. Configure theOLE DB Provider for SQL Server as a data source.
C. Configure MCSE -SR06 as a linked server on MCSE -SR04. Configure theOLE DB Provider for ODBC as a data source.
D. Configure MCSE -SR06 as a linked server on MCSE -SR04. Configure theOLE DB Provider for SQL Server as a data source.

Answer: A

Explanation: To support distributed queries, you must configure the Microsoft Access database server as a linked server on the SQL database server. Microsoft Access requires the OLE DB Provider for Jet to communicate with the linked server.
Incorrect answers:
B: Microsoft Access requires the OLE DB Provider for Jet to communicate with the linked server, not the OLE DB Provider for SQL Server.
C, D: You cannot create linked servers in Microsoft Access. You must create them in
SQL Server 2000.