Case Study: Menaubi Foods

Menaubi Foods is a wholesale distributor of non-perishable food items to locations around the globe. The company runs a SQL Server 2000 machine that is used through a web site for online queries and updates. The server is also used on the local LAN, where a Visual Basic application provides accounting information and updates the same.

The database consists of a complex structure of tables that have been fully normalized for efficient storage. During some accounting updates, the Internet application stops responding, and after a time the machine running the accounting application must be rebooted. How would you go about finding the problem?

ANALYSIS

There are potentially a few problems in this scenario. The most likely is some form of error in the accounting application. There also seems to be a deadlock or blocking situation preventing the Internet application from responding.

Run the Profiler, set up for a replay trace, and ensure that you are tracking locking information as well as the standard template data. The trace could then be replayed on a test machine to diagnose the problem. It also might be beneficial to reinstall the accounting application. If you suspect locking, the quickest, most direct way to confirm this is to use the Enterprise Manager or the stored procedures sp_who or sp_lock .

The first problem you will face on the exam will be which tool to use, given a set of circumstances. Second, you will need to present a course of action for monitoring and troubleshooting. Third, you will need to read and diagnose the output and then select an appropriate solution. A person who implements databases needs to be comfortable in all these areas, and you will find a few of each type of question on the exam.

Throughout this book, a great deal of information has been provided that could potentially apply to this vast topic. Everything from appropriate database design, to the correct use of technologies, to the proper definition and use of SQL Server objects all will play a role. Expect exam questions that cover a variety of these topics wound into a single scenario.

Now that you have completed the entire book and have an understanding of all the concepts covered, the exam should be a cake-walk for you. If you have completed the Case Studies, Exercises, and Step by Steps of the chapter, you will have obtained valuable practical experience with the product. In the appendixes of this book are a couple of other elements that you can use for final preparation. Quick facts about the exam, more sample exam questions, and terminology definitions can all be found in the pages that follow.