Hello,
First of all I am not really sure where to post my question. From my Veeam account I am linked to the R&D forums. But there it says ‘Not a support forum!’. So I ask it here.
Is it possible to backup the transaction logs of an SQL server running on an Azure VM with Veeam Backup for Azure ?
I can enable application-aware snapshots but where and how do i select the applications to protect?
Thanks,
Chris
+7
Hello
You want to backup periodicly the SQL transactions logs ?
For me it’s currently not possible except if you install a Veeam Agent for Windows on the VM.
Enable application-aware processing ensure that the applications will be able to recover successfully, without data loss, but you cant choose the application, by default Veeam does not process application logs and creates a crash-consistent backup of VMs with applications that use transaction logs for operations. You can create a transactionally consistent backup - in this case Veeam Backup & Replication will process application logs.
+17
Hi
+17
Hi
+20
Hi,
That’s correct, currently because of the method of processing the VMs (VBfMA uses the service bus) there’s no direct communication between the components that (currently) supports this.
You could deploy Veeam Agent for Microsoft Windows via VBR to protect these servers as
I’d suggest looking into the second approach because this way you still benefit from the snapshot + backup functionality natively via VBfMA, but you can then backup & restore your VM-based databases and transaction logs, via the plug-in. There are pros & cons to each, to summarise those I can think of:
Veeam Agent for Microsoft Windows:
Pro:
Is supported for Azure VMs via the Cloud Machines agent functionality (Backup of Cloud Machines - Veeam Agent Management Guide)
Gives you full server backup + transaction log shipping via a single, simplified job
More efficient vs having a full server backup via VBfMA + another database backup via Veeam Plug-In for Microsoft SQL Server
Can use Veeam Explorer for Microsoft SQL Server
Can backup directly to object storage
Con:
If you back up the server with this + VBfMA, you’re creating yet more backups.
It’s an in-guest backup which could cause issues with VSS limitations for disks (64TB+), if you replaced VBfMA with this, you lose the possibility of server snapshots
Veeam Agent increases CPU & RAM requirements as the backup process takes place within host vs VBfMA
Veeam Plug-In for Microsoft SQL Server:
Pro:
More powerful functionality, in-guest backup & restores are possible, cross database backup & restores are possible
Allows VBfMA to protect your VM instance, whilst providing dedicated backup for your SQL instances
Con:
Can’t use Veeam Explorer for Microsoft SQL
Additional space consumed via two database backups (1 via VBfMA, 1 via plug-in)
Can’t directly backup to object storage
Hi coolsport00 and MicoolPaul,
thanks for your advice!
As I am new to Veeam I do not quite understand the difference between the Veeam Agent for Microsoft Windows and the Veeam Plug-In for Microsoft SQL Server.
We have some SQL servers running on Azure Windows VMs. The VMs are shut down every night. Our idea is to take a full backup of these VMs every night when the are not running. During the day we just want to backup the transactions logs from the SQL server.
Could the Veeam Explorer for Microsoft SQL Server then combine these two backups to restore the database as a whole or parts of it like tables?
Or is it just working using the Veeam Agent for Microsoft Windows? Or not at al?
Or would I first need to restore the whole VM from the image and then apply the transaction logs from the Veeam Plug-In for Microsoft SQL Server?
+7
Hey
Veeam Agent for Windows is a data protection and disaster recovery solution for both physical and virtual machines. This solution can operate independently or be managed by a Veeam Backup Server (excluding Veeam Backup for Azure). It offers a wide array of functionalities, including the capability to back up transactional SQL logs.
Regarding the Plug-in aspect, it necessitates having a Veeam Backup & Replication environment initially. Do you have this setup, or do you exclusively possess Veeam for Azure? In my understanding, the plug-in allows DBAs to utilize Veeam components to manage their backups. With the Plugin, you can restore a database to the same or different Microsoft SQL Server instance. If you have backup of Microsoft SQL Server transaction logs, you can specify a point in time to which you want to restore the database. Otherwise, Veeam Plug-in will restore the database to the time when the restore point was created.
Find more info here: https://helpcenter.veeam.com/docs/backup/plugins/mssql_plugin.html?ver=120
As a non-expert in databases, in your situation, you could potentially use native MS SQL components to back up your logs during the day, and employ Veeam for Azure to take snapshots, creating a visual of the logs from the Veeam perspective.
+20
Hi
There’s one major issue with your plan (but that’s okay! Let’s work it out now!)
As you want to do transaction log backup, I’ll presume your database is set to full instead of simple recovery mode.
If you’re backing up the server when it’s offline, at no point is your backup application saying to your database ‘Hey, I just did a backup, you can disregard all your transaction logs up to this point now’.
This is an important process because your log file will endlessly grow, as it needs to replay its transaction logs from its last database for recovery, which because it’s never told it’s backed up, could be a LONG time ago.
Lets explore this point then hopefully come up with a way forward for you 😊
Hi Stabz and MicoolPaul,
Thanks for your input!
I am testing with Veeam Backup for Azure plus a Veeam Backup & Replication environment.
If I would backup the server when it is online would Veeam then via the Windows agent or the SQL server plugin be able to reset the transaction logs deleting all logs made before the backup?
Or would this just be possible after a SQL server full database backup?
Disregarding the growing transactions logs could Veeam Explorer for Microsoft SQL Server apply the necessary transactions logs to a VM restored from an offline backup?
+20
Hi,
Thats correct you’d be able to notify the database instance that it’s been backed up and therefore the transaction logs would become white space. But crucially it won’t auto shrink, you’d have to shrink the log file yourself. Word of advice, don’t shrink the log file as far as it can go if the log file is going to grow a lot again. Shrink it to an expected size to cover your expected consumption between backups (even if you’re doing transaction log shipping it’s good to have enough space to cover a failure of that component).
And yes, Veeam let’s you restore from the latest image level backup, it also lets you restore back to that and automatically apply transaction logs up to a specific time (with transaction log shipping functionality enabled), and then finally you can use the “fine tune” option to select specific transactions within the date/time selected to allow you to revert to just before a disaster! As for your specific scenario of an offline backup, I don’t believe so, Veeam needs to be integrated into the database which requires the server running
Thanks MicoolPaul!
I should set up a test machine and try the different scenarios out.
+20
Hope all goes well and as always, we’re here to help if needed!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
