Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

trxlogs backup best practices

Created: 24 Apr 2013 • Updated: 24 Apr 2013 | 4 comments

Hi,

Can anyone get insight and recommendations on what other large enterprises with SQL Server are doing, specifically around trxlogs using sql agents. Our customer has 400+ SQL servers with some degree of trxlog backup requirement(90% based using SQL agents). The amount of jobs this generates puts a huge load on Netbackup master, causing us to scale out more which adds complexity to the environment. During peak times, the time it takes to run a single DB  trxlog backup can vary greatly depending on master server load ( 30 seconds to several minutes ) which is a concern to the DBAs. There is also concern from DBA's on missing trxlog backups during maintenance windows ( OS patching(normal/emergency), NB patching, etc ).     

I am looking at best practices for a very large implementation like ours (400+ servers). My questions are:

1.      Are you using SQL agents to backup trxlogs

2.      How many SQL servers have been implemented at your environment

3.      What is trxlog backup requirement? Are you backing hourly, or 4, 6 ?

Operating Systems:

Comments 4 CommentsJump to latest comment

StefanosM's picture

Are you using media servers or just a master/media server?

And your SQL backups are going to tapes or to disk?

Mohmec's picture

Stefanos,

We are using multiple media servers.

All trx log backups go to data domain via sql agent.

Thanks

StefanosM's picture

I have a customer with almost the same environment with yours. 1200 clients and 500 SQL servers. This produce almost 11000 jobs per day, with a 600 GB netbackup catalog.

But he do not care about transaction logs for all SQL servers. We use log backups for less than 50 servers.

we have a good master server with16 hyperthreating cores and 32 GB of ram. We have 8 media servers (windows and Linux) and 22 shared LTO4 and LTO5 drives.

For SQLs we use everyday Full schedule and for file backups we use  "mess scheduling" (full backup can occur every day of the week, with frequency 7 days).

For log backups some backups are running every 4 hours and some at 2 hours depending the critically.

I do not think that all 400 of your servers need to have log backups. I'm sour that if the DBA's "sit"  with the application owners will understand that not all 400 SQLs need log backup.

Apart from that, to have better speed I could use all available media servers (load balancing) to write the LOG backups to DD using DDboost (DD ost).

Another tip, but it is questionable if you want to give this capability to DBAs is to let them run the LOG backup when they want, using the cli commands on the client.
So they can run  there critical servers more often and have a control over the backup.But be careful, there is a chance that they will overdo it.
I use this ones because a customer has systems with very little disk space and the log growth ware unpredictable. So we create scripts to run backups at 80% of the disk.
 

Mohmec's picture

This is very informative. Thanks Stefanos.