Transcript 1 Chapter Overview Performing Configuration Tasks Setting Up

Chapter Overview
•
Performing Configuration Tasks
•
Setting Up Additional Features
• Performing Maintenance Tasks
1
Windows 2000
Configuration
•
Maximizing throughput for network operations
•
Optimizing performance for background
operations
•
Configuring virtual memory
2
SQL Server Service
Configuration
•
•
•
•
Generally self-tuning and self-regulating.
•
Processor settings can improve multiprocessor
performance.
•
Server settings can modify default server behavior.
Connection limits can be set to limit load.
Default database properties can be configured.
Memory settings can be configured for running
multiple applications on a server or for a dedicated
server.
3
Active Directory Integration
•
Registering a SQL Server instance.
•
Registering databases and publications.
• Using the MSSQLServerADHelper service.
•
SQL Server service account must be either a
local administrator or a member of the Power
Users group.
4
Additional Permissions for
Additional Functionality
•
Running operating system commands for
nonmembers of the sysadmin server role
•
Publishing and registering objects with the Active
Directory database
•
Creating operating system and ActiveX Script jobs
for nonmembers of the sysadmin server role
•
Restarting SQL Server Agent service automatically if
it stops unexpectedly
•
Running SQL Server Agent jobs automatically when
processor is idle
5
SQL Agent Service
Configuration
•
Error log properties
•
Enabling the auto-restart feature
• Configuring a centralized reporting server
•
Defining when a processor is considered idle
•
Modifying the SQL Server Agent security context
6
Registering Instances with
Enterprise Manager
•
Registration required to manage with SQL Server
Enterprise Manager.
•
•
Local instances are registered automatically.
•
Registration properties include authentication mode,
login information, display of system databases and
autostart upon connection.
•
Registration information can be shared or private.
Registered directly or by using Register SQL Server
wizard.
7
SQL Mail
8
SQLAgentMail
9
Configuring a Messaging
(Mail) Profile
•
SQL Mail requires a mail profile for the SQL Server
service domain user account.
•
SQLAgentMail requires a mail profile for the SQL
Server Agent service domain user account (same
profile if same account).
•
•
Log in as the domain user to create the mail profile.
Use the Mail program in Control Panel to manage the
mail profile.
10
Setting Up SQL Mail
11
Setting Up SQLAgentMail
12
Linked Servers
13
Configuring Linked Servers
•
Define the linked server.
•
Define user login mappings between local server
and linked server.
14
ODBC Data Source Name
(DSN)
•
Contains connection and driver information
required to connect to SQL Server using ODBC
• Create using the ODBC Data Source
Administrator
•
Types include user, system, and file
15
XML Support in SQL Server
2000
16
Distribution Statistics
•
Describe the selectivity and distribution of key values
in an index and on selected nonindexed columns.
•
Used by the query optimizer to determine efficient
execution plans.
•
•
Default is to autocreate and autoupdate statistics.
•
Use the Database Maintenance Plan wizard to
schedule.
Manual updates after significant updates or inserts
can be useful.
17
Indexes
•
Page splits affect performance and cause
fragmentation.
• Use fill factor to leave free space on index pages
to minimize page splits.
•
Rebuild indexes periodically to reestablish free
space on each index page.
•
Schedule index rebuilds for nonbusy periods
using the Database Maintenance Plan wizard.
18
Full-Text Indexes
•
Indexes of character data from tables stored in the
file system.
•
•
•
Implemented through the Microsoft Search Service.
•
•
Full or incremental update options are available.
A database must be enabled to use full-text indexes.
Full-text indexes can be kept current as a
background task or scheduled task.
Server-wide resource use by full-text indexes
performing background updates can be limited.
19
Chapter Summary
•
•
•
•
SQL Server generally manages resources dynamically.
•
Enabling linked servers and XML access require
additional configuration.
•
Distribution statistics, indexes, and full-text indexes
must be kept current.
Additional permissions required for advanced functions.
Register instances for remote administration.
SQL Mail and SQLAgentMail require a MAPI client and a
configured mail profile.
20