0

Trying to get a sense of the optimal setup in our environment.

I have a large SQL05 DB (500+GB) that is log shipped to a read-only system over a WAN. I'm currently running in FULL recovery model, with log dumps every 5 minutes during peak hours and 15 minutes off hours.

It's my understanding that BULK_LOGGED recovery model will allow me to maintain log shipping integrity, at the cost of not being able to do a point-in-time recovery to a time within the period covered by the backup.

Given the frequency of our log dumps, does it make sense to run FULL at all -- since I can restore with the BULK_LOGGED log dumps to a 5-minute recovery window? Or do I misunderstand how this works?

Zoredache
  • 128,755
  • 40
  • 271
  • 413

1 Answers1

1

Is the log shipping for reporting or for BCP/DR mostly?

If reporting, yes. If for BCP/DR, then it depends on the requirements (ie what data loss is business prepared for)

Have you looked at DB mirroring? This allows you full backups on the primary and "high performance" allows lazier writes to the mirror which may achieve the same result. Log shipping is a manual failover process anyway, so you lose nothing with this mirroring mode.

gbn
  • 6,009
  • 1
  • 17
  • 21
  • Log shipping is for BCP/DR. As for what we're prepared for -- well, if BULK_LOGGED won't give us a database that can "step in when absolutely required", it won't work for us. DB mirroring will not work in our environment (too many tables with too many columns, or so I'm told). –  Jan 18 '10 at 22:30