Highlighted
Contributor.. Contributor..
Contributor..
1720 views

How can I set DP to backup MSSQL DB more than 1 DB at a time of backup?

Jump to solution

Hi

I saw in MSSQL integration manual mentioned that I can backup more than 1 DB at a time of backup to multiple devices but I do not know how to configure this. I have found option "Concurrent streams" which will parallel 1 DB to multiple stream which not what I want. What I want is to backup multiple DB at the same time with 1 stream for each DB to multiple devices. How can I do that?

I am currently using DP ver 8.14 and MSSQL version is 2005, 2008 and 2012.

Thank you in advance for all your suggestion to my question.

 

0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi,

Selecting databases manually, as already mentioned in one of the earlier posts is one possibility to get the "Database parallelism" working. If you want to have this also working for an "entire SQL server" selection then you'll need to upgrade to the latest DP9 version and get a fix from support. The fix will also be included in a future version.

The related change request ID is: QCCR2A68293..

New environment parameters that have been introduced for this:

OB2_PREFER_DATABASE_PARALLELISM
OB2_DEFAULT_CONCURRENT_STREAMS

Hope this helps.

Koen

View solution in original post

9 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

To backup multiple databases in the same instance at the same time you need multiple available backup devices and configure the correct Load balancing (Min/Max) in the Destination tab of the backup spec. There are some differences between the backup deivces. What is the backup device of your choice?

Please use the Accept Solution button next to my post and assign a KUDO (thumbs up icon) if this works for you.

Regards,
Sebastian Koehler

---
Please use the Like button below, if you find this post useful.
0 Likes
Highlighted
Contributor.. Contributor..
Contributor..

My device is VTL emulate as LTO-6 configured with 1 concurrency. I have testing create backup policy with using 2 drive and load balance is set to min=1 and max=5. During backup it is still backup only 1 DB at a time.

Regards

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Hi all,

the trick is to increase the concurrency for the database you want to backup. Unfortunately this caanot be configured as required for the whole instance (in this case I was able to get up to 6 streams only). Instead you have to select the databases manually (deselect on instance and select any database). In last tabe in summary now all databases should be listed. When changing the properties of the objects (databases) you can increase the number of concurrency for each object according to your needs. I recommend to have StoreOnce (or many filelibrary devices) as backup target, as you can then select more streams. When working with physical devices or VTL's you need as many drives as concurrency configured. With StoreOnce it is handled different. Please keep in mind that selecting a single database for the backup will change the behavior for the backup specification - new databases are not backed up automatically (this is possible only when selecting on instance level).

Best regards

Daniel

-----------
Please assign Kudos - How to assign...
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hello

 

With SQL integration you can increase streams concurrency it means backup one database in more than one device at the same time, but is not possible increase device concurrency, copy more than one database in parallel.

It is described into integration guide page 147.

Select the devices. Click
Properties
to set the media pool and preallocation policy. The device concurrency is set to 1 and cannot be changed. For more information on options, press F1

A possible workaround will be use VSS integration instead SQL agent

Best Regards

 

 

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi,

Selecting databases manually, as already mentioned in one of the earlier posts is one possibility to get the "Database parallelism" working. If you want to have this also working for an "entire SQL server" selection then you'll need to upgrade to the latest DP9 version and get a fix from support. The fix will also be included in a future version.

The related change request ID is: QCCR2A68293..

New environment parameters that have been introduced for this:

OB2_PREFER_DATABASE_PARALLELISM
OB2_DEFAULT_CONCURRENT_STREAMS

Hope this helps.

Koen

View solution in original post

Highlighted
Contributor.. Contributor..
Contributor..

Hi All

Thank you for all your suggestion.  I did not know if my question is clear enough as I may confusing about parallelsm and concurrency. To make it clear I will try to explain my question again.

Assume I have MSSQL instance ABC which have DB 1, 2, 3 and 4.  I have test create backup policy by choosing all DB by selecting instance ABC instead of DB 1, 2, 3 and 4. I have assign 2 drives as destination for this policy. The default "Concurrect stream" was set to 1 and no additional setting was set. During backup 1 drive will active while another drive will be inactive/waiting and DB will backup in sequence 1 -> 2 -> 3 -> 4 to the active drive until job finish. DB 2 will start backup after DB 1 was complete (The same to 3 and 4). This thing also happen even I try manual select each DB instead of entire instance.

 

I try to find out a way to set policy that it will backup DB 1 and 2 each to seperate drive at the same time and will increase in parallel if I assing more drive.

I have been using NetBackup before and I saw that NB can perform something like that  so I am curious if DP can do the same thing and how to do it.

 

Regards

Kiatkachorn

 

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Kiatkachorn,

Your scenario is exactly what I explained.

Koen

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

HI Koen, Kiatkachorn

Thanks for your input but I have tried to reproduce QCCR2A68293 without success, I mean that using DP 9.07_110 I have been able to backup two databases at the same time using two different devices please check attachment file capture.jpg

Not sure under with circumstances QCCR2A68293 happen but it seems to works fine in my environment, for that just necessary the following

1º Select entire instance as source

2º Select two different drive

3º Configure SQL concurrence streams, maximum value will be equal to number of devices selected please check capture2.JPG

Best Regards

 

 

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

@JBasilio wrote:

Thanks for your input but I have tried to reproduce QCCR2A68293 without success, I mean that using DP 9.07_110 I have been able to backup two databases at the same time using two different devices please check attachment file capture.jpg


Hi Jose,

based on your screenshot you backup the same database with two streams (DB/0 and DB/1) to different devices. It seems that the fix QCCR2A68293 allows parallel VDI backup operation. Great news! Thanks @Koen Verbelen for sharing!

Regards,
Sebastian Koehler

---
Please use the Like button below, if you find this post useful.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.