1. Data Center Design Consideration

Location

Thermal Solution

 Fire SUPPRESSION

Security

2. SQL Server Hardware & Software requirements

CPU selection

RAM size

 Data is saved Asynchronously by the Checkpoint process       

HDD/SSD storage

Are SSDs best for everything ?        

Disk Block Size ?         

DAS/NAS/SAN which storage type?

Operating System Selection

Is Anti-Virus necessary for SQL Server?

3.  Installing SQL Server 2019

Default or Named instance

Service Account selection

Authentication Modes

TempDB Optimization

4. Network Configuration

Keep Alive 

Network Protocols (Named Pipes, TCP/IP)

TCP/IP Port selection

Firewall configuration

5. SQL Server Configuration Manager

Changing the Service Account

Changing the Startup mode

What to do if the Service does not start ?

6. SQL Server Management Technologies

SQL Server Management Studio (SSMS)

 Templates     

Code Snippets     

SP_msForEachDB & SP_msForEachTable procedures    

SQLCMD utility

Writing parametric scripts      

PowerShell scripting

Windows PowerShell ISE     

How much PowerShell should we learn ?    

7. System Databases

SystemResource

Master

Model

MSDB

TempDB

8. Introduction to Schemas & Tables

 What is Schema good for?

 Why should we forget about the old dbo schema?

 How to Create Schemas and Tables inside schemas?

 How to change the schema of existing tables?

 How to use SYNONYMs to keep the old queries working?

9. Database Physical Design

Data Files & Filegroups

 How to design Filegroups for Storage management     

 How to design Filegroups for performance     

 How to move existing table into a Filegroup     

 How to add or remove files to/from a Filegroup     

Transaction Log File & Recovery Models

What is a Transaction?     

Why a log file is needed for ACID Transactions?     

 10 steps behind a successful Transaction?     

 Why a Log file size grows and how can we manage its size?     

 What is Transaction Log Backup?     

Log Reuse Wait description?     

Forget about the Simple Recovery Model for ever?     

 Is Bulk Logged recovery model still a good choice ?     

Log slows down the transactions  What can we do about it ?    

Control Transaction Durability     

- Delayed Durability             

Data Compression

Page/Row compression?     

Benefits of Data Compression ?     

Disadvantages of Data Compression ?     

 Who should use Data Compression ?     

Data & Index Partitioning

 What is Data Partitioning ?     

Aligned Indexes     

Partition Function & Partition Scheme     

How to partition a new table or index ?     

How to partition existing table and index ?     

Is Partitioning good for Query Performance ?    

What are the benefits of Data Partitioning  ?  

Sliding Window scenario     

Introduction to In Memory OLTP

Some history on the background of the subject

A demo of the 30x performance boost when switching to In Memory     

Schema & Data Durability     

No Locks or Latches (Optimistic Concurrency)     

Natively Compiled Procedures     

Database Maintenance and Repair 

DBCC CHECKDB        

Suspect Pages        

10. Backup & Restore

Types of Backup (Full , Differential , Transaction Log)

Implementing a Backup Strategy

Backup file storage (local, remote , cloud , disaster site)

COPY-ONLY backups

Backup Encryption

Minimize the Down Time for Restore operations

Tail of Log Backup

Types of Restore 

Overwriting existing database        

Database Crash (Disaster) Recovery

Physical Crash        

- Data files are Lost but Log file is intact              

- Data files are intact but Log file is lost              

- Both Data and Log files are lost              

- Even Backup files  are also lost (Total Disaster)              

Logical Crash       

- Using Apex SQL Log reader application             

- Restore to a point in time             

- Restore with STANDBY option             

- Performing Restore to recover the lost data             

Filegroup Backup and Restore

It is a solution for Very Large Databases with correct physical design        

Filegroup Backup       

Partial Restore       

Piecemeal restore        

Database Snapshots

It is not a backup of the Database        

    Get a copy of your data instantly (in less than 1 second )      

   What is it good for?       

   Reverting a database to a Snapshot      

Maintenance Plans

 11 Tasks in a maintenance plan       

Designing the work flow       

Hourly/Daily/Weekly/Monthly tasks       

Backup Retention Time       

Ola Hallengren Maintenance Solution       

11. Security

Authentication

Windows Authentication vs SQL Server Authentication        

Active Directory Groups as Windows Logins        

Default Database and Default Language        

Authorization

Server Level Permissions          

- Server Roles membership                

- Server Permissions                

- User Defined Server Roles                

Database Level Permissions          

- Database Owner                

- Guest User                

- Database User Permissions                

    + Database Role Membership                        

   Public Role                                    

   DB_OWNER Role                                    

    + Database Permissions                        

   + Schema Ownership and Schema Permissions                        

   + Specific Object Permissions                       

   + Column Level Permissions                        

- Orphan User                

- How to Transfer Login with their Passwords to a new Server                

- Contained Databases and Partial Containment                

Ownership Chaining         

System Views and Functions to List User Permissions         

SQL Server 2019 Security New Features

Dynamic Data Masking         

Row Level Security         

Always Encrypted         

12. Automating tasks (Jobs/Alerts)

Review of SQL Server Agent Service and MSDB database

CREATE a new Job

Job Owner      

Adding new Steps      

   - T-SQL Step               

   - CMDEXEC Step               

   - SSIS Step               

Scheduling Jobs     

Be warned about Auto Delete jobs !      

Job Notifications and Database Mail Service

Job History

Alerts

How to grant Permission to non SysAdmins to Create Jobs?

 Job Proxy       

Multi-Server Jobs

13. Replication

Do we still need the Replication?

Replication Topologies

Transactional Replication

Merge Replication

Peer to Peer Replication

Backup & Restore strategy for Replication

14. Monitoring & Performance Tuning

Hardware Monitoring

Resource Monitor      

Performance Monitor     

Software Monitoring

SQL Server Data Collector      

Profiler     

Extended Events      

Finding High Cost (Slow) Queries    

Missing indexes      

Wait Stats and Queue Analysis

15. Server & Database Auditing

Server Audit

Database Audit

Using Trigger for Audit

16. Policy Based Management

Creating Conditions

Creating Policies

Running Policies

17. Resource Governor

Creating Resource Pools

Creating Workload Groups

Creating a Classifier Function

Enabling the Resource Governor

18. High Availability (AlwaysOn)

  %99.999 Availability is possible

Overview of different HA techniques in SQL Server

Instance Failover Clustering         

Log Shipping        

Peer to Peer Replication        

Database Mirroring         

AlwaysOn         

Synchronous vs Asynchronous Commit modes

Automatic vs Manual Failover

Read Only Replicas and Read Only Routing

 Steps in AlwaysOn Setup

Create the WSFC (Windows Server Failover Cluster)       

You do not need a SAN storage        

Enable the AlwaysOn feature for the Database Engine service)