I have a fairly crazy scenario where I am processing ~250 million inserts and 250 million deletes for a table that contains data that is only relevant for the moments that it is in the database. Once we get new data for that key, we delete the old and insert the new.

I am using an azure database, and we have been running with very few issues for a few years now. The problem is this: Microsoft has recently started charging their PIT backups. While I'm not opposed to them charging fair prices, it seems that I never really thought about HOW MUCH data we are storing for PIT recover for this table. Last month we had just over 8 TB in transnational logs. That means our PIT storage fees were ~$2000. I am running a small company and for us that is debilitating. Microsoft has been helpful and offered us some refunds to get us through, however the base problem still exists.

My question is this: is there anything I can do to lower my logging? From my research, I'm not sure Simple Recovery mode will solve this issue, and I don't think Azure supports simple recovery either.

I am proficient is SQL (enough to get by and optimize things occasionally), but by no means an expert. Is there anything I can do. is it possible to not log a table. Is it possible to not log a Sproc? Do I have options to reduce my Logging on azure?

As a last resort, is there another technology that would work for storing the data in this one table ( this would cause a large re-write, but for $2K a month I would consider it).

I'm a little desperate here! Any and all suggestions are welcome.

  • How long does this data stay in the database ? Minutes, hours, days ? What is your RPO (Recovery Point Objective) ie how many data are you ready to loose ? – pifor Mar 3 at 17:20
  • Some of the data stays for seconds, some stays for minutes, some days, some months. However, only the most recent update we have on the Key is valid. Thus, no recover is needed for this data. The rest of the database, I would want a point where I could recover with in the last couple of days. I am prepared to lose data for 24-48 hours if necessary ( we can rebuild that data...even if it would be work intensive) I am willing to lose PIT recovery to avoid $2000 a month in costs. We have never needed to use a backup, but I see the value in having one. – Michael Troyer Mar 3 at 17:28

Maybe you could have a look to minimal logging in SQL Server (but this requires changing database recovery model to simple or bulk-logged) and means that you cannot restore/recover database using point in time recovery (I have never used Azure so I don't know if this is possible).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.