================================================ SEARCHDATABASE.COM'S DBA Tips and Tricks May 2, 2001 ================================================ Welcome to the searchDatabase.com DBA Tips and Tricks newsletter! Today's tip, "What makes a good database?" can also be viewed online at: http://www.searchDatabase.com/tip/1,289483,sid13_gci549256,00.html ------------------------------------------------ LEARNING ZONE FEATURED BOOK OF THE WEEK ------------------------------------------------ "Microsoft SQL Server 2000 Administrator's Companion" By Marci Frohock Garcia & Jamie Reding This comprehensive, easy-to-read guide saves you time by providing all the facts you need to deploy, administer, and support SQL Server 2000 in organizations of any size. Using a hands-on, example-rich approach, this authoritative, one-volume reference book provides peer advice, product information, detailed procedures, and real-world troubleshooting tips from experienced Microsoft SQL Server 2000 professionals. http://www.digitalguru.com/dgstore/product.asp?isbn=0735610517%20&ac_id=58 ************************************************ "What makes a good database?" By Ryan Stephens & Ronald Plew The definition of a good database is relative to the requirements of each customer because every situation is different. A good database is determined as seen through the eyes of the customer, the end user, the database administration team, and management. If all parties are happy with the database, the allocation of resources to design a new database might be unnecessary. On the other hand, if some of the parties involved are unhappy about a few things, it might be worthwhile to begin designing a new database. Keep in mind that there might not be an existing database in place. If a database is not currently in use, it is still important to understand the key principles of a "good" database before thinking about designing one. Although there are many hallmarks of a good database, this excerpt from "Teach Yourself Database Design" (Sams, 2000) discusses the most common, including: * Data storage needs having been met * Data is readily available to the end-user * Data being protected through database security * Data being accurate and easy to manage * Overall database performance being acceptable * Having a minimized amount of redundant data stored Storage needs met ----------------- The foremost objective of a database is to store data. In order to determine if data storage is adequate, the following questions might be of use: * Have all storage needs been met for the database? * Has all data been stored effectively? * Is the database model used capable of handling the complexity of business relationships? * Is the database model used capable of handling the estimated volume of data for the proposed database? * Is the hardware adequate for storage needs? * Does the database software meet the storage needs? * What data is stored offline as opposed to online storage? * How easy is it to access offline data storage? * Has all unnecessary online data been purged or archived into an offline storage device? Some of the factors that affect data storage include the design of the database, the database software, the hardware and operating system on which the database runs, and the types of data stored. As you will learn throughout the book, it is imperative to carefully plan the design of the database so that all storage needs are met. A database software program must be used to implement a database that has been designed. Some vendors provide features that others do not with their database software. Although the hardware and disk space on which the database runs may have been adequate initially, the database may have grown beyond the physical limits of the hardware. Finally, the type of data stored must be considered. Online data is data that is readily available to the end user and is stored in the database. Offline data is data that is archived and not stored in the database. If no data is ever archived, it can appear that storage needs are not being met. Data Is Available ----------------- What good is a database if data is not readily available to the end-user? Data must be available as requested by the end-user, during all hours of business operation. Many organizations are referred to as 24X7 shops. This means that data must be available twenty-four hours a day, seven days a week, to satisfy the needs of many groups of users that have a need to potentially access the database from many sites in different time zones around the world. Database down time is often necessary, but must be scheduled around hours of peak user activity. Database down time should not occur if it has not been scheduled. However, down time sometimes occurs from time to time based on factors that are related to the design of the database. If data is not available when expected, then the database is not fulfilling its purpose. Data availability is also related to the user's expected and perceived performance of the database. Consider this: A user starts an application and performs some function that requests data. Depending on the nature of the request, it may take several minutes or seconds for data to become available, or it might appear instantly on the screen. In the production environment, perception is reality, and if a user feels an application or database is slow, it will create the perception of poor performance. A database environment may be simply stellar in its design and implementation, but expectations might overtake the possible realistic performance of the database and application. In some cases, small changes to the application may help, assuming the database itself has been well-designed. For example, an OLE database call into a Visual Basic record set object may take several seconds. When the screen is painted, an additional few seconds is required. Suppose the screen is painted while the record set is being retrieved from the database. The few seconds it takes to paint the screen so that the screen from the application and the data from the database pop together may be worth it. In this example, the users perceived the database to be slow. A simple change in perception solved the issue. Data Is Protected ----------------- After the data is stored in the database, it is important to ensure that the data is well protected. Database security should be established to protect the data from unauthorized users. Some questions that might be asked to determine how well the data is protected include the following: * Does security exist in the database? * Is the data protected from outside users? * Is the data protected from internal users? * How easy is it for unauthorized users to access the data? * Have there been any security breaches since the database implementation? * How easy is it to limit the access to various groups of users within the scope of the database? * How easy is it to grant and revoke data access to various groups of users? Without database security, the database can easily become corrupted, whether intentional or not. It is important to restrict access to the database from individuals not requiring access. As a general rule, you should be able to limit the access to the data at a very low level. Many databases are designed with little or no security. Although a lack of security does not justify the complete redesign of a database, it is a definite setback and can cause problems that leave decision makers with the impression that they have a poor database. THE REMAINDER OF THIS ARTICLE CAN BE FOUND HERE: http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci549256,00.html "Teach Yourself Database Design" (Sams, 2000) is available for purchase here: http://www.digitalguru.com/dgstore/product.asp?isbn=0672317583&ac_id=58 ------------------------------------------------ FOR MORE INFORMATION ------------------------------------------------ The Best Data Storage Web Links: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax281661,00.html The Best Data Security Web Links: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax281660,00.html Ask your toughest data storage and security questions--or help out your peers by answering them--in our live discussion forums: http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html Check out our new "Ask the Experts" feature! We have Database Design, SQL, Oracle, DB2, and SQL Server gurus waiting to answer your technical questions: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html ================================================ SUBMIT A TECHNICAL TIP AND WIN A PRIZE! ================================================ Do you have a time-saving shortcut, trick, or script that you want to share with other database pros? The first fifty individuals who submit a tip will receive a free searchDatabase.com hat. The highest rated tips each month will win our "Tip of the Month" contest and receive a high-quality searchDatabase.com denim shirt AND a free book of your choice from Wrox Press. We're accepting short, focused tips or code snippets on topics of interest to DBA's and database developers, such as: * Oracle * DB2 * SQL Server * database design * SQL * performance tuning Click here for more info and to submit your tip: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html This will be a great way to share your knowledge, cement your status as an industry expert, and maybe win a prize. Send us your tip today! To Remove your email address from the distribution list for this specific newsletter "Reply" to this message with REMOVE in the subject line. You will receive an email confirming that you have been removed. To Remove yourself from additional distribution lists or to update your preferences, go to the searchDatabase.com registration page at: http://searchDatabase.techtarget.com/register
