MOC 20764 Administering a SQL Database Infrastructure
How To Take This Class
Live Instructor-Led Online Class
Cost: $2,375.00 or 5 Vouchers
- Open enrollment class for individuals
- Live class with an instructor
- Free class retakes forever!
- Six months of instructor email support
- Hands-on exercises and student labs
- Classes never cancelled due to low enrollment
- Money-back guarantee
Sorry, no courses are scheduled at this time. Please Contact Us to schedule this course.
Onsite or Offsite Group Training
Cost: Based on number of students
- For groups as small as 3 people
- Class Held at our location or yours
- Hands-on exercises and student labs
- Customization at no extra charge
- Six months of instructor email support
- All-inclusive pricing
- Money-back guarantee
Course Duration
5 Days
What Students Receive
- Expert instruction from a SQL Server Trainer
- A printed courseware manual with samples and hands-on exercises for use during and after class.
- Sample course files and solutions
- Access to a complete, online SQL Server lab environment both during and after class to practice your new skills.
- A course completion certificate upon successful completion of the course
Course Description
Course Objectives
- Authenticate and authorize users
- Assign server and database roles
- Authorize users to access resources
- Protect data with encryption and auditing
- Describe recovery models and backup strategies
- Backup SQL Server databases
- Restore SQL Server databases
- Automate database management
- Configure security for the SQL Server agent
- Manage alerts and notifications
- Managing SQL Server using PowerShell
- Trace access to SQL Server
- Monitor a SQL Server infrastructure
- Troubleshoot a SQL Server infrastructure
- Import and export data
Course Audience
Course Prerequisites
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
- Working knowledge of Transact-SQL.
- Working knowledge of relational databases.
- Some experience with database design.
Course Syllabus
- SQL Server Security
- Authenticating Connections to SQL Server
- Authorizing Logins to Connect to databases
- Authorization Across Servers
- Partially Contained Databases
- Lab: Authenticating Users
- Create Logins
- Create Database Users
- Correct Application Login Issues
- Configure Security for Restored Databases
- Assigning Server and Database Roles
- Working with server roles
- Working with Fixed Database Roles
- Assigning User-Defined Database Roles
- Lab: Assigning server and 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
- Lab: Authorizing users to access resources
- 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
- Lab: Using Auditing and Encryption
- Working with SQL Server Audit
- Encrypt a Column as Always Encrypted
- Encrypt a Database using TDE
- Recovery Models and Backup Strategies
- Understanding Backup Strategies
- SQL Server Transaction Logs
- Planning Backup Strategies
- Lab: Understanding SQL Server recovery models
- Plan a Backup Strategy
- Configure Database Recovery Models
- Backing Up SQL Server Databases
- Backing Up Databases and Transaction Logs
- Managing Database Backups
- Advanced Database Options
- Lab: Backing Up Databases
- Backing Up Databases
- Performing Database, Differential, and Transaction Log Backups
- Performing a Partial Backup
- Restoring SQL Server 2016 Databases
- Understanding the Restore Process
- Restoring Databases
- Advanced Restore Scenarios
- Point-in-Time Recovery
- Lab: Restoring SQL Server Databases
- Restoring a Database Backup
- Restring Database, Differential, and Transaction Log Backups
- Performing a Piecemeal Restore
- Automating SQL Server Management
- Automating SQL Server management
- Working with SQL Server Agent
- Managing SQL Server Agent Jobs
- Multi-server Management
- Lab: Automating SQL Server Management
- Create a SQL Server Agent Job
- Test a Job
- Schedule a Job
- Configure Master and Target Servers
- Configuring Security for SQL Server Agent
- Understanding SQL Server Agent Security
- Configuring Credentials
- Configuring Proxy Accounts
- Lab: Configuring Security for SQL Server Agent
- 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
- Lab: Monitoring SQL Server with Alerts and Notifications
- Configuring Database Mail
- Configuring Operators
- Configuring Alerts and Notifications
- Testing Alerts and Notifications
- Introduction to Managing SQL Server by Using PowerShell
- Getting Started with Windows PowerShell
- Configure SQL Server using PowerShell
- Administer and Maintain SQL Server with PowerShell
- Managing Azure SQL Databases using PowerShell
- Lab: Using PowerShell to Manage SQL Server
- Getting Started with PowerShell
- Using PowerShell to Change SQL Server settings
- Tracing Access to SQL Server with Extended Events
- Extended Events Core Concepts
- Working with Extended Events
- Lab: Extended Events
- Using the System_Health Extended Events Session
- Tracking Page Splits Using Extended Events
- Monitoring SQL Server
- Monitoring activity
- Capturing and Managing Performance Data
- Analyzing Collected Performance Data
- SQL Server Utility
- Lab: Monitoring SQL Server
- Monitor current activity
- Capture and manage performance data
- Analyze collected performance data
- Configure SQL Server Utility
- Troubleshooting SQL Server
- A Trouble Shooting Methodology for SQL Server
- Resolving Service Related Issues
- Resolving Connectivity and Log-in issues
- Lab: Troubleshooting Common Issues
- 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
- Transferring Data to and from SQL Server
- Importing and Exporting Table Data
- Using bcp and BULK INSERT to Import Data
- Deploying and Upgrading Data-Tier Application
- Lab : 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