Currently, I am investigating some way to more efficiently backup 4 Production SQL Databases (3 different servers/instances) and would like to know if anyone else has had the problems we are having and if anyone has any recommendations.
- Database 1: 80GB
- Database 2: 74GB
- Database 3: 5GB
- Database 4: 30GB
Database 1 & 2 are still currently SQL Server 2005 with no plans on upgrading them right now
Database 3 & 4 are for new systems and will be growing rapidly over the next several months. Currently Mirrored.
The problem we are running into is that there are maintenance tasks on the servers that run for these databases for backups that take roughly 30 min for Database 1 & 2. Now these all run at the same time as several other environment backups that the Sys Eng have running.
After the backup, a schedules task using 7-zip is run to compress the backups. All of this is killing the CPU during that time frame and if for whatever reason a job/task gets locked up, it will affect the entire system.
Now due to the business we are in, we have a very limited timeframe we can run these backup jobs, and these backups need to be done. We also need to start encrypting the backups after the compression to further our HIPPA compliance... yep...
Another part of the problem that we are are aware of is we have 20 other databases doing the same thing at the same time, which is killing the SAN, all of which we know and believe don't need these backup tasks.
So aside from that which we are working on, we are trying new methods of backing up databases to try to take the CPU load off the database servers as much as possible and hopefully do the backups for the databases incrementally.
Any suggestions? Tools, methods? We've tested Red-Gates SQL backup pro tool, SQL backupandftp freeware and using SSIS but none have seemed to accomplish what we were hoping for (ability to quickly accomplish the task and/or run on another server taking the CPU off the DB server).