{"id":4164,"date":"2018-06-20T12:00:34","date_gmt":"2018-06-20T11:00:34","guid":{"rendered":"https:\/\/www.data-protector.org\/wordpress\/?p=4164"},"modified":"2018-06-16T09:40:17","modified_gmt":"2018-06-16T08:40:17","slug":"optimize-mssql-backup-performance","status":"publish","type":"post","link":"https:\/\/www.data-protector.org\/wordpress\/2018\/06\/optimize-mssql-backup-performance\/","title":{"rendered":"Optimize MSSQL backup performance"},"content":{"rendered":"

Optimizing performance has always been a hot topic. This post contains information on how to optimize your MSSQL backup performance with the Data Protector. As always, with this kind of advice, this MAY be helpful in most environments but is not a general solution for any environment. Please test any of the changes and consult your SQL Server administrator if you have any doubts.<\/p>\n

    \n
  1. General recommendations\n
      \n
    1. Tuning using omnirc<\/li>\n
    2. Database integrity check<\/span><\/li>\n
    3. SQL backup compression<\/span><\/span><\/li>\n<\/ol>\n<\/li>\n
    4. Parallel database backup<\/li>\n
    5. Multi-stream database backup<\/li>\n<\/ol>\n

      Parallel database and\u00a0Multi-stream database backup for Microsoft SQL is available since Data Protector A.09.08.<\/p>\n

      <\/p>\n

      1. General recommendations<\/strong><\/h3>\n

      Tuning using omnirc<\/strong><\/h3>\n

      The following omnirc<\/strong> options are usually a good choice to optimize general backup performance using the MSSQL online integration agent. Using the settings will increase the transfer size and block size to their maximum values.\u00a0This assumes that the underlying file systems were formatted correctly (64k).<\/p>\n

      # Increase block size and transfer size for MSSQL backups\r\nOB2SQLBLOCKSIZE=65536\r\nOB2SQLMAXTRANSFERSIZE=4194304<\/pre>\n
      Database integrity check<\/span><\/strong><\/h3>\n
      The option\u00a0Check database integrity<\/strong> is useful to detect silent database corruption at backup time. Checking the database integrity takes time and eats disk and CPU performance on the database server. If you have different backup jobs for full backups and transaction log backups it is recommended to Disable<\/strong> the Check database integrity<\/strong> on transaction log backups, especially if they have to run very frequently.<\/span><\/p>\n
      SQL backup compression<\/span><\/strong><\/h3>\n
      Data Protector allows to configure the usage of SQL backup compression<\/strong>. By default the MSSQL server settings, configured in the database, are used. Typical backup devices such as tape and deduplication devices (StoreOnce, Data Domain) offer a high performance data reduction at backup time but having issues with pre-compressed data. If you have doubts, or not full control over the MSSQL server, choose to\u00a0Disable <\/b>for\u00a0SQL backup compression.<\/strong><\/b><\/p>\n
      2. Parallel database backup:\u00a0<\/strong>Large number of databases per instance<\/strong><\/h3>\n
      This is a typical example for MSSQL servers that contain hundreds of databases per instance. Even if they are relatively small the processing rate will be slow. The solution is to configure the integration agent to backup multiple databases at the same time. The following example assumes you have successfully installed the agent on the client system. Then add the following omnirc options to the client system to enable parallel processing.<\/p>\n
      # Allow to backup multiple SQL databases in parallel, one stream per database
      \nOB2_PREFER_DATABASE_PARALLELISM=1
      \nOB2_DEFAULT_CONCURRENT_STREAMS=1<\/p>\n
      The entire MSSQL Server needs to be selected for backup.<\/p>\n
      \"\"<\/a><\/p>\n
      Choose the right amount of target devices. B2D libraries can open multiple data streams per gateway. In our example here up to 4 data streams will be accepted.<\/p>\n
      \"\"<\/a><\/p>\n
      In Backup Object Summary<\/strong> select Properties<\/strong> of the MSSQL server and change Concurrent streams<\/strong> in the MS SQL Integration<\/strong> tab to match the number of data streams selected for destination devices. This is 4 in our example.<\/p>\n
      \"\"<\/a><\/p>\n
      When the backup is executed we see 4 active data connections and the backup proceed very quickly compared to a single stream backup.<\/p>\n
      \"\"<\/a><\/p>\n
      3. Multi-stream database backup:\u00a0<\/strong>For of a few large databases<\/strong><\/h3>\n
      If the MSSQL server that contains only a few, but very large databases the following might be useful. The solution is to configure the integration agent to backup databases with multiple data streams. The following example assumes you have successfully installed the agent on the client system. Then add the following omnirc options to the client system to enable multi-streaming. Please note that the number of streams per database is configured in this file. In our example we use 2 concurrent streams.<\/p>\n
      # Allow to backup multiple SQL databases in parallel, 2 streams per database
      \nOB2_PREFER_DATABASE_PARALLELISM=1
      \nOB2_DEFAULT_CONCURRENT_STREAMS=2<\/p>\n
      The entire MSSQL Server needs to be selected for backup. Each database will be backed up in 2 pieces.<\/p>\n
      \"\"<\/a><\/p>\n
      Choose the right amount of target devices. B2D libraries can open multiple data streams per gateway. If we choose 4 here we will be able to accept up to 4 data streams. This is true for multi-streaming as well as for parallel backup.<\/p>\n
      \"\"<\/a><\/p>\n
      In Backup Object Summary<\/strong> select Properties<\/strong> of the MSSQL server and change Concurrent streams<\/strong> in the MS SQL Integration<\/strong> tab to match the number of data streams selected for destination devices. This is 4 in our example.<\/p>\n
      \"\"<\/a><\/p>\n
      When the backup is executed we see 4 active data connections and the backup proceed very quickly compared to a single stream backup. 2 databases are backed up at the same time and each database is backed up in 2 pieces.<\/p>\n
      \"\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
      Optimizing performance has always been a hot topic. This post contains information on how to optimize your MSSQL backup performance with the Data Protector. As always, with this kind of advice, this MAY be helpful in most environments but is not a general solution for any environment. Please test any of the changes and consult […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"spay_email":""},"categories":[11],"tags":[212],"translation":{"provider":"WPGlobus","version":"2.8.8","language":"en","enabled_languages":["en","de"],"languages":{"en":{"title":true,"content":true,"excerpt":false},"de":{"title":true,"content":true,"excerpt":false}}},"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p28cjj-15a","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1960,"url":"https:\/\/www.data-protector.org\/wordpress\/2014\/04\/special-whitepaper-protecting-ms-sql-server-hp-storeonce-catalyst-data-protector\/","url_meta":{"origin":4164,"position":0},"title":"Special Whitepaper – Protecting MS SQL Server \/ HP StoreOnce Catalyst \/ Data Protector","date":"17. April 2014","format":false,"excerpt":"{:en}In the past we learned from different whitepaper how to optimize the deduplication ratio for different applications and data types. And different whitepaper are available at HP.com, explaining the settings to optimize the backup to a StoreOnce system using Catalyst and HP Data Protector (Please note: most of the settings\u2026","rel":"","context":"In "Data Protector Links"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4083,"url":"https:\/\/www.data-protector.org\/wordpress\/2018\/05\/sysbackup-oracle-12c\/","url_meta":{"origin":4164,"position":1},"title":"SYSBACKUP for Oracle 12c","date":"28. May 2018","format":false,"excerpt":"{:en}Starting with Oracle 12c three new administrative roles and users have been introduced to conform the principle of access to the least privilege. The users and roles named SYSBACKUP, SYSDG and SYSKM will be created when the database is created with their account in expired and locked status.\u00a0SYSBACKUP is used\u2026","rel":"","context":"In "HowTo"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":903,"url":"https:\/\/www.data-protector.org\/wordpress\/2012\/06\/backup-exec-no-longer-for-the-enterprise\/","url_meta":{"origin":4164,"position":2},"title":"Backup Exec no longer for the Enterprise","date":"7. June 2012","format":false,"excerpt":"{:de}Kurz und Knapp: Symantec hat in einem Artikel f\u00fcr Backup Exec 2012 einige seiner Komponenten auf EOL - end of life - gesetzt und verliert aus meiner Sicht die F\u00e4higkeit im Enterprise Backup & Recovery L\u00f6sungen anzubieten. Der Einsatz im Mittelstand ist fragw\u00fcrdig, da auch in diesem Bereich Anwendungen wie\u2026","rel":"","context":"In "Common"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":831,"url":"https:\/\/www.data-protector.org\/wordpress\/2011\/12\/slow-backup-on-windows-2008-r2\/","url_meta":{"origin":4164,"position":3},"title":"Slow backup on Windows 2008 R2","date":"19. December 2011","format":false,"excerpt":"{:en}When running Data Protector 6.20 together with a media agent installed on Windows 2008 sometimes you might have a bad backup performance. Sure, this was a known problem when DP 6.20 was launched and it was fixed with patch DPWIN_00516. If you still experience bad backup performance I recommend to\u2026","rel":"","context":"In "Common"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1155,"url":"https:\/\/www.data-protector.org\/wordpress\/2014\/01\/hanging-sessions-dp-7-01\/","url_meta":{"origin":4164,"position":4},"title":"Hanging Sessions with DP 7.01","date":"23. January 2014","format":false,"excerpt":"{:en}It has been told to me and I've seen it in some environments... with DP 7.01 it can occur that a sessions hang till forever or the backup is aborted after 7200 second. The message displayed shows IPC errors (i.e error below) as no more data from Disk Agent or\u2026","rel":"","context":"In "HowTo"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2973,"url":"https:\/\/www.data-protector.org\/wordpress\/2016\/04\/hpe-cloud-optimizer\/","url_meta":{"origin":4164,"position":5},"title":"HPE Cloud Optimizer","date":"1. April 2016","format":false,"excerpt":"{:en} It is often found during the backup of virtual machines that performance of a backup job is disappointing. This is not necessarily a Data Protector issue, usually it is a sign for bottle necks in the IT environment you want to fix. At this point HPE can support with\u2026","rel":"","context":"In "SCD"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.data-protector.org\/wordpress\/wp-json\/wp\/v2\/posts\/4164"}],"collection":[{"href":"https:\/\/www.data-protector.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.data-protector.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.data-protector.org\/wordpress\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.data-protector.org\/wordpress\/wp-json\/wp\/v2\/comments?post=4164"}],"version-history":[{"count":27,"href":"https:\/\/www.data-protector.org\/wordpress\/wp-json\/wp\/v2\/posts\/4164\/revisions"}],"predecessor-version":[{"id":4214,"href":"https:\/\/www.data-protector.org\/wordpress\/wp-json\/wp\/v2\/posts\/4164\/revisions\/4214"}],"wp:attachment":[{"href":"https:\/\/www.data-protector.org\/wordpress\/wp-json\/wp\/v2\/media?parent=4164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.data-protector.org\/wordpress\/wp-json\/wp\/v2\/categories?post=4164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.data-protector.org\/wordpress\/wp-json\/wp\/v2\/tags?post=4164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}