Sql-server – DPM backup of VM breaks SQL backup chain/timeline

hyper-vscdpmsql serversystem-center

DPM 2012R2.
Hyper-V failover cluster with CSV on Windows Server 2012R2.
Guest is also Windows Server 2012R2 with SQL Server 2014.
All latest updates, update rollups, cumulative updates and some recommended hotfixes.

Story: Each installation of SQL in VM guests have its own maintenance plan with Full, Diff and Log database backups to network share. Now I'm adding protection to VMs with help of DPM.

What happens: each time when DPM backups VMs it uses VSS which hits SQL VSS Writer, which in its own turn performs backup in SQL.

Whats bad: This VSS backup is Full backup of database with all consequences.
So now own SQL backups cannot be used because there is no way to restore SQL database from VMs VHDx in IN RECOVERY state to apply diff or log backups. There is a way only to restore mdf/ndf/ldf files and attach them or the whole VM.

As stated in SQL VSS Writer documentation, it doesn't support log backups so there is no point-in-time recovery which is unacceptable for production databases. ( https://msdn.microsoft.com/en-us/library/ms175536.aspx )

The only way I see is to make this backup copy-only.
SQL VSS writer supports it.
VSS itself supports this paramener VSS_BACKUP_TYPE to VSS_BT_COPY

But I don't see the way to tell DPM to do that.

What I tried so far:

Inside VMs guest OS

  1. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLWriter\Settings\ .
    DWORD DisableLogShrink 1 .
    Only disables Log Shrink during SQL VSS backup, but it is still a Full backup.

  2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Virtualization\VssRequestor\ .
    DWORD BackupType 1 .
    KB975354.
    If this registry entry is created and its value is set to 1, application backup will not be affected by the virtual machine backup operation on the server that is running Hyper-V.
    No effect.

On Host Server (all nodes in cluster with reboot)

  1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Agent\HonorFilesNotToBackup\
    Plus HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\BackupRestore\FilesNotToBackup\
    MULTI_SZ string with path to VHDX containing SQL databases.
    No effect.

  2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Agent\2.0\ .
    DWORD CopyBackupEnabled 1.
    ( https://blogs.technet.microsoft.com/dpm/2015/05/20/an-in-depth-look-at-the-registry-settings-that-control-microsoft-dpm-2012/ )
    No effect.

On DPM Server

  1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Agent\2.0\CopyBackups\

DWORD Name of VM in DPM with value 1.

Maybe this is it and I just don't know how to correctly write VMs name that DPM will accept.
Now it is in formant VMNAME.CLUSTERNAME.DOMAIN.COM
No effect.

Seems like my google-bing-whatever-fu is ending here.

It leaves me with few questions.
– How do I have to protect VMs with SQL with saving the ability of point-in-time recovery of SQL databases inside them using DPM?
– How do all other people protect their VMs with SQL using DPM? Using 15 minutes sync interval for the whole VM? It's a lot of space/load/traffic etc. in case of tens-hundreds of VMs

And the MAIN question:
– Have anyone been able to achieve that DPM doesn't affect SQL backups inside Hyper-V VMs in the same setup?

Best Answer

You can stop using SQL VSS writer via disabling the servie "SQL Server VSS Writer" for SQL server .

Related Topic