Below are SQL Server topics which are essential for Database Administrator...
Server, DB Architecture
○ Server Architecture & Protocols
○ Query Processor & Storage Engine
○ Parser, Optimizer, SQL & DB Manager
○ File Manager and Transaction Services
○ IO & Lock Manager, MDAC, CLR, WAL
○ Buffer Manager, Lazy Writer, SQL OS
○ Linked Servers, RPC & Data Access
○ Database Architecture - Files, Pages
○ Primary (.mdf), Secondary Files (.ndf)
○ Filegroups Usage, Read Only Filegroups
○ File Properties: Size & Location
○ LSN, Mini LSN and Virtual Log File
○ Uniform Extent and Mixed Extent
○ Schemas: Creation, Usage, Migrations
BACKUPS - DB, Filegroup, File
○ Database Backups, Filegroup Backups
○ Log File Backups and Log Truncations
○ COPY_ONLY Backups and Real-time Use
○ Mirror Backups and Split Backups
○ Partial Backups - Read-Only Filegroups
○ Format, Compression and Checksum
○ Backup Verification, Retain-Period, Stats
○ Continue On Error and Backup Scripts
○ GUI and Script Backups: Differences
○ Backup History Tables in MSDB - Joins
○ Backup Audits. HOT and COLD Backups
○ Backup Devices - Creation and Usage
○ Using Backup Devices - Advantages
○ Common Errors and Solutions
RESTORES & DB RECOVERY
○ Restore Phases - COPY, REDO, UNDO
○ RECOVERY, NORECOVERY Options
○ STANDBY and REPLACE in Restores
○ File, File Group & Metadata Restores
○ Backup Verifications using GUI, Scripts
○ VERIFYONLY : Backup Verification
○ STATS, UNLOAD, STOPAT and INIT
○ PARTIAL / PIECEMEAL Restores - Use
○ Tail Log Backup Usage in Real-time
○ Restores using GUI and T-SQL Scripts
○ MOVE Options for File Level Restores
○ Point-In-Time Restore, Checkpoint LSN
○ Standby Restores and Read-Only State
○ Common Errors and Solutions
JOBS, MAINTENENCE PLANS
○ SQL Server Agent Service & Agent XPs
○ SQL Agent Jobs - GUI, Script Creations
○ Job Steps - Creation, Edits and Parse
○ Job Executions, Disable/Enable Options
○ Job History Purge. Job Activity Monitor
○ Database Offline and Backup Errors
○ Database Maintenance - Backup Jobs
○ Scheduling Database Maintenance Plans
○ Automated Job Creations using DMPs
○ Backup Cleanup & History Cleanup Jobs
○ Backup Strategies For Minimal Data Loss
○ Backup Options: Block Size, Transfer Size
○ DB Mail Configurations and Alert System
○ DB Mail Profiles, SMTP Email Accounts
○ Operators : Creation, Job Notifications
SECURITY MANAGEMENT
○ Authentication Types & Modifications
○ Windows Logins & SQL Server Logins
○ Logins - Users Mapping, DB Access
○ Server Roles & Database Roles - Usage
○ Object, Column and Schema Security
○ GRANT, WITH GRANT, DENY, REVOKE
○ CONTROL, OWNERSHIP, Authorization
○ Data Encryption: Keys and Certificates
○ Data Encryption with Stored Procedures
○ Job Security : Credentials and Proxies
○ Using Proxies for SSIS Jobs, Repl Jobs
○ Security Scripts and Documentation
○ DMVs for Security Audits, Orphan Users
○ Login, User, Server Principal Audits
DB Migrations, Perf Tuning
○ CDW : Copy Database Wizard @ SSMS
○ Database Detach and Attach Options
○ SMO Method and Database Scripting
○ CDW SSIS Packages, SSIS Proxies Use
○ Scheduling Database Migration Jobs
○ Detecting and Resolving Orphan Users
○ Containment Databases Authentication
○ Audit Long Running Queries : DMV, DMF
○ Activity Monitor Tool, Server Dashboards
○ Logical I/O, Physical I/O, Database I/O
○ Recent Expensive Queries, Wait Time
○ Active Expensive Queries, Statistics
○ Plan Handle, Execution Time - Audits
○ CPU, IO, Memory Consumption Reports
Indexes, Partitions
○ Indexes: Architecture and Index Types
○ B Tree Structure, IAM Page [Root]
○ Clustered & NonClustered Indexes
○ Included, Columnstore, Online
○ Filtered, Covering, Indexed Views
○ Fill Factor and Pad Index Options
○ Query Store - Settings and Advantages
○ PARTITIONS : Advantages, Performance
○ Partition Functions & Partition Schemes
○ Partitioning Un-partitioned Tables: GUI
○ Partition Compression : ROW and PAGE
○ Auditing Table Partitioned Structures
○ Statistics : Purpose, Auto Creation, Audits
○ NUMA Nodes, Processor, IO Affinity
Health Check Issues, Solutions
○ Alerts : Creation and Notifications
○ DB Suspect Event Alerts (023)
○ Important Perfmon Counters, Alerts
○ Log Space, Memory, Tempdb Alerts
○ Scheduling Alerts & Notifications
○ DBCC CHECKDB : DB Health Checks
○ Allocation Errors, Consistency Errors
○ DBCC ShowContig, Extent Fragmentation
○ Trace Flags and Page Restores
○ DBCC Page: GAM, SGAM and PFS
○ Consistency Errors : Cause & Solutions
○ Allocation Errors : Cause and Solutions
○ Log Space Issues and Log Rebuilds
○ Memory & TempDB Issues, Solutions
REPLICATION
○ Replication Architecture and Topology
○ Publication Types - Purpose, Importance
○ DB Articles, Publications, Subscriptions
○ Distribution DB Configuration, Snapshots
○ Snapshot Replication and Repl Agents
○ Adding Articles to Existing (LIVE) Replica
○ PUSH, PULL Subscriptions. N/W Shares
○ Transactional Replication Configuration
○ Log Reader Agent - Configuration, Keys
○ Replication Monitor - Tracer Tokens
○ Replication Monitor - Warnings, Alerts
○ Replication Monitor - Adding Articles
○ Replication Scripts, Reinitialization
○ Replication Warnings and Agent Alerts
○ Merge Replication and Merge Agent Job
○ Replication Conflicts and ROWGUIDCOL
○ Server Subscription & Client Subscription
○ Peer-Peer Replication Connections, Nodes
○ NodeID and Conflict Detection Options
○ Replication Conflicts and sp_MSRepl
○ sp_changedbowner, backup initialization
○ Replication Conflicts and Priority Settings
○ Replication Verify - Rowcount, Checksum
○ Disabling, Cleaning Replication Topology
○ Replication Strategies for HA and DR Plan
○ Replication for Load Balancing Topologies
○ SQL Server Configuration Manager Tool
○ Common Errors and Solutions
LOG SHIPPING (HA - DR)
○ Log Shipping Topology for HA and DR
○ Primary and Secondary: Recovery Plan
○ Log Shipping Monitor, Jobs and Alerts
○ NORECOVERY Mode - Configuration
○ STANDBY Mode Configuration & Jobs
○ Log Shipping Jobs and Manual Failover
○ Log Shipping Mode Changes - cautions
○ Re-Restoring Log Backups for Recovery
○ LSBackup, LSCopy & LSRestore Jobs
○ LS Job Audits, Dashboards (Reports)
○ TUF Files and Standby Options in LS
○ Broken Log Shipping Chains & Issues
○ Log Shipping Manual Fail-over Options
○ Log Shipping Configuration Precautions
○ Common Errors & Solutions
DB MIRRORING (HA - DR)
○ DB Mirroring Architecture For HA & DR
○ Log Shipping Versus Database Mirroring
○ TCP Endpoints, TCP Network Security
○ Heartbeat and Polling Concepts in DM
○ Automatic Fail-Over Procedures, Tests
○ PARTNER OFFLINE Conditions, Options
○ DB Mirroring Monitors and Commit Loads
○ SYNCHRONOUS & ASYNCHRONOUS
○ DB Mirroring and Port Configurations
○ Mirroring Monitor, Stop/Resume Options
○ Need for Always-On & Higher Availability
○ DB Recovery without Witness. Failover
○ Mirroring Monitor Jobs - Real-time Usage
○ Common Backups Errors & Solutions
PATCHES, UPGRADES, CUs
○ Maintenance: Precautions & Downtime
○ DB Backups, Scripting and Services
○ Service Packs and Patch/Hotfix Activities
○ Cumulative Updates (CU), Hotfix Process
○ Instance Selectivity for Updates, Cautions
○ Verifications, Smoke Test and Rollbacks
○ Multi Instance Updates & Port Changes
○ SERVER Upgrades & VERSION Changes
○ Silent Installation & Installation Repairs
○ Verifications, Smoke Test and Rollbacks
○ System Database REBUILDs using CMD
○ Silent Installation & Installation Repairs
○ SQLCMD Tool and Instance Connections
○ DAC : Dedicated Administration Console
SQL Cluster and Always-On
○ Detailed Implementation @ Ch 34, 35
○ Windows Clusters For HA and DR
○ Domain Controller (DC) Configuration
○ Active Directory (AD) Accounts, Use
○ SAN [Storage Area Network] and LUN
○ Public IP Address, Private IP Address
○ Windows Level Clusters, MSCS Service
○ Active-Active, Active-Passive Clusters
○ Always On Availability Group [AOAG]
○ Synchronous and Asynchronous Modes
○ Synchronization, Automated Seeding
○ Data Synchronization for AOAG
○ Backup Preferences, Location Options
No comments:
Post a Comment
If you have any doubt or question, please contact us.