IE0: Immersion Event for the Accidental/Junior DBA

Overview

The performance and reliability of line-of-business applications typically relies on a relational database management system (RDBMS) like SQL Server for data storage. This makes SQL Server one of the most important, but often most neglected parts of a business infrastructure. In this three-day course you will learn the basic information necessary to successfully install, configure, and administer SQL Server to ease manageability, maximize performance, and minimize the risk of data loss.

This course will help you learn how to keep the lights on so that SQL Server meets the business requirements for availability and performance, and is especially suited to:

  • A server administrator that has to maintain SQL Server as a part of the infrastructure
  • An administrator for a line-of-business application such as SharePoint, Microsoft CRM, Great Plains, Dynamics, or Biztalk
  • An administrator for one or many non-Microsoft systems that rely on SQL Server
  • A junior DBA just getting started with SQL Server

As an Accidental DBA, Involuntary DBA, or any other acronym that you want to apply to your current role that requires you to manage SQL Server, the responsibility for maintaining SQL Server properly is what the business expects from your current role. We’ll show you how to proactively manage SQL Server and prevent problems based on our own real-world client experiences diagnosing line of business applications that rely on SQL Server.

The course starts with the importance of proper hardware selection and configuration for SQL Server, and then demonstrates how to install and properly configure SQL Server. Planning for reliability and availability of SQL Server starts with developing a strategy around minimizing data loss risk to meet business Service Level Agreements. You’ll learn about SQL Server backups and recovery models, along with an introduction to high-availability features such as Database Mirroring, AlwaysOn Availability Groups, and Failover Clustering.

You’ll also get an understanding of the maintenance requirements needed to maintain SQL Server databases and provide the best performance for your solution. Last, but certainly not least, you’ll learn the most important metrics to monitor related to SQL Server performance so that you can identify potential problems before they significantly affect the end-user experience.

Instructors: Jonathan Kehayias, Erin Stellato

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.

Please see our Immersion Events Schedule for class dates and our comprehensive Immersion Events FAQ for class costs and other frequently asked questions.


Curriculum

Module 1: Hardware Considerations

The hardware selected to run SQL Server is one of the most important performance-affecting decisions you can make, and it’s critical to understand how SQL Server uses these resources.  This module covers how to determine the hardware requirements for SQL Server to obtain the best possible performance. Topics covered include:

  • SQL Server memory requirements and usage
  • Importance of the I/O subsystem
    • Planning requirements
    • Designing for redundancy and performance
    • Partition alignment
  • CPU selection 
  • Virtual machine considerations

Module 2: Installing and Configuring SQL Server

Although this may seem like a simple topic, there are many things to plan for prior to installing SQL Server, and there are a number of configuration changes to optimize SQL Server performance. Topics covered include:

  • SQL Server installation best practices
  • SQL Server configuration settings
  • Database configuration settings
  • Database file layouts
  • FileStream/Remote Blob Storage considerations

Module 3: Security

One of the primary jobs of a database administrator is to keep the data secure.  This extends beyond understanding who can log in and what privileges they have; protecting the data and tracking “who did what” must also be considered.  Topics covered include:

  • Authentication Modes
  • SQL Server logins, users and roles
  • SQL Server encryption
  • Certificates and auditing

Module 4: Backup and Restore

Database backups are essential for the prevention of data loss and the ability to recover from disasters.  Every SQL Server administrator must understand backup options, optimization techniques, and general best practices and we’ll make it simple to determine the right choice to fit your business requirements in this module. Topics covered include:

  • The anatomy of a data change in SQL Server
  • Understanding database recovery models
  • Backup and Restore fundamentals

Module 5: Disaster Recovery and High Availability

The reliability of your installation and the ability to recover from a disaster starts with properly planning a recovery strategy for SQL Server. This module will explain how to build a recovery strategy for SQL Server and how to plan for high availability and redundancy to meet your business Service Level Agreements. Topics covered include:

  • Understanding RPO and RTO
  • Planning a recovery strategy to meet RPO and RTO
  • Database Mirroring
  • Availability Groups
  • Failover Clustering
  • Log Shipping

Module 6: Database Maintenance

Properly maintaining your databases is paramount to maintaining the performance of the entire solution.  This module will explain the importance of consistency checking, index maintenance, and statistics management.  We’ll take a look at the built-in Database Maintenance Plan feature and free alternatives like Ola Hallengren’s Maintenance Solution to automate routine maintenance tasks in SQL Server.  Topics covered include:

  • Consistency checking
  • Index management
  • Statistics management
  • Understanding the impact of shrink
  • Planning for data purging tasks and ETL processes

Module 7: Monitoring

Monitoring SQL Server can help you identify problems as they occur, as well as help you plan hardware upgrades before performance degrades.  This module will explain the various methods of monitoring SQL Server performance and how to interpret the data you collect. Topics covered include:

  • Database mail/Operators
  • Configuring alerts for high severity problems
  • Creating a baseline
  • Benchmarking and testing changes
  • Tools for ongoing monitoring:
    • Performance Monitor and PAL tool
    • SQLTrace and Trace Analysis Tools
    • Essential DMVs for Monitoring
  • Wait stats 
  • Virtual file stats
  • Additional monitoring for VMs

Questions?

If you have any questions, please contact us.