Module 1 - Server Solutions with SQL Server 2005
Server Solutions with SQL Server 2005
There is More to SQL Server Than Just SQL Server
Full-Text Services
Native XML Web Services
Replication Concerns
SQL Server Agent
New Database Mail
Demo - Turning on Database Mail
Notification Services
Service Broker
Reporting Services
SQL Server Integration Services
T-SQL Enhancements
Demo - Ranking Function
.NET CLR
SQLXML
Review
Module 2 - Security – Its More Than Just a Password
Security – Its More Than Just a Password
Security Concerns
Demo - SQL Injection
Who Are You?
Preferred Approach Toward Securing Native Web Services
Preferred Approach Toward Code Access Security
Preferred Approach Toward Reporting Services Security
Preferred Approach Toward Notification Services Security
Preferred Approach Toward Integration Services Security
Preferred Approach Toward Replication Security
Preferred Approach Toward Agent and Database Mail Security
Protect Objects With Roles and Schemas
Demo - Revoke Is Not Enough
Execution Context
Protect Stored Procedures, Views, and User Defined Functions
Protect Columns
How to Audit SQL Server
Protect your Audit
Not All Employees Are Equal
Demo - Remove Built-in Admins
Use DDL Triggers for Audit Information
Demo - Protect Tables From Oops!!
Review
Module 3 - Plan for the Future by Planning for Today
Plan for the Future by Planning for Today
Using XML Requires Forethought
XML Has Redundant Data
How to Store – Character Type vs. New XML Type
Demo -What Impact Does XML Storage Have on Storage?
Model Your Database Solution
Balancing with OLAP
Rules of Normalization and When
Can I Index to Often?
Demo - Include Implications
Clustered Indexes on Non-Primary Keys
Scalability Up or Out
Spreading the Databases
Partition Tables and Indexes
Redundant Solutions with Scaling Out
Scaling Up
Review
Module 4 - Transaction – Concurrency and Consistency
Transaction – Concurrency and Consistency
Exploring Data Phenomenon
Isolation Levels Options
Demo - Connection and Database Options for Row Versioning
Change Data Processing Plans
Balance Locks and Performance as a Result of Errors
Accessing Objects
Rollback Transactions Correctly
Long Running Transactions
Hints or Directives
Review
Module 5 - Defining a Push Solution with Notification Services
Defining a Push Solution with Notification Services
Where and How to Store Data
What is an Event Schema
How does Indexing Help?
Archiving Events
Demo - Subscription Classes
Plan Toward a Subscription Schema
Plan the Rules
Plan the Subscription Indexes
Maintain Subscriptions
Demo - Event Classes
Demo - Notification Classes
Define the Notification
Plan the Notification Indexes
Plan Toward Notification Delivery
Protocol Planning
Setting Execution Settings
Review
Module 6 - Reliable Messaging with Service Broker
Reliable Messaging with Service Broker
Why Use Service Broker?
Locate Services
Locate Conversations
Plans for Dialogs
Plan for Queue Usage
Demo - Preparing Communication
Plan Toward Good Data Flow
Locate Data Staging
Locate Routes
Demo - Messaging
Plan for Activating Methods
Backup Planning
Review
Module 7 - Protect, Test, and Execute
Protect, Test, and Execute
Source Control Saves Time and Effort
Demo - Using Visual SourceSafe
Plan for Source Control
Protect Intellectual Property
Unit Tests Provide Low Level Confidence
Planning for Unit Tests
Perform Iterative Development
Compare Performance to Known Levels
Know What To Measure
Know How To Measure
Demo - Combining SQL Profiler and PerfMon
Adapting to Change
Put Services Where They Need to Be
Plan for Deployment
Module 7 Review
Module 8 - T-SQL Programming Enhancements
T-SQL Programming Enhancements
Common Table Expressions
Performance Gains over Sub-Queries?
Demo - Using CTEs
Pivot Queries
Demo - Pivot Queries at Work
Ranking Queries
Demo - Ranking Query Usages
XQueries and FLWORs
Planning for XQueries
Follow the Path
WHERE Matters
Steps in Converting Data
To XML…
…And Back Again
Review
Course Closure