{"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":3514,"url":"https:\/\/www.data-protector.org\/wordpress\/2017\/09\/data-protector-10-patch-bundle-a-10-01-build-307\/","url_meta":{"origin":4164,"position":0},"title":"Data Protector 10 \u2013 Patch Bundle A.10.01 (Build 307)","date":"28. September 2017","format":false,"excerpt":"{:en}The Patch Bundle A.10.01 (Build 307) for Data Protector 10 has been released on September 28, 2017. This first cumulative patch can be installed on top of A.10.00. It is recommended to install it on all Installation Servers (IS) and the Cell Managers (CM). Then push the new packages from\u2026","rel":"","context":"In "DP A.10.00"","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":831,"url":"https:\/\/www.data-protector.org\/wordpress\/2011\/12\/slow-backup-on-windows-2008-r2\/","url_meta":{"origin":4164,"position":2},"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":3952,"url":"https:\/\/www.data-protector.org\/wordpress\/2018\/04\/effectively-manage-omnirc-options\/","url_meta":{"origin":4164,"position":3},"title":"Effectively manage omnirc options","date":"20. April 2018","format":false,"excerpt":"{:en}Data Protector is highly customizable product with a wide range of configuration options. While the any\u00a0global options applies to the whole Cell, the omnirc configuration file allows the user to tweak a specific client or just a module on that client system. It you have to manage a large amount\u2026","rel":"","context":"In "HowTo"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"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":4},"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":935,"url":"https:\/\/www.data-protector.org\/wordpress\/2012\/05\/dp-7-0-omnirc-variable\/","url_meta":{"origin":4164,"position":5},"title":"DP 7.0 – omnirc – connection abort","date":"4. May 2012","format":false,"excerpt":"{:de}Mit Data Protector 7.0 kann es beim Backup zu untenstehender Fehlermeldung kommen. Der Fehler wurde in einem Case best\u00e4tigt. Zur L\u00f6sung kann in der omnirc die Variable OB2INETTIMEOUT=10 auf den betroffenen Clients aktiviert werden. Danke an Dennis Lorentsen der die Information bereitgestellt hat. Error: [Major] From: BSM@cellmanager.local \u201ctest_1\u2033 Time: 01-05-2012\u2026","rel":"","context":"In "Bugs"","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}]}}