Administering Microsoft SQL Server Databases

Module 1: Introduction to SQL Server 2016 and its Toolset

• This module introduces the entire SQL Server platform and its major tools. It covers editions, versions, basics of network listeners, and concepts of services and service accounts.

Lessons

• Introduction to the SQL Server Platform
• Working with SQL Server Tools
• Configuring SQL Server Services
• Lab : Introduction to SQL Server and its Toolset
• Verifying SQL Server Component Installation
• Altering Service Accounts for New Instance
• Enabling Named Pipes Protocol for Both Instances
• Creating an Alias for AdvDev
• Ensuring SQL Browser is Disabled and Configure a Fixed TCP/IP Port (Only if time permits

After completing this module, students will be able to:

• Describe the SQL Server Platform.
• Work with SQL Server Tools.
• Configure SQL Server Services.

Module 2: Preparing Systems for SQL Server 2012

• This module covers planning for an installation related to SQL Server I/O requirements, 32 bit vs 64 bit, memory configuration options and I/O subsystem pre-installation checks using SQLIOSim and SQLIO.

Lessons

• Overview of SQL Server Architecture
• Planning Server Resource Requirements
• Pre-installation Testing for SQL Server
• Lab : Preparing Systems for SQL Server
• Adjust memory configuration
• Pre-installation Stress Testing
• Check Specific I/O Operations

After completing this module, students will be able to:

• Describe the SQL Server architecture.
• Plan for server resource requirements.
• Conduct pre-installation stress testing for SQL Server.

Module 3: Installing and Configuring SQL Server 2012

• This module details installing and configuring SQL Server.

Lessons

• Preparing to Install SQL Server
• Installing SQL Server
• Upgrading and Automating Installation
• Lab : Installing and Configuring SQL Server
• Review installation requirements
• Install the SQL Server instance
• Perform Post-installation Setup and Checks
• Configure Server Memory

After completing this module, students will be able to:

• Prepare to install SQL Server.
• Install SQL Server.
• Upgrade and automate the installation of SQL Server.

Module 4: Working with Databases

• This module describes how data is stored in databases, how to create databases, and how to move databases either within a server or between servers.

Lessons

• Overview of SQL Server Databases
• Working with Files and Filegroups
• Moving Database Files
• Lab : Working with Databases
• Adjust tempdb configuration
• Create the RateTracking database
• Attach the OldProspects database
• Add multiple files to tempdb

After completing this module, students will be able to:

• Describe the role and structure of SQL Server databases.
• Work with files and filegroups.
• Move database files within servers and between servers.

Module 5: Understanding SQL Server 2016 Recovery Models

• This module describes the concept of the transaction log and SQL Server recovery models. It introduces the different backup strategies available with SQL Server.

Lessons

• Backup Strategies
• Understanding SQL Server Transaction Logging
• Planning a SQL Server Backup Strategy
• Lab : Understanding SQL Server Recovery Models
• Plan a backup strategy
• Configure Recovery Models
• Review recovery models and strategy

After completing this module, students will be able to:

• Describe the critical concepts surrounding backup strategies.
• Explain the transaction logging capabilities within the SQL Server database engine.
• Plan a SQL Server backup strategy.

• Module 6: Backup of SQL Server 2016 Databases
• This module describes SQL Server Backup and the backup types.

Lessons

• Backing up Databases and Transaction Logs
• Managing Database Backups
• Working with Backup Options
• Lab : Backup of SQL Server Databases
• Investigate backup compression
• Transaction log backup
• Differential backup
• Copy-only backup
• Partial backup

After completing this module, students will be able to:

• Back up databases and transaction logs.
• Manage database backups.
• Work with more advanced backup options.

Module 7: Restoring SQL Server 2016 Databases

• This module describes the restoration of databases.

Lessons

• Understanding the Restore Process
• Restoring Databases
• Working with Point-in-time recovery
• Restoring System Databases and Individual Files
• Lab : Restoring SQL Server 2012 Databases
• Determine a restore strategy
• Restore the database
• Using STANDBY mode

After completing this module, students will be able to:

• Understand the restore process.
• Restore databases.
• Work with Point-in-time Recovery.
• Restore system databases and individual files.

Module 8: Importing and Exporting Data

• This module covers the use of the import/export wizards and explains how they relate to SSIS. Also introduces BCP.

Lessons

• Transferring Data To/From SQL Server
• Importing & Exporting Table Data
• Inserting Data in Bulk
• Lab : Importing and Exporting Data
• Import the Excel spreadsheet
• Import the CSV file
• Create and test an extraction package
• Compare loading performance

After completing this module, students will be able to:

• Transfer data to and from SQL Server.
• Import and export table data.
• Insert data in bulk and optimize the bulk insert process.

Module 9: Authenticating and Authorizing Users

• This module covers SQL Server security models, logins and users.

Lessons

• Authenticating Connections to SQL Server
• Authorizing Logins to Access Databases
• Authorization Across Servers
• Lab : Authenticating and Authorizing Users
• Create Logins
• Correct an Application Login Issue
• Create Database Users
• Correct Access to Restored

After completing this module, students will be able to:

• Describe how SQL Server authenticates connections.
• Describe how logins are authorized to access databases.
• Explain the requirements for authorization across servers.

Module 10: Assigning Server and Database Roles

• This module covers fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.

Lessons

• Working with Server Roles
• Working with Fixed Database Roles
• Creating User-defined Database Roles
• Lab : Assigning Server and Database Roles
• Assign Server Roles
• Assign Fixed Database Roles
• Create and Assign User-defined Database Roles
• Check Role Assignments

After completing this module, students will be able to:

• Work with server roles.
• Work with fixed database roles.
• Create user-defined database roles.

Module 11: Authorizing Users to Access Resources

• This module covers permissions and the assignment of permissions.

Lessons

• Authorizing User Access to Objects
• Authorizing Users to Execute Code
• Configuring Permissions at the Schema Level
• Lab : Authorizing Users to Access Resources
• Assign Schema-level Permissions
• Assign Object-level Permissions
• Test Permissions

After completing this module, students will be able to:

• Authorize user access to objects.
• Authorize users to execute code.
• Configure permissions at the schema level.

Module 12: Auditing SQL Server Environments

• This module covers SQL Server Audit.

Lessons

• Options for Auditing Data Access in SQL
• Implementing SQL Server Audit
• Managing SQL Server Audit
• Lab : Auditing SQL Server Environments
• Determine audit configuration and create audit
• Create server audit specifications
• Create database audit specifications
• Test audit functionality

After completing this module, students will be able to:

• Describe the options for auditing data access in SQL Server.
• Implement SQL Server Audit.
• Manage SQL Server Audit.

Module 13: Automating SQL Server 2016 Management

• This module covers SQL Server Agent, jobs and job history.

Lessons

• Automating SQL Server Management
• Working with SQL Server Agent
• Managing SQL Server Agent Jobs
• Lab : Automating SQL Server Management
• Create a Data Extraction Job
• Schedule the Data Extraction Job
• Troubleshoot a Failing Job

After completing this module, students will be able to:

• Automate SQL Server Management.
• Work with SQL Server Agent.
• Manage SQL Server Agent jobs.

• Module 14: Configuring Security for SQL Server Agent
• This module covers SQL Server agent security, proxy accounts and credentials.

Lessons

• Understanding SQL Server Agent Security
• Configuring Credentials
• Configuring Proxy Accounts
• Lab : Configuring Security for SQL Server Agent
• Troubleshoot job execution failure
• Resolve the security issue
• Perform further troubleshooting

After completing this module, students will be able to:

• Explain SQL Server Agent security.
• Configure credentials.
• Configure Proxy accounts.

Module 15: Monitoring SQL Server 2016 with Alerts and Notifications

• This module covers the configuration of database mail, alerts and notifications.

Lessons

• Configuration of Database Mail
• Monitoring SQL Server Errors
• Configuring Operators, Alerts and Notifications
• Lab : Monitoring SQL Agent Jobs with Alerts and Notifications
• Configure Database Mail
• Implement Notifications
• Implement Alerts

After completing this module, students will be able to:

• Configure database mail.
• Monitor SQL Server errors.
• Configure operators, alerts and notifications.

Module 16: Performing Ongoing Database Maintenance

• This module covers database maintenance plans.

Lessons

• Ensuring Database Integrity
• Maintaining Indexes
• Automating Routine Database Maintenance
• Lab : Performing Ongoing Database Maintenance
• Check database integrity using DBCC CHECKDB
• Correct index fragmentation
• Create a database maintenance plan
• Investigate table lock performance

After completing this module, students will be able to:

• Ensure database integrity.
• Maintain indexes.
• Automate routine database maintenance.

Module 17: Tracing Access to SQL Server 2016

• This module covers SQL Profiler and SQL Trace stored procedures.

Lessons

• Capturing Activity using SQL Server Profiler
• Improving Performance with the Database Engine Tuning Advisor
• Working with Tracing Options
• Lab : Tracing Access to SQL Server 2016
• Capture a trace using SQL Server Profiler
• Analyze a trace using Database Engine Tuning Advisor
• Configure SQL Trace

After completing this module, students will be able to:

• Capture activity using SQL Server Profiler and Extended Events Profiler.
• Improve performance with the Database Engine Tuning Advisor.
• Work with tracing options.

Module 18: Monitoring SQL Server 2016

• This module introduces DMVs and the configuration of data collection.

Lessons

• Monitoring Activity
• Capturing and Managing Performance Data
• Analyzing Collected Performance Data
• Lab : Monitoring SQL Server 2016
• Investigating DMVs
• Configure Management Data Warehouse
• Configure Instances for Data Collection
• Work with Data Collector Reports

After completing this module, students will be able to:

• Monitor current activity.
• Capture and manage performance data.
• Analyze collected performance data.

Module 19: Managing Multiple Servers

• This module covers Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.

Lessons

• Working with Multiple Servers
• Virtualizing SQL Server
• Deploying and Upgrading Data-Tier Applications
• Lab : Managing Multiple Servers
• Configure CMS and execute multi-server queries
• Deploy a data-tier application
• Register and extract a data-tier application
• Upgrade a data-tier application

After completing this module, students will be able to:

• Work with multiple servers.
• Describe options for virtualizing SQL Server.
• Deploy and upgrade Data-Tier Applications.

Module 20: Troubleshooting Common SQL Server 2016 Administrative Issues

• This module covers common issues that require troubleshooting and gives guidance on where to start looking for solutions.

Lessons

• SQL Server Troubleshooting Methodology
• Resolving Service-related Issues
• Resolving Concurrency Issues
• Resolving Login and Connectivity Issues
• Lab : Troubleshooting Common Issues
• Troubleshoot and resolve SQL Server administrative issues

After completing this module, students will be able to:

• Explain SQL Server troubleshooting methodology.
• Resolve service-related issues.
• Resolve concurrency issues.
• Resolve login and connectivity issues.