SQL Server 2012 Administration Essentials Training

Lessons

Lesson 1: Course Introduction

00:03:16

Lesson Clips

Course Introduction 00:03:16

Lesson 2: Planning

00:31:46

Lesson Clips

Introduction 00:00:16
SQL Server Overview 00:04:46
Product Editions 00:04:37
Product Edition Reality 00:10:29
Capacity Planning 00:11:36

Lesson 3: Installing SQL Server 2012

00:37:25

Lesson Clips

Introduction 00:00:14
Pre-Installation Tasks 00:05:32
Running the Install from the GUI 00:31:39

Lesson 4: Advanced Installation

00:39:40

Lesson Clips

Introduction 00:00:12
Command Line Installs 00:06:22
Configuration File 00:07:45
Combining Command Line and Config File 00:25:19

Lesson 5: Basic Database Objects

00:37:31

Lesson Clips

Introduction 00:00:14
Creating a Database 00:09:25
Creating a Table 00:08:13
Creating a View 00:03:33
Stored Procedures and Triggers 00:10:00
Creating a Synonym 00:06:04

Lesson 6: Using SQL Server Management Studio

00:32:02

Lesson Clips

Introduction 00:00:23
Registered Servers 00:03:54
Group Server Queries 00:04:56
Import/Export Registered Servers 00:03:58
Central Management Server 00:02:29
Changing Options 00:04:58
Object Explorer Details 00:04:11
Templates 00:07:11

Lesson 7: Database File Management

00:39:41

Lesson Clips

Introduction 00:00:25
Files and Extensions 00:07:50
Filegroups and the Three Areas of Concern 00:08:52
Data Files vs. Log Files 00:04:21
Creating Filegroups 00:08:51
Moving User Database Files 00:09:19

Lesson 8: Database Configuration Options

00:27:02

Lesson Clips

Introduction 00:00:26
AutoClose 00:03:57
AutoShrink 00:04:27
Recovery Models 00:05:59
Statistics 00:08:52
Restricted Access 00:03:18

Lesson 9: Special Database Space Considerations

00:38:28

Lesson Clips

Introduction 00:00:18
Compression 00:12:53
Table Partitioning 00:25:16

Lesson 10: Server-Level Configurations

00:29:23

Lesson Clips

Introduction 00:00:26
sp_configure 00:23:06
Authentication Modes 00:05:50

Lesson 11: Database Console Commands (DBCC)

00:39:09

Lesson Clips

Introduction 00:00:52
DropCleanBuffers 00:04:24
ShrinkDatabase and ShrinkFile 00:07:28
FreeProcCache 00:04:04
CheckDB and CheckTable 00:10:59
SQLPerf 00:05:50
InputBuffer 00:02:57
Help 00:02:32

Lesson 12: Server-Level Security

00:24:07

Lesson Clips

Introduction 00:00:32
Create Logins 00:09:09
Roles 00:06:45
Granting Server-Level Permissions 00:07:39

Lesson 13: Database-Level Security

00:34:53

Lesson Clips

Introduction 00:00:42
Database Users 00:11:50
Schemas 00:12:36
Permissions Chains 00:09:44

Lesson 14: Transaction Logs and Recovery Models

00:28:12

Lesson Clips

Introduction 00:00:18
What is a Transaction Log? 00:02:51
Recovery Models 00:06:49
Managing Transaction Logs 00:18:12

Lesson 15: Instance Configuration and Management

00:31:36

Lesson Clips

Introduction 00:00:06
Patch Management 00:13:42
Cycling Error Logs 00:06:47
Trace Flags 00:10:59

Lesson 16: Working with Concurrency Problems

00:39:26

Lesson Clips

Introduction 00:00:17
What is Concurrency? 00:02:17
Concurrency Tools 00:05:16
Causes of Blocking 00:02:42
Blocking is a Race Condition 00:11:48
Compatibility Checking Problem and Solution 00:12:32
Killing Processes 00:04:30

Lesson 17: Collecting Troubleshooting Data

00:37:00

Lesson Clips

Introduction 00:01:05
SQL Profiler 00:16:53
Dynamic Management Views (DMVs) 00:10:37
Server-Side Trace (SST) 00:08:24

Lesson 18: Auditing SQL Server

00:38:02

Lesson Clips

Introduction 00:00:21
Configuring Auditing 00:04:31
Server-Level Audits 00:10:52
Database-Level Audits 00:04:36
Importing Audits 00:06:53
Policy-Based Management 00:10:47

Lesson 19: Backup Basics

00:27:29

Lesson Clips

Introduction 00:02:28
Full Backups 00:10:00
Log Backups 00:04:00
Differential Backups 00:03:06
Filegroup Backups 00:02:17
System Backups 00:05:35

Lesson 20: Restore Basics

00:32:53

Lesson Clips

Introduction 00:01:04
Simple Full Restore 00:03:50
Restore with Move 00:04:59
Restore Under Different Name 00:05:29
Restoring Logs 00:16:03
Restore Differentials 00:01:25

Lesson 21: Indexes

00:35:17

Lesson Clips

Introduction 00:02:57
Clustered vs. Non-Clustered Indexes 00:02:57
Index Fragmentation 00:06:18
Detecting Fragmentation 00:10:17
Fixing Fragmentation 00:12:46

Lesson 22: Basic Execution Plans

00:29:45

Lesson Clips

Introduction 00:01:04
Scan vs. Seek 00:12:49
Bookmark Lookups 00:08:11
Covering Indexes 00:02:37
Set Statistics IO 00:05:02

Lesson 23: Query Tuning

00:17:59

Lesson Clips

Introduction 00:00:22
Tuning Queries in Batch Mode 00:10:41
Calculate Savings 00:06:55

Lesson 24: SQL Server Agent

00:27:32

Lesson Clips

Introduction 00:00:38
Creating Jobs 00:12:58
Schedules 00:10:43
Agent Properties 00:03:12

Lesson 25: Database Mail

00:22:59

Lesson Clips

Introduction 00:01:45
Turning on DBMail 00:01:37
Profiles and Accounts 00:14:17
Testing and Troubleshooting 00:05:19

Lesson 26: Importing and Exporting Data

00:25:25

Lesson Clips

Introduction 00:00:35
SQL Server Integration Services Import and Export Wizard 00:07:21
Bulk Insert 00:05:00
Bulk Copy Program (BCP) 00:12:28

Lesson 27: High Availability Basics

00:35:35

Lesson Clips

Introduction 00:00:38
What is the Difference Between HA and DR? 00:05:00
Log Shipping 00:03:11
Replication 00:02:35
Mirroring 00:13:33
AlwaysOn Availability Groups 00:04:20
Clustering 00:06:14

Lesson 28: PowerShell

00:37:15

Lesson Clips

Introduction 00:00:33
What is PowerShell? 00:03:12
What are Cmdlets? 00:17:36
Using PSDrive 00:08:13
Variables and Shell Interaction 00:03:34
PowerShell and SQL Server 00:04:05

Instructor

Sean McCown

Sean McCown is a Microsoft Certified Master in SQL Server 2008. He is also a SQL Server MVP with over 15 years experience in databases. Sean is also founder and co-owner of the renowned website MidnightDBA.com where he records free SQL Server training videos and co-hosts the popular webshow, DBAs at Midnight. He is also the Lead Database Administrator for a large healthcare organization and oversees all aspects of development and administration for over 800 servers. Sean is a Contributing Editor for InfoWorld Magazine and blogs on NetworkWorld.com at SQL Marklar.