SQL Server 2016 Administration
4.0 (1 rating)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
10 students enrolled
Wishlisted Wishlist

Please confirm that you want to add SQL Server 2016 Administration to your Wishlist.

Add to Wishlist

SQL Server 2016 Administration

Database Administration from Zero to Hero
New
4.0 (1 rating)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
10 students enrolled
Created by John Hall
Last updated 8/2017
English
Current price: $12 Original price: $20 Discount: 40% off
4 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 2 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion

Training 5 or more people?

Get your team access to Udemy's top 2,000 courses anytime, anywhere.

Try Udemy for Business
What Will I Learn?
  • Install SQL Server, Create Always on groups, create a lab environment
  • Perform Windows Active Directory Install, Create DHCP Scopes.
  • Manage SQL Servers Via SSMS, Correctly configure windows firewall
View Curriculum
Requirements
  • You should know how to use Windows
  • PC with 4 cores and 8GB RAM or more to be able to run guests
Description

In this course you'll be taken from novice to experienced Database Administrator.

Learning about.

  • Creating a Lab environment.
  • Installing related services, Active Directory
  • High Availability Systems, including mirroring and always on clusters
  • SQL Server Security
  • Benefits of Powershell
  • Installing Windows Failover Cluster
  • Configuring Always On Cluster
  • Scans Vs Seeks
  • Top I/O and CPU queries

By the time you have completed this course you will have the skills to administrate SQL Server in corporate networks or your own startup.



Who is the target audience?
  • Developers, DevOps and New Database Administrators
  • Novice to Intermittent
Compare to Other SQL Server Courses
Curriculum For This Course
25 Lectures
01:53:47
+
Introduction
2 Lectures 05:00

In this area we'll cover the downloading of evaluation media for Windows Server and SQL Server

Downloading Media
01:08
+
Setting Up Your Lab
10 Lectures 47:01

In this section we'll cover he basic install of Windows Server.

Since this is a SQL Server training we won't be exploring all the options.

Installing Windows
02:31

If you haven't worked with Virtual Box before you should find this useful.

While I am using virtual box you might have another Virtual Machine software so feel free to skip over if you are using something else.

Virtual Box and Updates
02:37

This will be a basic Active Directory install so that we can use Active Directory with SQL Server later.

If you already have an AD in your lab or will not be using replication between server feel free to skip over this.

Active Directory Installation
03:39

Active Directory Configuration and DNS Configuration
08:07

Configuration of my lab is using the 172.16.1.x range of IP's you can use any range.

Please also note that I have disabled my virtual box DHCP on this network.

DHCP Server Configuration
02:20

Joining Member Servers to Windows Domain

Joining Active Directory
03:21

Installing SQL Server 2016 on Windows Server

Installing SQL Server
09:09

Installing SQL Management Studio
05:07

Configure Windows Firewall
04:07

Adding Sample databases, these will be used later for test.

Sample Databases
06:03
+
Administration
6 Lectures 36:44

Securing SQL Server from Man in the middle attacks by using SSL communication

Using SSL communication
06:52

SQL Server Logs
06:14

How to create new roles and assign permissions to them.

Security
05:59


How to list the cmdlets on a module

Powershell Cmdlets
03:20

Showing How a Database can be created and Deleted using Powershell.

Preview 06:52
+
High Availability
4 Lectures 16:21

Why Always On is better than SQL Mirroring.

Easier uses from applications, lower running costs from SQL Server.

High Availability Option
02:33

Installing windows failover clustering with PowerShell.

Here is the PowerShell script in case you want to run it yourself.


# Cluster nodes
$nodes = "server-sql1","server-sql2","WIN-MOH9H0KL2A1"
$cluster = "server-sql1","server-sql2"

# Install cluster node failover management
foreach($i in $nodes){
Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools –ComputerName $i
}

# Create new cluster
New-Cluster -Name SQLCLUSTER -Node $cluster -StaticAddress 172.16.1.200

Windows Cluster For Always On
04:47

Always On Setup with few faults to show how easy mistakes can be made.

Important always enable always on cluster under SQL Server Configuration Manager

AlwaysOn Setup Bad
05:37

Remember when running SQL Server Always On it is best to do so using a service account.

When creating databases on availability groups the easiest way is using network share that both the SQL Server service account and cluster object have access.

And remember the SQL Server endpoints must be open on the firewall.

Always On Setup Good
03:24
+
Performance
3 Lectures 08:41

Nested Views
04:04

Understanding how Queries impact on Memory, CPU and I/O

Query 1 : Top 10 total CPU consuming queries

SELECT TOP 10
QT.TEXT AS STATEMENT_TEXT,
QP.QUERY_PLAN,
QS.TOTAL_WORKER_TIME AS CPU_TIME
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP
ORDER BY TOTAL_WORKER_TIME DESC

Query 2 : Top 10 average CPU consuming queries

SELECT TOP 10
TOTAL_WORKER_TIME ,
EXECUTION_COUNT ,
TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] ,
QT.TEXT AS QUERYTEXT
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT
ORDER BY QS.TOTAL_WORKER_TIME DESC ;

Query 3 : Top 10 I/O intensive queries

SELECT TOP 10
TOTAL_LOGICAL_READS,
TOTAL_LOGICAL_WRITES,
EXECUTION_COUNT,
TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL],
QT.TEXT AS QUERY_TEXT,
DB_NAME(QT.DBID) AS DATABASE_NAME,
QT.OBJECTID AS OBJECT_ID
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT
WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0
ORDER BY [IO_TOTAL] DESC

Query 4 : Execution count of each query

SELECT 
QS.EXECUTION_COUNT,
QT.TEXT AS QUERY_TEXT,
QT.DBID,
DBNAME= DB_NAME (QT.DBID),
QT.OBJECTID,
QS.TOTAL_ROWS,
QS.LAST_ROWS,
QS.MIN_ROWS,
QS.MAX_ROWS
FROM SYS.DM_EXEC_QUERY_STATS AS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT
ORDER BY QS.EXECUTION_COUNT DESC


Most Expensive Queries
02:14
About the Instructor
John Hall
4.1 Average rating
8 Reviews
61 Students
2 Courses
Cloud Service Architect

John Hall is an IT professional with knowledge of Linux, Windows and Networking.

Spend large part of his career working for blue chip multinationals developing Private and Public cloud solutions for applications such as Sharepoint, SAP, J2EE and PaaS. 

Working in IT since 1999 he is an Microsoft Certified IT Professional in SQL Server and Windows Server however most of his time has been spend hunting down performance issue and improving overall customer service.