MCSA SQL Exam 70-764: Administering a SQL Database Infrastructure 2016

Microsoft SQL Server 2016 and Tools

• Overview Microsoft SQL Server 2016
• MS SQL Server Management Studio
• Creating and Managing T-SQL scripts
• Microsoft SQL Server 2016 architecture

SQL Azure

• Overview of SQL Azure
• Features of SQL Azure.
• Provision a SQL Azure server and configure its security.

T-SQL Querying

• T-SQL Scripts, Sets, Predicate Logic
• Logical Order of Operations in SELECT statements
• SELECT Statements, filter data, sort data using ORDER BY

SELECT Queries

• SELECT statement, focusing on queries against a single table.
• Eliminating Duplicates with DISTINCT
• Using Column and Table Aliases
• CASE Expressions, DISTINCT clause

Configuring Database Security - Logins, Users, and Roles

• SQL Server basic concepts
• Explain the use of Logins
• Explain the use of Users
• Understanding of Logins and Users
• Create Logins
• Create Database Users
• Creating Logins and Mapping to Users
• SQL Server connection authentication
• Authorizing Logins to Access Databases
• Creating and Maintaining Users
• Authorizing Users to Access Database Objects
• Granting Permissions to Users
• Managing Database Object Permissions
• Creating and Maintaining Custom Roles
• Granting Permissions to Roles
• Configuring Contained & Non-Contained Databases
• Correct Application Login Issues

Server Roles and Database Roles

• Explain the use of server roles to manage server-level security.
• Explain the use of fixed database roles
• Explain the use of custom database roles and application roles to manage database-level security
• Working with server roles
• Working with Fixed Database Roles
• Assigning User-Defined Database Roles
• Assigning Server Roles
• Assigning Fixed Database Roles
• Assigning User-Defined Database Roles
• Verifying Security

Authorizing Users to Access Resources

• Authorizing User Access to Objects
• Authorizing Users to Execute Code
• Configuring Permissions at the Schema Level
• Granting, Denying, and Revoking Permissions on Objects
• Granting EXECUTE Permissions on Code
• Granting Permissions at the Schema Level

Protecting Data with Encryption and Auditing

• Options for auditing data access in SQL Server
• Implementing SQL Server Audit
• Managing SQL Server Audit
• Protecting Data with Encryption
• Working with SQL Server Audit
• Encrypt a Column as Always Encrypted
• Encrypt a Database using TDE

Database Recovery Models and Backup Strategies

• Understanding Backup Strategies
• SQL Server Transaction Logs
• Planning Backup Strategies
• Plan a Backup Strategy
• Configure Database Recovery Models

Managing Backup of Databases

• Developing a Backup Strategy
• Backup Types – Full vs Differential vs Transaction Log Backup
• Managing Transaction Log Backups
• Configuring Backup Automation
• Advanced Database Options
• Performing Database, Differential, and Transaction Log Backups
• Performing a Partial Backup

Managing Restore of Databses

• Explain the restore process
• Recovery States
• Performing Restores
• Performing Recovery
• Performing POINT-IN-TIME Recovery
• Performing Restore with Recovery, NoRecovery, and Standby
• Restoring Individual Files
• Perform advanced restore operations

Monitoring Database Activity

• Monitoring Current Sessions
• Configuring the Data Collector

Monitoring Queries

• Extended Events
• Trace Events

Managing Indexes

• Identifying Index Fragmentation
• Repairing Index Fragmentation

Monitoring SQL Server Instances

• Creating and Managing Operators
• Creating and Managing SQL Agent Alerts
• Configuring Database Mail
• Receiving Email Notifications on Job Execution

Automating SQL Server Management

• Explain the methods for automating SQL Server Management
• Creating SQL Server Agent Jobs
• Working with SQL Server Agent
• Managing SQL Server Agent Jobs
• Scheduling and Automating SQL Server Jobs
• Multi-server Management
• Configure a SQL Server Agent Job
• Test a Job
• Schedule a Job
• Configure Master and Target Servers

Configuring Security for SQL Server Agent

• Explain SQL Server Agent security
• Understanding SQL Server Agent Security
• Configuring Credentials
• Configuring Proxy Accounts
• Analyzing Problems in SQL Server Agent
• Configuring a Credential
• Configuring a Proxy Account
• Configuring and testing the Security Context of a Job

Monitoring SQL Server with Alerts and Notifications

• Monitoring SQL Server Errors
• Configuring Database Mail
• Operators, Alerts, and Notifications
• Alerts in Azure SQL Database
• Configuring Database Mail
• Configuring Operators
• Configuring Alerts and Notifications
• Testing Alerts and Notifications

Overview of Managing SQL Server by using PowerShell

• PowerShell to Manage SQL Server
• Getting Started with PowerShell
• Using PowerShell to Change SQL Server settings
• Describe the benefits of PowerShell and its fundamental concepts
• Configure SQL Server by using PowerShell
• Administer and maintain SQL Server by using PowerShell
• Manage an Azure SQL Database by using PowerShell

Tracing Access to SQL Server with Extended events

• Using the System_Health Extended Events Session
• Tracking Page Splits Using Extended Events

Monitoring SQL Server

• Monitor current activity
• Capture and manage performance data
• Analyze collected performance data
• Configure SQL Server Utility

Troubleshooting SQL Server

• Troubleshoot and Resolve a SQL Login Issue
• Troubleshoot and Resolve a Service Issue
• Troubleshoot and Resolve a Windows Login Issue
• Troubleshoot and Resolve a Job Execution Issue
• Troubleshoot and Resolve a Performance Issue

Importing and Exporting Data

• Import and Excel Data Using the Import Wizard
• Import a Delimited Text File Using bcp
• Import a Delimited Text File using BULK INSERT
• Create and Test an SSIS Package to Extract Data
• Deploy a Data-Tier Application

Exam 70-765 : Provisioning SQL Databases

Opitimizing and Enchancing the Performance

• SQL Server Profiler
• Database Engine Tuning Advisor
• Trace Files

Components of SQL Server 2016

• Introduction
• Architecture
• Services
• Tools

Installation of SQL Server 2016

• Installing
• Configuring
• Storage
• Authentication Modes
• Configuring TempDB

Managing Instances

• Default
• Named

Managing Databases

• Creating Databases
• Managing Files and Filegroups
• Creating Secondary Files
• Adding Files and Filegroups

Microsoft Azure

• Introduction
• Creating Databases
• Migrating

High Availability, Disaster Recovery, and Always-On

• Configuring and Managing Database Mirroring
• Configuring and Managing Transaction Log Shipping
• Configuring and Managing Replication
• Configuring and Managing Failover Clustering