Home Page Icon
Home Page
Table of Contents for
Microsoft® SQL Server™ 2005 Administrator’s Pocket Consultant
Close
Microsoft® SQL Server™ 2005 Administrator’s Pocket Consultant
by William R. Stanek
Microsoft® SQL Server™ 2005 Administrator's Pocket Consultant
Microsoft® SQL Server™ 2005 Administrator’s Pocket Consultant
Acknowledgments
Introduction
Who Is This Book For?
How Is This Book Organized?
Conventions Used in This Book
Support
Postal Mail:
E-mail:
I. Microsoft SQL Server 2005 Administration Fundamentals
1. Microsoft SQL Server 2005 Administration Overview
SQL Server 2005 and Your Hardware
Microsoft SQL Server 2005 Editions
SQL Server and Windows
Services for SQL Server
SQL Server Authentication
Service Accounts for SQL Server
Using the Graphical Administration Tools
Using the Command-line Tools
SQLCMD
BCP
Other Command-Line Tools
2. Deploying Microsoft SQL Server 2005
SQL Server Integration Roles
Using SQL Server Integration Services
Using SQL Server 2005 for Relational Data Warehousing
Using SQL Server 2005 for Multidimensional Databases and Data Mining
Using SQL Server 2005 for Managed Reporting
Planning for Your SQL Server 2005 Deployment
Building the Server System for Performance
Configuring the I/O Subsystem
Ensuring Availability and Scalability
Ensuring Connectivity and Data Access
Running and Modifying SQL Server Setup
Creating New Instances of SQL Server
Understanding SQL Server Instances
Installing a SQL Server Instance
Adding Components and Instances
Maintaining Installed Components
Uninstalling SQL Server
3. Managing the Surface Security, Access, and Network Configuration
Getting Started with the Configuration Tools
Using SQL Server 2005 Surface Area Configuration
Connecting to a Remote SQL Server Implementation
Managing the Services Configuration
Managing the Connections Configuration
Managing SQL Server Component Feature Access
Configuring SQL Server Services
Managing Service State and Start Mode
Setting the Startup Service Account
Configuring Service Dump Directories, Error Reporting, and Customer Feedback Reporting
Managing the Network and SQL Native Client Configuration
Configuring the Shared Memory Network Configuration
Configuring the Named Pipes Network Configuration
Configuring the TCP/IP Network Configuration
Configuring the Native Client Protocol Order
Configuring the Shared Memory Native Client Configuration
Configuring the TCP/IP Native Client Configuration
Configuring the Named Pipes Native Client Configuration
4. Configuring and Tuning Microsoft SQL Server
Accessing SQL Server Configuration Data
Working with the System Catalog and Catalog Views
Working with System Stored Procedures
Techniques for Managing SQL Server Configuration Options
Setting Configuration Options
Working with SET Options
Working with Server Options
Working with Database Options
Managing Database Compatibility
Configuring SQL Server with Stored Procedures
Using SQL Server Management Studio for Queries
Executing Queries and Changing Settings
Checking and Setting Configuration Parameters
Changing Settings with sp_dboption
II. Microsoft SQL Server 2005 Administration
5. Managing the Enterprise
Managing SQL Server Startup
Enabling or Preventing Automatic SQL Server Startup
Setting Database Engine Startup Parameters
Adding Startup Parameters
Removing Startup Parameters
Common Startup Parameters
Managing Services from the Command Line
Managing the SQL Server Command-Line Executable File
Using SQL Server Management Studio
Getting Started with SQL Server Management Studio
Connecting to a Specific Server Instance
Connecting to a Specific Database
Managing SQL Server Groups
Introducing SQL Server Groups
Creating a Server Group
Deleting a Server Group
Editing and Moving Server Groups
Adding SQL Servers to a Group
Managing Servers
Registering a Connected Server
Registering a New Server in Registered Servers View
Registering Previously Registered SQL Server 2000 Servers
Updating Registration for Local Servers
Copying Server Groups and Registration Details from One Computer to Another
Editing Registration Properties
Connecting to a Server
Disconnecting from a Server
Moving a Server to a New Group
Deleting a Server Registration
Starting, Stopping, and Configuring SQL Server Agent
Starting, Stopping, and Configuring the Microsoft Distributed Transaction Coordinator
Starting, Stopping, and Configuring the Microsoft Search Service
Working with Full-Text Search
Managing Full-Text Catalogs
Viewing Catalog Properties
Creating Catalogs
Enabling Indexing of Tables and Views
Editing Indexing of Tables and Views
Disabling and Removing Full-Text Indexing from Tables and Views
Populating Full-Text Catalogs
Populating Catalogs Manually for All Selected Tables and Views
Using Scheduled Jobs to Populate Catalogs for All Selected Tables and Views
Populating Catalogs Manually for a Specific Table or View
Using Scheduled Jobs to Populate Catalogs for a Specific Table or View
Rebuilding Current Catalogs
Cleaning Up Old Catalogs
Removing Catalogs
Managing Server Activity
Examining Process Information
Tracking Locks by Process ID and Object
Troubleshooting Deadlocks and Blocking Connections
Tracking Command Execution in SQL Server
Killing Server Processes
6. Configuring SQL Server with SQL Server Management Studio
Managing the Configuration with SQL Server Management Studio
Determining System and Server Information
Configuring Authentication and Auditing
Setting Authentication Mode
Setting Auditing Level
Tuning Memory Usage
Working with Dynamically Configured Memory
Using Fixed Memory
Enabling AWE Memory Support
Optimizing Memory for Indexing
Allocating Memory for Queries
Configuring Processors and Parallel Processing
Optimizing CPU Usage
Setting Parallel Processing
Configuring Threading, Priority, and Fibers
Configuring User and Remote Connections
Setting Maximum User Connections
Setting Default Connection Options
Configuring Remote Server Connections
Managing Server Settings
Setting Default Language for SQL Server
Allowing and Disallowing System Updates
Allowing and Disallowing Nested Triggers
Controlling Query Execution
Configuring Year 2000 Support
Managing Database Settings
Setting the Index Fill
Configuring Backup and Restore Time-Out Options
Configuring Backup and Restore Retention Options
Flushing Cache with Checkpoints
Adding and Removing Active Directory Information
Troubleshooting Configuration Problems
Recovering from a Bad Configuration
Changing Collation and Rebuilding the Master Database
7. Core Database Administration
Database Files and Logs
Database Administration Basics
Viewing Database Information in SQL Server Management Studio
Viewing Database Information Using T-SQL
Checking System and Sample Databases
Examining Database Objects
Creating Databases
Creating Databases in SQL Server Management Studio
Creating Databases Using T-SQL
Altering Databases and Their Options
Setting Database Options in SQL Server Management Studio
Modifying Databases Using ALTER DATABASE
Configuring Automatic Options
Controlling ANSI Compliance at the Database Level
Configuring Cursor Options
Controlling User Access and Database State
Setting Online, Offline, or Emergency Mode
Managing Cross-Database Chaining and External Access Options
Configuring Recovery, Logging, and Disk I/O Error-Checking Options
Viewing, Changing, and Overriding Database Options
Managing Database and Log Size
Configuring SQL Server to Automatically Manage File Size
Expanding Databases and Logs Manually
Compressing and Shrinking a Database Manually
Manipulating Databases
Renaming a Database
Dropping and Deleting a Database
Attaching and Detaching Databases
Detaching a Database
Attaching a Database with Multiple Files
Attaching a Database with Only Data Files
Tips and Techniques
Copying and Moving Databases
Moving Databases
Moving and Resizing tempdb
Creating Secondary Data and Log Files
Preventing Transaction Log Errors
Preventing a Filegroup Is Full Error
Creating a New Database Template
8. Managing SQL Server 2005 Security
Overview of SQL Server 2005 Security
Working with Security Principals and Securables
Understanding Permissions of Securables
Examining Permissions Granted to Securables
Examining Built-in Permissions
Examining Effective Permissions
SQL Server 2005 Authentication Modes
Windows Authentication
Mixed Security and SQL Server Logins
Special Purpose Logins and Users
Working with the Administrators Group
Working with the Administrator User Account
Working with the sa Login
Working with the NETWORK SERVICE and SYSTEM Logins
Working with the Guest User
Working with the dbo User
Working with the sys and INFORMATION_SCHEMA Users
Permissions
Object Permissions
Statement Permissions
Implied Permissions
Roles
Server Roles
Database Roles
Managing Server Logins
Viewing and Editing Existing Logins
Creating Logins
Editing Logins with T-SQL
Granting or Denying Server Access
Enabling, Disabling, and Unlocking Logins
Removing Logins
Changing Passwords
Configuring Server Roles
Assigning Roles by Login
Assigning Roles to Multiple Logins
Revoking Access Rights and Roles by Server Login
Controlling Database Access and Administration
Assigning Access and Roles by Login
Assigning Roles for Multiple Logins
Creating Standard Database Roles
Creating Application Database Roles
Removing Role Memberships for Database Users
Deleting User-Defined Roles
Transact-SQL Commands for Managing Access and Roles
Managing Database Permissions
Assigning Database Permissions for Statements
Object Permissions by Login
Object Permissions for Multiple Logins
III. Microsoft SQL Server 2005 Data Administration
9. Manipulating Schemas, Tables, Indexes, and Views
Working with Schemas
Creating Schemas
Modifying Schemas
Moving Objects to a New Schema
Dropping Schemas
Getting Started with Tables
Table Essentials
Understanding Data Pages
Understanding Extents
Understanding Table Partitions
Working with Tables
Creating Tables
Modifying Existing Tables
Viewing Table Row and Size Information
Displaying Table Properties and Permissions
Displaying Current Values in Tables
Copying Tables
Renaming and Deleting Tables
Adding and Removing Columns in a Table
Adding Columns
Modifying Columns
Removing Columns
Scripting Tables
Managing Table Values
Using Native Data Types
Using Fixed-Length, Variable-Length, and Max-Length Fields
Using User-Defined Data Types
Creating User-Defined Data Types
Managing User-Defined Data Types
Allowing and Disallowing Nulls
Using Default Values
Using Identities and Globally Unique Identifiers
Using Views
Working with Views
Creating Views
Modifying Views
Using Updatable Views
Managing Views
Creating and Managing Indexes
Understanding Indexes
Using Clustered Indexes
Using Nonclustered Indexes
Using XML Indexes
Determining Which Columns Should Be Indexed
Indexing Computed Columns and Views
Viewing Index Properties
Creating Indexes
Managing Indexes
Using the Database Engine Tuning Advisor
Column Constraints and Rules
Using Constraints
Setting Unique Constraints
Designating Primary Key Constraints
Using Foreign Key Constraints
Using Check Constraints
Using Not Null Constraints
Using Rules
10. Importing, Exporting, and Transforming Data
Working with Integration Services
Getting Started with Integration Services
Integration Services Tools
Integration Services and Data Providers
Integration Services Packages
Creating Packages with the SQL Server Import and Export Wizard
Stage 1: Source and Destination Configuration
.NET Framework Data Provider Connections
File-Based Data Connections
Server-Based Connections to Databases Other Than SQL Server
Server-Based Connections to SQL Server
Importing and Exporting Flat Files
Stage 2: Copy or Query
Specifying Tables and Views to Copy
Building a Query
Stage 3: Formatting and Transformation
Stage 4: Save and Execute
Understanding BCP
BCP Basics
BCP Syntax
BCP Permissions and Modes
Importing Data with BCP
Exporting Data with BCP
BCP Scripts
Using the BULK INSERT Command
11. Linked Servers and Distributed Transactions
Working with Linked Servers and Distributed Data
Using Distributed Queries
Using Distributed Transactions
Running the Distributed Transaction Coordinator Service
Managing Linked Servers
Adding Linked Servers
Configuring Security for Linked Servers
Setting Server Options for Remote and Linked Servers
Deleting Linked Servers
12. Implementing Snapshot, Merge, and Transactional Replication
An Overview of Replication
Replication Components
Replication Agents and Jobs
Replication Variants
Planning for Replication
Replication Models
Preliminary Replication Tasks
Preparing for Snapshot Replication
Preparing for Transactional Replication
Preparing for Merge Replication
Distributor Administration
Setting Up a New Distributor
Updating Distributors
Creating Distribution Databases
Enabling and Updating Publishers
Enabling Publication Databases
Deleting Distribution Databases
Disabling Publishing and Distribution
Creating and Managing Publications
Creating Publications
Snapshot and Transactional Publications
Merge Publications
Viewing and Updating Publications
Setting Publication Properties
Setting Agent Security and Process Accounts
Controlling Subscription Access to a Publication
Creating a Script for a Publication
Deleting a Publication
Subscribing to a Publication
Subscription Essentials
Creating Subscriptions
Viewing Subscription Properties
Updating, Maintaining, and Deleting Subscriptions
Validating Subscriptions
Reinitializing Subscriptions
IV. Microsoft SQL Server 2005 Optimization and Maintenance
13. Profiling and Monitoring Microsoft SQL Server 2005
Monitoring Server Performance and Activity
Reasons to Monitor SQL Server
Getting Ready to Monitor
Monitoring Tools and Resources
Working with Replication Monitor
Starting and Using the Replication Monitor
Adding Publishers and Publisher Groups
Working with the Event Logs
Examining the Application Log
Examining the SQL Server Event Logs
Examining the SQL Server Agent Event Logs
Monitoring SQL Server Performance
Choosing Counters to Monitor
Creating and Managing Performance Monitor Logs
Creating and Managing Performance Logging
Creating Counter Logs
Creating Trace Logs
Replaying Performance Logs
Configuring Alerts for Performance Counters
Solving Performance Problems with Profiler
Using Profiler
Creating New Traces
Working with Traces
Saving a Trace
Replaying a Trace
Requirements for Replaying Traces
Replaying Traces on a Different Server
Replaying and Analyzing a Trace
14. Backing Up and Recovering SQL Server 2005
Creating a Backup and Recovery Plan
Initial Backup and Recovery Planning
Planning for Mirroring and Mirrored Database Backups
Planning for Backups of Replicated Databases
Planning for Backups of Very Large Databases
Selecting Backup Devices and Media
Using Backup Strategies
Creating a Backup Device
Performing Backups
Creating Backups in SQL Server Management Studio
Creating a New Backup Set
Adding to an Existing Backup Set
Using Striped Backups with Multiple Devices
Creating a New Media Set
Adding to an Existing Media Set
Using Transact-SQL Backup
Performing Transaction Log Backups
Options and Commands That Invalidate Log Sequences
Log Truncation Options
Backing Up Full-Text Search Catalogs
Restoring a Database
Database Corruption and Problem Resolution
Restoring a Database from a Normal Backup
Restoring Files and Filegroups
Restoring a Database to a Different Location
Recovering Missing Data
Creating Standby Servers
Creating a Cold Standby
Creating a Warm Standby
Using Transact-SQL Restore Commands
Restoring Full-Text Catalogs
Restoring the Master Database
Mirroring Databases
Configuring Mirroring
Managing Mirroring
Monitoring Mirroring Status and Performance
15. Database Automation and Maintenance
Overview of Database Automation and Maintenance
Using Database Mail
Performing the Initial Database Mail Configuration
Managing Database Mail Profiles and Accounts
Viewing or Changing Database Mail System Parameters
Using SQL Server Agent
Accessing Alerts, Operators, and Jobs
Configuring the SQL Server Agent Service
Setting the SQL Server Agent Mail Profile
Using SQL Server Agent to Restart Services Automatically
Managing Alerts
Using Default Alerts
Creating Error Message Alerts
Handling Alert Responses
Deleting, Enabling, and Disabling Alerts
Managing Operators
Registering Operators
Deleting and Disabling Notification for Operators
Configuring a Fail-Safe Operator
Scheduling Jobs
Creating Jobs
Assigning or Changing Job Definitions
Setting Steps to Execute
Configuring Job Schedules
Handling Job Alerts
Handling Notification Messages
Managing Existing Jobs
Managing Job Categories
Working with Job Categories
Creating Job Categories
Updating Job Categories
Automating Routine Server-to-Server Administration Tasks
Copying Users, Tables, Views, and Other Objects from One Database to Another
Copying Alerts, Operators, and Scheduled Jobs from One Server to Another
Multiserver Administration
Event Forwarding
Multiserver Job Scheduling
Multiserver Scheduling Requirements
Configuring Master Servers
Configuring Target Servers
Database Maintenance
Database Maintenance Checklist
Using Maintenance Plans
Creating Maintenance Plans
Checking Maintenance Reports and History
Viewing, Editing, Running, and Deleting Maintenance Plans
Checking and Maintaining Database Integrity
Using DBCC CHECKDB
Using DBCC CHECKTABLE
Using DBCC CHECKALLOC
Using DBCC CHECKCATALOG
Using DBCC DBREINDEX
Managing Log Shipping
Log Shipping: How It Works
Preparing for Log Shipping
Upgrading SQL Server 2000 Log Shipping to SQL Server 2005 Log Shipping
Enabling Log Shipping on the Primary Database
Adding Log Shipping Secondaries
Changing the Transaction Log Backup Interval
Changing the Copy and Restore Intervals
Failing Over to a Secondary Database
Using Mirroring and Log Shipping
William R. Stanek
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Cover
Next
Next Chapter
Acknowledgments
Microsoft® SQL Server™ 2005 Administrator’s Pocket Consultant
William R. Stanek
Copyright © 2009 William Stanek (All)
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset