SQL Server Performance

Simple or Full?

Discussion in 'Getting Started' started by boct, Jan 11, 2011.

  1. boct New Member

    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
  2. Luis Martin Moderator

    If roll back to last day or...., is reasonable to you and your customers then why not?
  3. boct New Member

    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!
  4. Luis Martin Moderator

    What about to T log backup more frequently?. Said, each our or less.
  5. boct New Member

    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?
  6. Luis Martin Moderator

    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.
  7. satya Moderator

    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.
  8. boct New Member

    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
  9. satya Moderator

    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.
  10. RamJaddu Member

    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
  11. boct New Member

    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
  12. satya Moderator

    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.

Share This Page