SQL in Full Recovery model replication / backup

[Kazz]

Hi,

Our SQL database is currently in simple recovery model. We are replicating SQL VM to a DR site using Veeam 9.5u4. When checking Application Aware processing options, we don't see a SQL tab. Is it not possible to backup sql logs with a replication job?

We have a new business requirement to switch to a full recovery model in SQL for more granular restore options. Do we have to switch Veeam job from replication to backup to support SQL log backup?

Thanks

[Mildur]

I don‘t think, that it‘s possible todo sql replica. There is no need todo that in a replica job.

You can create a replica every 15 minutes for most updated data on second site. It will only replicated changed vmdk blocks.

With application aware enabled, your sql server data is in a consistent state on the replica vm/site.
You can use simple or full recovery model for this scenario.

[Kazz]

Once we switch to full recovery model in SQL something needs to backup / truncate the logs. If Veeam can't do it in a replica job, I think we have two options here.

Switch from replica to backup job so we can take advantage of Veeam SQL processing or use a 3rd party (https://ola.hallengren.com/) or native sql log backup job.

Is anyone using veeam to backup sql logs in full recovery model? Would like to see you comments

Thanks!

[Mildur]

We are using veeam to backup vms/agents with ms sql. Log truncation is working perfectly with Veeam Backup Job.

Additionally, If you have a Full Recovery Model, you can use Log File Backup between two „veeam backup job“-Restore Points. This gives you the possibility to restore more granular (point-in-time restore of sql data)

https://helpcenter.veeam.com/docs/backu ... ml?ver=100

[Kazz]

We are replicating to a remote site, which usually take about 2 hours and does not give us an option to do replication every 15 min. Looks like we have to change the job to backup if we want to get transaction logs backed up every 15 min.

[pkelly_sts]

Bear in mind that replication is normally considered differently to backup (at least in my mind!). Replicas are normally a point-in-time copy of an original VM, but the creation/update of that replica copy of a VM does not impact/change the original VM in any way. Normally log truncation is done as part of backup rather than replication - e.g. if you had an issue with your original SQL server and wanted to roll back an hour, you wouldn't (normally?) do so from a replica of it, you'd do so from a backup?

[Mildur]

if you had an issue with your original SQL server and wanted to roll back an hour, you wouldn't (normally?) do so from a replica of it, you'd do so from a backup?

Hi pkelly. You are correct. Log Truncation is only possible with a backup job.
But you can do restores of sql databases for rollback scenarios with replicas too.

Veeam has the possibility todo restores from a replica vm. Application Restore is also working from a replica.

https://helpcenter.veeam.com/docs/backu ... ml?ver=100

You can use Veeam Explorer for Microsoft SQL to restore databases from any successfully created backup or replica of a virtualized Microsoft SQL Server. The backup or replica must be created with application-aware processing enabled and the corresponding options turned on.

[pkelly_sts]

Yes I'm aware that you can do restores from replicas but I'd still argue that it perhaps isn't always the first approach one would take and based on the fact that TLs can't be managed as part of replication then you probably won't have the granularity available in a replica that you would have in a backup?

[Mildur]

I‘m on your site.

Backups Jobs are better for database backup and restore purposes.