sql server availability and recovery
TRANSCRIPT
endava.com
QUALITY. PRODUCTIVITY. INNOVATION.
Availability and recoverySQL Server
2
Availability and recovery
SQL Server
Log file settings Recovery models Truncate/shrink Backups and restore
QUALITY. PRODUCTIVITY. INNOVATION.
3
3
Log file settings
Recommendations Size log files (set initial log size, auto-growth – not use default) Log file on a different partition (for performance and recovery) Monitor the log size and create jobs and alerts to regularly manage the transaction log Using Maintenance plans to -create a backup files and Maintenance Clean-up task – to delete old
backups
VLF• DBCC LOGINFO – results count• Having too many VLFs adds overhead to both backup performance and transaction log performance
(logging)
QUALITY. PRODUCTIVITY. INNOVATION.
4
4
Recovery models
1. Simple
2. Bulk logged
3. Full
QUALITY. PRODUCTIVITY. INNOVATION.
5
5
Truncate/shrink
Truncate• Frees up used space within the file
• Occurs automatically at checkpoints, after log backups
Shrink• Shrinks space from a file – looses continuity for log transaction/backup sets
QUALITY. PRODUCTIVITY. INNOVATION.
6
6
Back up and restore
Full database
Transaction log
Differential database
QUALITY. PRODUCTIVITY. INNOVATION.
7
7
References
• Microsoft sql server 2000 high availability – Chapter 9 Database environment basics for recovery
• Different ways to restore a SQL Server database - http://www.mssqltips.com/sqlservertip/3049/different-ways-to-restore-a-sql-server-database/
QUALITY. PRODUCTIVITY. INNOVATION.
