Table of Contents
Introduction
1 Selecting and Designing SQL Server Services to Support Business Needs
Before You Begin
Lesson 1: Selecting the Appropriate Services
Practice: Selecting the Appropriate Services to Support Business Needs
Lesson 2: Evaluating Core, SQL Server Agent, and Database Mail Solutions
Transact-SQL Enhancements
Considerations for Using CLR Integration
Using SQL Server Agent
Using Database Mail
Practice: Selecting an Appropriate Programming Language
Lesson 3: Using Advanced Services
SQL Server Web Services
Using Service Broker
Replication Enhancements
Implementing Distributed Transactions
Practice: Using Advanced Database Engine Features
Lesson 4: Evaluating Other Services
Notification Services
Reporting Services
Analysis Services
Integration Services
Practice: Using Other Services
Case Scenario: Select SQL Server Services to Support Business Needs
Chapter Summary
2 Designing a Logical Database
Before You Begin
Lesson 1: Systematically Approaching Design Stages
Key Steps and Best Practices for Data Modeling
Object Role Modeling and the Conceptual Model
Entity Relationship and the Logical Model
Practice: Opening Models
Lesson 2: Designing a Normalized Database
First Normal Form
Second Normal Form
Third Normal Form
Practice: Normalizing the Database
Lesson 3: Optimizing the Database Design by Denormalizing
Practice: Denormalizing the Database
Lesson 4: Designing the Data Flow Architecture
Data Flow for OLTP Applications
Data Flow for Business Intelligence Applications
Lesson 5: Supertypes and Subtypes
Supertypes and Subtypes
Modeling Hierarchies
Practice: Supertypes, Subtypes, and Hierarchies
Case Scenario: Design a Logical Database
Chapter Summary
3 Designing a Physical Database
Before You Begin
Lesson 1: Choosing Column Data Types and Sizes
System Data Types
Best Practices for Data Types and Sizes
Practice: Choosing Appropriate Data Types
Lesson 2: Designing User-Defined Data Types
T-SQL Aliases (UDDTs)
CLR User-Defined Types (UDTs)
Best Practices for User-Defined Data Types
Practice: Creating User-Defined Data Types
Lesson 3: Defining Entities and Entity and Referential Integrity
Designing and Creating Entities
Entity Integrity
Referential Integrity
Special Attributes
Practice: Defining Entities and Entity and Referential Integrity
Lesson 4: Defining Domain Integrity and Business Rules
Default Constraints
Check Constraints
Practice: Implementing Domain Integrity
Lesson 5: Creating Programmable Objects to Maintain Integrity
DML Triggers
Practice: Creating DML Triggers and Testing Data Integrity
Case Scenario: Design a Physical Database
Chapter Summary
4 Designing a Database for Performance
Before You Begin
Lesson 1: Optimizing Queries by Creating Indexes
The Basics of Optimizing Queries
Maintaining Statistics
Practice: Selecting Columns to Index
Lesson 2: Designing Indexes
Clustered and Nonclustered Indexes
Creating Indexes
Practice: Designing Indexes
Lesson 3: Specifying Indexed Views
Indexing a Computed Column
Indexing a View
Practice: Specifying Indexed Views
Lesson 4: Partitioning a Table
Understanding Table Partitioning
Practice: Partitioning a Table
Case Scenario: Design a Database for Performance
Chapter Summary
5 Using Appropriate Database Technologies and Techniques for Your Application
Before You Begin
Lesson 1: Using XML Data in Databases
XML Data Type Usage
XML Indexes
Practice: Using XML Data in a Database
Lesson 2: Choosing Languages
CLR vs. T-SQL
CLR and T-SQL vs. Other Languages
Practice: Choosing Appropriate Languages
Lesson 3: Designing for Scalability
Scaling Up
Scaling Out
Developing Aggregation Strategies
Practice: Using Bulk Insert
Lesson 4: Designing Interoperability with External Systems
Synchronous Methods
Asynchronous Methods
Practice: Linking to Excel 2007
Case Scenario: Implement Database Technologies and Techniques for Your Application
Chapter Summary
6 Designing Objects That Retrieve Data
Before You Begin
Lesson 1: Designing Views
Choosing Between the Different Types of Views
Practice: Designing Views
Lesson 2: Designing Stored Procedures
What Type of Stored Procedure Do You Need?
What Type of Data Will the Stored Procedure Return?
Defining Input, Output, and Optional Parameters for
the Stored Procedure
Defining the Status Value the Stored Procedure Returns
Designing Error Handling Routines
Executing Under the Right Security Context
Practice: Creating and Modifying a Stored Procedure
Lesson 3: Designing User-Defined Functions
What Type of UDF Do You Need?
What Type of Data Will the UDF Return?
Defining Input Parameters for the UDF
Designing Error Handling Routines
Executing Under the Right Security Context
Practice: Designing User-Defined Functions
Case Scenario: Designing Objects That Retrieve Data
Chapter Summary
7 Designing Objects That Extend Server Functionality
Before You Begin
Lesson 1: Creating and Designing Stored Procedures
Understanding Stored Procedures
Creating T-SQL Stored Procedures
Changing and Deleting T-SQL Stored Procedures
Designing T-SQL Stored Procedures
Creating CLR Stored Procedures
Practice: Creating a T-SQL Stored Procedure to Add Employees
Lesson 2: Designing Scalar User-Defined Functions
Creating T-SQL Scalar Functions
Creating CLR Scalar Functions
UDF Properties
Practice: Creating a CLR User-Defined Function to Extract E-Mail
Lesson 3: Designing DML and DDL Triggers
Creating DML Triggers
Creating DDL Triggers
Practice: Using a Trigger to Create a Deleted-Rows Table
Lesson 4: Designing CLR User-Defined Aggregates
Programming User-Defined Aggregates
User-Defined Aggregate Attributes
Practice: Creating a User-Defined Aggregate
Case Scenario: Adding an Audit Trail
Chapter Summary
8 Designing a Secure Application Solution
Before You Begin
Lesson 1: Securing Components of a SQL Server Solution
SQL Server Authentication Modes
Authorization Strategy
Securing HTTP Endpoints
CLR Integration Security
Guidelines for Replication Security
Linked Servers Security
SQL Server Agent and DatabaseMail
Designing Security for Notification Services
Designing Security for Reporting Services
Designing Security for Analysis Services
Designing Security for Integration Services
Practice: Securing a SQL Server Solution
Lesson 2: Designing the Database to Enable Auditing and Encryption
Considerations for an Auditing Strategy
Auditing Events, Techniques, Tools, and Storage
Data Protection
Data Encryption
Practice: Using Event Notifications to Audit DDL Events
Case Scenario: Design a Secure Application Solution
Chapter Summary
9 Designing a Secure Database
Before You Begin
Lesson 1: Designing a Database-Access Strategy
Managing Principals
Practice: Designing a Database-Access Strategy
Lesson 2: Managing Schemas
Defining Schemas
Guidelines for Managing Schemas
Schemas and Database Roles
Schemas and Object Ownership
Practice: Using Schemas and Name Resolution
Lesson 3: Specifying Database Object Security Permissions
Statement Permissions
Checking Object Permissions
Practice: Verifying Statement Permissions and Hierarchy
Lesson 4: Managing Objects That Access Data
Using Programmable Objects to Maintain Security
What Are Ownership Chains?
Practice: Using Ownership Chains
Lesson 5: Designing an Execution-Context Strategy
What Is the Execution Context?
Module Signing
Practice: Defining the Execution Context
Case Scenario: Design a Secure Database
Chapter Summary
10 Designing a Unit Test Plan for a Database
Before You Begin
Lesson 1: Assessing Which Components to Unit Test
Goals of Unit Testing
Planning for Unit Testing
Creating the Testing Script
Example: How to Write Unit Testing Code with T-SQL
Creating a Setup Testing Script
Creating a Teardown Testing Script
Validating the Testing, Setup, and Teardown Scripts
Creating or Setting Up a Test Database
Setting Up Testing Data
Executing the Unit Test
Evaluating the Test Result
Practice: Creating a Unit Testing Script and a Testing Database
Lesson 2: Designing Tests for Query Performance
How to Design a Test
Writing a Test to Validate Query Performance
Practice: Designing a Testing Script and Setting Performance Goals
Lesson 3: Designing Tests for Data Consistency
Testing Values of Attributes
Validating Foreign Key Constraints
Validating Custom Constraints
Writing a Test to Validate Data Consistency
Practice: Validating Data Consistency in a UDF
Lesson 4: Designing Tests for Application Security
Validating the Existence of Permissions, Principals, and Roles
Validating the Execution Context for Specific Permissions,
Principals, and Roles
Writing a Test to Validate Application Security
Practice: Validating Whether a User Has Been Created
Lesson 5: Designing Tests for System Resources Use
Setting Performance Goals
Using Performance Goals to Evaluate Performance
Counters and Metrics
Practice: Choosing Performance Counters and Metrics
Lesson 6: Designing Tests to Ensure Code Coverage
Setting a Goal for Code Coverage
Meeting Code-Coverage Test Requirements
Writing a Test to Validate Code Coverage
Practice: Designing a Test to Ensure Code Coverage
Case Scenario: Design a Unit Test Plan for a Database
Chapter Summary
11 Creating a Database Benchmarking Strategy
Before You Begin
Lesson 1: Establishing Performance Objectives and Capacity Planning
Establishing Performance Objectives
Performance Modeling
Capacity Planning
Practice: Setting Performance Objectives
Lesson 2: Creating a Strategy for Measuring Performance Changes
Generating a Representative Baseline
Measuring a Baseline
Measuring Performance Changes
Monitoring the Test Environment
Implementing Performance Measuring Techniques
Using SQL Server Profiler
Practice: Measuring Performance Changes
Lesson 3: Creating a Plan for Responding to Performance Changes
Setting Goals
Determining What Has Changed
Determining How Change Affects the System
Responding to Performance Changes
Issues That Can Affect Performance and Scalability
Practice: Responding to Performance Changes
Lesson 4: Creating a Plan for Tracking Benchmark Statistics Over Time
Setting Goals
Continued Testing and Performance Measuring
Generating and Documenting Best Practices
Practice: Creating a Plan
Case Scenario: Create a Performance Baseline and Benchmarking Strategy
Chapter Summary
12 Creating a Plan for Deploying a Database
Before You Begin
Lesson 1: Selecting a Deployment Technique
Deploying with the SSMS Copy Database Wizard
Customizing the SSIS Package Created by the Copy Database Wizard
Deploying with T-SQL Scripts
Deploying with the Import and Export Wizard
Deploying with SSIS
Deploying with the SQLCmd Utility
Practice: Detaching and Attaching a Database
Lesson 2: Practical Deployment Considerations
Deploying Securely
Creating an Object-Change Strategy
Creating a Data-Change Strategy
Creating an Audit Trail
Defining Change Control
Creating a Project-Management Methodology
Practice: Deploying to SQL Server Express by Using Backup and Restore
Case Scenario: Deploying a Database
Chapter Summary
13 Controlling Changes to Source Code
Before You Begin
Lesson 1: Managing Source Code Changes
SQL Server Management Studio and Source Control
Adding the Project to Source Control
Working with a Source-Controlled Database Project
Practice: Managing Changes to Source Code
Lesson 2: Setting File Permissions
Visual SourceSafe User Permissions and Rights
Folder Permissions
Opening Visual SourceSafe–Controlled Projects
Optional Practice: Setting Source Control File Permissions
Lesson 3: Setting and Retrieving Version Information
Practice: Set Version Information
Lesson 4: Detecting Differences Between Versions
Practice: Detect Version Differences
Lesson 5: Encrypting Source Code
Practice: Source Code Encryption
Lesson 6: Tracking Changes to Groups of Objects
Case Scenario: Controlling Changes to Source Code
Chapter Summary
14 Designing for Data Distribution
Before You Begin
Lesson 1: Designing a DatabaseMail Solution for Distributing Data
DatabaseMail Architecture
Enabling DatabaseMail
DatabaseMail Accounts, Profiles, and Security
Sending Messages
Practice: Sending E-Mail Messages by Using DatabaseMail
Lesson 2: Designing SQL Server Agent Alerts
Defining Alerts
Designing WMI Triggers
Defining and Notifying Operators
Creating User-Defined Events
Practice: Creating a SQL Server Agent Alert
Lesson 3: Specifying a Web Services Solution for Distributing Data
Creating and Defining SQL Server Web Services
SQL Server Web Services Security
Guidelines for Using SQL Server Web Services
Practice: Creating a SQL Server Web Service
Lesson 4: Specifying a Reporting Services Solution for Distributing Data
Reporting Services Delivery Options
Reporting Services Subscriptions
Distributing Data by Using Data-Driven Subscriptions
Creating a Data-Driven Subscription
Practice: Specifying SSRS Options for Distributing Data
Lesson 5: Specifying a Notification Services Solution for Distibuting Data
Notification Services Architecture
Scale-Out Options
Defining Notification Services Applications
Practice: Identifying When to Use a Notification Services Solution
Case Scenario: Design a Distributed Data Solution
Chapter Summary
15 Designing Applications That Support Reporting and Use Reporting Services
Before You Begin
Lesson 1: Evaluating the Use of Reporting Services and Designing Reports
Evaluating Reporting Services Uses
Designing Reporting Services Reports
Practice: Creating a Report with the Report Wizard
Lesson 2: Designing a Snapshot Strategy, Schema, Indexes and Data Transformations
Reporting Services Real-Time Requirements
Designing the Snapshot Strategy
Designing the Schema
Designing Indexes
Designing the Data Transformation
Practice: Creating a Purchasing Summary Report
Lesson 3: Designing Programmatic Interfaces and the Data Access Method for Reporting
Querying Tables Directly
Using Views to Support Reports
Using Stored Procedures
Practice: Creating a Row-Filtered Report
Lesson 4: Optimizing Reports
Report Caching
Report Snapshots
Specifying Subscription Models
Practice: Optimizing Report Performance
Case Scenario: Building a Reporting Services Infrastructure for a SharePoint Portal
Chapter Summary
16 Developing Applications for Notification Services
Before You Begin
Lesson 1: Configuring Notification Services Instances and Applications
Configuring Notification Services Instances
Configuring Notification Services Applications
Practice: Configuring Notification Services Applications and Instances
Lesson 2: Defining Notification Services Events and Event Providers
Defining Event Classes
Defining Event Providers
Practice: Using Event Providers and Event Classes
Lesson 3: Configuring the Notification Services Generator and Distributor
Configuring the Notification Services Generator
Configuring the Notification Services Distributor
Practice: Configuring the Generator and Distributor
Lesson 4: Testing the Notification Services Application
Creating and Registering a New Instance and Compiling
Your Application
Exploring Your Instance and Application Objects
Removing the Instance and Application
Practice: Testing Your Application
Lesson 5: Creating Subscriptions
Defining the Subscription Class
Subscription Management Interfaces
Practice: Subscription in Notification Services
Lesson 6: Optimizing Notification Services
Optimizing Event Data
Optimizing Subscriptions
Optimizing Notifications
Practice: Optimizing Notification Services Solutions
Case Scenario: Design a Notification Services Application
Chapter Summary
17 Developing Packages for Integration Services
Before You Begin
Lesson 1: Creating Integration Services Packages
What Is a Package?
Control Flow
Data Flow
Practice: Creating an SSIS Package
Lesson 2: Debugging and Testing SSIS Packages
Running SSIS Packages
Debugging SSIS Packages
Testing SSIS Packages
Practice: Debugging Control Flow and Data Flow
Lesson 3: Selecting an Appropriate SSIS Technology or Strategy
SSIS ETL Design Patterns
SSIS: Beyond ETL
Case Scenario: Building an SSIS ETL Infrastructure
Chapter Summary
Case Scenario Answers
Index
© Microsoft. All Rights Reserved.