{"id":2117,"date":"2024-05-12T11:43:37","date_gmt":"2024-05-12T11:43:37","guid":{"rendered":"https:\/\/www.theSQLReport.com\/?p=2117"},"modified":"2024-05-12T11:43:37","modified_gmt":"2024-05-12T11:43:37","slug":"using-ola-hallengren-backup-procedure-with-cohesity","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=2117","title":{"rendered":"Using Ola Hallengren Backup Procedure with Cohesity"},"content":{"rendered":"

Note:<\/strong><\/p>\n

I was asked to run a proof of concept with Cohesity for SQL Server using Ola Hallengren’s backup stored procedure.\u00a0 What is shared here is what I wish Cohesity support would have shared as a white paper.\u00a0 I waded though their Cohesity’s documentation and a support call to collect what shared below.<\/p>\n

 <\/p>\n

Issue:<\/strong><\/p>\n

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.<\/p>\n

Example:<\/em><\/p>\n

EXECUTE [dbo].[DatabaseBackup] @Databases = ‘My50GBdb’, @BackupType = ‘FULL’, @Directory = ‘\\\\cohesity_node01.myco.com<\/a>\\sql-test-view’ , @Compress = ‘N’, @CleanupTime = 672;<\/p>\n

 <\/p>\n

Introduction:<\/strong><\/p>\n

Four parameters (@Directory, @MaxTransferSize, @BufferCount, & @Compress) need to altered or added to the Ola Hallengren backup statement.\u00a0 Once these changes are made, we found that this solution performed better than our existing backup solution.<\/p>\n

Also note that these parameters also works with the restore statements.<\/p>\n

 <\/p>\n

@Directory<\/strong><\/p>\n

The biggest help to speeding up the backup is to run a Multi-Threaded Backup using the @Directory parameter.\u00a0 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.<\/p>\n

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.\u00a0 Set Ola Hallengren backup parameter @Directory to each node for the Cohesity View, separating those each with a comma.\u00a0 Again, if each of the Cohesity storage nodes does have a hostname, an IP address does work.<\/p>\n

Example: \\\\ < hostname or IP address > \\ < Cohesity View name><\/p>\n

@Directory = ‘<\/p>\n

\\\\192.168.1.11\\sql-test-view,<\/p>\n

\\\\192.168.1.12\\sql-test-view,<\/p>\n

\\\\192.168.1.13\\sql-test-view,<\/p>\n

\\\\192.168.1.14\\sql-test-view,<\/p>\n

\\\\192.168.1.15\\sql-test-view,<\/p>\n

\\\\192.168.1.16\\sql-test-view,<\/p>\n

\\\\192.168.1.17\\sql-test-view’<\/p>\n

 <\/p>\n

@MaxTransferSize<\/strong><\/p>\n

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).<\/p>\n

We have not found an issues using the maximum value for this parameter, but recommend testing the environment.<\/p>\n

Example: @MaxTransferSize = 4194304<\/p>\n

 <\/p>\n

@BufferCount<\/strong><\/p>\n

Specifies the total number of I\/O buffers to be used for the backup operation.\u00a0 I have found that issues when using more that 250 value on some of the smaller sized servers.\u00a0 Each SQL Server should be tested for an acceptable value.<\/p>\n

Example: @BufferCount = 250<\/p>\n

 <\/p>\n

@Compress<\/strong><\/p>\n

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.\u00a0 Thus making the Cohesity View nothing more than a network share.<\/p>\n

Example: @Compress = ‘N’<\/p>\n

 <\/p>\n

Example Code<\/strong>:<\/p>\n

EXECUTE [dbo].[DatabaseBackup] @Databases = ‘My50GBdb’,<\/p>\n

@BackupType = ‘FULL’,<\/p>\n

@Directory = ‘<\/p>\n

\\\\192.168.1.11\\sql-test-view,<\/p>\n

\\\\192.168.1.12\\sql-test-view,<\/p>\n

\\\\192.168.1.13\\sql-test-view,<\/p>\n

\\\\192.168.1.14\\sql-test-view,<\/p>\n

\\\\192.168.1.15\\sql-test-view,<\/p>\n

\\\\192.168.1.16\\sql-test-view,<\/p>\n

\\\\192.168.1.17\\sql-test-view’,<\/p>\n

@Compress = ‘N’,<\/p>\n

@CleanupTime = 672,<\/p>\n

@MaxTransferSize = 4194304,<\/p>\n

@BufferCount = 250;<\/p>\n

 <\/p>\n

References:<\/strong><\/p>\n

Cohesity’s documentation on Native Backups is in the link below:<\/em><\/p>\n

https:\/\/docs.cohesity.com\/HomePage\/PDFs\/Cohesity-Solution-Guide-Microsoft-SQL-Server-Native-Backups-Cohesity-Platform.pdf<\/a><\/p>\n

Ola Hallengren backup command documentation:<\/em><\/p>\n

https:\/\/ola.hallengren.com\/sql-server-backup.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Note: I was asked to run a proof of concept with Cohesity for SQL Server using Ola Hallengren’s backup stored procedure.\u00a0 What is shared here is what I wish Cohesity … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-2117","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2117"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2117"}],"version-history":[{"count":8,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2117\/revisions"}],"predecessor-version":[{"id":2126,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2117\/revisions\/2126"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2117"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2117"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2117"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}