Microsoft SQL Server DBA Survival Guide --Introduction Microsoft SQL Server DBA Survival Guide Introduction In the late 1980s and early 1990s, the Sybase RDBMS (Relational Database Management System) was one of the most popular and innovative RDBMS systems. RDBMS systems could be found in UNIX and NetWare environments. But the entry fee to purchase an RDBMS system was out of the reach of many small businesses and workgroups. To this end, Sybase and Microsoft entered into a joint venture. Microsoft would license and sell the Sybase RDBMS system, SQL Server, under the Microsoft name on the OS/2 platform. Microsoft SQL Server for OS/2 became a good, affordable workgroup RDBMS system. The product, however, was limited by OS/2 in its scalability and performance. The relationship between Microsoft and Sybase became strained and ended around the time Microsoft announced that they had rewritten SQL Server for the Windows NT platform. The Windows NT platform is Microsoft's operating system of the future, slated to take on the UNIX and NetWare operating systems. Because of its great pricing and performance, Microsoft SQL Server for Windows NT quickly became one of the most popular Windows NT applications. The split between Sybase and Microsoft becomes quite apparent when you look at the two SQL Server products. Microsoft SQL Server is tightly integrated into the NT operating system and the database administration tasks are packaged into several graphical front-end tools. Administering Microsoft SQL Server for Windows NT is not the same as administering a Sybase SQL Server, which lacks the graphical administrating and scheduling tools. It was about this time that we first started hearing cries from customers and various online services for a book that specifically covered Microsoft SQL Server. Microsoft began previewing to customers and SPs the next generation of SQL Server for Windows NT that was to compete in the VLDB (Very Large Database) arena against Sybase and Oracle. The next generation of SQL Server had a brand-new graphical interface, code-named starfighter, and was designed to allow database administration in an enterprise environment. When we saw the sweeping changes being added to SQL Server, we began to realize that it was time for a book that concentrated on Microsoft SQL Server. At the same time, Sams Publishing was introducing a brand-new series of books called the DBA Survival Guides that concentrated on real-world experience in managing RDBMS systems. When we saw the format for the DBA Survival Guide series, Orryn and I realized that this was the type of book everyone had been asking for! To make a long story short, Orryn and I and the folks at Sams Publishing put out the very first SQL Server 6.0 book on the market, the Microsoft SQL Server DBA Survival Guide. The book has been very well received by the Microsoft SQL Server community. Orryn and I have appreciated all the e-mail and feedback we have received from readers of that first edition. When we learned that Microsoft was preparing the new release, SQL Server Version 6.5, we were excited to learn that Sams wanted us to do a second edition of the book for the new release. This second edition incorporates all the popular features of the first edition plus additional tips and tricks and descriptions of all the new and exciting features of the by-far-best-release-to-date of Microsoft SQL Server--version 6.5. The Goals of This Book Managing a Microsoft SQL Server is quite different from managing several other RDBMS packages because of the graphical nature and ease of use of the overall SQL Server system. The trick to becoming a good Microsoft DBA is to become familiar with the graphical front-end, to understand what happens behind the scenes (that is, what happens when you push a particular button), and to have a good understanding of the product and your job. The goals of this book are as follows: To provide the knowledge and know-how to administrator a SQL Server database To appeal to all levels of DBAs: beginner, intermediate, and experienced To appeal to all levels of developers: beginner, intermediate, and experienced Offer any tips, tricks, and suggestions buried deep within the documentation Offer real-world insight and experience and to pass on any tips, tricks, or suggestions learned the hard way Provide checklists and examples for SQL Server DBA tasks Provide conventions and naming standards Provide insight into the tasks that make up a DBA's job description The Organization of the Book The book is organized into several parts that compromise the various jobs and tasks the DBA performs. Part I, "Introduction," is an overall introductory section that includes the following chapters. For DBAs new to the world of client/server computing, Chapter 1 provides an overview of general client/server concepts. It explains what client/server really means (all vendors seem to attach the term client/server to their products, even when it really does not meet the definition of client/server). This chapter also explains the benefits of client/server computing compared to other types of computing (such as mainframe and PC/file server). Not sure what a DBA is or what the responsibilities of a DBA are? Chapter 2 is for you. Part II, "The World of Microsoft's SQL Server," is a high-level overview. Chapter 3 discusses enhancements made to SQL Server 6.x. Chapter 4 details how SQL Server integrates with Windows NT and also explains the benefits of SQL Server being tightly integrated with Windows NT--and how this integration helps differentiate the product from its competitors. Part III, "Planning and Installing/Upgrading SQL Server," does just what its title suggests. Chapter 5 covers the planning steps required before you attempt a SQL Server upgrade or installation. Do you have all your bases covered in the event an upgrade fails? This chapter covers this topic and many more. Chapter 6 discusses installation and upgrade. Part IV, "Database Operation," is the largest section of the book and includes chapters about many of the functions you will perform as a DBA. Chapter 7 provides a high-level explanation of the types of tasks that can be performed through the Enterprise Manager. Chapter 8 discusses how to manage devices. Chapter 9 explains how to create, manage, and delete a database. The chapter includes a discussion about making logs an integral part of a database. If you are a DBA new to SQL Server, you will not want to skip the topic titled "The Two Most Common Database Errors." Without exception, every DBA encounters the errors discussed in this section. Knowing how to deal with these errors ahead of time will simplify a DBA's life. Chapter 10 discusses user management. Every organization should be concerned with data security; Chapter 11 discusses in detail how to implement data security through SQL Server. Several strategies are offered to help simplify security administration while maintaining an effective security model. Not sure what Microsoft SQL Server's replication is all about? Having trouble installing the distribution database? Not sure why a replicated database should be read-only? Find these answers and more in Chapter 12. Wondering what distributed transactions are all about and how to administer them with SQL Server 6.5? See Chapter 13. Curious how to use your backups? When and how often to back up? These are standard questions all new DBAs face. What happens if a database fails after a backup? Can you provide up-to-the-minute recovery? See Chapters 14 and 15. Can't get BCP to work? You're not alone--almost every DBA hits a snag or two when trying to work with BCP. Chapter 16 discusses in detail how BCP works and provides numerous tips and examples on how to make BCP work. Also included in this chapter are several alternatives to BCP. Having problems? You won't want to miss Chapter 17, which discusses checkups and problem detection. What would a database book be without a section on performance and tuning? Part V, "Performance and Tuning," fills that role. Having performance problems with SQL Server? Look at Chapter 18. SQL Server provides numerous tools to help diagnose and isolate bottlenecks. The secret is knowing how to effectively use these tools. For example, the Performance Monitor allows you to monitor over 40 different SQL Server counters and several hundred different operating system counters. Which counters do you look at? Chapter 18 guides you in the right direction if you are wondering which Performance Monitor counters you should analyze. The chapter also discusses how to monitor user activity, a feature that has been extensively enhanced in version 6.5. Need to configure SQL Server? Which knobs do you turn? Check out Chapter 19. Chapter 20 provides information on database design issues. Do indexes have you all tangled up? If so, don't miss Chapter 21. Chapter 22 explains in easy-to-understand vernacular the inner workings of SQL Server's cost-based optimizer. When transactions are slow to process or you are experiencing blocking or deadlocks, you will want to refer to the tips and tricks in this chapter. Knowing how to read a showplan is a key element to diagnosing query performance problems. The hard part about reading a showplan is knowing what to look for because lots of cryptic information is generated. This chapter explains what to look for in the output generated by a showplan, what the output really means, and how to improve performance based on showplan information. I think every DBA has seen an application that runs fine when a single user is logged in to the system, but when multiple users log on, the system bogs down. With multi-user applications, issues such as blocking and deadlocks must be addressed. Chapter 23 offers solutions that can reduce the headaches associated with a multi-user system. Be sure to take a look at the section titled, "Ten Tips to Help Minimize Locking and Prevent Deadlocks." Do you want to learn more about stored procedures and cursors? Look at Chapter 24. This chapter provides a detailed discussion on these two topics and includes several examples the DBA can use to automate common tasks. The chapters in Part VI, "Maintaining the Shop," consist of ways to automate and help you plan and schedule various DBA tasks. Chapter 25 explains why maintenance should be periodically performed on SQL Server and the Windows NT operating system. In addition to explaining why you should perform maintenance, the chapter provides step-by-step instructions on how to maintain the system. After reading Chapter 25, you will want to automate several of the maintenance tasks discussed in the chapter. Chapter 26 explains how to automate common DBA tasks through the use of two core components of SQL Server: Task Scheduler and Alert Manager. Graphical automation and advanced features such as e-mail and pager notification are two examples of how these components can help simplify a DBA's life. You already know that OLE is included with SQL Server, but what can you do with it? Chapter 27 walks through the construction of an application that helps simplify database administration task using Visual Basic. Chapter 28 describes some utilities that are new to SQL Server 6.5--don't miss out on what's new! Part VII, "Appendixes," is dedicated to some useful quick-references that can provide additional information. Every DBA should understand how to use DBCC commands; inadequate DBCC knowledge can prolong data corruption and complicate data restoration. Be sure to look at the appendix on DBCC commands. It details each command, provides a comparison of the commands, and recommends which commands should be frequently run as part of a maintenance schedule. Conventions Used in this Book The following conventions are used in this book: The computer font is used for commands, parameters, statements, and text you see on-screen. A boldfaced computer font indicates text you type. Italics indicate new terms or items of emphasis. NOTE: Notes provide additional information pertinent to the current subject matter. TIP: Tips offer useful hints and information. CAUTION: Caution boxes present warnings and describe the consequences of particular actions. Stranger than Fiction! Some say that truth is stranger than fiction. These boxes offer fun facts to know and tell that are stranger than fiction! DISCLAIMER To order books from QUE, call us at 800-716-0044 or 317-361-5400. For comments or technical support for our books and software, select Talk to Us. © 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.