Discuss prevention strategies, best practices, and disaster recovery plans that should be in place to help a DBA avoid major issues. Strategies for recovery when an issue does arise including Sandbox database creation and management.
Upon completing this Aeries conference session, attendees should be familiar with:
- Disaster Recovery Plans (RPO/RTO)
- Recommended Backup Plans and Schedules
- Outside resources for SQL Servers
- Sandboxing and setting up a version of Aeries for restoring data
Disaster Recovery Plans (RPO/RTO):
The first step to recovering from a data outage actually happens long before the outage. Having a strong disaster recovery plan in place can make any DBA feel more confident. Testing of these Recovery Plans can help to determine your RPO and RTO and is essential for success in the case of a true outage.
A Recovery Point Objective (RPO) is the amount of data you are willing to lose.
The Recovery Time Objective (RTO) is the amount of time you are willing to be down.
Recommended Backup Plans and Schedules:
Backup Plan schedules can vary widely depending on the needs of a District.
Some of the more common schedules we have seen and assisted with can be seen below;
Weekly Full Backup, Daily Differential Backups, and Hourly Transaction Log Backups
Daily Full Backup, Hourly Differential Backups, and 5 minute Transaction Log Backups (To obtain a 5 minute RPO, there must be Transaction Log Backups taken every 5 minutes)
It is recommended to do regular Full Backups of your 'master' and 'msdb' databases as well.
Outside SQL Resources:
Below are some helpful outside SQL resources;
A Sandbox database can be used for many different functions. Testing major database changes and recovering data are two common uses. The video below has a helpful walkthrough demonstrating the creation of a Sandbox;
Once a Sandbox database is in place, it is recommended practice to rename LOC to make the database easily identifiable in the Aeries Web Interface. The query below can be used to update LOC or reverse the change;
-------Update loc names for sandboxes update loc set nm = 'Sandbox - ' + nm where nm not like '%sandbox%' and db_name() like '%sandbox%' --Reverse it update loc set nm = replace(nm, 'Sandbox - ', '') where nm like '%sandbox - %' and db_name() like '%sandbox%'