AlwaysOn and transaction logs

Question

I am interested in using AlwaysOn Availability Groups. I notice that it's required that you use the Full recovery mode for this. However, we aren't really interested in point-in-time backups and restores. We are quite content with nightly database backups, and today we keep all of our databases in Simple recovery mode on our SQL 2008 R2 servers.

Would there be a way for us to run Availability Groups but regularly truncate the logs without backing them up?

Thanks,
Eric

---

Eric Hodges

Answer 1

Hi,

What would be the advantage of implementing AlwaysOn if you're not interested in a point-in-time recovery and you just run once-a-day backups?

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 2

High availability without shared storage. Also, to use the secondary server as a read-only replica to reduce the workload on the primary.

---

Eric Hodges

Answer 3

OK.

Let's suppose you backup at midnight.

An accident happens at 23:59 then you switch to secondary site.

That site would have a database copy which is almost 1 day old.

Is your business OK with that?

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 4

That is a fair question. I suppose that my thinking is that if I cover for hardware failure, then most of the "accident" possibilities are covered. Note that we only have one physical site, so if something happens to it we are doomed anyway.

---

Eric Hodges

Answer 5

In my experience, many restore scenarios are because somebody did something bad in the database (like delete all customers). When this happen, you want to be able to restore to *as close as possible* that accident. I.e., you want to complement you database backups with log backups.

If you really really really don't want do save your log backups, you can do them to the physical file name 'nul', which is the binary bin.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 6

We cannot implement AlwayOn without meeting the prerequisites, and one among them is to make sure users database are in Full Recovery mode. Please review the  "Availability Database Prerequisistes and Restrictions" section in the the below KB for more details:

http://msdn.microsoft.com/library/ff878487(v=sql.110).aspx



---

Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.