Microsoft® SQL Server® 2008 For Dummies®

Microsoft® SQL Server® 2008 For Dummies®

by
Released September 2008
Publisher(s): For Dummies
ISBN: 9780470224656

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

If you’re a database administrator, you know Microsoft SQL Server 2008 is revolutionizing database development. Get up to speed on SQL Server 2008, impress your boss, and improve your company’s data management — read Microsoft SQL Server 2008 For Dummies!

SQL Server 2008 lets you build powerful databases and create database queries that give your organization the information it needs to excel. Microsoft SQL Server 2008 For Dummies helps you build the skills you need to set up, administer, and troubleshoot SQL Server 2008. You’ll be able to:

  • Develop and maintain a SQL Server system

  • Design databases with integrity and efficiency

  • Turn data into information with SQL Server Reporting Services

  • Organize query results, summarizing data with aggregate functions and formatting output

  • Import large quantities of data with SSIS

  • Keep your server running smoothly

  • Protect data from prying eyes

  • Develop and implement a disaster recovery plan

  • Improve performance with database snapshots

  • Automate SQL Server 2008 administration

Microsoft SQL Server 2008 For Dummies is a great first step toward becoming a SQL Server 2008 pro!

Table of contents

  1. Copyright
  2. About the Author
  3. Author's Acknowledgments
  4. Introduction
    1. About This Book
    2. Conventions Used in This Book
    3. What You Are Not to Read
    4. Foolish Assumptions
    5. How This Book Is Organized
    6. Icons Used in This Book
    7. Where to Go from Here
  5. I. Welcome to SQL Server 2008
    1. 1. Introducing SQL Server 2008
      1. 1.1. Starting Off on the Right Foot
        1. 1.1.1. Examining SQL Server editions
        2. 1.1.2. Checking system requirements
      2. 1.2. Understanding the Basic Components of SQL Server
        1. 1.2.1. SQL and Transact-SQL
        2. 1.2.2. SQL Server components
      3. 1.3. Implementing Databases with SQL Server 2008
        1. 1.3.1. Setting up your server
        2. 1.3.2. Stocking it with databases
        3. 1.3.3. Accessing and updating your data
        4. 1.3.4. Managing and protecting what you've built
      4. 1.4. What's New in SQL Server 2008
        1. 1.4.1. Declarative Management Framework
        2. 1.4.2. Encryption and Auditing
        3. 1.4.3. Resource conservation
        4. 1.4.4. Date/Time Data Types
    2. 2. Building Your SQL Server
      1. 2.1. Installing SQL Server 2008
        1. 2.1.1. Choosing between default and named instances
        2. 2.1.2. Selecting an authentication mode
        3. 2.1.3. Choosing service accounts
        4. 2.1.4. Selecting the collation
        5. 2.1.5. Performing the installation
      2. 2.2. Upgrading an Existing SQL Server Installation
        1. 2.2.1. Preparing for an upgrade with Upgrade Advisor
        2. 2.2.2. Upgrading Your Installation
      3. 2.3. Configuring Database Mail
      4. 2.4. Using SQL Server's Built-In Databases
        1. 2.4.1. Master database
        2. 2.4.2. The msdb database
        3. 2.4.3. The model database
        4. 2.4.4. The tempdb database
    3. 3. Working with SQL Server Tools
      1. 3.1. Using SQL Server Configuration Manager
        1. 3.1.1. Launching SQL Server Configuration Manager
        2. 3.1.2. Starting and stopping services
        3. 3.1.3. Changing service accounts
        4. 3.1.4. Changing service start modes
        5. 3.1.5. Modifying networking settings
      2. 3.2. Managing Your Server with SQL Server Management Studio
        1. 3.2.1. Starting SSMS and connecting to an instance
        2. 3.2.2. Exploring the SSMS Interface
        3. 3.2.3. Issuing Transact-SQL queries
      3. 3.3. Working from the Command Line
  6. II. Building SQL Server 2008 Databases
    1. 4. Planning Your Database
      1. 4.1. Introducing Database Design Concepts
      2. 4.2. Understanding the Elements of a Database
        1. 4.2.1. Database servers
      3. 4.3. Organizing a Database
        1. 4.3.1. Defining your database objectives
        2. 4.3.2. Grouping data into tables
        3. 4.3.3. Selecting primary keys
        4. 4.3.4. Linking related tables
      4. 4.4. Diagramming Your Database
      5. 4.5. Staying Fit and Trim with Normalization
        1. 4.5.1. First normal form
        2. 4.5.2. Second and third normal forms
      6. 4.6. Choosing Data Types for Your Tables
        1. 4.6.1. Numeric data types
        2. 4.6.2. Date and time data types
        3. 4.6.3. Character string data types
        4. 4.6.4. Binary data types
        5. 4.6.5. Other data types
      7. 4.7. Working with NULL Values
    2. 5. Creating Databases and Tables
      1. 5.1. Creating a Database
        1. 5.1.1. Altering database properties
        2. 5.1.2. Deleting or renaming a database
      2. 5.2. Specifying Files and Filegroups
        1. 5.2.1. SQL Server files
        2. 5.2.2. Using filegroups
      3. 5.3. Creating a Table
        1. 5.3.1. Getting started
        2. 5.3.2. Adding columns
        3. 5.3.3. Selecting a primary key
        4. 5.3.4. Modifying tables
        5. 5.3.5. Deleting tables
    3. 6. Imposing Constraints and Relationships
      1. 6.1. Introducing Constraints
      2. 6.2. Controlling Database Contents Using Constraints
        1. 6.2.1. Filling in empty values with DEFAULT constraints
      3. 6.3. Enforcing Database Integrity
        1. 6.3.1. Enforcing uniqueness
        2. 6.3.2. Enforcing referential integrity with FOREIGN KEY constraints
  7. III. Retrieving Data from Databases
    1. 7. Constructing Simple Database Queries
      1. 7.1. Retrieving Data with SELECT Statements
        1. 7.1.1. The SELECT...FROM clause
        2. 7.1.2. The WHERE clause
      2. 7.2. Organizing Query Results
        1. 7.2.1. Sorting output
        2. 7.2.2. Summarizing data with aggregate functions
        3. 7.2.3. Grouping results
        4. 7.2.4. Renaming columns in your output
    2. 8. Joins and Other Advanced Queries
      1. 8.1. Joining Data from Multiple Tables
        1. 8.1.1. Matching records with INNER JOINs
        2. 8.1.2. Including nonmatching records with OUTER JOINs
        3. 8.1.3. Joining a table with itself
      2. 8.2. Taking SELECT to the Next Level
        1. 8.2.1. Computing values
        2. 8.2.2. Managing complexity with subqueries
        3. 8.2.3. Dealing with different cases
      3. 8.3. Using Database Views
        1. 8.3.1. Creating a view
        2. 8.3.2. Modifying a view
        3. 8.3.3. Deleting a view
    3. 9. Turning Data into Information with SQL Server Reporting Services
      1. 9.1. Setting up SQL Server Reporting Services
      2. 9.2. Creating an SSRS Report with Report Builder
        1. 9.2.1. Installing and starting Report Builder 2.0
        2. 9.2.2. Choosing a data source and data set
        3. 9.2.3. Laying out the report
        4. 9.2.4. Publishing the report
      3. 9.3. Working with Deployed (Published) Reports
        1. 9.3.1. Viewing reports
        2. 9.3.2. Configuring report security
  8. IV. Inserting and Manipulating Your Data
    1. 10. Inserting, Updating, and Deleting Data
      1. 10.1. Inserting Small Quantities of Data
        1. 10.1.1. Understanding simple data entry
        2. 10.1.2. Writing INSERT statements
      2. 10.2. Modifying and Deleting Data
        1. 10.2.1. Modifying data with UPDATE
        2. 10.2.2. Removing data from a database
      3. 10.3. Importing Large Quantities of Data
        1. 10.3.1. Inserting query results
        2. 10.3.2. Copying bulk data with BULK INSERT
        3. 10.3.3. Performing blk operations from the command line with bcp
      4. 10.4. Working with SQL Server Integration Services
    2. 11. Saving Time with Functions, Stored Procedures, and Triggers
      1. 11.1. Reusing Logic with Functions
        1. 11.1.1. Understanding types of functions
      2. 11.2. Leveraging SQL Server's built-in functions
        1. 11.2.1. Calling built-in functions
        2. 11.2.2. Obtaining a list of built-in functions
      3. 11.3. Creating Your Own Functions
      4. 11.4. Reusing SQL Code with Stored Procedures
        1. 11.4.1. Saving time with system stored procedures
        2. 11.4.2. Writing your own stored procedures
      5. 11.5. Updating Data Automatically with Triggers
        1. 11.5.1. Creating a trigger
        2. 11.5.2. Disabling a trigger
      6. 11.6. Modifying and Deleting Functions, Stored Procedures, and Triggers
        1. 11.6.1. Modifying objects
        2. 11.6.2. Deleting objects
  9. V. SQL Server Administration
    1. 12. Keeping Your SQL Server Running Smoothly
      1. 12.1. Indexing Data to Improve Query Performance
        1. 12.1.1. Using clustered indexes
        2. 12.1.2. Creating nonclustered indexes
        3. 12.1.3. Optimizing index performance
      2. 12.2. Improving Performance with Partitions
        1. 12.2.1. Creating a partition function
        2. 12.2.2. Creating a partition scheme
        3. 12.2.3. Creating a partitioned table
      3. 12.3. Updating Database Statistics
        1. 12.3.1. Automatically updating statistics
        2. 12.3.2. Manually updating statistics
      4. 12.4. Managing File Sizes
        1. 12.4.1. Automatically shrinking database files
        2. 12.4.2. Manually shrinking a single database file
        3. 12.4.3. Manually shrinking all files associated with a database
      5. 12.5. Checking Database Integrity
        1. 12.5.1. Running DBCC CHECKDB
        2. 12.5.2. Correcting integrity errors
      6. 12.6. Governing Resource Consumption
        1. 12.6.1. Creating resource pools
        2. 12.6.2. Creating workload groups
        3. 12.6.3. Creating classifier functions
        4. 12.6.4. Activating and deactivating Resource Governor
    2. 13. Automating SQL Server 2008 Administration
      1. 13.1. Scheduling Tasks with SQL Server Agent
        1. 13.1.1. Starting SQL Server Agent
        2. 13.1.2. Creating a SQL Server Agent job
        3. 13.1.3. Adding job steps to a SQL Server Agent job
        4. 13.1.4. Scheduling a SQL Server Agent job
        5. 13.1.5. Notifying someone when the job completes
      2. 13.2. Implementing Database Maintenance Plans
        1. 13.2.1. Identifying the tasks to include in a maintenance plan
        2. 13.2.2. Creating a maintenance plan
      3. 13.3. Alerting Administrators about Database Events
        1. 13.3.1. Configuring database operators
        2. 13.3.2. Creating SQL Server alerts
    3. 14. Troubleshooting SQL Server 2008 Problems
      1. 14.1. Understanding the Inner Workings of SQL Server Queries
        1. 14.1.1. Creating a trace with SQL Server Profiler
        2. 14.1.2. Reviewing trace results
      2. 14.2. Reviewing Log Records
        1. 14.2.1. SQL Server error log
        2. 14.2.2. Windows Application Log
        3. 14.2.3. SQL Server Management Studio Log File Viewer
      3. 14.3. Monitoring Your Server with Performance Studio
        1. 14.3.1. Configuring Performance Studio
        2. 14.3.2. Reviewing performance data
      4. 14.4. Tuning Your Database with Database Engine Tuning Advisor
    4. 15. Replicating Data across Multiple Servers
      1. 15.1. Understanding Replication
        1. 15.1.1. Server roles
        2. 15.1.2. Articles and publications
        3. 15.1.3. Replication types
      2. 15.2. Publishing Data with Snapshot Replication
        1. 15.2.1. Creating a distributor
        2. 15.2.2. Creating a publication
      3. 15.3. Subscribing to a Publication
      4. 15.4. Monitoring Replication
  10. VI. Protecting Your Data
    1. 16. Protecting Your Data from Prying Eyes
      1. 16.1. Creating and Managing Logins
        1. 16.1.1. Creating server logins
        2. 16.1.2. Removing database logins
      2. 16.2. Adding Database Users
      3. 16.3. Managing Rights with Roles
        1. 16.3.1. Understanding fixed server roles
        2. 16.3.2. Understanding fixed database roles
        3. 16.3.3. Creating database roles
        4. 16.3.4. Assigning users to database roles
      4. 16.4. Preserving Confidentiality with Encryption
        1. 16.4.1. Encrypting database connections
        2. 16.4.2. Encrypting stored data
      5. 16.5. Auditing SQL Server Activity
        1. 16.5.1. Enabling and configuring auditing
        2. 16.5.2. Reviewing audit records
    2. 17. Preserving the Integrity of Your Transactions
      1. 17.1. Preserving Transaction Integrity with the ACID Model
        1. 17.1.1. Atomicity
        2. 17.1.2. Consistency
        3. 17.1.3. Isolation
        4. 17.1.4. Durability
      2. 17.2. Creating SQL Server Transactions
        1. 17.2.1. COMMIT or ROLLBACK?
        2. 17.2.2. Testing Transact-SQL statements with transactions
      3. 17.3. Changing the Transaction Isolation Level
        1. 17.3.1. READ UNCOMMITTED
        2. 17.3.2. READ COMMITTED
        3. 17.3.3. REPEATABLE READ
        4. 17.3.4. SERIALIZABLE
        5. 17.3.5. SNAPSHOT
      4. 17.4. Handling Errors
    3. 18. Preparing for Disaster
      1. 18.1. Backing Up Your Data
        1. 18.1.1. Backing up databases
        2. 18.1.2. Saving time with differential backups
        3. 18.1.3. Saving space with backup compression
        4. 18.1.4. Backing up the transaction log
      2. 18.2. Specifying Disaster Recovery Requirements with Recovery Models
        1. 18.2.1. Choosing a recovery model
        2. 18.2.2. Changing recovery models
      3. 18.3. Restoring Your Data after a Disaster
      4. 18.4. Using Database Snapshots
        1. 18.4.1. Creating a database snapshot
        2. 18.4.2. Accessing a database snapshot
        3. 18.4.3. Reverting to a database snapshot
    4. 19. Staying Alive: High Availability in SQL Server 2008
      1. 19.1. Creating Redundancy with Database Mirroring
        1. 19.1.1. Choosing an operating mode
        2. 19.1.2. Configuring mirroring
        3. 19.1.3. Monitoring Database Mirroring
        4. 19.1.4. Failing over a mirrored database
      2. 19.2. Synchronizing Databases with Log Shipping
        1. 19.2.1. Configuring log shipping
        2. 19.2.2. Failing over to a log shipping secondary instance
    5. 20. Implementing Policy-Based Management with the Declarative Management Framework
      1. 20.1. Coming to Terms with DMF
      2. 20.2. Creating DMF Policies
        1. 20.2.1. Creating a condition
        2. 20.2.2. Creating a policy
      3. 20.3. Using On Demand Evaluation Mode
        1. 20.3.1. Verifying policy compliance
        2. 20.3.2. Enforcing a policy manually
      4. 20.4. Automated Policy Enforcement
      5. 20.5. Viewing Policies Affecting a Target
  11. VII. The Part of Tens
    1. 21. Ten Ways to Keep Your SQL Server 2008 Databases Humming
      1. 21.1. Monitor Query Performance
      2. 21.2. Back Up Your Data Routinely
      3. 21.3. Verify Database Integrity Often
      4. 21.4. Tune the Physical Structure of Your Databases
      5. 21.5. Conserve Transaction Log Disk Space
      6. 21.6. Monitor Database Logs
      7. 21.7. Automate Administrative Alerts
      8. 21.8. Manage Multiple Servers
      9. 21.9. Simplify User Rights Administration with Roles
      10. 21.10. Perform Security Reviews
    2. 22. Ten Database Design Tips
      1. 22.1. Plan Ahead
      2. 22.2. Draw Before You Click
      3. 22.3. Choose Primary Keys Carefully
      4. 22.4. Select Data Types with Space Efficiency in Mind
      5. 22.5. Make Sure Your Fields Are Single Purpose
      6. 22.6. Remember the Meaning of NULL
      7. 22.7. Normalize when Possible
      8. 22.8. Manage Your Relationships
      9. 22.9. Use Descriptive Names
      10. 22.10. Document Your Design

Product information

  • Title: Microsoft® SQL Server® 2008 For Dummies®
  • Author(s):
  • Release date: September 2008
  • Publisher(s): For Dummies
  • ISBN: 9780470224656