After completing this course, students will be able to:

• Plan and install SQL Server.
• Describe system databases, the physical structure of databases, and the most common configuration options related to them.
• Explain the concept of the transaction log and the SQL Server recovery models and implement different backup strategies that are available with SQL Server.
• Back up SQL Server databases.
• Restore SQL Server databases.
• Import and export data by using wizards, the bulk copy utility, and data-tier applications.
• Work with SQL Server security models, logins, users, and contained databases.
• Work with server roles and database roles.
• Use permissions to secure resources.
• Use SQL Server audit and Transparent Data Encryption.
• Work with SQL Server Agent to configure and execute jobs.
• Implement SQL Server Agent security, proxy accounts, and credentials.
• Configure database mail, alerts, and notifications.
• Create database maintenance plans.
• Use SQL Server Profiler, SQL Trace, and Distributed Relay.
• Use Distributed Management Views and configure data collection.

Course outline:

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

Module 2: Working with Databases

• Overview of SQL Server Databases
• Creating Databases
• Using Database Files and Filegroups
• Moving Database Files
• Buffer Pool Extension

Module 3: SQL Server 2014 Recovery Models

• Backup Strategies
• Understanding SQL Server Transaction Logging
• Planning a SQL Server Backup Strategy

Module 4: Backup of SQL Server 2014 Databases

• Backing up Databases and Transaction Logs
• Managing Database Backups
• Working with Backup Options

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

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

Module 7: Authenticating and Authorizing Users

• Authenticating Connections to SQL Server
• Authorizing Logins to Access Databases
• Authorization Across Servers
• Partially Contained Databases

Module 8: Assigning Server and Database Roles

• Working with Server Roles
• Working with Fixed Database Roles
• Creating 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

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

Module 11: Automating SQL Server 2014 Management

• Automating SQL Server Management
• Working with SQL Server Agent
• Managing SQL Server Agent Jobs
• Multi-Server Management

Module 12: Configuring Security for SQL Server Agent

• Understanding SQL Server Agent Security
• Configuring Credentials
• Configuring Proxy Server Accounts

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

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

Module 14: Performing Ongoing Database Maintenance

• Ensuring Database Integrity
• Maintaining Indexes
• Automating Routine Database Maintenance

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

Module 16: Monitoring SQL Server 2014

• Monitoring Activity
• Capturing and Managing Performance Data
• Analyzing Collected Performance Data
• SQL Server Utility