contents

preface
acknowledgments
about Operation Smile
about this book
about the editors
about SQL Server MVPs

1 Part 1 Architecture

Edited by Louis Davidson
 
1 Where are my keys?
Ami Levin
Keys in the relational model
The debate
The arguments
Additional considerations
Recommendations
Simplicity and aesthetics
Summary
2 “Yes, we are all individuals” A look at uniqueness in the world of SQL
Rob Farley
Introducing uniqueness
Constrained to uniqueness
Unique constraint or unique index?
Uniqueness in results
Summary
3 Architectural growth pains
Chris Shaw
Manage your data types
Naming conventions
Inconsistent design
Normalization
Primary keys and foreign keys
Indexes
Summary
4 Characteristics of a great relational database
Louis Davidson
Coherent
Normal
Fundamentally sound
Documented
Secure
Encapsulated
Well performing
Summary
5 Storage design considerations
Denny Cherry
Selecting the correct RAID type
File placement
Disk alignment
Snapshots
Clones
Summary
6 Generalization: the key to a well-designed schema
Paul Nielsen
A place for normalization
Lessons from the UIX discipline
Generalization defined
Benefits of generalization
Summary

Part 2 Database administration

Edited by Paul Randal and Kimberly Tripp
 
7 Increasing availability through testing
Allan Hirt
Testing—it’s not just for application functionality
The missing link
Knowledge is power
Test early, test often
Automated versus manual testing
What needs to be tested?
First things first
Remember the big picture, too
Summary
8 Page restores
Gail Shaw
Restore granularities
Requirements and limitations
Performing a page restore
What’s coming?
Summary
9 Capacity planning
Greg Larsen
What is capacity planning?
Gathering current database disk space usage
Performance metrics
Summary
10 Discovering your servers with PowerShell and SMO
Joe Webb
Using PowerShell and Excel
Using SMO with PowerShell
Collecting instance and machine information
Collecting SQL Agent job information
Collecting database information
Summary
11 Will the real Mr. Smith please stand up?
John Magnabosco
Personally identifiable data
Today’s superhero: the DBA
Summary
12 Build your own SQL Server 2008 performance dashboard
Pawel Potasinski
DMVs as the source of performance-related information
Using SQLCLR to get the performance counter values
Sample solution for performance monitoring
Use Reporting Services for performance monitoring
Some ideas to improve the solution
Summary
13 SQL Server cost recovery
Peter Ward
The context for SQL Server as a Service
What’s SQL Server as a Service?
An introduction to chargebacks
Implementing a chargeback model
Summary
14 Best practice compliance with Policy-Based Management
Rod Colledge
The context for contemporary database administration
The importance of best practice compliance
Central Management Servers
Policy-Based Management
Policy-Based Management with Central Management Servers
Summary
15 Using SQL Server Management Studio to the fullest
Rodney Landrum
Querying many servers at once
Creating and using a scripting solution with templates
Scripting multiple objects and now data, too
Summary
16 Multiserver management and Utility Explorer—best tools for the DBA
Satya Shyam K Jayanty
SQL Server 2008 R2 tools for the DBA
Tools of the trade
Managing multiple instances using Utility Control Point
Multiserver management and administration
Best practices
Summary
17 Top 10 SQL Server admin student misconceptions
Tibor Karaszi
Simple recovery model
Default collation
Table-level backups
Using replication for high availability
Timing query performance
Shrinking databases
Auditing login access
Tail-log backups
Database defaults
Difficulty
Summary
18 High availability of SQL Server in the context of Service Level Agreements
Tobiasz Janusz Koprowski
High availability—a definition
Types of unavailability
Unavailability indicators
High availability options in SQL Server
Service Level Agreement
Measurement indicators
The structure of a Service Level Agreement
Service Level Agreements: the context for high availability
Summary
Useful links

Part 3 Database development

Edited by Paul Nielsen
 
19 T-SQL: bad habits to kick
Aaron Bertrand
SELECT *
Declaring VARCHAR without length
Not choosing the right data type
Mishandling date range queries
Making assumptions about ORDER BY
Summary
20 Death by UDF
Kevin Boles
Poor estimates
Row-by-row processing
What can you do about it?
What about code reuse?
One last example of how bad scalar UDFs can be
Summary
21 Using regular expressions in SSMS
John Paul Cook
Eliminating blank lines
Removing extra newline characters
Collapsing multiple lines into a single line
Using the beginning-of-line metacharacter
Using the end-of-line metacharacter
Summary 198
22 SQL Server Denali: what’s coming next in T-SQL
Sergio Govoni
OFFSET and FETCH
SEQUENCE
EXECUTE…WITH RESULT SETS
THROW
Summary
23 Creating your own data type
Hugo Kornelis
Anatomy of a CLR user-defined type
Building the data type: the bare basics
Summary
24 Extracting data with regular expressions
Matija Lah
Understanding before coding
The solution
Homework
Summary
25 Relational division
Peter Larsson
Why use relational division?
Defining relational division
Background
Sample data for two simple cases
Comparison charts
Let’s go on with the real stuff
Set-based solution to common relational division
Does one query exist for all types of relational division?
Summary
26 SQL FILESTREAM: to BLOB or not to BLOB
Ben Miller
To FILESTREAM or not to FILESTREAM
Configuring FILESTREAM in SQL Server
Database configuration
Creating a table that uses FILESTREAM
Things to consider
How do I use FILESTREAM?
Summary
27 Writing unit tests for Transact-SQL
Luciano Moreira
Unit test basics
Automating unit test execution
Summary
28 Getting asynchronous with Service Broker
Mladen Prajdic
The Service Broker usage template
Creating Service Broker objects
Summary
29 Effective use of HierarchyId
Denis Reznik
Hierarchies in a database
Introduction to the HierarchyId data type
Effective indexing
More information about HierarchyId
Summary
30 Let Service Broker help you scale your application
Allen White
Scalable solutions
Service Broker objects
ETL trigger demonstration
Summary

Part 4 Performance tuning and optimization

Edited by Brad M. McGehee
 
31 Hardware 201: selecting and sizing database server hardware
Glenn Berry
Why database server hardware is important
Scaling up or scaling out
SQL Server and hardware selection
Database server–specific hardware factors
Intel vs. AMD processors
Memory recommendations
Traditional storage subsystems
New developments in storage subsystems
Benchmarking and sizing tools
Summary
32 Parameter sniffing: your best friend…except when it isn’t
Grant Fritchey
Understanding parameter sniffing
Parameter sniffing gone wrong
Dealing with bad parameter sniffing
Summary
33 Investigating the plan cache
Jason Strate
Plan cache dynamic management objects
Investigating missing indexes
Investigating index usage
Investigating operations
Investigating index scans
Investigating parameters
Plan cache considerations
Summary
34 What are you waiting for? An introduction to waits and queues
Robert Pearl
Introduction to total response time
What are wait stats?
The execution model
Viewing and reporting on wait statistics
Calculating wait time: signal waits vs. resource waits
Correlating performance data: putting it together
Summary
35 You see sets, and I see loops
Linchi Shea
What loops?
The loop perspective
Loops in a query execution plan
Loops in complex queries
User-defined scalar functions in implicit loops
Merging multiple loops into one
Parallelizing loops
Linked server calls in a loop
Squeezing the fat out of loops with a slim table
Summary
36 Performance-tuning the transaction log for OLTP workloads
Brad M. McGehee
How can the transaction log be a bottleneck?
Factors that contribute to transaction log bottlenecks
Determining whether the transaction log is a bottleneck
Strategies for dealing with transaction log I/O bottlenecks
Summary
37 Strategies for unraveling tangled code
Jennifer McCown
Organize: make it readable
Break down: what does it do?
Streamline: resolve obvious issues
Streamline: optimize joins
Streamline: similar subqueries and queries
Streamline: dynamic SQL
Summary
38 Using PAL to analyze SQL Server performance
Tim Chapman
Performance Analysis of Logs (PAL)
Using PAL with SQL Server
Capturing performance counter data
Performing PAL analysis
The PAL report
Summary
39 Tuning JDBC for SQL Server
Jungsun Kim
JDBC performance tuning can be effective
Recommendations for tuning jTDS configuration
Unicode character issues
API cursor issues
ResultSet Type issue
Prepared statement mechanism issue
Controlling global configuration
Summary

Part 5 Business intelligence

Edited by Greg Low
 
40 Creating a formal Reporting Services report part library
Jessica M. Moss
Report parts defined
Deciding to create a report part library
Selecting report parts
Using a report part library
Summary
41 Improving report layout and visualization
Greg Low
Target-based rendering
Control over pagination
Joining across datasets
Aggregates of aggregates
Writing mode
Domain scope
Databars
Sparklines
Summary
42 Developing sharable managed code expressions in SSRS
William (Bill) Vaughn
Coding report expressions
Creating sharable managed code expressions
Referencing DLLs from your report
Summary
43 Designing reports with custom MDX queries
Paul Turley
MDX query builder
Building a report
Handling parameters
Summary
44 Building a scale-out Reporting Services farm
Edwin Sarmiento
What is network load balancing?
Preparing your network
Adding the network load balancing feature
Creating the NLB cluster
Adding hosts to the NLB cluster
Installing Reporting Services on the NLB cluster
Configuring the first Reporting Services instance
Configuring the second Reporting Services instance
Joining the second Reporting Services instance
Configuring view state validation
Configuring the hostname and UrlRoot
Workarounds for the HTTP 401 error message
Summary
45 Creating SSRS reports from SSAS
Robert Cain
Creating the report project
Adding a shared data source
Creating shared datasets
The report
Summary
46 Optimizing SSIS for dimensional data loads
Michael Coles
Optimization quick wins
Type 0 dimensions
Type 1 SCDs
Type 2 SCDs
Summary
47 SSIS configurations management
Andy Leonard
Building the demo database
Starting in the middle
Changing the connection
Externalizing the connection
Taking a step back
Abstracting a bit
Summary
48 Exploring different types of enumerators in the SSIS Foreach Loop container
Abolfazl Radgoudarzi and Shahriar Nikkhah
Make it dynamic
Summary
49 Late-arriving dimensions in SSIS
John Welch
A late-arriving dimension scenario
Natural keys and surrogate keys
The example data structure
Working around late-arriving dimensions
Handling late-arriving dimension members in fact processing
Processing the dimension update
Summary
50 Why automate tasks with SSIS?
Ted Krueger
Automation by example
SSIS key tasks and components
Creating reusable and mobile SSIS packages
Precedence and manipulating control flow
Monitoring the results
Summary
51 Extending SSIS using the Script component
Tim Mitchell
The Swiss Army knife of SSIS
Summary
52 ETL design checklist
Rafael Salas
Discovering data realities
Extract phase
Transform phase
Load phase
ETL system instrumentation and management
Summary
53 Autogenerating SSAS cubes
Johan Åhlén
Background
Developing InstantCube, a simple cube generator
Summary
References
54 Scripting SSAS databases – AMO and PowerShell, Better Together
Darren Gosbell
Advantages of PowerShell
Advantages of compiled code
Automating processing
Repetitive design changes
Summary
55 Managing context in MDX
Boyan Penev
Named sets
Calculated members
Scope assignments
Summary
56 Using time intelligence functions in PowerPivot
Thiago Zavaschi
Introducing Data Analysis Expressions
Time intelligence functions
Samples
Summary
57 Easy BI with Silverlight PivotViewer
Gogula Aryalingam
Presenting Silverlight PivotViewer
What makes up your pivot
The way to go
Summary
58 Excel as a BI frontend tool
Pedro Perfeito
Key points to consider when choosing a BI frontend tool
Why Excel?
Why use OLAP as a source?
Dashboard implementation
Summary
59 Real-time BI with StreamInsight
Allan Mitchell
What is StreamInsight?
What are events and event streams?
Deployment
Architecture
How does querying streams differ from querying an RDBMS?
Where is StreamInsight useful?
Querying the streams
Summary
60 BI solution development design considerations
Siddharth Mehta
Architecture design
Other aspects influencing architecture design
Solution development
Summary

index