Administering Microsoft SQL Server Databases

Introduction to SQL Server Database Administration

This module introduces SQL Server in general, and the main tools administrators will use, such as Management Studio, SQLCMD and the SQL Server configuration manager.

• Database Administration Overview
• Introduction to the SQL Server Platform
• Database Management Tools and Techniques
• LAB: Introduction to SQL Server Database Administration

Installing and Configuring SQL Server

Lots of decisions need to be made when installing SQL Server: Which components to install, the collation to use, ... . This module walks you through the different options and explains the consequences of the choices made.

• Planning SQL Server Installation
• Installing SQL Server
• SQL Server Installation Options and Settings
• Post-Installation Configuration
• LAB: Installing and Configuring SQL Server

Working with Databases and Storage

A SQL Server database consists of data files, a log file and optional filestream folders. In this module you will learn how SQL Server stores its data in these files, and how to manage and optimize the storage.

• Introduction to Data Storage with SQL Server
• Managing Storage for System Databases
• Managing Storage for User Databases
• Moving Database Files
• Working with Filegroups
• Configuring the Buffer Pool Extension
• LAB: Working with Databases and Storage

Planning and Implementing a Backup Strategy

Disaster recovery is an essential part of a database administrator's job. In this module the backups are discussed. SQL Server supports many different types of backups, each having its advantages and disadvantages.

• Understanding SQL Server Recovery Models
• Planning a Backup Strategy
• Backing up Databases and Transaction Logs
• Using Backup Options
• Ensuring Backup Reliability
• LAB: Planning and Implementing a Backup Strategy

Restoring SQL Server Databases

From a single page over a file till the complete database: SQL Server offers different types of restore.

• Understanding the Restore Process
• Restoring Databases
• Advanced Restore Scenarios
• Working with Point-in-Time Recovery
• LAB: Restoring SQL Server Databases

Importing and Exporting Data

If you need to load large amounts of data from other systems, this module can help you identify the best options for doing so. Not only does it discuss different techniques (SSIS, BCP, ...) but it also provides tricks to improve the performance of these data loads.

• Introduction to Transferring Data
• Importing and Exporting Table Data
• Copying or Moving a Database
• LAB: Importing and Exporting Data

Monitoring SQL Server

SQL Server is not a black box, there are many ways to figure out what is happening inside your server. Monitoring your SQL Server becomes an essential skill if you want to keep your SQL Server in good shape.

• Introduction to Monitoring SQL Server
• Dynamic Management Views and Functions
• Performance Monitor
• LAB: Monitoring SQL Server

Tracing SQL Server Activity

The SQL Server Profiler is a tool that is frequently used to monitor which actions are executed on a SQL Server. From finding resource-heavy queries till troubleshooting locking issues: the Profiler can be used for all these things.

• Tracing SQL Server Workload Activity
• Using Traces
• Working with Extented Events
• LAB: Tracing SQL Server Activity

Managing SQL Server Security

SQL Server security is rather advanced. Not only does it cover data access, but also permissions to create databases, backups etc.

• Introduction to SQL Server Security
• Managing Server-Level Security
• Managing Database-Level Principals
• Managing Database Permissions
• Working with Contained Databases
• LAB: Managing SQL Server Security

Auditing Data Access and Encrypting Data

Who will watch the watchmen? Database administrators have permissions to access any part of a SQL Server database. How can a company then guarantee that DBAs didn't misuse their permissions? This module shows two possible solutions to this dilemma: Auditing and Encryption.

• Auditing Data Access in SQL Server
• Implementing SQL Server Audit
• Encrypting Databases
• LAB: Auditing Data Access and Encrypting Data

Performing Ongoing Database Maintenance

Lots of things need to be done to keep a SQL Server in good shape. This module discusses the most important ones and shows how to automate these maintenance tasks.

• Ensuring Database Integrity
• Maintaining Indexes
• Automating Routine Database Maintenance
• LAB: Performing Ongoing Database Maintenance

Automating SQL Server Management

Automation can go beyond the maintenance plans. The last two module explain all the options of the SQL Server Agent, from scheduled jobs over database mail till responding to specific events.

• Automating SQL Server Management
• Implementing SQL Server Agent Jobs
• Managing SQL Server Agent Jobs
• Managing Job Step Security Contexts
• LAB: Automating SQL Server Management

Monitoring SQL Server by Using Alerts and Notifications

• Monitoring SQL Server Errors
• Configuring Database Mail
• Configuring Operators, Alerts, and Notifications
• LAB: Monitoring SQL Server by Using Alerts and Notifications