Is it possible to run a backup while the DB is in use?

jcat

FYI - I'm evaluating SQL Backup 6 Pro

Normally I would avoid this, but I know that the native SQL backup allows for backups while the DB is in use and I've seen no performance problems when doing that.

I tested this with SQL Backup 6 on maximum compression and found the test server became completely unusable. Is there a set of options that would allow this? Is it just that maximum compression is eating up so much CPU that the server stalls out?

Thanks for your help.

EDIT: I also tried a test backup set at compression level "1" and while it was certainly less intensive than "4" it still made the server essentially unusable.

SQL 2008 x64, virtualized with VMware ESX vSphere 4.1 - the VM has 16GB RAM and the physical server has 2x Quad-Core AMD Opterons.

petey

I do not have any experience using VMware ESX vSphere 4.1, so all my comments are based on running SQL Backup on non-virtualised SQL Server instances.

Compression level 1 uses the least CPU resources, and 4 the most. Among the factors that would affect the responsiveness of the system when a SQL Backup (SQB) backup is performed would be:

- number of backup threads used
The larger the number of threads used, the more CPU load that will be imposed on the system

- type of data
If your database contains a lot of compressed data, running a SQB backup would actually incur a heavier CPU load. If your compression ratio is 60% or less, then SQB is probably spending a lot of time trying to compress data that is uncompressible.

- the CPU load on the machine as a whole
If the CPU load is already at 60% or more, running a SQB backup would certainly impose an even heavier load on the system, causing it to be unresponsive

- thread priority
There is a THREADPRIORITY option in SQB, that allows you to set at which priority the SQB process will run at. Default value is 3, which is normal priority. Setting a value of 2 or lower may improve the responsiveness of the system while SQB is running, at the expense of taking a longer time to complete the backup.