2

Environment: SQL Server 2008 R2 Standard Edition on Windows Server 2008 R2 (yeah - I know). VMWare, Commvault.

We have a 1.4 TB DB with one table. Here is the schema:

CREATE TABLE [dbo].[FileManager_FilesContent](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [FileContent] [varbinary](max) NOT NULL,
    [Extension] [varchar](20) NULL,
    [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_FileManager_FilesContent] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_FileManager_FilesContent] ON [dbo].[FileManager_FilesContent]
(
    [GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

We have a network share where we backup the other DBs. This one won't backup there because it would take more than 40 hours. My thought is a local drive (or drives) plus backup compression, striping, and dialing in maxtransfersize and buffercount might help.

Any other advice on how to get this DB backed up?

Thanks in advance, -Peter

Improve this question
4
  • Backup doesn't look the DB, maybe 40 hours worth it.
    – McNets
    Oct 25, 2019 at 21:46
  • if you have sufficient space in local drive, you can try backup with compression to local drive and then move to network share
    – Squirrel
    Oct 26, 2019 at 1:01
  • What do you mean “it won’t back up”? Do you mean it is failing for some reason or your not letting it run because it will take 40 hours? What’s the hardware setup? That will determine how fast you can back up the data.
    – kevinnwhat
    Oct 26, 2019 at 1:34
  • Thanks, everyone. "it won't back up" means I have chosen to cancel the backup in order to not have the 40 hour duration interfere with regular workloads in the DB. I have done a backup to NUL which took 3hours ish. Leaning towards asking the infra team to add a fast local disk and use compression and other config options as @squirrel suggested.
    – PeterDaniels
    Oct 27, 2019 at 14:14

2 Answers 2

Reset to default
2

You could partition the table. Put each partition in a separate filegroup via the partition scheme. Each filegroup can be backed up individually. From SQL Server 2019 there is a DMV that makes available information on which pages, and hence files, are dirty.

There will still be 1.4TB to backup so a complete backup cycle will still take at least 40 hours. However, each individual filegroup will take much less time avoiding downtime, network congestion and all the other worries.

If writes typically touch only a few partitions you could establish some form of change tracking - either built-in or application code - and only backup the dirty filegroups.

Depending on when rows are written you may be able to make some filegroups read-only. These can then be omitted from the on-going backup cycles once a final backup is taken, reducing elapsed.

Potentially backups and transfers to network shares could happen in parallel reducing the amount of local workspace required (backup partition 1, transfer that over the network while partition 2's backup is prepared, then transfer 2 while 3 is backed up and so on; each local backup can be deleted as soon as it is on the network drive).

If you can arrange things appropriately you may be able to implement piecemeal restores which will certainly improve your RTO from 40hr.

Of course the scripting and restores will be that bit more complicated.

Improve this answer
3
  • Thank you, Michael. We can't partition because sql server 2008 R2 standard edition does not support table partitioning. Even if that was availabile, I also struggle with knowing which column (s) to partition on since there is no date/time columns.
    – PeterDaniels
    Oct 27, 2019 at 14:09
  • Yes, sorry. The documentation's no longer available for 8R2 so I couldn't check. I thought it might be helpful for others on a different edition or version, so I typed it up anyway. Good luck.
    – Michael Green
    Oct 27, 2019 at 23:27
  • You could implement something similar in user space with partitioned views, each underlying table having its own file/ filegroup. A bit more work and management, though.
    – Michael Green
    Oct 27, 2019 at 23:31
0

Multiple file backup to a local or direct attached drive if the network is too slow. SSD, NVme, etc.

All the other options (how many files, maxtransfer, etc.) fall into the "It Depends" category and need to be tested to see what's optimal.

Or..."Backup" by log shipping it to another SQL box. Not the cleanest option, but at least its a choice.

Improve this answer
1
  • 1
    Gut says this is the right vector as it's been challenging for me to get a good view into our network. I've heard rumors that they use some sort of bandwidth assignment tool that assigns 90 of the bandwidth to the vmware/commvault backup servers and services during a maint window at night. I'll try to get more details on that.
    – PeterDaniels
    Oct 27, 2019 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.