Allow Recovery Model to be changed to SIMPLE in Azure SQL Database
According to this article (https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/12/28/lesson-learned-50-adding-pk-led-to-transaction-log-full-error/) the recovery model of a Azure SQL Database cannot be altered from the default option FULL. In a very data intensive setup, this blows up the transaction log -> the LOG IO percentage in portal is at 100% all the time. Allowing the SIMPLE recovery model should improved the performance significantly.
I just now noticed this isn't a feature. Looking to migrate some data loads. Obviously saving this for the Azure Data Warehouse product...
The small DWH is exactly where we could benefit from it too.
I wonder how that would impact the Azure SQL SLA. So I'll give it a vote :)
A workaround for anyone having to deal with rising transaction log costs, reduce PiTR to 1 day and introduce weekly long term retention backups. Be mindful of your RPO.
Alberto Ramon Portoles commented
Allow recovery model = Bulk logged will remove the insert bottleneck
I was shocked to find I could not do this.
I completely agree. This is a no brainer. Using Azure Synapse Analytics in place of Azure SQL Database will be simply too expensive for small scale data warehouse requirements. I would love to have an option to make recovery model SIMPLE on Azure SQL Database.
I'd like to see this too. We have just spent a lot of money moving from a managed service to Azure and now our reports (which do a lot of read/writes) take twice as long.
Azure SQL is sufficient for smaller BI environments. Azure DWH would be an overkill (features, pricing).
Therefore Simple Recovery Mode is a MUST for such BI environments. Especially for its ETL Processes loading/transforming data volumes every day.
I also would like to see this happen
Hennie de Nooijer commented
Yes, please. IMO is full logging not acceptable for DWH ing. SQL DW is not an option for a couple 100 GB's (<1TB) of data.
Mike Shih commented
Would love to see this feature as well. We lose a lot of flexibility on which service tiers we can be on because of the log I/O bottlenecks.
Naveen Kumar Thummalapenta commented
May be considering this feedback would be definitely be an additional asset to Azure SQL Offerings.
Benjamin Weber commented
This is very crucial. In the current situation, Azure SQL DB is not usable for BI without extremely high costs.