Note:
I was asked to run a proof of concept with Cohesity for SQL Server using Ola Hallengren’s backup stored procedure. What is shared here is what I wish Cohesity support would have shared as a white paper. I waded though their Cohesity’s documentation and a support call to collect what shared below.
Issue:
A normal Ola Hallengren stored procedure backup statement for 50 GB will take over two hours for a full backup, even with a 10GB network connection.
Example:
EXECUTE [dbo].[DatabaseBackup] @Databases = ‘My50GBdb’, @BackupType = ‘FULL’, @Directory = ‘\\cohesity_node01.myco.com\sql-test-view’ , @Compress = ‘N’, @CleanupTime = 672;
Introduction:
Four parameters (@Directory, @MaxTransferSize, @BufferCount, & @Compress) need to altered or added to the Ola Hallengren backup statement. Once these changes are made, we found that this solution performed better than our existing backup solution.
Also note that these parameters also works with the restore statements.
@Directory
The biggest help to speeding up the backup is to run a Multi-Threaded Backup using the @Directory parameter. By using more than one of the Cohesity nodes for available to the Cohesity View (looks the same as network share). Note that small volume backups like log backups will have acceptable performance using a single node of a Cohesity View.
The Cohesity administrator should be able to give you all the hostnames or IP addresses for the Cohesity storage nodes that are available to your network view. Set Ola Hallengren backup parameter @Directory to each node for the Cohesity View, separating those each with a comma. Again, if each of the Cohesity storage nodes does have a hostname, an IP address does work.
Example: \\ < hostname or IP address > \ < Cohesity View name>
@Directory = ‘
\\192.168.1.11\sql-test-view,
\\192.168.1.12\sql-test-view,
\\192.168.1.13\sql-test-view,
\\192.168.1.14\sql-test-view,
\\192.168.1.15\sql-test-view,
\\192.168.1.16\sql-test-view,
\\192.168.1.17\sql-test-view’
@MaxTransferSize
This parameter specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).
We have not found an issues using the maximum value for this parameter, but recommend testing the environment.
Example: @MaxTransferSize = 4194304
@BufferCount
Specifies the total number of I/O buffers to be used for the backup operation. I have found that issues when using more that 250 value on some of the smaller sized servers. Each SQL Server should be tested for an acceptable value.
Example: @BufferCount = 250
@Compress
This is probably the most important parameter, because if compression needs to be turned off on the SQL Server backup, otherwise Cohesity will not be able to use its own deduplication & compression. Thus making the Cohesity View nothing more than a network share.
Example: @Compress = ‘N’
Example Code:
EXECUTE [dbo].[DatabaseBackup] @Databases = ‘My50GBdb’,
@BackupType = ‘FULL’,
@Directory = ‘
\\192.168.1.11\sql-test-view,
\\192.168.1.12\sql-test-view,
\\192.168.1.13\sql-test-view,
\\192.168.1.14\sql-test-view,
\\192.168.1.15\sql-test-view,
\\192.168.1.16\sql-test-view,
\\192.168.1.17\sql-test-view’,
@Compress = ‘N’,
@CleanupTime = 672,
@MaxTransferSize = 4194304,
@BufferCount = 250;
References:
Cohesity’s documentation on Native Backups is in the link below:
Ola Hallengren backup command documentation:
Leave a Comment