August 26, 2014 at 11:21 am
We recently moved to a new datacenter with new SANs and VM farm. Our operations team wants to use VM whenever possible. In the past, we've resisted the move for SQL because of performance concerns. I'm now more concerned with not being able to insure we can restore to a point in time because snapshots in the virtual environment.
I came across an announcement of a fix to SQL to not fail a snapshot backup if a database is read-only.
http://blogs.msdn.com/b/psssql/archive/2014/08/12/vss-backup-of-alwayson-secondaries.aspx
It failed because the backup could not update the database - something about a reset of differential bitmap. This was the opposite of what I wanted. I wanted the option to always fail the hyper-v snapshot on any database - read-only or not. Why? Because the update resets the differential bitmap upon which we rely to support the SLA of the database. Would it not be better to do a "COPY_ONLY" backup for a snapshot?
Please vote for this if you think it's important.
https://connect.microsoft.com/SQLServer/feedbackdetail/view/910349/protect-us-from-veeam
Has anybody gone through this already? Any advice?
If you run this query and see a log backup to NUL or a "virtual disk" type, then you might want to double check that you don't need transaction log or differential restores. If you do need these, then you will need to have your virtual backup adjusted. (We needed a point in time restore last Friday evening because of a process failure. We could not use our SQL backups because of a Veeam tran log backup to NUL in addition to the Veeam snapshot invalidation of the diff backup. We were lucky and were able to repair - not restore - the database by Monday morning. The server now uses backup-exec, not veeam. Please note that this is not a Veeam problem, but a SQL Server design issue. The SQL Server team is responsible for SQL Server and how a snapshot affects it.)
select top 100 bs.server_name + '.' + bs.database_name as [database], bs.user_name, bs.backup_start_date, bs.backup_finish_date,
CASE bs.type WHEN 'D' THEN 'D = Database' WHEN 'I' THEN 'I = Differential database' WHEN 'L' THEN 'L = Log'
WHEN 'F' THEN 'F = File or filegroup' WHEN 'G' THEN 'G =Differential file' WHEN 'P' THEN 'P = Partial'
WHEN 'Q' THEN 'Q = Differential partial' ELSE CAST(bs.type as char(1)) END as [type],
--bs.recovery_model,
bs.is_snapshot as [snapshot], bs.is_copy_only as [copy_only],
bf.physical_device_name,
CASE bf.device_type WHEN 2 THEN '2 = Disk' WHEN 5 THEN '5 = Tape' WHEN 7 THEN '7 = Virtual device' ELSE CAST(bf.device_type as varchar(4)) END as [device_type]
--,bs.*, bf.*
from msdb.dbo.backupset bs
join msdb.dbo.[backupmediafamily] bf
on bs.media_set_id = bf.media_set_id
where 1=1
--and database_name='dbname'
--and type='L'
--and physical_device_name is null
order by backup_start_date desc
The option of a snapshot backup is great. It's not so great that it can do so much harm without warning. Be warned that you might not get any warning.
Thanks,
Randy
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 26, 2014 at 2:20 pm
In the Veeam backup or snapshot , 'is the application aware image processing' should be selected. This would require the id/pwd for the account SQL Server runs on set up on the job. If you have process to prune the logs, select 'Do not truncate' on the transaction logs for the Veeam job
August 26, 2014 at 4:51 pm
Thanks. We have a job to back up the transaction logs, so truncating them is probably not the option for us. Is this option only for SQL Servers that are not managed?
We have never been asked to provide credentials to our SQL Servers. Perhaps the administrator/domain admin account is used. Can the Veeam application-aware image processing control the VSS snapshot enough to prevent resetting the database differential map while still getting a good image?
As a DBA, I'm used to db migration and recovery via DETACH\ATTACH and BACKUP\RESTORE. I think of a snapshot as a relative of a DETACH\ATTACH because ATTACH does not have the option to leave the database in a non-operational state where differentials and transaction log backups can be applied.
Our SLA requires the option for a restore of a database to a point in time. Is this possible with snapshot based product without SQL BACKUP\RESTORE? It would be nice to restore a db - or server - from a snapshot then apply diff and log backups to a point in time. If this is not possible with a snapshot based product, then I need that product to not prevent the use of SQL BACKUP/RESTORE.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 27, 2014 at 2:12 am
rstone (8/26/2014)
It would be nice to restore a db - or server - from a snapshot then apply diff and log backups to a point in time. If this is not possible with a snapshot based product, then I need that product to not prevent the use of SQL BACKUP/RESTORE.
Not possible, the two are independant of each other.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 27, 2014 at 9:09 am
When a snapshot is taken on a server, the drives needs to be quiesced for a very neglible time. Even this little time affects databases. So, an application aware image processing will ensure that the quiesce happens as per the application and hence not forced. On the Veeam Snaphot job, make sure 'Application Aware Image Processing is Selected' for SQL Server. Since you have SQL Server managing logs and back up, Select 'Do not truncate' on the transaction logs.
August 27, 2014 at 9:14 am
If your database is very volatile and transactions drive, on the Veeam Snaphot job exclude your database and transaction log volumes. For your VM restore strategy, The snaphot will be restored on all volumes, then you can run the db restore from your back up for the database volumes.
August 27, 2014 at 10:50 am
Thanks for the responses. I'll ask operations to make insure 'Application Aware Image Processing' and 'Do not truncate' are selected. Are the strategies below valid? Are there better strategies?
1) Nightly server snapshot after a SQL FULL backup to insure FULL backup file is in backup. A full server restore is fully operational with all databases. SQL FULL and LOG backups are valid for point-in-time restores. Diffs still don't work after a snapshot even with the right options set.
2) Nightly custom partial snapshot that excludes drives with user database data or log files. A full server restore is partially operational with a working SQL Server, but no user databases. SQL FULL, DIFF, and LOG backups are valid for point-in-time restores of user databases. SQL DIFF backups are not valid for a system databases. Excluded drives would require another means to restore the folder structure.
3) Weekly full server snapshot plus nightly custom partial snapshot. A full server restore is fully operational with all databases - but up to a week old. The weekly snapshot restore can be used to recover the folder structure of a lost drive if required. The nightly snapshot restore can recover the system or SQL databases to night prior. The nightly snapshot restore can be used to obtain SQL backup files for a point-in time restore using FULL, DIFF and LOG backup files.
4) Full Server snapshot followed by SQL FULL backup followed by a backup drive snapshot. A full server restore is fully operational. Files captured in the second snapshot are valid to use for point-in-time restores. FULL, DIFF, and LOG point-in-times restores are valid after the second snapshot until the following snapshot. DIFF backups are not valid between the full server snapshot and the following SQL FULL backup.
Options?
In #4, exclude the backup drive in the full server snapshot - it's not a FULL snapshot then - because this drives is captured in the second snapshot.
Take snapshots during the day of the backup drive to capture transaction log backup files. Is there any advantage to using a separate drive for the transaction log backups in this case?
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 27, 2014 at 12:20 pm
TheAccidentalDBA (8/27/2014)
When a snapshot is taken on a server, the drives needs to be quiesced for a very neglible time. Even this little time affects databases. So, an application aware image processing will ensure that the quiesce happens as per the application and hence not forced. On the Veeam Snaphot job, make sure 'Application Aware Image Processing is Selected' for SQL Server. Since you have SQL Server managing logs and back up, Select 'Do not truncate' on the transaction logs.
If you have "Application Aware Image Processing" selected, doesn't that mean that veeam is now doing backups of SQL server? And won't this then invalidate the differentials if you're using native SQL backups?
...
b) only ever use application-aware backups for servers where you *do* need that functionality. Domain controllers, exchange boxes, SQL servers that have no separate backups etc.
from
http://forums.veeam.com/veeam-backup-replication-f2/sql-do-not-truncate-logs-ignored-t20054.html
I know when we've used some server backups, if we didn't specifically arrange for SQL backups to be ommitted, they would get backed up to virtual devices and break our diff chains, and this seems like a similar situation.
If you want differential backups to work, don't you need to use the same mechanism for the differential backup as the full backup its based against?
Feel free to shoot me down if I'm wrong, this is a very important topic and we should be 100 percent correct and TEST the restore solution we go with.
I don't see any bug with Microsoft here, they offer a 3rd party interface to allow access to SQL backup for integrated backup solutions, but the ultimate responsibility for SQL backups is with the database owner. Obviously there could be a bug I'm simply not seeing, I'm not the sharpest tool in the shed 😉 so feel free to correct me here!
August 27, 2014 at 1:53 pm
SSC-Addicted nice points and links. I posted two responses separately.Yes, totally agree on your points that its needs to be tested with restore. Logically, the following might work.
1. Only Veeam backup for the SQL Server, 'Application Aware Processing' is turned ON. Also, select 'Truncate logs on Successful backups'
2. Veeam backup and Native full backups daily, 'Application Aware Processing' is turned ON. Also, select 'Do not Truncate logs'
3. Veeam backup & Native full/differential/transaction back up , 'Application Aware Processing' is turned OFF, the db data/transaction log/backup drives not included.
Snapshot can get a valid backup on the db data/transaction log/back up drives only after quescing. These drives are actively used by SQL Server Process which needs to be momentarily stopped for a quick snapshot. If these drives are not included in the snapshot, then it will not try to stop the SQL Server Process.
Does this make sense?
August 27, 2014 at 2:48 pm
TheAccidentalDBA (8/27/2014)
SSC-Addicted nice points and links. I posted two responses separately.Yes, totally agree on your points that its needs to be tested with restore. Logically, the following might work.1. Only Veeam backup for the SQL Server, 'Application Aware Processing' is turned ON. Also, select 'Truncate logs on Successful backups'
2. Veeam backup and Native full backups daily, 'Application Aware Processing' is turned ON. Also, select 'Do not Truncate logs'
3. Veeam backup & Native full/differential/transaction back up , 'Application Aware Processing' is turned OFF, the db data/transaction log/backup drives not included.
Snapshot can get a valid backup on the db data/transaction log/back up drives only after quescing. These drives are actively used by SQL Server Process which needs to be momentarily stopped for a quick snapshot. If these drives are not included in the snapshot, then it will not try to stop the SQL Server Process.
Does this make sense?
I think it does!
On a couple of points if using Veeam backup, rstone brings interesting points with the timing, as sql differential backups must follow the sql full backups with no intervening Veeam backup (if application aware is turned on and db included).
+1 Informative!
August 27, 2014 at 3:28 pm
SSC-Addicted (8/27/2014)
I don't see any bug with Microsoft here, they offer a 3rd party interface to allow access to SQL backup for integrated backup solutions, but the ultimate responsibility for SQL backups is with the database owner. Obviously there could be a bug I'm simply not seeing, I'm not the sharpest tool in the shed so feel free to correct me here!
Bug or feature?
Great Feature: I see operations and DBA with two separate processes to backup servers and databases. In the new era of snapshots, we can now backup everything. This allows a very quick recovery without the need to build servers, install SQL, and restore databases manually. (I like this very much.)
Feature: SQL Server will (recent update) respond to a FULL VSS snapshot using COPY_ONLY on a read_only database. This prevents the snapshot from failing. (Okay, but why stop there?)
Lost opportunity (i.e., bug): SQL Server does not respond to a FULL VSS snapshot using COPY_ONLY on a writeable database. A COPY_ONLY is a valid backup that does not affect the backup chain. COPY_ONLY is what should always be used by a snapshot until snapshots can be used for point-in-time restores. Perhaps it requires additional coding to safely flush data to disk without updating the diff map?
Lost opportunity: SQL Server does let DBA control the snapshot response. It also might be time to let DBA prevent the transaction log backup to NUL.
The design choice made by Microsoft cost several of us our weekend. There is more work ahead for us to insure operations and DBA collaborate correctly on this. It might not be a bug, but all this would have been unnecessary if the COPY_ONLY design was correct to start with. COPY_ONLY exists just for this sort of thing.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 27, 2014 at 3:36 pm
TheAccidentalDBA (8/27/2014)
SSC-Addicted nice points and links. I posted two responses separately.Yes, totally agree on your points that its needs to be tested with restore. Logically, the following might work.1. Only Veeam backup for the SQL Server, 'Application Aware Processing' is turned ON. Also, select 'Truncate logs on Successful backups'
2. Veeam backup and Native full backups daily, 'Application Aware Processing' is turned ON. Also, select 'Do not Truncate logs'
3. Veeam backup & Native full/differential/transaction back up , 'Application Aware Processing' is turned OFF, the db data/transaction log/backup drives not included.
Snapshot can get a valid backup on the db data/transaction log/back up drives only after quescing. These drives are actively used by SQL Server Process which needs to be momentarily stopped for a quick snapshot. If these drives are not included in the snapshot, then it will not try to stop the SQL Server Process.
Does this make sense?
I think it does also. I'm trying to get the best of both worlds. I'm very annoyed at how beautifully the two methods would work together without this flaw.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 29, 2014 at 10:23 am
rstone - I stand corrected, we should never not select 'application aware processing'. If its done, the OS might be consistent but not application. This is the case even when the drives are not included.
Having full sized back ups thru Veeam and Native is waste of storage. The only reason you need a snapshot of VM could be because of all the changes in security update/patches et al applied on the OS. Probably , you need a combined back up with Systems Administrations and DB group.
Option 1: A VM backup is taken after every patching/update window. After that the back up is controlled by SQL Server, complete full back up & then differential.
Option 2: Use real time SAN to SAN replication. Solution is expensive
More open to discussion
August 29, 2014 at 3:38 pm
Now I'm more confused. We have 3 new SANs with replication for DR. I don't see how this helps me with point in time restores for a database. I would "like to have" snapshot backups because I'm lazy. I can get a full system back without any work - installing SQL Server. But I also need to have point in time restores. It seems like we have to walk a tight rope to have operations and DBA both meet their SLAs.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply