SQL Server 2014 Administration New Features

Every new release of SQL Server brings a whole load of new features that an administrator can add to their arsenal of efficiency. This course takes a tour of the basic new features that were introduced, including backup, security, column store enhancements, Resource Governor, General Administration, Azure integration, extensions with SQL Server 2014 and more.
Course info
Rating
(181)
Level
Intermediate
Updated
Jul 30, 2014
Duration
4h 17m
Table of contents
Introduction
Backup Enhancement
Security Enhancements
Columnstore Index Enhancements
Online Database Operational Enhancements
Enhancements With Microsoft Azure
Buffer Pool Extension
Resource Governor IO Enhancements
Miscellaneous Enhancements
Description
Course info
Rating
(181)
Level
Intermediate
Updated
Jul 30, 2014
Duration
4h 17m
Description

Every new release of SQL Server brings a whole load of new features that an administrator can add to their arsenal of efficiency. This course takes a tour of the basic new features that were introduced, including backup, security, column store enhancements, Resource Governor, General Administration, Azure integration, extensions with SQL Server 2014 and more. To get the most out of this course, viewers should be familiar with SQL Server and database concepts.

About the author
About the author

Pinal Dave is a Pluralsight Developer Evangelist.

More from the author
Getting Started with MariaDB
Beginner
2h 23m
Nov 2, 2018
Monitoring MySQL with Performance Schema
Intermediate
1h 59m
Apr 28, 2017
More courses by Pinal Dave
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi! This is Pinal Dave, and welcome to the course of SQL Server 2014 Administration New Features. SQL Server is a foundation of Microsoft's comprehensive data platform. It delivers breakthrough performance for mission critical application using various advanced technologies. In SQL Server 2014 there are various new features introduced for performance, stability, and Cloud. SQL Server is also a platform for building, deploying, and managing solutions that span on-premises, as well as on Microsoft Azure. In this Introduction module, we are going to discuss outline of what we are going to cover in this course.

Backup Enhancement
Hi! This is Pinal Dave, and welcome to the module of Backup Enhancements with SQL Server 2014. Starting in SQL Server 2014, SQL Server has the ability to encrypt the data while creating a backup. By specifying the encryption _____, and a certificate, or a symmetric key when creating a backup, we can create an encrypted backup file. In this module, we will understand various backup enhancements with SQL 2014. Here is the outline. First, we'll understand backup scenario today. Right after that, we will understand what are the various prerequisites to implement enhancements introduced in SQL Server 2014. Right after that, we'll discuss backup encryption. Before we end this module, we will have a summary of various important concepts covered in this module.

Security Enhancements
Hi! This is Pinal Dave, and welcome to the module of Security Enhancements. With regard to security, SQL Server always has been a very robust product. However, in SQL Server 2014, there are a few new enhancements related to permissions, as well as encryption. We have covered database encryption in previous module. So, in this module, we are going to talk about various security related permissions. Security can be a very dry subject to understand. Hence, we'll create two different scenarios. Both of these scenarios are taken from real world experience of my own. With the help of demonstrations, we will understand how CONNECT ANY DATABASE permission works with ALL USER SECURABLE permission. Right after that, we'll talk about how impersonation can create big security loop in your entire strategy of securing your data. We will end this module with a quick summary on what we have just learned. Now, let's start with our very first scenario.

Columnstore Index Enhancements
Hi! This is Pinal Dave, and welcome to the module of Columnstore Index Enhancements. Columnstore indexes stores columns instead of rows, and are designed to speed up processing of data warehousing queries. Well, as of SQL Server 2014, columnstore index can now positively impact OLTP transactions as well. In this module, we are going to cover introduction to columnstore index, and we'll understand characteristics of columnstore indexes. Right after that, we'll talk about the difference between SQL Server 2012 and SQL Server 2014 columnstore indexes. There are definitely a few enhancements related to SQL Server 2014, and we'll understand them with the help of demonstration. Right after that, we'll talk about internals of columnstore index. I'm very confident that you'll appreciate the diagram and demonstration I have built for internals of columnstore index. Right after that, we'll summarize what we have learned in this module. Now, let's start with introduction.

Online Database Operational Enhancements
Hi! This is Pinal Dave, and welcome to the module of Online Database Operational Enhancements. Keeping data online and available is extremely important for any database. There are a few SQL Server operations when they are happening in SQL Server, data is not available for application. In this module, we will talk about those operations, and how we can overcome them with the help of SQL Server 2014 enhancements. First, we'll see what are the various problems we have with online operations in SQL Server 2012. Right after that, we'll understand what's new with Managed Lock Priorities. We will also cover various options related to Managed Lock Priority. Finally, we'll have summary.

Enhancements With Microsoft Azure
Hi! This is Pinal Dave, and welcome to the module of Enhancement with Microsoft Azure. SQL Server 2014 introduced managed backup to Windows Azure. Now, SQL Server can manage backup to Windows Azure at database level, as well as on instance level. In case of the SQL Server, it can run either on-premises, or in hosted environment, you can still take your backup to Microsoft Azure. This presents a very interesting scenario as your SQL Server can be running on virtual machine, or on-premises, but now your backup is safe on Microsoft Azure hosted environment. In this module, we are going to learn about how do we take backup today, and what are the different prerequisites you will need to take managed backup. Right after that, we will see how we can take backup to the Cloud. The most interesting part of this module will be where we take encrypted backup to Cloud. Right after that, we will see how we can take managed backup to Cloud based on various parameters available. We'll end this module with summary. We'll try to tell various small stories to describe various scenarios, which we are going to discuss in this module.

Buffer Pool Extension
Hi! This is Pinal Dave, and welcome to the module of SSD Buffer Pool Extensions. In SQL Server 2014, the buffer pool extension provides a seamless integration of solid state drive. This helps database engine buffer pool to significantly improve I/O throughput. In this module, we are going to discuss why and how buffer pool extension works, benefits of buffer pool extension, syntax, and internals with the help of page movement and eviction. We'll also discuss various configuration details before we sum up the entire module. Let's start with very essential question of why buffer pool extension?

Resource Governor IO Enhancements
Hi! This is Pinal Dave, and welcome to the module of Resource Governor IO Enhancements. Resource Governor enables us to manage SQL Server workloads and resources by specifying limit on resource consumption by incoming requests. The best part of Resource Governor is that we can limit consumption of various resources in real time with minimal impact on workloads executing on your server. In this module, we are going to talk about Resource Governor I/O, and we'll discuss why do we need that. We'll also quickly go over syntax, and see a demonstration explaining impact of Resource Governor I/O. Finally, we'll look at summary, and understand why Resource Governor I/O is critical in certain scenarios. Next, we'll see a quick story about need of Resource Governor, and its I/O control.

Miscellaneous Enhancements
Hi! This is Pinal Dave, and welcome to the module where we are going to discuss various Miscellaneous Enhancements of SQL Server 2014. Here is what we are going to cover in this module. First, we will talk about online index rebuilding operation, and how it impacts performance, as well as manageability for database administrator. Right after that, we'll talk about live plan for long running queries. When we are running any SQL Server query, at that time, it's not possible to see actual execution plan; however, with live plan we can do the same very easily. Next we'll talk about delayed durability, and do a quick test where we will see how delayed durability can positively impact performance. Following that, we will talk about new enhancements of cardinality estimation. Cardinality estimation algorithm is rewritten in SQL Server 2014, and it provides _____ position to SQL Server engines. And finally, we will see a quick demonstration where we will discuss in-memory OLTP optimization. Now, we'll start with our very first topic of online index rebuilding operations.