1

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?

Steven Lee - MSFT
  • 713
  • 4
  • 8
Alkochm
  • 11
  • 5

2 Answers2

0

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

Elton Ji - MSFT
  • 281
  • 1
  • 3
  • Thanks, I thought about that. But one thing that stops me - is that exact same problem is solved by KB975354 and KB975925, and somehow Windows Server 2012/R2 is left out from this. Also I'm vorried about consistency of such backups - if SQL VSS Writer is not used, than database files will be locked for read OR database restored from them will not be consistent. So I'm more looking for a right way to do such backups with minimal damage considering that this should be very common situation. – Alkochm Sep 23 '16 at 16:16
  • Registry entry "BackupType" controls whether Applications' VSS Writers will be ignored by hyper-v vss writer (It equals to disable ALL applications' VSS writer ) . So , I suggested to disable only SQL VSS writer . As a result you need to use SQL backup for your DB . If SQL VSS writer was ignored/disabled , VM backup is SQL-Inconsistent , you need to restore SQL DB from SQL backup additionally after restoring SQL VM to achieve disaster recovery . – Elton Ji - MSFT Sep 25 '16 at 04:39
  • Thx, I think I'll do this if nothing else helps. But I'm looking for normal way where whole VM backup will be consistent and it is normal because exact same problem solved in other backup solutions by VSS COPY ONLY mode of backup. - in Veeam: https://forums.veeam.com/microsoft-hyper-v-f25/sql-server-differential-backup-chain-broken-by-veeam-t27134.html - in Symantec backup exec: http://arstechnica.com/civis/viewtopic.php?t=1213095 - Even for DPM 2007 in MS KB970867! This allows SQL backup to be not affected and VM backup be consistent at the same time. – Alkochm Sep 25 '16 at 11:45
0

Well. I finally did it.

What really worked for me:
On Host Server
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Agent\2.0
DWORD CopyBackupEnabled 1
That really made VSS backups copy-only, but they are still logged in backuphistory (somehow I didn't expect this and overlooked that it actually works).

Secondly, by default in guest OS WS2012R2 logs are not truncated, but oppositely in 2008R2 they are truncated by default.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Virtualization\VssRequestor DWORD BackupType 1
Has no effect on SQL, but HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLWriter\Settings\ DWORD DisableLogShrink 1
does help
Log truncation is checked by fn_dblog() or by log size.

Additionally,
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
Working not as expected.
VHDX files are still affected by VSS when these keys are used, but they are excluded from resulting backup. So, if using these keys be aware, that SQL will log this backup and may truncate logs even if VHDX with DB is excluded (or if just database files is excluded).

And at last, DPM Server key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Agent\2.0\CopyBackups
DWORD Name of datasource
Not affecting VM backups and looks like that sharepoint config database is the only target for this key

Alkochm
  • 11
  • 5