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.

Developing SQL Server 2014 Databases

This Microsoft SQL Server training course with Garth Schulte covers Microsoft’s SQL Server database server, including how to design, implement, optimize, and troubleshoot SQL Server 2014 database and programming objects....
This Microsoft SQL Server training course with Garth Schulte covers Microsoft’s SQL Server database server, including how to design, implement, optimize, and troubleshoot SQL Server 2014 database and programming objects.

70-464 is the first exam for the SQL Server MCSE: Data Platform certification (retiring March 31, 2017, being replaced by the MCSE: Data Management and Analytics certification). This SQL server training will help you prepare for the 70-464 exam and beyond by covering Microsoft's exam objectives.

The 70-464 exam and this course cover the following general topic areas: Implementing database objects; implementing programming objects; designing database objects; and optimizing and troubleshooting queries. Let's BEGIN!

Note: An instructional PDF has been added to the Supplemental Files for your use within the Virtual Lab.

Recommended Experience
  • 2+ years of experience working with SQL Server
  • Familiarity with writing T-SQL statements
  • MCSA: SQL Server 2012
Recommended Equipment
  • Microsoft SQL Server 2014
Related Certifications
  • MCSE: SQL Server 2014 Data Platform (retiring March 31, 2017)
  • MCSE: Data Management and Analytics (replacing MCSE: Data Platform)
Related Job Functions
  • Database designers
  • Database developers
  • Database administrators
  • Database wizardry
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-464: Developing SQL Server 2014 Databases (16 min)
2. Tables (16 min)
3. Demo: Working with Tables (16 min)
4. Data Types (14 min)
5. Demo: Working with Data Types (16 min)
6. Data Integrity (10 min)
7. Demo: Domain Data Integrity (18 min)
8. Demo: Entity and Referential Data Integrity (13 min)
9. Security (13 min)
10. Demo: Implementing Security (28 min)
11. Indexes (23 min)
12. Demo: Working with Indexes (29 min)
13. Demo: Maintaining Indexes (28 min)
14. Views (10 min)
15. Demo: Working with Views (10 min)
16. Stored Procedures (13 min)
17. Demo: Working with Stored Procedures (26 min)
18. User-defined Functions (12 min)
19. Demo: Working with User-defined Functions (19 min)
20. In-Memory Features (10 min)
21. Demo: In-Memory OLTP (21 min)
22. Data Files (13 min)
23. Demo: BLOB Storage (12 min)
24. Demo: Table Partitioning (15 min)
25. XML Data (16 min)
26. Demo: Working with XML Data (24 min)
27. Query Tuning (21 min)
28. Demo: Query Tuning (24 min)
29. Concurrency (21 min)
30. Demo: Concurrency (23 min)
31. Automation (12 min)
32. Demo: Automation (18 min)
33. Managed Code (10 min)
34. Demo: Working with Managed Code (18 min)
35. Monitoring and Troubleshooting (12 min)
36. Demo: Monitoring and Troubleshooting (16 min)

Introduction to 70-464: Developing SQL Server 2014 Databases

00:00:00

Introduction to 70-464, Developing SQL Server 2014 Databases. Welcome, my friends, to 70-464. In this introductory Nugget, we're going to get up to speed on what SQL Server 2014 is all about, the additions, components, and tools that it comes with. We'll also talk about 70-464 and what the target audience is, what Microsoft is going to expect from us as far as the exam goes, and we'll also talk about MCSE Data Platform Certification.

00:00:23

We'll finish it up with a look at the Virtual Nugget Lab, which is the environment we're going to be working in for all of our demonstrations and examples. So we'll get familiar with that machine, which is just a single database server that's going to be hosting a couple of SQL Server 2014 instances for us to work with.

00:00:37

Let's get started here with a look at what SQL Server 2014 is all about. The latest version of SQL Server takes everything to the next level-- performance, scalability, high availability, and reliability. And it builds on what SQL Server 2012 started with cloud integration.

00:00:52

And that's really the theme here for 2014, if there was one, would be more cloud integration, as well as in memory capabilities, performance. So cloud integration and performance are the big ones. And really, 2014 caters to businesses of all shapes and sizes, small up to enterprise, and all shapes of data, tiny to big.

00:01:11

So it's a lot more diverse than its predecessor, because it's giving us a lot more ways to design our infrastructure in today's world, whether that's on premise, private cloud, public cloud, or a hybrid approach. So creating solutions that span offices, data centers, the globe, or the cloud here, with Microsoft cloud OS, Windows Azure, just got a whole lot easier.

00:01:30

What would an introduction to SQL Server 2014 be without a top 10 list, right? So let's go over some of the top 10 awesome new features and enhancements here to the product. First up, as I mentioned, there is a lot more Windows Azure integration. Just to give you a couple of really cool features here, we can now store our data in log files up in Azure.

00:01:47

So we can move our storage into the cloud. We can also deploy a database directly to an Azure virtual machine, the infrastructure as a service offering up there in Windows Azure. So that's pretty cool for a management studio. This will make it very easy for us to migrate our on-prem databases up to the cloud.

00:02:04

We can also deploy our databases up to their platform as a service offering, which is known as Azure SQL Database. We can also directly back up our databases to the Azure BLOB storage service. So, there's lots of cloud integration here in SQL Server 2014.

00:02:19

We also get some AlwaysOn enhancements, more AlwaysOn is a good thing. This was the big, high availability feature in SQL Server 2012 that allowed us to create replicas of our main database and do things like, again, high availability, so we could do failover.

00:02:32

But we could also offload processes and take the heat off of our main database. So we could do backups on replicas, ETL processes, reporting processes, all that stuff on these replicas, which, again, could span the globe. Well, now what we get with AlwaysOn is hybrid high availability.

00:02:49

Because now we can store replicas-- you guessed it-- in Azure. So we have an Add Azure replica wizard that makes it very easy for us to do that. We can also now have up to eight replicas rather than four. We've got new DMVs for troubleshooting always on.

00:03:03

And there's a bunch of other features as well. But those are the big ones. Backup enhancements-- as I mentioned, one of the big features here is the ability to backup to a URL, which means we can easily back up to the Azure BLOB storage service. But another really big feature that's been requested as long as I can remember here in SQL Server is backup encryption.

00:03:19

We now have native support to back up our at-rest data-- very cool. We get some security enhancements here. More separation of duty between system admins and database admins means database admins have more power and control over security and access. And there's also a handful of new database and server-level permissions.

00:03:37

The business intelligence tools-- we still have SQL Server Data Tools, which is our integrated development environment for working with integration services, analysis services, reporting services, comes with some nice designers and components for working with those services.

00:03:50

But Microsoft is also providing us, as a separate tool outside of SQL here-- you can download it from their site-- something known as SSDTBI, which is really just more-- more projects, more templates, more tools to work with there. And it's really targeted as kind of an all-in-one tool for working with all of those services.

00:04:10

But we also get something known as Power BI for Office 365, which integrates tools like Power Pivot, Power View, Power Query, and Power Map right into their office products. So users can now create some pretty powerful data navigation and visualizations from these tools.

00:04:26

Column store indexes have been enhanced. So this is another huge performance feature in memory technology in SQL Server 2012 that allowed us to dramatically improve the performance of our data warehouse style queries. But the original implementation meant that if we put a column store index on a table, that table was read only.

00:04:45

And in order to get data in that table, we would have to drop the column store index, reload the data, then recreate the column store index, which was more work for us to do. So in 2014, that restriction has been removed. We can now update tables that have a column store index on them-- pretty cool.

00:05:01

Another highly talked about feature here is the Resource Governor for I/O. This is really nice, because disk I/O is easily the most precious resource of a database, right? And how often have you seen a large query or a rogue query take over the system, suck down all the resources, and make it painful for all the other queries to do their job?

00:05:19

Happens all too often, right? Well, with the Resource Governor for I/O, we can now put those potentially devastating queries into their own resource pool and limit the amount of I/O per volume they're allowed to use. And it does so without monopolizing the system.

00:05:33

So large queries can still run, but it just won't affect other queries and the entire system as a whole. Another nice performance improvement here is delayed durability. Is a cool option, especially if you have issues with transaction log performance.

00:05:46

So in SQL Server, changes to data are first written to a log, the transaction log. That's known as write ahead logging. Control isn't returned back to the application until the log has been written to disk, a process known as hardening. So delayed durability allows us to return control back to the application before the log is hardened.

00:06:05

So this can dramatically speed up transactions, again, especially if you have issues with log performance. Buffer pool extension, another performance enhancement-- so SQL Server 2014 gives us a new solid state integration capability that lets us expand the buffer pool to SSD.

00:06:20

So this is going to be good for those systems that have maxed out their memory, and it can give us some pretty big performance gains for those read-heavy OLTP workloads. We saved the best for last here-- In-Memory OLTP, what was known as Project Hekaton.

00:06:32

This is easily the most exciting and talked-about feature in SQL Server 2014. It allows us to take our disk-based tables and move them into memory, where they live entirely in memory. They're accessed from memory. And we can even create what are known as natively compiled store procedures to access them for the ultimate in performance.

00:06:48

And this isn't something that was just bolted on to SQL Server. This was built from the ground up. Disk-based tables use locking and pessimistic concurrency. In-Memory tables uses optimistic concurrency. They're lock-free. So not only do they live in memory, but we have a lot less contention when it comes to them.

00:07:05

And this is going to result up to a 30-times performance improvement over disk-based tables-- pretty incredible feature that alone could be a reason to upgrade to SQL Server 2014. We also get a memory optimization adviser, which makes it very easy for us to migrate our disk-based tables to memory optimized tables.

00:07:22

Let's get up to speed on the many flavors of SQL Server 2014. And they're broken out across what are known as principal editions and special editions. So the principal editions are what we're going to be using for most of our production environment type work.

00:07:36

For instance, here in the principal side, we've got Enterprise Edition, which is really the premier edition, highest level of scalability, reliability, high availability. You get all the features, all the bells and whistles, and it's really, again, meant for data-center style, enterprise-level workloads, and mission critical apps.

00:07:51

On the standard side, this is meant for departmental-style databases, small to medium sized businesses. You get basic data management and business intelligence capabilities built right in. And then we have Business Intelligence Edition, which is really Standard Edition with all the BI capabilities built into it.

00:08:08

On the special editions side, you've got the Web Edition here, which is really aimed at web providers. It gives them a cost-effective solution here for hosting internet-facing websites and web-based applications. Developer Edition-- this is what we're going to be using inside of the Virtual Nugget Lab for all of our demonstrations.

00:08:24

But this is essentially Enterprise Edition without a license four production. So it's really meant for developers to build and test applications and really anybody. It's cheap. It's $50. You can get this for $50. You essentially get Enterprise Edition to learn, play, build, and test applications on.

00:08:42

Finally, we have Express Edition, which is free. Can't argue with free. But it doesn't come with all the bells and whistles and features like you see in all these other editions. But still, it's a great entry-level database for learning and building small applications on.

00:08:54

Let's take a quick run-through of all the components and tools in SQL Server 2014 here just to get up to speed on what we've got. So yes, we've still got the database engine, because without that, we wouldn't be able to do anything, right? That's the core service here for everything SQL Server.

00:09:08

We've got integration services, otherwise known as SSIS. This is just a great tool. It's our ETL tool, extract transform load for orchestrating data flows between systems. We also have analysis services, otherwise known as SSAS. This is our OLAP engine.

00:09:24

This is what we can do to create multi-dimensional cubes and data-mining applications. Master data services here, MDS, is a great way to manage master and reference data. And this is just a great tool all around, because rather than have 20 databases where you maintain a list of cities or states or zip codes or countries, now you can have one centralized list that you can access from all those databases.

00:09:46

So it's a nice way to keep things consistent rather than manage those same lists in multiple databases. The last of our components here is data quality services, another really nice one for keeping our data consistent and clean. So this is a knowledge-driven product that allows you to do things like correct, enrich, standardize, and deduplicate your data.

00:10:05

On the tools front, SQL Server Management Studio is still going to be our tool of choice for managing and working with many of these services, primarily working with the database engine. So that's where we'll be spending most of our time throughout all of our SQL Server courses.

00:10:19

We also have SQL Server Data Tools, as I mentioned. This is really Visual Studio for SQL Server. It's an all-in-one development tool that gives us a nice integrated development environment for working with all of these services over here, integration services, reporting services, and analysis services.

00:10:32

We also have SQL Server Configuration Manager, which is just a nice, centralized tool for managing all of SQL Server Services, as well as some network configuration options. SQL Server Profiler is just a great tool for getting insight into everything that's hitting our SQL Server.

00:10:48

So it's a monitor and tracing tool. It's going to help a lot when you're developing or troubleshooting your databases. We also have the Database Engine Tuning Advisor, which is our performance tool to help us recommend indexes based on workloads that we're going to provide, those workloads we're going to capture in SQL Server Profiler.

00:11:04

And then the Memory Optimization Advisor, as I mentioned, is our tool for migrating our disk-based tables to memory-optimized tables. Let's dig into 70-464. So this is the exam that targets database developers. Who are database developers? Well, we're the ones that are responsible for designing and building the physical database objects, encoding up these programming objects.

00:11:24

So we're the construction workers and the structural engineers of the database. One thing I would advise doing before even starting is getting familiar with the exam objectives so you know what's going to be expected from you as far as the exam goes.

00:11:36

This 70-464 exam is broken across four categories here-- implementing database objects, implementing programming objects is over half the exam. So knowing how to work with and actually physically build-- hence the implement side here-- your tables, your views, indexes, stored procedures, user-defined functions, so on, and so forth, is over 50% of the exam.

00:11:53

So make sure you're very comfortable with Transact SQL. Also designing database objects is a quarter of the exam. So designing your tables, designing stored procedures, views, indexing strategies, that kind of thing. And then a quarter of the exam is also here for optimizing and troubleshooting queries, understanding how to read execution plans, how to optimize your indexes, how to monitor and troubleshoot using DMVs as well as the profiler and some other tools here that we'll be looking at.

00:12:18

Head over to this URL for more information and to see all those exam objectives. I'll head over there in one second, just because I want to point out a few things on that page. Now as far as the certification goes, 70-464 is a credit towards the MCSE Data Platform Certification, which requires the 464 exam and the 465 336 exam.

00:12:36

The MCSA is a prerequisite to this. So you'll need 70-461 through 463 on SQL Server 2012. The real big thing to mention, the two big changes that were made when SQL Server 2014 came out as far as certifications goes, is that the MCSA is not getting updated to SQL Server 2014.

00:12:55

So that's going to stay on SQL Server 2012. That is the 70-461, 462, and 463 exams. And those probably won't change until the next version of SQL Server comes out, whenever that may be. And also, SQL 2014 questions were added to Data Platform as well as the business intelligence.

00:13:11

So 464, 465, 466, and 467 all have SQL Server 2014 questions added to them. So let me take you over to the exam page real quick just to show you a couple of things. So here it is. The first thing I want to show here is down here underneath Skills Measured is where you can find all the objectives broken out by category.

00:13:27

So make sure you check those out. But the second one here is this little URL right above those that says, content covering SQL Server 2014. So click on that. Open up the PDF. And you can see what has been added for SQL Server 2014, all this stuff here that we're going to be covering and beyond in this course.

00:13:45

Let's finish this Nugget up by getting familiar with the Virtual Nugget Lab. So the Virtual Nugget Lab is the environment we're going to be using for all of our demonstrations and coding examples. It consists of one database server known as SQL Nugget Lab.

00:13:55

That database server is running Windows Server 2012 R2 as the operating system. And I've installed two instances of SQL Server 2014 Developer Edition. That way we get access to all the bells and whistles if we need them. Those two instances our DEV and PROD.

00:14:09

And right now, DEV has AdventureWorks 2014 and NuggetLab DB ready to go. So AdventureWorks 2014 is a sample database for SQL Server 2014 that will give us an opportunity to reference some existing structures and schemas and examples. And NuggetLab DB right now is empty.

00:14:24

It's our own database that we're going to be building up and creating our own examples with as we go. We've got an N drive here to host all of our NuggetLab files. So we'll have a directory per demonstration Nugget that will contain the queries that pertain to that Nugget.

00:14:36

And then we've got an S drive where we'll be hosting all of our SQL Server data and log files and backups and such. I should also mention that these NuggetLab files will be available upon course completion on the Course Page, as well as the Virtual NuggetLab itself.

00:14:50

So you can get access to this entire environment, also accessible on the Course Page when it's complete. And the nice thing about that is the environment contains all of this ready to go. You just launch your browser and start getting in and playing.

00:15:00

You don't have to install this on your own equipment, download, and get the NuggetLab files set up. It's all there and ready to go as you see it here as we're going to be working on it. Now let me get you familiar with the actual environment. I've got it open and ready to go here.

00:15:12

So here it is. Here's our SQL NuggetLab machine. If we head into the file system here, we're going to see there's our N drive. Here's our NuggetLab directory with all of our directors, one broken our per Nugget. And at the bottom here, I also included the backup for the AdventureWorks 2014 database.

00:15:25

So if you download the NuggetLab files, you'll have that ready to go for you. And then we've also got a solution that wraps all these projects together. I'll show you that here shortly. And then on our S drive is where we have both of our installations.

00:15:36

There's our DEV, and there's our PROD. So let's bring up SQL Server Management Studio. And let's just go ahead and connect to our DEV instance. Let's also get a connection to our PROD instance here. And they're there. So there's both of our instances.

00:15:49

Here we have the AdventureWorks 2014 ready to go, as well as NuggetLab DB. I'm also going to go ahead and open that solution. So let's open up Projects slash Solution here, go on into NuggetLab, scroll down to the bottom, hit that Solution file, and there it is.

00:16:02

So a Solution is just a container for projects. And Projects are a nice container to store all of your queries. So this will keep us nice and organized here and give you an easy way to reference the examples per Nugget. And that's the virtual Nugget Lab.

00:16:14

We'll be spending plenty of time in this environment getting familiar with SQL Server 2014. In this CBT Nugget, we took an introduction to 70-464 and SQL Server 2014. The goal of this Nugget was to get you up to speed on the latest and greatest SQL Server 2014 has to offer, cover the additions, components, and tools that come with it, talk a little bit about 70-464, and the MCSE Data Platform and get you familiar with the Virtual Nugget Lab.

00:16:37

I hope you're as pumped as I am for this course. We're going to have fun. We're going to learn a lot, and we're going to do some pretty cool things along the way. I hope this has been informative for you, and I'd like to thank you for viewing.

Tables

Demo: Working with Tables

Data Types

Demo: Working with Data Types

Data Integrity

Demo: Domain Data Integrity

Demo: Entity and Referential Data Integrity

Security

Demo: Implementing Security

Indexes

Demo: Working with Indexes

Demo: Maintaining Indexes

Views

Demo: Working with Views

Stored Procedures

Demo: Working with Stored Procedures

User-defined Functions

Demo: Working with User-defined Functions

In-Memory Features

Demo: In-Memory OLTP

Data Files

Demo: BLOB Storage

Demo: Table Partitioning

XML Data

Demo: Working with XML Data

Query Tuning

Demo: Query Tuning

Concurrency

Demo: Concurrency

Automation

Demo: Automation

Managed Code

Demo: Working with Managed Code

Monitoring and Troubleshooting

Demo: Monitoring and Troubleshooting

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 11 hrs 36 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