TDP for SQL - doubled backup

[Dikajos]

Hi,
We noticed that in our environment there is doubled very big backup of SQL.
It looks like each command tdpsqlc backup xxxx full/log/diff is performing backup twice.
From SQL level we can see that there are two restore points available.

When we are checking FS information there is second FS in below convention:

NodeName\data\0001
NodeName\data\0002

Of course 0002 shouldn't be existing.
When we are performing below command:
select FILESPACE_NAME, STATE, TYPE, HL_NAME, LL_NAME, BACKUP_DATE from backups where node_name='NodeName.SQL' and FILESPACE_ID='X' and HL_NAME like 'XXXX%'
We are receiving the same output for both Filespaces, even the time so it looks like backup is done once but sent to two FileSpaces.


From SQL Logs we can see few things which are not normal in our opinion:
1. Number of dump devices: 2
2. Type=VIRTUAL_DEVICE('TDPSQL-00003368-0000',TDPSQL-00003368-0001')


We have done below tests:
1. Backup was performed for TestDB to newly created node but it's the same situation.
2. SQL Dump was performed to local disk and only one .bak file was created.


Take a look in to below version information:
BA Version 7.1.6.5
TDPSQL 7.1.6.2 FlashCopyManager 4.1.6.2
Windows Server 2008 R2 x64
SQL Server 2008 SP4 - 10.0.6535.0


Did any one of you have this kind of situation? Or maybe you know where to search the reason of doubled FS during each backup.

 

[Dikajos]

I forgot to mention about TSM Version:
Storage Management Server for Windows - Version 7, Release 1, Level 3.0

 

[RecoveryOne]

For comparison TDPSQL 7.1.6.0, FCM 4.1.6.0
Server on AIX is 7.1.7.0
W2k8 R2 64bit with SQL Server 2008 SP4 10.0.6241.0

Now, I'm just starting to dig into Data Protection for SQL on a few of our servers and by no means an expert.

From one of my TDP MSSQL nodes I have a meta filespace and a data filespace. For example:

KDBTEST_SQL      KDBTEST\meta\0000        1       TDP MSSQL Win64 API:SqlData     No      0 bytes 0.0
KDBTEST_SQL      KDBTEST\data\0001        2       TDP MSSQL Win64 API:SqlData     No      0 bytes 0.0

Also, in the FCM properties what setting do you have for Performance?
Right now, all the ones I have set are default.
DP Buffers =3
DB Buffer size =1024
SQL Buffers =0
SQL Buffer size =1024
Stripes=1

 

[Dikajos]

Hi,
Thank you for your respond.

For my case, we have double one meta FS and double Data FS (\0001 and \0002).

Node Name Filespace Name FSID Type Platform Filespace Is Filespace Unicode? Capacity Pct Util
--------------- ----------- ---- -------- --------- ---------- ----------- -----
XXXXX.SQL XXXXX\meta\0000 1 WinNT API:SqlData No 0 KB 0.0

XXXXX.SQL XXXXX\data\0001 2 WinNT API:SqlData No 0 KB 0.0

XXXXX.SQL XXXXX\data\0002 3 WinNT API:SqlData No 0 KB 0.0

We were checking our parameters, read about it and we didn't find anything not correct. Please take a look in to our settings:

SQLAUTHentication INTegrated
MOUNTWaitfordata Yes
BACKUPMethod LEGACY
DIFFESTimate 20
BUFFers 5
BUFFERSIze 1024
STRIPes 2
SQLBUFFers 2
SQLBUFFERSIze 1024
LOGPrune 60
LANGuage ENG
BACKUPDestination TSM
REMOTEDSMAgentnode

 

[hogmaster]

Hello,
Change stripes to 1 and repeat the test.
Currently you are sending 50% of the db backup on "session" 1 and the rest on "session" 2.

Havinge more stripes will increase your backup performance. (However you restore may be slow if you migrate the data to tape, thne you may run in to a contention problem)

After your test confirm that all of the data is sent to only one filespace. then chnage the values back to 2 or even 3 and check the backup performance.

 

[Dikajos]

Hi,
We tested your proposition and it looks like after changing Stripes parameter it's using only one stream and writing only to one FS. It's very good as it looks like it will resolve our problem.

In the tests the backup was much longer with one stripe but it's creating only one file so it's saving space in our case. When we once again change to 2 strips, then it's once again used two FS's so we will use for some time 1 Stripes and redundant data will disappear.

We read about Stripes parameter and it's about using more streams, splitting backup. We still don't know why this option is not only splitting data for two FS, but it's consuming twice the size.

In our case we are using Full backup for 1TB database. During Backup Job is creating two FS and putting 1TB file in each so together one backup is consuming 2TB. Does anyone know why it's happening?


Once again thank you for your comment !!! We will test it on production (need to wait till weekend), and return with output.

 

[marclant]

In our case we are using Full backup for 1TB database. During Backup Job is creating two FS and putting 1TB file in each so together one backup is consuming 2TB. Does anyone know why it's happening?

What data are you using to come up with that conclusion? It might logically appear to use 2TB, but physically its' not. Did you look at QUERY OCCUPANCY for that node?

 

[Dikajos]

Database size is 1TB.
From TSM Site we checked occupancy(physical/logical). We also checked it by special commands which were counting size of each files and these numbers are also doubled.

 

[marclant]

I'm not convinced of that. Because you have two streams, you have parts of the object sent in one stream and another part of the same object in the other stream. Logically, each object is reported twice, but that doesn't necessarily mean that it's physically occupying 2 TB.

We also checked it by special commands

Which commands?

 

[Dikajos]

Hi,
Please take a look in to below command:
select cast(float(sum(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count(bk.bfsize) as number_of_objects from backups b, backup_objects bk where b.object_id=bk.objid and b.type='FILE' and b.node_name='XXXXX.SQL'

The output for above command is 2.4 TB.

We also checked and counted amount of backups from the TDPO for SQL (MMC) and summary of the data is 1.2 TB.

 

[marclant]

select cast(float(sum(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count(bk.bfsize) as number_of_objects from backups b, backup_objects bk where b.object_id=bk.objid and b.type='FILE' and b.node_name='XXXXX.SQL'

That query is probably counting it logically, not physically. Because of the two streams, there the data is split in two, and stored in 2 filespaces (1 for each stream, so that it can restore the same way). However, it's reporting the size of the object in each filespace, rather than the size of the amount of data that as included in that stream.

We also checked and counted amount of backups from the TDPO for SQL (MMC) and summary of the data is 1.2 TB.

That's how much data is stored on the Spectrum Protect Server. It would make no sense to split the data in two, send it in two streams, re-assemble both streams back on the Spectrum Protect Server to store two copies of the same data. The Spectrum Protect Server is not aware of the data itself, it's just blobs of bytes, it would not know how to re-assemble it. Only the client knows because it's the one that split it into two streams.

 

[Dikajos]

Hi,
We were using this command for other cases and from what we know it's counting bytes of the files located on the TSM Storage, so exactly the space which is consumed. This command was also used together with TSM Support as PMR was created for other case where the command was used.

Yes, exactly. 1.2 TB is the space which should be used by this database. From TSM and TDP for SQL site we see only one version of these files and it's counting it as 1.2 TB. But from SQL site, we checked it and there are available two restore points done on the same time.

In our scope we have small Disk storage so we see exactly how many volumes are used and it should be 1.2 TB consumed, but it's consuming 2.4 TB of space, exactly doubled.

After changing Stripes parameter, we compared information, and now the newest log backup wasn't duplicated, the space usage is decreasing. But it's small amount as it's log backups. The biggest difference we will notice after weekend (Full bcakup).

I agree with you that description of the Stripes option saying about spiting streams, not doubling data. But in our case unfortunately it was doubling used storage. And we still don't know why ;/

 

[marclant]

I can't explain it.