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

0:00Welcome, my friends, to our many SQL Server 2016 courses here at CBT Nuggets. In this Nugget, we're going to cover some of those new features that SQL Server 2016 offers that you'll be learning about throughout these courses. We'll also get up to speed on the tools and components that you'll be working with. And we'll talk about the virtual lab, our hands on lab environment that you can use to follow along with me throughout these courses. Let's jump in. There's no better place to start than to talk about some of the new exciting features SQL Server 2016 has to offer.

0:30Now, I just handpicked a few of my favorites out of the database engine component, but believe me, there are a ton of new features in SQL Server 2016 spanning all of the components and tools that SQL has to offer. And we'll be covering a good majority of them throughout these six SQL Server exams. I saved the best for first, because this is easily one of the coolest features to come out of SQL Server 2016. It has so many good uses. System version temporal tables we enable with the flick of a switch. And then SQL Server will track the history, the lifetime, of a record, all data modifications against it.

1:06This gives us the ability to see what a record looked like at any point in time. Another highly anticipated feature is the Query Store. This will keep track of our query executions over time and all the performance metrics around them, including execution plans, which will give us much better insight when troubleshooting and optimizing our queries. Next up is one of many new security features. We have things like always encrypted, dynamic data masking. But this one, oh, this one is the one we've been asking for a long time.

1:40Row-level security, it's finally built into the database engine, meaning we don't need to come up with our own custom solutions to implement row-level security. If you're into the cloud, this next feature is for you. The stretch database feature allows SQL Server to transparently migrate cold data up into Microsoft Azure. And the best part is that data is still accessible. You can still query it just as if it was on-premise. And this works with cold data. And so if you have tables with both cold and hot data, you can actually write a filter function to define what cold data is.

2:13Pretty cool and another feature that's really easy to use. Finally, we have in-memory OLTP. And I know, I know, this isn't a new feature. But it's one that continues to evolve. And it's still one of the most important features when it comes to performance in SQL Server. Many of the limitations that annoyed us have been removed, and it now supports tables up to 2 terabytes, up from 256 gigs. Here's a quick look at all of the SQL Server's major components and tools, all of which are covered in the SQL Server 2016 exams at some point.

2:44On the component side, we have the database engine. Of course, that is the core component that makes everything tick in SQL Server. We also have SQL Server Analysis Services. This is our solution for building OLAP and multi-dimensional data solutions. Next up is our reporting solution, SQL Server Reporting Services. We also have our ETL solution-- that's extract transform and load-- in SQL Server Integration Services. And finally, we can model and manage master data with MDS, Master Data Services.

3:15On the tool side, two big ones here. One is our administration management and querying tool, SQL Server Management Studio. We will be spending a lot of time in there. And the other is our development tool in SQL Server, Data Tools. This is actually the replacement for the old Business Intelligence Development Studio, or BIDS for short. When it comes to SQL Server 2016's additions, nothing has changed much. We have the same five flavors that we've had for a while now in SQL Server, starting at the very top with Enterprise Edition. This is the top dog.

3:45No scalability limits and all the bells and whistles you could possibly ask for. Next in line is Standard Edition. And this is the one that most folks generally go with unless they have crazy scalability requirements and need access to all those high end features. Next up we have Developer Edition. And this is essentially Enterprise Edition, just not licensed for production use. So this is good for application developers or QA in testing environments. Web Edition, as the name implies, is designed to host databases that store web application based data.

4:21So a lot of the features have been removed that don't really apply to that kind of workload. Finally, we have the free edition, SQL Server Express. Anybody can download and play with this. And it's really aimed at the student and the hobbyist. The last thing we're going to touch on here is our virtual lab, your hands-on environment, again, for following along and getting that crucial hands-on experience with SQL Server. We call these Nugget Level Virtual Labs because every single Nugget that has a lab has it tailored to that specific Nugget. And by the way, you can tell when a Nugget has a lab when you see this icon in the upper left-hand corner of the Intro slide.

4:56You can then launch that lab from the right-hand side of the course page. So follow along with me click by click, query by query, and also use the lab as your own personal sandbox. You can learn without having to worry about messing up a production environment or your own environment. In this CBT Nugget we took an introduction to SQL Server 2016. Thanks for joining us here at CBT Nuggets. And thanks for taking this journey with me through Microsoft's incredible database platform. I hope this has been informative for you, and I'd like to thank you for viewing.

0:00SQL Server 2016 brings with it brand new certifications. Woo, hoo. Something we haven't seen since SQL Server 2012. And Microsoft has done a great job of streamlining and simplifying the structure and requirements for these new certifications. Let's jump in and break them all down. Here's the big picture. At the very top, we have what's known as the MTA. That's the Microsoft Technology Associate. All MTA certifications-- and there's three of them by the way. There's one for database, one for developer, one for IT infrastructure. They are targeting the absolute beginning, essentially the fundamentals of IT, as Microsoft calls it.

0:34And this database one is not new, by the way. This has been around for 7 or 8 years. I included it because if you have absolutely no SQL Server or prior database experience, this is a great starting point to get you up to speed. In order to acquire the MTA database certification, you will need to pass a single exam, 98-364 Database Fundamentals. This course does a good job of giving you a light introduction to all areas and all job responsibilities in SQL Server. It will ensure that you understand your core relational database concepts.

1:05It will give you a light introduction to Transact SQL and writing queries, as well as database development and database administration. So again, the MTA is recommended if you're brand new to this SQL Server stuff. It's not required. You do not need to acquire this certification in order to attempt our next certification, which are the Microsoft Certified Solution Associate Certification. And that's right you're seeing that correctly. We now have three of them in SQL Server 2016. And this is what I mean by streamlined and simplified. Microsoft did a great job of breaking these down, targeting an MCSA per job role.

1:40And the reason this is great is because if you were around for SQL Server 2012 or 2014, you'll know that the MCSA-- there was only one of them and it required three exams, which was 70-461, 462, and 463. And those three exams were essentially three different job roles. So you really needed a broad set of skills in order to even attempt to acquire that certification. So if you are strictly a database developer or strictly a database administrator or strictly a BI developer, you can now focus on the MCSA that targets your job description.

2:12And if you're a multi-faceted SQL Server ninja, you can acquire multiple or even all three of these to prove your broad range of knowledge. Each one of these certifications has two exams associated with them. For example, here is the MCSA for database development. This certification targets developers who spend the majority of their time coding up our database objects and writing the queries that our database. The first exam is 70-761. This is really all about Transact SQL and ensuring that you can write basic queries, advance queries, and again a light introduction to coding up those database objects, like views, stored procedures, and user defined functions.

2:50The next exam is 70-762. This exam is focused on developing SQL databases, really designing, implementing, managing, and optimizing our database and the objects within. The next MCSA is focused entirely on database administration. This one again consists of two exams. The first one is 70-764. This one's all about administering your SQL servers-- so security, backup and restoration, managing and monitoring, as well as high availability and disaster recovery. The second one, 70-765 is all around provisioning SQL Server databases.

3:23This one focuses on planning and deploying your SQL Server instances, both in Microsoft Azure, Microsoft's cloud platform, as well as on-premise. It also focuses on managing and monitoring your instances and the databases within, as well as managing and maintaining storage. The final MCSA is focused entirely on business intelligence, designing and implementing an infrastructure to support BI. The first exam here, 70-767, is your data warehousing exam, knowing how to design, implement, and maintain a data warehouse, load that data warehouse with your operational data, using SSIS, SQL Server Integration Services, which you'll use to build that ETL process, and finally improve the quality and accuracy of that data, using data quality services and master data services.

4:09The second exam, 70-768, is your SQL Server Analysis Services exam. This one is focused on configuring, managing, and maintaining SSAS, as well as designing multi-dimensional data models and writing the queries that target those models using multi-dimensional expressions-- that's MDX-- as well as data analysis expressions, DAX. And that's that for your new MCSA certifications in SQL Server 2016. Again, very cool that Microsoft streamlined these to target specific SQL Server job roles. And that brings us to a brand new MCSE certification.

4:41That's Microsoft's Certified Solutions Expert. We only have one. Again, back in Server 2012, 2014, we had two, and they both required multiple exams. And they were fairly difficult to acquire. Not anymore. Now we only need to pass a single exam from a big list of electives. And you can then easily acquire your MCSE. You also you need to have at least one MCSA. So that is a prerequisite to the MCSE. So assuming you've acquired one of these three SQL Server 2016 MCSA certifications, or if you already have a SQL Server 2012/2014 MCSA-- yes, those still count-- then all you will need to do is take one of these exams on this list of electives and boom, you'll be an MCSE in data management and analytics.

5:23Microsoft has also stated that this list of electives will change over time. New exams will get added. Older exams-- like all these 400s, which are SQL Server 2012 and 2014 exams-- will eventually go away. Another big change made to the MCSE certification is that it never expires. And we have the opportunity to acquire it every year to show that we're keeping up with these technologies. Here's how it works. Let's say that we acquired one of these SQL Server 2016 certifications, say administration, for example. Then we came over to this list of electives.

5:53And we took one of these. Let's take 70-773 as an example here. And we passed it. Now in our transcript, it's going to show that we're an MCSE for the current year. Let's say the current year is 2017. Now, let's say 2018 hits, we have an opportunity to acquire the MCSE once again. We cannot take this exam any more, because we've already taken it. So we'll have to take another exam on this list of electives. And if we did that and passed it, then it would show up on our transcript that we are now a MCSE in the year 2018. Pretty cool, right?

6:24Our Microsoft transcript now has much more value for ourselves and employers. They can now gauge someone's overall skill set. They can see what they specialize in and also see how they've kept up with technology over the years. Now that you're acquainted with the brand new SQL Server 2016 certifications, you can plot your path and begin your journey towards acquiring them. Good luck, my friends. I hope this has been informative for you, and I'd like to thank you for viewing.