Inside SQL Server 2005 Tools

Inside SQL Server 2005 Tools

by Michael Raheem, Dima Sonkin, Thierry D’Hers, Kami LeMonds
Released October 2006
Publisher(s): Addison-Wesley Professional
ISBN: 0321397967

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

Direct from Microsoft Insiders: the Complete Hands-On Guide to

SQL Server 2005’s Powerful Tools!

Microsoft SQL Server 2005’s high-powered management tools can dramatically

improve DBA productivity and effectiveness. Now there’s a comprehensive guide

to SQL Server 2005’s toolset, straight from the Microsoft team that created it.

This book covers the entire toolset in unprecedented depth, guides database

professionals in choosing the right tools, and shows them how to use various

tools collectively to solve real-world problems. The authors present “how-to”

solutions and never-before-published tips for SQL Server 2005 installation,

upgrades, configuration, authoring, schema design, development, deployment,

optimization, troubleshooting, and more. Coverage includes

  Understanding how SQL Server 2005 tools differ from their predecessors

  Leveraging the new Business Intelligence Development Studio and

Management Studio to accomplish dozens of common tasks

  Improving security by reducing the “surface area” you expose

  Executing queries with the new Query Editor and SQLCMD query tools

  Optimizing with the revamped SQL Server Profiler and Database

Engine Tuning Advisor

  Scheduling maintenance tasks with the SQL Server Agent

  Monitoring with the Activity Monitor, SQL Server Agent, and other tools

  Moving data efficiently and reliably with Integration Services

  Using SQL Server’s new e-mail tool, Database Mail

  Managing replication in complex distributed environments

  Mastering SQL Server’s Analysis Services’ OLAP and Data Mining tools

  Using Notification Services 2.0, Reporting Services, and more

  Extending functionality using underlying APIs and the included

CD-ROM’s sample code.

MICHAEL RAHEEM is the senior product manager of the Microsoft SQL Server

team and led the design and implementation of several SQL Server tools, such

as SQL Server Management Studio, Database Mail, Upgrade Advisors, and

Surface Area Configuration. He currently leads the SQL Server enterprise

marketing efforts including high availability, scalability, and performance.

DIMA SONKIN is development lead in the SQL Server Tools group responsible

for SQL Server Agent, SMO, DMO, SQL Profiler, Database Engine Tuning Advisor,

Database Mail, SQLMail, SqlCmd, and OSQL.

THIERRY D’HERS is the lead program manager on Microsoft’s SQL BI Analysis

Services team and has spent five years designing and specifying Analysis

Services tools.

KAMI LEMONDS is the documentation manager for Microsoft’s SQL Server User

Education team and contributed to the SQL Server 2000 Resource Kit.

CD-ROM contains all sample code from the book and corresponding

Visual Studio project files.

Contents

Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii

Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . xxvi

About the Authors . . . . . . . . . . . . . . . . . . . . . . . xxvii

Chapter 1 Inside the Fundamentals. . . . . . . . . . . . . . . . . . . . . 1

Chapter 2 Inside the Installation and Upgrade Tools. . . . . . . 23

Chapter 3 Inside the Configuration Tools . . . . . . . . . . . . . . . 73

Chapter 4 Inside Management and Administration Tools . . 129

Chapter 5 Inside Data Authoring Tools . . . . . . . . . . . . . . . . 181

Chapter 6 Inside SQLCMD Query Tools . . . . . . . . . . . . . . . . 199

Chapter 7 Inside Tuning and Optimization Tools . . . . . . . . . 237

Chapter 8 Inside Scheduling Tools . . . . . . . . . . . . . . . . . . . 273

Chapter 9 Inside Monitoring Tools. . . . . . . . . . . . . . . . . . . . 305

Chapter 10 Inside Email Tools . . . . . . . . . . . . . . . . . . . . . . . . 337

Chapter 11 Inside Programming Object Models . . . . . . . . . . 357

Chapter 12 Inside Replication Tools . . . . . . . . . . . . . . . . . . . 385

Chapter 13 Inside Analysis Services OLAP Tools . . . . . . . . . 429

Chapter 14 Inside Analysis Services Data

Mining Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519

Chapter 15 Inside Notification Services Tools . . . . . . . . . . . 543

Chapter 16 Inside Integration Services Tools . . . . . . . . . . . . 601

Chapter 17 Inside the Reporting Services Tools . . . . . . . . . . 679

Appendix A Sample KPI Client Code— Retrieving and

Exposing Your First KPI. . . . . . . . . . . . . . . . . . . . 711

Appendix B KPI Utilities—Code for Parsing Display

Folders and Getting Image Indexes . . . . . . . . . . 713

Appendix C KPI Viewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721

Appendix D Complete List of Data Mining Stored

Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 723

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 733

Table of contents

  1. Contents (1/3)
  2. Contents (2/3)
  3. Contents (3/3)
  4. Foreword
  5. Preface (1/2)
  6. Preface (2/2)
  7. Acknowledgments
  8. About the Authors
  9. Chapter 1 Inside the Fundamentals
    1. History of SQL Server Tools
    2. Why Database Tools Are Important
    3. Introducing the SQL Server 2005 Family of Products
      1. Database Engine
      2. Analysis Services
      3. Reporting Services
      4. Notification Services
      5. Integration Services
      6. SQL Server Everywhere Edition
    4. SQL Server 2005 Tools
      1. Installation and Upgrade Tools
      2. Configuration Tools
      3. Management and Administration Tools
      4. Monitoring and Operations Tools
      5. Database, Schema, and Report Design Tools
      6. Development Tools
      7. Deployment Tools
      8. Query Tuning and Optimization Tools
      9. Maintenance Tools
      10. Email Delivery Tools
      11. Programming Object Models
    5. Comparing SQL Server 2005 with SQL Server 2000 Tools
    6. Summary
  10. Chapter 2 Inside the Installation and Upgrade Tools
    1. SQL Server 2005 Installation Issues
      1. Pre-Installation Tasks
      2. Choosing the Right SQL Server Edition
      3. Understanding SQL Server Instances
      4. SQL Server Installation Tool
      5. SQL Server Setup Wizard
    2. Installing SQL Server 2005
      1. Installing SQL Server Database Engine (1/3)
      2. Installing SQL Server Database Engine (2/3)
      3. Installing SQL Server Database Engine (3/3)
      4. Installing Analysis Services
      5. Installing Reporting Services
      6. Installing Integration Services
      7. Installing Notification Services
      8. Installing SQL Server Tools and Workstation Components
      9. Installing SQL Server Books Online
      10. Installing Sample Databases, Sample Codes, and Sample Applications
      11. Installing SQL Server 2005 on a Computer with a Previous Version of SQL Server
      12. Adding or Removing SQL Server Components from an Existing Instance
      13. Post-installation Tasks
    3. Upgrading to SQL Server 2005
      1. Upgrade Planning
      2. Pre-upgrade Tasks
      3. Backward Compatibility Issues (1/2)
      4. Backward Compatibility Issues (2/2)
      5. Upgrade Tasks (1/2)
      6. Upgrade Tasks (2/2)
      7. Choosing an Upgrade or Migration Tool
      8. Performing an In-place Upgrade
      9. Post-upgrade Tasks
    4. Summary
  11. Chapter 3 Inside the Configuration Tools
    1. SQL Server Configuration Tools
      1. SQL Server Configuration Manager
      2. SQL Server Surface Area Configuration
      3. Reporting Services Configuration
      4. Usage and Error Reporting Configuration
    2. Common Configuration Scenarios
      1. Identifying SQL Server Instances and Components on a Computer
      2. Starting, Stopping, and Viewing State of Services
      3. Configuring Service Account and Password
      4. Configuring Advanced Service Settings
      5. Language
      6. Configuring Database Server Network Protocols
      7. Configuring Database Client Application Network Protocols
      8. Configuring a Remote Computer
      9. Encrypting Connections to Database Engine
      10. Configuring Database Engine Startup Parameters
      11. Starting Database Engine in Single User Mode
      12. Starting Database Engine with a Trace Flag
      13. Enabling Database Mirroring
      14. Configuring Surface Area of SQL Server (1/3)
      15. Configuring Surface Area of SQL Server (2/3)
      16. Configuring Surface Area of SQL Server (3/3)
      17. Copying Surface Area Settings Between Two Computers
      18. Stopping SQL Server Services Across Multiple Instances
      19. Configuring Surface Area of a Remote Computer
      20. Sending Information About Feature Usage and Serious Errors to Microsoft
    3. Summary
  12. Chapter 4 Inside Management and Administration Tools
    1. SQL Server Management Studio
      1. Installing and Launching SQL Server Management Studio
      2. Management Studio Components (1/5)
      3. Management Studio Components (2/5)
      4. Management Studio Components (3/5)
      5. Management Studio Components (4/5)
      6. Management Studio Components (5/5)
    2. Management and Administration Scenarios
      1. Connecting to a Server via Dedicated Administrator Connection
      2. Removing Most Recently Used (MRU) Server Names from the Connection Dialog
      3. Changing Default Startup Behavior of Management Studio
      4. Updating Registered Servers with Local Instances
      5. Importing Registered Servers from Enterprise Manager
      6. Referencing Registered Servers with a Friendly Name
      7. Copying Registered Servers Between Users and Computers
      8. Adding Registered Servers to an Export File
      9. Copying Query Result to Excel Spreadsheet
      10. Configuring WMI to Show Server Status in Registered Servers
      11. Leveraging an Asynchronous Population of Objects in Object Explorer
      12. Editing Queries in Disconnected Mode
      13. Using Query Designer for Writing Queries
      14. Executing a Query Across Multiple Servers
      15. Viewing and Editing XML Data
      16. Deleting and Scripting Multiple Objects
    3. Summary
  13. Chapter 5 Inside Data Authoring Tools
    1. Query Editor in SQL Server Management Studio
      1. Query Editor Architecture
      2. XML Result Support
    2. Bulk Copy Utility (BCP)
      1. BCP Architecture
      2. New in SQL2005 BCP
      3. BCP Command Syntax
      4. BCP Usage Scenarios (1/2)
      5. BCP Usage Scenarios (2/2)
    3. Summary
  14. Chapter 6 Inside SQLCMD Query Tools
    1. Command Line Options
      1. Overview of Command Line Options
      2. Connection Related Options
      3. Formatting Options
      4. Command Execution Options
      5. File Stream and Redirection Options
      6. Error Handling and Reporting Options
      7. Language-Specific Conversion Options
      8. Security and Scripting Options
    2. Scripting Variables
    3. SQLCMD Commands
      1. Overview of SQLCMD Commands
      2. Explanations and Examples of SQLCMD Commands (1/2)
      3. Explanations and Examples of SQLCMD Commands (2/2)
    4. SQLCMD Startup Script
    5. Authoring of SQLCMD Queries Using SQL Server Management Studio Query Editor
      1. Turning the SQLCMD Feature On and Off
      2. Colorizing of SQLCMD Commands and Variables
      3. SQLCMD Mode Script Execution in Query Editor
      4. SQLCMD Commands Not Supported by Query Editor
      5. Redirecting Results and Errors in Query Editor
    6. Summary
  15. Chapter 7 Inside Tuning and Optimization Tools
    1. Using SQL Server Management Studio for Query Optimization
    2. SQL Server Profiler
      1. Trace Definition
      2. Advanced Tracing Options
      3. Editing Profiler Templates
      4. Saving a Trace in XML Format
      5. Performance Counter Correlation
      6. Trace Objects API
      7. SQL Profiler Replay (1/2)
      8. SQL Profiler Replay (2/2)
    3. Database Tuning Advisor
      1. DTA Tuning Options
      2. DTA Architecture
      3. Evaluative Tuning
    4. Summary
  16. Chapter 8 Inside Scheduling Tools
    1. SQL Server Task Scheduling via SQL Server Agent
      1. Supported Task Types
      2. Jobs and Job Steps
      3. Creating Jobs
      4. Creating Job Steps
      5. Creating Job Schedules
    2. Monitoring Job Execution
      1. Looking at the Job History
      2. Job Activity Monitor
      3. SQL Server Agent Performance Monitor Counters
    3. SQL Server Agent Security
      1. Database Roles
      2. Proxy Accounts
      3. Setting Up a Proxy Account
      4. Job Step Logging
    4. SQL Server Agent Multi-Server Administration
    5. SQL Server Agent Architectural Overview
    6. SQL Server Agent Advanced Service Configuration
    7. Summary
  17. Chapter 9 Inside Monitoring Tools
    1. Monitoring Server Activity
      1. Activity Monitor
      2. Monitoring Scenarios
    2. Using SQL Server Agent Monitoring Capabilities
      1. SQL Server Agent Operator
      2. SQL Server Event Alerts
      3. SQL Server Performance Alerts
      4. Windows Management Instrumentation Alerts
    3. Using SQL Server Profiler for Monitoring Server Activity
    4. Monitoring Using Microsoft Operations Manager and SQL Server Management Pack
      1. Administrator Console
      2. Rule Groups (1/2)
      3. Rule Groups (2/2)
      4. Operator Console
    5. Summary
  18. Chapter 10 Inside Email Tools
    1. SQL Mail
      1. Overview
      2. Sending Mail
      3. Reading Mail
    2. Database Mail
      1. Mail Accounts and Mail Profiles
      2. Configuring Database Mail
      3. Database Mail Security
      4. Sending Mail
      5. System Configuration Parameters
      6. Monitoring and Troubleshooting Database Mail
      7. Managing Database Mail Storage
    3. SQL Agent Mail
    4. Summary
  19. Chapter 11 Inside Programming Object Models
    1. The SMO Object Model
    2. Some Uses of SMO Objects
      1. Create a Simple Login Using SQL Authentication
      2. Alter an Existing Table by Changing the Data Type of One of Its Columns to Small Integer
      3. Drop Full Text Index on View
    3. Capture Mode
    4. Scripting
      1. Single Phase Scripting
      2. Behind the Scenes
      3. Advanced Scripting
    5. Backup and Restore
      1. Back Up the AdventureWorks Sample Database to a Disk with Mirroring
      2. Perform Asynchronous Database Backup
      3. Restore a Complete Database by Replacing the Existing One
    6. Architecture
    7. Local Trees
    8. Monitoring Server Events
      1. Monitor Table Create/Drop Events at the Server Instance Level
      2. Monitor an Index Creation Event on a Table Object Level
      3. Monitoring a Trigger Alter or Drop at the Object Level
    9. Optimized Instantiation
    10. Pre-fetch Capability
    11. Summary
  20. Chapter 12 Inside Replication Tools
    1. Database Replication Basics
      1. A Brief Overview of Replication
      2. Initializing and Synchronizing Subscriptions
    2. Wizards in Management Studio
    3. Administering Replication in Management Studio
      1. Using the New Publication Wizard to Create Publications (1/3)
      2. Using the New Publication Wizard to Create Publications (2/3)
      3. Using the New Publication Wizard to Create Publications (3/3)
      4. Using the New Subscription Wizard to Create Subscriptions
    4. Monitoring Replication Activity in Replication Monitor
      1. Adding Publishers to Replication Monitor
      2. Publisher Detail Pane
      3. Transactional Publication Detail Pane
      4. Transactional Subscription Detail Window
      5. Merge Publication Detail Pane
      6. Merge Subscription Detail Window
    5. Summary
  21. Chapter 13 Inside Analysis Services OLAP Tools
    1. Analysis Services OLAP Tools Overview
      1. BI Development Studio
      2. SQL Management Studio
    2. Using Analysis Services Tools to Address Business Scenarios
      1. How Should I Upgrade My Existing Application? (1/2)
      2. How Should I Upgrade My Existing Application? (2/2)
      3. What Exactly Is the UDM?
      4. How Do I Decide Whether I Should Work Live on the Server or in Project Mode?
      5. How Do I Deploy a DB Across Servers?
      6. How Do I Take an AS2K Cube and Enhance It with New AS2K5 Features?
      7. How Do I Create New Templates for Cubes and Dimensions?
      8. Why Should I Be Typing My Dimensions and Attributes with Dimension Types (Time, Currency, Account, and so on) or Attribute Types?
      9. How Do I Make Visual Development Studio Look Like Analysis Manager 2000?
      10. How Can I Produce Documentation for Every Object Contained in My Solution?
      11. When Would Auto-scrolling During Drag-and-Drop Operations Help?
      12. How Do I Use SQL in Cube, Dimension, and Partition Definitions?
      13. How Can I Prototype a Cube Rapidly Without Prerequiring Any Existing Data Sources?
      14. How Can I Easily Use Test Data to Speed Development of Cubes and Dimensions?
      15. How Do I View Sample Measures Data in the Cube Wizard?
      16. How Do I Compare Two Measure Groups' Dimensionality?
      17. How Can I Have a Measure Group with a Level of Granularity Above the Key?
      18. How Do I Change Calculation, Action, or KPI MDX Templates?
      19. Should I Use the KPI Display Folder or KPI Parent Property?
      20. How Can I Rebuild My Own KPI Browser, Similar to the One Available in BI Development Studio, and Embed It in My Application?
      21. How Do I Use the Cube Debugger as a Calculation Builder? (1/2)
      22. How Do I Use the Cube Debugger as a Calculation Builder? (2/2)
      23. How Do I Automate Loading Translation Strings into My Project?
      24. How Do I Automatically Create Partitions for a New Data Set?
      25. What Is the Difference Between the Cubes Filter Component and the OWC Filter in the Cube Browser Page?
      26. How Do I Test My Roles and Security?
      27. How Do I Change Time Intelligence Templates?
      28. How Do I Document Changes Generated by the BI Wizard?
      29. How Do I Bring Pictures into My UDM?
      30. Which Tree Views in the User Interface That Support Multiple Selections Might Help in My Case?
      31. How Do I Set Up a Dimension with Multiple Parents Rollup with Weights?
      32. How Should I Build the Time Dimension?
      33. How Do I Execute Multiple MDX Queries from SQL Server Management Studio?
      34. How Do I Find a Specific Server Property?
      35. How Do I Set a Process Option for More than One Object at Once in the Process Object(s) Dialog?
    3. Summary
  22. Chapter 14 Inside Analysis Services Data Mining Tools
    1. Analysis Services Tools Overview
    2. Using Analysis Services Data Mining Tools to Solve Problems in Business Scenarios
      1. How to Embed Data Mining Controls and Sample Code in a Custom Application
      2. How to Interpret "Little Diamonds" and How to View Them
      3. Recoding a Column Using the Data Source View
      4. Measuring Lift over Global Statistics
      5. Creating a Classification Matrix Report
      6. How Multi-Selecting in the Data Mining Wizard and Editor in BI Development Studio Can Enhance Productivity
      7. Using Association Rules Viewer Filtering Capability
      8. Use the Same Base Column Multiple Times with Different Properties in Different Algorithms
      9. Creating Multiple Models, Using the Same Algorithm but with Varying Column Settings or Changing Properties
      10. Copying Grid and Trees Viewers in Microsoft Excel, Microsoft Word, or HTML
      11. DMX Editor Can Call the Stored Proc to Display the Data Contained in the Viewers
      12. How to View and Customize DMX Templates
    3. Summary
  23. Chapter 15 Inside Notification Services Tools
    1. Overview of Notification Services
      1. Notification Services Architecture
      2. Building Notification Applications
      3. Defining Instances of Notification Services
      4. Deploying and Managing Instances of Notification Services
    2. Overview of the Notification Services Tools
      1. NSControl Command Line Utility
      2. SQL Server Management Studio
      3. Notification Services Stored Procedures and Views
      4. API
    3. Other Tools
      1. Windows Event Viewer
      2. SQL Server Configuration Manager and SQL Server Surface Area Configuration
      3. Windows Performance
    4. Common Deployment and Management Scenarios
      1. Deploying an Instance of Notification Services
      2. Upgrading an Instance from SQL Server 2000 to SQL Server 2005 (1/2)
      3. Upgrading an Instance from SQL Server 2000 to SQL Server 2005 (2/2)
      4. Upgrading to a Different Edition of SQL Server 2005
      5. Viewing the Notification Services Summary Report
      6. Enabling, Disabling, Starting, and Stopping
      7. Updating an Instance of Notification Services
      8. Updating Registry Information
      9. Exporting Instance and Application Metadata
      10. Configuring Logging Levels
      11. Removing Obsolete Data
      12. Deleting an Instance of Notification Services
      13. For More Information
    5. Summary
  24. Chapter 16 Inside Integration Services Tools
    1. Integration Services Overview
    2. Integration Services Tools Overview
      1. Business Intelligence Development Studio
      2. SQL Server Import and Export Wizard
      3. SSIS Designer (1/2)
      4. SSIS Designer (2/2)
      5. Variable Management Tools
      6. Logging Configuration Tools
      7. Properties Management Tools
      8. Package Configuration Tools
      9. Debugging Tools
      10. Package Deployment Tools
      11. SQL Server Management Studio
      12. Command Prompt Utilities
    3. Using Integration Services Tools in Business Scenarios
      1. Common Environment Configuration Scenarios
      2. Common Package Development Scenarios (1/4)
      3. Common Package Development Scenarios (2/4)
      4. Common Package Development Scenarios (3/4)
      5. Common Package Development Scenarios (4/4)
      6. Common Package Deployment Scenarios
      7. Common Package Management Scenarios (1/4)
      8. Common Package Management Scenarios (2/4)
      9. Common Package Management Scenarios (3/4)
      10. Common Package Management Scenarios (4/4)
    4. Summary
  25. Chapter 17 Inside the Reporting Services Tools
    1. Reporting Services Tools Overview
      1. Setup and Configuration
      2. Reporting Services Configuration Tool
      3. Scale-out Configuration
    2. Building Reports
      1. Using Report Designer
      2. Deploying Reports
      3. Using Report Builder
      4. Using Model Data Sources in Report Designer
      5. Choosing the Correct Type of Data Source for Your Report
    3. Managing and Securing Reports
      1. Using SQL Server Management Studio
      2. Securing Reports
      3. Using Report Manager
    4. Using Reporting Services Tools to Solve Problems in Business Scenarios
      1. How Do I Optimize My SQL-, DMX-, or MDX-based Report through Report Designer?
      2. How Can I Build a Report with Both Relational and OLAP Data That Share the Same Parameter Field?
      3. How Do I Use Report Designer to Bring KPIs into a Report?
      4. How Can I Let Users Pick Their Own Languages at Runtime to Display the Report?
      5. How Can I Use Report Designer to Build an Asymmetrical Report?
    5. Summary
  26. Appendix A: Sample KPI Client Code— Retrieving and Exposing Your First KPI
  27. Appendix B: KPI Utilities—Code for Parsing Display Folders and Getting Image Indexes (1/2)
  28. Appendix B: KPI Utilities—Code for Parsing Display Folders and Getting Image Indexes (2/2)
  29. Appendix C: KPI Viewer
  30. Appendix D: Complete List of Data Mining Stored Procedures (1/2)
  31. Appendix D: Complete List of Data Mining Stored Procedures (2/2)
  32. Index
    1. A
    2. B
    3. C
    4. D
    5. E
    6. F
    7. G
    8. H
    9. I
    10. J
    11. K
    12. L
    13. M
    14. N
    15. O
    16. P
    17. Q
    18. R
    19. S (1/2)
    20. S (2/2)
    21. T
    22. U
    23. V
    24. W
    25. X–Z (1/2)
    26. X–Z (2/2)

Product information

  • Title: Inside SQL Server 2005 Tools
  • Author(s): Michael Raheem, Dima Sonkin, Thierry D’Hers, Kami LeMonds
  • Release date: October 2006
  • Publisher(s): Addison-Wesley Professional
  • ISBN: 0321397967