Teaching Tips
This section is provided to give you, the instructor, some ideas on how to engage the attention of the students, while emphasizing important points that might be missed in their preparation for taking the Microsoft exam.
Potential Problems
There is a lot of information in this chapter. Spend the majority of your time in this chapter on sections one and two, since these are foundational for the information that will follow throughout the course.
Much of the material in the third section will be covered in later chapters; so much of it can be covered briefly. (The material to cover summarily includes Replication, Schema Binding, and User-Defined Functions [UDFs].)
In section four, there are many Server Configuration Options and Database Configuration Options. Ensure thorough coverage of each, but don't bog down in minute details.
Emphasis and Review
Point out that the collation sequence can be set on a database-by-database basis, or within each table at the column level, in SQL Server 2000, as opposed to in earlier versions where it could only be set as a server-wide setting. Also, note that the choice of the collation sequence needs to be made carefully, since it can be changed only by rebuilding the database once it has been set.
A change between SQL Server 7 and 2000 is that you can set database properties with the ALTER DATABASE statement, rather than just with the sp_dboption stored procedure. You may want to explain that there are several properties that cannot be set with the ALTER DATABASE command if the database is in use, or an unqualified user is accessing the database. In these instances the database designer needs to indicate a termination option in the command. This gives you the chance to terminate the connections of any unqualified users, generate an error message, or wait for the situation to change.
In reference to placing objects within file groups, point out that while objects can be easily moved from one file group to another, once a file group has been assigned to a database, the files cannot be moved to a different file group. Note that log files cannot be part of a file group. Let your students know that while the concepts regarding placing objects in file groups will most certainly be tested on the exam, in the real world, only those administrators who are experts in performance and tuning, and fully understand the access patterns of their data, would use file groups. The key to placement is to identify those objects that create "hotspots" or "bottlenecks" to data access and place these resource hogs on a server with fewer resource requirements.
Point out that a backup strategy for file groups must include backing up the indexes and data at the same time, because SQL Server does not enforce the backup of data and index file groups in a single operation. For example, if Table1 is placed on FileGroup1 and Table1's index is situated on FileGroup2, then the backup requirement is to back up both FileGroup1 and FileGroup2 in the same backup operation.
The primary piece of information in the section on leaving space for inserts is FILLFACTOR. Point out what FILLFACTOR is, when it is applied, and what happens when a page fills and needs to be split.
Since the use of Cascading Actions is a new feature in SQL Server 2000, make sure that you completely cover its effect on update and delete activities against Foreign Key/Primary Key values.
It may be helpful to students to demonstrate the idea of partitioned views with a drawing. Draw a table to represent the view, with rows and columns, and split it horizontally, and point the sections to various boxes to represent servers. Identify one of the columns and emphasize that a CHECK constraint must be used to enforce the mutual exclusion of redundant data on multiple servers.
Study Strategies
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 will 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 may occur. Some technologies will impact the database schema applying their own objects.
Watch out for "What's new in SQL Server 2000." Typically the exam will test on new features within the software and this exam is certainly no different. The physical design topics will discuss and review many important design and exam criteria, many of which are new features.
Other Resources
Microsoft SQL Server 2000 product documentation. To access Books Online (BOL), click the Start button, point to Programs, then Microsoft SQL Server, and then click Books Online. Double-click on Creating and Maintaining Databases, to review Databases, Database Design Considerations, Creating a Database, Optimizing Database Performance, Tables, and Views. Double-click on Replication and review Partitioned Views. Also, review Transact-SQL Reference and Troubleshooting.
Delaney, Kalen. Inside Microsoft SQL Server 2000. Redmond, Washington: Microsoft Press, 2000. Especially, Chapter 5, "Databases and Database Files," and Chapter 6, "Tables."
Students can find additional information on RAID at http://www.raid-advisory.com/CIC.html.