Microsoft SQL Server 2012: Administration Skills Upgrade

Course Overview

SQL Server 2012 includes an extensive array of abilities for SQL Server administrators. Those migrating from SQL Server 2008 or SQL Server 2005 must upgrade their skills in order to leverage the enhanced feature set. This skills upgrade course provides the knowledge required to effectively take advantage of the new features in SQL Server 2012. You learn to build database availability solutions and exploit the wide range of administrator tools to gain maximum benefit for your organisation.

CourseMonster specialises in offering Microsoft training to corporations and individuals alike, we are happy to help with any of your Microsoft training requirements.

Related Courses

• Microsoft SQL Server 2012: Business Intelligence Skills Upgrade
• Microsoft SQL Server 2012: Querying Microsoft SQL Server 2012
• Microsoft SQL Server 2012: Administering Microsoft SQL Server 2012 Databases
• Microsoft SQL Server 2012: Developing Microsoft SQL Server 2012 Databases
• Microsoft SQL Server 2012: Implementing a Data Warehouse with Microsoft SQL Server 2012
• Microsoft SQL Server 2012: A Comprehensive Hands-On Introduction
• Microsoft SQL Server 2012: Database Administration
• Microsoft Business Intelligence with SQL Server 2012 Training Course
• Microsoft SQL Server 2012: Implementing Data Models and Reports with Microsoft SQL Server 2012
• MCTS: Microsoft SQL Server 2012, Implementation and Maintenance Power Camp

Audience

Experienced SQL Server administrators considering or currently migrating to SQL Server 2012. Practical DBA experience with prior versions of SQL Server databases is assumed.

Skills Gained

Learn How To

• Leverage the new SQL Server 2012 administrative features
• Build database high availability solutions with AlwaysOn groups
• Implement user-defined server roles to delegate responsibilities
• Take advantage of the FileTable capabilities for storing large object data
• Configure a contained database to achieve deployment flexibility
• Create a columnstore index to improve decision support query performance

Course Outline

Throughout this course, a series of extensive hands-on exercises provides you with practical experience administering SQL Server 2012. Exercises include:

• Installing and migrating databases to SQL Server 2012
• Leveraging the new features in Management Studio
• Configuring AlwaysOn availability groups
• Building, populating and querying a FileTable
• Creating a contained database to avoid orphaned users
• Troubleshooting waits and queues
• Constructing a columnstore index to improve performance

Introducing the New Database Engine Features

Preparing for installation

• Selecting the appropriate edition
• Defining a Managed Service account in Windows Server 2008 R2
• Running SQL Server with a Managed Service account
• Downloading and implementing the .NET framework

Installing SQL Server

• Choosing installation options
• Acquiring the AdventureWorks sample database

Upgrading to SQL Server 2012

Planning an upgrade

• Installing the Upgrade Advisor
• Analysing pre-2012 databases

Migrating a legacy SQL Server database

• Comparing an in-place upgrade with migration
• Transferring a database to 2012

Implementing AlwaysOn Availability

Demystifying AlwaysOn availability groups

• Combining clustering, mirroring and replication
• Choosing an architecture

Configuring an availability group

• Creating the Windows cluster
• Setting up the SQL Server group for high availability and disaster recovery
• Monitoring with the Availability Group Dashboard

Offloading resource-intensive tasks to secondary servers

• Taking full and transaction log backups
• Checking database consistency
• Running report queries

Securing SQL Server 2012

Taking advantage of user-defined server roles

• Segregating roles and responsibilities
• Constructing a user-defined server role

Applying new audit capabilities

• Server vs. database audits
• Filtering audit events

Developing FileTable Solutions

Contrasting file system data stores

• Transactional vs. nontransactional processing
• Integrating FileTable and FILESTREAM data stores

Establishing the prerequisites for FileTables

• Enabling FILESTREAM at the instance level
• Permitting nontransactional access at the database level
• Specifying the directory for FileTables

Building and querying a FileTable

• Creating, modifying and dropping
• Populating
• Querying

Managing Contained Databases

Comparing contained and noncontained databases

• Considering the advantages of contained databases
• Exploring the new security model
• Handling temporary tables with a contained database

Developing and creating a contained database

• Granting user access
• Connecting to the database
• Converting an old database to a contained database

Breaking Performance Bottlenecks by Monitoring Waits and Queues

Obtaining wait and queue information from dynamic management objects

• Forming the query
• sys.dm_os_waiting_tasks
• sys.dm_os_wait_stats

Troubleshooting performance issues

• Querying sys.syscacheobjects
• Specifying the plan cache as the Database Engine Tuning Advisor (DTA) workload

Applying Columnstore Indexes to Decision Support Queries

Dissecting columnstore indexes

• Comparing traditional and columnstore indexes
• Determining when to apply a columnstore index

Improving query performance with columnstore indexes

• Designing the index
• Measuring the impact of the index