CA11 - Microsoft SQL Server Administration - 5 Days

Course Description

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

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 and when to use it
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
Always Encrypted
Stretch Database
Real-time Operational Analytics
PolyBase into SQL Server
Native JSON Support
Enhancements to AlwaysOn
Enhanced In-Memory OLTP

Module 16: Working with Azure SQL Databases
Creating an Azure SQL Database
Connecting to an Azure SQL Server
Exporting data from an on-premises database
Querying an Azure SQL Database

Module 17: Azure SQL Database Admin Features
Security
Advanced Threat Protection
Auditing
Dynamic Data Masking
Elastic Pools
Monitoring
Tuning

Course Enquiry