Database Administration - Backup/Recovery Planning -

You need to implement a database recovery strategy. The business unit from Proseworks Inc. has provided 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.

There is another instance of SQL Server installed for supporting customer service operations. Your manager is concerned that existing databases on the CustomerService server instance are configured inappropriately and have invalid backup strategies, based on their RPO and RTO requirements. You need to review the database recovery models and backup strategies for the databases on the CustomerService instance, and provide recommended changes.

Business Database Continuity Requirements for Databases on the Proseware Server Instance

Recovery Time Objectives (RTO):

• The MarketDev database must never be unavailable for longer than eight hours.
• The Research database must never be unavailable for longer than two hours.

Recovery Point Objectives (RPO):

• When the MarketDev database is recovered from a failure, no more than 30 minutes of transactions may be lost.
• When the Research database is recovered from a failure, all transactions that were completed up to the end of the previous weekday must be recovered.

Projected Characteristics

Characteristic	Estimated Value
MarketDev database size	20 GB
Research database size	200 MB
Total backup throughput	100 MB/minute
Total restore throughput	80 MB/minute
Average rate of change to the MarketDev database during office hours	1 GB/hour
Average rate of change to the Research database during office hours	10 MB/hour
Percentage of the MarketDev database changed each day (average)	1.2%
Percentage of the Research database changed each day (average)	80%
Office hours (no full database backups permitted during these hours)	08:00 to 18:00

Business Database Continuity Requirements for Databases on the Customer Service Server Instance

Recovery Time Objectives:

• The Credit Control database must never be unavailable for longer than two hours
• The Potential Issue database must never be unavailable for longer than one hour.

Recovery Point Objectives

• When the Credit Control database is recovered from a failure, no more than five minutes of transactions may be lost
• When the Potential Issue database is recovered from a failure, no more than 30 minutes of transactions may be lost.

Projected Characteristics

Characteristic	Estimated Value
Credit Control database size	20 GB
Potential Issue database size (at the start of each week after archiving activity is complete)	200 MB
Total backup throughput	100 MB/minute
Total restore throughput	80 MB/minute
Average rate of change to the Credit Control database during office hours	500 MB/hour
Average rate of change to the Potential Issue database (constant throughout the week, 24 hours per day)	10 MB/hour
Percentage of the Credit Control database changed each day (average)	60%
Percentage of the Potential Issue database changed each day (average)	50%
Office hours (no full database activity permitted during these hours)	08:00 to 19:00

Existing Backup Strategy for Credit Control Database

Recovery Model: Full

Type of Backup	Schedule
Full	Saturday 06:00 Wednesday 06:00
Differential	Sunday 22:00 Monday 22:00 Tuesday 22:00 Thursday 22:00 Friday 22:00
Transaction Log	Every 60 minutes on the hour

Existing Backup Strategy for Potential Issue Database

Recovery Model: Full

Type of Backup	Schedule
Full	Sunday 22:00
Log	Every 15 minutes, starting at 10 minutes past the hour

Complete the following:

1. Complete a backup strategy recommendation for the Proseware server instance.Explain why you chose the recovery models you selected and how they help address the business requirement and need. To meet the business requirements, each database might need more than one backup type and schedule.

Include the following:

• The recovery model that should be used
• The type of backups that should be used
• The backup schedule