SQL Assignment 2- Translation from Sandranese
--
Introduction
|
Video Ezy in Rockingham has been destroyed by a fire caused by improper cooking of pumpkin soup. The business’s computer system was backed up on a daily basis. However, all data was held on site in a non-fireproof safe.
Prepare a comprehensive report that covers all of the following issues:
• A range of backup and restoration methods based on organisational and security requirements.
• Full offline backups, completed to organisational and security requirements with minimal downtime.
• Online file backups completed to organisational and security requirements with minimal downtime.
• A database baseline with and without archiving, determined by organisational requirements.
• Fully recovering the database to the baseline with minimal downtime.
• Database recovery without loss of committed transactions, so there are no rollbacks.
• Point of failure mitigation.
• Creating or preparing a standby database to meet organisational requirements.
• Implementing a standby database to support critical business functions.
• Documenting a standby database.
Prepare a report to discuss what Video Ezy could/should have done.
|
Determine Database Backup methods
-list of backup/restoration methods (and ways to minimalise downtime), with brief description of each (with advantages/disadvantages).
-in depth analysis of offsite storage scenarios (purpose, advantages, disadvantages).
-in depth analysis of onsite storage scenarios (purpose, advantages, disadvantages).
-other precautions. eg. removable hard-drives, fire proof storage, etc (purpose, advantages, disadvantages).
Determine Baseline and Recovery Procedures
-what could be the organisational and security requirements for a place like Video Ezy?
-plan possible baseline and recovery procedures for Video Ezy (with and without archiving).
-How could you recover a database in relation to the planned baseline?
+with minimal downtime
+without loss of transactions (no rollbacks)
(ie. creating, preparing, implementing, and documenting a standby database)
-Discuss the points of failure in this plan, and how it fares to the old point of failure (better or worse?).
Conclusion
|
Out of everything you just explained, what would you recommend for Video Ezy?
--
List of backup/restoration methods:
|
Backup
-Full backup
-Copy backup
-Incremental backup
-Differential backup
-Transaction log backup
-File and filegroup backup
Recovery
-Simple (SQL Recovery Model)
-Full (SQL Recovery Model)
-Bulk-logged (SQL Recovery Model)
--
List of things to take into account
|
What type of databases are being backed up?
-Master database, crucial for everything but doesn't change too often once set up.
-Other databases contain customers and monetary amounts and rental time that change often.
-Video database.
-Company staff and payroll databases also exist.
How important is the data in the database?
-Databases containing monetary aspects are very important (this would be most databases).
-The video database would also be very important.
-Databases relating to rental time and returns wouldn't be as important as the video database since you can work out what videos are missing using that video database.
-Company staff databases wouldn't be as important as the customer databases.
How often are changes made to the databases?
-Rental and monetary databases would change the most.
-Customer databases would change the second most.
-The video database would only be changed when new/old videos are purchased/sold.
-Staff database would only change with the staff- but relating databases like their payroll might change often.
How quickly do you need to recover the data?
-Needs to be working during work hours at least.
What's the best time to schedual backups?
-Off hours for mostly non-changing databases.
-Slow databases like Videos and Staff can be backed up weekly.
-Master database should be backed up whenenver it's changed.
-Fast databases should be backed up continually if possible, or frequently (perhaps during non-peak hours), or at the least daily.
--
Bibliography
|
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
http://en.wikipedia.org/wiki/Backup#Lore
http://en.wikipedia.org/wiki/Off-site_Data_Protection
http://en.wikipedia.org/wiki/Disaster_Recovery#Preventions_against_data_loss
http://searchstorage.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid5_gci1248183,00.html
http://www.pcguide.com/care/bu/methodHard-c.html
--