سرفصل ها و محتوای دوره آموزش مدیریت بانک اطلاعاتی SQL Server
(هرآنچه به عنوان یک DBA به آن نیاز دارید)
طول دوره: 80 ساعت
پیشنیاز دوره: آشنایی با بانک های اطلاعاتی
معرفی دوره:
با توجه به حساسیت سرورهای بانک اطلاعاتی، مسئولیت نگهداری سرور از مهمترین مشاغل در یک Data Center و در کل شرکت است. کسی که این مسئولیت خطیر بر دوش اوست DBA مخفف Database Administrator نامیده می شود.
وظایف یک DBA در چهار دسته طبقه بندی می شوند:
Backup and Disaster Recovery: حفظ، نگهداری و صیانت از اطلاعات سازمان به گونه ای که هرگز و تحت هیچ شرایطی حتی یک بایت از اطلاعات با ارزش سازمان از بین نرود.
High Availability: سرویس بدون وقفه سرورهای دیتابیس و سیستم های وابسته به آنها در 24 ساعت شبانه روز و 7 روز هفته به گونه ای که حتی برای چند دقیقه وقفه در عملیات سازمان پیش نیاید. شناسایی مشکلات و رفع بموقع آنها (Troubleshooting) نیز در همین دسته قرار می گیرد.
Security: حفظ امنیت اطلاعات به گونه ای که افراد فقط در حد مجوزهای داده شده بتوانند اطلاعات سازمان را مشاهده کنند یا اصلاح نمایند. تمامی عملیات کاربران در سرور باید لاگ شده و حسب لزوم برای کنترل و آدیت (Audit) در اختیار مدیریت قرار گیرد.
Performance: حفظ راندمان سرور به گونه ای که سیستم ها کند نشده و با بالاترین سرعت همواره در حال اجرا باشند. بدین منظور سرور باید مورد نظارت و مانیتورینگ 24 ساعته باشد و کوئری های کند سریعاً شناسایی شده و مشکل کندی آنها برطرف گردد (Tuning).
در این کلاس استاد پرویز آقاصادقی با بیش از 20 سال سابقه مدیریت و مشاوره و تدریس بانک اطلاعاتی SQL Server شما را در 18 موضوع همراه با کار عملی با مسئولیتهای یک DBA آشنا می کنند و تجارب خود را به شما انتقال خواهند داد.
سرفصل دوره :
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)