Speed up SQL database backup

[Einas]

Our DBAs want to speed up SQL database backup of a database of 4 GB data size into a network drive on another server. We are using WINNT 4.0 (SP3)& SQL Server 6.5 (SP4). We know that "Backup buffer size" SQL configuration parameter is a way of doing it. Below are results of increasing the value:
1. If the value is more than 10, the runnable value is always 10 even when stopping and starting SQL server services.
2. The SQL backup took much longer time than before when increasing the value from 5 to 10.

How to properly do it ? What could be the problem ? and are there any other considerations to meet ?

Note that total RAM of servers is 512 MB out of which 128 MB is allocated to SQL server.

Anyone's experience and cooperation will be highly appreciated.
Regards,
Einas (DBA)

[Don Romano]

On 12/21/98 12:30:49 AM, Einas wrote:
> Our DBAs want to speed up SQL database backup of a database of 4 GB data
> size into a network drive on another server. We are using WINNT 4.0 (SP3)&
> SQL Server 6.5 (SP4). We know that "Backup buffer size" SQL
> configuration parameter is a way of doing it. Below are results of
> increasing the value:
1. If the value is more than 10, the runnable value
> is always 10 even when stopping and starting SQL server services.
2. The
> SQL backup took much longer time than before when increasing the value from
> 5 to 10.

How to properly do it ? What could be the problem ? and are
> there any other considerations to meet ?

Note that total RAM of servers
> is 512 MB out of which 128 MB is allocated to SQL server.

Anyone's
> experience and cooperation will be highly appreciated.
Regards,
Einas (DBA)

[Don Romano]

Sorry 'bout the previous reply... fat fingers near the 'Enter' key. :-)

Backup Buffer Size maximum is 10 so you won't get it above that. You might want to be careful on that one because each "1" is the size in 32-page increments ((2048 - overhead) * 32) so you could really be starting to stress your system by overallocating RAM. I'd start with 1 and increase and test by one to see where you're point of dimishing returns happens. Also... I'm not sure if the RAM for backup buffer comes out of SQL Server allocation (I would think so) or just the available RAM on the server.

You might want to look at Backup Threads as a parameter to configure as well.

What else is on the server running SQL Server? You seem to have most of the memory available to other applications. If you can spare more RAM for SQL Server than allocate the RAM to it.

Dumb question... what is the bottleneck to your backups. How fast is the network connection between your SQL Server and the backup device? Try to do a local disk backup (on a different disk than your database and log) and see if performance is better. Network backups can take a long time so to improve the speed of the backup is almost the same as improving the throughput of the network connection. Maybe it would be faster to backup locally and then just copy the backup file to the network drive.

Look at all bottleneck possibilities: server utilization, disk i/o, memory, paging, network, etc...

Just some thoughts,

Don

On 12/21/98 12:30:49 AM, Einas wrote:
> Our DBAs want to speed up SQL database backup of a database of 4 GB data
> size into a network drive on another server. We are using WINNT 4.0 (SP3)&
> SQL Server 6.5 (SP4). We know that "Backup buffer size" SQL
> configuration parameter is a way of doing it. Below are results of
> increasing the value:
1. If the value is more than 10, the runnable value
> is always 10 even when stopping and starting SQL server services.
2. The
> SQL backup took much longer time than before when increasing the value from
> 5 to 10.

How to properly do it ? What could be the problem ? and are
> there any other considerations to meet ?

Note that total RAM of servers
> is 512 MB out of which 128 MB is allocated to SQL server.

Anyone's
> experience and cooperation will be highly appreciated.
Regards,
Einas (DBA)