Mastering SQL Server can seem like a daunting task, even for people who have experience working with other databases. Because ANSI has defined standard requirements for SQL, after you have SQL Server running, learning to use it is a relatively straightforward process. Mastery of the total environment, however, is difficult because of the interdependencies between SQL Server, the Windows OS, and the network services on which SQL Server depends. This article offers some recommendations for books, training, online resources, and other tools that will help you become the office guru.
People who use SQL Server have many types of jobs. Your job will determine what skills and resources you need most. All SQL Server developers and administrators should have similar basic skills, but a wide disparity exists among specific skill sets that different SQL Server professionals need. (For more information about determining your skill set, see Brian Moran, "The Future of the DBA," page 18.) Creating a universal set of recommendations is impossible, so let's examine some resources that every SQL Server developer and administrator should have, then look at resources that are useful for two specific groups: DBAs and desktop and Web application developers. The sidebar "Bibliography of Resources,"contains bibliographic information for each resource that I mention.
Resources That Everyone Needs
Working on SQL Serverspecific skills is easy because SQL Server is isolated from the overall server environment, and you can practice without risking your whole server setup. If you're just getting started with SQL, I recommend The Practical SQL Handbook: Using Structured Query Language. This book not only teaches basic SQL syntax but also shows how to use intermediate-level SELECT statements (e.g., subqueries and correlated subqueries) and introduces aggregating information by groups. After you've mastered basic queries, Joe Celko's SQL for Smarties: Advanced SQL Programming, 2nd edition, is a great way to open your mind to the possibilities that more advanced queries and query combinations can offer. Pay special attention to queries that you can use to replace solutions that use server- or client-side cursors.
Next, everyone should read Inside Microsoft SQL Server 2000, Inside Microsoft SQL Server 7.0, or Inside Microsoft SQL Server 6.5. Frequently, familiarity with another database system leads you to expect SQL Server to operate the same way. But Microsoft's SQL Server architecture is completely different from all other database management systems (DBMSs), including the Sybase version of SQL Server. These books contain detailed information about how Microsoft's DBMS implements its core functionality. The chapters about hardware performance planning will help those of you who need to make decisions about server hardware, and the chapters about query performance tuning will help developers write more efficient applications. These books should be the foundation on which you build the rest of your SQL Server skills.
One key to efficient SQL Server performance is knowing how SQL Server works with Windows. Many of SQL Server's perfor-mance enhancements in the past 5 years have come from using existing functionality in Windows more efficiently. The tighter integration with Windows, however, creates additional work for SQL Server administrators and developers. Because SQL Server 2000 and 7.0 are now using COM interfaces and native Windows APIs, developers and administrators need to understand how Windows implements those services. For example, SQL Server 2000 running on Windows 2000 can use a user's Win2K logon credentials to log in to another server on behalf of the user. This feature is called delegation: All queries on the remote server run in the security context of the user, not the account that SQL Server is using. For administrators, tighter integration with Windows makes it easier to integrate SQL Server into the overall network infrastructure.
The more you know about Windows, the more you can achieve with SQL Server. So, look for books and training that will help you learn more about the capabilities of Win2K and Windows NT. (Note that Win2K offers some powerful options that you won't find in NT.) You can find information about Microsoft Official Curriculum (MOC) courses at the Microsoft Training & Certification Web site. MOC courses 2152, 2153, and 2154 will give you a good, broad-based foundation in Win2K features and capabilities. Designed for network administrators, these courses might include information that developers don't need. But knowing the basics of network operation is useful, especially if you want to use network services such as SQL Server. Similar courses exist for NT 4.0, but few training centers teach them now. The print versions of those courses are rare, too, so you might have to do some research to find them.
Inside Microsoft Windows 2000 and Inside Windows NT, 2nd edition, explain what happens behind the scenes at the OS level. Although these books and the other Inside titles are part of the Microsoft Programming Series, network and database administrators need to read these books as well. SQL Server is a Windows application, and in many cases, improving Windows performance translates into improved performance for SQL Server. In particular, the chapters about virtual memory and the file system are worth the price of the book.
Next, database and network administrators who want to understand how Kerberos security and Active Directory (AD) affect SQL Server running on Win2K need to read Understanding Windows 2000 Distributed Services. The chapter that explains how Kerberos works is especially easy to understand. The rest of the book also offers excellent explanations of everything from AD to OLE DB to Data Transformation Services (DTS) to Microsoft IIS. I recommend this book for developers, administrators, and managers.
Finally, the books in the Notes from the Field series from Microsoft Press contain descriptions of deployments of Microsoft products by Microsoft Consulting Services (MCS). Deploying Microsoft SQL Server 7.0: Notes from the Field contains insights about everything from deploying OLAP Services (including how to choose the right hardware) to implementing replication to upgrading from SQL Server 6.5, 6.0, or 4.21a to SQL Server 7.0. Although you might stray from the MCS method, the information in this book will make your deployment go more smoothly.
In general, knowledge about Windows pays just as many dividends as knowledge about SQL Server. For example, most problems with SQL Server connections happen because the user can't authenticate properly. Administrators who know a lot about Windows networks can resolve problems faster, and developers who are well versed in Windows internals can avoid mistakes that cause problems after the application goes into production. Now let's turn our attention to resources that will help each of two general categories of SQL Server users: administrators and developers.
Resources for Administrators
After they've mastered basic SQL Server concepts, administrators and developers need to branch out on different paths. For administrators, common tasks include planning hardware purchases, backing up data, managing security, and monitoring system performance. The Microsoft SQL Server home page has information ranging from planning guides to white papers about implementing features to online access to the Microsoft SQL Server 2000 Resource Kit and the Microsoft SQL Server 7.0 Resource Guide. These resource kits are worthwhile; each edition offers detailed explanations about how advanced features such as heterogeneous queries, OLAP, and English Query work. You can get these resource kits in any of four ways: in print, from the Microsoft Developer Network (MSDN) Library, on the TechNet CD-ROM, and on the TechNet home page on Microsoft's Web site. If you subscribe to TechNet, you already get the resource kits for SQL Server 2000 and 7.0. In fact, although it costs as much as 10 or more technical books, TechNet is an essential resource for most people who work with Microsoft products.
In addition to SQL Server Magazine's comprehensive site about all things SQL Server, a popular, non-Microsoft Web site for SQL Server professionals is Swynk.com. This site has been a great resource for articles, product reviews, how-to articles, and scripts relating to SQL Server for many years, but in the past couple of years, it has expanded its coverage beyond SQL Server to include information about Microsoft Exchange Server, Microsoft Systems Management Server (SMS), Windows, and Windows scripting. Before you start to write a script, be sure to check Swynk.com. You'll probably find that someone else has written exactly what you need or something you can tailor to fit your situation. And if you develop a unique scripting solution for a problem, be sure to submit it so that other developers can benefit.
For the performance-minded administrator, or anyone who looks for better ways to solve problems, SQL-Server-Performance.Com is a site dedicated to tips, tricks, and insights for improving performance. Browse the site's articles to see whether you're making some of the common performance mistakes. Also, read Windows 2000 Performance Tuning & Optimization. This book includes a section about how to help Microsoft BackOffice applications run faster. Nonprogrammers will likely find this book more approachable than Inside Microsoft Windows 2000, and reading both books will give you a strong foundation for improving Windows performance.
A final general resource, MOC Course 2072: Administering a Microsoft SQL Server 2000 Database, covers all the basic tasks for administering SQL Server. This course will help you learn tasks such as allocating disk space for databases, setting up security, performing backups, and implementing replication. To get the most out of an instructor-led MOC course, find an instructor who can clearly articulate his or her experience with using SQL Server. After more than 6 years teaching SQL Server courses and talking to other trainers, my unscientific research suggests that students learn better and retain more when an instructor has experience managing at least one server. Therefore, talk to SQL Server trainers in your area before you decide where to take the course. For more information about choosing an instructor-led course, see Kalen Delaney's Windows 2000 Magazine article "Instructor-Led Training."
Resources for Developers
Unlike administrators, developers need to learn more about using SQL and T-SQL efficiently than they need to learn about system maintenance or network integration. When you're ready to go beyond The Practical SQL Handbook: Using Structured Query Language, the next book you should read is SQL Server 2000 Design and T-SQL Programming. The authors, SQL Server Magazine contributing editors Michael D. Reilly and Michelle A. Poolet, explain database design theory in an easy-to-follow style. As part of the explanation, they walk you through the steps for implementing a database design on SQL Server. The book concludes with advice about how to improve performance in multiuser applications through intelligent use of transactions, locking, indexing, and stored procedures. Most important, you can use this book to learn how improper transaction handling, including the implicit transactions in INSERT, UPDATE, and DELETE statements, can drastically affect not only application performance but also the overall capacity of the server. In general, the most common mistakes that degrade server performance occur because the application developer doesn't understand how locking and transactions affect the system; therefore, these areas should be high on your list of topics to study.
The next step toward mastering SQL Server development is to read Professional SQL Server 2000 Programming. This book addresses the client/server developer who needs to use SQL Server from within Visual Basic (VB) or Active Server Pages (ASP), including how to use distributed transaction services in a multitiered application. Because SQL Server is typically the back-end database for an application—not the platform running the application—you need different skills to write programs that use SQL Server efficiently. This book does a good job of helping you avoid common mistakes.
You might think the three books I've recommended overlap quite a bit, but they have different viewpoints. The Practical SQL Handbook: Using Structured Query Language teaches someone without database experience how to write basic and intermediate queries for most relational database systems. SQL Server 2000 Design and T-SQL Programming teaches future project managers and administrators how to build and manage a database and how to write T-SQL code. Professional SQL Server 2000 Programming targets the client/server developer who needs to use SQL Server from within VB or ASP, and includes information about how to use DTS in a multitiered application.
Microsoft offers two MOC courses for SQL Server 2000 developers. Course 2071: Querying Microsoft SQL Server 2000 with Transact-SQL teaches basic T-SQL syntax and programming. Course 2073: Programming a Microsoft SQL Server 2000 Database covers writing stored procedures and triggers; creating databases, tables, and indexes; and other programming topics. Again, if you take an instructor-led course, check the instructor's credentials before signing up. In both of these courses, you'll benefit from having an instructor with on-the-job experience programming SQL Server at the server level.
How to Get Ahead
All the books I mention in this article are on my bookshelf, within arm's reach of my desk. I use them regularly, and they've taught me quite a few useful things. Often, being the office hero is a matter of knowing something that no one else knows. The person who reads magazines and books, talks to other experts in the area, and spends time exploring what SQL Server can do is the one who has the greatest chance of being a hero some day.
Another proven technique for getting ahead is to network with like-minded people who share your passion for a subject. User groups are a great way to maintain contact with other SQL Server professionals in your region. To locate a user group near you, visit the home page of the Alliance of Windows IT Professionals. SQL Server Magazine and Windows & .NET Magazine formed the alliance to promote membership in and growth of user groups worldwide and to provide a forum for education and discussion. Another group, the Professional Association for SQL Server (PASS), is a nonprofit community of database administrators and developers who share a common interest in SQL Server. The PASS Web site has links to discussion forums in which users work together to answer questions and solve problems. Between the online networking and the interaction at local user-group meetings, you'll find yourself way ahead of your coworkers.
The Microsoft Certified DBA (MCDBA) certification also adds a lot of credibility to a résumé. The certification hasn't been "watered down" because it's hard to pass the SQL Server exams without demonstrating real experience; SQL Server is complex enough to make simply memorizing answers for an exam difficult. The MCDBA certification is a good way to advertise that you know what you're doing. Just remember that although companies might grant you interviews because of your certification, you still need knowledge and experience.
How to Stay Ahead
Mastery of SQL Server is a moving target. Typically, Microsoft releases a new SQL Server version every 18 to 24 months. But the company also uses service packs to add new features or change the behavior of existing features. So, just about the time you think you've mastered SQL Server, Microsoft gives you something new to learn. The only way to stay ahead is to keep learning constantly.
One of the best ways to stay ahead is to become a SQL Server beta tester. Unlike the betas that the company releases to the general public when a new version nears release, private betas are versions that Microsoft releases to a smaller group of about 10,000 users who agree to give feedback about its tests. You can become a member of this group by invitation only; to get invited, ask your local Microsoft representative. After you're in the group, you stay in by filling out surveys about your experiences with SQL Server, submitting bug reports, and participating in discussions in the private beta newsgroups. Use caution, however: You'll get banned from all private betas for all Microsoft products if you let beta copies of software slip outside your control. Microsoft gives beta testers the chance to see software as far in advance as 2 years before the official release date, so beta testing is a great way to stay in front of the herd.
Another way to stay ahead is to set up a test network and try out features you don't use every day in your job. An easy, inexpensive way to build a home network is to install the 120-day evaluation versions of Win2K and SQL Server 2000 on a VMware Virtual Machine (VM). VMware software simulates a complete PC and runs in a window as any other Windows application does. You can install a number of "guest" OSs on the VMware VM, just as you would on a physical computer. Because most of today's hardware can easily support two or three virtual machines, VMware's low cost means that you can create a small network for a lot less money than you'd spend to buy more computers. Workstation 3.0 runs on Windows XP, Win2K, and NT and supports XP, Win2K, NT, Windows Me, Windows 9x, and Windows 98 Second Edition (Win98SE) as guest OSs.
Another way to try unfamiliar features is to visit the Microsoft SQL Server Technical Resources Web site. This site contains demos, labs, and sample data for such features as English Query, data warehousing, and replication, so you can become familiar with features without using your production server.
Be Methodical
SQL Server utilities and wizards exist expressly to make SQL Server one of the easiest database systems to use, but mastering SQL Server means a lot of time spent studying and exploring a live server. Rather than trying to absorb everything all at once, divide your areas of study into related groups and examine each group separately. After you feel comfortable with one area, move on to a related area. For example, after you understand basic queries that use temporary tables, try building the same queries with a derived table replacing the temporary table. By working methodically through all the things SQL Server can do, you'll become the expert your coworkers and colleagues seek out for answers.
