70-462: SQL Server Database Administration (DBA)
4.4 (1,835 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
9,345 students enrolled

70-462: SQL Server Database Administration (DBA)

Beginner-Intermediate practical experience in Database Administration (DBA) SQL Server. Good for Microsoft exam 70-462.
Bestseller
4.4 (1,835 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
9,350 students enrolled
Created by Phillip Burton
Last updated 1/2020
English
English, Italian [Auto], 2 more
  • Polish [Auto]
  • Spanish
Current price: $139.99 Original price: $199.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 9.5 hours on-demand video
  • 11 articles
  • 5 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
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
  • If you wish to eventually take the 70-462 exam, you would need to memorize various T-SQL table names.
  • If you want to know enough T-SQL to assist you with your SSRS work, please have a look at the first two sessions of my 70-461 courses.
  • 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.
Description

Do you want to gain practical experience in Database Administration in SQL Server? Are you wanting to prepare for the official Microsoft exam 70-462 "Administering Microsoft SQL Server"? Either way, 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 Part 1 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 2016, 2014 and 2012, and mostly in 2008R2 and 2008.

Who this course is for:
  • 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 are 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.
Course content
Expand all 96 lectures 09:41:56
+ Introduction
7 lectures 26:33
Welcome to Udemy
00:35
The Udemy Interface
02:00
Downloading and Installing SQL Server Developer Edition
12:14
Databases - Resources
00:09
Downloading AdventureWorks
04:13
+ Backup and Restore - Part 1
12 lectures 01:21:56
Looking around SSMS and how to generate T-SQL
08:30
Backing up Database, and problem with Restoring
05:19
Changing Recovery Model
04:01
13a. Different backup models - practice in SSMS
04:45
13a and 14c. Point in Time Recovery
11:37
Recovery and Backup models
4 questions
Using NORECOVERY and RECOVERY
08:04
13g and 13h. Back up an SQL Server environment and system databases
05:39
13c and 13f. Perform backup/restore based on strategies inc. backup redundancy
07:34
13d and 14b. Recover from a corrupted drive
08:32
More backing up questions
4 questions
Practice Activity Number 1
00:36
+ Manage logins and server roles
10 lectures 01:04:23
17c. Create login accounts
07:26
17d. Manage access to the server, 17b. secure the SQL server using server roles
08:51
17e. Create and maintain user-defined server roles
07:08
19c. Create Database User Accounts
05:07
18b. Fixed Database-Level Roles
07:04
User Database-Level Roles
08:36
Creating and Using Schemas
08:40
Creating access to server/database with least privilege
07:17
18c. Protect objects from being modified
03:35
Manage logins and server roles
3 questions
Practice Activity Number 2
00:39
+ Implement and maintain indexes
9 lectures 56:55
What are indexes?
12:47
15d. Implement indexes
06:17
Fragmentation - theory
09:06
Fragmentation, reorganize and rebuild in SSMS
10:19
Fill factor
04:51
15g. Optimise indexes (full, filter index)
03:23
15c. Identify unused indexes
05:03
15h. Statistics (full, filter) force or fix queue
04:56
Implement and maintain indexes
3 questions
Practice Activity Number 3
00:13
+ Import and export data
2 lectures 15:01
Transfer data
11:27
Bulk Insert
03:34
Import and Export Data
2 questions
+ Manage SQL Server Agent - Part 1
7 lectures 34:35
5a. Create, maintain and monitor jobs
08:26
5b. Administer jobs and alerts
04:05
RAISERROR and Create Event Alerts
06:04
What happens when a job runs? And running a RAISERROR WITH LOG
03:12
5b. Adding operators to jobs and alerts
09:06
11j. Create alerts on critical server condition
03:19
Manage SQL Server Agent
3 questions
Practice Activity Number 4
00:23
+ Manage and configure databases
12 lectures 01:16:48
6b, 7a. Database configuration: autoclose, autoshrink, recovery models
08:27
6a. Design multiple file groups
06:55
6a. Creating database with multiple file groups
05:59
6c. Manage file space, including adding new filegroups and moving objects
09:25
6g. Partitioning in theory
06:34
Partitioning in practice using the GUI
07:02
Partitioning in practice using T-SQL, and testing partitioning
08:25
13e. Manage a multi-TB database; 14d. File group restore
06:33
6h. Manage log file growth
05:29
6i. DBCC
03:30
6d, 19d. Implement and configure contained databases and logins
08:08
Manage and configure databases
6 questions
Practice Activity Number 5
00:21
+ Compression
4 lectures 25:01
6e. Page and Row Data Compression
12:16
6e. Sparse columns
05:14
15k. Columnstore Indexes
07:20
Compresison
2 questions
Practice Activity Number 6
00:11
+ Identify and resolve concurrency problems
5 lectures 31:40
10d. Diagnose blocking, live locking and deadlocking - theory
07:38
10d. Diagnose deadlocking - practice; 10h. kill processes
06:09
10c. Monitor via DMV or other MS product; 10e. diagnose waits
05:13
10a. Examine deadlocking issues using the SQL server logs using trace flags
06:23
10g. Know What Affects Performance
06:17
Identify and resolve concurrency problems
3 questions
+ Collect and analyse troubleshooting data
5 lectures 28:12
11a, 11c. Collect trace data by using SQL Server Profiler
07:12
11i. Use XEvents (Extended Events)
05:28
11b, 11f, 11g, 11h. Collect performance data by using System Monitor
10:38
11l. Identify IO v memory v CPU bottlenecks
04:28
Collect and analyse troubleshooting data
2 questions
Practice Activity Number 7
00:26