Audience
This course is designed for those who are responsible for administering SQL Server.
Prerequisites
Before attending this course, students must have:
- An understanding of relational database concepts
- Experience of querying database tables
- Experience using SQL Server Management Studio
Duration
5 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. 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
- 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
- Reduce infrastructure costs by storing data in the cloud
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 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