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.

This Microsoft SQL Server video training with Garth Schulte covers Microsoft's in-demand database server, including how to plan, design and implement instances, databases, security, administration, automation and programming objects....
This Microsoft SQL Server video training with Garth Schulte covers Microsoft's in-demand database server, including how to plan, design and implement instances, databases, security, administration, automation and programming objects.

Database designers are the architects of database implementations. They love to plan and design every aspect of a database implementation, from instances to databases to schemas and the objects that slice, dice, store, and access data. This Nugget course takes you through the mindset of a designer to learn how the blueprints for successful database implementations are constructed.

70-465 is the final exam for the Microsoft SQL Server MCSE: Data Platform certification for database engineers. This course helps you prepare for the 70-465 exam and beyond with real-world examples and live demonstrations.

Note: The exam associated with this course is scheduled to retire on March 31, 2017. Learners should plan accordingly to sit for the exam before its official retirement.

Recommended Experience
  • 2 years experience working with SQL Server
  • Familiarity with writing T-SQL statements
Recommended Equipment
  • Microsoft SQL Server 2012
Related Certifications
  • MCSE: SQL Server 2012 Data Platform (retiring March 31, 2017)
  • MCSE: Data Management and Analytics (replacing MCSE: Data Platform)
Related Job Functions
  • Database designers
  • Database developers
  • Database administrators
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-465: Designing Database Solutions for Microsoft SQL Server 2012 (8 min)
2. Designing a Database Server Infrastructure (18 min)
3. Planning for Consolidation (20 min)
4. Designing SQL Server Instances (19 min)
5. Logical Database Design (34 min)
6. Physical Database Design (31 min)
7. Designing Backup & Recovery (25 min)
8. Designing Tables (21 min)
9. Designing BLOB Storage and Full-Text Search (30 min)
10. Designing Views and Stored Procedures (26 min)
11. Designing Concurrency & Transactions (26 min)
12. Designing Multi-Server Automation (16 min)
13. Designing Instance Security (16 min)
14. Designing Database Security (20 min)
15. Designing Policy-based Management (17 min)
16. Designing a Maintenance Strategy (18 min)
17. Designing for High Availability (25 min)
18. Designing for Replication (24 min)
19. Monitoring Server Health (14 min)
20. Tuning Query Performance (12 min)

Introduction to 70-465: Designing Database Solutions for Microsoft SQL Server 2012

00:00:00

Introduction to 70-465. Designing database solutions for Microsoft SQL Server 2012. Hey, everybody. Garth Schulte from CBT Nuggets. I personally wanted to welcome and thank you for letting me be your guide through 70-465 and all of our SQL Server 2012 courses here at CBT Nuggets.

00:00:19

In this Nugget, we're going to get familiar with the 70-465 exam and course. And we're going to define what a database design is. This is a design course, so it's all about designing and specing out every aspect of a SQL Server implementation. So we'll get familiar with the course, we'll head over to Microsoft's website, and look at the exam objectives.

00:00:36

And we'll head over to the Virtual Nugget lab so we can get familiar with our virtual sandbox. Let's do it. This course and the exam is aimed at the database designer. What is a database designer? Think of them as the architects of SQL Server. They're the ones who are going to come up with the blueprints, based on the requirements, and spec out instances in databases.

00:00:57

Their responsibilities are going to include defining the purpose of the database, coming up with a logical design, and mapping out how the physical design should be laid out. The database designer's scope is pretty much the entire SQL Server implementation.

00:01:10

They'll work with nearly every role in the organization. They'll work with business analysts and users to get an understanding of the vision and the requirements. They'll take those requirements transform them into a logical design with which they'll work with developers to bring to life.

00:01:23

And then they'll work with DBAs to map out the physical implementation and even instance configurations. So it's a big job. Which probably explains why designers get paid so much because it's a critical role. Blueprints are the base for everything in a SQL Server implementation.

00:01:38

Without a proper blueprint, all the other levels are going to suffer. And this is just high level, but think of all the things a designer needs to plan for and map out. They need to think about their maintenance strategies, backup strategies, high availability strategies, security, automation-- the list goes on and on.

00:01:57

So that's a high level look at what database designers are all about. Let's check out the course content here. So we have 20 total Nuggets. One intro, which you're watching now. 19 objective Nuggets. We'll go over those objectives shortly. Here's how the exam is broken down.

00:02:10

29% of it is designing database structure. So we have six Nuggets that are going to cover everything from how to design a database server infrastructure to SQL Server instances, logical and physical database design, and we'll even talk about our backup and recovery strategy.

00:02:24

Then we have five Nuggets, 32% of the exam, covering how to design your database and database objects. So here we'll talk about how to design a database model. We'll talk about a design tables, store procedures, views, transactions, concurrency, and we'll even get multi-server automation and administration in there.

00:02:40

Then security here is just a small part of the exam, 15%. We have three Nuggets that cover it. We've got a Nugget for database security, designing instance security, and one for designing and utilizing policy based management. That's a good one. And at the end here we've got 24% percent of the exam, five Nuggets, covering how to design and optimize SQL Server solutions.

00:03:01

Here we're going to talk about how to design a maintenance strategy, design for high availability, designed for replication, and we've got a couple of Nuggets on monitoring server health and tuning database performance. And as a side note, you probably are already aware here if you're watching this, but 70-465 is the final exam for the MCSE Microsoft Certified Solutions Expert Data Platform certification.

00:03:24

So woo-hoo, you're almost there. It's like climbing Mount Everest to get here. I know, right? But we're at the top. We're at the peak. We're at the snow cap of the mountain. So don't look back. Don't look down. Just look ahead, and let's do it. Let's get this one done.

00:03:40

With that why don't we head over to Microsoft's website and check out the exam objectives for 465. So here I'm on the main page here for the MCSE Data Platform Certification that lists all the exam requirements. We're on the last one. So let's click on 465 here, designing database solutions for SQL Server 2012.

00:03:55

And let's scroll down and check out the skills measured. So here they are. The first one here, designing database structures. Again, 29% of the exam is really going to deal with coming up with your logical, your physical design and mapping out your instances and your infrastructure.

00:04:10

So we've got design for business requirements, design physical database and object placement, table and index partitioning strategy, migration consolidation and upgrade strategy, designing instances, and designing for backup and recovery. So that's going to be 29% of the exam.

00:04:25

And I do want to note, usually for these I try to map a Nugget to an objective. Well, as you'll see here, that was a little difficult to do with this one because it's almost like they just felt like copying and pasting some of these from 464, and I didn't want to rehash over everything we've gone through in 464.

00:04:42

So I've added some things that aren't in here and took some things away. So if we look at designing databases and database objects, we will do a design a database model designing tables. This one right here, designing for concurrency, is funny. They literally copied and pasted this objective from 464 because it's the exact same thing.

00:04:59

So we do have one that is designing transactions and concurrency because they did the same thing for transactions down here. This is the exact same objective from 464, so I threw these into one Nugget. Consolidated them so we'll just more-- rather than rehash exactly what we've already talked, I'll throw some new things in.

00:05:18

Throw some new demos in, but essentially here, it's going to be a refresher on what you already know for 464. Then we've got design stored procedures and then also come up with a management automation strategy. On the security side here, we have just regular security.

00:05:33

Really database and instance level security. So that's what we're going to do here. And again, some of this stuff we've already covered in previous exams for SQL 2012. So we will rehash some of it, but I also threw one in here for policy based management because that is actually in the MOCC course, the Microsoft Official Curriculum Course for 465.

00:05:52

So it's not an objective per se, but it is in the Microsoft Official Curriculum. So I wanted to throw that one in there. So we'll cover database security, incident security, and then security using policy based management. And at the end here, designing and troubleshooting an optimization solution.

00:06:06

We'll come up with a maintenance strategy, concurrency issues, high availability-- that's a good one. Designing high availability is a big one. And then we'll look at monitoring both performance and health at the instance level. So those are all of the objectives.

00:06:20

I also do want to point out, if you want to look at the actual curriculum you can go can go down to instructor led training and just open this up. Let me fire it up in a new tab here. And this will map out the course details. So you can see that a lot of these modules do not directly relate to objectives.

00:06:34

So that's why I kind of took the best of both worlds to come up with the outline here for this course. The last thing I want to take you through here is the Virtual Nugget Lab. Now if you're not familiar with the Virtual Nugget Lab, it's our demonstration environment.

00:06:47

It is our virtual sandbox. We'll be doing a lot of work over here. And just to get you familiar with this environment, let's launch SQL Server Management Studio. I'm going to launch right into the solution for this series. So this is a solution which is going to contain many projects.

00:06:59

We'll have a project per Nugget that will contain all of the demonstrations within that project. And just to show you our instance here, we're going to work with good old SQL Nugget Lab as our instance. The default instance here on this machine. And we've got plenty of sample databases to work with as well.

00:07:15

We've got AdventureWorks OLTP, AdventureWorks OLAP, and then we have our Nugget Demo DB, which we'll be using to create our own logical and physical design. I also want to make mention that at course completion you will have access to this machine, and I'll also create a lab manual which contains tasks and challenges.

00:07:31

And it's just a great way for you to do some lab exercises and get familiar with a lot of the major aspects of 70-465. And practice. Practice for the exam, practice for the real world, and practice for whatever you want. Have a little fun. You can do it all.

00:07:45

I should also point out that on the file system here we have a directory called Nugget Lab files. This will be our working directory, and you can see here is the solution we opened up over here. We'll have a directory, again, per project. And then we also have a data folder that houses all of our databases.

00:08:01

In this CBT Nugget we took an introduction to 70-465. We started off by defining what a database designer is. They are the architects of a SQL Server implementation. We got familiar with the course. We went over to Microsoft's website and took a look at the exam objectives.

00:08:15

And at the end, I got you familiar with the Virtual Nugget Lab, our sandbox for demonstrations. I hope this has been informative for you, and I'd like to thank you for viewing.

Designing a Database Server Infrastructure

Planning for Consolidation

Designing SQL Server Instances

Logical Database Design

Physical Database Design

Designing Backup & Recovery

Designing Tables

Designing BLOB Storage and Full-Text Search

Designing Views and Stored Procedures

Designing Concurrency & Transactions

Designing Multi-Server Automation

Designing Instance Security

Designing Database Security

Designing Policy-based Management

Designing a Maintenance Strategy

Designing for High Availability

Designing for Replication

Monitoring Server Health

Tuning Query Performance

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
Advanced 7 hrs 20 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