Learning Path: Sql: Up And Running With Sql Server 2016

Deep dive into administration, maintenance and automation

What you'll learn

Create user databases and how to administer it

Learn to improve query performance

Create a backup strategy and perform full and differential restores

Configure and use a database mail to send messages under server automation

Create full database backups and work with different actions

Install and configure ‘AlwaysOn’ availability groups

Create operations and notifications based on server options

Requirements

Basic knowledge of SQL Server

Some understanding of T-SQL would be helpful, but not mandatory

Description

Microsoft SQL Server 2016 is considered the biggest leap in the data platform history of the Microsoft, in the ongoing era of Big Data and data science. Compared to its predecessors, SQL Server 2016 offers developers a unique opportunity to leverage the advanced features and build applications that are robust, scalable, and easy to administer. If you’re an administrator, developer or a DBA and want to learn SOL along with maintaining and automating your DBMS, then go for this Learning Path.


Packt’s Video Learning Paths are a series of individual video products put together in a logical and stepwise manner such that each video builds on the skills learned in the video before it.


The highlights of this Learning Path are:

Work with the latest features of the SQL Server 2016 release

A comprehensive, example-filled tutorial that helps you to master in SQL 2016


Let’s take a quick look at your learning journey. This Learning Path will teach you basic as well as advanced administration tasks for handling, managing, and monitoring your SQL Server 2016 efficiently. After setting up and configuring your virtual machine for SQL Server 2016, you will implement various techniques to improve query performance, create indexes, and perform activity monitoring. Learn how to secure your SQL Server solution by implementing various authentication techniques.


Develop a backup strategy and carry out full database backups as well as differential database backups. Next, you will explore the different options for restoring your system logs, and how to restore your database’s state to a specified point in time. From there, you will move on to automating your server. You will learn to create tasks and jobs for your system, and also set up operations and notifications. You will also learn to configure alerts and database emails for sending messages to users. After that, you will dive right into high availability and disaster recovery, where you will database mirroring, log shipping, and availability groups. Finally, you will explore the different types of server and data replication and how they are used.


By the end of this Learning Path, you will become an expert in SQL Server 2016.


Meet Your Expert:

We have combined the best works of the following esteemed author to ensure that your learning journey is smooth:


Steve Jones has been working with SQL Server since 1991. He has worked with all versions of SQL Server as a developer and DBA, in a variety of industries and companies. Over the last 25 years, he been very pleased with the enhancements and growth of the platform. He feels that SQL Server is an outstanding database platform well-suited to a wide variety of needs and situations.


Steve is a graduate of the University of Virginia. He has been awarded the title of Microsoft Data Platform MVP from 2008 - 2017 for his many contributions to the SQL Server community. Steve holds an MCSE from NT 4.0, an MSITPro in SQL Server, and numerous other MCP certifications in SQL Server.


Steve regularly presents at SQL Saturday conferences and other technical events on career and technical topics, and enjoys meeting and interacting with the SQL Server community. He has been a presenter at //build/, the PASS Summit, Dev/IT Connections, VSLive, SQL Bits, SQL Intersection, and many user groups in the U.S.

Overview

Section 1: Getting Started with SQL Server 2016 Administration

Lecture 1 The Course Overview

Lecture 2 Requirements and Recommendations

Lecture 3 Installing SQL Server from the GUI

Lecture 4 Installing SQL Server from the Command Line

Lecture 5 Upgrading SQL Server

Lecture 6 Security Architecture

Lecture 7 Server Logins

Lecture 8 Built-in Server Roles

Lecture 9 User-Defined Server Roles

Lecture 10 Database Users

Lecture 11 Built-in Database Roles

Lecture 12 User-Defined Database Roles

Lecture 13 Object Level Permissions

Lecture 14 System Databases

Lecture 15 User Databases

Lecture 16 FileGroups

Lecture 17 Database Options

Lecture 18 Database Space

Lecture 19 Tables

Lecture 20 Views

Lecture 21 Stored Procedures

Lecture 22 User-Defined Functions

Lecture 23 Assemblies

Lecture 24 Durable Memory-Optimized Tables

Lecture 25 Non-Durable Memory-Optimized Tables

Lecture 26 Row Store Indexes

Lecture 27 Clustered Indexes

Lecture 28 Non-clustered Indexes

Lecture 29 Columnstore Indexes

Lecture 30 Nonclustered Columnstore Indexes

Lecture 31 Indexed Views

Section 2: SQL Server 2016 – Maintenance and Automation

Lecture 32 The Course Overview

Lecture 33 Backup Strategy

Lecture 34 Full Database Backups

Lecture 35 Differential Database Backups

Lecture 36 Transaction Log Backups

Lecture 37 Basic Database Restores

Lecture 38 Restoring Transaction Logs

Lecture 39 Restoring to a Point in Time

Lecture 40 SQL Server Agent

Lecture 41 SQL Agent Jobs

Lecture 42 Job Schedules

Lecture 43 Operators and Notifications

Lecture 44 Alerts

Lecture 45 Database Mail

Lecture 46 Clustering

Lecture 47 Availability Groups

Lecture 48 Database Mirroring

Lecture 49 Log Shipping

Lecture 50 Replication

This Learning Path is for administrators, developers, or DBAs with some knowledge of SQL Server concepts and who want to administer, maintain, and automate their DBMS.