Smart Database Design
Paul Nielsen
22nd - 23rd September 2008
Design and development principles for SQL Server 2000/2005 Database Professionals: database architects, data modellers, database developers, web developers, and .NET developers who want to develop high-performance SQL Server database.
Database performance, scalability, and extensibility don't happen by accident.
The truth is that the way the database is designed is the single greatest factor determining the performance of the database.
|
 |
Smart Database Design is the premise that an elegant physical schema makes the data intuitively obvious and enables writing great set-based queries that respond well to indexing. This in turn creates short, tight transactions which improves concurrency and scalability while reducing the aggregate workload of the database.
This flow from layer to layer becomes a methodology for designing, developing, and optimizing databases. |
The core of Smart Database Design is based on the six primary goals of every database:
Usability, Data Integrity, Performance/Scalability, Extensibility, Security, and Availability.
|
|
Every option and pattern for every design and development decision for each layer (schema, queries, indexing, concurrency, adv. scalability) is analyzed by how the option impacts, improves, or risks these six goals.
As you can see, Smart Database Design is more than a collection of best-practices; it's the business and architectural case for the best-practices. |
|
 |
Why Smart Database Design?
Most database architects and developers spend years learning how to design and develop database projects that perform well.
Paul has built a career of turning around failing database projects. He knows what works, what doesn't, and why. With Smart Database Design, you'll learn the key decision points for any database project, how to evaluate the options, and how to both reduce risk and develop the database faster with better results.
And, what's even more important than designing a successful project is knowing why.
You Will Learn:
- How to evaluate database design decisions.
- Which design errors are the most costly in the long run and how to avoid them.
- The keys to designing an elegant, efficient database physical schema that's fast but also easy to query.
- How to avoid the trap of over-normalization and overly complex databases.
- Why denormalization usually hurts performance and when to responsibly denormalize.
- How to select the best T-SQL solution for every type of problem.
- The five situations when a cursor is the best solution.
- The three methods of refactoring complex cursors into set-based solutions.
- How to avoid locking and blocking in the first place, and what to do when it's already a problem.
- How to analyze the database workload and develop a comprehensive indexing strategy.
- Exactly how to best employ clustered and non-clustered indexes.
- The three worst errors in SQL Server development (unnecessary cursor is number 3).
- How to think like a data architect.
Who Should Attend:
- Data Architects who want to design balanced, extensible database
- Data Modellers who want to design high-performance database physical schemas
- Database Developers tasked with improving the performance of a legacy database
- Database Professionals designing and developing a new database
- .NET or web developers who design their own SQL Server databases
Seminar Agenda:
1) Data Architecture
- Defining Data Architecture / Enterprise Data Architecture
- Data Architecture Principle
- Six Database Design Goals
2) Smart Database Design
- RDBMS System
- Smart Database Design Layers
- Applying the Methodology
3) Physical Schema Performance
- The Modelling Process
- Designing Entities
- Normalization and the Rules of One
- Data Design Patterns
- Generalization
- Responsible Denormalization
- Primary and Foreign Keys
- Situational Modelling
- Muck Tables
- Managing Optional Data
4) Set-based Queries
- Recommended Solutions
- Logical Query Flow
- Cursor Strategies and Refactoring Cursors
- Hierarchical Patterns
- T-SQL Best Practices
- Query Plan Reuse
5) Zen and the Art of Indexing
- Index Structures
- Scans, Seeks, and SARGS
- Query Paths
- Base Indexes
- Comprehensive Indexing Strategy
6) Managing Transactions, Locking & Blocking
- Transactional Integrity & ACID
- Isolation Levels and Locks
- Transaction Log Flow
- Locking and Blocking
- Optimistic Locking
- Triggers
7) Advanced Scalability
- Disk Subsystem Planning
- Partitioning
- Indexed Views
- Filtered Indexes
- Data Compression
Course Length & Type
Two day instructor led seminar.
Paul Nielsen
Paul Nielsen is a hands-on database developer, Microsoft SQL Server MVP, and trainer specializing in data architecture and database development using Microsoft SQL Server technologies.
Active in the SQL Server community, Paul is the and founder of Colorado PASSCamp. Paul presents around the world at conferences such as Microsoft Tech Ed (Dev), SSWUG Virtual Conference, SQL Teach (Canada), SQL Open World (Denmark), devLINK (Nashville), and the PASS Summit.
Besides holding several certifications, Paul is an instructor with Learning Tree, served on the Microsoft Education Domain Objectives panel for SQL Server 2005, and was the Design-SME (subject matter expert) for the Microsoft Official Course, 2784: Tuning and Optimizing Queries using Microsoft SQL Server 2005.