SQL Server® 2012: Administration Skills Upgrade

Course 2105 | 3 Days

Attendee Rating

Evaluations in the Last 12 Months
  • 5 stars: 65%
    5 Star Ratings: 65%
  • 4 stars: 31%
    4 Star Ratings: 31%
  • 3 stars: 4%
    3 Star Ratings: 4%
  • 2 stars: 0%
    2 Star Ratings: 0%
  • 1 star: 0%
    1 Star Ratings: 0%
SQL Server 2012 Administration Skills Upgrade

You Will 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
  • Resolve performance problems by examining waits
  • Configure a contained database to achieve deployment flexibility
SQL Server 2012 Administration Skills Upgrade

Hands-On Experience Includes:

  • Migrating databases to SQL Server 2012
  • Leveraging the new features in Management Studio
  • Configuring AlwaysOn availability groups
  • Defining SQL Server Audits
  • Creating a contained database to avoid orphaned users
  • Analyzing waits to troubleshoot performance problems

About This Course: SQL Server 2012 includes an extensive array of capabilities 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.

  • Course Info

    Course 2105 Content

    Introducing the New Database Engine Features

    Installing SQL Server

    • Assigning service accounts
    • Choosing installation options
    • Acquiring the AdventureWorks sample database

    Exploring Licensing

    • Core vs. Server and CAL licenses
    • Maximizing virtualization opportunities

    Upgrading to SQL Server 2012

    Planning an upgrade

    • Installing the Upgrade Advisor
    • Analyzing 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

    • 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
    • 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-level audits
    • Auditing database events
    • Filtering audit events

    Managing Contained Databases

    Comparing contained and noncontained databases

    • Considering the advantages of contained databases
    • Handling temporary tables with a contained database
    • Avoiding collation conflicts

    Developing and creating a contained database

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

    Breaking Performance Bottlenecks in SQL Server 2012

    Querying important dynamic management objects

    • sys.syscacheobjects
    • sys.dm_os_waiting_tasks
    • sys.dm_exec_query_stats
    • sys.dm_os_wait_stats
    • sys.dm_os_volume_stats
    • sys.dm_os_performance_counters

    Exploring the plan cache

    • Viewing cache contents
    • Building a Database Engine Tuning Advisor (DTA) Workload

    Monitoring waits and queues to improve performance

    • Contrasting waits and queues
    • Analyzing significant wait types
    • Correlating waits and queues

    Simulating concurrent users

    • Investigating SQL Server Distributed Replay
    • Preprocessing the workload
    • Executing on multiple threads

    Employing extended events for performance tuning

    • Modules
    • Packages
    • Events
    • Predicates
    • Constructing packages
    • Resolving blocking problems with Extended Events

    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

    • Designing the index
    • Measuring the impact of the index

    Who Should Attend

    Experienced SQL Server administrators considering or currently migrating to SQL Server 2012, who have practical DBA experience with prior versions of SQL Server databases.

    Training Hours

    Standard class hours:
    9:00 a.m. - 4:30 p.m.

    Last day class hours:
    9:00 a.m. - 3:30 p.m.

    Free optional course exam:
    3:30 p.m. - 4:30 p.m.

    Each class day:
    Informal discussion with instructor about your projects or areas of special interest:
    4:30 p.m. - 5:30 p.m.

  • More Course Info

    What is this course about?

    In this course, you gain the in-depth knowledge and skills to take advantage of the new administrative features and tools available in SQL Server 2012, that will be Microsoft's next release of its data management software. You learn to build availability solutions with AlwaysOn availability, develop FileTable solutions, manage contained databases, resolve performance bottlenecks by monitoring waits, and apply columnstore indexes to decision support queries.

    Who will benefit from this course?

    This course is valuable for experienced SQL Server administrators who are planning to migrate to SQL Server 2012. Those administrators who wish to evaluate this product for their organisation will also benefit greatly.

    What background do I need?

    This course is not designed for those new to SQL Server administration. This assumes administrative experience at the level of Course 138, SQL Server® 2008 Database Administration, or equivalent knowledge.

    What is SQL Server 2012?

    Microsoft SQL Server 2012 provides a trusted, productive, and intelligent data platform that enables you to run your most demanding mission- critical applications, reduce time and cost of development and management of applications, and deliver actionable insight to your entire organisation.

    What are the new important features in Business Intelligence for SQL Server 2012?

    In addition to the classic multidimensional SQL Server Analysis Services database, there is now the new option of the tabular model database. This database structure, completely new for 2012, allows SQL Server administrators and developers new methods for providing critical data to business executives, analysts and report writers. SQL Server 2012 also includes Power View, which allows end users to generate sophisticated reports on their own. SQL Server 2012 also includes new tools for making data warehouses more efficient, migrating data using the Integration Services and providing data for Excel and SharePoint PowerPivot users.

    I am still using SQL Server 2000 and my organisation would like to move directly to SQL Server 2012. Is this course useful to me?

    No. There are significant differences between SQL Server 2012 and SQL Server 2000 that would make this course difficult for the SQL Server 2000 administrator.

    I am still using SQL Server 2005 and my organisation would like to move directly to SQL Server 2012. Is this course useful to me?

    Yes, it is possible to move from 2005 to 2012. However, you may need to do research on the 2008 features that are not in 2005.

    What are the major differences between SQL Server 2012 and SQL Server 2008?

    The major differences between the versions are in the areas of high availability, manageability, programmability, scalability and performance.

    What course would be applicable to me if I'm moving from SQL Server 2000 to SQL Server 2012?

    Course 2108, SQL Server® 2012 Database Administration, would be a good course for you to take. It will have some overlap in administration content with your current knowledge, but you will obtain the skills and knowledge you need to successfully administer SQL Server 2012.

    Does this course cover the components in Microsoft's Business Intelligence platform?

    The focus of this course is on SQL Server 2012 Administration Skills.

    For more information on Analysis Services, see Course 139, SQL Server® Analysis Services for Business Intelligence. For Integration Services, see Course 134, SQL Server® Integration Services for Business Intelligence, and for additional information on Reporting Services, see Course 140, SQL Server® Reporting Services.

    What platform is used for the hands-on exercises?

    This course runs on Windows 2008 R2 Enterprise edition with SQL Server 2012.

    How much time is spent on each topic?

    Content Hours
    Introducing the New Database Engine Features 1.0
    Upgrading to SQL Server 2012 3.0
    Implementing AlwaysOn Availability 4.5
    Securing SQL Server 2012 2.0
    Managing Contained Databases 2.0
    Breaking Performance Bottlenecks in SQL Server 2012 3.0
    Applying Columnstore Indexes to Decision Support Queries 1.5

    Times, including the workshops, are estimates; exact times may vary according to the needs of each class.

    What kinds of hands-on exercises are performed in the course?

    Approximately 40% of time on the course is devoted to hands-on exercises, allowing you to gain extensive experience administering SQL Server 2012. Exercises include:

    • Migrating databases to SQL Server 2012
    • Leveraging the new features in Management Studio
    • Configuring AlwaysOn availability groups
    • Defining SQL Server Audits
    • Creating a contained database to avoid orphaned users
    • Analysing waits to troubleshoot performance problems

    I'm attending this course from work using AnyWare - Learning Tree's web-based remote attendance platform. How will that impact what I learn?

    You will participate fully in the course and acquire the same knowledge and skills as your classmates who participate in the classroom. You will have the same course materials, be able to easily communicate back and forth and ask questions of your instructors and peers, and you will control an in-classroom workstation dedicated entirely to you. Your instructor will be able to see exactly what you're doing and can interactively offer concrete help.

    How does this course relate to other Learning Tree courses?

    Learning Tree offers a number of courses that will help you expand upon the knowledge gained in this course and further your skills in several key areas. Those courses include:

  • Certification/Credits

    Many Learning Tree courses provide college credit and industry continuing education credits. You can also earn a Learning Tree Professional Certification in your area of expertise and prepare for popular industry certifications. For continuing professional development information, click here.

  • Tuition

    3-Day FeeNew AttendeeNotes
    Fee Per Course £1,645

    Click here for multi-course savings plans.

    Tuition payment is not required at time of enrolment.


Guaranteed to Run Guaranteed to Run event

Scheduled Event Scheduled event

Afternoon Start Time Event Afternoon Start Time event

Course Dates
3 Days

Attend from Home, Work or AnyWare Centre Via AnyWare Learn More »


Attend In-Class at our London Education Centre or LiveLinked from an AnyWare Centre
Learn More »

Ed. Centre


First - Select Your Date:


Then - Select Your Attendance Method:

Attend from AnyWare Learning Centres  

Birmingham, Bracknell, Bristol, Brussels, Edinburgh, Leatherhead, Leeds, Manchester

You are provided with a dual screen computer and high-speed Internet — all set up and ready to go.
Click here to learn more.

Attend from Home or Work  

Using your computer and Internet Connection.
Click here to learn more.

Tuition Fees & Savings Plans

Take advantage of our Multi-Course Tuition Savings Plans. Learn more »

Enrol Today – Risk Free!

Call 0800 282 353 or enrol online by selecting your date above.

  • No advance payment required.
  • No fees for cancellation or rescheduling, ever.
  • Take up to 30 days after your course date to pay.
  • Satisfaction guaranteed – or you pay no tuition fee.

You can bring this course on-site to your organisation

Bring Learning Tree Training to your workplace with our On-Site Training Solution. Learn more »