Prochaines sessions
Programme
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.