DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to learn SQL Server 2008 administration from video?

The following is the sqlusa.com screencast video course syllabus for SQL Server 2008 administration. Lesson video can be paused at any time so that you can practice along on your own copy of SQL Server. Read accompanying books for in-depth understanding of the subject.

Note: Training videos are distributed on 8GB/16GB USB memory sticks which are more reliable than CD-s.

Audience: SQL Server database administrators/experts

CD 1 - OVERVIEW (2 hours 50 minutes)
1. Introduction and Welcome Notes
2. Your First SQL 2008 Email Report to the CEO
3. 2008 the Feature-Rich Sequel to 2005
4. What is New in SQL Server 2008
5. History of Relational Database Technology & SQL
6. Microsoft DBA Career Opportunity
7. SQL Server 2008 for Developers
8. The Advantages of the SQL Language
9. Enterprise Scale, Performance & Responsiveness
10. Data Warehousing & Business Intelligence
11. Microsoft's New BI Drive: BI for the Masses
12. New SSIS Data Profiling Task & DW Population
13. Enhanced OLAP Analysis Scale & Performance
14. Enterprise Reporting Engine & MS Word Reports
15. Temporal, Spatial Data Types & IntelliSense
16. Ten Guidelines for DBA & Developer Excellence


CD 2 - MANAGEMENT TOOLS (3 hours 2 minutes)
1. Editions of SQL Server 2008
2. Installing & Configuring SQL Server 2008
3. Installing & Configuring Part Two
4. Installing & Configuring Part Three
5. Program Group & Creating Desktop Shortcuts
6. First Look from Management Studio
7. SQL Server 2008 Upgrade Advisor
8. SQL Server Configuration Manager and Facets
9. Installing AdventureWorks2008 Database
10. SSIS Import and Export Wizard
11. Familiarizing with Query Editor
12. Books Online & the Graphical Query Designer
13. SQL Server Profiler & Tuning Advisor
14. Business Intelligence Development Studio - BIDS
15. Analysis Services - SSAS
16. Reporting Services - SSRS


CD 3 - ENTERPRISE SECURITY (3 hours 2 minutes)
1. Principals, Securables and Permissions
2. Metadata Visibility by Principals
3. Setting up Logins with Management Studio
4. Setting up Logins, DB Users, Roles with Statements
5. Fixed Server Roles and Permissions
6. Policy-Based Management for Better Security
7. DB User and Role Management with MS
8. Creating Security Accounts in the Enterprise
9. SCHEMAs and Security Hierarchy
10. Transparent Data Encryption (TDE) of Data
11. Extensible Key Management & Data Auditing
12. CREATE CERTIFICATE, Encryption and Decryption
13. 3DES Encryption of Credit Card and Bank Data
14. Asymmetric Key - Public Key & Private Key
15. Clear Text Encryption and Decryption
16. RSA 512-bit, 1024-bit & 2048-bit Encryptions


CD 4 - DATABASE BACKUP (3 hours 5 minutes)
1. Site Server Architecture and Deployment
2. RAID Disk Storage Architecture - SAN
3. Storage Offers from Major Computer Manufacturers
4. Dell SQL Server Database Advisor Tool
5. GUI Database Backup with Management Studio
6. Database Backup Using Scripts
7. Differential, Transaction Log & COPY-ONLY Backup
8. Advanced Database Backup Options
9. Policy-Based Management of DB Backups
10. Creating a Database Maintenance Plan
11. The bcp Utility for Flat File Import/Export
12. Configuring Database Mail
13. Backup Success or Failure Email Notification
14. Enabling Database Backup File Compression
15. Database and Backup File Encryption
16. 7 Keys to SQL Server 2008 DBA Success


CD 5 - DATABASE RESTORE (3 hours 2 minutes)
1. Restoring Transaction Log Backups in Order
2. Restore a Database to a Point in Time after Crash
3. Disaster Recovery Planning
4. Backup Set Verify with RESTORE VERIFYONLY
5. Database Restore with Visual Tools & Scripts
6. Policy-Based Management of DB Recovery
7. Recovery of Databases at Server Startup
8. Copy Database Using Backup, Copy & Restore
9. Restoring the master, model & msdb Databases
10. Media Sets and Families - Overwrite or Append
11. Initializing and Password Protecting Media
12. Moving 2 Terabyte DB from Phoenix to Boston
13. Restore Completion Database Mail Notification
14. Flat File Bulk Import with BULK INSERT
15. Openrowset, Opendatasource and Openquery
16. Configure Management Data Warehouse Wizard


CD 6 - PERMISSIONING (2 hours 35 minutes)
1. Builtin and Implied Permissions
2. Understanding Permissions Hierarchy
3. CREATE SCHEMA with AUTHORIZATION
4. System Administrator(sa) Permissions
5. Non-sa Server Roles Permissions
6. Fixed Database Role Permissions
7. Object Ownership Within the Database
8. Policy-Based Management of Permissions
9. Object Permissioning Using Object Explorer
10. Statement Permissioning Using Scripts
11. Object Ownership Chains & Object Dependencies
12. ALTER SCHEMA and ALTER AUTHORIZATION
13. Developing Permissioning Strategy
14. Securing Against SQL Injection Attacks
15. Working with the New PUBLIC Server Role
16. TOP 7 SQL Server Security Vulnerabilities


CD 7 - DATABASE INFRASTRUCTURE (2 hours 42 minutes)
1. Create Database by Using Management Studio
2. Accounting Database Setup and Initialization
3. Create Database by Statement Execution
4. Exploring Database Properties - sp_helpdb
5. Primary and User-Defined Filegroups
6. Alter Database & Configure Database Options
7. Architecting & Implementing FILESTREAM Storage
8. Create Database Advanced Features
9. Remove, Rename DB by Object Explorer & Scripts
10. Database Snapshot for Reporting & Safeguarding
11. Moving Databases between Different Servers
12. Scalability and Very Large Databases - VLDB
13. DB Policies - Declarative Management Framework
14. Audits; Database & Server Audit Specifications
15. DBCC; CheckDB, ShrinkDatabase & Other Commands
16. Executing Generate SQL Server Scripts Wizard


CD 8 - SAMPLE DATABASES (2 hours 47 minutes)
1. Installing SQL Server Sample Databases
2. Conceptual Diagram of AW2008 Entity Model
3. Visio Diagram of AdventureWorks2008 Database
4. Primary Key and Foreign Key; RDBMS Basics
5. 1NF, 2NF & 3NF Normal Forms in AdventureWorks2008
6. AdventureWorks2008; Bike Manufacturer OLTP DB
7. AW2008 Business Scenarios & AW Data Dictionary
8. Reviewing AdventureWorks2008 New Features
9. AdventureWorksDW2008 Data Warehouse Database
10. HierarchyID Based Trees in AdventureWorks2008
11. Creating, Saving & Emailing Database Diagrams
12. pubs Book Publishing Sample Database Overview
13. Northwind Food & Drink Supplier Sample DB
14. Advanced Review of pubs Database Diagram
15. Advanced Review of Northwind Database Diagram
16. Accounting DB Review: GL, AP, AR & Inventory


CD 9 - DATABASE DESIGN (2 hours 54 minutes)
1. Understanding Logical Data Modelling
2. Working with Table Column Data Types
3. New; DATE, TIME, DATETIMEOFFSET & DATETIME2
4. CREATE TABLE by Script & Using Object Explorer
5. Primary Key and Foreign Key Constraints
6. Database Diagram Design in Object Explorer
7. Logical Database Modelling with Visio
8. Relational Database Design with Visio
9. Reverse Engineering a Database with Visio
10. ALTER TABLE and Changing Tables by MS
11. Designing Compressed Tables & Indexes
12. Partitioned Table, Partition Function and Scheme
13. Create Partition & Manage Partition Wizards
14. The Data Compression Wizard
15. Code, Translate and Lookup Table Design
16. Database Design and Programming Standards


CD 10 - THE SELECT STATEMENT (3 hours 1 minute)
1. Inner Join, Self Join, Outer Join and Cross Join
2. Basic SQL SELECT and SELECT INTO Statements
3. Sorting SELECT Query Results with ORDER BY
4. Exploring CTEs, Nested & Correlated Subqueries
5. SELECT with UNION, UNION ALL, ROLLUP and CUBE
6. Date & Time, String and Math System Functions
7. Exploring Configuration & System Statistical Functions
8. Dynamic SQL for Automatic Query Generation
9. The GROUP BY Clause & Aggregate Functions
10. Working with Hierarchy ID and Metadata Functions
11. TOP, ROW_NUMBER, RANK, DENSE_RANK and NTILE
12. Understanding Recursive Queries & Tree Processing
13. Creating Comma-Delimited Lists with XML Path
14. Crosstab (Matrix) Query with CTE and PIVOT
15. Building Multidimensional Crosstab Query
16. Architecting Business Intelligence Dashboards


CD 11 - MODIFY DATA (3 hours 2 minutes)
1. INSERT INTO Statement with VALUES Clause
2. Understanding INSERT and SELECT Subquery
3. INSERT and EXECUTE Statement
4. Data Removal with the DELETE Statement
5. Data Modification with the UPDATE Statement
6. Working with the MERGE Statement
7. Synchronizing Two Tables with MERGE
8. Modifying Data with Cursors, Subqueries & JOINs
9. Logged and Minimally-Logged Operations
10. Creating Audit Trail with the OUTPUT Clause
11. Combining INSERT with CTE and OUTPUT
12. UPDATE with CTE, OVER & PARTITION BY
13. Deleting Duplicate Rows with CTE
14. Updating Binary Mask and CSV List Columns
15. Using UPDATE with the FROM Clause
16. UPDATE with GROUP BY Aggregate Subquery


CD 12 - DATA INTEGRITY (3 hours)
1. Data Integrity Constraints in AdventureWorks2008
2. Surrogate Key Architecture, Natural Key & GUID
3. PK, FK, UNIQUE & CHECK Constraints; Defaults
4. Data Integrity Enforcement with Triggers
5. Working with Data Integrity Templates
6. Stored Procedure vs. Ad-hoc SQL Script
7. Entity, Domain and Referential Database Integrity
8. Entity Integrity Definition Using Management Studio
9. Listing and Scripting Data Integrity Objects
10. Understanding Domain Integrity Enforcement
11. Implementing Referential Integrity Constraints
12. Creating User Defined Integrity Objects
13. Exploring Table-Level CHECK Constraints
14. Enterprise-Level Business Rules Enforcement
15. Transactions for Data Integrity Maintenance
16. Comparing and Synchronizing Databases


CD 13 - PROGRAMMABILITY (3 hours 4 minutes)
1. Stored Procedure Design and Programming
2. Exploring Table-Valued, XML & OUTPUT Parameters
3. Stored Procedures in AdventureWorks2008 Database
4. DML - Data Manipulation Language Triggers
5. Trigger Examples with DELETED & INSERTED Tables
6. Discovering INSTEAD OF Triggers & AFTER Triggers
7. DDL - Data Definition Language Triggers
8. Designing & Creating Views - Modifying Data
9. Table-Valued, Scalar-Valued & Inline Functions
10. Understanding Plan Guides for High Performance
11. Temporary Tables, Table Variables & tempdb
12. EXECUTE AS for Execution Context Definition
13. Multiple, Nested, Cascading & Recursive CTE-s
14. Grouping Sets for Multiple Groupings Definition
15. Spatial Data Types: Geography & Geometry
16. Applying Manual Debugging Techniques


CD 14 - TRANSACT-SQL (3 hours 1 minute)
1. TRANSACT-SQL, Batch, and Scripts
2. BEGIN, COMMIT, ROLLBACK TRANSACTION
3. Using TRY…CATCH Blocks for Exception Handling
4. Locks, Isolation Levels and Deadlocks
5. Snapshot Isolation for OLTP Concurrency
6. PROGRAMMING STATEMENTS in Transact-SQL
7. User Defined and System Stored Procedures
8. Working with Linked Servers & 4-part Reference
9. Exploring CROSS APPLY & OUTER APPLY Operators
10. DBA Script Generator Using Query Editor
11. Discovering Undocumented T-SQL Features
12. Dynamic PIVOT and Matrix (Crosstab) Scripts
13. INTERSECT and EXCEPT Set Operators
14. FILESTREAM Data and SPARSE Columns
15. Optimizing with Database Engine Tuning Advisor
16. Debugging with the Transact-SQL Debugger


CD 15 - SSIS IN THE ENTERPRISE (2 hours 52 minutes)
1. ETL & Integration Tool; Integration Services
2. SSIS Import/Export Wizard - Creating Shortcut
3. Exporting Database Data to Flat File
4. Exporting Database Data to Excel
5. Exporting Database Data to Access
6. Exporting Database Data to Another DB
7. Exporting Database Data in XML Format
8. Importing from Flat File to Database
9. Importing from Excel to Database
10. Importing from Access to Database
11. Importing OLAP Cube Data to Database
12. Importing XML Data to Database
13. Saving and Editing SSIS Package
14. Scheduling SSIS Package for Execution
15. Understanding SSIS Control Flow Basics
16. Simple SSIS Data Flow Transformations


CD 16 - SQL SERVER AGENT (2 hours 50 minutes)
1. Automating Database Administrative Tasks
2. Configuring SQL Server Agent - Startup Account
3. Reviewing Job Scheduling in Windows Environment
4. Properties of Jobs, Defining Jobs with SSMS
5. Multi Steps Jobs with Completion Options
6. Creating Jobs Using Scripts
7. Database Mail Setup for Notifications
8. Manage Schedules, Job Categories & View History
9. Configuring Operators Using MS
10. Configuring Alerts Using MS
11. Security; Configuring Proxies Using MS
12. Trouble Shooting SQL Server Agent
13. Automating Night & Weekend Processing
14. Database Maintenance Plan Jobs
15. Executing Jobs & Checking Status from Scipts
16. Exploring the Job Activity Monitor & Reports


CD 17 - DISASTER RECOVERY (3 hours 8 minutes)
1. Log Shipping for Disaster Recovery
2. Live Demonstration of Log Shipping
3. Configuring Database Transaction Log Shipping
4. Log Shipping Jobs: Backup, Copy & Restore
5. Failover to Standby DB in Case of Disaster
6. SQL Server 2008 Failover Clustering
7. Snapshot, Transactional & Merge Replication
8. Live Demonstration of Transactional Replication
9. Publishers, Subscribers & Distributors Setup
10. Replication Monitor, Agents and Profiles
11. Configuring Replication Using Scripts
12. Exploring the Distributor DB & Troubleshooting
13. Change Publishing, Distribution & Subscription
14. Database Mirroring for Failover Redundancy
15. Single Server Site Disaster Recovery Strategy
16. Compare Log Shipping, Mirroring & Clustering


CD 18 - MANAGEMENT STUDIO (3 hours 1 minute)
1. Dynamic UI, Docking, Auto Hiding Windows
2. Working with Query Editor - IntelliSense Option
3. Navigating Registered Servers & Object Explorer
4. SQL Server Activity and Performance Reports
5. DB Transactions, Disk & Index Usage Reports
6. SSIS Object Explorer - Package Management
7. Policy Management: Policies, Conditions & Facets
8. Resource Governor Configuration
9. Advanced Template Explorer and Design
10. Object Search and Object Explorer Details
11. Management Studio Top Menubar Walk Through
12. Toolbars, Server Properties & Right Click Menu
13. Databases Properties & Right Click Menu
14. Policies, Server Registration Export & Import
15. Activity Monitor and SQL Server Logs
16. Replication, Security, and Management Nodes


CD 19 - PERFORMANCE TUNING (3 hours 13 minutes)
1. Ten Step Performance Tuning Bootcamp
2. Performance Related Dynamic Management Views
3. Exploring Covering Index & SET STATISTICS IO ON
4. Reviewing Deadlock Visualization & Avoidance
5. Working with Windows Performance Monitor
6. Correlating Perfmon Chart with Profiler Trace
7. Blocking & 24/7 Monitoring of Databases
8. Setting up Trace Using SQL Profiler
9. Analyzing Trace Table or File in Query Editor
10. sp_who, sp_lock and sp_monitor Commands in QE
11. Discovering Available Options in SQL Profiler
12. Index Design for Superior Performance
13. Index Fragmentation & REBUILD with FILLFACTOR
14. Exploring Index-Related DMVs and DMFs
15. The Database Engine Tuning Advisor
16. Understanding & Using Execution Plans


CD 20 - EXTRA TOPICS (3 hours 6 minutes)
1. Sending Email Messages with Results of Queries
2. Formatting Queries/Sprocs for HTML Email
3. Scheduling Email Reports for Distribution
4. Exploring SQL Server Configuration Manager
5. Instant Trace Query in SQL Server Profiler
6. Introducing the Visual Query & View Designer
7. Advanced Design with Query & View Designer
8. XML Overview, FOR XML Clause & OPENXML
9. Discovering Xquery - XML Query Language
10. Understanding XML Query, Value & Modify Methods
11. Designing Primary & Secondary XML Indexes
12. Working with Command Prompt & xp_cmdshell
13. Improving Performance with Indexed Views
14. Full-text Indexing and Full-text Search
15. Upgrading and Migrating to SQL Server 2008
16. Service Packs, Cumulative Updates & Downloads


CD 21 - HOW TO (3 hours 3 minutes)
1. How To Resolve OLTP Performance Bottlenecks
2. How To Debug Parametrized Stored Procedures
3. How To Solve - "Worked Yesterday, Broken Today"
4. How To Cleanse Data; ISNUMERIC, ISDATE & ISNULL
5. How To Format a Date in SQL Server 2008
6. How To Setup Alert Notification for DDL Changes
7. How To Use SQLCMD Mode to Edit SQLCMD Scripts
8. How To Connect as Dedicated Administrator - DAC
9. How To Best Maintain 24/7 E-Commerce Databases
10. How To Monitor CPU Busy, Disk Reads and Writes
11. How To Use Event Viewer for Troubleshooting
12. How To Setup Linked Server & ODBC Datasource
13. How To Admin from Command Prompt; sqlservr.exe
14. How To Use the PowerShell Scripting Shell
15. How To Connect Access 2007 to SQL Server 2008
16. How To Bulk Import & Export XML Documents


Bonus CD
CD 1 T-SQL Scripts
CD 2 T-SQL Scripts
CD 3 T-SQL Scripts
CD 4 T-SQL Scripts
CD 5 T-SQL Scripts
CD 6 T-SQL Scripts
CD 7 T-SQL Scripts
CD 8 T-SQL Scripts
CD 9 T-SQL Scripts
CD 10 T-SQL Scripts
CD 11 T-SQL Scripts
CD 12 T-SQL Scripts
CD 13 T-SQL Scripts
CD 14 T-SQL Scripts
CD 16 T-SQL Scripts
CD 17 T-SQL Scripts
CD 18 T-SQL Scripts
CD 19 T-SQL Scripts
CD 20 T-SQL Scripts
CD 21 T-SQL Scripts

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE