Microsoft SQL Server 2008 : management and administration

If you need to deploy, manage, or secure Microsoft SQL Server 2008, this is the complete, fast-paced, task-based reference you’ve been searching for. Authored by a world-class expert on SQL Server in the enterprise, this book goes far beyond the basics, taking on the complex tasks that DBAs need to make the most of Microsoft’s flagship database platform. SQL Server MVP, Ross Mistry presents proven techniques for SQL Server 2008 installation, upgrades, backup/restore, data transfer, indexing, high availability, security, and much more. He draws on extensive testing in high-profile production environments to offer step-by-step solutions and powerful tips you won’t find anywhere else. Every chapter begins with a section identifying SQL Server 2008’s most significant new improvements, and concludes with a convenient summary of best practices. Each chapter also outlines the benefits of leveraging Windows Server 2008. Understand how to: Master DBA tips, tricks, and best practices proven in actual enterprise environments Install, upgrade or transition to SQL Server 2008. Harden and Secure an implementation. Encrypt SQL Server from an end-to-end perspective. Implement high availability–and leverage SQL Server 2008’s major improvements to failover clustering and database mirroring Save time with SQL Server 2008’s new policy-based management tools Performance tune and troubleshoot a SQL Server 2008 environment. Optimize application performance and manage workloads with the powerful new Resource Governor Implement Performance Studio, maintenance plans, Transparent Data Encryption and much more...Bonus Content: The book is based on Windows Server 2008 Step by step instructions of how to implement a failover cluster on Windows Server 2008 SQL Server PoweShell Administration Tasks Consolidate and virtualize SQL Server with Hyper-V Step by step instructions on how to install Hyper-V Proactively Monitor SQL Server with Operations Manager Install Windows Server 2008 certificates to encrypt SQL Server dataContributing Writers include: Hilary Cotter — SQL Server MVP John Welch — SQL Server MVP Marco Shaw — PowerShell MVP Maciej Pilecki — SQL Server MVP Shirmattie Seenarine — Technical Writer Table of Contents......Page 6 Introduction......Page 32 Part I: Installing, Administering, and Managing the Database Engine......Page 40 1 Installing or Upgrading to the SQL Server 2008 Database Engine......Page 42 What’s New for Installation with SQL Server 2008?......Page 43 Preplanning and Preparing a SQL Server 2008 Server Database Engine Installation......Page 45 Gathering Additional Information Necessary to Proceed......Page 54 Installing a Clean Version of SQL Server 2008......Page 59 Upgrading the Database Engine to SQL Server 2008......Page 65 Finalizing the SQL Server 2008 Installation or Upgrade......Page 75 Managing SQL Server 2008 Installations......Page 77 Best Practices......Page 79 What’s New for DBAs When Administering the Database Engine on SQL Server 2008......Page 82 Administering SQL Server 2008 Server Properties......Page 84 Administering the SQL Server Database Engine Folders......Page 101 Administering Database Properties......Page 110 SQL Server Database Engine Management Tasks......Page 125 Administering the SQL Server Agent......Page 138 Summary......Page 142 Best Practices......Page 143 3 Creating Packages and Transferring Data with Integration Services......Page 144 What’s New in Integration Services for SQL Server 2008......Page 145 Options for Creating Packages......Page 146 Packages......Page 147 Developing Packages......Page 154 Enhancing Packages......Page 165 Deploying and Running Integration Services Packages......Page 178 Transferring Data with Integration Services......Page 187 Best Practices......Page 190 What’s New for Indexes with SQL Server 2008......Page 192 The Importance of Indexes......Page 193 Index Design and Strategy......Page 201 Administering Indexes......Page 205 Configuring Indexes for Maximum Performance......Page 220 Best Practices......Page 231 5 Administering SQL Server 2008 Full-Text Search......Page 234 New Features of SQL 2008 Full-Text Search......Page 235 Creating a Full-Text Catalog Using the Wizard......Page 248 Creating a Full-Text Index Using the Full-Text Wizard......Page 250 Using Full-Text Search......Page 270 Troubleshooting Full-Text Indexing......Page 272 Best Practices......Page 275 Summary......Page 276 What’s New for Maintenance with SQL Server 2008?......Page 278 Establishing a SQL Server Maintenance Plan......Page 279 Creating a Maintenance Plan......Page 288 Establishing Maintenance Schedules for SQL Server......Page 300 Summary......Page 302 Best Practices......Page 303 What’s New for Backup and Recovery with SQL Server 2008?......Page 304 The Importance of Backups......Page 306 Backing Up and Recovering the Database Engine......Page 308 Backing Up and Recovering Full-Text Catalogs......Page 338 Understanding and Creating Database Snapshots......Page 339 Summary......Page 341 Best Practices......Page 342 Part II: SQL Server 2008 Security Practices......Page 344 What’s New for Hardening a SQL Server 2008 Implementation?......Page 346 Windows and SQL Server Authentication......Page 347 Hardening SQL Server Service Accounts......Page 362 Installing Service Packs and Critical Fixes......Page 366 Understanding How SQL Server Security Logs Play a Part in Security......Page 367 Additional SQL Server Hardening Recommendations......Page 369 Best Practices......Page 377 What’s New for Security and Authorization with SQL Server 2008?......Page 380 SQL Server Security......Page 381 Security Management DDL......Page 393 Administering SQL Server Security......Page 398 Best Practices......Page 412 Introduction to Policy Based Management......Page 414 Policy Based Management Concepts......Page 416 Implementing Policy Based Management......Page 424 Sample Templates and Real-World Examples......Page 432 Summary......Page 434 Best Practices......Page 435 11 Encrypting SQL Server Data and Communications......Page 436 Encryption in SQL......Page 437 Column Encryption......Page 438 Securing the Data Storage......Page 441 Extensible Key Management......Page 449 Transparent Data Encryption......Page 452 Securing Connections......Page 457 Using Certificates......Page 460 SQL Server and BitLocker Drive Encryption......Page 468 Configuring BitLocker Drive Encryption on a SQL Server System......Page 471 Best Practices......Page 481 Part III: SQL Server 2008 High Availability Alternatives......Page 482 SQL Server 2008 Failover Clustering Overview......Page 484 What’s New for SQL Server 2008 Failover Clustering When Running on Windows Server 2008?......Page 485 Additional Elements of Failover Clustering......Page 493 Implementing a Single-Instance SQL Server 2008 Failover Cluster......Page 500 Implement a Multiple-Instance SQL Server 2008 Failover Cluster......Page 518 Managing Failover Clusters from a SQL Server 2008 Perspective......Page 522 Managing Failover Clusters from a Windows Server 2008 Perspective......Page 526 Best Practices......Page 530 13 Administering and Managing Database Mirroring......Page 532 What’s New for Database Mirroring with SQL Server 2008?......Page 533 SQL Server 2008 Database Mirroring Overview......Page 534 Combining Database Mirroring with Other SQL Server 2008 Technologies......Page 541 Administering a Database Mirroring Session......Page 543 Managing a Database Mirroring Session......Page 551 Monitoring and Troubleshooting a Database Mirroring Session......Page 556 Best Practices......Page 560 SQL Server 2008 Log Shipping Overview......Page 562 Administering Log Shipping......Page 569 Managing Log Shipping......Page 580 Monitoring and Troubleshooting Log Shipping......Page 586 Best Practices......Page 589 15 Implementing and Administering SQL Server Replication......Page 592 SQL Server 2008 Replication Essentials......Page 593 Administering SQL Server Replication......Page 609 Monitoring and Troubleshooting SQL Server Replication......Page 630 Best Practices......Page 632 Part IV: Monitoring and Troubleshooting SQL Server 2008......Page 634 16 Managing Workloads and Consumption with Resource Governor......Page 636 Resource Governor Concepts......Page 637 Resource Governor Scenarios......Page 639 Enabling Resource Governor......Page 640 Creating a Classifier function......Page 641 Creating a Resource Pool......Page 644 Creating a Workload Group......Page 646 Managing Resource Governor......Page 649 Monitoring Resource Governor......Page 650 Best Practices......Page 656 17 Monitoring SQL Server 2008 with Native Tools......Page 658 Gaining Quick Insight into a SQL Server System......Page 659 Monitoring Events with SQL Server Audit......Page 671 Data Collection with Performance Studio......Page 684 Creating Operators and Sending Email Alerts......Page 697 Using the Windows Server 2008 Performance and Reliability Monitoring Tools......Page 701 Additional Tools to Monitor SQL Server 2008......Page 709 Best Practices......Page 710 18 Proactively Monitoring SQL Server 2008 with System Center Operations Manager 2007......Page 712 Overview of System Center Operations Manager......Page 713 Outlining OpsMgr Architecture......Page 715 Understanding How to Use OpsMgr......Page 718 Understanding OpsMgr Component Requirements......Page 721 Understanding Advanced OpsMgr Concepts......Page 723 Securing OpsMgr......Page 727 Exploring the SQL Server Management Pack......Page 729 Downloading and Extracting the SQL Server 2008 Management Pack......Page 732 Summary......Page 736 Best Practices......Page 737 19 Performance Tuning and Troubleshooting SQL Server 2008......Page 738 Platform Troubleshooting and Optimization......Page 740 Database Troubleshooting and Optimization......Page 749 Application Optimization and Troubleshooting......Page 762 Introducing Extended Events to Troubleshoot SQL Server......Page 767 Best Practices......Page 772 Part V: SQL Server 2008 Bonus Content......Page 774 Overview of PowerShell......Page 776 PowerShell Scripting Basics......Page 785 PowerShell in SQL Server 2008......Page 800 Step-By-Step Examples......Page 806 Best Practices......Page 820 21 Consolidating and Virtualizing SQL Server 2008 with Hyper-V......Page 822 Understanding Microsoft’s Virtualization Strategy......Page 823 Planning Your Implementation of Hyper-V......Page 826 Installation of the Microsoft Hyper-V Server Role......Page 830 Becoming Familiar with the Hyper-V Administrative Console......Page 832 Installing a SQL Server 2008 Guest Operating System Session......Page 838 Modifying SQL Server 2008 Guest Session Configuration Settings......Page 842 Launching a Hyper-V Guest Session......Page 845 Using Snapshots of Guest Operating System Sessions......Page 847 Best Practices......Page 850 A......Page 852 B......Page 854 C......Page 856 D......Page 861 E......Page 869 F......Page 871 G......Page 874 H......Page 875 I......Page 876 L......Page 879 M......Page 881 N......Page 884 O......Page 885 P......Page 887 R......Page 892 S......Page 896 T......Page 905 U......Page 908 V......Page 909 W......Page 910 X......Page 911

If you need to deploy, manage, or secure Microsoft SQL Server 2008, this is the complete, fast-paced, task-based reference you’ve been searching for. Authored by a world-class expert on SQL Server in the enterprise, this book goes far beyond the basics, taking on the complex tasks that DBAs need to make the most of Microsoft’s flagship database platform. SQL Server MVP, Ross Mistry presents proven techniques for SQL Server 2008 installation, upgrades, backup/restore, data transfer, indexing, high availability, security, and much more. He draws on extensive testing in high-profile production environments to offer step-by-step solutions and powerful tips you won’t find anywhere else. Every chapter begins with a section identifying SQL Server 2008’s most significant new improvements, and concludes with a convenient summary of best practices. Each chapter also outlines the benefits of leveraging Windows Server 2008.

 Understand how to:

•    Master DBA tips, tricks, and best practices proven in actual enterprise environments

•    Install, upgrade or transition to SQL Server 2008.

•    Harden and Secure an implementation.

•    Encrypt SQL Server from an end-to-end perspective.

•    Implement high availability–and leverage SQL Server 2008’s major improvements to failover clustering and database mirroring

•    Save time with SQL Server 2008’s new policy-based management tools

•    Performance tune and troubleshoot a SQL Server 2008 environment.

•    Optimize application performance and manageworkloads with the powerful new Resource Governor

•    Implement Performance Studio, maintenance plans, Transparent Data Encryption and much more…

Bonus Content:

•    The book is based on Windows Server 2008

•    Step by step instructions of how to implement a failover cluster on Windows Server 2008

•    SQL Server PoweShell Administration Tasks

•    Consolidate and virtualize SQL Server with Hyper-V

•    Step by step instructions on how to install Hyper-V

•    Proactively Monitor SQL Server with Operations Manager

•    Install Windows Server 2008 certificates to encrypt SQL Server data

Contributing Writers include:

Hilary Cotter — SQL Server MVP

John Welch — SQL Server MVP

Marco Shaw — PowerShell MVP

Maciej Pilecki — SQL Server MVP

Shirmattie Seenarine — Technical Writer

If you need to deploy, manage, or secure Microsoft SQL Server 2008, this is the complete, fast-paced, task-based reference you've been searching for. Authored by a world-class expert on SQL Server in the enterprise, this book goes far beyond the basics, taking on the complex tasks that DBAs need to make the most of Microsoft's flagship database platform. SQL Server MVP, Ross Mistry presents proven techniques for SQL Server 2008 installation, upgrades, backup/restore, data transfer, indexing, high availability, security, and much more. He draws on extensive testing in high-profile production environments to offer step-by-step solutions and powerful tips you won't find anywhere else. Every chapter begins with a section identifying SQL Server 2008's most significant new improvements, and concludes with a convenient summary of best practices. Each chapter also outlines the benefits of leveraging Windows Server 2008. Understand how to: Master DBA tips, tricks, and best practices proven in actual enterprise environments Install, upgrade or transition to SQL Server 2008. Harden and Secure an implementation. Encrypt SQL Server from an end-to-end perspective. Implement high availability–and leverage SQL Server 2008's major improvements to failover clustering and database mirroring Save time with SQL Server 2008's new policy-based management tools Performance tune and troubleshoot a SQL Server 2008 environment. Optimize application performance and manage workloads with the powerful new Resource Governor Implement Performance Studio, maintenance plans, Transparent Data Encryption and much more... Bonus Content: The book is based on Windows Server 2008 Step by step instructions of how to implement a failover cluster on Windows Server 2008 SQL Server PoweShell Administration Tasks Consolidate and virtualize SQL Server with Hyper-V Step by step instructions on how to install Hyper-V Proactively Monitor SQL Server with Operations Manager Install Windows Server 2008 certificates to encrypt SQL Server data Contributing Writers include: Hilary Cotter — SQL Server MVP John Welch — SQL Server MVP Marco Shaw — PowerShell MVP Maciej Pilecki — SQL Server MVP Shirmattie See