Description

This course will provide you with the knowledge necessary to install, configure and administer a SQL Server 2014 or 2012 database server. You will discover the architecture of the server, learn how to configure it and monitor its operation, manage storage spaces, automate tasks and perform backups and restores.

Who is this training for ?

For whom ?

Database administrators, operations managers, system administrators and engineers.

Prerequisites

Training objectives

Install and configure the Microsoft SQL Server DBMS Create a database and organize data file storage Define permissions and access controls on the database and manage certificates Automate and schedule routine administration tasks Create data backup and recovery plans Implement continuous monitoring of the database on modifications and its performance

Training program

• Installation et configuration
  • The tasks of the administrator and the means at his disposal.
  • General architecture of SQL Server 2012.
  • Installation, migration.
  • Configuration tools.
  • SQL Server Configuration Manager.
  • Service and network management tool.
  • Presentation of client tools.
  • System objects and databases.
  • Practical work Installation of SQL Server.
  • Using dynamic consultation tools.
  • Configuration using Policy Based Management (PBM).
• Storage space management
  • Storage engine and physical storage.
  • Object allocation.
  • Table partitioning and indexes.
  • Partition function.
  • Partition scheme.
  • Calculated columns.
  • Managing database size and growth.
  • Database dumps ( SNAPSHOT).
  • Snapshot operationsrnInserting and importing data.
  • Main options for BULK INSERT or BCP.
  • Practical workr nCreating a database.
  • File management.
  • Creating snapshots.
• Security and encryption
  • External and internal security.
  • Secure connection and sessions, internal security model.
  • Authentication modes.
  • Resolution names.
  • Roles: server roles, fixed database roles, user-created roles, application roles.
  • Assignment of privileges (Grant, Revoke , Deny .
  • .
  • ).
  • Ownership chaining.
  • Contained Databases.
  • Context of 'execution.
  • Data encryption (symmetric, asymmetric).
  • Encryption architecture.
  • Database master key.
  • Backing up and restoring keys.
  • Extensible Key Management.
  • SQL Server certificates, example usage.
  • Encryption metadata.
  • Transparent Data Encryption.
  • Database audit, syntax for creating events to audit.
  • Practical work Manipulation of permissions and access controls.
  • Certificate management.
• Administrative tasks
  • Task scheduling by the SQL Server agent.
  • Job organization: job categories, step types, precedence rules and errors, notifications.
  • Job scheduling, MSDB specific roles.
  • Execution log, monitor.
  • SQL Agent settings.
  • Configuring alerts and of notifications.
  • Managing operators.
  • Sending emails from SQL Server: configuration of Database Mail and Agent parameters for sending emails.
  • Configuring multi-server jobs.
  • Event redirection DDL triggers.
  • SQL Server events: performance, WMI, WQL.
  • Physical integrity check.
  • Retrieve low-level information about the SQL Server (DBCC) engine.
  • Index structure reminders and index maintenance.
  • Indexed views.
  • Full text index.
  • Recalculation of statistics.
  • The maintenance plan tool.
  • Use SQL Server Data Tools.
  • Practical work Automation and scheduling of administrative work.
• Backups and restores
  • The different types of backup (full, differential, journal.
  • .
  • ) Recovery mode (full, simple, bulk journaled.
  • .
  • ) and influence on data recovery.
  • Establishment of a backup strategy.
  • Backup security .
  • T-SQL backups: integrity.
  • Multi-file, multi-family backups.
  • Scheduling backups.
  • Base restoration, repairing damaged environments.
  • Log restoration.
  • Restoration in a different database.
  • Page restoration.
  • Base backup system.
  • Master restoration.
  • Practical work Creation of backup and restoration configurations.
• Monitoring and performance
  • The profiler and the eXtended Events (XEvents) to trace usage.
  • Extended events.
  • DDL triggers to trace structure modifications.
  • Metadata views and dynamic management views.
  • Metadata stored procedures.
  • Windows performance monitor.
  • Counters and user events.
  • Database Tuning Advisor.
  • Visualization of locks and blocks, detection and treatment of deadlocks.
  • Configuration of the server and databases.
  • The resource governor.
  • Practical work Setting up monitoring on structure modifications.
  • Using the views of dynamic management.
  • Management of a deadly lock.