Exam Reviews

Demanding Data Design

The design and implementation of a database can make the difference between a well-tuned application and one that's a thorn in your side. The SQL Server 2000 Design exam tests your expertise in both.

• By Anil Desai
• 07/01/2001

For the last several years, Microsoft has divided the focus of SQL Server into two areas. One is database administration, covered by exam 70-228. This test focuses on your ability to set up and manage SQL Server 2000 solutions at a systems level. It’s geared toward systems administrators responsible for the care and feeding of SQL Server installations. (I reviewed this exam in the March 2001 issue.)

The other, 70-229, challenges your ability to design and implement databases. This test focuses more on development aspects, such as the logical and physical design of databases based on business requirements and the implementation of these designs using the features available in SQL Server. Overall, I found the exam (which I took in its beta form) to be fairly challenging. Microsoft expects that anyone who’s worked with SQL Server in heterogeneous environments, with client/server configurations of up to 5,000 users and databases as large as two terabytes, will be able to pass this exam. But in my opinion, if you’ve had a year of solid database development experience involving SQL Server, go ahead and train for the test. With that said, let’s move on to the details of what you should know as a Certified Database Administrator.

A Logical Data Model
A well-designed database structure’s a necessary foundation for a well-built, data-driven business application. The database design includes the logical separation of different entities (such as “Employees” and “Departments”) within the database, as well as attributes related to those entities (“First Name” or “Employee Number”). It’s important to understand the relationships between the various entities that will be the basis of your application. You should be able to answer design-related questions. For example, can an employee belong to more than one department? Can a department have zero employees? Must all employees be assigned a unique “Employee Number” value? These factors can have a large impact on how you design your database.

Entire books have been filled with rules and guidelines related to database design. For this certification, be sure you understand the concept of database normalization. A highly normalized database favors the elimination of redundancy of data and attempts to make sure all the attributes for an entity pertain only to that entity. Multiple “normal forms” have been defined, but it’s not necessary to know the details for each degree of normalization. Just understand the purpose of normalization and how you might normalize a schema, given a specific database structure. In some cases, you might choose to denormalize a database. One common business reason for denormalization is for reporting purposes. In some scenarios, the redundancy of information is an acceptable trade-off for performance.

In addition to following normalization rules, a well-designed database ensures the relationships between entities in the database aren’t violated. For example, users or applications may not be allowed to delete a department to which employees are assigned. Deleting the department would cause “orphans” in the Employees table (that is, it would result in employees who aren’t attached to an existing department). This type of data integrity is maintained through the use of referential integrity constraints. Specifically, you can use and define PRIMARY KEY and FOREIGN KEY relationships. New in SQL Server 2000 is the ability to allow cascading of UPDATE and DELETE operations once you’ve defined the proper constraints. For example, I could instruct the database to delete all employees related to a department when I delete the department itself. The schema tool within Enterprise Manager can be used to easily create and manage these types of constraints.

It’s likely you’ll want to add constraints to the types of values that can be entered into columns. The most basic type of column-level constraint is data type. You might choose to use an integer data type for a zip code column (or a character data type, if you want to allow dashes to be entered).

You can apply a UNIQUE constraint to enforce business rules. For example, suppose that employee numbers can’t be duplicated within the Employees table. In this case, a UNIQUE constraint on the “EmployeeNumber” column would prevent duplication from happening.

Tip: It’s important to understand how database design can affect the overall implementation of database objects. Make sure you know how and why a database should be normalized and be able to normalize or denormalize a database structure, given an existing schema. Also, be sure you have plenty of exposure to the schema tools in SQL Server’s Enterprise Manager.

The Physical Database
Once you’ve decided how you want your database to look, it’s time to address the task of implementing the design. The first order of business is to create it. Although SQL Server 2000 makes it easy for you to create databases with a simple command or a few mouse clicks in Enterprise Manager, you have various options for data storage implementations. For example, you can use multiple database files to spread information across multiple physical disks, and you can use filegroups to simplify the administration of your files. Important considerations include the efficient use of physical hard disks for the placement of datafiles and transaction log files. Given a set of database performance characteristics, you should be able to determine the best placement for data files. Once the database has been created, you need to understand how to use Data Definition Language (DDL) commands to create and alter objects such as tables.

Next, you need to understand the various objects you can create within SQL Server 2000 databases. The basics include tables and views. Understand how views can be used to simplify database access. Other features include constraints (discussed earlier), user-defined functions (new in SQL Server 2000), stored procedures, user-defined data types, rules, defaults and triggers.

Each type of object can be used to improve the overall performance of your database; the trick is to know when to use each. For example, given a specific set of requirements, you may be able to choose between views, stored procedures and user-defined functions for implementation. All of those options might work, but in some situations, there’s clearly a best choice. Make sure you know how to choose from among them. In addition to choosing the objects, you can create items with the “schema binding” option and encrypt object definitions.

Finally, more advanced implementations will take advantage of SQL Server’s replication functionality. You should know the different types of replication possible (snapshot, merge and transactional) and the pros and cons of each. Microsoft uses a publisher, distributor and subscriber metaphor for replication. You should also be familiar with queued updating and the problems this can solve. Another advanced feature is distributed partition views. In large installations where performance is important, this feature can be used to spread database load across many servers, thereby making more efficient use of resources and increasing scalability.

Tip: It’s a good practice to store your transaction log on a different physical disk from the data files for the database. This reduces disk-related contention caused by reads and writes to these files. Also, be sure you understand the purpose of each type of object in SQL Server 2000, and when and how to use them. For example, given a set of requirements, you should be able to figure out whether a view or user-defined function is the better choice.

Retrieving and Modifying Data
If you’re a database implementer, you can’t hide from Transact-SQL! It’s likely that you’re familiar with the basics of SELECT, INSERT, UPDATE and DELETE statements. But before tackling your certification, you should probably brush up on all of the features that Transact-SQL has to offer. For example, you should understand the behavior of aggregates and GROUP BY clauses in complex SELECT statements. You get bonus points (from me, not from Microsoft!) if you understand the purpose and function of the little-used (but valuable) HAVING clause. Since Transact-SQL is the language your users and applications will use to communicate with SQL Server, be sure you’re a fluent speaker before you attempt the exam.

Few real-world business environments exist in a vacuum. That is, most companies have many different types of data stores that can include simple text files, spreadsheets, file-based databases (such as Microsoft Access), mainframes and relational database systems. Microsoft’s ensured that SQL Server’s a team player by providing multiple tools for transferring information between data storage platforms.

Thus, you should be familiar with the concepts of linked servers. Also understand how you can use system functions such as OPENQUERY, OPENROWSET and OPENXML to read and write data from various sources within your environment.

In SQL Server’s bag of tricks for transferring data between systems is the bulk copy (bcp) utility, the BULK INSERT command and Data Transformation Services (DTS). Each has its strengths and weaknesses. Be able to choose the right tool for the job, given a set of business requirements.

Although it’s not a cure-all for business challenges, XML files provide several advantages to businesses that want to transfer information between systems. New in SQL Server 2000 is the ability for users to generate XML files simply by using the FOR XML clause in queries. Know the options for this clause, including RAW, AUTO and EXPLICIT, as well as the XMLDATA and ELEMENTS specifications.

Tip: Take the time to play around with SQL queries on a development system before you take the exam. XML features are an important improvement in SQL Server 2000. Be sure you practice reading from and writing to and from XML files.

Programming Business Logic
When you’re accessing and manipulating data in real-world relational database systems, you need to take advantage of many of Transact-SQL’s more advanced features. In general, the Transact-SQL language is quite powerful, but it’s also different from many other programming languages. Remember the main goal of SQL: retrieving and modifying data. As an MCDBA, you should understand concepts such as transactions (their purpose, their limitations and how you implement them). Also, know how to take advantage of SQL Server features such as triggers, views, user-defined functions and stored procedures for simplifying the processes of modifying and retrieving data. Each of these object types can be helpful in controlling how your application behaves, for optimizing performance and for ensuring that data integrity is properly maintained.

In addition to knowing how to create objects for managing business logic, be sure you understand the rules of logical flow. For example, you can call stored procedures from within other stored procedures, and there are ways to find out how “deep” the calling chain is. Also, be sure you can troubleshoot potential problems that may arise when you have logical errors.

Tip: There’s no real shortcut to learning how to program business logic efficiently in relational databases. If you don’t think you’ve had enough hands-on experience in designing a real-world business application, get hold of some sample code and walk through how it works. A well-designed system will take advantage of transactions and other features for improving performance.

Tuning Data Access
Achieving high performance is generally high on the list of priorities for all types of developers, and relational database systems are no exception. In fact, the efficient flow of data into and out of SQL Server can mean the difference between the success and failure of an application. Fortunately, many different tools are available for monitoring and analyzing performance. Performance monitoring and optimization can be addressed at various levels.

If you want to monitor overall system health at the server level, you should start with the Windows Performance tool (or Windows NT 4.0 Performance Monitor). SQL Server includes many objects and counters that can be used for finding out how busy your server is and how efficiently it’s operating. Many of the performance objects have changed in SQL Server 2000, so be sure you know which counters are useful for finding certain types of problems. The “Explain” button can be an excellent resource for understanding what you’re actually measuring.

At the level of specific databases, you can use the SQL Profiler to monitor the queries running on your machine. SQL Profiler can monitor hundreds of different parameters related to the action your SQL Server’s experiencing. You can monitor query times and various other operations such as server logins and logouts. Furthermore, you can save all this information to binary files or database tables for further analysis. Microsoft has made several changes and improvements to Profiler in SQL Server 2000, so be sure to familiarize yourself with its features.

Most database developers have run into particularly troublesome, slow-performing queries. In response, Microsoft has designed many different query analysis features into the SQL Query Analyzer tool. Make sure you understand the types of statistics you can view and how you can use the “execution plan” to find potential performance problems or bottlenecks in a query. This is a great way to determine if indexing might be needed or if other changes to the query logic might help. In some cases, changing a few lines of SQL code will result in orders of magnitude in improved performance.

Finally, no discussion of database performance is complete without mentioning indexes. SQL Server allows you to create one clustered index and many non-clustered indexes on tables. Understand the purpose and differences between these types of indexes, and how to choose which type to use, given a set of requirements. You can use SQL Server’s Index Tuning Wizard to examine queries or trace files (generated by SQL Profiler) and provide recommendations on indexing.

Tip: Become familiar with SQL Server’s various performance monitoring and optimization tools, including Performance Monitor objects and counters, SQL Profiler, Query Analyzer and the Index Tuning Wizard. You can set up these tools in a test environment on a sample application to create troubleshooting scenarios. Having these skills will be important in the real world and on the exam.

A Database Security Plan
Security is a major concern for database developers and database implementers alike. You must be sure that only authorized users can get to specific information. Many developers are moving away from using database-level security in favor of application-level security; but you should still understand permissions in SQL Server. You should understand how logins and database users interact. Also, know the purpose of roles and how to use them to simplify database administration.

In addition to these types of security, you can also implement very granular permissions on database objects such as tables. For example, you can allow members of your Human Resource department to modify all information in the Employees table, whereas other users can only query a subset of that data. Many different types of database security strategies exist. A well-designed plan will require minimal administrative effort while still meeting business requirements. Good techniques include the effective use of roles for creating groups of users based on their specific job functions. You can nest roles and change their membership. Make sure you have the ability to design a solid security strategy.

Tip: Be sure to experiment with server roles, database roles and object-level permissions before taking the exam. Start by creating some objects (such as tables and views) and setting various permissions on them to test the effects. In some cases, the results might not be exactly what you’d expect!

All’s Well That Begins Well
Database design and implementation is a complicated topic, one with a steep learning curve. The design and implementation of a database can make the difference between a well-tuned application and one that’s constantly a thorn in your side! However, Microsoft has worked hard to make SQL Server both user-friendly and powerful. Real-world experience is probably the best prep for exam 70-229, but studying features that you don’t often employ is just as important. Good luck!