دوره آموزش SQL Server Administration

توضیحات

مدت دوره

40 ساعت

شرح دوره

دوره SQL Server Administration از آموزش‌های تخصصی بانک اطلاعاتی است.

پيش نياز

SQL Server Design and Querying

 

مخاطب دوره

کارشناسان و مدیران بانک‌های اطلاعاتی

علاقه‌مندان به حوزه بانک‌های اطلاعاتی

سرفصل دوره

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. 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

14. Server & Database Auditing

Server Audit

Database Audit

Using Trigger for Audit

15. Policy Based Management

Creating Conditions

Creating Policies

Running Policies

18. Resource Governor

Creating Resource Pools

Creating Workload Groups

Creating a Classifier Function

Enabling the Resource Governor

 

17. 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)