Unsolved
This post is more than 5 years old
9 Posts
0
3463
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.
ble1
14.3K Posts
0
February 5th, 2016 05:00
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.
jeremylc
9 Posts
0
February 5th, 2016 07:00
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.
ble1
14.3K Posts
0
February 5th, 2016 11:00
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?
jeremylc
9 Posts
0
February 5th, 2016 13:00
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.
jeremylc
9 Posts
0
February 5th, 2016 13:00
It looks like 8.2.1.6.Build:806
ble1
14.3K Posts
0
February 6th, 2016 15:00
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.
carlos.alfaro
28 Posts
0
February 8th, 2016 15:00
Hi, you try to increase the parallelism of the client, when small databases downtime exists between them, put client parallelism in 8 or higher.
jeremylc
9 Posts
0
February 9th, 2016 07:00
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.
ble1
14.3K Posts
0
February 9th, 2016 15:00
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: ) and check if you get delayed execution as well. Simply create separate client and group to test this.
carlos.alfaro
28 Posts
0
February 10th, 2016 20:00
It seeks to increase the parallelism.
Check the properties of the group has Savegrp parallelism >= 10.
Verify if your pool and device allow writing in parallel.
Create many copies of client SQL within same group, but each client with different SQL data bases, 30 DBs for client is good idea,as Hrvoje Crvelin says.
Please review this links:
Does the savegrp parallelism value override the client parallelism value?
Understanding NetWorker Parallelism and target sessions settings
jeremylc
9 Posts
0
February 11th, 2016 06:00
30 DBs per client? I have 4 affected servers with a combined total of just under 1000 databases. I would have to manually create a total of 34 clients to back these up. I was hoping for a less labor-intensive solution. If there's a way to create clients from, say, PowerShell, then I could do that and automate the process.
The group parallelism isn't set (== 0, default to client parallelism), but I can test that parameter and see if it helps, too.
ble1
14.3K Posts
0
February 11th, 2016 14:00
This is just for test to see how it behaves. You don't have to configure them all.