sql server 2005 management and administration

Ross Mistry, Chris Amaris, Alec Minty, Rand Morimoto0-672-32956-5, 978-0-672-32956-2

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

---

Table of contents :
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