MSCE Interview Questions and Answers

QUESTION 91
You work as the database administrator at Certkiller .com. All database servers run
Windows 2000 Server and SQL Server 2000. All client computers run Windows
2000 Professional. The Certkiller .com network contains a database server named
Certkiller -DB02 that hosts a database named CK_Sales. The tables in the
CK_Sales database are shown in the following database diagram.




Certkiller .com has several Sales Representatives that use portable client computers.
These portable client computers run SQL Server 2000 Desktop Engine (MSDE
2000).

Certkiller .com has developed a new sales tracking application named CK_sales. The
CK_sales application provides access to the Customers table in the CK_Sales
database. Several Sales Representatives use the sales tracking application as part of
Certkiller .com's Customer Relationship Management (CRM) strategy. You need to
ensure that the Sales Representatives can interact with the CK_Sales database while
offline. The Sales Representatives must be able to access information about their
customers while offline; must be able to add new customers while offline; and must
be able to synchronize with Certkiller -DC02 on demand using Web
synchronization.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. Implement snapshot replication.
B. Implement merge replication.
C. Implement transactional replication.
D. Configure the portable client computers for pull subscriptions.
E. Configure the portable client computers for push subscriptions.

Answer: B, D

Explanation: To allow offline access to the CK_Sales database and synchronization
on demand using Web synchronization, you need to implement merge replication.
To enable the portable client computers to synchronize with Certkiller -DC02 on
demand using Web synchronization you must configure them for pull subscriptions.
During synchronization changes are propagated to the publisher and to other
subscribers.

Incorrect Answers:

A: You cannot use snapshot replication to replicate transactions from multiple computers to a single database server. Also, with snapshot replication the entire database is sent during synchronization.
C: Transactional replication requires that data be entered at the Publisher. In this
scenario, data will be entered at the subscribers.
E: Push replication does not support synchronization on demand from the subscriber.
Only the publisher determines when synchronization occurs.


QUESTION 92
You work as the database administrator at Certkiller .com. All database servers run
Windows 2000 Server and SQL Server 2000. All client computers run Windows
2000 Professional. Certkiller .com has its headquarters in Washington and branch
offices in Detroit, Chicago and Delaware. The branch offices are connected to
headquarters by unreliable WAN Links as shown in the exhibit.



Certkiller .com has a database named CK_Sales that stores sales data for the
company. The database is hosted on a SQL Server 2000 database server in each
office. Each office is responsible for taking orders from the customers in its region
but all sales goods are shipped from headquarters. You need to ensure that the
Washington office receives all sales transactions at the end of the working day. You
must also ensure that conflicts caused by multiple updates are handled and
resolved.
What should you do?

A. Implement snapshot replication with each branch office configured as a publisher and headquarters configured as a subscriber.
B. Implement merge replication with each branch office configured as a publisher and
headquarters configured as a subscriber.
C. Implement transactional replication with headquarters configured as a publisher and each branch office configured as a subscriber.
D. Implement snapshot replication with headquarters configured as a publisher and each branch office configured as a subscriber.

Answer: B

Explanation: You should use merge with the branch offices configured as a
publishers and headquarters configured as the subscriber because the branch
offices must send the data to headquarters.

Incorrect Answers:
A: Snapshot replication does not resolve data conflicts as it does not monitor for updates to the data.
C: Transactional replication is only required if the subscriber must receive the orders as they occur. You need to ensure that the Washington office receives all sales transactions at the end of the working day. Furthermore, you should configure headquarters as the subscriber and the branch offices as publishers because headquarters must receive the updates from the branch offices.
D: Snapshot replication does not resolve data conflicts as it does not monitor for updates to the data. Furthermore, you should configure headquarters as the subscriber and the branch offices as publishers as headquarters must receive the updates from the branch offices.

QUESTION 93
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Active Directory domain named Certkiller .com.
Certkiller .com has its headquarters in Washington and branch offices in Detroit and
Delaware. All servers on the Certkiller .com network run Windows 2000 Server and
all client computers run Windows 2000 Professional.
The Certkiller .com network contains four SQL Server 2000 database servers named
Certkiller -DB01, Certkiller -DB02, Certkiller -DB03, and Certkiller -DB04.
Certkiller -DB01 and Certkiller -DB02 are located at headquarters while
Certkiller -DB03 is located at the Detroit branch office and Certkiller -DB04 is
located at the Delaware branch office. You need to implement a new database
named OrdersDB to track sales orders from all three offices. You need to design
replication for the OrdersDB database. You decide to implement updateable
subscriptions with Certkiller -DB01 as a Publisher, Certkiller -DB02 as a
Distributor, and Certkiller -DB03 and Certkiller -DB04 as Subscribers. You
need to ensure that use the immediate update mode.
What should you do? (Each correct answer represents part of the solution. Choose
TWO.)

A. On Certkiller -DB01, create a Windows account for the Queue Reader Agent.
B. On Certkiller -DB01, install and configure the Microsoft Distributed Transaction
Coordinator (MS DTC).
C. On Certkiller -DB02, install and configure the Microsoft Distributed Transaction
Coordinator (MS DTC).
D. On Certkiller -DB03 and Certkiller -DB04, install and configure the Microsoft
Distributed Transaction Coordinator (MS DTC).

Answer: B, D

Explanation: To use immediate update mode, you need to install and configure the
Microsoft Distributed Transaction Coordinator (MS DTC) on the Publisher and the
Subscribers.

Incorrect Answers:
A: The Queue Reader Agent is not required when you use immediate update mode. It is
only required when you use queued update mode.
C: The Microsoft Distributed Transaction Coordinator (MS DTC) should be installed and
configured on the Publisher and the Subscribers. It is not required on the Distributor.

QUESTION 94
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Windows 2000 domain named Certkiller .com. All servers
on the Certkiller .com network run Windows 2000 Server and all client computers
run Windows 2000 Professional. The Certkiller .com network contains a SQL Server
2000 database server named Certkiller -DB01 that hosts a database named
CK_Sales. The CK_Sales database stores sales data for the company.
Certkiller .com has several Sales Representatives that use portable client computers
when they visit prospective clients. SQL Server 2000 is installed on the portable
computers. The Sales Representatives must be able to check stock availability and
must be able upload sales data to the CK_Sales database from customer locations.
What should you do?

A. Implement snapshot replication with Certkiller -DB01 configured as a publisher and
each portable computer configured as a pull subscriber.
B. Implement merge replication with each portable computer configured as a publisher
and Certkiller -DB01 configured as a pull subscriber.
C. Implement merge replication with Certkiller -DB01 configured as a publisher and
each portable computer configured as a pull subscriber.
D. Implement snapshot replication with Certkiller -DB01 configured as a publisher and
each portable computer configured as a push subscriber.

Answer: C

Explanation: Merge replication with pull subscribers will allow Sales
Representatives to check stock availability and to upload sales data to the CK_Sales
database when they are connected to the corporate network from customer locations.

Incorrect Answers:
A: Snapshot replication transmits the entire database as it appears at a specific moment in time and does not monitor for updates to the data. It is best used as a method for replicating data that does not change frequently.
C: You should configure Certkiller -DB01 as the publisher and the portable computers
as subscribers because Certkiller -DB01 must receive the updates from the portable
computers.
D:Snapshot replication transmits the entire database as it appears at a specific moment in time and does not monitor for updates to the data. It is best used as a method for replicating data that does not change frequently. Furthermore, push subscriptions are implemented by the publisher. This would not be possible as you do not know when the subscribers will be connected to the network.


QUESTION 95
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Active Directory domain named Certkiller .com.
Certkiller .com has its headquarters in Washington and five field offices in the city.

All servers on the Certkiller .com network run Windows 2000 Server, all database
servers run SQL Server 2000 and all client computers run Windows 2000
Professional. The Certkiller .com network contains a database server named
Certkiller -DB01 that is located at headquarters. Certkiller -DB01 hosts a
database named CK_Repairs.

Certkiller .com users in the Purchasing department order parts that technicians use
to repair customer's computers at customer sites. The parts are shipped to the field
offices. At present Certkiller .com tracks the usage of parts through data entered on
the customer's invoice. Each field office has a client computer that runs SQL Server
2000 Standard Edition that is used to capture invoice data.
Certkiller .com wants to implement a new inventory tracking application for the
parts. The new application will access a table named _Parts in the CK_Repairs
database. You need to enable users at headquarters and at the various field offices
to update the _Parts table. You decide to implement merge replication with
Certkiller-DB01 as the Publisher and the SQL Server 2000 Standard Edition
computers at the field offices as the subscribers.
To minimize the usage of disk space on the Subscribers, you need to ensure that
only the Publisher is used to store conflict records.
What should you do?

A. Run the sp_addmergepublication stored procedure with the @conflict_retention
argument on Certkiller -DB01.
B. Run the sp_addmergesubscription stored procedure with the @publication argument
on Certkiller -DB01.
C. Run the sp_addmergepublication stored procedure with the @centralized_conflicts
argument on Certkiller -DB01.
D. Run the sp_ addmergesubscription stored procedure with the @centralized_conflicts argument on Certkiller -DB01.

Answer: C

Explanation: The centralized_conflicts = 'true' argument of the
sp_addmergepublication stored procedure specifies that conflict records should be
stored only on the Publisher.

Incorrect Answers:

A: The @conflict_retention argument Specifies the retention period, in days, for which conflicts are retained. It does not define the placement of the conflict records. B, D: When sp_addmergesubscription is run on the Publisher, it is used to register a subscriber. The sp_addmergesubscription stored procedure does not define the placement of the conflict records.


QUESTION 96
You work as the database administrator at Certkiller .com. All database servers run
Windows 2000 Server and SQL Server 2000. All client computers run Windows
2000 Professional. Certkiller .com has its headquarters in Washington and branch
offices in Chicago, Madrid and Tokyo. The branch offices are connected to
headquarters by fast and reliable WAN Links.
Certkiller .com has a database named CK_Sales that stores sales information for the
company. The database is hosted on a SQL Server 2000 database server in each
office. Each branch office sells cultural specific products in its region and requires inventory information from headquarters. All products are shipped from
headquarters. You are implementing a replication strategy for Certkiller .com with
headquarters as the publisher and the branch offices are subscribers. You need to
minimize the amount of data that is sent over the WAN links.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. Partition data horizontally.
B. Partition data vertically.
C. Use a single publication for all subscribers.
D. Use a separate publication for each subscriber.

Answer: A, C

Explanation: SQL Server 2000 supports transformable partitions by partitioning
data that is specific to a subscriber. This reduces the administrative overhead
required to maintain multiple publications and allows you to minimize the amount
of data that must be transmitted to each subscriber. Horizontal partitioning allows
you to select rows that are specific to a subscriber. This allows you to select products that are specific to a subscriber.

Incorrect Answers:

B: Vertical partitioning allows you to select columns that are specific to a subscriber. However, inventory information specific to a branch office will be located in rows rather than columns.
D: SQL Server 2000 supports transformable partitions by partitioning data that is specific to a subscriber. This reduces the administrative overhead required to maintain multiple publications and allows you to minimize the amount of data that must be transmitted to each subscriber.


QUESTION 97
You work as the database administrator at Certkiller .com. All database servers run
Windows 2000 Server and SQL Server 2000. All client computers run Windows
2000 Professional. Certkiller .com has its headquarters in Chicago and branch offices
in New Orleans and San Francisco. The branch offices are connected to
headquarters by VPN connections.
Certkiller .com has a SQL Server 2000 database server named Certkiller -DB01.
Certkiller -DB01 hosts a database named CK_Data that stores business
information for the company. Certkiller -DB01 is located at headquarters. You
need to transmit large amounts of data from the CK_Data database to SQL Server
2000 database servers at the branch offices at the end of each month. You want to
accomplish this using snapshot replication. You Certkiller -DB01 as the
Publisher/Distributor and the branch offices are the Subscribers. You need
minimize the processing load on Certkiller -DB01.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. Set the min server memory option to 12 MB.
B. Set the @SkipError option to 1.
C. Create a separate snapshot folder for each subscriber.
D. Create a single snapshot folder on a network share.

Answer: D

Explanation: You can reduce the load on Certkiller -DB01 by moving the snapshot
folder to a network share.

Incorrect Answers:
A: Themin server memory option for a Publisher/Distributor should be set to at least 16MB.
B: The @SkipError option is used with the sp_addscriptexec stored procedure to specify a script to run for all subscribers to a snapshot, transactional or merge publication. The
@SkipError option specifies whether the Distribution Agent should stop if an error is encountered or log the error and continue. However; there is not mention of using scripts in this scenario.
C: The same snapshot must be sent to each subscriber; therefore you do not need
separate snapshot folders. This will not reduce the load on Certkiller -DB01.


QUESTION 98
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Active Directory domain named Certkiller .com. All
servers on the Certkiller .com network run Windows 2000 Server and all client
computers run Windows 2000 Professional. The Certkiller .com network contains a
SQL Server 2000 database server named Certkiller -DB01. Certkiller -DB01
hosts a database named CK_Sales.

The Domain User accounts of the Certkiller .com staff that need access to CK_Sales
have been granted login rights in SQL Server. The Certkiller .com Sales department
members often have to travel and have all been issued with laptop computers on
which Internet Explorer 5.5 have been installed. They requested that they be
allowed to enter orders into CK_Sales. To this end you need to provide them with
access to CK_Sales over the Internet.

Following are the restraints and requirements that you need to keep in mind when
provide access to CK_Sales:
1. Internet Information Services (IIS) will be used to provide Internet Access to
CK_Sales.
2. Only the employees with Windows user accounts will be allowed access.
3. Traveling Sales Department members will connect to their local Internet Service
Providers (ISPs) to gain Internet access.
4. There will be no virtual Private Network (VPN).
5. Direct dial connections from users to the corporate LAN will not be configured.
6. Password security must be maintained.
7. Different users should have different levels of permissions in CK_Sales.
You thus need to decide on a strategy to provide access to CK_Sales to those
employees who require access using the least amount of administrative effort.
What should you do?

A. Login rights to the default, built-in Internet guest account in SQL Server must be
granted. Then configure a virtual directory in IIS to use Anonymous Access.
B. Virtual Directory in IIS should be configured to use
Windows Integrated Authentication.
C. Login right in SQL Server should be granted to the Domain Users group.
Virtual Directory in IIS should be configured to use Windows Integrated
Authentication.
D. A SQL Server login with the same account name and password as the user's Windows
account name and password must be created for each user.
Virtual Directory in IIS should be configured to use Basic Authentication.

Answer: B

Explanation: XML functionality is supported by SQL Server 2000. You need to
configure access to CK_Sales over the Internet, thus you should configure a
corresponding virtual directory using the IIS Virtual Directory Management for SQL
Server tool. On the Security Tab in the Properties Sheet you need to enable Integrated Authentication. This will ensure that users who have been assigned valid Windows 2000 user accounts will log on locally to their laptops and connect to the Internet. Then they will type the URL associated with the virtual directory in a Web Browser. Then they will be prompted to provide their user names and passwords from a Enter Network Password dialog box. Each of these users must be granted the right to log on to SQL Server to be able to gain access to CK_Sales. The activities they will be carrying out on CK_Sales will depend on their level of permissions.

Incorrect answers:

A: Granting Anonymous Access would be allowing access to all users even those who
do not have Windows User accounts.
C: Granting login rights to the Domain Users group does not provide different levels ofdatabase access to individual users. Thus this option will not comply with the fact that different users should have different levels of permissions in CK_Sales.
D: If you make use of Basic Authentication (This means Clear Text), then you would
be required to create duplicate SQL Server loging for all users. This will amount to more administrative effort than is necessary.


QUESTION 99
You work as the database administrator at Certkiller .com. The Certkiller .com
network are spread over two offices: one in Chicago and the other in Los Angeles.
The Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01. Certkiller -DB01 is located in the Chicago office where you are
stationed. You need to provide access to a database names CK_Customers which is
located on Certkiller -DB01, to the Los Angeles users.
Following are the restraints and requirements that you need to keep in mind when
provide access to CK_Customers:

1. Internet Information Services (IIS) will be used by the Los Angeles users to access CK_Customers.
2. Most Los Angeles users who require access to CK_Customers have been assigned
Windows Accounts that has been granted login rights in SQL Server.
3. The Los Angeles users use Windows 2000 Professional workstations that have
been configured with Internet Explorer 5.5.
4. There are some users whose workstations run UNIX.
These UNIX users also require access to CK_Customers.
UNIX workstations might be running other Web browsers.
UNIX users do not have Windows user accounts.
You thus need to decide on a strategy to provide access to CK_Sales to the Los
Angeles employees who require access using the least amount of administrative
effort.
What should you do?

A. Create SQL Server logins for the UNIX users by configuring a virtual directory in IIS for Windows Integrated Authentication.
B. Create SQL Server logins for the UNIX users by configuring a virtual directory in IIS for both Basic and Windows Integrated Authentication.
C. Create SQL Server logins for all users by configuring a virtual directory in IIS for Basic Authentication.
D. Create Windows accounts for the UNIX users and then grant SQL Server logins to
those accounts by configuring a virtual directory in IIS for Windows Integrated
Authentication.

Answer: C

Explanation: XML functionality is supported by SQL Server 2000. You need to
configure access to CK_Customers over the Internet, thus you should configure a
corresponding virtual directory using the IIS Virtual Directory Management for SQL
Server tool. On the Security Tab in the Properties Sheet you need to enable Basic
Authentication (Clear Text) to SQL Server account option to enable the UNIX users to
access CK_Customers over the Internet. Basic authentication verifies the SQL Server
login and password that is presented when a user attempts to log in to SQL server. And SQL Server logins and password do not rely on the operating system of the client
computer. Thus option will present the least administrative effort to accomplish the goal at hand.

Incorrect answers:

A: If you enable Windows Integrated Authentication, then you will only be granting
access to the users who has Windows accounts then the UNIX users will not have access.
B: You can only configure one authentication mode when using IIS Virtual Directory
Management for SQL Server tool.
D: It is mentioned in the question that the UNIX client computers may be running
non-Internet Explorer browsers. Also UNIX does not support Windows Authentication.


QUESTION 100
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Active Directory domain named Certkiller .com. All
servers on the Certkiller .com network run Windows 2000 Server and all client
computers run Windows 2000 Professional. The Certkiller .com network contains a
SQL Server 2000 database server named Certkiller -DB01. Certkiller -DB01
hosts a database named CK_Sales.

You are currently developing a client application. This application must:
1. Enable certain users to access CK_Sales.
2. When these users run the application, they must be able to perform certain tasks
within CK_Sales.
3. No one must be allowed to access CK_Sales by any other method.
The application is coded to connect to SQL Server by using the name SalesReport.
You now need to enable the application to access the database.
What should you do?

A. The application must be coded to use Windows Authentication.
Create a Windows 2000 group and add the users to that group.
Grant the group the appropriate permissions in CK_Sales.
B. A SQL Server login named SalesReport must be created.
Then create an application role and grant that role the appropriate permissions in
CK_Sales. Then add the appropriate users to that role and make the application use that role.
C. A SQL Server login named SalesReport must be created.
Then create an application role and grant that role the appropriate permissions in
CK_Sales. Make the application use that role.
D. A Windows user account named SalesReport must be created and granted login.
Then create an application role and grant that role the appropriate permissions.
Make the application use that role.

Answer: C

Explanation: An application must first connect to SQL Server. This means that the
application must be configured to use a login. It is mentioned in the question that the application is coded to connect to CK_Sales using the name SalesReport. Since there is no domain name or backslash character in this name, SQL Server will authenticate SalesReport as a SQL Server login. Thus you should create a SQL Server login named SalesReport.
You should also ensure that users do not use any other means to access CK_Sales. This
means that you should code the application to use an application role.
An application role is created in a database and it can be assigned permissions to
manipulate objects in that database.
You also need to make the application use the application role by coding the application to call the sp_setapprole stored procedure in the CK_Sales context to and provide a password that activates the application role.
Any permissions that a user may have courtesy of his/her user account or group
memberships will be replaced by the permissions that have been assigned to the
application role.

Incorrect answers:

A: You should not use a Windows 2000 group and add those users to the group because
the question pertinently states that no unauthorized users must be allowed to access
CK_Sales using the application.
B: An application role cannot contain users
D: Client applications always run under the security context of a current user, and not under the security context of a specific Windows user account without the user who owns that account being logged on.