Smart Database Design
Paul Nielsen
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
Why should I attend?
If you want to design a high performance database then this is the seminar for you. If you are struggling with a poorly performing database and want to get an idea of where to start looking then this is the seminar for you. If you want to know why cursors can be your friends and your enemies then this is definitely the seminar for you.
Course Length & Type
Two day instructor led seminar.
Location
22nd - 23rd September 2008 de Havilland, Hatfield
de Havilland Conference, Hatfield Hertfordshire. Directions map PDF, and Vistor Information leaflet.
(TBC)
Registration
Register before 8th September 2008 and receive your early bird discount:
- £300 + VAT before 8th Sep
- £398 + VAT full rate
Discounts
Early bird discounts must be booked before 08 September 2008,
and full payment must be received by 15 September 2008 to qualify for the discount.
We offer a discount of 5% for 2 - 4 places and a 10% discount for 5 or more places booked.
Discount bands apply to the total number of places booked, they are not restricted to an individual attended or course, i.e. booking two people on one course or one person on two courses will both qualify the 5% discount for 2 places booked.
For full details please see our Event Terms & Conditions
Registration
Seminar include lunch and refreshments. Rates are exclusive of VAT.
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.
Events
- Best Practices in Performance and Availability for SQL Server 2005/2008 Hatfield, Hertfordshire, 1st - 3rd September 2008
- Indexing for Performance in SQL Server 2000/2005/2008 Edinburgh, 8th - 9th September 2008
- Smart Database Design Hatfield, Hertfordshire, 22nd - 23rd September 2008
- Smart Database Design Edinburgh, 29th - 30th September 2008
- SQL Server Data Storage Formats: Internals, Performance and Best Practices Harpenden, Hertfordshire, 3rd November 2008
- SQL Server Concurrency Control: Locking, Blocking and Row Versioning Harpenden, Hertfordshire, 4th November 2008
- SQL Server Data Internals and Tuning Harpenden, Hertfordshire, 5th - 7th November 2008
- Making the most of data through Business Intelligence Harpenden, Hertfordshire, 12th September 2008
- Using .Net inside SQL Server Harpenden, Hertfordshire, 12th September 2008
Training Courses
SQL Server Integration Services (SSIS) Deep Dive - 4 Day Training Course