Solved

DPM backup of VM breaks SQL backup chain/timeline

Posted on 2016-09-22
15
50 Views
Last Modified: 2016-10-05
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)
3. 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.

4. 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
5. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Agent\2.0\CopyBackups
DWORD Name of VM in DPM.
Maybe this is it and I just don't know how to correctly write VMs name that DPM will accept.
Not 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?
0
Comment
Question by:Alexey Kochmarskiy
  • 8
  • 4
  • 2
15 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41812189
I'm not a DPM expert but wondering why do you need 2 backups method (SQL native & DPM) ?
Usually you should chose only one method and I think that's why you're having issues.
0
 

Author Comment

by:Alexey Kochmarskiy
ID: 41812239
DPM is used to protect VM (from the Hyper-V host).
SQL native backup is used to achieve desired convenience and protection with SQL backups.
For example, to restore SQL database to desired time using DPM backup of the whole VM:
- I have to run VM backup very often, like every 15-60 minutes. Multiplied by the number of VMs (up to 100) and time that it takes to make one sync of VM - backup will be running 100% of time. Not mentioning the space it will take and load.
- I will be only able to recover database files itself and attach them. Not usual restore database operation.
- Person, who performs the recovery of database will have to use DPM self service tool
- There is no way to perform point-in-time recovery of database using only Host level backups of VM which can not be afforded for production databases
- Even if I will install DPM agent inside VM guest OS and will make SQL backups with it, Host level backup still will affect backup timeline

And all of this compared to familiar to everyone method of backuping databases with full-diff-log model, achieving point-in-time recovery with minimal resources and simple SSMS GUI.

Also this problem was solved with previous versions of Hyper-V (see KB975354 and KB975925) and it solved in other products that performing host level backups of VMs (like symantes backup exec)

So, I don't see why this problem should be considered as normal way of things now, if it was accepted as a problem in previous versions and other backup solutions.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41812258
I guess that DPM may truncate the transaction logs and that's why is interfering with your SQL Native backup strategy.
Do you have any SQL instance with databases with single recovery model only? So you can test and compare with this one?
Can you see why do you need SQL native backups while you have DPM? Maybe is just more easy to get rid of SQL native backups and keep only DPM if it fulfills your needs.
0
 

Author Comment

by:Alexey Kochmarskiy
ID: 41812269
Simple recovery model is not acceptable for production databases.
Yes, I can see why I need SQL native backups. They allow point-in-time recovery, provide simple interface to users through SSMS GUI and have much more simple way of backup/restore operations.

My exact problem is that host level backups of VM through DPM affecting SQL backup timeline inside VM.

Please, don't advice on changing SQL native backups to something else.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41812286
Simple recovery model is not acceptable for production databases.
I understand that. It was only for test purposes. Can't you use DPM on a test environment?

My exact problem is that host level backups of VM through DPM affecting SQL backup timeline inside VM.
IMHO you need to separate what are you backing up.
Even I don't have any experience with DPM, I have with Netbackup and in my previous company we're using it for backing up everything so no SQL native backups were used.
In my current company we use SQL native backups and send the backups files to a network share where Netbackup backups them to a tape system. Also Netbackup is backing up our servers so when we need to restore a server we use Netbackup and when we need to restore a database we use Netbackup to get the backup files from the tape and then use SQL Server to restore the database.

So, never use 2 backup methods simultaneously for SQL Server. That's my advice.
0
 

Author Comment

by:Alexey Kochmarskiy
ID: 41812319
Yeah, i get it from the first message, don't use two products to backup one thing.
I need the same and that is the problem - I don't want DPM to have anything with SQL backups, but there is no way I can exclude his interaction with SQL through VSS when performing host level bakup of VMs. And all methods that meant to do this is not helping.

But wait! You are saying that you are doing the same thing! You use SQL native backups + Netbackup to backup servers. So, I  need almost same thing with DPM, just with simple SQL native backup to network share.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41812335
Alexey, I added this question to more zones to call the attention of Experts on those areas.

By my own experience we never had local storage to store database files so we could easily not include those disks in the server backup tasks. I don't know if the same can be done with DPM but I guess it can. I've found this article. Please check if it helps.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Alexey Kochmarskiy
ID: 41812369
As I mentioned in the initial post - whats written about in article you provided is my option number 3.  SQL databases is moved to separate disk in VM, and it is excluded from backup with HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Agent\HonorFilesNotToBackup and HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\BackupRestore\FilesNotToBackup

It doesn't affect host level VM backup.
0
 
LVL 36

Expert Comment

by:ArneLovius
ID: 41812576
The perils of running two backup types...

If you remove the Differential backups from the SQL Maintenance Plan, you will be able to do "simple" point in time restores as granular as your DPM backup frequency, and to get more granular, you can restore an earlier version of the database, and then roll the logs forward.

As an alternative to backing up the VM in hyper-V you might consider installing the DPM agent in the SQL Server Guest VM.
0
 

Author Comment

by:Alexey Kochmarskiy
ID: 41812665
"you can restore an earlier version of the database, and then roll the logs forward.".
No.
From VM backup I can get mdf/ndf/ldf database files and attach them, but with this method database can not be set to IN RECOVERY mode, so I cannot roll logs forward.

This leaves me only with granularity of DPM backups of the whole VM and completely removes point-in-time recovery of SQL databases which is not acceptable for production.
Even so, I would have to backup each VM once per hour, and churn of VM is much more than amount of database logs per the same hour.

Also, at the current moment I don't see how installing DPM agent into guest OS and performing database backups with DPM helps here. It woldn't be affected by the host level backup? Why so? They are both based on the same SQL backup system.

To abstract from guest OS consider that I'm some service provider, so I can not interfere with guests OS own software or operations and my responsibility is VM protection.
0
 
LVL 36

Expert Comment

by:ArneLovius
ID: 41813679
Aah yes, I see the problem.

A VSS backup automatically travels up the stack in a Microsoft environment.

I wonder what would happen if you replicate the VM to a different Hyper-V host, and then backup the replica VM.
0
 

Author Comment

by:Alexey Kochmarskiy
ID: 41813702
Since replica VM is not really turned on VM - this issue shouldn't reoccur and this backup will go differently, but consistency that provided by the VSS components inside guest VM may be lost.
0
 

Author Comment

by:Alexey Kochmarskiy
ID: 41813710
To clear why I wan't this with DPM:
- Exact same problem solved by using VSS Copy-only backup in Veeam: https://forums.veeam.com/microsoft-hyper-v-f25/sql-server-differential-backup-chain-broken-by-veeam-t27134.html

- Exact same problem solved by using VSS Copy-only backup in Symantec backup exec:
http://arstechnica.com/civis/viewtopic.php?t=1213095

- Same problem described in MS KB975354 and KB975925 and solved by registry keys in the topic

- Same problem solved for DPM 2007 in MS KB970867

And all of them solved by VSS COPY ONLY mode of backup.

So I don't see a reason why it is not in DPM 2012R2 and my only guess - there is no information on how to enable it and use, but it should be there
0
 

Accepted Solution

by:
Alexey Kochmarskiy earned 0 total points
ID: 41821361
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 can be 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
1

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now