Jeremy Carter

I wrote this script quite awhile ago and it seems like I use it pretty much every day. I figured if I used it so much, then others would probably find a good use for it to. It parses the backup history tables in MSDB and returns the relevant information like how long the backup took, what was the size of the backup, what user took the backup, is it a copy only backup, etc.

As written below it returns information for full and differential backups taken in the last week for all databases.

SELECT bu.database_name as [DB Name],
name as [Backup Name],
CASE bu.type
WHEN 'D' THEN 'FULL'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as [Backup Type],
CASE bu.is_copy_only
WHEN '1' THEN 'Copy'
ELSE ''
END as [Copy],
bf.physical_device_name as [Physcial Device Name],
bu.backup_start_date as [Start Date],
bu.backup_finish_date as [Finish Date],
CAST(DATEDIFF(s, bu.backup_start_date, bu.backup_finish_date)/3600 AS varchar) + ' hours, '
 + CAST((DATEDIFF(s, bu.backup_start_date, bu.backup_finish_date))/60%60 AS varchar)+ ' minutes, '
+ CAST((DATEDIFF(s, bu.backup_start_date, bu.backup_finish_date))%60 AS varchar)+ ' seconds'
 AS [Total Time],
CAST((bu.backup_size/1024/1024) AS int) AS [Size in MB],
bu.user_name as Username
FROM msdb.dbo.backupset as bu
INNER JOIN msdb.dbo.backupmediafamily as bf ON bu.media_set_id = bf.media_set_id
WHERE
type!='L'
--AND database_name = 'AdventureWorks'
AND DATEDIFF(d, backup_start_date, GETDATE()) <= 7
ORDER BY database_name ASC, backup_start_date DESC

The folks over on the SQL Server support team have done it again. They saw a need for a tool to aid in troubleshooting, they wrote it, and have released it on Codeplex. This latest offering helps you troubleshoot issues with SQL Server backups and restores using 3rd party utilities.

I have to say I am pretty excited about this one. At work we are very compartmentalized and I have zero access to the backup software/servers. Whenever SQL backups fail, it can sometimes lead to finger pointing and confusion in deciding if it is SQL server or the backup application at fault. Luckily our 3rd party backup app does write a pretty verbose log to the local file system so I can sometimes look through that and get a good idea of where the issue occurred and why. However sometimes I look at the log and it is just complete gibberish and I have no choice but to throw up the white flag.

Enter SQL Server Backup Simulator. With this new tool I will be able to simulate taking backups and performing restores using sqlvdi.dll just like those 3rd party applications do. This tool was just released so I can’t say just how helpful it will be but I am hopeful that this will be another great tool to add to my toolbox. It will be great if I can use this to eliminate any issues on the SQL Server side so less time is wasted going back and forth. If the issue turns out to be SQL Server it looks like the tool will provide some good information about the sqlvdi.dll versions and errors.

The SQL Server Backup Simulator can be downloaded from Codeplex. If you have used to tool please let me know. I would love to hear how it worked for you.

Don't let the elephants trample you...

If you have data centers on opposite sides of the country or on the other side of the world chances are you have experienced the effects of an elephant. I know your probably thinking “A what?”. The elephant is a Long Fat Network or LFN (pronounced elephant) for short. The phenomenon is caused by the way TCP works and it can really slow down your work.

TCP is a good protocol and has some very useful features which help reduce network congestion and automatically detect dropped packets. This is where the problem comes in. In order to do those things, TCP relies on acknowledgements. Normally in a lan this isn’t a big deal because the latency is low. However in a wan we start to see an increase in latency so it takes much longer for these acknowledgements to get back to the sender.

A lot of people misunderstand the difference between bandwidth and throughput. You may have a large pipe of 1Gbps or even 10Gbps connecting those faraway data centers but you can still experience slow transfers because of the high latency. In essence your actual throughput is nowhere near the actual bandwidth. As an example if you are trying to copy files between a data center in Dallas, TX and London, England you will experience about 125ms of delay. Because of this delay it doesn’t matter if you have a 10Mbps pipe or 1Gbps pipe. You are still limited to throughput of about 4.19Mbps for each flow. This means there is a lot of extra bandwidth setting there unused and your transfers are going to take much longer than they need to.

So now that you know what an elephant is your probably wondering why this matters to you. At work we have data centers around the world and I am always having to design and configure things like log shipping, mirroring, and replication across data centers. Even for small databases it can take a long time to copy the backup to initialize the secondary servers in these configs. Luckily there are a few things we can do to drastically reduce the time it takes to copy the backups over.

The first thing that probably came to mind is compression. If you are using SQL 2008 Enterprise, SQL 2008R2 Standard or SQL 2008 R2 Enterprise you can use the built in compression for your backups. Of course there are also third party backup tools or you can even compress the regular backup files using something like winzip or winrar.

The other method I like to use in combination with the compression is to split the backups into multiple files. In TSQL you can create the backup to multiple files like so.

BACKUP DATABASE [AdventureWorks]
TO DISK='c:\backups\AdvetureWorks-part1.bak',
TO DISK='c:\backups\AdventureWorks-part2.bak';

So now that I have multiple files I can copy both of them at the same time and double my effective throughput. Instead of transferring at 4.19Mbps I mentioned before I can now get 8.38Mbps throughput and the file effectively copies in half the time. If I have the available bandwidth I might even split the file into 4 pieces for even faster throughput. From my experience copying between two and four files at a time gives me the best throughput. Anything over four files did not seem to improve things much.

Network Engineers can be your friends!

Make sure you test to find out the optimal number of files for your situation. Also it is a pretty good idea to get to know your network engineers to find out how much bandwidth you have. You want to make sure that you are playing nice with everyone else and not saturating the network. If you are interested in calculating the throughput for yourself, check out this handy calculator from Silver Peak. By working with your network engineers and leveraging a little info about how TCP works you can save yourself a lot of time when transferring files across great distances.

If anyone out there has their own tips I would love to hear them!