Essential SQL Server Administration Tips (for DBAs)
4.4 (9 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
99 students enrolled

Essential SQL Server Administration Tips (for DBAs)

As a DBA, you need to maintain secure, performant and efficient SQL Server instances. Learn how!
4.4 (9 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
99 students enrolled
Last updated 4/2020
English
English [Auto-generated]
Current price: $69.99 Original price: $99.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 6 hours on-demand video
  • 27 downloadable resources
  • 1 Practice Test
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • How to efficiently administer SQL Server instances and databases in many aspects
  • How to perform essential SQL Server maintenance tasks
  • How to perform essential SQL Server performance tasks
  • How to efficiently secure SQL Server
  • How to handle common errors you might get in SQL Server
  • How to perform different SQL Server integration tasks
  • Get deeper understanding about key SQL Server topics and concepts
Course content
Expand all 81 lectures 05:56:17
+ Introduction
2 lectures 03:37

This is the introduction to the course. We will talk about how this course is structured and what will you learn after completing this course.

Preview 02:33

Learn how can this course help you with your SQL Server journey.

Preview 01:04
+ SQL Server Maintenance
15 lectures 01:16:21

This lecture, is an overview of what you will learn in this section.

Preview 00:50

In this lecture, you will learn how to patch a standalone SQL Server machine.

Preview 07:10

In this lecture, you will learn how to patch a SQL Server failover cluster instance.

How to Patch a SQL Server Failover Cluster Instance
07:04

In this lecture and demo, you will learn about the “Maximum Number of Concurrent Connections Setting” in SQL Server.

The Maximum Number of Concurrent Connections Setting in SQL Server
07:40

In this lecture and demo, you will learn how to search for specific keywords in SQL Server Agent jobs.

Searching for Keywords in SQL Server Agent Jobs
07:32

In this lecture, you will learn about the SQL Server Browser service.

The SQL Server Browser Service and UDP Port 1434
05:02

In this lecture and demo, you will learn how to get disk usage statistics for all tables in a SQL Server database.

Getting the Disk Usage Statistics for all Tables in a Database
05:23

In this lecture, you will learn how to handle disk space issues that might come up during heavy index rebuild operations.

Handling Disk Space Issues During Heavy Index Rebuild Operations
03:09

In this lecture, you will learn how to add a database to a SQL Server Availability Group using T-SQL.

How to Add a Database to a SQL Server Availability Group Using T-SQL
03:44

In this lecture and demo, you will learn about contained databases in SQL Server and how to migrate from a non-contained database to a contained database.

Migrating to a Contained Database in SQL Server
09:59

In this lecture and demo, you will learn how to model database creation in SQL Server, using the Model system database.

Modelling Database Creation with the Model System Database in SQL Server
04:51

In this lecture, you will learn about the SQL Server compatibility levels.

Compatibility Levels Supported by Different SQL Server Versions
03:33

In this lecture and demo, you will learn about backup compression in SQL Server.

Backup Compression in SQL Server
03:06

In this lecture and demo, you will learn how to transfer ownership of all database objects back to dbo.

Transferring Ownership of All Database Objects Back to DBO
06:35

This lecture, is a recap of what you have learned in this section.

Section Recap
00:43

Test what you have learned in the "SQL Server Maintenance" section.

SQL Server Maintenance
4 questions
+ SQL Server Performance
11 lectures 45:08

This lecture, is an overview of what you will learn in this section.

Preview 00:28

In this lecture, you will learn about the importance of database indexes.

The Importance of Database Indexes
01:24

In this lecture and demo, you will learn how to rebuild indexes in SQL Server.

Index Maintenance Scripts
06:23

In this lecture, you will learn about the tempdb system database in SQL Server.

The TempDB System Database in SQL Server
03:33

In this lecture, you will learn more about the tempdb system database in SQL Server, and more specifically about tempdb growth.

tempdb growth
05:29

In this lecture, you will be introduced to the powerful In-Memory OLTP Engine in SQL Server.

Introducing In-Memory Optimization in SQL Server
05:17

In this lecture and demo, you will learn even more about In-Memory OLTP in SQL Server, and we will create a T-SQL based example of using In-Memory OLTP.

In-Memory Optimization in SQL Server: A Simple Experiment
08:34

In this lecture and demo, you will learn how to get RAM usage statistics for SQL Server databases, using T-SQL scripts.

Getting the RAM Usage per Database in SQL Server
04:45

In this lecture and demo, you will learn how to get CPU usage statistics for SQL Server databases, using T-SQL scripts.

Getting the CPU Usage per Database in SQL Server
04:37

In this lecture, you will learn about concurrency control in SQL Server, and more specifically, about locking and blocking.

Locking and Blocking in SQL Server
03:58

This lecture, is a recap of what you have learned in this section.

Section Recap
00:40

Test what you have learned in this section.

SQL Server Performance
4 questions
+ SQL Server Security
16 lectures 01:22:19

This lecture, is an overview of what you will learn in this section.

Preview 02:05

In this lecture, we will discuss about the necessity for securing your SQL Server instances, databases and servers.

Why You Need to Secure Your SQL Server Instances
02:33

In this lecture and demo, you will learn about Transparent Data Encryption in SQL Server.

Transparent Data Encryption (TDE) in SQL Server
14:27

In this lecture, you will learn about 10+1 useful facts about Transparent Data Encryption in SQL Server.

10+1 Facts About SQL Server Transparent Data Encryption
03:05

In this lecture and demo, you will learn how to encrypt a SQL Server database backup.

Encrypting a SQL Server Database Backup
09:21

In this lecture, you will learn about SQL Server support for TLS 1.2.

SQL Server Support for TLS 1.2
01:37

In this lecture and demo, you will learn how to enable SSL certificate-based encryption on a standalone SQL Server machine.

How to Enable SSL Certificate-Based Encryption on a Standalone SQL Server
07:16

In this lecture and demo, you will learn how to enable SSL certificate-based encryption on a SQL Server failover cluster.

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster
08:01

In this lecture and demo, you will learn about the SELECT ALL USER SECURABLES permission in SQL Server.

The SELECT ALL USER SECURABLES Permission in SQL Server
06:12

In this lecture and demo, you will learn about Dynamic Data Masking in SQL Server 2016 and later.

Dynamic Data Masking in SQL Server 2016 or Later
07:36

In this lecture, you will learn about the “Public” database role in SQL Server.

Preview 01:39

In this lecture and demo, you will learn about Row Level Security in SQL Server 2016 and later.

SQL Server Row Level Security by Example
05:55

In this lecture and demo, you will learn why the Windows “Built-In\Administrators” Group should not be SQL Server SysAdmins.

Should Windows “Built-In\Administrators” Group be SQL Server SysAdmins?
01:55

How to Rename Logins in SQL Server

How to Rename Logins in SQL Server
03:42

In this lecture and demo, you will learn about orphaned users in SQL Server and how you can handle them.

Handling Orphaned Users in SQL Server
06:14

This lecture, is a recap of what you have learned in this section.

Section Recap
00:41

Test what you have learned in this section.

SQL Server Security
4 questions
+ SQL Server Integration
6 lectures 16:26

This lecture, is an overview of what you will learn in this section.

Preview 00:34

In this lecture and demo, you will learn how you can use proxy accounts in SQL Server Agent jobs.

Using Proxy Accounts in SQL Server Agent Jobs
04:07

In this lecture and demo, you will learn about Unicode support in pre-SQL Server 2019 versions.

Preview 03:47

In this lecture and demo, you will learn about Unicode support in SQL Server 2019 and later versions.

UTF-8 Support in SQL Server 2019 and later
03:19

In this lecture and demo, you will learn how to create a simple linked server between two SQL Server instances.

How to Create a Simple Linked Server Between SQL Server Instances
04:09

This lecture, is a recap of what you have learned in this section.

Section Recap
00:30

Test what you have learned in this section.

SQL Server Integration
4 questions
+ SQL Server Error Handling
18 lectures 55:36

This lecture, is an overview of what you will learn in this section.

Preview 00:59

In this lecture, you will learn how to troubleshoot the error message: Rule “KB2919355 Installation” failed.

Resolving the Error Message: Rule “KB2919355 Installation” failed
01:53

In this lecture and demo, you will learn how to troubleshoot the error message: Error converting data type varchar to float.

Preview 02:55

In this lecture, you will learn how to troubleshoot the error message: Database […] cannot be upgraded because it is read-only or has read-only files.

Database […] cannot be upgraded because it is read-only or has read-only files
00:58

In this lecture, you will learn how to troubleshoot the error message: Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=10.0.0.0, …

Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=10.0.0.0, …
01:33

In this lecture, you will learn how to troubleshoot the error message: A connection was successfully established with the server, but then an error occurred during the login process.

A connection was successfully established with the server, but then an error...
02:13

In this lecture, you will learn how to troubleshoot the error message: The SELECT permission was denied on the object ‘extended_properties’, database ‘mssqlsystemresource’, schema ‘sys’.

The SELECT permission was denied on the object ‘extended_properties’, database..
01:32

In this lecture and demo, you will learn how to troubleshoot the error message: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

ORDER BY items must appear in the select list if SELECT DISTINCT is specified
02:19

In this lecture, you will learn how to troubleshoot the error message: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Preview 02:24

In this lecture, we troubleshoot the SQL Server error message "There is no SQL Server Failover Cluster Available to Join".

There is no SQL Server Failover Cluster Available to Join
04:25

In this lecture, we troubleshoot the SQL Server error message "An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database".

An error occurred within the report server database. This may be due...
04:19

In this lecture, we troubleshoot the SQL Server error message "Operating System Error 170 (Requested Resource is in use)".

Operating System Error 170 (Requested Resource is in use)
05:02

In this lecture, we troubleshoot the error message "Cannot Connect to WMI Provider" you might get when trying to run SQL Server Configuration Manager.

Cannot Connect to WMI Provider (SQL Server Configuration Manager)
04:59

In this lecture, we troubleshoot the SQL Server error message "The Multi-Part Identifier Could not be Bound".

The Multi-Part Identifier Could not be Bound
05:18

In this lecture, we troubleshoot the SQL Server error message "Argument data type ntext is invalid for argument 1 of left function".

Argument data type ntext is invalid for argument 1 of left function
05:21

In this lecture, we troubleshoot the SQL Server error message "String or binary data would be truncated".

String or binary data would be truncated
06:38

In this lecture, we troubleshoot the SQL Server Integration Services error message "Microsoft SSIS Service failed to start. Configuration system failed to initialize".

Microsoft SSIS Service failed to start. Configuration system failed to...
01:54

This lecture, is a recap of what you have learned in this section.

Section Recap
00:54

Test what you have learned in this section.

SQL Server Troubleshooting
4 questions
+ Special Topics
11 lectures 01:10:52

This lecture, is an overview of what you will learn in this section.

Section Overview
00:49

In this lecture and demo, you will learn where are temporary tables actually stored in the tempdb system database.

Preview 04:18

In this lecture, you will learn how to suppress the “N Row(s) Affected” output message in SQL Server.

How to Suppress the “N Row(s) Affected” Output Message in SQL Server
01:51

In this lecture, you will learn how to setup SQL Server Reporting Services (SSRS) in order to be able to access it using a fully qualified domain name (FQDN).

Accessing Reporting Services Using a Fully Qualified Domain Name
02:37

In this special topic, we will talk about Stretch Database in SQL Server and via a comprehensive live demonstration, we will see how it is possible via the Stretch Database technology, to store a database table on Azure SQL Database, while being able to access it from your on-premises SQL Server database.

How to Enable Stretch Database in SQL Server
15:36

SQL Server Administrators, among other, need to also know how to work with SSMS in terms of running queries. Therefore, in this special topic, we will see how we can run queries against SQL Server using SQL Server Management Studio, as well as how we can script different wizard-based procedures to T-SQL code.

Running Queries and Scripting to T-SQL
07:39

A DBA must know how to import and export data in SQL Server. Via this special topic, we will talk about some of these methods, as well as see a live demonstration.

Methods of Importing and Exporting Data in SQL Server
10:48

A DBA must know how to interact via SQL Server with the Operating System for performing different tasks, for example file operations. In this special topic, we will see how you can write to a text file using SQL in SQL Server.

How to Write to a Flat File Using SQL in SQL Server
05:02

A DBA must know how to interact via SQL Server with the Operating System for performing different tasks, for example file operations. In this special topic, we will see how you can read from a text file using SQL in SQL Server.

How to Read from a Flat File Using SQL in SQL Server
03:19

In this special topic, we are going to see how we can install SQL Server 2019 on CentOS Linux version 8.

How to Install SQL Server on CentOS Linux
11:10

As Database Administrators (DBAs), we are called many times for supporting developers connecting their applications to our SQL Server instances (i.e. provide the connection string, etc.). Therefore, we need to have the relevant knowledge. In this special topic, via a live demonstration, you will learn how to connect to SQL Server and run queries, via a C++ program.

How to Connect to SQL Server from a C++ Program
07:43
+ Putting it All Together
1 lecture 01:23

In this lecture, we will talk about what you have learned in this course.

What Have you Learned in this Course?
01:23

This is the main practice test for this course. It consists of 20 questions and can help you extensively test what you've learned in this course.

Essential SQL Server Administration Tips
20 questions
+ Learning More
1 lecture 04:35

Bonus lecture.

Bonus Lecture
04:35
Requirements
  • At least basic SQL Server knowledge is required
  • It would be helpful to know some basic database-related concepts
Description

In this course, you will learn many SQL Server administration hands-on tips, that will help you efficiently administer SQL Server within the context of SQL Server maintenance, performance, security, integration and error handling.

SQL Server is one of the world's leading Data Platforms. It is full of features that enable you to store, organize, retrieve and transform your data into useful business insights and knowledge. Like in all Database Management Systems, in order for your SQL Server instances to operate to the best possible level in terms of stability and performance, you need to properly administer and maintain them.

I have carefully designed this course, in order to help you with your SQL Server journey. With many live demonstrations and step by step guides, via my course, I will help you learn how to perform many SQL Server administration tasks, as well as, get a deeper understanding about critical SQL Server topics that all modern SQL Server Administrators, Engineers and Architects needs to know.

Via my course, among other, you will learn how to patch standalone and failover cluster SQL Server instances, how to enable Transparent Data Encryption (TDE) for data at rest and encrypt database backups, how to enable SSL encryption for data in motion, how to tune SQL Server instances via indexes, CPU, RAM and storage monitoring, how to protect your data with Dynamic Data Masking and Row Level Security, how to use Unicode, how to troubleshoot common error messages in SQL Server and much more.


AFTER THE COURSE:

  • You will know how to efficiently perform different SQL Server administration tasks on the below SQL Server knowledge areas:

    • Maintenance

    • Performance

    • Security

    • Integration

    • Error Handling

  • You will have a deeper understanding on many SQL Server administration topics that fall under the above knowledge areas

  • You will be able to download a rich set of downloadable resources, mainly T-SQL scripts, that will help you better understand all tips provided via the course.


PREREQUISITES:

  • At least basic knowledge of SQL Server is required.

  • It would be helpful to know some basic database-related concepts (i.e. what is a database, a table, etc.) but is not a prerequisite, since we will talk about it as well.

  • Willingness and excitement to learn!


THIS COURSE CONTAINS/OFFERS:

  • 6 hours of high-quality video

  • Live demonstrations and hands-on guides that will help you perform many essential SQL Server administration tasks

  • Useful quizzes for testing your knowledge on each section

  • Many downloadable resources such as T-SQL scripts and links

  • A practice test in the end of the course

  • Ability to ask the instructor questions via the Q&A section

  • Certificate on course completion


WHAT ARE PEOPLE SAYING ABOUT THE COURSE:

  • "Very useful and well organized course. The knowledge gained will be actively used in practice.", Andreas Josephides

  • "The demonstrations and provided SQL scripts will definitely help in becoming a better SQL Server Administrator and make SQL Server instances perform faster which was very important for me.", Giannis Hadj

  • "A very interesting and useful course for database professionals. I can directly apply it at my work and believe it will resolve difficulties regarding the administration of our SQL server.", Andreas Nestorides

  • "Great course! Live demonstrations make it really easy for me to understand how to perform important tasks in SQL Server.", Rena Leandrou

Who this course is for:
  • SQL Server Administrators (DBAs)
  • SQL Server Engineers
  • SQL Server Architects
  • SQL Server Developers who want to learn more about SQL Server Administration