Description

This internship will provide you with the necessary knowledge to install, configure and administer SQL Server 2012. You will discover the architecture of the server, learn how to configure it, optimize data storage, automate tasks as well as secure and guarantee data integrity.

Who is this training for ?

For whom ?

Experienced non-SQL Server database administrators, application administrators, and consultants.

Prerequisites

Training objectives

Install and configure the SQL Server database

Organize and optimize the physical storage of objects on the database

Automate common administrative tasks

Use several modes of saving and restoring database data

Repair damaged databases

Ensure the maintenance of indexes and statistics

Training program

• Installation et configuration
  • - SQL Server architecture.
  • - Installation, migration.
  • - Service configuration tools.
  • - SQL Server Management Studio and SQLcmd.
  • - exe.
  • - Instances, schema databases.
  • - Objects and system databases.
  • - The "Data Management Views" .
  • - Administration policies.
  • - Configure the instance.
  • - Practical work Use dynamic consultation tools.
  • - Configuration using Policy Based Management (PBM).
• Storage space management
  • - Storage engine and physical storage.
  • - Object allocation.
  • - Table partitioning and indexes.
  • - Calculated columns.
  • - Manage database growth.
  • - Database snapshots (SNAPSHOT).
  • - Transparent Data Encryption: encrypt storage.
  • - Practical work Creating a database.
  • - File management.
  • - Creating snapshots.
  • - Setting up TDE.
• Automation of administrative tasks
  • - SQL Server agent scheduling tasks.
  • - MSDB specific roles.
  • - Execution log, monitor.
  • - Alerts and notifications.
  • - Operator management.
  • - Sending emails: configure settings.
  • - Multi-server work.
  • - Redirection of emails events.
  • - DDL triggers.
  • - Events: performance, WMI, WQL.
  • - Hands-on labs Automate and schedule jobs.
• Backups and restores
  • - Backup types.
  • - Recovery modes and their influence.
  • - Backup strategy.
  • - Security.
  • - Planning.
  • - Restoration, repair of damaged environments.
  • - The different restorations.
  • - Backup of system databases.
  • - Practical work Configure backups and restores.
• Routine maintenance
  • - Check physical integrity.
  • - Recover engine information (DBCC).
  • - Repair damaged databases.
  • - Diagnostics of indexes in SQL Server.
  • - Query metrics and execution plan.
  • - Setting up indexes.
  • - Maintaining indexes and statistics.
  • - The maintenance plan tool.
  • - Practical work Maintain indexes and statistics.
  • - Check storage integrity.
• Introduction to High Availability
  • - The principle of "log shipping" and mirroring.
  • - AlwaysOn: availability groups, replicas, listeners.