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 SQL Server security with logins, roles, users and permissions
- Develop backup strategies and recover from disasters
- Leverage SQL Server Agent to automate administrative tasks
- Isolate and repair fragmentation and database corruption
Hands-On Experience Includes:
- Employing Transact-SQL and the graphical tools
- Upgrading SQL Server 2005 and 2008 databases
- Implementing security for logins, databases and objects
- Restoring a database to point-of-failure
- Repairing database corruption
- Building a database maintenance plan
About This Course: SQL Server 2012 offers a sophisticated platform for enterprise data management. Organizations rely on database administrators who know how to manage their mission-critical databases correctly. This course targets those that are new to database administration and offers the essential skills you need to install this next generation of SQL Server.
Course 2108 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
- Dedicated Administrator Connection
Constructing and Managing Databases
Inspecting storage structures
- Relating servers, databases and files
- Creating databases and transaction logs
Designing file groups
- Maximizing storage utilization
- Placing tables on 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
- Contrasting users and logins
- Adding users
- Defining new roles
- Delegating privileges with predefined roles
- Repairing mismapped logins
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
- Investigating the transaction log
- Running full, log and differential backups
- Performing a post-crash log backup
- 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
- Initiating multi-step jobs
- Associating alerts with 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
- Identifying blocked processes
- Killing blockers
Who Should AttendThose new to SQL Server administration and who will be responsible for administering SQL Server 2012. A basic understanding of SQL Server 2012 or experience at the level of Course 2107, "SQL Server 2012 Introduction," is required.
- SQL Server Transact-SQL Programming - Course 532
- SQL Queries for SQL Server - Course 534
- SQL Server Integration Services for Business Intelligence - Course 134
- SQL Server Analysis Services for Business Intelligence - Course 139
- SQL Server Reporting Services - Course 140
- SQL Server High Availability - Course 141
- Developing High-Performance SQL Server Databases - Course 535
- Windows PowerShell: Automating Administrative Tasks - Course 969
- Windows Server 2012 Introduction - Course 2410
- Windows Server 2012 Administration - Course 2411
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 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.
I am an experienced SQL Server Database Administrator and want to learn what's new in SQL Server 2012. Is this the right course for me?
No, Course 2105, SQL Server® 2012: Administration Skills Upgrade, covers the new features of SQL Server 2012 for experienced database administrators. This course targets those that are new to database administration.
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 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 Introduction: Hands-On, along with a working knowledge of computers and a familiarity with Windows, or Course 960, Windows Server® 2008 Introduction: Hands-On. Programming experience is helpful but not required.
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.
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 BI components, please refer to Course 146, SharePoint® Business Intelligence: Hands-On, for those who are not familiar with previous versions of the Microsoft business intelligence tools.
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.
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.
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 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 Introduction: Hands-On
- 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
- 139, SQL Server® Analysis Services for Business Intelligence
- 140, SQL Server® Reporting Services: Hands-On
- 141, SQL Server® High Availability: Hands-On
- 961, Windows Server® 2008 Administration: Hands-On
- 535, Developing High-Performance SQL Server® Databases
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 for 2 semester hours of college credit as certified by the American Council on Education's College Credit Recommendation Service (ACE CREDIT). Read More... This course qualifies as a Core Course in our Job-Specific Professional Certification Program. Read More...
4-Day Tuition New Attendee Returning Attendee Notes Commercial $2,810 $2,530
Click here for multi-course savings plans.
Tuition payment is not required at time of enrollment.
Government $2,499 $2,249
Tuition & Savings Plans
Take advantage of our Multi-Course Tuition Savings Plans. Learn more »
Enroll Today – Hassle Free!
Call 1-888-843-8733 or enroll 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.
You can bring this course on-site to your organization
Bring Learning Tree Training to your workplace with our On-Site Training Solution. Learn more »
"The course content was very relevant to my job and taught me SQL Server skills I will apply immediately. The instructor gave us actual, real-world examples—he kept us busy without overwhelming us."
– S. Kijek
U.S. Department of Veterans Affairs