Microsoft SQL Server 2005 Management and Administration (Adobe Reader)

SQL server 2005 management and administration......Page 1 Contents......Page 7 Introduction......Page 34 Part I: Administering SQL Server Components......Page 42 What's New for the Database Engine with Service Pack 2......Page 44 Administering SQL Server 2005 Server Properties......Page 46 Administering the SQL Server Database Engine Folders......Page 62 Administering Database Properties......Page 69 SQL Server Database Engine Management Tasks......Page 84 Administering the SQL Server Agent......Page 92 Best Practices......Page 97 2 Administering SQL Server 2005 Analysis Services......Page 100 An Overview of Analysis Services......Page 101 What's New with Service Pack 2 for Analysis Services......Page 104 Administering Analysis Services......Page 105 Analysis Services Management Tasks......Page 119 Securing Analysis Services......Page 124 Administering and Monitoring Analysis Services Performance......Page 127 Best Practices......Page 129 What's New for Reporting Services in Service Pack 2......Page 132 Identifying Reporting Services Components......Page 133 Identifying Reporting Service Administrative Tools......Page 137 Using the SQL Server Configuration Manager......Page 138 Using the Reporting Services Configuration Manager......Page 140 Using the Reporting Services Administration Tools......Page 150 Administering Reporting Services Site Settings......Page 159 Administering Reporting Services Security......Page 166 Administering Shared Data Sources......Page 171 Administering Reports......Page 174 Troubleshooting Reporting Services......Page 178 Best Practices......Page 179 Identifying Notification Services Components......Page 182 Notification Services Security......Page 187 Administering Notification Services Components......Page 190 Administration from the Command Line......Page 212 Troubleshooting Notification Services......Page 213 Best Practices......Page 215 5 Administering SQL Server 2005 Integration Services......Page 216 Understanding Integration Services......Page 217 Managing Integration Services......Page 220 Administering Integration Services......Page 228 Command Prompt Utilities......Page 235 Best Practices......Page 240 What Is New for Replication with Service Pack 2?......Page 242 SQL Server 2005 Replication Essentials......Page 243 Administering SQL Server Replication......Page 252 Managing SQL Server 2005 Replication......Page 277 Monitoring and Troubleshooting SQL Server Replication......Page 283 Best Practices......Page 290 Part II: Managing SQL Server 2005......Page 292 7 Conducting a SQL Server 2005 Health Check......Page 294 Data Gathering for the Health Check......Page 296 Collecting Core Performance Information......Page 301 Analyzing the Data and Interpreting the Data Output......Page 307 Health Areas to Look Out For......Page 315 Best Practices......Page 320 What's New for Maintenance with Service Pack 2......Page 322 Establishing a SQL Server Maintenance Plan......Page 323 Creating a Maintenance Plan......Page 332 Establishing Maintenance Schedules for SQL Server......Page 345 Summary......Page 347 Best Practices......Page 348 The Importance of Indexes......Page 350 Index Design and Strategy......Page 357 Administering Indexes......Page 360 Configuring Indexes for Maximum Performance......Page 371 Best Practices......Page 383 Full-Text Search......Page 386 Reviewing the Full-Text Data Definition Language......Page 388 Managing Full-Text Catalogs and Indexes......Page 390 Administering the Full-Text Search......Page 411 Best Practices......Page 418 11 Creating Packages and Transferring Data......Page 420 Packages......Page 421 Creating Packages......Page 431 Instrumenting Packages......Page 448 Deploying Integration Services Packages......Page 456 Best Practices......Page 465 Part III: Securing the SQL Server Implementation......Page 466 12 Hardening a SQL Server 2005 Environment......Page 468 Windows and SQL Server Authentication......Page 469 Security Ramifications of the SA Account......Page 472 Using Configuration Tools to Harden the Installation......Page 475 Hardening SQL Server Service Accounts......Page 488 Installing Service Packs and Critical Fixes......Page 493 Understanding How SQL Server Security Logs Play a Part in Security......Page 494 Additional SQL Server Hardening Recommendations......Page 495 Best Practices......Page 500 SQL Server Security......Page 502 Security Management DDL......Page 515 Administering SQL Server Security......Page 520 Securing Other SQL Server Components......Page 536 Best Practices......Page 539 Encryption in SQL......Page 542 Securing the Data Storage......Page 546 Securing the Transmissions......Page 555 Using Certificates......Page 559 Best Practices......Page 566 Part IV: SQL Server 2005 Overview......Page 568 15 SQL Server 2005 Technology Primer......Page 570 Features of SQL Server 2005......Page 571 Database Engine Component......Page 576 Analysis Services Component......Page 584 Reporting Services Component......Page 589 Notification Services Component......Page 592 Integration Services Component......Page 594 Full-Text Search Component......Page 596 Replication Component......Page 598 Service Broker Component......Page 601 SQL Server 2005 Editions......Page 602 Summary......Page 603 16 Tools of the Trade......Page 604 The Most Important Tool: Help......Page 605 Studio Class Tools......Page 607 Explorer Class Tools......Page 611 Configuration Class Tools......Page 614 Tuning Class Tools......Page 618 Command-Line Utilities......Page 620 Other Tools......Page 621 Comparing SQL Server 2000 and SQL Server 2005......Page 626 Best Practices......Page 627 Part V: Disaster Recovery and High Availability......Page 628 17 Backing Up and Restoring the SQL Server 2005 Environment......Page 630 The Importance of Backups......Page 631 Backing Up and Recovering the Database Engine......Page 634 Backing Up and Recovering Reporting Services......Page 666 Backing Up and Recovering Analysis Services......Page 670 Backing Up and Recovering Full-Text Catalogs......Page 673 Understanding and Creating Database Snapshots......Page 674 Summary......Page 676 Best Practices......Page 677 SQL Server 2005 Failover Clustering Overview......Page 680 Administering SQL Server Failover Clustering......Page 687 Administering Patch Management on a SQL Server 2005 Failover Cluster......Page 704 Managing a SQL Server 2005 Failover Cluster......Page 713 Summary......Page 722 Best Practices......Page 723 19 Administering and Managing Database Mirroring......Page 724 SQL Server 2005 Database Mirroring Overview......Page 725 Combining Database Mirroring with Other SQL Server 2005 Technologies......Page 732 Administering a Database Mirroring Session......Page 733 Managing a Database Mirroring Session......Page 741 Monitoring and Troubleshooting a Database Mirroring Session......Page 746 Summary......Page 751 Best Practices......Page 752 SQL Server 2005 Log Shipping Overview......Page 754 Administering Log Shipping......Page 761 Managing Log Shipping......Page 772 Monitoring and Troubleshooting Log Shipping......Page 778 Best Practices......Page 781 Part VI: Monitoring and Troubleshooting SQL Server......Page 784 Monitoring SQL Server with Native Tools......Page 786 Using OpsMgr 2007 to Proactively Monitor SQL Server 2005......Page 797 Explaining How OpsMgr Works......Page 799 Outlining OpsMgr Architecture......Page 800 How to Use OpsMgr......Page 802 Exploring the SQL Server Management Pack......Page 803 Understanding OpsMgr Component Requirements......Page 806 Advanced OpsMgr Concepts......Page 808 Securing OpsMgr......Page 813 Summary......Page 818 Best Practices......Page 819 22 Performance Tuning and Troubleshooting SQL Server 2005......Page 820 Platform Troubleshooting and Optimization......Page 821 Component Troubleshooting and Optimization......Page 825 Database Troubleshooting and Optimization......Page 835 Application Optimization and Troubleshooting......Page 848 Best Practices......Page 852 Basic Installation of SQL Server 2005 Including All Components......Page 854 Installing SQL Server 2005 Service Pack 2......Page 857 Attaching the Latest AdventureWorks Database......Page 860 Basic Installation of Certificate Services......Page 861 Basic Installation of Operations Manager 2007......Page 863 Defining Access and Service Accounts......Page 864 Installing Operations Manager 2007 Prerequisites......Page 865 Installing Operations Manager 2007......Page 866 Installing Operations Manager 2007 Reporting......Page 867 A......Page 870 B......Page 875 C......Page 876 D......Page 881 E......Page 890 F......Page 892 H......Page 895 I......Page 896 L......Page 900 M......Page 902 N......Page 907 O......Page 908 P......Page 910 R......Page 915 S......Page 919 T......Page 931 V......Page 936 W......Page 937 X–Z......Page 939 Microsoft SQL Server 2005 Management and Administration , based on Service Pack 2, addresses the challenges database administrators regularly encounter on SQL Server 2005 by providing detailed guidance in the areas of management, administration, security, and monitoring. With coverage of the new features and functionality of SQL Server 2005 Service Pack 2, this book is designed to be comprehensive, resulting in something for all database administrators—from simple tips to tactical solutions. Microsoft SQL Server 2005 Management and Administration goes far beyond the basic installation and setup information found in many other resources. The book looks at day-to-day administration, best practices, tips, and step-by-step configurations based on real-world examples found in the industry. Unlike others, this book includes not only administration and management details on the Database Engine, but also coverage of other SQL Server 2005 components often overlooked, including Analysis Services, Reporting Services, and more. Understand how to... Configure and tune the Database Engine, Reporting Services, Analysis Services, Integration Services, and Notification Services Harden a SQL Server implementation Implement SQL Server highavailability alternatives, such as Failover Clustering, Log Shipping, Database Mirroring, and Replication Monitor a SQL Server 2005 infrastructure with Operations Manager 2007, including how to configure the SQL Server Management Pack and install Operations Manager 2007 Automate SQL Server routine maintenance Encrypt SQL Server data and communications, including setting up a Certificate Authority Performance tune and troubleshoot a SQL Server environment Create Integration Services packages and transfer data On the Web: Download bonus chapters from www.informit.com/title/9780672329562 Introduction 1 Part I Administering SQL Server Components 1 Administering SQL Server 2005 Database Engine 11 2 Administering SQL Server 2005 Analysis Services 67 3 Administering SQL Server 2005 Reporting Services 99 4 Administering SQL Server 2005 Notification Services 149 5 Administering SQL Server 2005 Integration Services 183 6 Administering SQL Server Replication 209 Part II Managing SQL Server 2005 7 Conducting a SQL Server 2005 Health Check 261 8 SQL Server 2005 Maintenance Practices 289 9 Managing and Optimizing SQL Server 2005 Indexes 317 10 Managing Full-Text Catalogs 353 11 Creating Packages and Transferring Data 387 Part III Securing the SQL Server Implementation 12 Hardening a SQL Server 2005 Environment 435 13 Administering SQL Server Security 469 14 Encrypting SQL Server Data and Communications 509 The following chapters are located online: Part IV SQL Server 2005 Overview (located online) 15 SQL Server 2005 Technology Primer 537 16 Tools of the Trade 571 Part V Disaster Recovery and High Availability (located online) 17 Backing Up and Restoring the SQL Server 2005 Environment 597 18 Administering and Managing Failover Clustering 647 19 Administering and Managing Database Mirroring 691 20 Administering and Managing Log Shipping 721 Part VI Monitoring and Troubleshooting SQL Server (located online) 21 Monitoring SQL Server 2005 753 22 Performance Tuning and Troubleshooting SQL Server 2005 787 Appendix SQL Server 2005 Management and Administration 821 __Microsoft SQL Server 2005 Management and Administration__, based on Service Pack 2, addresses the challenges database administrators regularly encounter on SQL Server 2005 by providing detailed guidance in the areas of management, administration, security, and monitoring. With coverage of the new features and functionality of SQL Server 2005 Service Pack 2, this book is designed to be comprehensive, resulting in something for all database administrators—from simple tips to tactical solutions. __Microsoft SQL Server 2005 Management and Administration__ goes far beyond the basic installation and setup information found in many other resources. The book looks at day-to-day administration, best practices, tips, and step-by-step configurations based on real-world examples found in the industry. Unlike others, this book includes not only administration and management details on the Database Engine, but also coverage of other SQL Server 2005 components often overlooked, including Analysis Services, Reporting Services, and more. Understand how to... * Configure and tune the Database Engine, Reporting Services, Analysis Services, Integration Services, and Notification Services * Harden a SQL Server implementation * Implement SQL Server highavailability alternatives, such as Failover Clustering, Log Shipping, Database Mirroring, and Replication * Monitor a SQL Server 2005 infrastructure with Operations Manager 2007, including how to configure the SQL Server Management Pack and install Operations Manager 2007 * Automate SQL Server routine maintenance * Encrypt SQL Server data and communications, including setting up a Certificate Authority * Performance tune and troubleshoot a SQL Server environment * Create Integration Services packages and transfer data On the Web: Download bonus chapters from www.informit.com/title/9780672329562 Introduction 1 Part I Administering SQL Server Components 1 Administering SQL Server 2005 Database Engine 11 2 Administering SQL Server 2005 Analysis Services 67 3 Administering SQL Server 2005 Reporting Services 99 4 Administering SQL Server 2005 Notification Services 149 5 Administering SQL Server 2005 Integration Services 183 6 Administering SQL Server Replication 209 Part II Managing SQL Server 2005 7 Conducting a SQL Server 2005 Health Check 261 8 SQL Server 2005 Maintenance Practices 289 9 Managing and Optimizing SQL Server 2005 Indexes 317 10 Managing Full-Text Catalogs 353 11 Creating Packages and Transferring Data 387 Part III Securing the SQL Server Implementation 12 Hardening a SQL Server 2005 Environment 435 13 Administering SQL Server Security 469 14 Encrypting SQL Server Data and Communications 509 The following chapters are located online: Part IV SQL Server 2005 Overview (located online) 15 SQL Server 2005 Technology Primer 537 16 Tools of the Trade 571 Part V Disaster Recovery and High Availability (located online) 17 Backing Up and Restoring the SQL Server 2005 Environment 597 18 Administering and Managing Failover Clustering 647 19 Administering and Managing Database Mirroring 691 20 Administering and Managing Log Shipping 721 Part VI Monitoring and Troubleshooting SQL Server (located online) 21 Monitoring SQL Server 2005 753 22 Performance Tuning and Troubleshooting SQL Server 2005 787 Appendix SQL Server 2005 Management and Administration 821