9780735625891_SQLServer08AdminPocketConsult_ch05.pdf
(
3460 KB
)
Pobierz
Microsoft
®
SQL
Server
®
2008
Administrator's
Pocket
Consultant
William R. Stanek
To learn more about this book, visit Microsoft Learning at
http://www.microsoft.com/MSPress/books/12755.aspx
9780735625891
©
2009
William R. Stanek. All rights reserved.
Table of Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
Who Is This Book For? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
How Is This Book Organized? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xx
Conventions Used in This Book. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi
Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii
Part I
Microsoft SQL Server 2008 Administration
Fundamentals
1
Microsoft SQL Server 2008 Administration Overview . . . . . . . . . . . . 2
SQL Server 2008 and Your Hardware . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Microsoft SQL Server 2008 Editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
SQL Server and Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Services for SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
SQL Server Logins and Authentication . . . . . . . . . . . . . . . . . . . . . 10
Service Accounts for SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Using the Graphical Administration Tools . . . . . . . . . . . . . . . . . . . . . . . 12
Using the Command-Line Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
BCP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
SQLCMD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Other Command-Line Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Using the SQL Server PowerShell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Running and Using Cmdlets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Running and Using the SQL Server PowerShell . . . . . . . . . . . . . . 21
Working with SQL Server Cmdlets . . . . . . . . . . . . . . . . . . . . . . . . . 22
2
Deploying Microsoft SQL Server 2008 . . . . . . . . . . . . . . . . . . . . . . . . 24
SQL Server Integration Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Using SQL Server Integration Services. . . . . . . . . . . . . . . . . . . . . . 24
Using SQL Server 2008 for Relational Data
Warehousing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books
and learning resources for you. To participate in a brief survey, please visit:
www.microsoft.com/learning/booksurvey
iii
iv
Table of Contents
Using SQL Server 2008 for Multidimensional Databases
and Data Mining. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Using SQL Server 2008 for Managed Reporting . . . . . . . . . . . . . 29
Planning for Your SQL Server 2008 Deployment . . . . . . . . . . . . . . . . . 30
Building the Server System for Performance . . . . . . . . . . . . . . . . 30
Configuring the I/O Subsystem . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Ensuring Availability and Scalability . . . . . . . . . . . . . . . . . . . . . . . 33
Ensuring Connectivity and Data Access . . . . . . . . . . . . . . . . . . . . 35
Managing SQL Server Configuration and Security . . . . . . . . . . . 36
Running and Modifying SQL Server Setup . . . . . . . . . . . . . . . . . . . . . . 37
Creating New Instances of SQL Server . . . . . . . . . . . . . . . . . . . . . 38
Adding Components and Instances. . . . . . . . . . . . . . . . . . . . . . . . 49
Repairing a SQL Server 2008 Installation . . . . . . . . . . . . . . . . . . . 50
Upgrading Your Edition of SQL Server 2008 . . . . . . . . . . . . . . . . 50
Uninstalling SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
3
Managing the Surface Security, Access,
and Network Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Managing SQL Server Component Feature Access . . . . . . . . . . . . . . . 53
Configuring SQL Server Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Managing the Services Configuration. . . . . . . . . . . . . . . . . . . . . . 57
Managing Service State and Start Mode . . . . . . . . . . . . . . . . . . . 61
Setting the Startup Service Account . . . . . . . . . . . . . . . . . . . . . . . 62
Configuring File Streaming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Configuring Service Dump Directories, Error Reporting,
and Customer Feedback Reporting . . . . . . . . . . . . . . . . . . . . . . . . 67
Managing the Network and SQL Native Client Configuration . . . . . 68
Managing the Connections Configuration. . . . . . . . . . . . . . . . . . 69
Specifying the Shared Memory Network
Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Specifying the Named Pipes Network Configuration. . . . . . . . . 71
Specifying the TCP/IP Network Configuration. . . . . . . . . . . . . . . 72
Configuring Security for Native Client Configurations. . . . . . . . 75
Configuring the Native Client Protocol Order . . . . . . . . . . . . . . . 75
Configuring the Shared Memory Native Client
Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Configuring the Named Pipes Native Client
Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Configuring the TCP/IP Native Client Configuration . . . . . . . . . 77
Table of Contents
v
4
Configuring and Tuning Microsoft SQL Server 2008 . . . . . . . . . . . . 78
Accessing SQL Server Configuration Data . . . . . . . . . . . . . . . . . . . . . . . 79
Working with the System Catalog and Catalog Views . . . . . . . . 80
Working with System Stored Procedures . . . . . . . . . . . . . . . . . . . 85
Techniques for Managing SQL Server Configuration Options . . . . . . 91
Setting Configuration Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Working with SET Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Working with Server Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Working with Database Options. . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Managing Database Compatibility. . . . . . . . . . . . . . . . . . . . . . . . . 98
Configuring SQL Server with Stored Procedures . . . . . . . . . . . . . . . . . 99
Using SQL Server Management Studio for Queries. . . . . . . . . . . 99
Executing Queries and Changing Settings . . . . . . . . . . . . . . . . . 101
Checking and Setting Configuration Parameters. . . . . . . . . . . . 103
Changing Settings with ALTER DATABASE . . . . . . . . . . . . . . . . . 106
Part II Microsoft SQL Server 2008 Administration
5
Managing the Enterprise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Using SQL Server Management Studio . . . . . . . . . . . . . . . . . . . . . . . . 112
Getting Started with SQL Server Management Studio . . . . . . . 113
Connecting to a Specific Server Instance . . . . . . . . . . . . . . . . . . 114
Connecting to a Specific Database. . . . . . . . . . . . . . . . . . . . . . . . 115
Managing SQL Server Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Introducing SQL Server Groups . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Creating a Server Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Deleting a Server Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Editing and Moving Server Groups . . . . . . . . . . . . . . . . . . . . . . . 119
Adding SQL Servers to a Group . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Managing Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Registering a Connected Server . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Registering a New Server in the Registered
Servers View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Registering Previously Registered SQL Server 2000
Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Updating Registration for Local Servers . . . . . . . . . . . . . . . . . . . 122
Copying Server Groups and Registration Details
from One Computer to Another. . . . . . . . . . . . . . . . . . . . . . . . . . 123
Editing Registration Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Connecting to a Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Disconnecting from a Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
vi
Table of Contents
Moving a Server to a New Group . . . . . . . . . . . . . . . . . . . . . . . . 126
Deleting a Server Registration . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Starting, Stopping, and Configuring SQL Server Agent . . . . . . . . . . 126
Starting, Stopping, and Configuring Microsoft Distributed
Transaction Coordinator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Managing SQL Server Startup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Enabling or Preventing Automatic SQL Server Startup . . . . . . 128
Setting Database Engine Startup Parameters . . . . . . . . . . . . . . 129
Managing Services from the Command Line. . . . . . . . . . . . . . . 133
Managing the SQL Server Command-Line
Executable File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Managing Server Activity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Examining Process Information . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Tracking Resource Waits and Blocks . . . . . . . . . . . . . . . . . . . . . . 137
Troubleshooting Deadlocks and Blocking Connections . . . . . . 140
Tracking Command Execution in SQL Server . . . . . . . . . . . . . . . 142
Killing Server Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
6
Configuring Microsoft SQL Server with SQL Server
Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Managing the Configuration with SQL Server
Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Determining System and Server Information . . . . . . . . . . . . . . . . . . . 146
Configuring Authentication and Auditing . . . . . . . . . . . . . . . . . . . . . 147
Setting the Authentication Mode . . . . . . . . . . . . . . . . . . . . . . . . 148
Setting the Auditing Level. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Enabling or Disabling C2 Audit Logging . . . . . . . . . . . . . . . . . . 148
Enabling or Disabling Common Criteria Compliance . . . . . . . . 149
Tuning Memory Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Working with Dynamically Configured Memory. . . . . . . . . . . . 152
Using Fixed Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Enabling AWE Memory Support . . . . . . . . . . . . . . . . . . . . . . . . . 153
Optimizing Memory for Indexing . . . . . . . . . . . . . . . . . . . . . . . . 154
Allocating Memory for Queries . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Configuring Processors and Parallel Processing. . . . . . . . . . . . . . . . . 156
Optimizing CPU Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Setting Parallel Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Configuring Threading, Priority, and Fibers . . . . . . . . . . . . . . . . . . . . 159
Configuring User and Remote Connections . . . . . . . . . . . . . . . . . . . . 161
Setting Maximum User Connections . . . . . . . . . . . . . . . . . . . . . . 161
Setting Default Connection Options . . . . . . . . . . . . . . . . . . . . . . 162
Configuring Remote Server Connections . . . . . . . . . . . . . . . . . . 164
Plik z chomika:
red_dewil
Inne pliki z tego folderu:
9780735625891_SQLServer08AdminPocketConsult_ch05.pdf
(3460 KB)
9780735626041_SQL_Server_2008_ch03.pdf
(1149 KB)
Inne foldery tego chomika:
Code
eBook
Practice Tests
StartCD
Zgłoś jeśli
naruszono regulamin