70-462: Administering Microsoft SQL Server 2012

The 70-462: Administering Microsoft SQL Server 2012 online training course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2012 database. The course focuses on teaching individuals how to use SQL Server 2012 product features and tools related to maintaining a database.
Read More

Also this online training course helps you prepare for the Exam 70-462 and helps toward the requirements for the MCSA: SQL Server certification

Exam Number: 70-462

Microsoft Course Demo

Skills Learned

After completing this course, the student should be able to:

  • Plan, install, and troubleshoot SQL Server 2012 and SQL Server databases
  • Working knowledge of SQL Databases, the concept of the transaction log, SQL Server 2012 database recovery, backup and restoration
  • SQL Server 2012 security models, logins and users
  • Fixed server roles, user-defined server roles, fixed database roles and permissions and the assignment of permissions, user-defined database roles
  • Partial database containment option provided by SQL Server 2012
  • SQL Profiler
  • Virtualization of SQL Server 2012 and Data-Tier Applications, Central Management Servers and Multi-Server queries
  • Auditing SQL Server 2012 environments
  • Monitoring and maintaining SQL Server 2012 databases
  • Managing and working with multiple servers including virtualizing SQL Server 2012and deploying and upgrading data-tier applications

Who Should Attend This Course

This online training course for is intended for IT Professionals who administer and maintain SQL Server 2012 databases. Individuals performing database administration and maintenance as their primary area of responsibility, or who work in environments where databases play a key role in their primary job should also attend.

Additionally, those developing applications that deliver content from SQL Server 2012 databases will find this course beneficial. This course is also intended for IT Professional seeking to prepare for the 462 exam and work towards attaining their MCSA: SQL Server certification.

Prerequisites

In addition to their professional experience, students who attend this course should already have the following technical knowledge:

  • 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 Outline

Module 1: Introduction to SQL Server 2012 and Its Toolset

  • Introduction to the SQL Server Platform
  • Working with SQL Server Tools
  • Configuring SQL Server Services

Module 2: Preparing Systems for SQL Server 2012

  • Overview of SQL Server Architecture
  • Planning Server Resource Requirements
  • Pre-installation Testing for SQL Server

Module 3: Installing and Configuring SQL Server 2012

  • Preparing to Install SQL Server
  • Installing SQL Server
  • Upgrading and Automating Installation

Module 4: Working with Databases

  • Overview of SQL Server Databases
  • Working with Files and File groups
  • Moving Database Files

Module 5: Understanding SQL Server 2012 Recovery Models

  • Backup Strategies
  • Understanding SQL Server Transaction Logging
  • Planning a SQL Server Backup Strategy

Module 6: Backup of SQL Server 2012 Databases

  • Backing up Databases and Transaction Logs
  • Managing Database Backups
  • Working with Backup Options

Module 7: Restoring SQL Server 2012 Databases

  • Understanding the Restore Process
  • Restoring Databases
  • Working with Point-in-time Recovery
  • Restoring System Databases and Individual Files

Module 8: Importing and Exporting Data

  • Transferring Data To/From SQL Server
  • Importing & Exporting Table Data
  • Inserting Data in Bulk

Module 9: Authenticating and Authorizing Users

  • Authenticating Connections to SQL Server
  • Authorizing Logins to Access Databases
  • Authorization Across Servers

Module 10: Assigning Server and Database Roles

  • Working with Server Roles
  • Working with Fixed Database Roles
  • Creating User-defined 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

Module 12: Auditing SQL Server 2012 Environments

  • Options for Auditing Data Access in SQL Server
  • Implementing SQL Server Audit
  • Managing SQL Server Audit

Module 13: Automating SQL Server 2012 Management

  • Options for Auditing Data Access in SQL Server
  • Implementing SQL Server Audit
  • Managing SQL Server Audit

Module 14: Configuring Security for SQL Server Agent

  • Understanding SQL Server Agent Security
  • Configuring Credentials
  • Configuring Proxy Accounts

Module 15: Monitoring SQL Server 2012 with Alerts and Notifications

  • Overview of Database Mail
  • Database Mail Profiles
  • Database Mail Security
  • Database Mail Logs and Retention

Module 16: Performing Ongoing Database Maintenance

  • Ensuring Database Integrity
  • Maintaining Indexes
  • Automating Routine Database Maintenance

Module 17: Tracing Access to SQL Server 2012

  • Capturing Activity Using SQL Server Profiler and Extended Events Profiler
  • Improving Performance with the Database Engine Tuning Advisor
  • Working with Tracing Options

Module 18: Monitoring SQL Server 2012

  • Monitoring Activity
  • Capturing and Managing Performance Data
  • Analyzing Collected Performance Data

Module 19: Managing Multiple Servers

  • Working with Multiple Servers
  • Virtualizing SQL Server
  • Deploying and Upgrading Data-tier Applications

Module 20: Troubleshooting Common SQL Server 2012 Administrative Issues

  • SQL Server Troubleshooting Methodology
  • Resolving Service-related Issues
  • Resolving Login and Connectivity Issues
  • Resolving Concurrency Issues

Hands-On Labs

Install SQL Server and Related Services

  • Planning SQL Server 2012 Installation
  • Install SQL Server and Related Services
  • Plan for capacity
  • Core mode installation

Implement a Migration Strategy

  • Restore vs. Detach / Attach

Configure Additional SQL Server Components

  • Configure Full-Text Indexing
  • Configure SSIS Security
  • Configure FILESTREAM

Manage SQL Server Agent

  • Create, maintain, and monitor Jobs
  • Automate (setup, maintenance, monitoring) across multiple databases and multiple instances

Manage and Configure Databases

  • Designing multiple filegroups
  • Configuring and standardizing databases
  • Implementing and configuring contained databases

Configure SQL Server Instances

  • Installing default and named instances
  • Configuring server level settings
  • Configuring database mail
  • Configuring SQL Server Engine

Manage SQL Server Instances

  • Installing an instance
  • Managing resource utilization by using Resource Governor

Identify and Resolve Concurrency Problems

  • Examining deadlock issues
  • Creating Alerts on Critical Server Condition
  • Monitoring data and server access

Audit SQL Server Instances

  • Configuring server audits
  • Configuring policy-based management

Manage Logins and Server Roles

  • Configuring server security
  • Managing access to the server, SQL Server instance, and databases

Manage Database Permissions

  • Configuring database security

Troubleshoot Security

  • Managing certificates and keys

Manage Users and Database Roles

  • Managing security roles
  • Creating database user accounts
  • Creating contained logins

We also include these bonus Hands-on labs:

  • Creating Excel 2010 Reports based on a SQL Server 2012 Tabular BI Semantic Model
    • Work with PivotTables
    • Work with the Excel CUBE functions
    • Work with slicers
    • Sparklines and charts
    • Publish to SharePoint Server
    • Interact with the published workbook
  • AlwaysOn Availability Groups
    • Using the Availability Group Wizard
    • Enable a secondary replica in a Availability Group
    • Connect to the primary replica using the Availability Group
    • Perform backups of databases in an Availability Group
    • Monitor an Availability Group using the Dashboard and DMVs
    • Perform a failover of an Availability Group
  • Creating SQL Server 2012 Tabular BI Semantic Model with PowerPivot
    • Import data into PowerPivot
    • Create table relationships
    • Create calculated columns
    • Create hierarchies
    • Hide columns
    • Create measures
    • Create a KPI
    • Create a perspective
    • Create a PivotTable report
  • Exploring SQL Server 2012 Reporting Services PowerView
    • Use the PowerPivot Gallery to create a Power View reportBecome familiar with the Power View designer
    • Create a visually compelling Power View report based on various visualization types
    • Save the Power View report to a document library, export the Power View report to
  • Developing a SQL Server 2012 Tabular BI Semantic Model with Visual Studio 2010
    • Create a Tabular project
    • Load data in the model designer
    • Enhance the model design with calculated columns and hierarchies
    • Define measures to aggregate and transform the tabular data
    • Explore and test the model using Excel
    • Define perspectives, define and refresh table partitions
    • Define a security role, and deploy the project to a Tabular instance of Analysis Services
  • Exploring SQL Server 2012 Integration Services
    • Introduce project connection managers
    • Introduce parameters, create an SSIS Catalog
    • Deploy an Integration Services project to the SSIS Catalog
    • Execute packages from the SSIS Catalog
    • Script package execution commands
    • Monitor the Integration Services environment and package execution
  • Exploring Transact-SQL Improvements in SQL Server 2012
    • Create and use sequence objects
    • Query rows through paged results
    • Raise and catch exceptions using Throw/Catch
  • Managing Rich Unstructured Data in SQL Server 2012
    • Create FileTables
    • Create Fulltext Indexes with semantic extraction capabilities
    • Retrieve information about document properties
    • Retrieve semantic information