1. 1 Production server (VMWare, Server 2008, SQL Server 2008 R2 Enterprise)
2. 1 SAN ( SQL server storage allocated 3.5TB (600GB SAS drives X 12))
3. 100 databases (avg size 25GB, largest DB 980GB, sum DB sizes 2.6TB)
SQL Server OS vmdk has image level snapshot copied to archive, to be stored offsite daily.
Backups made of critical databases: Full daily, 15 minute trans; Full weekly, daily diffs, 15 minute trans; Daily Fulls; Weekly Fulls; Monthly Fulls with Daily diffs, in order of criticality and Acceptable Loss policy.
Backups stored on seperate server (not on SAN, not virtualised, Unix)
Differences between backups copied offsite, weekly full sync of all databases.
Scenario 1: Full server failure and all database destruction.
Best way to mitigate this? Best practice?
My intention is to have a secondary server offsite in standby mode ready with latest backups restored in read-only mode (much like log shipping, but bulk imports are recorded too).
Scenario 2: Individual Database corruption of 1 or more databases
My SQL Server can restore a database to a point in time prior to detected corruption, if corruption is within Acceptable Loss policy restrictions.
If only a table is corrupted, a restored database to a different server can be used as a source to repair the affected table.
These are the two main scenarios, total server failure, and undetected corruption, and I was wondering if this is as good as it gets?
The last time we had to do a restore of one of the larger databases it took 8 hours. We used VDI devices, striped across as many stripes as we have CPU's. If I had to restore all my databases to recover from Scenario 1 it would like take more than 24 hours.
What's the best way to get the users back to work fast?
If you have had the unfortunate experience of needing to rebuild a whole server, with recovery using restores, or other ways, give us a detailed description of what went wrong (scenario), what you needed to do (number of databases, size of data) and how long it took and what hardware you had it on. Also what you do differently now that you have had this experience.
So long, and thanks for all the fishpaste ;-)