70-462: Sql Server Database Administration (Dba)

Beginner-Intermediate practical experience in Database Administration (DBA) SQL Server. Was for Microsoft exam 70-462.

What you'll learn

Create Backups and Restore Databases, understanding the different recovery models

Manage logins and server roles, and import and export data

Implement and maintain indexes, and manage and configure databases, including compression and concurrency problems

Manage SQL Server Agent and audit SQL Server instances

Collect and analyse troubleshooting data

Plan and install SQL Server and related services, including implementing a migration strategy

Requirements

You don't need any prior DBA knowledge prior to taking this course.

It would be helpful if you knew how to create simple T-SQL statements (SELECT, FROM, WHERE, GROUP BY) for some parts of the course, but not essential

You don't need any form of SQL Server to already be installed on your computer; I will show you have to download it and install it - for free.

You will need a Windows computer. We will be installing SQL Server Management Studio (SSMS) on your computer.

Most of the DBA functionality is not available on Azure Data Studio, and therefore you will not be able to do the DBA operations using a Mac.

Description

Please note: Microsoft has withdrawn the 70-462 exam. However, the information in this course is still relevant to DBA.Do you want to gain practical experience in Database Administration in SQL Server? Welcome.In this course, learn some of the skills that Microsoft want you to know, and add another tool to your CV or resume, and even go for the official Microsoft certification.SQL Server is one of the main database tools that is requested in the job market, and the ability to being able to do DBA for SQL Server could be very useful for your current work, and your next job hunt.We'll download and install for free SQL Server Developer, which gives you all of the capabilities of the full-cost Enterprise edition (though without being able to use it for commercial purposes). We'll start by downloading a demonstration database, and learning how to backup and restore this database, and what recovery models are appropriate for other databases, to enable you to offer Point in Time recovery.We'll manage logins and server roles, so you can control who gets access to what data, implement and maintain indexes, so you can speed up searches, and import and export data. We'll look at managing SQL Server Agent, so you can automate routine tasks, and also be alerted to problems, we'll manage and configure databases, and look at compression, both of which aids with administrating big databases. And much more.The course will take around 10 hours to complete, but completing this will enable you to versed in Database Administration tasks, giving you hands-on experience.The course increases in difficulty slowly, so you'll manually backup a database, but then later we'll use SQL Server Agent to automate this. In this course, we will only do activities that require one computer, instead of multiple servers or virtualization.The course is fairly relaxed - there will be a few "wrong turns", so you can see what problems might arise, but every lesson works towards an end goal at a relatively slow pace, so you can follow on your own computer easily. I assume that you know how to use a computer, including installing programs, but the actual DBA will be at a basic level, and I'll introduce every component as we go on.No knowledge of T-SQL is required, as I'll demonstrate everything I can through the GUI (Graphical User Interface) as well as T-SQL. However, if you are going to go for the official Microsoft exam, the equivalent knowledge as per my first two courses in 70-461 T-SQL is essential. At the end of the course, you can download a certificate of completion, so you can show everyone your new-found skills, and continue your preparations for the official Microsoft certifications.The course is recorded on SQL Server 2017, but will also work fully in 2022, 2019, 2016, 2014 and 2012, and mostly in 2008R2 and 2008.

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Welcome to Udemy

Lecture 3 The Udemy Interface

Lecture 4 Do you want auto-translated subtitles in more languages?

Lecture 5 Curriculum

Lecture 6 Databases - Resources

Lecture 7 Downloading SQL Server Developer 2022

Lecture 8 Installing SQL Server Developer 2022

Lecture 9 Which version of SSMS should I use?

Lecture 10 Installing SQL Server Management Studio (SSMS)

Lecture 11 Downloading AdventureWorks

Section 2: Backup and Restore - Part 1

Lecture 12 Restoring a Database

Lecture 13 Looking around SSMS and how to generate T-SQL

Lecture 14 Backing up Database, and problem with Restoring

Lecture 15 Changing Recovery Model

Lecture 16 13a. Different backup models - theory

Lecture 17 13a. Different backup models - practice in SSMS

Lecture 18 13a and 14c. Point in Time Recovery

Lecture 19 Using NORECOVERY and RECOVERY

Lecture 20 13g and 13h. Back up an SQL Server environment and system databases

Lecture 21 13c and 13f. Perform backup/restore based on strategies inc. backup redundancy

Lecture 22 13d and 14b. Recover from a corrupted drive

Lecture 23 Practice Activity Number 1

Section 3: Manage logins and server roles

Lecture 24 17c. Create login accounts

Lecture 25 17d. Manage access to the server, 17b. secure the SQL server using server roles

Lecture 26 17e. Create and maintain user-defined server roles

Lecture 27 19c. Create Database User Accounts

Lecture 28 18b. Fixed Database-Level Roles

Lecture 29 User Database-Level Roles

Lecture 30 Creating and Using Schemas

Lecture 31 Creating access to server/database with least privilege

Lecture 32 18c. Protect objects from being modified

Lecture 33 Practice Activity Number 2

Section 4: Implement and maintain indexes

Lecture 34 What are indexes?

Lecture 35 15d. Implement indexes

Lecture 36 Fragmentation - theory

Lecture 37 Fragmentation, reorganize and rebuild in SSMS

Lecture 38 Fill factor

Lecture 39 15g. Optimise indexes (full, filter index)

Lecture 40 15c. Identify unused indexes

Lecture 41 15h. Statistics (full, filter) force or fix queue

Lecture 42 Practice Activity Number 3

Section 5: Import and export data

Lecture 43 Transfer data

Lecture 44 Bulk Insert

Section 6: Manage SQL Server Agent - Part 1

Lecture 45 5a. Create, maintain and monitor jobs

Lecture 46 5b. Administer jobs and alerts

Lecture 47 RAISERROR and Create Event Alerts

Lecture 48 What happens when a job runs? And running a RAISERROR WITH LOG

Lecture 49 5b. Adding operators to jobs and alerts

Lecture 50 11j. Create alerts on critical server condition

Lecture 51 Practice Activity Number 4

Section 7: Manage and configure databases

Lecture 52 6b, 7a. Database configuration: autoclose, autoshrink, recovery models

Lecture 53 6a. Design multiple file groups

Lecture 54 6a. Creating database with multiple file groups

Lecture 55 6c. Manage file space, including adding new filegroups and moving objects

Lecture 56 6g. Partitioning in theory

Lecture 57 Partitioning in practice using the GUI

Lecture 58 Partitioning in practice using T-SQL, and testing partitioning

Lecture 59 13e. Manage a multi-TB database; 14d. File group restore

Lecture 60 6h. Manage log file growth

Lecture 61 6i. DBCC

Lecture 62 6d, 19d. Implement and configure contained databases and logins

Lecture 63 Practice Activity Number 5

Section 8: Compression

Lecture 64 6e. Page and Row Data Compression

Lecture 65 6e. Sparse columns

Lecture 66 15k. Columnstore Indexes

Lecture 67 Practice Activity Number 6

Section 9: Identify and resolve concurrency problems

Lecture 68 10d. Diagnose blocking, live locking and deadlocking - theory

Lecture 69 10d. Diagnose deadlocking - practice; 10h. kill processes

Lecture 70 10c. Monitor via DMV or other MS product; 10e. diagnose waits

Lecture 71 10a. Examine deadlocking issues using the SQL server logs using trace flags

Lecture 72 10g. Know What Affects Performance

Section 10: Collect and analyse troubleshooting data

Lecture 73 11a, 11c. Collect trace data by using SQL Server Profiler

Lecture 74 11i. Use XEvents (Extended Events)

Lecture 75 11b, 11f, 11g, 11h. Collect performance data by using System Monitor

Lecture 76 11l. Identify IO v memory v CPU bottlenecks

Lecture 77 Practice Activity Number 7

Section 11: Audit SQL Server Instances

Lecture 78 12a. implement a security strategy for auditing; 12c. configure server audits

Lecture 79 12e. Monitor elevated privileges as well as unsolicited attempts to connect

Lecture 80 12b. Configure an audit; 12d. Track who modified an object

Lecture 81 Practice Activity Number 8

Section 12: Plan installation

Lecture 82 1l. Choose the right hardware; 1a. Evaluate Installation Requirements

Lecture 83 1c. Plan Scale-up vs Scale-out basics

Lecture 84 1d. Plan for capacity; 1i. Windows-Level and Service Level Security

Lecture 85 1f. Design the storage for new databases

Section 13: Install SQL Server and related services

Lecture 86 9a. Install an instance; 7b. Install Default and named instances

Lecture 87 1b, 2b, 2c, 4a, 4b. Design the installation of SQL Server and its components

Lecture 88 4a. Set up and configure all SQL Server components, including Visual Studio SSDT

Lecture 89 2a. Test connectivity

Lecture 90 2d. Configure an OS disk

Lecture 91 Practice Activity 9

Section 14: Implement a migration strategy

Lecture 92 3a. Restore versus detach/attach

Lecture 93 3b. Migrate Security

Lecture 94 3c. Migrate from a previous version; 3d. Migrate to new hardware

Lecture 95 3e. Migrate systems and data from other sources

Section 15: Install SQL Server, related services and additional SQL Server components

Lecture 96 4b. Configure full-text indexing

Lecture 97 4c. SSIS Security

Lecture 98 4d. Filestream

Lecture 99 4e. Filetable

Section 16: Well done

Lecture 100 Congratulations

Lecture 101 Bonus Lecture SQL

This course is for you if you want practice experience in SQL Server Database Administration,No prior experience with T-SQL is required, although the more knowledge you have it, the better.,In this course we will cover several aspects which used to be tested in Microsoft's 70-462 exam "Administering Microsoft SQL Server",Although the exam specifically says "SQL Server 2012/2014", all parts of this course are applicable for the 2016 and 2017 versions, and most in the 2008 and 2008R2 versions.,This course is not for you if you want advanced training on Database Administration.