Microsoft MCSA SQL Server 2016 (70-764): Overview

0:00Welcome, my friends, to our many SQL

0:02Server 2016 courses here at CBT Nuggets.

0:05In this Nugget, we're going to cover

0:06some of those new features that SQL Server 2016 offers

0:09that you'll be learning about throughout these courses.

0:12We'll also get up to speed on the tools and components

0:14that you'll be working with.

0:16And we'll talk about the virtual lab,

0:18our hands on lab environment that you

0:20can use to follow along with me throughout these courses.

0:23Let's jump in.

0:24There's no better place to start than to talk about some

0:26of the new exciting features SQL Server 2016 has to offer.

0:30Now, I just handpicked a few of my favorites out

0:32of the database engine component, but believe me,

0:34there are a ton of new features in SQL Server 2016 spanning

0:38all of the components and tools that SQL has to offer.

0:42And we'll be covering a good majority of them

0:44throughout these six SQL Server exams.

0:47I saved the best for first, because this is easily

0:51one of the coolest features to come out of SQL Server 2016.

0:53It has so many good uses.

0:56System version temporal tables we enable

0:58with the flick of a switch.

0:59And then SQL Server will track the history, the lifetime,

1:03of a record, all data modifications against it.

1:06This gives us the ability to see what a record looked

1:09like at any point in time.

1:12Another highly anticipated feature is the Query Store.

1:15This will keep track of our query executions

1:19over time and all the performance metrics

1:21around them, including execution plans, which

1:23will give us much better insight when troubleshooting

1:27and optimizing our queries.

1:28Next up is one of many new security features.

1:32We have things like always encrypted,

1:33dynamic data masking.

1:35But this one, oh, this one is the one

1:38we've been asking for a long time.

1:40Row-level security, it's finally built into the database engine,

1:43meaning we don't need to come up with our own custom solutions

1:46to implement row-level security.

1:49If you're into the cloud, this next feature is for you.

1:52The stretch database feature allows SQL Server

1:54to transparently migrate cold data up into Microsoft Azure.

1:59And the best part is that data is still accessible.

2:02You can still query it just as if it was on-premise.

2:05And this works with cold data.

2:07And so if you have tables with both cold and hot data,

2:09you can actually write a filter function

2:11to define what cold data is.

2:13Pretty cool and another feature that's really easy to use.

2:16Finally, we have in-memory OLTP.

2:19And I know, I know, this isn't a new feature.

2:21But it's one that continues to evolve.

2:23And it's still one of the most important features

2:25when it comes to performance in SQL Server.

2:27Many of the limitations that annoyed us have been removed,

2:30and it now supports tables up to 2 terabytes, up from 256 gigs.

2:36Here's a quick look at all of the SQL Server's

2:38major components and tools, all of which

2:40are covered in the SQL Server 2016 exams at some point.

2:44On the component side, we have the database engine.

2:47Of course, that is the core component

2:50that makes everything tick in SQL Server.

2:52We also have SQL Server Analysis Services.

2:55This is our solution for building

2:57OLAP and multi-dimensional data solutions.

3:00Next up is our reporting solution, SQL Server Reporting

3:03Services.

3:04We also have our ETL solution-- that's extract transform

3:06and load--

3:07in SQL Server Integration Services.

3:09And finally, we can model and manage master data

3:12with MDS, Master Data Services.

3:15On the tool side, two big ones here.

3:17One is our administration management and querying tool,

3:20SQL Server Management Studio.

3:21We will be spending a lot of time in there.

3:23And the other is our development tool in SQL Server, Data Tools.

3:27This is actually the replacement for the old Business

3:30Intelligence Development Studio, or BIDS for short.

3:34When it comes to SQL Server 2016's additions,

3:36nothing has changed much.

3:37We have the same five flavors that we've

3:39had for a while now in SQL Server,

3:41starting at the very top with Enterprise Edition.

3:44This is the top dog.

3:45No scalability limits and all the bells and whistles

3:48you could possibly ask for.

3:50Next in line is Standard Edition.

3:52And this is the one that most folks generally

3:54go with unless they have crazy scalability requirements

3:58and need access to all those high end features.

4:01Next up we have Developer Edition.

4:03And this is essentially Enterprise Edition,

4:05just not licensed for production use.

4:08So this is good for application developers

4:11or QA in testing environments.

4:13Web Edition, as the name implies,

4:15is designed to host databases that store web

4:19application based data.

4:21So a lot of the features have been removed that don't really

4:24apply to that kind of workload.

4:26Finally, we have the free edition, SQL Server Express.

4:29Anybody can download and play with this.

4:31And it's really aimed at the student and the hobbyist.

4:34The last thing we're going to touch on here

4:36is our virtual lab, your hands-on environment, again,

4:39for following along and getting that crucial hands-on

4:41experience with SQL Server.

4:43We call these Nugget Level Virtual Labs

4:45because every single Nugget that has a lab

4:48has it tailored to that specific Nugget.

4:50And by the way, you can tell when

4:51a Nugget has a lab when you see this icon

4:54in the upper left-hand corner of the Intro slide.

4:56You can then launch that lab from the right-hand side

5:00of the course page.

5:01So follow along with me click by click, query by query,

5:04and also use the lab as your own personal sandbox.

5:07You can learn without having to worry

5:09about messing up a production environment

5:11or your own environment.

5:13In this CBT Nugget we took an introduction

5:15to SQL Server 2016.

5:17Thanks for joining us here at CBT Nuggets.

5:19And thanks for taking this journey with me

5:22through Microsoft's incredible database platform.

5:25I hope this has been informative for you,

5:27and I'd like to thank you for viewing.

0:00SQL Server 2016 brings with it brand new certifications.

0:03Woo, hoo.

0:04Something we haven't seen since SQL Server 2012.

0:06And Microsoft has done a great job

0:08of streamlining and simplifying the structure and requirements

0:12for these new certifications.

0:14Let's jump in and break them all down.

0:16Here's the big picture.

0:17At the very top, we have what's known as the MTA.

0:20That's the Microsoft Technology Associate.

0:22All MTA certifications-- and there's three of them

0:24by the way.

0:25There's one for database, one for developer,

0:27one for IT infrastructure.

0:28They are targeting the absolute beginning, essentially

0:31the fundamentals of IT, as Microsoft calls it.

0:34And this database one is not new, by the way.

0:37This has been around for 7 or 8 years.

0:39I included it because if you have absolutely no SQL

0:42Server or prior database experience,

0:44this is a great starting point to get you up to speed.

0:47In order to acquire the MTA database certification,

0:50you will need to pass a single exam, 98-364 Database

0:54Fundamentals.

0:55This course does a good job of giving you

0:57a light introduction to all areas

0:59and all job responsibilities in SQL Server.

1:01It will ensure that you understand

1:03your core relational database concepts.

1:05It will give you a light introduction

1:06to Transact SQL and writing queries,

1:09as well as database development and database administration.

1:12So again, the MTA is recommended if you're brand new to this SQL

1:15Server stuff.

1:16It's not required.

1:17You do not need to acquire this certification in order

1:20to attempt our next certification, which

1:22are the Microsoft Certified Solution Associate

1:25Certification.

1:26And that's right you're seeing that correctly.

1:28We now have three of them in SQL Server 2016.

1:31And this is what I mean by streamlined and simplified.

1:34Microsoft did a great job of breaking these down,

1:37targeting an MCSA per job role.

1:40And the reason this is great is because if you

1:42were around for SQL Server 2012 or 2014,

1:45you'll know that the MCSA--

1:47there was only one of them and it required three exams,

1:50which was 70-461, 462, and 463.

1:53And those three exams were essentially three different job

1:56roles.

1:57So you really needed a broad set of skills

2:00in order to even attempt to acquire that certification.

2:03So if you are strictly a database developer or strictly

2:06a database administrator or strictly a BI developer,

2:08you can now focus on the MCSA that

2:11targets your job description.

2:12And if you're a multi-faceted SQL Server ninja,

2:15you can acquire multiple or even all three of these

2:18to prove your broad range of knowledge.

2:20Each one of these certifications has

2:21two exams associated with them.

2:24For example, here is the MCSA for database development.

2:27This certification targets developers

2:29who spend the majority of their time

2:31coding up our database objects and writing

2:34the queries that our database.

2:36The first exam is 70-761.

2:38This is really all about Transact SQL

2:41and ensuring that you can write basic queries, advance queries,

2:43and again a light introduction to coding up those database

2:46objects, like views, stored procedures,

2:48and user defined functions.

2:50The next exam is 70-762.

2:52This exam is focused on developing SQL databases,

2:55really designing, implementing, managing,

2:57and optimizing our database and the objects within.

3:01The next MCSA is focused entirely

3:03on database administration.

3:04This one again consists of two exams.

3:06The first one is 70-764.

3:09This one's all about administering your SQL

3:11servers--

3:12so security, backup and restoration,

3:14managing and monitoring, as well as

3:16high availability and disaster recovery.

3:19The second one, 70-765 is all around provisioning SQL Server

3:23databases.

3:23This one focuses on planning and deploying your SQL Server

3:27instances, both in Microsoft Azure, Microsoft's cloud

3:30platform, as well as on-premise.

3:32It also focuses on managing and monitoring

3:34your instances and the databases within, as well as managing

3:37and maintaining storage.

3:39The final MCSA is focused entirely

3:42on business intelligence, designing and implementing

3:45an infrastructure to support BI.

3:47The first exam here, 70-767, is your data

3:50warehousing exam, knowing how to design, implement, and maintain

3:53a data warehouse, load that data warehouse

3:55with your operational data, using SSIS, SQL Server

3:58Integration Services, which you'll

4:00use to build that ETL process, and finally improve the quality

4:04and accuracy of that data, using data quality services

4:07and master data services.

4:09The second exam, 70-768, is your SQL Server Analysis Services

4:13exam.

4:14This one is focused on configuring, managing,

4:16and maintaining SSAS, as well as designing multi-dimensional

4:20data models and writing the queries that target those

4:23models using multi-dimensional expressions-- that's MDX--

4:26as well as data analysis expressions, DAX.

4:28And that's that for your new MCSA certifications

4:31in SQL Server 2016.

4:32Again, very cool that Microsoft streamlined these to target

4:36specific SQL Server job roles.

4:38And that brings us to a brand new MCSE certification.

4:41That's Microsoft's Certified Solutions Expert.

4:44We only have one.

4:45Again, back in Server 2012, 2014, we had two,

4:48and they both required multiple exams.

4:49And they were fairly difficult to acquire.

4:52Not anymore.

4:53Now we only need to pass a single exam

4:55from a big list of electives.

4:57And you can then easily acquire your MCSE.

4:59You also you need to have at least one MCSA.

5:02So that is a prerequisite to the MCSE.

5:05So assuming you've acquired one of these three SQL Server 2016

5:08MCSA certifications, or if you already have a SQL Server

5:122012/2014 MCSA--

5:14yes, those still count--

5:15then all you will need to do is take one of these exams

5:18on this list of electives and boom,

5:20you'll be an MCSE in data management and analytics.

5:23Microsoft has also stated that this list of electives

5:25will change over time.

5:27New exams will get added.

5:28Older exams-- like all these 400s,

5:31which are SQL Server 2012 and 2014 exams--

5:33will eventually go away.

5:35Another big change made to the MCSE certification

5:37is that it never expires.

5:39And we have the opportunity to acquire it

5:42every year to show that we're keeping up

5:44with these technologies.

5:46Here's how it works.

5:46Let's say that we acquired one of these SQL

5:48Server 2016 certifications, say administration, for example.

5:51Then we came over to this list of electives.

5:53And we took one of these.

5:55Let's take 70-773 as an example here.

5:57And we passed it.

5:58Now in our transcript, it's going

6:00to show that we're an MCSE for the current year.

6:03Let's say the current year is 2017.

6:05Now, let's say 2018 hits, we have an opportunity

6:08to acquire the MCSE once again.

6:11We cannot take this exam any more,

6:12because we've already taken it.

6:14So we'll have to take another exam on this list of electives.

6:16And if we did that and passed it,

6:18then it would show up on our transcript

6:20that we are now a MCSE in the year 2018.

6:23Pretty cool, right?

6:24Our Microsoft transcript now has much more value

6:27for ourselves and employers.

6:28They can now gauge someone's overall skill set.

6:31They can see what they specialize in

6:33and also see how they've kept up with technology over the years.

6:36Now that you're acquainted with the brand new SQL Server

6:382016 certifications, you can plot your path

6:41and begin your journey towards acquiring them.

6:44Good luck, my friends.

6:45I hope this has been informative for you,

6:47and I'd like to thank you for viewing.