Module 5 Understanding SQL Server 2008 R2 Recovery Models

1. Module 5 Understanding SQL Server 2008 R2 Recovery Models

2. Module Overview • Backup Strategies • Understanding SQL Server Transaction Logging • Planning a SQL Server Backup Strategy

3. Lesson 1: Backup Strategies • Discussion: Previous Experience with Backup Strategies • Determining an Appropriate Backup Strategy • Choosing Appropriate Backup Media • Determining a Retention and Testing Policy for Backups

4. Discussion: Previous Experience with Backup Strategies • What types of backup have you used? • How often do you perform backups? • Who is reponsible for planning and executing a backup strategy? • How often are your backups tested? • Do you use 3rd party tools for backups? • What type of backup media do you use?

5. Determining an Appropriate Backup Strategy • Different backup types can be combined • Data based Backups (Full, Differential, Copy, Filegroup, File) • Log based Backups (Log , Tail Log) • Safety levels should be determined • How long can recovering take? (RTO) • How much data is it acceptable to lose? (RPO) • Is it possible to recover the data from other sources? • Backup strategy should be mapped to requirements • Types and frequency of backups • Backup media to use • Retention period for backups and for media • Backup testing policy

6. Choosing Appropriate Backup Media • A single backup is called a backup set • Not all users can perform backups • Backup sets are written to media sets that comprise one or more backup devices • Media sets can contain several backup sets • Backup devices can be physical or logical • Physical devices are tapes or disk files

7. Determining a Retention and Testing Policy for Backups • Planning for backup retention must be part of the strategy • Should form part of the test plan to ensure accuracy • Several considerations: • Combination of backups needed for a database recovery • Archival requirements • Synchronization with database checks • Available secure storage location • Hardware required for restoring backups

8. Lesson 2: Understanding SQL Server Transaction Logging • Overview of SQL Server Transaction Logs • Transaction Log File Structure • Working with Recovery Models • Capacity Planning for Transaction Logs • Demonstration 2A: Logs and Full Recovery

9. Overview of SQL Server Transaction Logs Modification is recorded in transaction log on disk 3 Buffer Cache Data pages are located in, or read into, the buffer cache and then modified 2 Checkpoint writes dirty pages to database 4 Transaction logs provide a history of actions executed by a database management system to guarantee ACID properties Data modification is sent by application 1

10. Transaction Log File Structure • Sufficient information is logged to be able to: • Roll back transactions if requested • Recover the database in case of failure • Write Ahead Logging is used to create log entries • Transaction logs are written in chronological order in a circular way • Truncation policy for logs is based on the recovery model

11. Working with Recovery Models

12. Capacity Planning for Transaction Logs • Capacity needs are based on several factors • Recovery model used for the database • Transaction log backup frequency in full and bulk logged recovery models • Number and size of transactions in the database • Examine log behavior during pre-deployment testing

13. Demonstration 2A: Logs and Full Recovery • In this demonstration you will see how log truncation works in full recovery model

14. Lesson 3: Planning a SQL Server Backup Strategy • Overview of Microsoft SQL Server Backup Types • Full Database Backup Strategies • Transaction Log Backup Strategies • Differential Backup Strategies • Discussion: Meeting Business Recovery Requirements

15. Overview of Microsoft SQL Server Backup Types

16. Full Database Backup Strategies Sunday Monday Tuesday Full Database Backups: Backup all data and part of the log records ü Can be used to restore the whole database ü Permit recovery to backup times only ü

17. Transaction Log Backup Strategies Sunday Monday A Database and Transaction Log Backup Strategy: Involves at least full and transaction log backups ü Enables point in time recovery ü Database can be fully restored in the case of data file loss ü

18. Differential Backup Strategies Monday Tuesday A Differential Backup Strategy: Involves performing full and differential database backups ü Includes differential backups with only changed data ü Is useful if only a subset of a database is modified more frequently than the rest of the database ü

19. Discussion: Meeting Business Recovery Requirements • How would you design a backup strategy for a database supporting an online store?

20. Lab 5: Understanding SQL Server Recovery Models • Exercise 1: Plan a backup strategy • Exercise 2: Configure recovery models • Challenge Exercise 3: Review recovery models and strategy (Only if time permits) Logon information Estimated time: 45minutes

21. Lab Scenario You need to implement a database recovery strategy. The business unit from Proseworks, Inc. has provided you with the availability needs for the databases on the new Proseware SQL Server instance. You need to plan how you will meet the requirements and then implement your strategy. If you have time, there is another issue that your manager would like you to work on. There is another instance of SQL Server installed for supporting Customer Service operations. There is concern that existing databases on the CustomerService server instance are configured inappropriately and have invalid backup strategies, based on their RPO and RTO requirements. In this exercise, you need to review the database recovery models and backup strategies for the databases on the CustomerService instance and provide recommended changes.

22. Lab Review • Can transaction log backups be performed if a database is configured for simple recovery model? • Can transaction log backups be performed if a database is configured for bulk-logged recovery model?

23. Module Review and Takeaways • Review Questions • Best Practices