Quantcast

Start with 7 free days of training.

Gain instant access to our entire IT training library, free for your first week.
Train anytime on your desktop, tablet, or mobile devices.

Administering Microsoft SQL Server 2012 Databases

This SQL Server video training with Garth Schulte covers Microsoft’s in-demand server including how to install SQL Server 2012, implement a backup strategy, and more. It also prepares learners for Microsoft's 70-462 exam....
This SQL Server video training with Garth Schulte covers Microsoft’s in-demand server including how to install SQL Server 2012, implement a backup strategy, and more. It also prepares learners for Microsoft's 70-462 exam.

SQL Server database administrators are in extremely high demand all over the world. SQL Server 2012, with its high availability and cloud-ready features, is an industry leader, further increasing the demand for DBAs. If there ever was a time to get certified as a SQL Server professional, it's now!

This course leads you down the path of ultimate database geekery and helps you develop the skills necessary to become a SQL Server DBA. Learn what it takes to administer and manage SQL Server 2012 – from planning, installing, troubleshooting, recovering, and securing instances and databases.

Exam 70-462 is a prerequisite for the SQL Server 2012 MCSA certification, which is the first step toward attaining both MCSE certifications: Data Platform and Business Intelligence. This course helps prepare you for the 70-462 exam and beyond with real-world examples and live demonstrations.

Note: The exam associated with this course was retired December 1, 2015. However, this course still retains value as a training resource.

Recommended Experience
  • Familiarity with databases
Recommended Equipment
  • Microsoft SQL Server 2012
Related Certifications
  • MCSA: SQL Server 2012
  • MCSE: Data Platform
  • MCSE: Business Intelligence
  • MCSM: Data Platform
Related Job Functions
  • Database administrators
  • Database developers
  • Database analysts
Garth Schulte has been a CBT Nuggets trainer since 2001 and holds a variety of Microsoft and Google certifications spanning system administration, development, databases, cloud, and big data.
 show less
1. Introduction to 70-462: Administering SQL Server 2012 (10 min)
2. Planning a SQL Server 2012 Installation (32 min)
3. Installing SQL Server 2012 (39 min)
4. Implementing a Migration Strategy (31 min)
5. Configuring SQL Server Components (52 min)
6. Managing SQL Server Agent (34 min)
7. Configuring and Managing Databases (57 min)
8. Configuring and Managing Instances (46 min)
9. Implementing a Clustered Instance (43 min)
10. Identifying and Resolving Concurrency Issues (36 min)
11. Troubleshooting SQL Server (35 min)
12. Auditing SQL Server (32 min)
13. Implementing a Backup Strategy (38 min)
14. Restoring Databases (23 min)
15. Implementing and Maintaining Indexes (56 min)
16. Importing and Exporting Data (27 min)
17. Managing Logins and Server Roles (24 min)
18. Managing Users and Database Roles (28 min)
19. Managing and Troubleshooting Security (25 min)
20. Implementing AlwaysOn Availability Groups (37 min)
21. Implementing Database Mirroring (20 min)
22. Implementing Replication (34 min)

Introduction to 70-462: Administering SQL Server 2012

00:00:00

An introduction to 70-462, Administering SQL Server 2012. Hello and welcome, everyone, Garth Schulte here. I'll be your guide through this series. I just wanted to take an introductory Nugget here to get you familiar with SQL Server 2012, its components, the roles and responsibilities of us, the individuals that are going to be managing all of these components, and get you familiar with the buzzwords, the acronyms, because there's a lot of them-- things like SSAS and SSIS and SSRS and SSMS.

00:00:29

It goes on and on. So we'll cover all of those, talk about the roles and responsibilities. And I really want to get you familiar here with the 70-462 exam objectives. I'd like to go over Microsoft's website, look at what they're going to expect from us as far as the exam goes, and how you can relate it back to the Nuggets in this series.

00:00:46

Let's kick things off here with a look at the components and roles of SQL Server 2012. And where a better place to start than the heart and soul of SQL Server, which is the database engine itself? So the database engine is where the databases sit, where the data sits, and really where everything starts here in SQL Server.

00:01:01

And who's responsible for the database engine? You guessed it. That's going to be the production DBA. So DBA here, the Database Administrator, or as I like to call it, the big guy or gal in the sky because in order to do anything in SQL Server, you have to go through your DBA.

00:01:16

Not only that, but if anything goes wrong in SQL Server, who's the first person people are looking for? The DBA. So the DBA has a lot on his plate, a lot of responsibilities, things like security. That's a big responsibility here, ensuring that not only our system is secure, but our data is secure, ensuring availability, that our data is always available.

00:01:33

And if things do go wrong, we have a plan in place to quickly restore that data. So backup strategies, restore strategies. Also hardware and software responsibilities, ensuring that our hardware is properly scaling, ensuring that our software is properly patched and service packs applied.

00:01:51

There is another role at the database engine level, someone that I like to call the production DBA's right hand man. This would be known as the development DBA, or as I like to call them the DBDs, Database Developers of the world. And their primary responsibilities are going to revolve around the physical design of the database, building things like stored procedures and queries and views and really the programming side of SQL Server.

00:02:13

Now outside of the database engine, we have all of these surrounding technologies that can tap in and work with the data inside of the database engine. Starting right here, we have SQL Server Reporting Services, which is a reporting solution for SQL Server.

00:02:26

And I've worked with this many, many times over the years, small projects, big projects. I personally love it. It's very easy to use to build anything from simple reports to complex reports to automated and scheduled reports that do email delivery. Not only that, but there's an ad hoc side to this that is really, really nice.

00:02:43

Ad hoc reporting allows us to basically build a data model, or report model, as we call it, which is really just a simplified view of the underlying data structure inside of a database. And so we can give users, via the reporting services web interface, a way to launch a report builder, which is just a very simplified report building tool that they can use to design their own reports on their local system against our ad hoc data models.

00:03:12

So it's a very, very easy way to give users access to build their own reports. Love it. Definitely look into that if you're using SQL Server and you need a reporting solution because it integrates very well with SQL Server and it's really easy to use.

00:03:28

So we have reporting capabilities. We also data mining capabilities with SQL Server Analysis Services, which gives us a way to create data warehouses and work with multi-dimensional data. Not only that, but if we have any ETL needs, electronic, transformation, and load, allows us to work with data specifically, import and export data out of SQL Server.

00:03:47

We have SQL Server Integration Services. And I like to call SQL Server Integration Services our Swiss army knife of the back end world because it allows us to do so much more than just import and export data. We can automate a lot of administrative features in SQL Server.

00:04:02

And it's great. Every DBA and DBD should know SQL Server Integration Services because it just has so much utility in it. Really, you can automate a lot of the redundant tasks in your life using Integration Services. It's very, very, very powerful. So I definitely advise you getting familiar with SSIS.

00:04:20

So all of these are known as the business intelligence side of SQL Server. And because they've gotten so popular over the years, we have a new role. We would call this the Business Intelligence Developer or the Business Intelligence DBA. So this is the individual responsible for managing all of these services, not really the services themselves, but more the development side of it, so building reports and building the cubes here in analysis services and managing your data warehouses and building your packages in SSIS.

00:04:53

Now one thing all of these roles have in common is they all use SSMS, SQL Server Management Studio, to administer and manage all of these components, especially the DBA and developer when it comes to the database engine. That's going to be their main tool.

00:05:09

The business intelligence developer's main tool for development is going to be SQL Server Data Tools, which is really just a Visual Studio 2010 interface on top of all of these components. So you can build reporting services projects, analysis services projects, integration services projects, which will then have all their specific designers for building things in their specific tools.

00:05:32

Another thing to note is that these roles may be broken out in larger companies, but smaller companies, you may just have a DBA that wears all of these hats. So that's it. These are all the components and roles. Most of our time inside of this series going to be spend down here in the database engine, working with the role of the production DBA and learning all the different things that we're going to do from a management's perspective here in SQL Server 2012, especially when it comes to things like high availability, security, backup and restore strategies, and all the cool new features that we can work with here in SQL Server 2012.

00:06:06

Now before we head over to Microsoft site and look at the exam objectives, I just want to talk a little bit about our virtual Nuggetlab. Now you know at CBT Nuggets we love doing hands-on live demonstrations so you can follow along and see exactly how they're done, but we've always done them on our local machine, which you wouldn't have access to.

00:06:22

Well now we're going to do them through the virtual Nuggetlab in this series, which allows me to do things on this virtual server that you can then access directly yourself and literally follow along as we do them together on the same machine. So head over to cbtnuggets.com or nuggetlab.com to find out more information how you can access our virtual Nuggetlab.

00:06:43

All right, why don't we head over to Microsoft's website and take a look at the exam objectives for the 70-462 exam, Administering SQL Server 2012? So I'm just going to launch Internet Explorer and google 70-462. And the first link we'll get here should be the link right to their exam.

00:06:57

And this will give us not only an overview of all the things that we should know before taking the exam-- and these are all the things we'll learn about throughout this series-- but if you head over to Skills Measured, this will break it out for us. And I tried to develop the actual outline for this series to follow exactly what you see here.

00:07:14

So our first Nugget after to this one is actually planning a SQL Server 2012 insulation, just like you see here. So 19% of the exam is going to be installing and configuring SQL Server. We'll start with how to plan an installation. We'll follow that up with how to actually do an installation, so we'll head over to our virtual Nuggetlab.

00:07:31

We'll install SQL Server and some of the surrounding components that we just took a look at, things like SQL Server Integration Services. We'll take a look here at how to implement a migration strategy. We'll look at how to configure the other additional SQL Server components because you can see up here in the original install, it's not SSRS.

00:07:47

It's SSAS. So we'll take a look at the additional stuff, analysis services, reporting services, integration services. We'll then take a look at how to manage and configure SQL Server Agent, which is another component, but it sits inside of the database engine.

00:08:00

So we'll look at that. We'll then take a look at how to maintain instances in databases. That's going to be 17% of the exam, so we'll take a big look at here how to manage and configure databases. We'll then take a look at how to manage and configure instances.

00:08:13

We'll look at clustering, a very cool part of SQL Server. We'll take a look at how to manage our SQL Server instances. And then we'll look at how to optimize and troubleshoot SQL Server, everything from concurrency issues to just general troubleshooting of SQL Server to auditing SQL Server, a big thing in today's world.

00:08:30

We'll then look at how to manage data, from backup and restore strategies to implementing and managing and maintaining indexes to importing and exporting data at the database level. Not using integration services here, but at the database level. We'll then take a look at security, another huge part of SQL Server, everything from managing server login and server roles to database logins, database roles and permissions.

00:08:52

We'll take a look at security. And at the end here, we'll look at all the high-availability features of SQL Server. We'll take a big look here at Always On, that brand new feature, the highly touted feature that everyone's looking forward to. We'll take a big look at how it works.

00:09:04

We'll look at database mirroring. And we'll also look at replication. So flipping over to the series layout, you'll see it's laid out exactly the same as the exam objectives. So if you find you're fuzzy in a specific area in the exam objectives, you'll have an easy way to find it here inside of the series.

00:09:20

So we'll start at the top in learning how to plan, install, and configure SQL Server. We'll then learn how to manage and maintain databases and instances. We'll take a look at how to optimize, how to manage the data, backup restores, indexes, importing and exporting, security, both at the server and database level, and then, of course, high availability.

00:09:40

This has been an introduction to 70-462, Administering SQL Server 2012, where we just took a brief look at all the components and technologies in SQL Server 2012, the roles and responsibilities of the individuals involved. We then took a look at what Virtual Nuggetlab is all about, where we will be doing all of our hands-on and live demonstrations.

00:09:58

We took a look at the exam objectives and the series layout to see how you could relate the two together. And I just wanted to say thank you again for letting me be your guide through this series. We'll learn a lot. We'll have a lot of fun doing so and hopefully prepare you for the 70-462 exam.

00:10:11

I hope this has been informative for you, and I thank you for viewing.

Planning a SQL Server 2012 Installation

Installing SQL Server 2012

Implementing a Migration Strategy

Configuring SQL Server Components

Managing SQL Server Agent

Configuring and Managing Databases

Configuring and Managing Instances

Implementing a Clustered Instance

Identifying and Resolving Concurrency Issues

Troubleshooting SQL Server

Auditing SQL Server

Implementing a Backup Strategy

Restoring Databases

Implementing and Maintaining Indexes

Importing and Exporting Data

Managing Logins and Server Roles

Managing Users and Database Roles

Managing and Troubleshooting Security

Implementing AlwaysOn Availability Groups

Implementing Database Mirroring

Implementing Replication

Please help us improve by sharing your feedback on training courses and videos. For customer service questions, please contact our support team. The views expressed in comments reflect those of the author and not of CBT Nuggets. We reserve the right to remove comments that do not adhere to our community standards.

comments powered by Disqus
Intermediate 13 hrs 22 videos

COURSE RATING

Training Features


Practice Exams
These practice tests help you review your knowledge and prepare you for exams.

Virtual Lab
Use a virtual environment to reinforce what you are learning and get hands-on experience.

Offline Training
Our iOS and Android mobile apps offer the ability to download videos and train anytime, anywhere offline.

Accountability Coaching
Develop and maintain a study plan with one-to-one assistance from coaches.

Supplemental Files
Files/materials that supplement the video training.

Speed Control
Play videos at a faster or slower pace.

Bookmarks
Included in this course
Pick up where you left off watching a video.

Notes
Included in this course
Jot down information to refer back to at a later time.

Closed Captions
Follow what the trainers are saying with ease.
Garth Schulte
Nugget trainer since 2002