SQL Server® 2012 Database Administration: Hands-On

• Course Info

  You Will Learn How To

  • Administer SQL Server 2012 with SQL Server Management Studio and Transact-SQL
  • Ensure tight SQL Server security with logins, roles, users and permissions
  • Develop backup strategies, perform backups and recover from disasters
  • Leverage SQL Server Agent to automate administrative tasks and generate alerts
  • Isolate and repair fragmentation and database corruption

  Hands-On Exercises

  

  • Employing Transact-SQL and the graphical tools
  • Upgrading SQL Server 2005 and 2008 databases
  • Implementing security for logins, databases and objects
  • Creating contained databases
  • Repairing database corruption
  • Building a database maintenance plan

  Course Content

  Getting Started with SQL Server 2012

  Setting up SQL Server 2012

  • Selecting installation options
  • Installing a named instance

  Leveraging essential tools

  • SQL Server Management Studio
  • Configuration Manager
  • Transact-SQL
  • sqlcmd
  • PowerShell
  • Dedicated Administrator Connection

  Constructing and Managing Databases

  Inspecting storage structures

  • Relating servers, databases and files
  • Creating databases and transaction logs
  • Spreading data amongst file groups

  Upgrading and moving databases

  • Choosing between upgrade and migration
  • Detaching and attaching databases

  Controlling database space

  • Permitting automatic database growth
  • Adding database files to expand databases

  Handling Server and Database Security

  Implementing server security

  • Comparing authentication modes
  • Defining logins
  • Creating user-defined server roles
  • Enforcing password policy

  Granting database access

  • Adding users and defining new roles
  • Delegating privileges with predefined roles
  • Repairing mismapped logins

  Creating contained databases

  • Examining components and terminology
  • Enabling contained databases
  • Contrasting SQL and Windows users

  Converting existing databases to contained databases

  • Discovering barriers to containment
  • Transforming current users

  Granting and Revoking Permissions

  Managing database-scoped privileges

  • Permitting object creation
  • Giving blanket permissions

  Defining object-level permissions

  • Limiting object access
  • Meeting complex permission requirements with roles

  Backup and Recovery

  Backing up databases

  • Selecting a recovery model
  • Running full, log and differential backups

  Restoring databases

  • Rebuilding the master database
  • Recovering user and system databases

  Streamlining Tasks with Jobs,Alerts and Database Mail

  Configuring SQL Server Agent

  • Setting agent properties
  • Implementing database mail

  Defining jobs to handle routine tasks

  • Creating alerts and operators
  • Associating alerts with jobs and initiating multistep jobs

  Database Maintenance and Monitoring

  Handling fragmentation and database corruption

  • Identifying and curing fragmentation
  • Repairing database corruption

  Building database maintenance plans

  • Scheduling plan execution
  • Notifying operators of success or failure
  • Resolving contention problems

  Who Should Attend

  Those responsible for administering SQL Server 2012 who have a basic understanding of SQL Server 2012 or experience at the level of Course 2107, "SQL Server 2012 Comprehensive Introduction".

  Related Courses

  • Windows Server 2008 Comprehensive Introduction - Course 960
  • SQL Server Transact-SQL Programming - Course 532
  • SQL Queries for SQL Server - Course 534
  • SQL Server Integration Services - Course 134
  • SQL Server Analysis Services - Course 139
  • SQL Server Reporting Services - Course 140
  • Windows Server 2008 Administration - Course 961
  • High-Performance SQL Server Databases - Course 535
  • Windows PowerShell: Automating Administrative Tasks - Course 969

  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 extensive experience using SQL Server 2012 tools to perform real-world administrative tasks with SQL Server Management Studio and Transact-SQL. You learn to install SQL Server, create and modify data structures, back up and recover databases, implement login and database security, and manage permissions.

  Who will benefit from this course?

  This course is designed for those new to SQL Server who have not had prior training or experience in administration. This course is valuable for those whose work involves, or will involve, the administration of a SQL Server 2012 database. Typical participants include database administrators, system administrators, and application and system developers. The jobs performed by both a system administrator (primarily the custodian of resources and responsible for tuning) and a database administrator (usually responsible for security, backup and recovery, and task scheduling) are covered in-depth.

  What background do I need?

  It is assumed that you have relational database experience at the level of Course 2107, SQL Server^® 2012: A Comprehensive Hands-On Introduction, along with a working knowledge of computers and a familiarity with Windows, or Course 960, Windows Server^® 2008: A Comprehensive Hands-On Introduction. Programming experience is helpful but not required.

  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 organization.

  What are the major new features for administrators in SQL Server 2012?

  There are several new features for administrators in SQL Server 2012. These include: the introduction of contained databases which help solve the problems people experience when moving databases from one server computer to another and the addition of user-defined server roles.

  SQL Server 2012 also includes enhanced features for analyzing the physical integrity of a database and for repairing any damaged database pages. The auditing capabilities introduced in SQL Server 2008 have also been improved and are available in more editions of SQL Server 2012.

  Does this course cover SQL Server 2008?

  No, this course focuses solely on SQL Server 2012. For coverage on SQL Server 2012 database administration, please see Course 138, SQL Server^® 2008 Database Administration: Hands-On.

  I have taken Course 138, SQL Server^® 2008 Database Administration: Hands-On. Should I take this course?

  No. Course 138 covers many of the same fundamental elements of database administration that this course covers. Those with a background in SQL Server 2008 administration should take Course 2105, SQL Server^® 2012: Administration Skills Upgrade instead.

  I have taken Course 2105, SQL Server^® 2012: Administration Skills Upgrade. Should I take this course?

  No. Course 2105 covers the new features of SQL Server 2012 for experienced administrators. This course targets those that are new to database administration.

  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, scalability and performance.

  How much time is spent on each topic?

  Content	Hours
  SQL Server 2012 installation and new features	3.0
  Constructing and managing databases	3.5
  Handling server and database security	5.0
  Granting and revoking permissions	2.0
  Backup and recovery	5.0
  Streamlining tasks with jobs, alerts and database mail	2.0
  Ensuring SQL Server 2012 health	2.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 this course?

  Numerous hands-on exercises performing basic and advanced database administration tasks are conducted during the course, accounting for 40 percent of class time. These exercises are intended to reinforce the concepts taught in class and provide you with a real-world experience with SQL Server. They include installing SQL Server 2012, managing storage, back up and recovery of system and user databases, managing security, scheduling multi-step jobs, setting up alerts, performing routine maintenance, and monitoring SQL Server.

  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.

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

  The focus of this course is on SQL Server 2012 Database Engine 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 uses Microsoft SQL Server 2012 running on Windows Server 2008. In addition, there is a domain controller running Windows Server 2008.

  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:

  • 960, Windows Server® 2008: A Comprehensive Hands-On Introduction
  • 532, SQL Server Transact-SQL® Programming: Hands-On
  • 534, Developing SQL Queries for SQL Server®: Hands-On
  • 134, SQL Server® Integration Services for Business Intelligence: Hands-On
  • 139, SQL Server® Analysis Services for Business Intelligence: Hands-On
  • 140, SQL Server® Reporting Services: Hands-On
  • 961, Windows Server® 2008 Administration: Hands-On
  • 535, Developing High-Performance SQL Server® Databases: Hands-On

• 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. See below for continuing professional development credits associated with this course.

  This course qualifies as a Core Course in our Job-Specific Professional Certification Program. Read More...

• Tuition

  4-Day Fee	New Attendee	Returning Attendee	Notes
  Fee Per Course	£1,985	Call 0800 282 353	Click here for multi-course savings plans. Tuition payment is not required at time of enrolment.