Recovery Point Objective (RPO) is one of the main DBA concerns in creating a Backup policy or disaster recovery plan. The RPO is the acceptable last point in time for recovery. Once established , the DBA creates backup procedures to protect the RPO.
Data sets are growing. Requirements for increased Production data availability are expanding in the 24 x 7 world. But amongst all this change – there still remains the critical demand for the DBA to support the agreed Recovery Point Objective (RPO).
I’m always looking for ways to offload the backup workload . During the backup window, server performance degrades. I’ve recently been researching Flash Copy Manager. These are some scenarios \ thoughts \ issues requiring investigation.
When investigating new Backup and Restore products and how they may apply to an environment, simple or obvious questions can sometimes elicit responses revealing gold mine information. I’m never scared to ask the obvious question.
Scenario 1 – Data corruption and FlashCopy Manager
Can we complete a data recovery of an older level of data , for example, due to a logical application error? Would that be at a database level or drive level?
Scenario 2- ETL and FlashCopy Manager
Can Flash Copy of data be restored onto another server, with another server name, security and supporting files, maintaining consistent transactions and be available for read\write?
Is there a documented process for Instant restore of volume process?
Scenario 3 – High Availability and FlashCopy Manager
If the SQL Server is set up in an AlwaysOn configuration i.e high availability databases – do the nodes on TSM need to be set up differently.?
Is the snapshot transaction consistent across all the drives?If data and transaction log files are stored across different drives
If any Production DBAs have Flash Copy Manager experience in a Production Database Server environment, I’d like to hear from you. Your feedback will assist me to assess Flash Copy manager
“Assumption is the mother of all xxxx ups” is a great quote from Under Siege 2. It summarises perfectly assumptions about last known good backups.
An application owner contacted me this week. He requested an immediate Restore of 3 databases from a specific date ,he’d corrupted during an application install. I’ve never had a problem restoring from this backup system, but like any system it requires rigorous governance and testing rigour.
Regular review of backup retention policy
A recent retention policy adjustment changed the last active database setting. In theory , based on the documented details of the retention policy, those backups should have been available. But they weren’t.
We recovered the system in a different way, but the point is still relevant about checking for last known good backups.
Moral of the story : regardless of how incredible your backup system, allow for reporting on databases that don’t have a backup suitable to Recovery Point Objective (RPO).
When a database is in Simple Recovery Mode , log space is automatically reclaimed when a transaction finishes. This means log space management is not required .
In the event of database loss , only data from the last back is recoverable. Any data since the last backup is lost, and needs to be recreated.
If the recovery Point required is different, change the recovery mode to FULL and take log backups. Regular log backups will ensure a data file loss does not mean a catastrophic failure, but only data loss up to the last log backup. Recovering a database to a specific point in time is critical to some applications , such as financial data systems.
I review databases as part of my daily routine, I check for databases in Full Recovery Mode with no log backups. I speak to the application owner to establish the recovery point. If the recovery point is last full backup , there is an option to use Simple Recovery . If the recovery point after the last full backup , a log backup schedule is set up to support the recovery point