Microsoft SQL Server Administration

Microsoft SQL Server Administration

Audience

This course is intended for students who need to learn the skills necessary to maintain a Microsoft SQL Server database infrastructure. Being comprehensive and hands on, it is aimed at quickly getting students familiar with the Microsoft SQL Server administration concepts, tools and utilities.

Prerequisites

Before attending this course, students should have:

  • An understanding of relational database concepts
  • Some experience of querying database tables

Some basic experience using SQL Server Management Studio is useful but not essential.

Duration

4 days. Hands on.

Course Objectives

This intensive course is aimed at getting students familiar with SQL Server administration skills and tools. Improving database and server performance will be covered. This will include implementing a database security model. The course is applicable to all recent releases of SQL Server, but will also contain the new features found in SQL Server 2016.

On completion, delegates will be able to:

  • Improve database design
  • Improve query performance
  • Improve database resilience with a backup policy
  • Secure data stores by restricting user access
  • Access remote data stores
  • Integrate diverse data stores to a single repository

Course Content

Module 1: SQL Server Overview
The Role of Database Providers
Client Side and Server Side Components
SQL Server Architecture
SQL Server Developer Tools
SQL Server Sample Databases

Module 2: Installing SQL Server
Preparing for Installation
Upgrading an Earlier Version
Installing SQL Server
Configuring the Server

Module 3: Working with SQL Server Management Studio
Getting Started with SSMS
Exploring the Object Explorer
Working with the Query Editor
Using SQL Server Books Online

Module 4: Designing a Database
Defining Entities
Applying Normalisation
Database Objects
System Tables
Defining Databases
Setting Database Options
Managing Data and Log File Growth

Module 5: Implementing Tables
Creating Tables
Setting Column Data Types
Adding and Dropping a Column
Generating Column Values
Adding Constraints
Using the Identity Property

Module 6: Backup and Recover a Database
Database Recovery Models
Backing Up Transactions Logs and Databases
Backing Up in Management Studio
Restoring in Management Studio

Module 7: Design and Administer Security Levels
Designing security plan
Administering server and database authentication
Administering database authorization
Administering database permissions
Administering users, groups and roles

Module 8: Indexing Tables
Introduction to Indexes
Index Architecture
How SQL Server Retrieves Stored Data
How SQL Server Maintains Index and Heap Structures
Deciding Which Columns to Index

Module 9: Managing Transactions and Locks
Introduction to Transactions and Locks
Managing Transactions
SQL Server Locking
Managing Locks

Module 10: Accessing Linked Servers
Introduction to Distributed Queries
Executing an Ad Hoc Query on a Remote Data Source
Setting up a Linked Server Environment
Executing a Query on a Linked Server
Executing a Stored Procedure on a Linked Server
Managing Distributed Transactions
Modifying Data on a Linked Server
Using Partitioned Views

Module 11: Monitoring and Tuning
SQL Servers inbuilt monitoring tools
Use SQL Profiler to monitor a database
Describe how the Index Tuning Wizard works
Define database partitioning

Module 12: Automating Administrative Tasks
Using SQL Server Agent
Creating and scheduling job
Maintenance Plans
SQL Management Objects (SMO)

Module 13: Programming Replication
Overview of SQL Server Replication
Replication Programming Interfaces
Configuring Replication
Synchronizing Data

Module 14: Using Integration Services
Importing and Exporting Data
Integration Services Tools
Building a Package
Troubleshooting a Package

Module 15: New Features in SQL Server 2016
Always Encrypted
Stretch Database
Real-time Operational Analytics
PolyBase into SQL Server
Native JSON Support
Enhancements to AlwaysOn
Enhanced In-Memory OLTP

Public Courses

On-Site Courses

Can't attend one of our public classes? Booking for multiple people?

All our courses are available on your site! Delivered for your staff, at your premises.

Contact us to find out more...