Windows Server 2008 (Web edition) MS SQL Server 2008 (standard) DotNetNuke with half a dozen parent portals all tied to SQL server running webs and CMS Current status with regard to backups et al database size about a 1 GB - Running database as FULL - Full weekly database backups - Daily differential database backups - Several daily Transaction Log backups - Clean up database (full and diffs) backups, delete over 1 month Transaction Logs are a pain .. big, bloaty and I think typically unnecessary for my case. Question: If I change my plan to this .. good ?? - Run as SIMPLE - Full weekly database backup - two daily differentials backups (7am, 1pm) - Clean up to realize two week window (always have two latest full backups going back two weeks) - Clean up diffs by keeping no older than one week On crap storm we can always roll back to last day or this morning or afternoon .. correct?? And this will keep the Tran-log file ( now pretty well redundant in this scenario?? ) to a little piggy ..correct? Any insight would be appreciated .. even laughter (easy on the ridicule though) B
Hey Luis.. yeah, that's why I was thinking granulating the differential backup twice during normal business hours instead of once daily.. the database run several domain websites dynamically in addition to offering content management for client with those domains.. because every http request (pretty well any activity from a web user) will result in a transaction to the database .. that's ends up being a lot of erroneous, non-critical transactions ( I think ?? ) that would never require restore or rollback...and therefore crazy transaction log size relevant to mission critical... but I am not absolutely certain that Simple is the best way to go and maybe this scenario IS better suited to Full with T Log backup and all .. perhaps I need to look further into best management of Transaction Log .. filtering parameters perhaps..gulp!
hey Luis yes, I settled on hourly T Log backups between 7am and 6pm and am only keeping one day's backup so .. here is what I have: keeping only two weeks full database backup with corresponding differentials keeping one days allotment of T Log backups (about 10 for each day ) results in this possible scenarios: Restore to any point in time for current day or previous day Restore to any day of current week (back to Sunday 2AM) longer than 2 days ago Restore to current week start (Sunday past) or previous week start (Previous week Sunday) Now the problem is, because each domain (separate clients) is stored under the one database .. if we need to rollback for one client then ALL changes will be lost for others ... so I will not be offering any restore functions for clients and only for catastrophic reasons . For this, I think I have enough (more than required) granularity .. what do you think?
Because we wrote what we think in one day, I would like to wait for others members opinions. I'm sure tomorrow more than one will give some expertise.
Welcome to the forums. As I presume from above you are runnign DotNetNuke portal that uses the SQL database to store the portals information for your customers. You want to provide a point in time recovery or DR strategy to keep up the data availability. In this case as the data for all customers are stored in one databases, any recovery of transaction log will have impact on other customer data too. You have two options either you have to divide each customer into 1 database or perform a script based update on the database to correct the discrepancy on any customer. With regard to recovery strategy using log shipping or database mirroring *(having 2 servers in different data centers to handle) will do, provided you have to manage the database using FULL recoveyr model.
Thanks .. this is a great resource site .. Yes, this is certainly a caveat to what is a great CMS platform otherwise .. some common directives from experienced DNN managers is to create separate DNN installs (separate app pools et al) for each domain and therefore separate databases .. I am considering another possibility in that DNN provides an export-import routine by/for per portal where I may be able to schedule export per portal (sort of a specific granular backup of portal specific data ). Or as suggested; in case of failure perform a rollback to time of good .. export the portal that has discrepancy ... roll forward to current .. and import portal data ... ha, are we having fun now! Either way administration/management is always prevalent with no real reduction or alleviation of tasks .. consolidated admin or granular admin .. there will always be a need for an admin b
See this article http://msdn.microsoft.com/en-us/library/ms191253.aspx on restore and recovery strategies to follow. In your case because of single-point installation of databases, it may not be easy to follow the usual methods. Still it is possible to provide the availability if you have followed best practices. If you wish you can get in touch with me offline for further review of your platform/strategy.
Hi there, Your database size 1GB I would suggest go for full recovery model. Schedule every day one fullback and couple(hourly) transactional backups. This provide you to do point in time recovery. If you have enough free space you can keep last 2 weeks backups. As Satya suggested, if you have 2 servers in different data centers you can go for data mirroring or logshipping Cheers
Thanks RamJaddu Similar to what I have currently in place Full (weekly) Diff (Daily) TransLogs (hourly 7am - 6:10pm) on a one month window I could do a full daily and drop the Diffs .. I also backup the complete backup sets to another server at a different physical location daily.. not quite the same as you suggest but still another degree of redundancy. 'flying by the seat of your pants .. with a diaper (nappy)' B
If you are managing the backup schedule on daily basis without having any issues thats fine, but make sure to test those backup by restoring them to ensure no corruption of files. Better assured....before be sorry []..and one of the best practice too.
