Microsoft SQL Server 2008 Management And Administration

DESCRIPTION

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 availabilityand leverage SQL Server 2008's majorimprovements 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 Table of Contents Introduction 1 Part I: Installing, Administering, and Managing the Database Engine 1 Installing or Upgrading to the SQL Server 2008 Database Engine 11 What's New for Installation with SQL Server 2008?. . . . . . . . . . . . . . . . . .12 Deprecated SQL Server 2008 Database Engine Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13 Preplanning and Preparing a SQL Server 2008 Server Database Engine Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14 Verifying Minimum Hardware Requirements. . . . . . . . . . . . . . . . . . . . .14 Examining SQL Server 2008 Software Prerequisites. . . . . . . .16 Choosing the Appropriate SQL Server Edition. . . . . . . . . . . . . . . . . .17 Choosing the Appropriate Windows Operating System Version and Edition to Support the SQL Server Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18 New Installation, Upgrade, or Transition? . . . . . . . . . . . . . . . . . . . . . . . .21 Gathering Additional Information Necessary to Proceed . . . . . . . . . . . .23 New SQL Server 2008 Installation or In-place Upgrade. . . .24 New SQL Server 2008 Stand-alone Installation or Failover Cluster. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24 Single-Instance or Multiple-Instance Installation . . . . . . . . . . . . . .24 Side-by-Side Installations with Previous Versions of SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25 Determine Which SQL Server 2008 Features to Install . . . .25 Installing a Clean Version of SQL Server 2008 . . . . . . . . . . . . . . . . . . . . . . . .28 Upgrading the Database Engine to SQL Server 2008 . . . . . . . . . . . . . . .34 Creating a SQL Server Feature Discovery Report . . . . . . . . . . . . .35 Backing Up the Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35 Verifying System Compatibility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35 Running the SQL Server Upgrade Advisor . . . . . . . . . . . . . . . . . . . . . . .35 Additional Considerations Before Upgrading the Database Engine to SQL Server 2008. . . . . . . . . . . . . . . . . . . . . . . . . .39 Performing the SQL Server 2008 Upgrade . . . . . . . . . . . . . . . . . . . . . .40 Finalizing the SQL Server 2008 Installation or Upgrade . . . . . . . . . . . .44 Reviewing SQL Server 2008 Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44 Downloading and Installing Updates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44 Hardening the SQL Server Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . .44 Items to Consider After an Upgrade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45 Managing SQL Server 2008 Installations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46 Employing Additional SQL Server 2008 Instances . . . . . . . . . . .46 Adding Features to an Existing SQL Server 2008 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47 Changing SQL Server 2008 Editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48 Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48 2 Administering the SQL Server 2008 Database Engine 51 What's New for DBAs When Administering the Database Engine on SQL Server 2008. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51 Administering SQL Server 2008 Server Properties . . . . . . . . . . . . . . . . . . .53 Administering the General Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54 Administering the Memory Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55 Administering the Processors Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57 Administering the Security Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60 Administering the Connections Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62 Administering the Database Settings Page . . . . . . . . . . . . . . . . . . . . .64 Administering the Advanced Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66 Administering the Permissions Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69 Administering the SQL Server Database Engine Folders. . . . . . . . . . . .70 Administering the Databases Folder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71 Administering the Security Folder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73 Administering the Server Objects Folder . . . . . . . . . . . . . . . . . . . . . . . . .74 Administering the Replication Folder. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .77 Administering the Management Folder. . . . . . . . . . . . . . . . . . . . . . . . . . . .77 Administering Database Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .79 Administering the Database Properties General Page . . . . . .80 Administering the Database Properties Files Page. . . . . . . . . . .81 Administering the Database Properties Filegroups Page . .85 Administering the Database Properties Options Page . . . . . .86 Understanding and Effectively Using Recovery Models. . . . .87 vi MicrosoftSQL Server 2008 Management and Administration Compatibility Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .89 Other Options (Automatic) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .89 Other Options (Cursor) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90 Other Options (Miscellaneous) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90 Other Options (Recovery) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .92 Other Options (Service Broker). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .92 Other Options (State) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .92 Administering the Change Tracking Page. . . . . . . . . . . . . . . . . . . . . . . . .93 Administering the Database Properties Permissions Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .93 Administering the Database Properties Extended Permissions Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .94 Administering the Database Properties Mirroring Page. . . . .94 Administering the Database Properties Transaction Log Shipping Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .94 SQL Server Database Engine Management Tasks . . . . . . . . . . . . . . . . . . . .94 Changing SQL Server Configuration Settings. . . . . . . . . . . . . . . . . . .95 Managing Database Engine Informational Reports . . . . . . . . . .95 Detaching and Attaching Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97 Scripting Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99 Managing SQL Server with PowerShell. . . . . . . . . . . . . . . . . . . . . . . . . .100 Backing Up and Restoring the Database. . . . . . . . . . . . . . . . . . . . . . .100 Transferring SQL Server Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .100 Taking a SQL Server Database Offline . . . . . . . . . . . . . . . . . . . . . . . . . .101 Shrinking a Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101 Data Compression in SQL Server 2008. . . . . . . . . . . . . . . . . . . . . . . .102 Partitioning Tables and Indexes in SQL Server 2008 . . . . .104 Enabling FILESTREAM Data for a SQL Server Instance. . .106 Renaming a Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107 Administering the SQL Server Agent. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107 Administering the SQL Server Agent Properties . . . . . . . . . . . . .108 Administering SQL Server Agent Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . .109 Administering SQL Server Alerts and Operators . . . . . . . . . . . .111 Administering SQL Server Proxies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111 Administering SQL Server Error Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . .111 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111 Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112