Microsoft SQL Server Black Book - Table of Contents (397 pages) Introduction What's on the CD-ROM Dedication Chapter 1—Preinstallation Considerations What Is A Device? What, Then, Is A Database? What Are Character Sets And Sort Orders? What Is The Recommended System Configuration? Where Should The Microsoft SQL Server Be Installed? What’s Stored In The Master Database? The Master Database The Pubs Database The Model Database Tempdb The Msdb Database Be Careful With Memory What Security Model Will Be Used? Spring Cleaning Protocols Services What About The SQL Mail Client? Should I Use The Default Location For My Devices? What Hardware Should I Install Microsoft SQL Server On? Finally Summary Practical Guide to Preinstallation The Preinstallation Checklist Chapter 2—Installing Microsoft SQL Server Installing A Production Data Server SQL Server A (The Base Level) SQL Server B (Middle Of The Road) SQL Server C (The High-End Beast) Before Installing SQL Server Creating Your SQLExec Account One More Account To Go Setting Up A Mail Client One Last Time Summary Practical Guide to Installation SQL Server Installation From Start To Finish Post-Installation Issues Chapter 3—Development Versus Production Setting Up The Development Environment Third-Party Tools Data Modeling Server-Level Parameters User Connections Tempdb in RAM Sort Pages Resource Timeout Read-Ahead Optimization Priority Boost Max Worker Threads Lock Escalation Parameters Fill Factor Application Parameters DBCC PINTABLE Registry-Type Tables List-Type Tables Setup Scripts Sample Server Setup Script Scripting Objects Third-Party Management Transferring Objects Transferring Data BCP INSERT/SELECT DBArtisan Permissions Users And Groups Summary Practical Guide to Transferring Objects Registering Servers Moving Objects From Server To Server Warning Messages Pitfalls Chapter 4—Replication Data Distribution Models Two-Phase Commit Replication Consistency Terminology Publisher Subscriber Distribution Server Transaction Log Synchronization Horizontal Partitions Vertical Partitions Articles Publications Push Pull Server Roles Publisher Server Subscriber Server Distribution Server Scenarios Considerations Central Publisher Central Publisher With Remote Distribution Publishing Subscriber Central Subscriber Multiple Publishers Of A Single Table Events And Processes Log Reader Process Synchronization Process Replication Distribution Process Communication Failures Prerequisites For Replication Memory Working Directory Same Character Set Protocol Trusts Disk Space SQL Executive User Connections Primary Key Summary Practical Guide to Replication Installing The Distribution Database Setting Publication Options Creating Publications And Articles Setting Subscription Options Subscribing To A Publication Chapter 5—Structured Query Language (SQL) ANSI-Compliant SQL Syntax Comments Pubs Database Authors Sales Titleauthor SELECT Statements WHERE Clause ORDER BY Clause GROUP BY Clause Join Conditions Aliases Aggregates And Functions SUM() MAX() MIN() AVG() COUNT() CONVERT() GETDATE() DATEDIFF() DATEPART() SOUNDEX() SUBSTRING() UPPER() CHARINDEX() RTRIM() System Functions ISNULL() USER_ID() USER_NAME() DATALENGTH() COL_LENGTH() Calculated Values Optimizer Hints Subqueries Union INSERT Statements Identity Columns Stored Procedures Triggers UPDATE Statements DELETE Statements Batches Cursors Summary Practical Guide To SQL Schema Changes Backing Up Data Renaming Objects To Be Modified Scripting Objects Converting And Inserting Old Data Cleaning Up The Environment A Word On Constraints Chapter 6—Stored Procedures Consistent Data Manipulation Enter Stored Procedures Establishing Standards Getting Data Modifying Data Modular Programming Reduced Client Processing Network Traffic Calling A Stored Procedure Query Optimizer Query Plan Parameters Variables NT Server Registry Maintenance Return Codes Additional Rules Nesting And Recursion System Stored Procedures Custom Stored Procedures External Stored Procedures Remote Stored Procedures Startup Stored Procedures Prior To Production Summary Practical Guide to Stored Procedures Parsing A String Redundant Code Reduced Network Traffic Calling Procedures Within Procedures Chapter 7—Views Syntax For Creating Views Normalized Data Partitioned Data Vertical Partitions Horizontal Partitions Multiple Tables Computed Values Security Updates Underlying Objects Performance Restrictions Summary Practical Guide to Views Determining Column Needs Partitioning And Combining Data Checking Index Coverage Modifications Chapter 8—Triggers Data Integrity Syntax Business Rules Permissions Nesting More On Triggers Virtual Tables Inserted Tables Deleted Tables Virtual Table Usage Global Variables INSERT Triggers UPDATE Triggers DELETE Triggers Limitations Multiple-Row Considerations Performance Summary Practical Guide to Triggers Remove The Foreign Key Constraints Define The Business Rule Identify The Child Records Graphically Represent The Trigger Firing Order Write A Test Script Check The titleAuthor Table Create The Trigger Test The Trigger Chapter 9—Rules, Defaults, Constraints, And User-Defined Data Types Rules Creating Rules Binding Rules Changing Rules Dropping Rules Defaults Creating Defaults Binding Defaults Changing Defaults Dropping Defaults Constraints Primary Key Unique Foreign Key Default Check User-Defined Data Types Entity Definition Create Table Statement Dependency Summary Practical Guide to Rules, Defaults, Constraints, And User-Defined Data Types Creating The Scripts Printing Out A UDT Listing Building A Table Structure Maintenance And Troubleshooting Chapter 10—Error Codes Errors In Microsoft SQL Server Method Or Madness Service Packs Research Summary Practical Guide to Error Codes Query/Connection-Based Errors Server Configuration Errors Connectivity Errors Transaction Log Errors Table And Index Errors Chapter 11—Performance Tuning And Optimization What Is Performance? Performance Monitor Windows NT Data Models Application Design Establish A Baseline Keep It Simple SQL Server Trace Flags SQL Trace SQL Probe Summary Practical Guide to Tuning And Optimization Using The Performance Monitor Using SQL Trace Chapter 12—Newsgroups And The Internet Accessing The Internet Browsers Functions Search Engines Knowledge Base Service Packs And Patches TechNet CD-ROM Microsoft SQL Server Books Online Newsgroups Summary Practical Guide to Free Agent Installation Installing The Newsreader Configuring The Source News Server Subscribing To Newsgroups Preferences Newsgroup Etiquette A Coriolis Group New Title—Bonus Chapter Chapter 13—Using Java To Access Databases The GuestBook Program Initializing The Application Handling Window Events Opening A Database Connection Closing The Database Connection Executing A SQL Command Handling Errors And Exceptions Setting Up The ODBC Data Source An Improved GuestBook Program The init( ) Method The execSQLCommand Method The moveDataToForm( ) Method The actionPerformed( ) Method The destroy( ) Method The WindowHandler Inner Class Java And Access Data Types Summary Appendix A Appendix B Glossary Index Introduction Welcome to the world of Microsoft SQL Server! Here is finally a client/server database product that can deliver world-class performance at a price that most enterprises can afford not only to purchase, but also to support. SQL Server’s ease of use, coupled with the incredible feature set that accompanies it, delivers enterprise-level client/server computing to everyone. Microsoft SQL Server Black Book will focus on the tasks involved in harnessing Microsoft SQL Server’s capabilities to create a solid production data server. This book focuses on the current release of Microsoft SQL Server while using many techniques that can be applied as far back as version 4.21. Writing Microsoft SQL Server Black Book has been the most challenging task I have undertaken in a long time. I was asked to write it to fill a void in the market, to deliver a book that focuses on creating production servers with hands-on, step-by-step processes for installing, configuring, and troubleshooting Microsoft SQL Server. I have tried to keep the language of the book as plain-English and matter-of-fact as possible, because that is the way I teach. I have supplied you with substantial technical background, while also supplying numerous examples. This book can be used as a tutorial or desktop reference to help you get Microsoft SQL Server to fulfill your organization’s needs. I have been through many classes as a student and as an instructor—about topics ranging from adult learning principles to Microsoft SQL Server Administration. From these classes, I have acquired a great deal of knowledge that can be applied to creating a solid production data server with Microsoft SQL Server. I want to share that with you and help you head off the problems that you may encounter configuring your servers. I am a business owner, a consultant, a DBA, and a teacher’s most likely are many of you. I have fought and continue to fight the same battles that you do on a daily basis. That is why I think this book can be such a great value to you! Hopefully my experience will help you develop solid database systems in your Microsoft SQL Server environment. Each chapter is broken into two sections. The first part of each chapter presents explanatory material about the chapter topics. The second page of this first part is a blank Administrator’s Notes page, for you to write on and refer back to later. The first part of the chapter ends with a Summary section, which is a bulleted list of the important points of the chapter. The second part of each chapter (the Practical Guide) supplies you with some step-by-step tasks that reinforce the content of the chapter and provide hands-on practice. Chapters 1to3 cover the installation and configuration of Microsoft SQL Server for both development and production environments. Chapter 4 explains the setup and terminology needed to implement replication between SQL servers. Chapters 5 to9 discuss the SQL language and the many objects that can be created for and utilized in client/server applications. Chapters 10, 11, and 12 cover the troubleshooting and tuning skills you will need to support your system over the long haul. One of the points I emphasize in this book is that you can solve any technical problem you are facing with the tools available to you. What are those tools? How do you research answers to your questions? How do you know if you can trust the sources you consult? How do particular features really work, and will they work for you? I cover all these questions and more in the pages of this book. I hope that you enjoy reading it this as much as I have writing it.