Highlighted

Using GROUPSIZE for SQL Server

Dear All,

I am trying to use GROUPSIZE parameter but it seems NB is ignoring the parameters. Below are the Script file contents:

GROUPSIZE 3
OPERATION BACKUP
DATABASE "RiazTest1"
SQLHOST "<DB Server>"
SQLINSTANCE "<Instance>"
NBSERVER "<NB Master>"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE
OPERATION BACKUP
DATABASE "RiazTest2"
SQLHOST "<DB Server>"
SQLINSTANCE "<Instance>"
NBSERVER "<NB Master>"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE
OPERATION BACKUP
DATABASE "RiazTest3"
SQLHOST "<DB Server>"
SQLINSTANCE "<Instance>"
NBSERVER "<NB Master>"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE

I am attaching the NB console output.

Environment:

NB Master: 8.0

OS: Windows 2008 R2

Client: 8.0

Client OS: Windows 2008 R2

SQL Server: 2012 SP3

I've even tried with database $ALL option without any success.

Can you please guide me how to correctly use GROUPSIZE?

 

Thanks

1 Solution

Accepted Solutions
Highlighted
Accepted Solution!

Re: Using GROUPSIZE for SQL Server

This is weird - have a look at this entry at the beginning of the backup:

14:50:52.531 [6568.8332] <4> CDBbackmain:: OperationsAreGroupable: INF - The batch file is not groupable because a standard policy type has been selected for backup.

EDIT: 

I think this entry in the manual is key :

The method for aggregating database Snapshot Client backups is called backup "grouping".

So - only supported for Snapshot Client policies? 

View solution in original post

24 Replies
Highlighted

Re: Using GROUPSIZE for SQL Server

I am not sure what exactly you are trying to achieve, but GROUPSIZE defines how many databases backed up (snapshotted) at once and if you look into your log file you'll see that all 3 DBs have been snapped at the same time. So the script is working as expected, if you are looking to implement any other behaviour please explain what do you want.

Highlighted

Re: Using GROUPSIZE for SQL Server

Thanks for your reply.

As per my understanding, when we use groupsize, the backup of grouped databases should go to one SQL Server Image which is not happening in my scenario.

As an example, please have a look at the atatched screenshot taken from the Netbackup SQL Admin Guide that shows how a grouped backup output should look like.

Regards,

Highlighted

Re: Using GROUPSIZE for SQL Server

Thinking of that the screenshot has been taken in 2006 things may have changed on the logging side.

Still, I hazard to guess it's likely working as expected but with logging format being different. The reason why I think this way is because the log only mention one Backup ID been created:  backupid=<DB Server>_1526370994

To confirm, I would use bplist -l -R -C <client> -backupid=<DB SERVER>_1526370994 -t 15 /

and check how many files are in that image

Some references https://www.veritas.com/support/en_US/doc/17221771-126559330-0/id-SF930841970-126559330

 

Highlighted

Re: Using GROUPSIZE for SQL Server

lol...I didn't saw the date in the output; it is still there in NB 8.0 SQL Admin Guide.

Anyways, I think bplist doesn't support -backupid clause because I get the following error:

bplist: unrecognized option -backupid

Neverthless, when I check the catalog from NB console, I see three backup images while I am expecting one.

Regards,

Highlighted

Re: Using GROUPSIZE for SQL Server

I would say it's inconsistent behaviour as the log you've shared clearly mentions only one backup id created.

I think I should also correct myself, for -backupid switch you need to use bpflist

Highlighted

Re: Using GROUPSIZE for SQL Server

Seems there are 3 images :

Using backup image <DB Server>.MSSQL7.<DB Server>\<Instance>.db.RiazTest1.~.7.001of001.20180515115807..C

Using backup image <DB Server>.MSSQL7.<DB Server>\<Instance>.db.RiazTest2.~.7.001of001.20180515115902..C

Using backup image <DB Server>.MSSQL7.<DB Server>\<Instance>.db.RiazTest3.~.7.001of001.20180515115951..C

Please create dbclient log folder (if it does not exist already) and set logging level at 3. 
dbclient log should tell us how NBU is interpreting the script. 

Highlighted

Re: Using GROUPSIZE for SQL Server

Yes, It's very confusing. Even when I check using bplist with -k <Policy>, I see three records and not one.

Highlighted

Re: Using GROUPSIZE for SQL Server

Please create dbclient log folder (if it does not exist already) and set logging level at 3. 
dbclient log should tell us how NBU is interpreting the script. 

Highlighted

Re: Using GROUPSIZE for SQL Server

The way I read the manual, the SQL guide says all images are tracked under a single backup id but it does not say they stored in the same image file.

Re: Using GROUPSIZE for SQL Server

Thanks for your reply.

Please see attached logs from dbclient folder.

Regards,

Highlighted

Re: Using GROUPSIZE for SQL Server

I still think it's a terminology issue rather than incorrect behavior as the "stored together" is related to split-mirror backups, not to standalone backups:

" The SQL Server agent provides a method in which multiple databases can be
quiesced together and split-off to form a single snapshot. This method minimizes
the usage of system resources if the databases exist on a single volume. This
happens because the aggregation of constituent files uses one snapshot volume
instead of one per database. The method for aggregating database Snapshot Client
backups is called backup "grouping".
When databases are backed up in a group, all of the databases are quiesced
simultaneously. The constituent files of all databases are backed up to a single
storage image under the same backup ID. This means that an "import and copy"
procedure would use only one image to export all of the database backups in the
group

 "

If you have indeed a split-mirror set up, I would question why these images are taken separately. Otherwise, I see no references that NBU client should package all databases into the same backup image.

Highlighted

Re: Using GROUPSIZE for SQL Server

Thanks for your reply. From below excerpt, what I understand is there will be one image for all the databases backed up in a group:

The constituent files of all databases are backed up to a single storage image under the same backup ID. This means that an "import and copy" procedure would use only one image to export all of the database backups in the group

Anyways, I will wait for feedback from other Gurus to see what they think about it.

Regards,

Highlighted

Re: Using GROUPSIZE for SQL Server

Hello,

it is only terminology play. You should have, for example:

<DB Server>_1531256478 (1 NetBackup "image") with 3 "files" in it:

<DB Server>.MSSQL7.<DB Server>\<Instance>.db.RiazTest1.~.7.001of001.20180515115807..C

<DB Server>.MSSQL7.<DB Server>\<Instance>.db.RiazTest2.~.7.001of001.20180515115902..C

<DB Server>.MSSQL7.<DB Server>\<Instance>.db.RiazTest3.~.7.001of001.20180515115951..C

Regards

Michal

Highlighted

Re: Using GROUPSIZE for SQL Server

Thanks for the reply.

So there should be one backup ID shown in the catalog for the grouped databases' backup?

Highlighted
Accepted Solution!

Re: Using GROUPSIZE for SQL Server

This is weird - have a look at this entry at the beginning of the backup:

14:50:52.531 [6568.8332] <4> CDBbackmain:: OperationsAreGroupable: INF - The batch file is not groupable because a standard policy type has been selected for backup.

EDIT: 

I think this entry in the manual is key :

The method for aggregating database Snapshot Client backups is called backup "grouping".

So - only supported for Snapshot Client policies? 

View solution in original post

Highlighted

Re: Using GROUPSIZE for SQL Server

Hello,

and yes - it must be a policy with "Snapshot" attribute ON.

With snapshot on the volume level, where all databases have files at that volume, it is obvious that all database backups are from exactly the same time (and thus <timestamp> in <client>_<timestamp> = backup ID, is the same.)

With "native" non-snapshot mssql db backups you cannot achiveve this, there is no mechanism for "exactly the same time" for backups of more dbs .

Regards

Highlighted

Re: Using GROUPSIZE for SQL Server

Thank you veryone for your feedback.

Is there any other option using which multiple databases' backups can be combined as one backup image?

Regards,

Highlighted

Re: Using GROUPSIZE for SQL Server

Why do you want 1 image only?

You can try to use BATCHSIZE instead. There will be separate images, but the backups will run simultaneously. 
See this TN for explanation: https://www.veritas.com/support/en_US/article.100016449

 

Highlighted

Re: Using GROUPSIZE for SQL Server

We had many SQL images pending replication to DR and they have been increasing.

This is because we have many small databases although many of them are under 5GB in size. We raised the issue to local vendor (whom we have support contract) who said (in addition to other points) that the number of backup images (not the size) is what taking consierable time (it's a de-dup storage). So we were looking into possibility of combining multiple database backup images into one NB backup image.

Regards,