Microsoft SQL Server Administration

This intensive course is aimed at getting students familiar with SQL Server administration skills and tools. Improving database and server performance will be covered. Along with administering databases in the Azure cloud. This will include implementing a database security model. The course will contain the new features in the latest version of SQL Server. This course can be customised for other versions of SQL Server.

7 Top Takeaways from the Course

1. Improve database design
2. Improve query performance
3. Improve database resilience with a backup policy
4. Secure data stores by restricting user access
5. Access remote data stores
6. Integrate diverse data stores to a single repository
7. Reduce infrastructure costs by storing data in the cloud

The modules are as follows:

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

Virtual Live Classroom

• Enquire for dates

Who’s it for?

This course is designed for those who are responsible for administering SQL Server.

What are the Course Requirements?

Before attending this course, students must have:

• An understanding of relational database concepts
• Experience of querying database tables
• Experience using SQL Server Management Studio

Are any Financial Options Provided?

• 0% Interest Free payments available.

Reward

Informal assessment will take place through group discussion, class room activities, and questions and answers sessions as guided by your tutor. Course Completion Certificate will be awarded on successful completion of the course.

What is provided with the Course?

• Course Material
• Aftercare Course Support