Administering Microsoft SQL Server

Administering Microsoft SQL Server

Course overview

The course provides you with knowledge and skills to administer and maintain a SQL Server database infrastructure.

The course lasts five days and is instructor-led. It is also useful for those developing applications that retrieve content from SQL Server databases.

Also check out our courses in data, cloud and AI to develop your knowledge in data analytics, cloud computing and artificial intelligence.

Course Objective

After the course, you can:

  • Design and implement SQL Server instances
  • Authenticate and authorize users
  • Assign server and database roles
  • Grant users permission to resources
  • Protect data with encryption and auditing
  • Describe recovery models and backup strategies
  • Back up and restore SQL Server databases
  • Automate database management

Target Group

The course is aimed at those who:

  • Will administer and maintain SQL Server databases
  • Works in an environment where databases play a key role
  • Develops applications that retrieve content from SQL Server databases

Prerequisites

In addition to professional experience, you need to have:

  • Basic knowledge of Windows and its core features
  • Work experience with Transact-SQL

It is an advantage if you have:

  • Work experience with relational databases
  • Some experience in database design

Certification

This course does not have any associated certification. Each participant who completes the course receives a course certificate.

Trainer

The course is conducted in collaboration with one of Informator’s partners.

More about the course

Course content

Module 1: SQL overview

This module introduces the main terms used in SQL Server instance administration and discusses the requirements of the SQL architecture, such as what services are available and what security options are available.

Lessons learned:

  • Introduction to the SQL Server platform
  • Overview of the SQL Server architecture
  • Lab A: Review and record the SQL Server configuration
  • Lab B: Designing requirements for instances

Module 2: Installation of SQL Server

This module aims to install a SQL instance (standard or named), select the required services and review the installation options. Lessons covered include installation considerations, configuring tempdb, defaults for database file location and other SQL instance options, unattended installations using the GUI or command line, and upgrading SQL Server instances.

Lessons learned:

  • Considerations for installing SQL Server
  • tempdb Files
  • Installing SQL Server
  • Automate the installation
  • Upgrading SQL Server
  • Lab A: Installing SQL Server
  • Lab B: Unattended installation

Module 3: Working with SQL databases

This module covers the tasks that can be performed by administrators working with databases, both in terms of installation and maintenance.

Lessons learned:

  • Introduction to data warehousing with SQL Server
  • Managing storage for system databases
  • Managing storage for user databases
  • Moving and copying database files
  • SQL Server transaction logs
  • Ensuring database integrity
  • Maintenance of indices
  • Lab A: Create and modify a database
  • Lab B: Reconfigure storage for a database
  • Lab C: Transaction log management
  • Lab D: Review index fragmentation

Module 4: Backing up and restoring SQL databases

This module covers all aspects of backup and recovery with SQL Server.

Lessons learned:

  • Types of backup
  • Managing database backups
  • Advanced database options
  • Understanding the recovery process
  • Recovery of databases
  • Advanced recovery scenarios
  • Recovery at a specific point in time
  • Lab A: Back up a database and a log
  • Lab B: Review backups made
  • Lab C: Restore a corrupt database
  • Lab D: Designing a backup strategy

Module 5: Authentication of SQL Server

This module covers the setup and maintenance of logins, users and connections at both instance and database level. Often SQL queries will involve multiple instances and databases, linked servers are covered.

Lessons learned:

  • Authentication of connections to SQL Server
  • Authorizing logins for connection to databases
  • Authorization over servers
  • Partially contained databases
  • Lab A: Create logins for an SQL instance
  • Lab B: Create users
  • Lab C: Add a linked server
  • Lab D: Add Enclosed Users

Module 6: Server and database roles

This module is about how to create and maintain roles at server and database level.

Lessons learned:

  • Working with server roles
  • Working with fixed database roles
  • User-defined database roles
  • Lab A: Maintaining server roles
  • Lab B: Assignment of database roles
  • Lab C: Use an application role

Module 7: Security of database objects

This module deals with the security of database-level objects such as tables, views, stored procedures and functions. This topic is usually a collaboration between administrators and developers.

Lessons learned:

  • Authorizing user access to objects
  • Authorize users to execute code
  • Configure permissions at schema level
  • Lab A: Secure database objects
  • Lab B: Using schedules

Module 8: Data security

This module builds on the object-level security of the previous module to protect data security from the protection of data within the objects.

Lessons learned:

  • Protection at row and column level
  • Authorization at column level
  • Security at row level
  • Dynamic data masking
  • Review of data changes
  • Audit with triggers
  • Audit with temporal tables
  • Management of SQL Server Audit
  • Introduction to SQL Server Audit
  • Defining a server audit
  • Create audit specifications for server and database
  • Audit-related dynamic management views and system views
  • Customized audit events
  • Protecting data with encryption
  • Transparent data encryption
  • Moving encrypted databases
  • Introduction to Always Encrypted
  • Adding encryption keys
  • Implementation of always encrypted
  • Lab A: Row and column security
  • Lab B: Implementing a temporal table
  • Lab C: Review of table operations
  • Lab D: Implement transparent database encryption
  • Lab E: Always use encrypted

Module 9: Automation of SQL Server management

This module covers the automation of the administration with the SQL Server instance using database records, jobs, alerts and operators.

Lessons learned:

  • Automation of SQL Server management
  • Understanding of SQL Server Agent Security
  • Overview of SQL Server Agent security
  • Roles for SQL Server agents
  • Assign security contexts to SQL Server Agent job steps
  • Overview of database records
  • Managing credentials
  • Managing proxy accounts
  • Configuring jobs
  • Configuring operators
  • Configuring alerts
  • Configuring jobs for multiple servers
  • Lab A: Installation of SQL Agent and Database Mail
  • Lab B: Licensing and Powers of Attorney
  • Lab C: Implementing Jobs
  • Lab D: Implementation of alerts

Good to know

If you participate remotely in a virtual classroom, you need an extra screen to work with the labs via remote desktop (GoToMyPC or LearnOnDemand). The screen should be at least 19 inches with a resolution of at least 1280×1024, with vertical resolution being the most important.

Course overview

41900 kr

5 days

Advanced

Can’t find a (suitable) date, but are interested in the course? Send in an expression of interest and we will do what we can to get an opportunity that suits.

För samtliga utbildningar gäller våra
Allmänna Villkor.

Company-adapted course

The course can be adapted from several perspectives:
  • Content and focus area
  • Scope of application
  • Structure

In collaboration with the course leader, we make sure that the course meets your wishes

Expression of interest