iTrain Scotland IT Training Edinburgh image

Microsoft SQL Server 2014 System Administration

iTrain Scotland IT Training Edinburgh image

Home > Courses > SQL courses > Microsoft SQL Server 2014 System Administration

Microsoft SQL Server 2014 System Administration Fundamentals

This 5 day course is intended for students who need to learn the skills necessary to maintain a Microsoft SQL Server 2014 system infrastructure. Being comprehensive and hands-on, it is aimed at quickly getting students familiar with the Microsoft SQL Server 2014 administration concepts, tools and utilities. Attendees will gain insight into the infrastructure and architecture of SQL Server, enabling individuals to fully understand the product with which they are dealing. Configuration of server instances and databases is given extensive treatment, as is the pivotal role of database transaction logs. Index creation and maintenance is also given in-depth analysis to facilitate better understanding of related performance issues arising from their use, and how to rectify problems that can often occur. Thorough coverage is given to managing SQL Server security, including both at the server and database level. The focus then shifts to examining how best to move and protect data, such as when importing or exporting it between heterogeneous data sources, backing it up to external media, and restoring data from a backup. Many tasks involved in the day to day administration of a SQL Server instance can be performed automatically, making an administrators life potentially simpler, and such is the subject matter in the module dealing with SQL Server Automation. Coverage of SQL Server high availability options is also provided, enabling students to better identify what choices are available and best suited to given scenarios. Finally, comprehensive treatment is given to the monitoring and troubleshooting of SQL Server performance and related issues.


This course is fully customisable and can be tailored to include only the material required.

Students should be familiar with working in a Microsoft Windows Server operating system environment. Delegates should also have an understanding of the fundamental design of relational databases including, but not restricted to, the purpose of primary and foreign keys, entity relationships, and data normalization. A working knowledge of the SQL language (such as might be gained from attending the 2 day “SQL Server 2014 Introduction to Querying” course) will also be assumed.

Upon successful completion of the full course, students will be able to:


  • Install and Configure a SQL Server 2014 Instance
  • Create, Configure and Manage Databases
  • Implement and Manage Indexes
  • Create and Manage Table and Index Partitions
  • Understand and Implement SQL Server 2014 Security
  • Understand Data Transfer Utilities
  • Backup and Restore Databases
  • Implement Database Snapshots
  • Automate Tasks in SQL Server 2014
  • Understand the Concepts of Maintaining High Availability
  • Implement Replication
  • Monitor and Troubleshoot SQL Server 2014

1. Installing SQL Server 2014

Preparing for an Installation

Installing SQL Server 2014

Upgrading an Instance to SQL Server 2014


2. Configuring and Managing SQL Server Instances

Configuring Instance-Level Settings

Configuring Database Mail

Installing Additional SQL Server Instances

Managing Software Updates


3. Configuring and Managing Databases

Designing the Storage for New Databases

Managing Files and Filegroups

Configuring Databases

Shrinking and Growing Databases

Contained Databases

Data Compression

Encrypting Databases (TDE)

Managing Log Files

Using Database Console Commands


4. Creating and Managing Indexes

SQL Server Index Architecture

Designing Indexes for Efficient Data Retrieval

Index Creation Options

Creating and Modifying Indexes

Tracking Missing Indexes

Reviewing Unused Indexes

Managing Index Fragmentation

Columnstore Indexes

Understanding and Managing Statistics


5. Partitioning Indexes and Tables

Partition Functions

Partition Schemes

Partitioning Tables and Indexes

Query Data in Partitions

Managing Partitions


6. SQL Server 2014 Security

Managing Logins and Server Roles


Dedicated Administrator Connection (DAC)

Managing Users and Database Roles

Contained Users

Application Roles

Understanding Securables

Assigning and Managing Permissions

Using Schemas

Troubleshooting SQL Server Security

Using Security Catalog Views

Auditing SQL Server Instances

Policy-Based Management


7. Transferring Data

Overview of Copying and Exporting Data

Using BCP to Import and Export Data




Using the SQL Server Import and Export Wizard

Introducing SQL Server Integration Services (SSIS)

Copying Databases to Other Servers

Migrating SQL Logins


8. Backups and Recovery

Understanding Database Recovery Models

Understanding Backup Types

Backup Options

Backup to URL

Encrypted Backups

Performing Database and Log Backups

Backing Up System Databases

Viewing Backup History

Restoring Database and Log Backups

Performing File Restores

Performing Page Restores

Restoring System Databases


9. Database Snapshots

How Database Snapshots Work

Benefits of Database Snapshots

Implementing Database Snapshots

Revert a Database to a Database Snapshot


10. SQL Server Automation

Overview of SQL Server Automation

The SQL Server Agent Service

Configuring Credentials and Proxy Accounts

Implementing Jobs

Implementing Operators and Notifications

Implementing Alerts

Managing Multiple Servers

Maintenance Plans


11. SQL Server High Availability

Overview of SQL Server High Availability Options

Implementing Database Mirroring

Implementing Log Shipping

Introducing Clustering on SQL Server 2014

Introducing AlwaysOn Availability Groups


12. Introduction to Replication

Replication Architecture

Replication Types

Snapshot Replication

Transactional Replication

Peer-to-Peer Transactional Replication

Merge Replication

The Replication Monitor

Managing Replication


13. Monitoring and Troubleshooting SQL Server

Introduction to Monitoring SQL Server

Using DMVs and DMFs

Activity Monitor

Performance Monitor

SQL Server Profiler

Extended Events

DDL and Logon Triggers

Using the Database Engine Tuning Advisor

Using Resource Governor

The Data Collector and Management Data Warehouse

Monitoring Disk Usage

Monitoring Memory Usage

Monitoring CPU Usage

Understanding Transactions

Understanding SQL Server Lock Management

Monitoring Concurrency Issues

Detecting and Correcting Deadlocks

Diagnosing Bottlenecks

Prices below are for scheduled courses held in our Edinburgh Training Centre. Major discounts available for onsite or private courses: click General Enquiry button below to enquire about private courses.
Group discount
1 place £ 3750 n/a
2 places £ 4500 n/a
3 places £ 11250 £ 5250
4 places £ 15000 £ 6000
5 places £ 18750 £ 6750
6 places £ 22500 £ 7500
7 places £ 26250 £ 8250
8 places £ 30000 £ 9000