| O BJECTIVES Create and alter databases. Considerations include file groups, file placement, growth strategy, and space requirements. -
Specify space management parameters. Parameters include autoshrink, growth increment, initial size, and maxsize. -
Specify file group and file placement. Considerations include logical and physical file placement. -
Specify transaction log placement. Considerations include bulk load operations and performance. -
The placement of the files related to a SQL Server 2000 database environment helps to ensure optimum performance while minimizing administration. Recoverability can also be improved in the event of data corruption or hardware failures if appropriate measures are taken. On the exam, you must be prepared to respond to these requirements and properly configure the interactions with the file system. Create and alter database objects. Objects include constraints, indexes, stored procedures, tables, triggers, user -defined functions, and views. -
Specify table characteristics. Characteristics include cascading actions, CHECK constraints, clustered, defaults, FILLFACTOR , foreign keys, nonclustered, primary key, and UNIQUE constraints. -
Specify schema binding and encryption for stored procedures, triggers, user-defined functions, and views. -
Specify recompile settings for stored procedures. -
Specify index characteristics. Characteristics include clustered, FILLFACTOR , nonclustered, and uniqueness. Alter database objects to support replication and partitioned views. -
Support merge, snapshot, and transactional replication models. -
Design a partitioning strategy. -
Design and create constraints and views. -
Resolve replication conflicts. -
A variety of technologies exist in handling multiple server environments, knowing what each technology offers ”as well its restrictions ”helps you adapt a database system applied across multiple machines. Considerations for controlling data alterations, having the data available when needed, and responding to queries in a timely manner will be the aim of questions within this area of the exam. Troubleshoot failed object creation. -
Troubleshooting is certainly a broad topic. In this chapter, the focus for troubleshooting is on the interactions with objects and the server as well as application settings that are required for an object to be created and used. On the exam, troubleshooting will be approached from a wide variety of angles. In the "real world," it is good practice to always view a procedure from a problem-solving perspective. Always be ready to ask yourself, "What could go wrong?" and "What can be done to resolve the problem?" O UTLINE Creating and Altering Databases Creating Database Files and Filegroups Using Filegroups File Placement Space Requirements Creating and Altering Database Objects Table Characteristics Application of Integrity Options Multiple Server Implementations Use of Replication Partitioning to Achieve a Balance Troubleshooting SQL Server Objects Setting Server Configuration Options Setting Database Configuration Options Setting the Database Options Apply Your Knowledge Exercises Review Questions Exam Questions Answers to Review Questions Answers to Exam Questions S TUDY S TRATEGIES -
Ensure that you have a thorough understanding of the variety of objects and technologies available within the realm of physical design. Know what each technique accomplishes (advantages) and also watch out for associated pitfalls (disadvantages). -
Understand the basics of the file system and its use by SQL Server. Know when to split off a portion of the database structure and storage to a separate physical disk drive. -
Know the interaction between SQL Server and the OS (operating system). Some of the physical design concepts that are discussed point out the role that the OS performs and the reason for its participation. -
Recognize the changes to the actual data structure and other areas of the database definition that might occur. Some technologies impact the database schema by applying their own objects. -
Watch out for "What's new in SQL Server 2000." Typically the exam tests on new features within the software, and this exam is certainly no different. The discussion of physical design topics reviews many important design and exam criteria, many of which are new features.  |
