SQL Server Administration Training Course

Highlights

• Install Microsoft SQL Server 2012/14 and the bundled tools

• Learn many tools that facilitate effective Database Administration

• Monitor the smooth running of the Database Server

• Maintain the Database and Indexes 

• Setup and control Database Security 

• Cope effectively with Database Disaster Recovery

• Implement High Availability and Replication

• Use relevant TSQL commands

• Understand the basics of Integration Services 

• Implement SQL Server Full-text search

Course Details

SQL Server 2012/2014 Overview

• Availability and Scalability Features

• Security Features

• Data Management Features

• Administration and Maintenance Features

• Development Features

• To enable SQLiMail

• Testing mail delivery

Installing SQL Server 2012/2014

• SQL Server Editions

• Installing Microsoft SQL Server

• System Requirements

• Preparing for a SQL 2012/14 Installation

• Installation Checklist

• Setup Features

• Using the System Consistency Checker

• Component Install

• Unattended Installation

• Installation Sequence

• Installation Maintenance

• Upgrading from previous versions of SQL Server

• Retaining SQL previous versions

• To verify the Installation

• Administrative Tools

Upgrading to SQL Server Management Studio

• Management Studio Windows

• Registered Servers

• Object Explorer

• Review Database Objects

• Modify Database Properties

• Create Database Scripts

• The Maintenance Plans Node

• Solution Explorer

• Query Editor

• Template Explorer

• Dynamic Help

• Upgrading to SQL Server Computer Manager

• SQL Server 2012 or 2014 Services node

• SQL Server 2012 or 2014 Network Configuration node

• SQL Server 2012 or 2014 Native Client Configuration node

• The SQLCMD Management Utility

• SQLCMD Syntax

• SQLCMD Variables

• Dedicated Administrative Connection

• SQL Server Management Studio SQLCMD Mode

• Using SQL Management Objects

• SMO Object Model

Monitoring the Database Server

• Managing Metadata Views

• Metadata Storage

• System Views

• Information Schema

• Catalog Views

• Sys All

• Dynamic Management Views

• System Stored Procedures

Database and Index Maintenance

• Index Management

• New Index Features

• Creating Indexes

• Online Index Maintenance

• Parallel (multiple CPU) Index Operations

• Lock Options

• Included Columns in Indexes

• Partitioned Tables and Indexes

• XML Index Support

• Altering an Index

• Rebuilding an Index

• Reorganising an Index

• Disabling an Index

• Dropping an Index

• Index Fragmentation

• Using the Database Tuning Advisor

Securing SQL Server 2012 2014

• Implementing Securables

• New Security Features

• Principals

• Securables

• Permissions

• Permission Types

• Managing Logins

• Managing Users

• Managing Schemas

• Schema Definition

• Using Schemas

• Managing Execution Context

• Managing Permissions

• Manage Server Permissions

• Manage Server-Scope Securable Permissions

• Manage Database Permissions

• Manage Database-Scope Securable Permissions

• Managing Certificates

• Native Data Encryption

• Code and Module Signing

SQL Server Disaster Recovery

• Disaster Recovery

• SQL 2012 / 2014 Disaster Recovery Feature

• Enhanced Features

• Database Snapshots

• Creating a Snapshot

• Common Snapshot Applications

• Undeleting table rows

• Undoing a table update

• Recovering a dropped object

• Backup and Restore

• Backup Changes

• Restore Changes

• Media Integrity Changes

• Recovering the MASTER Database

• Create a mirrored backup

• Restore from the backup mirror

• Create a snapshot

• Restore the snapshot data

• Automate the creation of daily snapshots

Database Availability

• SQL Server 2012 /2014 Data Availability

• Database Mirroring

• The Witness Server and Automatic Failover

• Configuring Database Mirroring

• Transaction Safety Level

• Operating Modes

• High Availability Operating Mode

• High Protection Operating Mode

• High Performance Operating Mode

• Mirroring Security

• Monitoring Mirroring

• Database States for Database Mirroring

• Prepare the Servers

• Configure Mirroring

• Verify Principal and Mirror Access

• Execute the Mirror Failover

• Verify Mirror and Witness Status

• Terminating the Mirror Session

Replicating Information

• Replication Enhancements

• Stand-alone Replication Monitor

• Support for Oracle Publishing

• HTTP Synchronisation for Merge Replication

• Subscriber Initialisation

• Replicating DDL Statements

• Modified User Interface

• Replication Management Objects

• To Configure Publishing and Distribution

• Configuring Peer-to-Peer Replication

• Create a Publication

• Create a Subscription with RMO

SQL Server 2012 /2014 Integration Services

• Creating the Database Objects

• Creating a Package Using Business Intelligence Studio

• Integration Services Project Template

• OLE DB Command object

• Column Mappings

• Adding a Data Viewer

Monitoring and Performance

• SQL Server Profiler

• Optimal Data Storage

• Diagnostic Tools

• Query Analyzer

• Database Engine Tuning Advisor

• Index Optimisation

• Federated Database Servers

• Partitioned Indexes

• Lock optimsation

• Best Practices for Native XML Web Services

Who Should Attend

IT professionals who need the fundamental skills to install, maintain, administer and troubleshoot Microsoft SQL Server 2012.