91 chapters from my SQL Server DBA handbook: tested procedures and scripts for setting up, running, tuning and troubleshooting SQL Server.
Setup, backup and high availability
- Backing Up and Restoring a Database
- Restoring a Full Database Backup (SSMS)
- Restoring a Database to a New Location
- Backup and Restore with T-SQL
- Disaster Recovery Planning
- Basic vs Advanced High Availability
- Adding a Database to an Availability Group
- Suspending and Resuming Data Movement
- Availability Group Health Troubleshooting
- Quorum Failure
- Log Shipping
- Database Mail Setup and Troubleshooting
- Moving TempDB
- Database Snapshots
- Server Build Checklist
- Encryption
- TDE: Backing Up the Certificate
- Data Obfuscation and Masking
- SQL Server Audit
- FILESTREAM
- Orphaned SQL Logins
- Replicating SQL Server Logins with Their SIDs
- Login Failures (Error 18456)
- Random Password Generator
- Generating the SPNs (Kerberos)
- SQL Server Replication: Setup, Types and Pitfalls
- Adding an Article to a Publication
- Enabling Change Tracking: Good and Bad Points
- Migrating a Reporting Services Installation (Native Mode)
- Upgrading SSRS 2005 to 2012 (Quick and Dirty)
- Azure SQL Options
- Migrating SQL Server to Azure
- Building an Availability Group
- Failover Cluster Instances and Distributed Availability Groups
- Backup to URL and Backup Encryption
- PowerShell and dbatools
- SQL Server Trace Flags
Performance
- Database Performance Tuning and Index Strategy
- Data Compression
- Forwarded Records
- NUMA
- Common Memory Issues
- Average Write Performance
- Stress Testing the I/O Subsystem with SQLIO
- When the SQL Server Cache Needs to Be Cleared
- Parameter Sniffing
- Table Partitioning
- Resource Governor
- What Happens During a Data Change: Buffer Pool, Write-Ahead Log and Durability
- Measuring Query Performance and Taking a Baseline
- Reading Execution Plans
- Plan Caching and Parameterization
- Statistics Problem or Caching Problem?
Troubleshooting
- Database Suspect, Recovery Pending or Stuck Recovering
- Shrinking Databases
- Transaction Log Full
- Data File Full and Disk Space (Error 1105)
- Shrinking Transaction Logs
- Stopping a Running Job in Job Activity Monitor
- Failed SQL Agent Job Triage
- Single-User Mode
- Tempdb Full or Contention
- Repairing the WMI Provider (mofcomp)
- SQL Server Referencing the Wrong Disk Drives
- Performance Troubleshooting Queries
- High CPU Troubleshooting
- SQL Locks
- SQL Server Error Numbers
- Monitoring Deadlocks
- Sleeping Processes
- DMVs and Session Monitoring
- Collecting Data with Performance Monitor
- Collecting a Trace with SQL Profiler
- Schema Locks and Online Index Maintenance
T-SQL
- SQL Best Practices
- T-SQL Coding Conventions and Programming Guidelines
- Common Table Expressions (CTEs)
- CROSS APPLY and OUTER APPLY
- Exporting Data to a File
- SET IDENTITY_INSERT
- OPENQUERY with Linked Servers
- Service Broker in SQL Server
- Enabling and Resetting Service Broker
- Change Data Capture (CDC)
- Extended Events
- Temporal Tables
- DDL and DML Audit Triggers
- Transaction Modes: Autocommit, Explicit and Implicit
- Error Handling in Transactions: TRY/CATCH, @@TRANCOUNT and XACT_STATE()
- Read Consistency: Read Committed, RCSI and Snapshot Isolation
- T-SQL Performance Anti-Patterns