SQL Server 2016: Administer a Database Infrastructure

Course details

SQL Server comes with many features for monitoring, securing, optimizing, and supporting your database infrastructure. Learn how to use these features to administer your SQL Server instances, and prepare for the Microsoft MCSA Administering a SQL Database Infrastructure (70-764) certification exam.

Adam Wilbert covers four main skill areas required of SQL Server 2016 administrators: configuring access to data, managing backups, monitoring performance, and implementing the high-availability features of the product. Learn about data encryption, access control, role-level security, and dynamic data masking. Use SQL Server auditing to gain insights into the health and performance of your system, and determine upgrade paths. Discover how to back up SQL Server and perform full or partial restores, and monitor activity. Explore indexing and query execution plan management. Plus, learn to configure availability groups to mitigate hardware failure.

Learning objectives
Implementing encryption
Configuring backup and connection encryption
Creating and maintaining users
Managing permissions and roles
Configuring SQL Server auditing
Developing a backup strategy
Restoring databases
Identifying and recovering from corruption
Monitoring database activity and queries
Managing indexes
Managing statistics
Monitoring instances
Implementing log shipping
Implementing always on availability groups

Concepts

0. Introduction

01 - Welcome
02 - Use the exercise files
03 - What you should know
04 - Install the sample database

1. Configure Encryption

05 - Encryption overview
06 - Implement cell-level encryption
07 - Configure transparent data encryption
08 - Implement Always Encrypted
09 - Implement backup encryption
10 - Configure encryption for connections

2. Configure Data Access and Permissions

11 - Create and maintain users
12 - Manage database object permissions
13 - Create and maintain custom roles
14 - User options for Azure SQL Database
15 - Configure row-level security
16 - Configure dynamic data masking

3. Configure Auditing

17 - SQL Server audits
18 - Create an audit on SQL Server
19 - Query the SQL Server audit log
20 - Configure an Azure SQL Database audit
21 - Analyze logs from Azure SQL Database

4. Develop a Backup Strategy

22 - Back up very large databases
23 - Configure database recovery models
24 - Manage transaction log backups
25 - Configure backup automation
26 - Back up databases to Azure

5. Restore Databases

27 - Automate and test restores
28 - Perform page recovery
29 - Restore file groups
30 - Perform piecemeal restores

6. Manage Database Integrity

31 - Identify database corruption
32 - Implement database consistency checks
33 - Recover from database corruption

7. Monitor Database Activity

34 - Monitor current sessions
35 - Identify causes of blocking activity
36 - Find sessions that use tempdb resources
37 - Configure the data collector

8. Monitor Queries

38 - Manage the Query Store
39 - Configure trace events
40 - Identify problematic execution plans
41 - Server health and extended events

9. Manage Indexes

42 - Identify and repair index fragmentation
43 - Identify and create missing indexes
44 - Identify and drop underutilized indexes
45 - Manage existing columnstore indexes

10. Manage Statistics

46 - Statistics objects
47 - Identify and correct outdated statistics
48 - Implement auto update statistics
49 - Implement statistics for large tables

11. Monitor SQL Server Instances

50 - Configure database mail
51 - Create and manage operators
52 - Create and manage SQL Server Agent alerts
53 - Define failure actions
54 - Identify available space on data volumes
55 - Introduction to Policy-Based Management

12. Implement Log Shipping

56 - Log shipping overview
57 - Configure log shipping

13. Implement Always On Availability Groups

58 - Configure Windows clustering
59 - Create an availability group
60 - Configure read-only routing
61 - Manage failover
62 - Create distributed availability groups

Conclusion

63 - Next steps