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

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.