Realdolmen Education

Details

Day-to-day database management in Microsoft SQL Server 2012

This course is currently not scheduled on the open calendar, but can be organized on request.

Request Course

Duration

5 day(s)

Audience

This course is intended for Database Administrators of medium sized systems and Database Developers who have to maintain and administer their own system. The course does not intend to prepare people who plan on taking an exam but rather those who have to do the practical day to day management in an environment where the line between developer and administrator is very faint.

Prerequisites

Before attending this course, students must have a working knowledge of relational databases and a basic knowledge of the Microsoft Windows operating system and its core functionality.

Objectives

The course focuses on teaching individuals how to use SQL Server 2012 product features and tools related to maintaining and programming a database.

Methods

Instructor led training alternated with demo's and exercises. Each student disposes of a workstation. All the labs for this course can be performed using the provided virtual machines.

Description

After completing this course, students will be able to: Create databases, Create users and grant them permissions, Backup and restore databases, Import and export data, Monitor the system. On the other hand, they will also be able to : Create tables and set constraints, Create indexes and analyse performance, Create views and Stored Procedures, Create highly concurrent applications.

Contents

  • Module 1: Installing and configuring SQL Server 2012
    • Preparing to install SQL Server 2012
    • GInstalling SQL Server
    • Upgrading and automating installation
  • Module 2: Working with databases
    • Overview of SQL Server databases
    • Working with files and filegroups
    • Moving database files
  • Module 3: Designing and implementing tables
    • Designing tables
    • Creating a schema
    • Creating the tables
  • Module 4: Ensuring data integrity through constraints
    • Enforcing data integrity
    • Implementing domain integrity
    • Implementing entity and referential integrity
  • Module 5: Planning for SQL Sever 2012 indexing
    • Core indexing concepts
    • Data types and indexes
    • Single columns and composite indexes
  • Module 6: Implementing table structures in SQL Server 2012
    • SQL Server table structures
    • Working with clustered indexes
    • Designing effective clustered indexes
  • Module 7: Reading SQL Server 2012 execution plans
    • Execution plan core concepts
    • Common execution plan elements
    • Working with execution plans
  • Module 8: Improving performance through nonclustered indexes
    • Designing effective nonclustered indexes
    • Implementing nonclustered indexes
    • Using the Database Engine Tuning Advisor
  • Module 9: Designing and implementing views
    • Introduction to views
    • Creating and managing views
    • Performance considerations for views
  • Module 10: Designing and implementing stored procedures
    • Introduction to Stored Procedures
    • Working With Stored Procedures
    • Implementing Parameterized Stored Procedures
    • Controlling Execution Context
  • Module 11: Backup of SQL Server 2012 Databases
    • Backing up Databases and Transaction Logs
    • Managing Database Backups
    • Working with Backup Options
  • Module 12: Restoring SQL Server 2012 Databases
    • Understanding the Restore Process
    • Restoring Databases
    • Working with Point-in-time recovery
    • Restoring System Databases and Individual Files
  • Module 13: Importing and Exporting Data
    • Transferring Data To/From SQL Server
    • Importing and Exporting Table Data
    • Inserting Data in Bulk
  • Module 14: Authenticating and Authorizing Users
    • Authenticating Connections to SQL Server
    • Authorizing Logins to Access Databases
    • Authorization Across Servers
  • Module 15: Assigning Server and Database Roles
    • Working with Server Roles
    • Working with Fixed Database Roles
    • Creating User-defined Database Roles
  • Module 16: Authorizing Users to Access Resources
    • Authorizing User Access to Objects
    • Authorizing Users to Execute Code
    • Configuring Permissions at the Schema Level
  • Module 17: Automating SQL Server 2012 Management
    • Automating SQL Server Management
    • Working with SQL Server Agent
    • Managing SQL Server Agent Jobs
  • Module 18: Performing Ongoing Database Maintenance
    • Ensuring Database Integrity
    • Maintaining Indexes
    • Automating Routine Database Maintenance
  • Module 19: Monitoring SQL Server 2012
    • Monitoring Activity
    • Capturing and Managing Performance Data
    • Analyzing Collected Performance Data
  • Module 20: Creating Highly Concurrent SQL Server 2012 Applications
    • Introduction to Transactions
    • Introduction to Locks
    • Management of Locking
    • Transaction Isolation Levels