jeremylc
1 Nickel

MS SQL Server backups - poor performance with many small databases

I have to clients with over 300 (small, <4MB) databases each and it seems that the NetWorker client for MS SQL Server takes about 2.5-3 minutes per database to backup, and with this many databases that means about 14 hours per client.  Further, these two clients take about 30 minutes just to START backing up due to the various checking and setup NetWorker does at the start of a job.

Is there any way to speed this up?

NetWorker 8.2 and SQL Server 2012 Express.

0 Kudos
12 Replies
ble1
5 Iridium

Re: MS SQL Server backups - poor performance with many small databases

I have similar environment, but no issues.  Difference is only that I use real sql server and not express one.  I mention this as in the past I have seen with some older instance of express that backup would take longer. Not sure if that is applicable to your case or not.

0 Kudos
jeremylc
1 Nickel

Re: MS SQL Server backups - poor performance with many small databases

We do use SQL Server 2012 R2 Enterprise edition on our other servers, but these use SQL Express because our vendor built their app using SQL Express and I can't change it.

0 Kudos
ble1
5 Iridium

Re: MS SQL Server backups - poor performance with many small databases

In my case we had only 4 DBs on express and I was happy when that went away. I suspect, or at least I suspected at that time, that something with VDI calls is not the same in express version and I guess some query (maybe even on NMM side) waits until timeout or whatever. I'm not sure about that theory as I never had any errors, but... which exact version of NMM do you use?

0 Kudos
jeremylc
1 Nickel

Re: MS SQL Server backups - poor performance with many small databases

It looks like 8.2.1.6.Build:806

0 Kudos
jeremylc
1 Nickel

Re: MS SQL Server backups - poor performance with many small databases

Just to elaborate a bit, it's only about 6.5-7 GB of data, too. It's just that each database is taking 2-3 minutes to backup.

0 Kudos
Highlighted
ble1
5 Iridium

Re: MS SQL Server backups - poor performance with many small databases

jeremylc wrote:

It looks like 8.2.1.6.Build:806

There are never patches at ftp://ftp.legato.com/pub/NetWorker/NMM/Cumulative_Hotfixes/ but I doubt they will fix anything in respect to your issue. You can try to log a ticket with EMC - perhaps there is solution, but I'm unaware of it.

0 Kudos
carlos.alfaro
1 Nickel

Re: MS SQL Server backups - poor performance with many small databases

Hi, you try to increase the parallelism of the client, when small databases downtime exists between them, put client parallelism in 8 or higher.

Paralle.png

0 Kudos
jeremylc
1 Nickel

Re: MS SQL Server backups - poor performance with many small databases

I changed the parallelism and I'm running a manual save now, but it does not appear to be going any faster. Moreover, it's still going in series, one DB at a time through the ~330 databases on the server.

0 Kudos
ble1
5 Iridium

Re: MS SQL Server backups - poor performance with many small databases

I know you have many databases, but for test, can you list databases (say 30) and test that backup? I assume you use instance based backup so all is sequential and you get single parallelism.  Try just for test to create group with 30 individual databases in (MSSQL:<DB NAME>) and check if you get delayed execution as well. Simply create separate client and group to test this.

0 Kudos