English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 9h 51m | 2.49 GB
Beginner-Intermediate practical experience in Database Administration (DBA) SQL Server. Was for Microsoft exam 70-462.
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 2016, 2014 and 2012, and mostly in 2008R2 and 2008.
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
Table of Contents
Introduction
1 Introduction
2 Welcome to Udemy
3 The Udemy Interface
4 Do you want auto-translated subtitles in more languages
5 Curriculum
6 Databases – Resources
7 Downloading SQL Server Developer E9
8 Installing SQL Server Developer E9
9 Installing SQL Server Management Studio (SSMS)
10 Downloading AdventureWorks
Backup and Restore – Part 1
11 Restoring a Database
12 Looking around SSMS and how to generate T-SQL
13 Backing up Database, and problem with Restoring
14 Changing Recovery Model
15 a. Different backup models – theory
16 a. Different backup models – practice in SSMS
17 a and 14c. Point in Time Recovery
18 Using NORECOVERY and RECOVERY
19 g and 13h. Back up an SQL Server environment and system databases
20 c and 13f. Perform backup restore based on strategies inc. backup redundancy
21 d and 14b. Recover from a corrupted drive
22 Practice Activity Number 1
Manage logins and server roles
23 c. Create login accounts
24 d. Manage access to the server, 17b. secure the SQL server using server roles
25 e. Create and maintain user-defined server roles
26 c. Create Database User Accounts
27 b. Fixed Database-Level Roles
28 User Database-Level Roles
29 Creating and Using Schemas
30 Creating access to server database with least privilege
31 c. Protect objects from being modified
32 Practice Activity Number 2
Implement and maintain indexes
33 What are indexes
34 d. Implement indexes
35 Fragmentation – theory
36 Fragmentation, reorganize and rebuild in SSMS
37 Fill factor
38 g. Optimise indexes (full, filter index)
39 c. Identify unused indexes
40 h. Statistics (full, filter) force or fix queue
41 Practice Activity Number 3
Import and export data
42 Transfer data
43 Bulk Insert
Manage SQL Server Agent – Part 1
44 a. Create, maintain and monitor jobs
45 b. Administer jobs and alerts
46 RAISERROR and Create Event Alerts
47 What happens when a job runs And running a RAISERROR WITH LOG
48 b. Adding operators to jobs and alerts
49 j. Create alerts on critical server condition
50 Practice Activity Number 4
Manage and configure databases
51 b, 7a. Database configuration autoclose, autoshrink, recovery models
52 a. Design multiple file groups
53 a. Creating database with multiple file groups
54 c. Manage file space, including adding new filegroups and moving objects
55 g. Partitioning in theory
56 Partitioning in practice using the GUI
57 Partitioning in practice using T-SQL, and testing partitioning
58 e. Manage a multi-TB database; 14d. File group restore
59 h. Manage log file growth
60 i. DBCC
61 d, 19d. Implement and configure contained databases and logins
62 Practice Activity Number 5
Compression
63 e. Page and Row Data Compression
64 e. Sparse columns
65 k. Columnstore Indexes
66 Practice Activity Number 6
Identify and resolve concurrency problems
67 d. Diagnose blocking, live locking and deadlocking – theory
68 d. Diagnose deadlocking – practice; 10h. kill processes
69 c. Monitor via DMV or other MS product; 10e. diagnose waits
70 a. Examine deadlocking issues using the SQL server logs using trace flags
71 g. Know What Affects Performance
Collect and analyse troubleshooting data
72 a, 11c. Collect trace data by using SQL Server Profiler
73 i. Use XEvents (Extended Events)
74 b, 11f, 11g, 11h. Collect performance data by using System Monitor
75 l. Identify IO v memory v CPU bottlenecks
76 Practice Activity Number 7
Audit SQL Server Instances
77 a. implement a security strategy for auditing; 12c. configure server audits
78 e. Monitor elevated privileges as well as unsolicited attempts to connect
79 b. Configure an audit; 12d. Track who modified an object
80 Practice Activity Number 8
Plan installation
81 l. Choose the right hardware; 1a. Evaluate Installation Requirements
82 c. Plan Scale-up vs Scale-out basics
83 d. Plan for capacity; 1i. Windows-Level and Service Level Security
84 f. Design the storage for new databases
Install SQL Server and related services
85 a. Install an instance; 7b. Install Default and named instances
86 b, 2b, 2c, 4a, 4b. Design the installation of SQL Server and its components
87 a. Set up and configure all SQL Server components, including Visual Studio SSDT
88 a. Test connectivity
89 d. Configure an OS disk
90 Practice Activity 9
Implement a migration strategy
91 a. Restore versus detach attach
92 b. Migrate Security
93 c. Migrate from a previous version; 3d. Migrate to new hardware
94 e. Migrate systems and data from other sources
Install SQL Server, related services and additional SQL Server components
95 b. Configure full-text indexing
96 c. SSIS Security
97 d. Filestream
98 e. Filetable
Well done
99 Congratulations
100 Bonus Lecture
Resolve the captcha to access the links!