SQL Server® 2012: Administration Skills Upgrade

• Course Info

  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

  Hands-On Exercises

  

  • 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

  Course 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
  • Maximising virtualisation opportunities

  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

  • 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
  • Analysing 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.

  Related Courses

  • High-Performance SQL Server Databases - Course 535
  • Windows PowerShell: Automating Administrative Tasks - Course 969
  • Designing an Effective Data Warehouse - Course 2191
  • Administering SharePoint 2013 Server Farms - Course 1532
  • SQL Server Reporting Services - Course 140
  • SQL Server Analysis Services - Course 139
  • .NET 4 and 4.5 Programming - Course 2620

  Training Hours

  First Day Orientation:
  8:00 a.m. - 9:00 a.m.

  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 Last Day:
  3:45 p.m. - 4:30 p.m.

  Each Class Day:
  Upon Request: Informal discussion with instructor about your work-specific projects or areas of special interest:
  4:30 p.m. - 5:00 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: Hands-On, 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 research 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: Hands-On, 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 an indepth look into the BI Stack, you may be interested in attending Course 146, SharePoint^® 2010 Business Intelligence: Hands-On

  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 by monitoring waits	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
  • Creating a contained database to avoid orphaned users
  • Troubleshooting waits
  • Constructing a columnstore index to improve performance

  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:

  • 1510, Administering SharePoint® Server 2010: Hands-On
  • 382, Relational Database Design, Tools and Techniques: Hands-On
  • 535, Developing High-Performance SQL Server® Databases: Hands-On
  • 296, Project Management: Skills for Success
  • 969, Windows PowerShell™ Hands-On: Automating Administrative Tasks

• 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 Fee	New Attendee	Returning Attendee	Notes
  Fee Per Course	£1,595	Call 0800 282 353	Click here for multi-course savings plans. Tuition payment is not required at time of enrolment.