Administering Microsoft SQL Server Databases

Writing T-SQL Queries for Microsoft SQL Server 2012

• Plan and install SQL Server.
• Describes the system databases, the physical structure of databases and the most common configuration options related to them.
• Explain the concept of the transaction log and SQL Server recovery models and implement different backup strategies available with SQL Server.
• Create SQL Server Backups.
• Restore SQL Server databases.
• Use the import/export wizards and explain how they relate to SSIS.
• Work with SQL Server security models, logins and users.
• Work with fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.
• Work with permissions and the assignment of permissions.
• Work with SQL Server Audit.
• Work with SQL Server Agent, jobs and job history.
• Implement SQL Server agent security, proxy accounts and credentials.
• Configure database mail, alerts and notifications.
• Create database maintenance plans.
• Work with SQL Profiler and SQL Trace stored procedures.
• Introduce DMVs and the configuration of data collection.
• Work with Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
• Troubleshoot SQL Server databases.

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

Une attestation de participation sera transmise aux participants