I’ve been trying to get a handle on all of the SQL server’s in our enterprise environment. The first task was identification, which came with a host of challenges such as, no naming convention, developer machines, applications that install Sql Server that I didn’t care about, strange firewall software blocking remote management and multiple instance boxes.
Once I got a list of servers, the thing that I wanted to check most was, “Are backups happening?” I didn’t really care at this point if the backups were actually good because I wanted to at least make sure they were happening. Sure there was a couple servers, but we were doing really well percentage wise. You can check last backup date with the SSMS GUI. Just go to properties->general. Or a query to join the backup sets to sysdatabases.
SELECT dbs.name, baks.backup_finish_date FROM sys.sysdatabases as dbs left join msdb.dbo.backupset as baks ON baks.database_name = dbs.name
So backups were happening, then fast forward to our certain demise. The disk backup and dedupe appliance we were using didn’t support daytime activity. This was fine for most servers and databases which were in simple mode. Another problem was the appliance didn’t support very large databases. Next, it was out of space and time to run the daytime processes.
We started attempting to squeeze our VLDBs into a tape solution we already had. The switch over went very poorly and restores took days… yes over 24 hours. So we’re stuck with some backups over here mirrored offsite and other backups over there and not mirrored. The replication of tape storage dies every time we need to do a restore so it is quite frequently behind in replication.
Its a mix-n-smash of technologies that seem to be changing faster than I can document. So I have charged myself with coming up with a solution that will at least work for SQL. One of the ideas I have is to find a piece of disk storage that simply does replication offsite. Then just send the compressed backups to it and boom the files are offsite.
Offsite replication is a technology that is extremely expensive. I just need some cheap storage for backups but when the replication feature is added the storage price goes through the roof.
There are some relatively inexpensive rack mount NAS solutions. I don’t really want to get into the game of rolling my own linux/freeNAS solution. I don’t want this device to be dependent on any existing disk solutions. I would like hot-swappable drives but they do not have to rebuild fast. It also has to integrate with AD.
If I got two separate targets I could run the backups locally and then robocopy them offsite. OR, I could use the MIRROR command that is built into SQL 2005 and up. I tried testing this on my express instance and got the dreaded error: Backup mirroring is not available in this edition of SQL Server. So I checked it out on another instance and sure enough, two exact copies. Delete the first file and you can restore from the second file without any problems.
BACKUP DATABASE [dba_maint] TO DISK=N'C:dbdba_maint_principal.bak' MIRROR TO DISK=N'C:dbdba_maint_mirror.bak' GO
If one or the other file location is inaccessible the whole backup will fail. This could be problematic if your offsite server is… offline constantly. I have not tested this, but I also believe the whole backup may be slower if the connection offsite is slow.
Not only can you mirror the files but you can also stripe backup files or write to multiple files at the same time. The benefit would be to hit more disk spindles and to remove other bottlenecks in the backup.
BACKUP DATABASE [dba_maint] TO DISK=N'C:dbdba_maint_principal.bak', DISK = N'C:dbdba_maint_mirror.bak' GO
Putting both of these files to the C: drive wouldn’t give you any boost. So RAID 0 or RAID 1… what will it be? Before you go striping, you might be better of creating a big RAID0 array and storing it in one file. If you stripe you run the risk of not having all the files to restore. Adding moving parts to your backups will complicate things. Remember that you are not always around when databases need to be restored.
If I get a local NAS and an Offsite NAS I could push the files in a MIRROR fashion as long as Kerberos works. Long story short, there are options for me to get rid of the stacks of 8 tracks.