Hi Folks

I am facing an issue with backing up one SQL server. We have one application deployed for 2 different customers and I am backing up these 2 databases server in same protection group. 

Customer A's Database server is very busy because they upload heavy files into application which mackes databases busy continously. 
Customer B's Database server isn't much more activ e but is been in general use since they are uploading small and average files. 

Now when DPM backing up this protection group, Database B server is backing up normally. But Server A databases are taking longtime (15-18 Hours) to complete syncing and Backing up. 
This is excpected to take time since there are lot of changes in databases but not expecting such a long time where next scheduled backup jobs are overlapping with old ones. 

I just want to know How DPM track SQL changes on protected Server like through voume filter or bitmap? What precuation I should take to resolve this issue!

Here are few points related to these databases. 

1. Databases sizes are from 30 GB to 300 GB
2. These are Simple mode databases but files are spreaded through .ndf files. 
3. Application is loading heavy files interacting those databases. 
4. I have set Sync Frequency 4 hours and Express full backups 2 times in a day 10AM, 10PM
5. Support guys are taking manual backups randomly in .bak format. 

Also during DB backups, Sysem file is creating and growing upto 50-60 GB.




Please help on this. 

1. I just want to know How DPM track SQL changes on protected Server like through voume filter or bitmap?

2. What precaution I should take to resolve this issue!

3. These are Simple mode databases but files are spreaded through .ndf files. 

4. I have set Sync Frequency 4 hours and Express full backups 2 times in a day 10AM, 10PM

Also during DB backups, Sysem file is creating and growing upto 50-60 GB.

1. Bitmap level tracking happens only for File-servers. Not applicable for Application level data.

2. If possible, implement SQL AG and start taking backup from the 'passive node'.

3. Make sure you have "Full recovery model" in SQL.

4. I'm wondering why are you taking Express full backups. You said the database is accessed heavily and info changed is too much. So, you need to implement "INCREMENTAL backups: must faster solution". Express full is 'bound' to take time and also putting slight impact on performance of SQL as well.

After this increase the sync frequency and you should be good.

Hope this helps.

Hi Abhijeet 

1. If Bitmap level tracking is for File-Servers, then how DPM track sql changes for SIMPLE mode databases!

2. These are two separate independednt SQL servers, not in cluster. 

3. Simple recovery mode is suggested by application developer, this isn't in our hand to change it. 

Hi,

Block level tracking is enabled for all protected workloads except client protection not just file servers.  Most of the time backup performance is related to disk performance, and from what I can tell there is a lot of IO occurring while there is an active snapshot, and that causes a lot of copy-on-write (COW).  For a write to a sql db, there is another read and another write before the write goes to the sql DB.   You can try adding a new disk, then move the shadow copy storage to that volume to eliminate the extra write on the SQL volume for the COW.

 c:\>vssadmin add shadowstorage /for=E: /on=F: /maxsize=unbounded

You can also run a performance monitor.

For some basic perfmon counters to help narrow down the possible bottleneck:

Perf Counters for DPM

******************

Logical Disk/Physical Disk

\%idle

100% idle to 50% idle = Healthy
49% idle to 20% idle = Warning or Monitor
19% idle to 0% idle = Critical or Out of Spec

\%Avg. Disk Sec Read or Write

.001ms to .015ms  = Healthy
.015ms to .025 = Warning or Monitor
.026ms or greater = Critical or Out of Spec

Current Disk Queue Length (for all instances)

80 requests for more than 6 minutes.

Indicates possibly excessive disk queue length.

Memory

*******

\Pool Non Paged Bytes*

Less that 60% of pool consumed=Healthy
61% - 80% of pool consumed = Warning or Monitor.
Greater than 80% pool consumed = Critical or Out of Spec.

\Pool Paged Bytes*

Less that 60% of pool consumed=Healthy
61% - 80% of pool consumed = Warning or Monitor.
Greater than 80% pool consumed = Critical or Out of Spec.

\Available Megabytes

50% of free memory available or more =Healthy
25% of free memory available = Monitor.
10% of free memory available = Warning
Less than 100MB or 5% of free memory available = Critical or Out of Spec.

Processor

*******

\%Processor Time (all instances)                                                                  

Less than 60% consumed = Healthy
51% - 90% consumed = Monitor or Caution
91% - 100% consumed = Critical

*************************************************************

Thanks Michael 

I will follow these instruction and will update it here.