SQL Server 2012: Database Administration

(1)

Database Administration

COURSE 171 · 5+ Days of instructor-led training

LearnItFirst.com is one of the most trusted names in the IT training industry. Founded in 2004, LearnItFirst now boasts a training library of more than 3,200 instructor-led training videos spanning nearly 800 hours! hƩ p://www.learniƞ irst.com/

· Database Engine

· Analysis Services

· Reporting Services

· Integration Services

C

OURSE 171

This course is a comprehensive, soup-to-nuts DBA course for Microsoft SQL Server 2012. Coverage includes installation planning and deployment, security, backup and recovery, database management, jobs and automation, and the basics of performance monitoring for DBAs. There are more than 200 videos and the total time is well over 50 hours of training. This course is currently in production with new videos being added weekly. This course also makes extensive use of LearnItFirst’s video exercises which include complete walk-throughs of the solutions (and come with the fi les containing both the problems and the solutions).

W

HAT’S INCLUDED IN THIS COURSE

· 16 chapters of database administration training · Equivalent to 5+ days of “live” classroom training · Course fi les (.sql, .pdf, .xml, exercises, and more) · Hands-on labs and exercises (with sample databases) · Highest quality audio and video

· Three ways to watch:

1. Download to your computer

2. Mobile devices - watch on your iPhone, iPad, Android device, etc.

3. DVD

W

HO IS THIS COURSE FOR?

People who have had some exposure to SQL Server but who have not undergone a full SQL Server 2012 DBA class. This is not a beginner-level course nor is an advanced course; it is intended for the beginner-to-intermediate and/or someone coming from another system (such as Oracle, DB2, or MySQL) who wants to learn how to administer a SQL Server.

P

RICING AND MULTI-USER LICENSING

LearnItFirst’s courses are priced on a per-user, per-course basis. Site licenses, training vouchers, volume discounts, and our “Buy 3, Get 1 Free” packages are also available. For more information, please visit our website:

(2)

SQL Server 2012:

Database Administration

C

OURSE CHAPTER LISTING

This 16-chapter course covers all facets of SQL Server 2012 Database Administration including planning, installation/upgrade, database creation, fi le management, security, backup and recovery, automation, mainte-nance, performance, and much more!

01 - Course Introduction

02 - Editions, Licensing, and What’s New in SQL Server 2012 03 - Installing and Upgrading to SQL 2012

04 - Using the Tools and Confi guring SQL Server 2012 05 - Creating and Managing Databases

06 - Security in SQL Server 2012

07 - SQL Server 2012 Backup and Recovery 08 - Automating Your SQL Server

09 - Database Maintenance

10 - SQL Server 2012 Performance Monitoring 11 - Virtualizing Your SQL Server 2012 Instance

12 - VLDBs, SANs, SSDs, Partitioning Strategies and More 13 - Managing SQL Server 2012 with PowerShell

14 - A Guide to Moving and Copying Databases 15 - SQL Server Replication

16 - Miscellaneous Administration Topics

F

REQUENTLY ASKED QUESTIONS

· “What is the quality of the video/audio?” The quality is excellent with sharp video and superb sounding audio. We output high quality, 1024x768 video with CD quality audio. Our videos are recorded and mixed in a professional

studio so that you get the highest quality possible.

· “How do I access the videos?” For immediate access to your course, you can download the videos from our website. If you’d like, a DVD copy is available as well.

· “Do I have to have internet access to watch the videos?” No! You only need internet access only to download the videos (unless you choose the DVD, which requires no internet connection).

· “How long are the videos?” Each video typically ranges between 10 and 20 minutes and is meant to cover a specifi c topic. This makes it easy to re-use the course later when you need to review a particular topic.

(3)

Database Administration

Course 171

V

IDEOS IN THIS COURSE (cont.):

Viewed?

Chapter 1: Course Introduction

1.1 - Course Introduc on - What Is Covered in this Course? 1.2 - How to Use this Course Eﬀ ec vely

1.3 - How this Course is Arranged

1.4 - How the Exercises Are Designed to Help You

1.5 - Opera ng Systems, SQL Server Edi ons, and Diﬀ erences 1.6 - Professional Development for SQL Server: What’s Next?

Chapter 2: Why SQL Server 2012?

2.1 - Edi ons of SQL Server 2012 2.2 - The Express and Web Edi ons

2.3 - 32-Bit or 64-Bit: Which One Should You Choose? 2.4 - Licensing SQL Server 2012: The Basics

2.5 - Licensing SQL Server 2012: CPUs and Core Factors 2. 6 - Licensing SQL Server 2012: Virtualiza on and HA/DR

2.7 - Sizing Up New Hardware and Choosing Your SQL Server Edi on 2 .8 - What’s New in SQL Server 2012 for DBAs: Overview

2 .9 - The History of SQL Server

Chapter 3: Installing and Upgrading to SQL Server 2012

3.1 - Decisions You Have to Make

3.2 - Decisions You Have to Make: Hardware Benchmarking

3 .3 - Decisions You Have to Make: Requirements, Architecture, Machines 3.4 - Decisions You Have to Make: Security Model

3.5 - What DBAs Need to Know About RAID Levels 3.6 - Decisions You Have to Make: File Placement

3.7 - Decisions You Have to Make: Service Accounts, Domain Groups

3.8 - Decisions You Have to Make: Instance Names, Mul ple Instances, Colla ons 3.9 - Se ng Up the Domain User and Managed Service Accounts

3.10 - Installing an Instance of SQL Server 2012

3.11 - Installing a Named Instance and A First Look at What’s Installed 3.12 - A Quick Look at Management Studio and Other Tools

(4)

SQL Server 2012:

Database Administration

Course 171

V

IDEOS IN THIS COURSE (cont.):

Viewed?

Chapter 3: Installing and Upgrading to SQL Server 2012 (cont.)

3.13 - Exercise 01: Preparing Your Environment to Install SQL Server 2012 3.14 - Exercise 02: Installing SQL Server 2012

3.15 - Other Things You Can Do to Op mize Your SQL Server Installa on 3.16 - Installing Service Packs, Hot Fixes, and Cumula ve Updates

3.17 - How to Use Product Updates to Slipstream a SQL Server 2012 Installa on 3.18 - Upgrading to SQL Server 2012 - Pre-Upgrade

3.19 - Upgrading to SQL Server 2012 - Running the Upgrade Advisor

Chapter 4: Using the Tools and Confi guring SQL Server 2012

4.1 - An Overview of Which Tools Perform Which Func ons 4.2 - SQL Server Management Studio - Walkthrough

4.3 - SQL Server Management Studio - Connec ons, Templates, and More 4.4 - SQL Server Management Studio - Tips and Tricks

4.5 - SQL Server Confi gura on Manager, SQL Server Data Tools, and Books Online 4.6 - How to Confi gure SQL Server 2012 - Service Accounts and Service Management 4.7 - How to Confi gure SQL Server 2012 - Network Protocols, Networking, and Aliases 4.8 - How to Confi gure SQL Server 2012 - Confi gure Memory Usage

4.9 - How to Confi gure SQL Server 2012 - Confi gure CPU Usage

4.10 - How to Confi gure SQL Server 2012 - Security Se ngs, Audi ng, Connec on Op ons 4.11 - How to Confi gure SQL Server 2012 - Even More Confi gura ons!

4.12 - How to Make SQL Server Confi gura on Changes

4.13 - Using SSMS, sp_confi gure, and ALTER SERVER CONFIGURATION 4.14 - Exercise 01: Confi gure Your SQL Server to Accept Remote Connec ons 4.15 - Exercise 02: Confi gure Your SQL Server Using SSMS and TSQL

4.16 - The SQL Server and Windows Error Logs

4.17 - First Look at SQL Server Profi ler and the Default Trace 4.18 - Other Tools Worth Knowing

Chapter 5: Creating and Managing Databases

5.1 - SQL Server Databases: The Data File and the Transac on Log 5.2 - The System Databases in SQL Server 2012

(5)

Database Administration

Course 171

V

IDEOS IN THIS COURSE (cont.):

Viewed?

Chapter 5: Creating and Managing Databases (cont.)

5.3 - The Types of Databases in a Typical SQL Server 2012 Instance 5.4 - Crea ng a Database: The Basics

5.5 - Crea ng a Database Using SSMS and TSQL

5.6 - Crea ng a Database: Sizing and Confi guring Database Files 5.7 - Expanding a Database

5.8 - Filegroups and Working with Mul ple Data and Log Files

5.9 - Crea ng and Expanding Databases with Mul ple Files and Filegroups 5.10 - What Happens When the Transac on Log is 100% Full?

5.11 - Database Confi gura on Op ons, Part 1 5.12 - Database Confi gura on Op ons, Part 2

5.13 - Changing Database Confi gura ons and Compa bility Levels 5.14 - Exercise 01: Planning the Course Database

5.15 - Exercise 02: Crea ng and Modifying the Course Database 5.16 - How to Delete and Empty Files in a Database

5.17 - A aching and Detaching SQL Server 2012 Databases 5.18 - Suggested Changes a er an Upgrade

5.19 - How and Why You Want to Enable Instant File Ini aliza on 5.20 - Tempdb Data File Considera ons

Chapter 6: Security in SQL Server 2012

6.1 - The Principle of Least Privilege 6.2 - Reducing the A ack Surface Area 6.3 - The SQL Server Security Model

6.4 - Logins Installed by SQL Server and How to Create SQL Logins 6.5 - How to Create Windows Logins and Windows Group Logins 6.6 - Server Roles and More

6.7 - Scope and Securables

6.8 - Crea ng and Assigning Server Roles and Assigning Server-Scoped Permissions 6.9 - Account Lockout, Password Policies, and Enabling and Disabling Logins 6.10 - Exercise 01: Crea ng Logins and Assigning Roles

(6)

SQL Server 2012:

Database Administration

Course 171

V

IDEOS IN THIS COURSE (cont.):

Viewed?

Chapter 6: Security in SQL Server 2012 (cont.)

6.12 - The Mysteries of the dbo Account 6.13 - Crea ng and Viewing User Accounts 6.14 - Database Roles in SQL Server 2012 6.15 - Applica on Roles in SQL Server 2012 6.16 - Crea ng and Assigning Database Roles

6.17 - Exercise 02: Crea ng Users and Assigning Roles 6.18 - Gran ng Table, View and Rou ne Permissions 6.19 - Schemas, Name Resolu on, and Object Ownership 6.20 - How to Assign Database Permissions

6.21 - Ownership: What It Is And How It Works 6.22 - Broken Ownership Chains in SQL Server 2012 6.23 - Impersona on in SQL Server 2012

6.24 - Exercise 03: Managing and Tes ng Permissions 6.25 - Exercise 04: Impersona on and Stored Procedures

6.26 - When a Windows User Who Belongs to a Group Creates an Object 6.27 - Dealing with Orphaned Users - Part 1

6.28 - Dealing with Orphaned Users - Part 2 6.29 - Quick Hits: SQL Server Audit

6.30 - Quick Hits: Policy-Based Management (PBM) 6.31 - Quick Hits: Par ally Contained Databases

6.32 - Quick Hits: Trustworthy, Cross Database Ownership Chaining

Chapter 7: SQL Server 2012 Backup and Recovery

7.1 - How the Transac on Log Works

7.2 - The Types of SQL Server Backups and Permissions Required 7.3 - Recovery Models

7.4 - What are Bulk Opera ons in SQL Server? 7.5 - The BULK_LOGGED Recovery Model 7.6 - How to Change the Recovery Model 7.7 - Exercise 01: Recovery Models

(7)

Database Administration

Course 171

V

IDEOS IN THIS COURSE (cont.):

Viewed?

Chapter 7: SQL Server 2012 Backup and Recovery (cont.)

7.9 - How to Take SQL Server Backups in SSMS 7.10 - Viewing the Contents of Backup Files 7.11 - Managing Backup History in SQL Server 7.12 - Backup Compression

7.13 - Exercise 02: Compression and Permissions 7.14 - Exercise 03: Taking Full and Diﬀ eren al Backups 7.15 - More About How the Transac on Log Works 7.16 - How to Backup the Transac on Log

7.17 - The Basics of Restoring a SQL Server Database 7.18 - Developing Backup Strategies, Part 1

7.19 - Developing Backup Strategies, Part 2

7.20 - Example Restore Scenarios and Crash Procedures

7.21 - How Changing the Recovery Model Aﬀ ects Backup Strategy 7.22 - Strategies With the Full and Bulk Logged Recovery Models 7.23 - How Changing to BULK_LOGGED Aﬀ ects Backup Strategies 7.24 - A How-to Guide to Performing Bulk Imports

7.25 - Copy Only Backups

7.26 - Emergency! My Transac on Log Has Filled Up! 7.27 - When Should You Back Up the System Databases?

7.28 - Disaster Recovery Prepara on: Backing Up Your En re SQL Server 7.29 - Exercise 04: Taking Transac on Log Backups

7.30 - Where to Look for Problems

7.31 - The Fundamentals of Restoring Databases 7.32 - Performing Point In Time Restores

7.33 - How to View The Transac on Log and Perform STOPAT Restores 7.34 - The Importance of the Tail Log Backup

7.35 - Restoring the Chapter07 Database

7.36 - How to Move and Copy Databases with Backup and Restore 7.37 - How to Backup the Log When the Data File(s) Is Missing 7.38 - Emergency Mode Restores and DBCC CHECKDB

(8)

SQL Server 2012:

Database Administration

Course 171

V

IDEOS IN THIS COURSE (cont.):

Viewed?

Chapter 7: SQL Server 2012 Backup and Recovery (cont.)

7.40 - How to Rebuild Your SQL Server (a.k.a. Rebuild Master) 7.41 - How to Restore Master and the System Databases 7.42 - Exercise 05: Restoring Databases

7.43 - How to Have Zero Down me in SQL Server 2012 (Ha!)

Chapter 8: Automating Your SQL Server

8.1 - What Can We Automate

8.2 - Confi guring Your Server to Send Email via Database Mail 8.3 - Tes ng and Troubleshoo ng Database Mail

8.4 - The SQL Server Agent

8.5 - How to Confi gure the SQL Server Agent

8.6 - SQL Server Operators and Alerts: What Are They and What Can They Do? 8.7 - Performance Condi on Alerts and WMI Event Alerts

8.8 - Create SQL Server Event Alerts and Customizing Your SQL Server 8.9 - How to Create a SQL Server WMI Alert

8.10 - Crea ng Performance Condi on Alerts 8.11 - Exercise 01: Confi gure the SQL Server Agent

8.12 - Exercise 02: Confi gure Database Mail and Operators

8.13 - SQL Agent and Security: Sysadmin-Created Jobs and The Agent 8.14 - Create a Job to Backup the Database

8.15 - SQL Agent and Security: Creden als, and Proxy Accounts 8.16 - How Security Works for Non-Sysadmin Owned Jobs

8.17 - Crea ng and Using Proxy Accounts and Non-Sysadmin Jobs 8.18 - Using Tokenized Jobs to Create a Powerful Event Repor ng System 8.19 - Send Email When SQL Agent Starts + Start Agent When SQL Server Starts 8.20 - Exercise 03: Create SQL Server Backup Jobs

8.21 - Exercise 04: Create Security Maintenance Jobs 8.22 - Create a Job to Perform a Bulk Import

8.23 - How to Schedule an SSIS Package as a Job 8.24 - SQL Server 2012 Resource Governor

(9)

Database Administration

Course 171

V

IDEOS IN THIS COURSE (cont.):

Viewed?

Chapter 9: : Database Maintenance

9.1 - Maintenance and the DBA

9.2 - The Types of Indexes in SQL Server 2012

9.3 -Understanding SQL Server Page Structures and Clustered Index Structure 9.4 - Page Splits - What They Are and How to Iden fy Them

9.5 - Iden fying and Repairing Index Fragmenta on

9.6 - How SQL Server Uses Sta s cs (and Why You Need to Know) 9.7 - How to View and Update SQL Server Sta s cs

9.8 - The Importance of DBCC CHECKDB 9.9 - Maintenance Plans

9.10 - Archiving/Purging Database Mail history

9.11 - Exercise 01: Crea ng a Job to Reindex Fragmented Tables

Chapter 10: SQL Server 2012 Performance Monitoring

10.1 - Performance Monitoring Tools and Concepts DBAs Need to Be Familiar With 10.2 - Performance Monitor Counters for SQL Server

10.3 - Crea ng Data Collector Sets for Monitoring SQL Server 10.4 - Using PAL to Automa cally Create Reports for SQL Server 10.5 - Using SSMS to Monitor Performance

10.6 - Key Concepts: sp_confi gure performance penalty, FREEPROCCACHE, DROPCLEANBUFFERS 10.7 - SQL Server Se ngs: Op mize for Ad-Hoc Workloads and Forced Parameteriza on

10.8 - Using the Database Engine Tuning Advisor to Diagnose Performance Issues

10.9 - DMVs (Dynamic Management Views) and DMFs (Dynamic Management Func ons) 10.10 - Common DMVs/DMFs for Monitoring

10.11 - Iden fying Disk Pressure Using DMVs and DMFs

10.12 - DMVs/DMFs for Monitoring Users and Database Ac vity 10.13 - Iden fying Memory Pressure Using DMVs and DMFs 10.14 - Locking and Blocking

10.15 - Lock Hints and Isola on Levels 10.16 - Waits and Queues

10.17 - Using SQL Server Profi ler to Spot Problems 10.18 - Extended Events

(10)

SQL Server 2012:

Database Administration

Course 171

V

IDEOS IN THIS COURSE (cont.):

Viewed?

Chapter 11: Virtualizing Your SQL Server 2012 Instance

11.1 - Why Virtualize?

11.2 - What Tools Are Available? 11.3 - SQL Azure and the Cloud

Chapter 12: VLDBs, SANs, SSDs, Partitioning Strategies and More

12.1 - Working with Very Large Databases 12.2 - Disk Subsytems: SAN, SSD, or Both? 12.3 - Par oning Strategies for Large Tables 12.4 - Indexed Views and Filtered Indexes

Chapter 13: Managing SQL Server 2012 with PowerShell

13.1 - What Can We Do with PowerShell and SQL Server? 13.2 - How to Navigate SQL Server with PowerShell 13.3 - Using LogParser with SQL Server 2012

Chapter 14: A Guide to Moving and Copying Databases

14.1 - Op ons for Moving and Copying Databases

14.2 - How to Move and Copy Databases with Backup and Restore 14.3 - A aching and Detaching SQL Server 2012 Databases 14.4 - Dealing with Orphaned Users, Part 1

14.5 - Dealing with Orphaned Users, Part 2

Chapter 15: SQL Server Replication

15.1 - What Is Replica on and Why Do We Need It? 15.2 - Types of SQL Server Replica on

Chapter 16: Miscellaneous Administration Topics

16.1 - SQL CLR - Managing .Net in the Database 16.2 - Encryp on in SQL Server 2012

16.3 - Database Snapshots 16.4 - FILESTREAM and FileTables

(11)

Viewed?

SQL Server 2012:

Database Administration

A

BOUT THE INSTRUCTOR

Scott Whigham is an experienced SQL Server/.NET consultant, trainer, and author with more than fi fteen years of hands-on experience working with SQL Server databases, writing and debugging applications using Visual Studio, data warehousing, and performance tuning. Before founding LearnItFirst.com, Scott had been an independent consultant since 1998 focusing on SQL Server, .NET, performance tuning, and data warehousing. Scott has also been a trainer for popu-lar in-person training organizations, taught several “Train the Trainer” workshops and classes, and authored/taught multiple “New Hire” programs for Fortune 100 companies. As an author and instructor, Scott has been asked to speak and train at events in the United States and Europe. Scott has written courses on SQL Server and C# and VB languages as well as popular articles for top-selling magazines and the MSDN (Microsoft Developer Network) library. Scott and his family live in the Dallas, Texas area.