Toutes nos formations sont désormais disponibles en "Live Virtual classes". Contactez-nous pour plus d’informations : formation@oxiane.luToutes nos formations sont désormais disponibles en "Live Virtual classes". Contactez-nous pour plus d’informations : formation@oxiane.lu

Administering Microsoft SQL Server Databases

Accueil » Formations » Data » Administering Microsoft SQL Server Databases

Cette formation vous permettra de tout savoir sur l'administration de SQL Server 2016,
avec la possibilité d'ajouter "sur demande" les nouveautés de SQL Server 2016 ( en fonction de la version choisie ).

2290 € HT 4 jours SQL S2014A

Prochaines sessions

Formulaire d'inscription

Télécharger le programme

Contactez nous pour un devis

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
Google+
LinkedIn
Viadeo