Learn your way! Get started

SQL Server 2005: Additional Topics

with experts Frank Tillinghast, Joshua Gins

Course at a glance

Included in these subscriptions:

Dev & IT Pro Video
Dev & IT Pro Power Pack

	Release date	9/26/2007
	Level	Intermediate
	Runtime	18h 45m
	Closed captioning	N/A
	Transcript	N/A
	eBooks / courseware	Included
	Hands-on labs	Included
	Sample code	Included
	Exams	Included
	Platform	Internet Explorer Only

Enterprise Solutions

Need reporting, custom learning tracks, or SCORM? Learn More

Course description

Developing a database, getting the database to work with your applications, and getting it to production is just the beginning, but can often be where the story ends. This course examines other SQL Server 2005 essential application, support, and maintenance topics—features you can use for high-availability, stability, reliability, and scalability of databases, plus enhancements for processing large amounts of data, handling complex data structures, implementing asynchronous messaging, and working with XML.

Meet the experts

Frank Tillinghast is a senior consultant with MTOW Software Solutions. He is a Microsoft Certified Solution Developer and has been developing applications for over 15 years. Most of his time is spent consulting for companies nationwide with troubled projects or mentoring projects to successful completion. When he is not developing software or mentoring clients, Frank is teaching other developers. He has taught thousands of application developers how to create business solutions with Visual Studio .NET. VB.NET, ASP.NET, Visual C#, and SQL Server.

Joshua Gins is an MCSD and experienced consultant with over 15 years of application development experience. Most of his time is spent consulting for companies nationwide. When he is not developing software or mentoring clients, Joshua is teaching other developers. He has taught over 3,500 application developers to create business solutions with Visual Studio.NET, Visual Basic .NET, ASP.NET, Visual C#, and SQL Server.

Course outline

Module 1

Plan Backup Strategies (26:42)

Introduction (01:33)
Overview (02:20)
Access Your Needs (01:14)
Data Recovery Needs (02:15)
Availability Demands (02:09)
Data Usage (00:33)
Database Size (00:32)
Server Configuration (01:29)
Disaster Recovery Testing (01:32)
Recovery Considerations (01:02)
SQL 2K5 Backup Options (01:35)
Backing up Databases (00:18)
Backup Devices (00:32)
Media Sets (02:28)
Backup Sets (03:43)
Demo: Create Backup (03:01)
Summary (00:18)

Create Backups (33:30)

Introduction (00:33)
Overview: Backups (01:49)
Demo: Perform a Backup (02:11)
Backup Recommendations (01:16)
Recovery Models (00:38)
Simple Model (02:12)
Full Model (02:16)
Bulk-Logged Model (03:30)
Types of Backups (00:23)
Data Backup (00:30)
Full Data Backup (00:32)
Partial Data Backup (01:17)
File Data Backup (01:04)
Demo: Data Backups (03:57)
Differential Backup (03:18)
Transaction Log Backup (01:50)
Tail-log Backup (00:43)
Copy-Only Backup (01:10)
Demo: Differential Backup (01:35)
Demo: Trans. Log Backup (00:58)
Demo: Copy-Only Backup (01:17)
Summary (00:18)

Schedule Backups (11:01)

Introduction (00:34)
Overview (00:41)
System DB Backups (01:05)
Simple Backups (00:45)
Full Backups (01:40)
Related Databases (01:02)
Verify the Media (00:48)
Use Checksums (01:29)
Use RESTORE VERIFYONLY (00:54)
Demo: Backup w/SSMS (00:51)
Demo: Backup w/TSQL (00:46)
Summary (00:18)

Module 2

Restore Databases (29:17)

Introduction (00:32)
Overview (00:46)
Roll Forward Set (00:31)
Simple Recovery (01:01)
Full/Bulk-logged (00:35)
Transaction Tail Log (02:03)
Restore Database (00:42)
Demo: Simple Recovery (03:23)
Demo: Full Recovery (06:12)
Restore Files/FileGroups (01:03)
Restore Pages (01:30)
Restore Point-in-time (01:10)
Demo: Files/FileGroups (02:38)
Demo: Pages (02:26)
Demo: Point-in-time (04:23)
Summary (00:15)

Snapshots (10:00)

Introduction (00:41)
Overview (01:07)
Copy-on-write (01:11)
Create Snapshot (01:50)
Revert to a Snapshot (01:29)
Delete Snapshot (00:26)
Demo: Snapshots (02:58)
Summary (00:16)

High Availability / Failover (12:07)

Introduction (00:52)
Overview (01:21)
Availability Levels (01:49)
Est. Availability Reqs (01:41)
Four Availability Features (00:47)
Failover Cluster Mission (00:25)
Failover Topology (00:38)
Failover Cluster Topology (01:13)
Failover Features (01:44)
Failover Limitations (00:16)
Failure Cluster Option (00:43)
Summary (00:34)

Clusters (43:18)

Introduction (00:34)
Overview: Clusters (01:52)
Cluster Key Terms (04:17)
Requirements: Hardware (01:47)
Reqs: Operating System (00:26)
Reqs: SQL Server Versions (00:50)
Reqs: Cluster Network (00:49)
Requirements: Software (00:49)
Requirements: Disks (00:40)
Special Considerations (01:50)
New Clusters (01:05)
Cluster Service Account (01:26)
Create Cluster Admin Account (02:04)
Prepare Static IP Addresses (01:39)
Node Setup Steps (04:45)
Demo: Node Setup (04:58)
Create New Cluster (05:01)
View Cluster Administrator (00:27)
Rename Groups (01:56)
Add Distributed Trans. Coord. (02:15)
Add Network Name (03:12)
Summary (00:25)

Module 3

Install SQL Server w/Failover (20:55)

Introduction (00:35)
Expectations (01:17)
Set Up Domain Accounts (01:36)
Run the Setup (00:26)
Demo: Set Up Accounts (02:50)
Demo: Install SQL Server (05:19)
Demo: Cluster Administrator (05:23)
Post-Install Considerations (01:46)
Overview: Failover Process (01:15)
Summary (00:22)

Add/Remove Cluster Nodes (25:14)

Introduction (00:42)
Add Additional Nodes (01:14)
Demo: Config Server (02:54)
Demo: Add a Node (05:14)
Add SQL Server to Node (01:22)
Demo: Add SQL Server (03:45)
Configure Failover (01:54)
Test Failover (03:06)
Remove Additional Nodes (01:18)
Evict a Node (00:31)
Demo: Remove a Node (02:34)
Summary (00:36)

Database Mirroring (39:10)

Introduction (00:46)
Overview (01:59)
Understand Mirroring (02:58)
Witness Server (00:43)
Review: Key Terms (01:05)
Demo: Setup Simple Mirror (05:37)
Session Log Chains (01:08)
Pause and Resume (01:35)
Multiple Sessions (01:08)
Operating Modes (01:57)
Transaction Safety (01:53)
High-Availability Mode (04:35)
High-Protection Mode (00:50)
High-Performance Mode (01:36)
Role Switching (Failover) (00:59)
Automatic Failover (02:49)
Manual Failover (00:34)
Forced Service (01:02)
Other Mirror Details (00:41)
Mirror Endpoints (03:13)
Client-side Redirection (01:30)
Summary (00:22)

Module 4

Configure Mirroring (SSMS) (18:57)

Introduction (00:28)
Prerequisites (02:49)
Config Recommendations (01:18)
Demo: Create db on Mirror (03:26)
Demo: Config Mirroring (04:20)
Demo: Start Mirroring (00:50)
Demo: Pause Mirroring (00:39)
Demo: Test Failover (03:51)
Demo: Remove Mirroring (00:35)
Summary (00:37)

Configure Mirroring (T-SQL) (34:23)

Introduction (00:35)
Setup Principal u/T-SQL (01:16)
Demo: Create db on Mirror (02:33)
Demo: Config Mirroring (00:06)
Demo: Setup Endpoints (04:40)
Demo: Setup Principal (01:22)
Demo: Setup Witness (00:33)
Demo: Start Mirroring (00:31)
Demo: Test Mirroring (00:04)
Demo: Pause Mirroring (00:50)
Demo: Resume Mirroring (00:33)
Demo: Test Failover (00:52)
Demo: Remove Witness (01:39)
Demo: FORCE_SERVICE (02:04)
Demo: Create Snapshot (02:02)
Demo: Remove Mirroring (00:58)
Monitor Mirroring (02:49)
Demo: Monitor Mirroring (05:15)
Demo: T-SQL Views (05:16)
Summary (00:15)

Log Shipping (43:32)

Introduction (00:49)
Overview (04:19)
Log Shipping Requirements (01:42)
Security Requirements (01:06)
Limitations (02:44)
Failover (01:31)
Configuration Tasks (00:36)
Resources (01:24)
Backup Settings (00:58)
Setup Log Shipping (01:53)
Configure Security (11:28)
Setup SQL Server Logins (01:38)
Setup Primary Database (00:23)
Backup Settings (01:46)
Secondary Database (03:38)
Restore Transaction Log (03:10)
Monitor Server (01:02)
Test Log Shipping (02:49)
Summary (00:27)

Module 5

Monitor Log Shipping (34:58)

Introduction (00:30)
Overview: Log Shipping (01:56)
Log Ship System Tables (03:43)
Demo: Log Shipping (00:08)
Demo: View Agent Jobs (01:44)
Demo: Job Activity Monitor (01:01)
Demo: Ship Status Report (03:28)
Demo: Queries/Stored Procs (06:07)
Demo: Remove Log Ship (01:27)
Using T-SQL (05:07)
Demo: Log Ship u/T-SQL (09:13)
Summary (00:29)

Peer-to-Peer Replication (23:35)

Introduction (00:52)
Overview (03:42)
Typical Peer-to-Peer (01:39)
Requirements/Limitations (00:59)
Features Not Available (00:33)
Partitioning (01:29)
Using Identity Columns (02:34)
Config Peer-to-Peer Pubs (01:04)
Security Planning (01:33)
Replication Agents (01:29)
Publication Access List (PAL) (01:14)
PAL Security Details (02:00)
Setup Remote Distributor (03:49)
Summary (00:33)

Config Peer-to-Peer Rep (30:29)

Introduction (00:48)
Overview: Distributor (01:26)
Demo: Config Distributors (07:04)
Publisher (00:49)
Demo: Config Publishers (00:45)
Specify Distributor (02:49)
Specify db to Replicate (00:10)
Specify Type of Publication (00:20)
Specify Articles to Publish (01:10)
Specify Agent Security (03:59)
Replication Monitor (01:31)
Log Reader Agent (00:21)
View Agent Job History (01:31)
Config Security Settings (06:14)
Test the Publisher (00:57)
Summary (00:29)

Module 6

Peer-to-Peer Topology (31:55)

Introduction (00:34)
Initialize the Server (03:10)
Restore the db (02:00)
Config Topology (00:31)
Specify Publication (00:10)
Specify Peer(s) (00:43)
Specify Log Reader Agent (01:13)
Specify Dist Agent (01:00)
Specify Peer Initialization (01:05)
Specify Security (02:21)
Set Identity Column (00:54)
Test Replication (01:10)
View Replication Monitor (00:27)
Add a Node in Topology (00:46)
Demo: Add/Config Node (10:15)
Demo: Well Connected (05:05)
Summary (00:23)

Diagnose / Resolve DB Errors (27:06)

Introduction (00:43)
Overview (00:31)
Dedicated Admin Connection (03:11)
Remote DAC Connections (00:32)
Limits / Restrictions (00:45)
DAC: Recommended Usage (01:12)
DAC Port (00:42)
Demo: DAC and SSMS (01:53)
Demo: DAC and SQLCMD Util. (01:35)
Demo: Remote Connections (00:56)
Demo: Using DAC (05:33)
SQL Server Logs (01:25)
Monitor Error Log (00:20)
Log Entry Fields (01:45)
Filtering (01:16)
Error Msgs in Event Logs (00:30)
Filter Event Logs (00:22)
Demo: Error / Event Logs (03:26)
Summary (00:20)

Using Alerts / History (14:10)

Introduction (00:41)
Alerts w/SQL Svr Agent (01:03)
Advantages of Alerts (00:28)
Setup Alerts (00:31)
Create Alerts (00:12)
New Alert Dialog (00:11)
Specify Perf. Condition Type (00:44)
Specify WMI Event Type (00:15)
Specify Response / Options (00:36)
Monitor Agent Job History (00:33)
Job Activity Monitor (00:50)
Demo: Create New Alert (05:41)
Demo: Agent History (01:18)
Demo: Job Activity Monitor (00:43)
Summary (00:18)

Module 7

Dynamic Management Views (37:50)

Introduction (00:44)
Overview (06:28)
Demo: DMVs (06:12)
Troubleshooting Bottlenecks (03:03)
Waits and Queues (00:29)
DMV Wait Statistics (01:34)
DMV Perf. Counters (00:31)
Demo: Perf. Count/DMV Waits (03:46)
Collect Info u/DMV (00:59)
Dashboard Reports (01:53)
Demo: Dashboard Reports (05:22)
SQL DMVStats (01:29)
Collect Query Stats (00:34)
Demo: SQL DMVStats (04:19)
Summary (00:21)

Intro to Service Brokers (12:12)

Introduction (00:48)
Overview (00:45)
SB Advantages (02:22)
SB Applications (01:08)
Anatomy of SB Applications (01:30)
SB Messaging (00:30)
SB Conversation (00:26)
Messaging Conv. Groups (00:58)
Transactional Messaging (01:06)
SB Uses (02:13)
Summary (00:20)

Service Broker Architecture (26:19)

Introduction (00:41)
Overview (00:52)
Planning Message Types (01:30)
SB Activation (01:03)
Startup / Schedule Tasks (01:33)
Internal Activation (01:45)
External Activation (01:04)
Transaction Life Cycle (00:33)
Initiator/Send Life Cycle (00:31)
Target Life Cycle (01:07)
Receive Msg from Queue (01:11)
Validate Content (01:27)
Process Message (00:39)
Using State (01:37)
Handle Errors (02:19)
Handle Poison Messages (01:55)
SB Security (00:53)
Transport Security (00:34)
Use Master Keys (00:26)
Login Account (00:31)
Service Certificate (00:16)
SB Endpoints (01:24)
Dialog Security (01:05)
Create Remote Login (00:42)
Summary (00:30)

Module 8

Program Service Broker Apps (02:37:41)

Introduction (00:43)
Overview (01:00)
Enable Service Broker (00:37)
Create Message Types (01:09)
Create Contract (02:46)
Create Queue (01:00)
Create Queue: Activation (01:24)
Create Queue: Execute As (02:03)
Create Service (01:49)
Create Route (01:12)
Create Route: Address (02:53)
Create SB Endpoints (56:59)
Demo: Setup a Service (02:49)
Demo: Define Msg Types (02:09)
Demo: Define Contract (00:50)
Demo: Setup Queues (05:33)
Demo: Setup Endpoints (01:32)
Demo: Create Route (01:32)
Demo: Setup Services (01:57)
Implement Apps (01:18)
Create Dialog Conversations (02:39)
Sending Messages (33:06)
Receive Messages (02:56)
Send Response (00:37)
End Conversation (01:40)
Demo: Additional Demos (24:52)
Summary (00:24)

Partitions (15:47)

Introduction (00:45)
Table-based Partitioning (00:39)
Why Partition? (01:45)
Partition History in SQL Server (05:07)
Range Partitions (01:05)
Partition Key (01:55)
Multiple Filegroups (02:43)
Index Partitioning (01:24)
Summary (00:19)

Module 9

Create Partitioned Tables (20:34)

Introduction (00:38)
Overview (00:51)
Create Partition Function (00:49)
LEFT/RIGHT Boundaries (02:58)
Demo: Partition Function (01:46)
Datetime Values (01:15)
Partition Scheme (04:17)
Demo: Partition Scheme (01:11)
Create Table/Index (02:25)
Demo: Create Table/Index (03:53)
Summary (00:25)

Query Partitions (37:32)

Introduction (00:41)
Overview (01:01)
$PARTITION Function (02:31)
Demo: $PARTITION (05:41)
Catalog Views (01:25)
sys.partition_functions (00:42)
sys.partition_range_values (00:18)
sys.partition_schemes (00:22)
sys.data_spaces (00:22)
Demo: Catalog Views (02:26)
Manage Partitions (00:30)
Mod Part Tables/Indexes (00:28)
Mod Partition Function (01:56)
Split Partitions (03:36)
Merge Partitions (03:44)
Switch Partitions (03:19)
Modify Partition Scheme (01:39)
Backup Partitions (02:13)
Performance Considerations (02:29)
Summary: Partitions (01:33)
Summary (00:26)

Work with Nulls (24:47)

Introduction (00:37)
Overview (00:18)
SqlTypes vs. CLR Types (03:35)
System.Data.SqlTypes (02:34)
Review: SqlTypes/CLR Types (01:41)
ANSI_NULLS Option (00:33)
Demo: ANSI_NULLS Option (04:28)
NULLs and SqlBoolean (02:12)
Demo: NULLs and SqlBoolean (02:31)
Assign NULL Values (00:39)
CLR Integration (00:23)
Direct Assignment (00:50)
NULLIF / COALESCE (00:55)
Demo: Assign NULL Values (01:18)
Demo: COALESCE (01:42)
Summary (00:23)

Module 10

Ranking (14:21)

Introduction (00:33)
Grouped Data (00:55)
ROW_NUMBER (01:15)
RANK (01:09)
DENSE_RANK (00:32)
Demo: ROW_NUMBER (01:57)
Demo: RANK (02:05)
Demo: DENSE_RANK (01:15)
NTILE (02:10)
Demo: NTILE (02:09)
Summary (00:15)

Correlated Subqueries (19:00)

Introduction (00:32)
Overview (00:58)
Subquery Basics (01:15)
Correlated Subquery (03:12)
Where Clause (01:37)
ANY / SOME / ALL Modifiers (01:02)
Demo: Subquery (01:31)
HAVING (02:03)
UPDATE (01:26)
JOINS / Temp Tables (04:59)
Summary (00:18)

CTE"s (18:30)

Introduction (00:41)
Overview (01:22)
CTE Syntax (03:03)
Recursive CTE (05:13)
Demo: Recursive CTE (07:55)
Summary (00:13)

Complex Data / Structures (27:20)

Introduction (00:36)
Overview (00:24)
Data Type Precedence (02:15)
Explicit Conversion w/CAST (01:32)
IMPLICIT Conversion (01:02)
Multinational Data (03:14)
Collation Labels (01:52)
Collation Precedence (02:06)
Demo: COLLATE Clause (03:30)
Query Hierarchical Data (01:37)
Multi-Referential (00:49)
Adjacency List (02:28)
Demo: CTE w/Recursion (05:26)
Summary (00:24)

Write Efficient Queries (19:32)

Introduction (00:41)
Overview (01:37)
EXISTS Clause (02:05)
Demo: EXISTS Clause (03:12)
JOINS vs. Subqueries (05:07)
One-Pass Queries (01:00)
Demo: One-Pass Queries (05:20)
Summary (00:26)

Module 11

Write Efficient Queries 2 (25:41)

Introduction (00:33)
Multiple ResultsSets (00:58)
Temporary Tables (04:54)
Table Variables (04:29)
UDFs (03:31)
Demo: UDFs (10:43)
Summary (00:30)

Write Efficient Queries 3 (20:23)

Introduction (00:35)
Common Table Expression (01:34)
Demo: CTEs (03:40)
Worktables (01:44)
Demo: Worktables (12:31)
Summary (00:17)

Working w/Complex Query (23:03)

Introduction (00:40)
Overview (00:58)
Implicit Transactions (03:48)
Demo: Implicit Transactions (03:35)
Use Sub Tasks (03:33)
Demo: Use Sub Tasks (10:08)
Summary (00:18)

Maintain Query Files (27:03)

Introduction (00:37)
Overview (01:23)
Source Control (02:05)
Visual SourceSafe (02:24)
Put Files in Source Control (00:45)
Demo: Source Control (06:26)
Keyword Expansion/Comment (02:44)
Enable Keyword Expansion (01:15)
Demo: Keyword Expansion (02:37)
Version Information (01:18)
Demo: Retrieve Version Info (03:04)
Demo: Compare Versions (01:54)
Summary (00:25)

Module 12

XML Data Types (25:19)

Introduction (00:32)
Overview (01:01)
Declare XML Objects (00:57)
Demo: XML Data Type (02:51)
XML Data Type Restrictions (01:54)
Load XML Data (00:43)
Demo: Load XML Data (02:09)
SELECT...FOR XML (01:44)
Demo: SELECT...FOR XML (02:07)
Bulk Load w/OPENROWSET (02:13)
Demo: OPENROWSET (02:45)
Indexing Columns (03:42)
Demo: Indexing Columns (02:08)
Summary (00:26)

XML Schema Collections (25:32)

Introduction (00:43)
Overview (00:58)
Typed/Untyped XML (01:30)
Schema Basics (01:08)
Demo: Examine XSD File (04:26)
Register Schemas (02:39)
Demo: Register Schema (06:55)
View Stored Schema (01:44)
Catalog Views/Functions (00:27)
sys.xml_schema_collections (00:29)
sys.xml_schema_namespaces (00:25)
sys.xml_schema_components (01:33)
xml_schema_namespace (02:01)
Summary (00:28)

Query XML (26:15)

Introduction (00:37)
Overview (00:41)
XQuery (01:21)
query() Method (00:26)
value() Method (00:53)
exist() Method (00:45)
modify() Method (00:26)
nodes() Method (00:40)
Demo: XQuery (04:58)
FOR XML (01:05)
OPENXML (03:37)
Demo: FOR XML (05:48)
Demo: OPENXML (04:32)
Summary (00:21)

Best Practices (10:07)

Introduction (00:39)
XML Support (03:20)
Storing XML (01:31)
Index XML Columns (02:30)
Querying (01:33)
Summary (00:32)