M20462 Administering Microsoft® SQL Server 2014 Databases

Module 1: Installing SQL Server 2014

• Introduction to the SQL Server Platform
• SQL Server Services and Configuration Options
• Considerations for Installing SQL Server
• Installing SQL Server 2014
• Lab : Installing SQL Server 2014
• Performing Pre-installation Stress Tests
• Installing a SQL Server Instance
• Performing Post-Installation Checks

Module 2: Working with Databases

• Overview of SQL Server Databases
• Creating Databases
• Using Database Files and Filegroups
• Moving Database Files
• Buffer Pool Extension
• Lab : Creating and Configuring Databases
• Creating a Storage Pool
• Adjusting tempdb Configuration
• Creating and Configuring a Database
• Attaching a Database
• Using Buffer Pool Extension

Module 3: SQL Server 2014 Recovery Models

• Backup Strategies
• Understanding SQL Server Transaction Logging
• Planning a SQL Server Backup Strategy
• Lab : Understanding SQL Server Recovery Models
• Planning a Backup Strategy
• Configuring Recovery Models
• Reviewing Recovery Models and Strategy

Module 4: Backup of SQL Server 2014 Databases

• Backing up Databases and Transaction Logs
• Managing Database Backups
• Working with Backup Options
• Lab : Backing Up SQL Server Databases
• Investigating Backup Compression
• Performing Transaction Log Backups
• Performing Differential Backups
• Performing Copy-Only Backups
• Performing Filegroup Backups

Module 5: Restoring SQL Server 2014 Databases

• Understanding the Restore Process
• Restoring Databases
• Working with Point-in-Time Recovery
• Restoring System Databases and Individual Files
• Lab : Restoring SQL Server Databases
• Determining a Restore Strategy
• Restoring a Database
• Using STANDBY Mode

Module 6: Importing and Exporting Data

• Transferring Data To/From SQL Server
• Importing and Exporting Table Data
• Inserting Data in Bulk
• Deploying and Upgrading Data-tier Applications
• Lab : Importing and Exporting Data
• Importing an Excel Spreadsheet
• Importing a CSV File
• Creating and Testing an Extraction Package
• Creating and Deploying a Data-tier Application
• Comparing Loading Performance

Module 7: Authenticating and Authorizing Users

• Authenticating Connections to SQL Server
• Authorizing Logins to Access Databases
• Authorization Across Servers
• Partially Contained Databases
• Lab : Authenticating Users
• Creating Logins
• Correcting an Application Login Issue
• Creating Database Users
• Correcting Access to a Restored Database

Module 8: Assigning Server and Database Roles

• Working with Server Roles
• Working with Fixed Database Roles
• Creating User-defined Database Roles
• Lab : Assigning Server and Database Roles
• Assigning Server Roles
• Assigning Fixed Database Roles
• Creating and Assigning User-defined Database Roles
• Checking Role Assignments
• After completing this module, you will be able to:
• Assign fixed server roles.
• Configure fixed database roles.
• Create and assign user-defined database roles.

Module 9: 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
• Assigning Schema-level Permissions
• Assigning Object-level Permissions
• Testing Permissions

Module 10: Protecting Data by Using Encryption and Auditing

• Options for Auditing Data Access in SQL Server
• Implementing SQL Server Audit
• Managing SQL Server Audit
• Protecting Data by Using Encryption
• Lab : Using Auditing and Encryption
• Determining Audit Configuration and Creating an Audit
• Creating Server Audit Specifications
• Creating Database Audit Specifications
• Testing Audit Functionality
• Using Transparent Data Encryption

Module 11: Automating SQL Server 2014 Management

• Automating SQL Server Management
• Working with SQL Server Agent
• Managing SQL Server Agent Jobs
• Multi-Server Management
• Lab : Automating SQL Server Management
• Creating a Job
• Scheduling a Job
• Troubleshooting a Failing Job
• Creating a Multi-Server Job

Module 12: Configuring Security for SQL Server Agent

• Understanding SQL Server Agent Security
• Configuring Credentials
• Configuring Proxy Server Accounts
• Lab : Configuring Security for SQL Server Agent
• Troubleshooting Job Execution Failure
• Resolving Security Issues
• Performing Further Troubleshooting

Module 13: Monitoring SQL Server 2014 by Using Alerts and Notifications

• Configuring Database Mail
• Monitoring SQL Server Errors
• Configuring Operators, Alerts, and Notifications
• Lab : Monitoring SQL Server by Using Alerts and Notifications
• Configuring Database Mail
• Implementing Notifications
• Implementing Alerts

Module 14: Performing Ongoing Database Maintenance

• Ensuring Database Integrity
• Maintaining Indexes
• Automating Routine Database Maintenance
• Lab : Performing Ongoing Database Maintenance
• Check Database Integrity by Using DBCC CHECKDB
• Correcting Index Fragmentation
• Creating a Database Maintenance Plan
• Investigating Table Lock Performance

Module 15: Tracing Access to SQL Server 2014

• Capturing Activity by Using SQL Server Profiler
• Improving Performance by Using the Database Engine Tuning Advisor
• Working with Tracing Options
• Distributed Replay
• Monitoring Locks
• Lab : Tracing Access to SQL Server
• Capturing a Trace by Using SQL Server Profiler
• Analysing a Trace by Using the Database Engine Tuning Advisor
• Configuring SQL Trace

Module 16: Monitoring SQL Server 2014

• Monitoring Activity
• Capturing and Managing Performance Data
• Analyzing Collected Performance Data
• SQL Server Utility
• Lab : Monitoring SQL Server
• Creating a Utility Control Point
• Configuring a Health Policy
• Configuring Instances for Data Collection
• Working with Data Collector Reports