Case Study: ACME Distributors


SCENARIO

ESSENCE OF THE CASE

Here are the essential points being addressed in this case:

  • Budget is limited.

  • High volume of updates.

  • Downtime must be minimized.

  • Data loss must be minimized if a failure occurs.

  • Referential integrity must be configured for replication.

ACME Distributors is a mail-order company with warehouse operations located in strategic locations throughout the United States. You are in charge of planning the physical server environment to be used in the implementation of a multi-server warehousing system. Each of the warehouses needs information on what the other warehouses have in stock and transactional replication has been chosen as the technique to deliver information among the warehouses.

Each warehouse is expected to handle about 1000 transactions of its own each day, as well as receive 9000 replicated transactions from other warehouses. You need to supply an environment that will function 24 hours a day, 7 days a week. You must pay particular attention to achieving an environment that has a minimum amount of downtime and data loss in the event of a failure.

With a limited budget, you have been asked to establish priorities within the system. A list should be prepared providing a range of choices from the minimum requirements to fulfill the needs of the system, and on the upper range, some alternatives that would still fall into a low-budget scenario.

ANALYSIS

This seems to be a far-too-typical situation, in which a company desires to have the optimum environment but is hesitant to invest the necessary funds to achieve the desired results.

To eliminate downtime, there are two separate possibilities. First, you can provide for failover clustering, which can incur a significant cost.

Second, you can include in the solution a mechanism where a middle- tier procedure can select the local server by default, but if the server is unavailable, the procedure can obtain the necessary information from one of the other warehouses. Although the later solution can appear less expensive at first, because hardware costs are lower, other factors, such as development, update ability, bandwidth, and licensing costs have to be considered .

Also worth considering is the use of a standby server or replication as a means of minimizing ”though not eliminating ”downtime. This would be a less expensive solution than failover clustering, but in the event of failure, you will have a small amount of downtime while adjusting the actual machine being used for production purposes. An additional advantage of this solution is the near elimination of data loss if a failure were to occur.

To minimize data loss in the event of failure, a number of options are available. On the less-expensive side, setting the database recovery mode to Full Recovery and adding a second disk volume can provide for the storage of the log files and allow for almost full data recovery. Other possibilities from less to more expensive would include RAID parity storage, RAID mirror storage, log shipping, replication, and failover clustering. As long as replication is already being planned for, this might be part of the desired solution.

To enable multiple warehouses to participate in replication and to share information, you must create a Primary Key constraint that combines both the location of the warehouse and the identifier for the data itself.

Here is the desired list:

  • Minimum Requirements:

    Compound Primary Key

  • Recommended Requirements:

    Second volume for transaction log and Full Recovery mode

    Frequent log backups and the development of a disaster recovery plan

    Data storage set up in RAID parity with OS mirrored

  • Also Possible (Recommended Order):

    Replication to additional subscriber at each location

    Standby server with log shipping configured (achieves similar results as replication)

    Cost Restrictive

  • Failover Clustering



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net