COURSE-DB-10775A-DBADENALI

10775A: Administering Microsoft® SQL Server® 2012 Databases (formerly code named “Denali”)

5-Day (COURSE-DB-10775A_DB_2012)

Description

This five-day instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2008 database. The course focuses on teaching individuals how to use SQL Server 2012 product features and tools related to maintaining a database.

Written by SolidQ Australia, this course is targeted at practical skills and features a large number of hands-on labs.

Attendees will work through an extensive set of detailed and practical hands-on labs that reinforce the material presented.

For details on booking courses or seminars, please visit our booking page.

Audience

The presentations and labs have been designed specifically for IT Professionals who administer and maintain SQL Server databases.

This course is also relevant and useful for those interested to understand and evaluate Microsoft’s SQL Server offerings.

NOTE: This course is also very useful for those currently working with the SQL Server 2008 R2, 2008, 2005 series of technologies. Differences between the releases are identified.

Prerequisites

Attendees should have a basic understanding of SQL Server and relational database technology.

Basic knowledge of the Microsoft Windows operating system and its core functionality.
Working knowledge of Transact-SQL
Working knowledge of Relational databases.
Some experience with database design

Course Objectives

This five-day instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2008 R2 database. The course focuses on teaching individuals how to use SQL Server 2008 R2 product features and tools related to maintain a database.

Technologies

The workshop will empower developers to exploit the BI functionality in the following technologies:

SQL Server 2012 Database Services

While based on SQL Server 2008 R2 versions of SQL Server, this course is highly relevant for those working on SQL Server 2005 versions as well.

Course Outline

Day 1

Course Introduction

Module 1 Introduction to SQL Server and its Toolset

Introduction to the SQL Server Platform
Working with SQL Server Tools
Configuring SQL Server Services
LAB: Introduction to SQL Server and its Toolset

Module 2 – Preparing Systems for SQL Server

Overview of SQL Server Architecture
Planning Server Resource Requirements
Preinstallation Testing for SQL Server
LAB: Introduction to SQL Server and its Toolset

Module 3 – Installing and Configuring SQL Server

Preparing to Install SQL Server
Installing SQL Server
Upgrading and Automating Installation
LAB: Installing and Configuring SQL Server

Module 4 – Working with Databases

Overview of SQL Server Databases
Working with Files and Filegroups
Moving Database Files
LAB: Working with Databases

Day 2

Module 5 – Understanding SQL Server Recovery Models

Backup Strategies
Understanding SQL Server Transaction Logging
Planning a SQL Server Backup Strategy
LAB: Understanding SQL Server Recovery Models

Module 6 – Backup of SQL Server Databases

Backing up Databases and Transaction Logs
Managing Database Backups
Working with Backup Options
LAB:Backup of SQL Server Databases

Module 7 – Restoring SQL Server Databases

Understanding the Restore Process
Restoring Databases
Working with Point in Time Recovery
Restoring System Databases and Individual Files
LAB: Restoring SQL Server Databases

Module 8 – Importing and Exporting Data

Transferring Data To/From SQL Server
Importing & Exporting Table Data
Inserting Data in Bulk
LAB: Importing and Exporting Data

Day 3

Module 9 – Authenticating and Authorizing Users

Authenticating Connections to SQL Server
Authorizing Logins to Access Databases
Authorization Across Servers
LAB: Authenticating and Authorizing Users

Module 10 – Assigning Server and Database Roles

Working with Server Roles
Working with Fixed Database Roles
Creating User-defined Database Roles
LAB: Assigning Server and Database Roles

Module 11 – Authorizing Users to Access Resources

Authorizing User Access to Objects
Authorizing Users to Execute Code
Configuring Permissions at the Schema Level
LAB:Authorizing Users to Access Resources

Module 12 – Auditing SQL Server Environments

Options for Auditing Data Access in SQL
Implementing SQL Server Audit
Managing SQL Server Audit
LAB: Auditing SQL Server Environments

Day 4

Module 13 – Automating SQL Server Management

Automating SQL Server Management
Working with SQL Server Agent
Managing SQL Server Agent Jobs
LAB: Automating SQL Server Management

Module 14 – Configuring Security for SQL Server Agent

Understanding SQL Server Agent Security
Configuring Credentials
Configuring Proxy Accounts
LAB: Configuring Security for SQL Server Agent

Module 15 – Monitoring SQL Server with Alerts and Notifications

Configuration of Database Mail
Monitoring SQL Server Errors
Configuring Operators, Alerts and Notifications
LAB: Monitoring SQL Server with Alerts and Notifications

Module 16 –Performing Ongoing Database Maintenance

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

Day 5

Module 17 – Tracing Access to SQL Server

Capturing Activity using SQL Server Profiler
Improving Performance with the Database Engine Tuning Advisor
Working with Tracing Options
LAB: Tracing Access to SQL Server

Module 18 – Monitoring SQL Server

Monitoring Activity
Capturing and Managing Performance Data
Analyzing Collected Performance Data
LAB: Monitoring SQL Server with Dynamic Management Views and Data Collection

Module 19 – Managing Multiple Servers

Working with Multiple Servers
Virtualizing SQL Server
Deploying and Upgrading Data-Tier Applications
LAB: Managing Multiple Servers

Module 20 – Troubleshooting Common SQL Server Administrative Issues

SQL Server Troubleshooting Methodology
Resolving Service-related Issues
Resolving Concurrency Issues
Resolving Login and Connectivity Issues
LAB: Troubleshooting Common SQL Server Administrative Issues