Prochaines sessions
Programme
Module 1: Introduction to SQL Server 2016 and its Toolset
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
- Description
- 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
