Home Page Icon
Home Page
Table of Contents for
About the Technical Reviewer
Close
About the Technical Reviewer
by Carmen Taglienti, Grant Fritchey, Robert E. Walters
Beginning SQL Server 2008 Administration
Copyright
About the Authors
About the Technical Reviewer
Acknowledgments
Introduction
Who This Book Is For
How This Book Is Structured
Getting the Most from This Book
Errata
Contacting the Authors
1. The Database Administration Profession
1.1. Why Do We Need a DBA? Access Runs Great!
1.2. What Exactly Does a DBA Do Anyway?
1.2.1. Database Maintenance
1.2.2. Security and Regulatory Compliance
1.2.3. Disaster Recovery
1.2.4. Design and Performance Improvement
1.2.5. Documentation
1.3. Salary Information
1.4. Words from Real-World DBAs
1.4.1. Grant Fritchey, Principal DBA
1.4.2. Roman Rehak, Principal Database Architect
1.5. Summary
2. Planning and Installing SQL Server 2008
2.1. Understanding the Editions
2.1.1. Demystifying 32-Bit and 64-Bit Architectures
2.1.2. Server Editions
2.1.2.1. Enterprise Edition (x86, x64, and IA64)
2.1.2.2. Standard Edition (x86 and x64)
2.1.2.3. Specialized Editions
2.1.2.4. Developer Edition (x86, x64, and IA64)
2.1.2.5. Workgroup (x86 and x64)
2.1.2.6. Web (x86 and x64)
2.1.2.7. Express (x86 and x64)
2.1.2.8. Compact (x86 Only)
2.1.2.9. Evaluation (x86, x64, and IA64)
2.1.3. SQL Server Terminology
2.2. Planning Your Installation
2.2.1. Assessing the Environment
2.2.2. Choosing the Right Edition
2.2.3. Validating Hardware and Software Requirements
2.3. Installing SQL Server Evaluation Edition
2.3.1. Step 1: Downloading SQL Server Evaluation Edition
2.3.2. Step 2: Understanding the SQL Server Installation Center
2.3.2.1. Planning Tab
2.3.2.2. Installation Tab
2.3.2.3. Maintenance Tab
2.3.2.4. Tools Tab
2.3.2.5. Resources Tab
2.3.2.6. Advanced Tab
2.3.2.7. Options Tab
2.3.3. Step 3: Installing an Instance of SQL Server
2.3.3.1. Feature Selection Page
2.3.3.2. Instance Configuration Page
2.3.3.3. Disk Space Requirements Page
2.3.3.4. Server Configuration Page
2.3.3.5. Database Engine Configuration Page
2.3.3.6. Error and Usage Reporting Page
2.3.3.7. Installation Rules Page
2.3.3.8. Ready to Install Page
2.4. Upgrading SQL Server
2.5. A Word from the SQL Server Setup Team
2.6. Summary
3. What's in the Toolbox?
3.1. SQL Server Management Studio
3.1.1. Connecting to SQL Server
3.1.2. Issuing Queries Using SSMS
3.1.2.1. Object Explorer
3.1.2.2. Writing the Query
3.1.2.3. Document Windows
3.1.2.4. Results Pane
3.1.3. Managing Multiple Servers
3.1.3.1. Registered Servers
3.1.3.2. Queries Against Multiple Servers
3.1.3.3. Policy-Based Management
3.1.4. Monitoring Server Activity
3.1.4.1. Activity Monitor
3.1.4.2. Performance Data Collector
3.1.4.3. Reports
3.2. Other Tools from the Start Menu
3.2.1. Analysis Services Folder
3.2.2. Configuration Tools Folder
3.2.2.1. SQL Server Installation Center
3.2.2.2. SQL Server Error and Usage Reporting
3.2.2.3. Reporting Services Configuration Manager
3.2.2.4. SQL Server Configuration Manager
3.2.3. Documentation and Tutorials Folder
3.2.3.1. Microsoft SQL Server Samples Overview
3.2.3.2. SQL Server Books Online
3.2.3.3. SQL Server Tutorials
3.2.4. Integration Services Folder
3.2.4.1. Data Profiler Viewer
3.2.4.2. Execute Package Utility
3.2.5. Performance Tools Folder
3.2.5.1. Database Engine Tuning Advisor
3.2.5.2. SQL Server Profiler
3.2.5.3. Import and Export Data Wizard
3.2.5.4. Business Intelligence Development Studio (BIDS)
3.3. Command-Line Tools
3.3.1. SQLCMD
3.3.1.1. Connecting to SQL Server
3.3.1.2. Passing Variables
3.3.2. PowerShell Provider for SQL Server
3.4. Summary
4. Creating Tables and Other Objects
4.1. Navigating the Object Explorer Tree
4.2. Scripting the Actions of an SSMS Dialog Box
4.3. Transact-SQL (T-SQL) Primer
4.3.1. Data Definition Language (DDL)
4.3.2. Data Manipulation Language (DML)
4.3.3. Data Control Language (DCL)
4.4. Creating Tables
4.4.1. Creating Tables from the Table Designer
4.4.2. Issuing the CREATE TABLE Statement
4.5. Altering Tables
4.6. Adding Constraints
4.6.1. NULL Constraints
4.6.2. CHECK Constraints
4.6.3. Primary Key and Unique Constraints
4.6.4. Foreign Key Constraints
4.7. Dropping Tables
4.8. Creating Indexes
4.9. Summary
5. Transact-SQL
5.1. The VetClinic Sample Database Revisited
5.2. Data Types
5.2.1. Unicode vs. ANSI
5.2.2. Living with NULL
5.3. Data Manipulation Language
5.3.1. SELECT
5.3.2. INSERT
5.3.3. UPDATE
5.3.4. DELETE
5.4. Transactions
5.4.1. Execution
5.4.2. Transaction Isolation
5.4.3. Deadlocks
5.5. Stored Procedures
5.5.1. Creating Stored Procedures Using Templates
5.5.2. Modifying Stored Procedures
5.5.3. System Stored Procedures
5.6. Functions
5.6.1. Creating a Function
5.6.2. Invoking a Function
5.6.2.1. System-Defined Functions
5.7. Triggers
5.8. Summary
6. SQL Server Internals
6.1. Databases
6.1.1. Master Database
6.1.2. Tempdb Database
6.1.3. Model Database
6.1.4. MSDB Database
6.1.5. Resource Database
6.2. Repairing Corrupt System Databases
6.3. Writing Data to Disk
6.4. SQL Server Services
6.5. Single-User Mode
6.5.1. Placing an Already-Started Database into Single-User Mode
6.5.2. Starting SQL Server in Single-User Mode
6.6. Summary
7. Storage Management Strategies
7.1. Storage Systems
7.2. Storage System Interfaces
7.2.1. IDE ATA
7.2.2. SATA
7.2.3. SCSI
7.2.4. Serial Attached SCSI
7.2.5. Fibre Channel Direct Attached Storage
7.2.6. Fibre Channel Storage Area Network
7.2.7. iSCSI
7.2.8. InfiniBand
7.3. Storage System Types
7.3.1. Direct Attached Storage
7.3.2. Storage Area Network
7.3.2.1. Logical Unit Numbers (LUNs)
7.3.3. Network Attached Storage
7.4. Disk Configuration: RAID
7.4.1. RAID 0 (Striping)
7.4.2. RAID 1 (Mirroring and Duplexing)
7.4.3. RAID 10 (Stripe of Mirrors)
7.4.4. RAID 5 (Striping with Parity)
7.5. Hardware and Software RAID
7.6. Selecting a Storage System for SQL Server 2008
7.6.1. I/O Performance
7.6.2. Redundancy Is Critical
7.7. Configuring Your Database
7.7.1. Data Compression
7.7.2. Disk Volume Alignment
7.8. Summary
8. Database Backup Strategies
8.1. Defining the Types of Backups
8.1.1. Full Backups
8.1.1.1. Full Backup Using SQL Server Management Studio
8.1.1.2. Full Backup Using T-SQL
8.1.2. Differential Backups
8.1.3. File and Filegroup Backups
8.2. Backing Up the Logs
8.2.1. Initiating Fully Recovery Mode
8.2.2. Log Backup Using Management Studio
8.2.3. Log Backup Using T-SQL
8.2.4. Backing Up a Copy of the Database
8.3. Automating the Backup Process
8.4. Encrypting Backup Files for Security
8.5. Compressing Backups to Save Space
8.6. Designing a Backup Plan
8.7. Summary
9. Database Restore Strategies
9.1. Restoring a Database from the Management Studio GUI
9.2. Specifying Restore Options from the GUI
9.3. Restoring a Database Using T-SQL
9.3.1. Executing a Simple Restore
9.3.2. Creating a Copy of a Database
9.3.3. Retrieving Logical and Physical File Names
9.3.4. Displaying General Information About a Backup
9.3.5. Cleaning Up
9.4. Restoring Differential Backups
9.5. Restoring Log Backups
9.6. Restoring File Backups
9.7. Testing Backups
9.8. Developing a Full Disaster Recovery Plan
9.8.1. Gathering Information About the Business
9.8.2. Establishing the Disaster Plan
9.8.2.1. Scenario 1: Large Commercial Insurance Company
9.8.2.2. Scenario 2: Small Online Retail Business
9.8.3. Testing the Disaster Plan
9.9. Summary
10. Common Database Maintenance Tasks
10.1. Backing Up and Restoring
10.2. Checking the Database Integrity
10.3. Shrinking the Database
10.4. Reorganizing and Rebuilding Indexes
10.4.1. Detecting Undue Fragmentation
10.4.2. Rebuilding an Index
10.4.3. Reorganizing an Index
10.5. Getting Updated Statistics
10.5.1. Checking the Status of Automatic Statistics Gathering
10.5.2. Manually Updating Statistics
10.6. Monitoring Logs
10.7. Creating SQL Server Agent Jobs
10.7.1. Connecting to SQL Server
10.7.2. Creating an Agent Job
10.8. Defining Alerts
10.9. Summary
11. Automation Through SQLCMD and PowerShell
11.1. Scripting in SQLCMD
11.2. Executing Commands Interactively
11.3. Executing Script in Batch Mode
11.4. Testing SQLCMD Scripts
11.5. Using SQLCMD to Back Up a Database
11.6. Generating Scripts from Management Studio
11.7. Scripting in PowerShell
11.7.1. Configuring PowerShell for Use With SQL Server
11.7.2. Understanding the Components
11.7.3. Starting PowerShell in Interactive Mode
11.7.4. Specifying an Execution Policy
11.7.5. Executing a PowerShell Script Interactively
11.7.6. Running PowerShell Scripts in Batch Mode
11.7.7. Running PowerShell from SQL Server Agent
11.7.8. Running PowerShell Scripts from Maintenance Plans
11.7.9. Using the PowerShell Integrated Scripting Environment
11.8. Summary
12. Database Maintenance Plans
12.1. Understanding the Fundamentals
12.2. Creating a Maintenance Plan
12.3. Starting the Maintenance Plan Wizard
12.3.1. Specifying Plan Properties
12.3.2. Specifying Job Schedule Properties
12.3.3. Selecting Maintenance Tasks
12.3.4. Selecting Maintenance Task Order
12.4. Configuring Individual Tasks
12.4.1. Check Database Integrity
12.4.2. Shrink Database
12.4.3. Reorganize Index
12.4.4. Rebuild Index
12.4.5. Update Statistics
12.4.6. History Cleanup
12.4.7. Back Up Database (Full)
12.4.8. Maintenance Cleanup
12.4.9. Select Report Options
12.5. Completing the Maintenance Plan Wizard
12.6. Modifying an Existing Maintenance Plan
12.7. Summary
13. Performance Tuning and Optimization
13.1. Measuring SQL Server Performance
13.1.1. Performance Counters
13.1.2. Performance Monitor
13.1.3. Dynamic Management Views
13.1.4. Data Collector
13.1.5. Setting Up the Data Collector
13.1.6. Viewing the Data Collector Data
13.1.6.1. Server Activity History
13.1.6.2. Disk Usage Summary
13.1.6.3. Query Statistics History
13.2. Tuning Queries
13.2.1. Understanding Execution Plans
13.2.2. Gathering Query Information with Profiler
13.2.2.1. Working with Trace Data
13.2.2.2. Using Trace to Capture Execution Plans
13.2.3. Using the Database Engine Tuning Advisor
13.3. Managing Resources
13.4. Limiting Resource Use
13.4.1. Leveraging Data Compression
13.5. Summary
14. SQL Server Security
14.1. Terminology
14.1.1. Authentication
14.1.2. Authentication Mode
14.1.3. Authorization
14.1.4. Server Instance vs. the Database
14.2. SQL Server Instance Security
14.2.1. Creating a SQL Server Login
14.2.2. Server Roles
14.2.3. Server Permissions
14.2.4. Endpoints
14.3. Database Security
14.3.1. Database Users
14.3.2. Schemas
14.3.2.1. An Example of the "Wrong" Way
14.3.2.2. The "Right" Way
14.3.2.3. Four-Part Naming Convention
14.3.2.4. Default Schema
14.3.2.5. Reassigning Schema Ownership
14.3.3. Fixed Database Roles
14.3.3.1. Database Permissions
14.3.4. Flexible Database Roles
14.3.5. Security Functions
14.3.5.1. fn_my_permissions() Function
14.3.5.2. HAS_PERMS_BY_NAME Function
14.4. Summary
15. Auditing, Encryption, and Compliance
15.1. Auditing in SQL Server 2008
15.1.1. Auditing Objects
15.1.2. Server Audit Object
15.1.3. Server Audit Specification Object
15.1.4. Database Audit Specification Object
15.2. Encryption
15.2.1. Encryption Primer
15.2.2. Password-Based Encryption
15.2.3. Certificate-Based Encryption
15.2.4. Transparent Data Encryption
15.3. Validating Server Configuration
15.3.1. The Need for a Policy
15.3.2. Create Policy on a Local Server
15.3.3. Evaluate the Policy
15.3.4. Using the Central Management Server
15.4. Summary
16. SQL Server in the Enterprise
16.1. Systems Center Operations Manager 2007
16.2. Installing the SQL Server SCOM Management Pack
16.3. Getting Familiar with the SQL Server Management Pack
16.4. Finding and Resolving an Issue
16.5. Using System Center Data Protection Manager
16.5.1. Setting Up a Protection Group
16.5.2. Executing DPM Reports
16.5.3. Recovering a Databases
16.6. Summary
17. Where to Go Next?
17.1. Visit SQL Server Books Online
17.2. Keep Up with Public SQL Server Websites
17.3. Attend a Conference
17.4. Find a Local Users Group
17.5. Create a Lab System and Practice
17.6. Get Certified!
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
About the Authors
Next
Next Chapter
Acknowledgments
About the Technical Reviewer
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