MS SQL Server DBA - Interview Preparation (Part 1)
1. Core Administration
• SQL Server Installation & Configuration (default vs named instance, collation, service
accounts).
• Database lifecycle management – creation, altering, detaching, attaching.
• Understanding SQL Server architecture (Buffer Manager, Transaction Log, Checkpoint,
LSN).
2. Backup & Restore
• Full, Differential, and Transaction Log backups.
• Point-in-Time Recovery, Tail-log backup, Piecemeal restore.
• Scripts: BACKUP DATABASE [DBName] TO DISK='C:\Backup\DBName.bak';
RESTORE DATABASE [DBName] FROM DISK='C:\Backup\DBName.bak' WITH
NORECOVERY;
3. High Availability & Disaster Recovery (HADR)
• Always On Availability Groups (setup, failover, troubleshooting).
• Log Shipping, Database Mirroring, Replication.
• Key Terms: RPO, RTO, quorum, synchronous vs asynchronous replication.
4. Performance & Troubleshooting
• Indexing strategies: clustered, non-clustered, filtered indexes.
• Query tuning using Execution Plans, Statistics.
• Common issues: - Blocking/Deadlocks: Use sys.dm_exec_requests, Extended Events. -
TempDB contention: add multiple data files, optimize queries. - Parameter sniffing: use
OPTION(RECOMPILE) or optimize indexing.
5. Security
• Logins, Users, Roles, Permissions.
• Contained Databases, Row-Level Security, Dynamic Data Masking.
• Scripts: CREATE LOGIN [testuser] WITH PASSWORD='P@ssw0rd'; ALTER SERVER
ROLE [sysadmin] ADD MEMBER [testuser];
6. Maintenance & Automation
• SQL Agent Jobs, Alerts, Operators.
• DBCC CHECKDB, index rebuild/reorganize, statistics updates.
• PowerShell Example: Invoke-Sqlcmd -ServerInstance 'SQLSERVER01' -Database
'master' -Query "BACKUP DATABASE [TestDB] TO DISK='C:\Backup\TestDB.bak'"
7. Real-World Troubleshooting Scenarios
• TempDB Full: - Identify usage with sys.dm_db_file_space_usage. - Add additional data
files, restart service if critical.
• Blocking Queries: - Identify blockers using sys.dm_exec_requests and
sys.dm_tran_locks. - Resolve by killing blocker SPID if necessary.
• AlwaysOn Sync Issue: - Check synchronization health in
sys.dm_hadr_database_replica_states. - Validate endpoint and network connectivity.
8. Interview Questions (Beginner to Advanced)
• Explain the difference between Full, Differential, and Transaction Log backups.
• What steps do you follow to troubleshoot a slow-running query?
• How do you configure and monitor Always On Availability Groups?
• Scenario: A critical database is in suspect mode. What do you do?
• How do you ensure security compliance in SQL Server environments?
