Comprehensive SQL Server Administration Part 2

Name: Comprehensive SQL Server Administration Part 2
Rating: 4.4 (6 reviews)

Database Design, Migration, Patching, Upgrade, Concurrency, Transparent Data Encryption, and Dynamic Data Masking

Created byAdewale Adeniji

Last updated 12/2024

English

What you'll learn

Database Migration Methodologies and Suitability for Business Requirements
Database Refresh, SQL Server Patching, SQL Server Upgrade, and Move Database Files
Database Security Technologies such as Transparent Data Encryption and Dynamic Data Masking
Database and Datawarehouse Design Architecture (Normalization, Denormalization, Data Types, and Constraints)
Concurrency in SQL Server (ACID Principle and Isolation Levels)
Indexes, their Structure, Components and Maintenance (Reorganize/Rebuild)

Course content

13 sections • 90 lectures • 12h 6m total length

Introduction to Database Migration6:13
Backup and Restore1:52
Demo: Backup and Restore7:26
Detach and Attach5:28
Demo: Detach and Attach Part 17:19
Demo: Detach and Attach Part 29:10
Copy Database1:42
Demo: Copy Database13:03
Import and Export Data Wizard3:06
Demo: Import and Export Data Wizard Part 113:19
Use the import export wizard to migrate tables from Adventureworks on VM 01 to VM 02, creating a new destination database and configuring mappings.
Demo: Import and Export Data Wizard Part 25:55
Knowledge Check: Migration

Introduction to Microsoft Azure0:51
Demo: Create Microsoft Azure Account2:20
Demo: Microsoft Azure Subscription (Free-Trial)4:18
Demo: Azure SQL Deployment Options5:25
Demo: Create Azure SQL Database14:15
Demo: Connect to Azure SQL Database2:32
Copy the Azure server name, connect with SQL Server authentication, and create a firewall rule to allow your client IP, highlighting the difference between on prem and Azure SQL Database.
Demo: Create Azure SQL Managed Instance7:26
Demo: Migration to Azure SQL Database Using Deploy Data Wizard6:43
Demo: Migration to Azure SQL Database Using Data-Tier Application (.BACPAC)9:03
Demo: Migration From Azure SQL Database Using Data-Tier Application (.BACPAC)6:15

Data Migration Assistant (DMA) Installation5:12
Database Migration Using DMA Part 19:18
Assess and migrate from on-prem SQL Server to Azure SQL Database with DMA, the data migration assistant, evaluating compatibility and blockers and considering elastic database jobs or managed instance.
Database Migration Using DMA Part 213:27
Database Migration Using DMA Part 32:58
Four tables migrate successfully to the destination azure sql database, while two fail due to a data type issue; the schema exists but lacks data. Next, migrate databases using powershell.

Introduction to SQL Server Patching8:00
Service Packs and Cumulative Updates9:02
Trace how service packs and cumulative updates evolved from 2014 to 2022, noting end of service packs after 2017; use build numbers and kb references to verify sql server versions.
Cumulative Updates Download5:45
SQL Server Patching Steps6:41
Follow SQL Server patching steps: test in lower environment, back up system and user databases, download service pack, ensure no running jobs, and coordinate always-on availability groups with manual failover.
Demo: SQL Server Patching Part 110:40
Demo: SQL Server Patching Part 213:16
Demo: SQL Server Patching Part 36:16

Introduction to Transparent Data Encryption13:07
Understand how transparent data encryption protects data at rest by encrypting all database files and backups with keys and certificates, including tempdb and secondary databases in high availability setups.
Database Encryption Steps9:20
Demo Database Encryption13:59
Encryption Verification2:30
Demo: Encrypted Database Restore Part 14:11
Demo: Encrypted Database Restore Part 213:45
Database Decryption3:28
Turn off TDE, verify the encryption state, and drop the database encryption key to complete decryption; note the tmdb remains encrypted until the key is removed.
Encryption in AlwaysOn AG5:36
Knowledge Check: Transparent Data Encryption and Dynamic Data Masking

Requirements

Completed Comprehensive SQL Server Administration Part 1 Or Have Prior Knowledge of the Concepts
Windows Personal Computer (Laptop or Desktop)
Reliable Internet Connection
Great Passion to Learn
Existing Virtual Lab Environment for Technical Hands-on Practices

Description

Dive into the world of SQL Server with our comprehensive course designed for existing and prospective Microsoft SQL Server Database Administrators. Whether you're a beginner, junior, or intermediate-level Database Administrator, this course equips you with essential skills and knowledge to excel in the field. You'll gain a solid foundation in SQL Server architecture and functionality, and learn critical topics such as database design, migration, patching, upgrade, concurrency, isolation levels, transparent data encryption (TDE), and dynamic data masking.

This course is rich in technical hands-on demonstrations to enhance your comprehension of the material, and it includes numerous practice exercises to reinforce your learning. You'll also find a wealth of typical interview questions to help you prepare for job interviews and knowledge checks. Designed to help you break into the IT field or switch roles within the industry, this course ensures you are well-prepared for your career.

This engaging course is perfect for those with a passion for technical hands-on activities and aims to provide a solid foundation in SQL Server administration.

Prerequisite to this course is a virtual lab, with setup videos available in my first course (Comprehensive SQL Server Administration Lab Setup). Trainees with an existing lab can skip the first course and proceed with this course.

Who this course is for:

Individuals with Technical Hands-On Abilities (Prospective Microsoft SQL Server Database Administrators)
Beginner/Junior Microsoft SQL Server Database Administrators
Mid-Level/ Intermediate Microsoft SQL Server Database Administrators

Comprehensive SQL Server Administration Part 2

What you'll learn

Explore related topics

Course content

Database Migration (On-Prem to On-Prem)11 lectures • 1hr 15min

Database Migration (On-Prem to Microsoft Azure)10 lectures • 59min

Database Migration Using Data Migration Assistant4 lectures • 31min

Database Migration Using PowerShell5 lectures • 44min

Database Refresh5 lectures • 32min

SQL Server Patching7 lectures • 1hr

SQL Server Upgrade4 lectures • 25min

Database Files Movement5 lectures • 50min

Database Security: Transparent Data Encryption8 lectures • 1hr 6min

Database Security: Dynamic Data Masking4 lectures • 26min

Requirements

Description

Who this course is for: