Discussions specific to the VMware vSphere hypervisor
Post Reply
matt_778
Influencer
Posts: 22
Liked: 1 time
Joined: Feb 08, 2010 9:25 am
Full Name: Matt
Contact:

Safely excluded SQL TEMPDB from Replications

Post by matt_778 » Sep 01, 2015 4:35 am

We are using Veeam 8 to replicate VMware VMs to a DR site.

The SQL DBA has suggested we exclude TEMPDB on SQL VMs as this is recreated when SQL server starts and it is responsible for a large change rate.
The suggestion is to house TEMPDB on its own Windows Volume, then either exclude that volume within advanced settings in the replication job, or create that volume (disk) as VMware independent, so Veeam skips over it.

The issue is that the Replicated SQL Server at the DR site needs to have that volume presented, so that on boot it will auto-create TEMPDB.

How do we exclude the data of TEMPDB for replications, whilst retaining the disk structure to the DR VM?

We're thinking of running a full replication so Veeam replicates the new volume one time, then changing it to independent disk so from that point onward Veeam skips it over (but hopefully the disk is then known and just hold old TEMPDB data)

Any thoughts?

foggy
Veeam Software
Posts: 18287
Liked: 1568 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Safely excluded SQL TEMPDB from Replications

Post by foggy » Sep 01, 2015 10:54 am

matt_778 wrote:We're thinking of running a full replication so Veeam replicates the new volume one time, then changing it to independent disk so from that point onward Veeam skips it over (but hopefully the disk is then known and just hold old TEMPDB data)
That should work, just be careful with failback to original location, not sure how it will behave in case original VM has independent disk.

AJ83
Enthusiast
Posts: 60
Liked: 1 time
Joined: Oct 06, 2009 2:32 pm
Contact:

[MERGED] Excluding SQL server TempDB from backups

Post by AJ83 » Jun 06, 2019 4:26 pm

Did Veeam ever consider making it a feature to exclude TEMPDB files from MS SQL server backups?
It would save a considerable amount of diskspace in the VBK/VBR files, especially considering there can be a large changerate in the TempDB files.

Using exclusion of the specific disk where TempDB files reside isn`t an option, because SQL server won`t start without access to the path where the TempDB files reside.

SQL server just needs the file location to be there at startup and creates new tempDB files if they are missing, so if you would configure the Tempdb files to be in the root of a specific disk, Veeam B&R would only need to be able to recover a disk as an empty disk.

HannesK
Veeam Software
Posts: 4030
Liked: 495 times
Joined: Sep 01, 2014 11:46 am
Location: Austria
Contact:

Re: Excluding SQL server TempDB from backups

Post by HannesK » Jun 07, 2019 5:35 am

Hello,
As an alternative to excluding disks, you could also exclude folders: bitlooker

I don't like that feature for its complexity at scale, but it might be the thing you are looking for.

The concept of Veeam is doing VM image level backup. Always backup everything with the advantage that you cannot forget anything. No need to think about anything. No need to somehow manage hundreds or thousands of exclusions. Complexity is worse than using a little more disk space.

Best regards,
Hannes

AJ83
Enthusiast
Posts: 60
Liked: 1 time
Joined: Oct 06, 2009 2:32 pm
Contact:

Re: Excluding SQL server TempDB from backups

Post by AJ83 » Jun 07, 2019 8:28 am

I know you can exclude the files/folder. But as you say it is a hassle to manage.

Ideally you would make the SQL TempDB disk Independant-Persistant in VMware, so it won`t snapshot (introducing risks/ longer commit with potential hickup) Veeam will not backup an Independant-persistant disk, but that introduces the problem in which SQL server wont start because the path is missing for creating TempDB files. So i would like to see a feature in wich you can specify that Veeam will add this as an emtpy disk in a restore scenario.

P.Tide
Product Manager
Posts: 5265
Liked: 459 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: Excluding SQL server TempDB from backups

Post by P.Tide » Jun 07, 2019 9:38 am

Hi,

Would you elaborate what exactly is difficult to manage when excluding only one file in a job? Bitlooker has been designed with an intention to provide an easy way to exclude certain things from a backup, and I think that your case fits it perfectly. That is, I'd really appreciate if you could help us improve it.

Thanks!

AJ83
Enthusiast
Posts: 60
Liked: 1 time
Joined: Oct 06, 2009 2:32 pm
Contact:

Re: Excluding SQL server TempDB from backups

Post by AJ83 » Jun 07, 2019 11:43 am

It`s not necessarily difficult to set up, but it takes some extra effort. The main thing is that we would like to use independant-Persistant disks for the TempDB files for obvious reasons.

skrause
Expert
Posts: 438
Liked: 91 times
Joined: Dec 08, 2014 2:58 pm
Full Name: Steve Krause
Contact:

Re: Excluding SQL server TempDB from backups

Post by skrause » Jun 07, 2019 12:54 pm

We do this by doing a file level exclusion in the image processing step.

You just have to have the entire folder path to where the TempDB lives and then use a wildcard.

example: X:\MSSQL\Data\*.*

Disclaimer: we do not use Veeam's SQL processing (we do copy only) so I don't know how that affects it but the backups work and I was able to test when I first created the exclusion by doing an instant VM recovery to a different location with no networking and SQL started up fine.

It would be nice if Veeam had a way to "exclude TempDB" like it excludes the pagefile though.
Steve Krause
Veeam Certified Architect

foggy
Veeam Software
Posts: 18287
Liked: 1568 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Safely excluded SQL TEMPDB from Replications

Post by foggy » Jun 07, 2019 1:53 pm

I've merged your thread into an older discussion containing a somewhat creative workaround. Please review.

AJ83
Enthusiast
Posts: 60
Liked: 1 time
Joined: Oct 06, 2009 2:32 pm
Contact:

Re: Safely excluded SQL TEMPDB from Replications

Post by AJ83 » Jun 07, 2019 2:13 pm

Thanks foggy! The workaround matt is describing works wonders for a replication scenario, but i reckon it won`t do any good for a backup that needs to be able to run in a surebackup job.

foggy
Veeam Software
Posts: 18287
Liked: 1568 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Safely excluded SQL TEMPDB from Replications

Post by foggy » Jun 07, 2019 2:35 pm

Anyway, I'm also supportive of the BitLooker approach, looks safer to me.

Post Reply

Who is online

Users browsing this forum: Bing [Bot], Google [Bot], PetrM and 15 guests