EXAM CRAM™ 2 Designing and Implementing Databases with SQL Server 2000 Enterprise Edition [Electronic resources] نسخه متنی

The master price list for the shoes your company has in stock is maintained in SQL Server in the corporate office that is updated constantly. You want all your outlets to receive the updated prices as quickly as possible from the corporate office. Which sort of replication is best suited for a situation like this?

A. Transactional replication

B. Snapshot replication

C. Merge replication

D. Immediate replication

Answer: A. To receive updated prices as quickly as possible, use transactional replication. Merge replication is not appropriate because of the timing considerations, and snapshot replication would be too intensive, would slow down operations, and would produce a lot of latency (delay). Immediate replication does not exist, though immediate Updating Subscriptions are an option of transactional and snapshot replication types. Often exam answers attempt to fool you by using wording that is purposely confusing, so read carefully and make no assumptions. For more details, see the "Data Replication" section.

You need to query some data on a temporary remote server for a one-time report. Which of the following functions is the best to use?

A. OPENQUERY()

B. OPENROWSET()

C. Linked servers

D. OPENXML()

Answer: B. Either option A or C would work, too, but they'd involve creating a linked server. For a one-time report it's more efficient to just use OPENROWSET. There's a whole section in this chapter on OPENROWSET called "Coding for Distributed Data."

Gordon is trying to get access to a server on the other side of the Black Mesa facility to complete his research. He just got access to the LambdaSQL server, and he is having problems running even simple queries. On the server, there is a database called BadThings with a table called Creepy. What's the problem with this query:

select count(*) from LambdaSQL.BadThings.Creepy

His colleagues seem to be having no problems.

A. You can't run an aggregate function across a remote server.

B. You should use OPENROWSET for this kind of an operation.

C. You should use OPENXML for this.

D. He left the owner name out of the object specification.

Answer: D. All cross-server activity has to be done with four-part names. The correct name is probably something like LambdaSQL.BadThings.dbo.Creepy. Cross-server joins were all covered in the section "Using Linked Servers."

You are designing a database that will be used for a small office. The client machines have minimal hard drive space and very little RAM. Other than the database server, no server-grade machines are available. You have chosen a SQL Server in a client/server architecture as the best implementation for this system. Which application architecture is best suited for this system?

A. Single-tier

B. Two-tier thin client

C. Two-tier thick client

D. N-tier

E. Internet

Answer: B. With few resources on the clients, you have to make the clients as small as possible. N-tier or Internet could be potential solutions, but with the lack of sufficient processing power in the form of a server-grade machine, these would not suit this scenario. For more details, see the section "SQL Server Supporting Applications."

In a large office with hundreds of users, you have several servers that are centrally located in a secured room that only IT staff can access. One server is used as a security server and also acts as a DHCP server. A second dual-processor server is running SQL Server, and another machine runs an email system with IIS operational. The office does not permit any other user access to the Internet, nor does it expose any information to the Internet through a website. You must select an application architecture suitable to this configuration. No other software is available on the servers. What application architecture would be best suited?

A. Single-tier

B. Two-tier thin client

C. Two-tier thick client

D. N-tier

E. Internet

Answer: E. This is a good candidate for an Internet solution because you already have an IIS server available. Whether or not you have Internet access is irrelevant because everything can be performed in-house using an HTML or XML solution across the network. For more information, see the "Internet Application Architecture" section.

In a large department store, an inventory database is maintained for all products sold. Data is updated frequently by multiple computer terminals. Four hundred computer terminals throughout the offices and store can access the database simultaneously to perform updates. You want to minimize conflict situations and reduce the load on the server as much as possible. The client systems have very little processing power of their own. What architecture would you select for this system?

A. Single-tier

B. Two-tier thin client

C. Two-tier thick client

D. N-tier

E. Internet

Answer: D. To minimize the amount of processing performed on a server, you would need to offload the processing onto the client by way of the two-tier thick client approach, or use a middle tier component such as an n-tier model. Because the client machines don't have much processing capability, the only remaining choice is to use the n-tier approach. For more information, see the "One- and Two-Tier Systems" section.

You have prepared the logical design for a very large database system that will act as the back end for an Internet application, as well as being accessed from the corporate WAN. You need to support a large number of concurrent users who will be accessing the database at various bandwidth speeds. Which SQL Server technologies could assist in allowing the users access while providing good performance? (Choose all that apply.)

A. Analysis services

B. Replication

C. Partitioned views

D. English query

E. Metadata services

Answer: B, C. Replication and partitioned views enable you to spread the load of a very large database system across several machines. The benefit of additional processing power and getting the data closer to the user could be recognized by both features, assuming that they were properly partitioned and configured. For more information, see the section "SQL Server Supporting Applications."