SQL Server® 2012 Database Administration: Hands-On
Course 2108 | 4 Days
Evaluations in the Last 12 Months
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
- 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
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
- 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
- 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 AttendThose 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".
- 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
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
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...
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.
Course 2108 • 4 Days
Two Ways to Attend Your Course
1. In the classroom -OR-
2. Via the web using AnyWare
Note: AnyWare, our web-based remote attendance platform, lets you participate fully in any scheduled course date below from your workplace or home. For AnyWare enrolments, please register at least 7 days prior to the start of the course.
Course Dates 4 Days
US East Coast Dates
*Work and train on the same day! Attend live US East Coast Courses online from 2pm to 9pm GMT.
Enrol Today – Risk Free!
Call 0800 282 353 or enroll online by selecting your date above.
- No advanced 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.
Tuition Fees & Savings Plans
Take advantage of our Multi-Course Tuition Savings Plans. Learn more »
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 ».
"My experience attending a course online via the Learning Tree AnyWare solution went very well. The instructor stayed in touch with me throughout the session so I always felt I was actively part of the class. I am looking forward to taking another online course with Learning Tree and will be recommending the format to our training coordinator for others to take advantage of".
– M. Evans
Service Desk Analyst