Exam Reviews

The SQL Server Challenge

You don’t have to be a DBA for a worldwide organization to pass this test, but you’d better know all the ins and outs of large database administration.

• By Anil Desai
• 03/01/2001

If you work with relational database servers, you can’t help but notice that Microsoft has been making a strong push toward the enterprise-level database storage marketplace. Microsoft continues its successful journey with SQL Server 2000, the successor to the popular SQL Server 7.0 platform. Specific areas of improvement in the database platform include key enhancements related to reliability, scalability, performance and availability. On the surface, SQL Server 2000 may not look like a huge upgrade (certainly not as dramatic a change as the move from SQL Server 6.5 to SQL Server 7.0), but when you drill down into the product, you’ll find many important architectural changes.

With exam 70-228, Microsoft wants to test your ability to administer SQL Server 2000 in large environments. In fact, the recommended “audience” for the exam is someone who has at least one year of experience with SQL Server 2000 in an environment that ranges from “50 to 5,000 or more users” and with “databases as large as 2TB.” That’s a fairly tall order. Rest assured that if you know what you’re doing, you could get by without being a DBA for one of the world’s largest databases. I took the beta version of this exam and found it to be challenging overall. However, if you really work with SQL Server often and have taken the time to learn about its many features, you should do well.

Overall Impressions
I found the exam to be a fair assessment of my SQL Server skills. The questions I encountered were detailed and focused in specific topics related to SQL Server administration. Performance monitoring and optimization seemed to be hit pretty hard, as was configuration of a database server given specific business requirements.

The questions themselves were fairly straightforward. The majority were standard multiple-choice questions, but the test also included several multiple response questions (where you’re required to choose two or three answers). All required a good understanding of how SQL Server 2000 works.

A good way to prepare for any exam is to start with the topics presented in Microsoft’s Preparation Guide. In this case, the goals truly do focus on features of SQL Server 2000 that I find to be relevant to the real-world work of DBAs. Let’s start by looking at the specific areas of the product that Microsoft expects you — a potential enterprise-level DBA — to understand.

Installing and Configuring the Software
Microsoft has gone to great lengths to make the installation and basic setup of SQL Server 2000 as simple as possible, and it really shows. In many cases, you’ll find yourself choosing all of the default installation options for SQL Server. However, a good DBA must understand the meaning and relevance of database sort order and character sets. It’s important to know how different versions of SQL Server can exist on the same machine and how you can upgrade from earlier versions of SQL Server. Also, you should understand how multiple instances of SQL Server 2000 work.

Tip: You can run multiple instances of SQL Server on the same machine at the same time. The default instance can be SQL Server 6.5, 7.0 or 2000, but additional instances can be SQL Server 2000 only. Make sure you know how to upgrade databases. To upgrade databases from an instance of SQL Server 7.0, you must use the Copy Database Wizard. To upgrade from SQL Server 6.5, you must use the SQL Server Upgrade Wizard.

Creating Databases
Perhaps one of the most important and common job functions for a database administrator is to create and manage actual databases. Important concerns include where to store data files and transaction log files and how to optimize storage for multiple databases. For small database servers, you can get away with ignoring some of these issues. However, in larger and more heavily used installations, file placement can dramatically affect performance. Be sure you know where to place data files given a specific server disk configuration. Once the databases have been created, understand the operations for automatically expanding database and transaction log files.

Although some people who find themselves in a strict DBA role (vs. that of a database developer) may not work with all of them frequently, you should understand how various database objects work. The list includes tables, indexes, constraints, views, stored procedures, user-defined functions (UDFs) and triggers.

Tip: When creating databases, it’s recommended that you place database files and transaction log files on separate physical hard disks from transaction log files. Note that placing log files on different logical partitions can actually decrease performance. Also, understand how various levels of RAID should affect data file placement.

Managing, Monitoring and Troubleshooting Databases
A good DBA will always proactively monitor the databases for which he or she is responsible. Fortunately, many features in SQL Server 2000 make this potentially complex task much more manageable. Keep in mind that your goal is to optimize resource usage and that the most valuable resources on your servers include processor utilization, physical memory usage, physical disk I/O and network performance. It’ll probably come as no surprise that you should understand the various backup methods available in SQL Server, including recovery modes (full, bulk-logged and simple), full backups, differential backups and transaction log backups. Note that the way you design your backup operations will affect how you can restore information.

Tip: Here’s one area where practice will really help. Create some sample databases on a test server and try using various backup and recovery methods. It helps if you have a sample application that can generate queries that run against SQL Server. Monitor the cache hit ratio and the number of batches that are executing. Also, use SQL Server Profiler to monitor some sample queries and see what types of information you can get from the traces.

In its objectives Microsoft places a heavy emphasis on a DBA’s ability to keep a database server running optimally. Specifically, you should know what the common Database Consistency Checker (DBCC) commands do and how and when you should use them. Be sure you understand how indexes work and how to determine if your indexes are fragmented. Remember that on enterprise-level servers, you can’t just go around rebuilding indexes whenever you want; this can reduce database performance. Also, know how and when you might want to use materialized views (also referred to as “indexed views”).

But wait, there’s more! Know how to monitor performance using the various SQL Server 2000 features that are at your disposal: Performance Monitor, SQL Server Profiler, SQL Server Alerts (and how they interact with the Windows NT/2000 Event log), features in Query Analyzer, and the Index Tuning Wizard. For example, do you know how to provide output from a SQL Profiler trace or a graphical execution plan generated by Query Analyzer and determine the potential cause of the performance problem? Such tasks are challenging but important to understand.

Extracting and Transforming Data
Wouldn’t it be nice if every relational database system in the world could communicate through a common language? Although the Structured Query Language (SQL) is a huge step in the right direction, there are many differences among real-world relational database systems. Large organizations tend to have many different types of data repositories. These can range from Excel spreadsheets to mainframes to various types of database servers. In an enterprise, DBAs are often responsible for moving and copying data between various systems. That’s one reason that Microsoft has included the Data Transformation Services (DTS) with SQL Server.

Be sure you understand the various methods for transferring data in and out of SQL Server, including DTS, BULK INSERT and bulk copy (bcp). Just as important, understand the capabilities and limits of each so you can decide the best solution for a given situation. Also, be sure you know how to set up linked servers and perform distributed queries (that is, queries across multiple database servers). Know which utilities are the right tools for the job.

Tip: Be sure to play around with DTS. Even if you don’t have access to large databases, you can use the user-friendly tools and tasks to move data between databases on your machine. Also, be sure to take advantage of the fact that you can install multiple instances of SQL Server on the same machine. This is great for simulating distributed queries and data movement between “servers.”

Managing and Monitoring Security
In many organizations, the company’s crown jewels are stored in databases. Therefore, it’s the job of a DBA to ensure that only authorized users are able to access information. Needless to say, you need to understand security. As an enterprise-level DBA you should understand that there are multiple levels of security in SQL Server 2000. Just as Windows 2000 allows you to set permissions on various objects such as logins, shares, files and folders, SQL Server allows you a flexible set of security options. You should thoroughly understand SQL Server’s authentication modes. Also, be sure you know the various security features, including server logins, server roles, database users, database roles, application roles and object-level permissions. All are important and must be administered carefully.

Tip: Microsoft recommends that you use Windows-based authentication whenever possible. Be sure you understand how this works and situations in which you may have to use mixed-mode authentication. Also, be sure you understand the interactions between SQL Server logins, database roles and database users. The differences are important, and using them effectively can save a great deal of administrative burden.

No Easy Task
General knowledge in other areas will also be helpful in your job as a DBA. For example, know what SQL Server’s Analysis Services are designed to do; also, have an overview of the concepts behind data warehousing and data mining.

In order to prepare for the exam, be sure you spend plenty of time with the product and its tools. I’ve worked with SQL Server 2000 since the beta days, and I still occasionally find new and useful features or “gotchas.” A good way to go about preparing for an enterprise-level exam is to imagine realistic scenarios and think of solutions. Make no mistake: Understanding SQL Server 2000 is no easy task, and the exam will make sure you’ve done your homework. But, if you have a solid background with the software, you’ll find the exam to be tough, fair and (best of all) passable. Good luck!