Course at a glance
Included in these subscriptions:
- Dev & IT Pro Video
- Dev & IT Pro Power Pack
| Release date | 11/12/2007 | |
| Level | Intermediate | |
| Runtime | 18h 17m | |
| Closed captioning | N/A | |
| Transcript | N/A | |
| eBooks / courseware | Included | |
| Hands-on labs | Included | |
| Sample code | Included | |
| Exams | Included | |
| Platform | Internet Explorer Only |
Course description
SQL Server 2005 includes a rich set of tools that go beyond the basics of querying and manipulating data. In this course, you will learn about the administrative features that are available in SQL Server. This course also examines other SQL Server 2005 essential application, support, and maintenance topics—features you can use for high-availability, stability, reliability, and scalability of databases.
Prerequisites
This course assumes that students have working experience with SQL Server 2000 or 2005; basic relational database concepts (e.g., tables, queries, and indexing); general knowledge of XML, Transact-SQL, and a fundamental understanding of networking and security concepts.
Meet the experts
Andy Baron is a nationally recognized industry expert specializing in Visual Basic, Visual C#, ASP.NET, ADO.NET, SQL Server, and SQL Server Business Intelligence. He is an experienced consultant, author, and editor for several industry publications and speaker at industry conferences. Andy has also been repeatedly selected as a Microsoft MVP for his contributions to Microsoft's online support.
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.
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
Overview and Authentication (41:13)
- Introduction (02:19)
- Some Security Threats (03:22)
- Security Design Philosophy (03:54)
- Two Stages of Security (01:49)
- Authentication (03:09)
- Configuring Security Settings (00:31)
- Windows Integrated Auth. (02:16)
- Adding a Win. Login Demo (03:55)
- Win. Logins via Transact-SQL (00:21)
- SQL Server Authentication (05:59)
- SQL Server Logins via T-SQL (01:17)
- SQL Server Security Settings (00:08)
- Win. Logins/SQL Server Login (01:23)
- Beware of the sa Login (01:55)
- Password Policy & Enforce (01:56)
- Local Security Settings applet (03:40)
- Enable/Disable Pwd. Policy (02:31)
- Summary (00:40)
Authorization and Permissions (01:06:21)
- Introduction (01:34)
- Principals (01:28)
- Roles (00:55)
- Server Roles (02:24)
- Add a User to a Server Role (01:49)
- SQL Server Login via T-SQL (00:26)
- Return List of All Server Roles (00:21)
- Get Desc. of a Single SR (00:10)
- Get List of the DiskAdmin Role (01:42)
- Database Roles (03:51)
- Add User to a Database Role (01:00)
- The Public Role (00:58)
- dbo (Database Owner) Role (01:14)
- User-Defined Roles (01:44)
- Create a UDR Demo (01:26)
- Securable Objects (03:19)
- Permission Types (05:20)
- Permission Statements (02:25)
- Granting Permissions Demo (05:47)
- Schemas (03:58)
- Roles/Permissions/Schemas (01:42)
- Schemas to Assign Perm. (01:57)
- Execution Context (08:48)
- The EXECUTE AS Clause (05:21)
- Metadata Security (06:05)
- Summary (00:25)
Module 2
Encryption (18:40)
- Introduction (02:55)
- Encryption Keys (03:52)
- Key Management (01:40)
- Encrypting Data Demo (07:58)
- Decrypting Data Demo (01:19)
- Summary (00:54)
Evaluating Performance (32:37)
- Introduction (02:03)
- SQL Server Monitoring Tools (03:07)
- SQL Server Profiler (01:16)
- Activity Monitor (00:32)
- Display Est. Exec. Plan (00:39)
- DB Engine Tuning Advisor (01:03)
- Windows Performance Monitor (00:43)
- Windows Event Viewer (00:18)
- SQL Server Profiler Term. (03:11)
- Diagnosing CPU Bottlenecks (02:01)
- SQL Server Profiler Demo (08:29)
- Run the Trace (03:52)
- Query the Trace File (03:18)
- Summary (02:00)
Tuning Queries (29:08)
- Introduction (03:48)
- How Join Types Affect Perf. (06:13)
- Join Hints (01:05)
- Join & Subquery Perf. Issues (03:06)
- Limit Num. of Tables in a Join (03:48)
- Avoid Cursors (00:48)
- Cursor Alternatives (01:13)
- Compare Subqueries & Joins (07:41)
- Summary (01:23)
Module 3
Indexes and Partitions (26:50)
- Introduction (00:31)
- What is an Index? (01:19)
- Non-Clustered/Clustered Index (07:01)
- Index Recommendations (06:19)
- How the Optimizer Decides (00:19)
- What is a Partition? (01:34)
- DB Engine Tuning Advisor (02:45)
- Indexing and Partition Rcmd. (03:12)
- View Tuning Options (02:43)
- Summary (01:03)
Identifying Bottlenecks (30:06)
- Introduction (02:00)
- Using Activity Monitor (11:01)
- Using Performance Monitor (09:20)
- Using Dynamic Mgmt. Views (03:22)
- Memory Usage Demo (02:21)
- Summary (01:59)
Automating Admin. Tasks (30:05)
- Introduction (01:42)
- SQL Server Agent (02:02)
- Start SQL Server Agent (01:34)
- Configure SQL Server Agent (02:39)
- Create an Operator (02:02)
- Create a Job (06:23)
- Create Alerts (03:13)
- Maintenance Plans (02:09)
- Maint. Plan Wizard Demo (05:50)
- Modify the Maintenance Plan (01:59)
- Summary (00:28)
Module 4
SQL Management Objects (24:04)
- Introduction (01:24)
- SMO Namespaces (01:13)
- The SMO Object Model (01:18)
- SMO Object Model Details (02:19)
- SMO Demo Application (02:59)
- View the Connect Code (05:13)
- View Create Database Code (01:57)
- View Backup Database Code (02:56)
- View Object Scripting Code (03:53)
- Summary (00:47)
Replication Concepts (33:05)
- Introduction (00:53)
- Publishing Model (02:20)
- Replication Types: Snapshot (02:10)
- Replication Types: Trans. (03:13)
- Replication Types: Merge (04:02)
- Replication Metadata (03:04)
- Replication Agents (06:22)
- Replication Prog. Interfaces (02:21)
- Replication Stored Procedures (02:53)
- Replication Mgmt. Objects (02:22)
- Agent Exec./ActiveX Controls (02:07)
- Summary (01:14)
Replication Demo (32:30)
- Introduction (00:30)
- Set Up Replication Demo (02:00)
- Configuration Wizard (07:26)
- Create a Merge Publication (04:04)
- Define a Filter for the Pub. (02:44)
- Add a Join to the Publication (04:38)
- Define Security Settings (01:38)
- Final Wizard Settings (00:21)
- Create the Publication (01:43)
- Generate the Snapshot (02:39)
- Generate Scripts (04:17)
- Summary (00:26)
Module 5
Coding Replication (34:18)
- Introduction (01:12)
- Create a Subscriber Database (00:59)
- View Replication Code (02:48)
- Create a Subscription in Code (06:23)
- Run the Create Code (01:30)
- Sync. the Subscription in Code (04:01)
- Run the Synchronize Code (03:07)
- Use Replication Monitor (01:43)
- Monitor Activity in Code (03:20)
- Run the Monitor Code (01:16)
- View the ChangeArticle Code (03:20)
- View the ReInitialize Code (01:56)
- View a Subscription History (01:44)
- Summary (00:53)
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)
Module 6
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)
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)
Module 7
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)
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)
Module 8
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)
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)
Module 9
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)
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)
Module 10
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)
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)
Module 11
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)
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 12
Program Service Broker Apps (01:51:09)
- 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 (00:49)
- SB Endpoints: Arguments (02:41)
- Demo: Setup a Service (53:29)
- Implement Apps (01:18)
- Create Dialog Conversations (02:39)
- Sending Messages (01:24)
- Conversation Group (01:34)
- Receive Messages (02:56)
- Send Response (00:37)
- End Conversation (01:40)
- Demo: Define SB App (24:52)
- Summary (00:24)