0345 4506120

Microsoft SQL Server 2014 Update

Microsoft SQL Server 2014 enhances the previous product in its use of In-Memory OLTP storage, enhanced columnstore, new hybrid cloud scenarios, enhanced security in server and database access, and backup encryption.

This workshop-style course is designed for IT professionals who are interested in learning how to implement these new features. It is suitable for those experienced with Microsoft SQL Server 2012 on an on-going DBA and developer level.

We don't currently have any courses listed for Microsoft SQL Server 2014 Update, would you like to view all courses for Microsoft SQL Server?

Learning Objectives

After completing this course, students will be able to describe key features and capabilities of SQL Server 2014; leverage new and enhanced features to develop database in SQL Server 2014; use in-memory database capabilities in SQL Server 2014; manage SQL Server 2014 by using dynamic management views and PowerShell; implement security in SQL Server 2014; implement high-availability and data recovery techniques in SQL Server 2014; use SQL Server 2014 Integration Services; use SQL Server 2014 Data Quality Services; use SQL Server 2014 Master Data Services; manage SQL Server 2012 Agent and Jobs; implement a tabular data model in SQL Server 2014 Analysis Services; implement a Self-Service BI solution with Microsoft Excel 2013; describe key elements of cloud solutions for data; implement a database in Windows Azure SQL Database; implement a SQL Server database in a Windows Azure virtual machine; implement a Big Data solution with Windows Azure HDInsight and the Windows Azure Marketplace.

Pre-Requisites

  • Advanced knowledge of Microsoft SQL Server 2012 features such as columnstore indexes, new data types and Always-On.
  • Courses which match the profile above are : M6231, M6232
  • It would be preferable for delegates to also have passed the MCSE Data Platform or attended the M20465 course

Course Content

Module 1: In-Memory OLTP
This module will introduce In-Memory OLTP data structures and Natively-compiled stored procedures which have been added in Microsoft SQL Serevr 2014.

Sections

In-Memory OLTP tables

  • What are the benefits of moving to In-Memory OLTP?
  • Structuring the database files and filegroups
  • Adding the In-Memory OLTP tables
  • Review of data types available
  • Creating new index structures (HASH and RANGE)
  • Limitations on the TSQL code that can be executed
  • Managing concurrency

Natively-Compiled Stored Procedures

  • What are the benefits of moving to Natively-Compiled Stored Procedures?
  • Where are they appropriate / possible?
  • Creating a natively-compiled stored procedure
  • Executing the stored procedures
  • Discussion on Inter-Op stored procedures

Demonstrations

  • Creating a database to incorporate In-Memory OLTP
  • Creating the In-Memory table including indexes
  • Running concurrent updates against the table and the types of error that may occur
  • Creating a natively-compiled stored procedure and showing the execution speed advantages
  • Using In-Memory OLTP tables from within standard (Inter-Op) stored procedures

Labs
Working to a scenario you will decide how to design the database structures to can benefits from the In-Memory table structures and natively-compiled stored procedures. You will then implement the database as per you design and test against a Microsoft SQL Server 2012 equivalent design.

  • Creating a database to incorporate In-Memory OLTP
  • Creating the In-Memory table including indexes
  • Creating a natively-compiled stored procedure
  • Test the design against a Microsoft SQL Server 2012 equivalent for execution speed.

Module 2: Columnstore Indexes
This module will introduce the new important changes to the Columnstore Indexes, first introduced in Microsoft SQL Server 2012. The major change is that new structures are clustered and allow updates directluy against the tables.

Sections

Clustered Columnstore Indexes

  • Review the benefits and limitations of Columnstore Indexes within Microsoft SQL Server 2012.
  • Comparing non-clustered and clustered Columnstore Indexes
  • Creating a clustered Columnstore index
  • Test the clustered Columnstore Index against a table with a heap and a non-clustered Columnstore Index

Demonstrations

  • Create a Microsoft SQL Server 2012 non-clustered Columnstore Index
  • Create a Microsoft SQL Server 2014 clustered Columnstore Index
  • Review the space requirements for each
  • Show DML code running against each

Labs
Working to a prepared design you will create a clustered columnstore index and test.

  • Create a clustered Columnstore index on a new table
  • Create a clustered Columnstore index on an existing table (with data present)
  • Run updates against the Columnstore indexes
  • Test the design against a Microsoft SQL Server 2012 equivalent for execution speed.

Module 3: Security
This module will cover the updates to security on Microsoft SQL Server 2014 and will include changes to permissions available at the server and database levels. Also included in the module is a discussion of the need to encrypt backup databases and the new feature to allow this natively with requiring TDE.

Sections

New Permissions

  • Explore the three new server level permissions
  • Explore the new database level permissions

Backup Encryption

  • Review creating key and certificates from previous editions of Microsoft SQL Server ready to provide these to the encryption process
  • Show how the backup encryption works

Demonstrations

  • Create a new auditor who needs to read from all the tables from all the databases hosted on the server with the minimum of effort.
  • Create a backups of a database with and without encryption

Labs
Working to a scenario you will decide how to implement for three different users: an internal auditor, and external HR auditor and a DBA/Developer specifically employed to maintain the HR system.

  • Create a database for the HR department
  • Implement the logins and database roles
  • Implement the permissions
  • Test the permissions

Module 4: SQL Engine Internals
This module will cover the update Resource Governor and a new feature Buffer Pool Extensions.

Sections

Resource Governor

  • Review the Resource Governor options from Microsoft SQL Server 2008 and Microsoft SQL Server 2012
  • The Resource governor now enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within a resource pool.

Buffer Pool Extensions

  • Introduce the Buffer Pool Extension which provides the seamless integration of solid-state drives (SSD) as a nonvolatile random access memory (NvRAM) extension to the Database Engine buffer pool to significantly improve I/O throughput.

Demonstrations

  • Create and enable resource governors to control the access to resources to separate competing applications
  • Create and enable buffer pool extensions to allow a SSD to hold database engine buffer pages

Labs
Working to a scenario control access to resources on the server so that the critically important database has the resources it needs whilst a less important database application will have its usage reduced.

  • Create a database for the applications
  • Test the applications before implementing the resources governor
  • Implement the resource governor
  • Restest the applications

Module 5: Cloud Integration
This module will cover the updates within Microsoft SQL Server 2014 look at making use of the Microsoft Azure platform. This includes holding data files within Windows Azure, backing up to Windows Azure using normal backup routines and the new Managed Backup and lastly using a Windows Azure VM as an Always-On failover partner.

Sections

Hybrid Cloud

  • Review how Microsoft SQL Server 2014 can use the Windows Azure blob storage as a storage host for database files
  • Discuss the possibilities and issues that may arise from such a design

Cloud Backup

  • Show how to use the Windows Azure blob storage to provide an off-site backup location for your databases
  • Introduce SQL Server Managed Backup for Windows Azure to allow SQL Server to manage the backup up of databases and log as either the database or instance level
  • Discuss the possibility of adding a failover secondary within a Windows Azure SQL VM to provide GeoDR

Demonstrations

  • Create certificates for the Windows Azure container
  • Backup database using TSQL and UI fro Windows Azure URL
  • Enabling SQL Server Managed Backup for Windows Azure

Exams & Certification

There are no exams directly associated with this course

Privacy Notice

In order to provide you with the service requested we will need to retain and use your contact information in accordance with our Privacy Notice. If you choose to provide us with this information you explicitly consent to us using the information as necessary to provide the request service to you. If you do not agree please do not proceed to request the service from us.

Marketing Permissions

Would you like to receive our newsletter and other information on products and services which we think will be of interest to you by email. We will always treat your information with care and in accordance with our Privacy Notice. You are free to withdraw this permission at any time.

 

Our Customers Include