| Author |
Topic |
|
mr_mist
Grunnio
1870 Posts |
 Posted - 2004-05-27 : 06:19:00
|
| Hi peopleJust wondering what sort of solutions you use for backing up of multi-gigabyte databases. There is an available window during the night, but what actual process do you use? The infrastructure guys here have been trying a 3rd party piece of software, but, whenever the test database reaches a certain size it seems to have a problem with freezing the IO so that it can take its snapshot.Does anyone have any experience of this or ideas to share?If I needed to freeze sql server programmatically is this possible? It's 2000 in a failover cluster so that may add complication.-------Moo. :) |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-05-27 : 06:58:41
|
| Backup to disk and then back the file on the disk up to tape... |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-05-27 : 07:14:01
|
quote:
Backup to disk and then back the file on the disk up to tape...
Granted, but specifically how is what I am interested in. Also, if people have noticed any issues with online/offline backups, what software you use, etc.-------Moo. :) |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-05-27 : 07:46:00
|
| Using BACKUP DATABASE, also depending on how your data is used depends on your strategy... If your database is transactional then you will want to look into differential backups, if it's a reporting database you will probably just want a full backup per day...I would never use a backup strategy that goes straight to tape as I prefer to have some redundancy in my backups and the fact that i've seen companies who have backed up straight to tape, then realised they didn't have any backups when they really needed one...Your backups are your lifeline, that should be your mantra... |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-05-27 : 09:09:40
|
| We're after a hot/online backup system that would (hopefully) perform better than sql backup. Do you pause your SQL Server whilst you take your backups?-------Moo. :) |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-05-27 : 09:20:53
|
| No, as there is absolutely no need... Users can carry on with whatever they're doing while the back-up is happening...Good luck with trying to find anything faster than Sql backups... |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2004-05-27 : 11:39:32
|
| File or filegroup is also an option for huge dbs. But lot of manual intervention and planning is required. We backup the db to a local folder and use arcserv (third party tool) to take the tape backup.------------------------I think, therefore I am |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-05-27 : 11:56:22
|
| A couple of solutions come to mind.If you want to take standard SQL backups, use a tool like SQL litespeed. It is actually much faster than the built in SQL backups, plus the backup file is compressed on the fly.If you are in a SAN environment, you can use the split mirror backup technology. Here is a good overview http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/spltmirr.mspxIt also may be possible to use Veritas Storage Foundation for Windows to achieve similar results if your SAN is not mentioned in the link above. VSFW uses the Volume Shadow Copy (VSS) capabilities of Win2k3 to perform snaps of your data. However, I have not yet seen any articles or howtos discussing using VSS with SQL 2K. Maybe support for VSS will be built into SQL 2005 (yukon).-ec |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-05-27 : 12:30:39
|
| Thanks, that article is the sort of thing that should help.-------Moo. :) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-27 : 18:28:31
|
| Just be aware of the write consistency issues you will face when going away from SQL Server (or litespeed) backups. Most third-party backup technology generally sucks when it comes to SQL Server. There are also severe limitations on many of the features they claim to have. When you get ready to make a decision, you might want to run it by this forum and a couple others to get opinions.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-05-27 : 18:50:29
|
| Don't forget to test whatever solution you decide to use. -ec |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-05-28 : 03:04:41
|
quote:
Don't forget to test whatever solution you decide to use.
That will happen. The reason this question is here at all is because the current solution is failing to back up large databases. It seems that the software has problems freezing the I/O.-------Moo. :) |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-05-28 : 03:20:10
|
| What do you mean freeze the I/O? SQL server does not have that capability. That is why the split mirror backups are not 'officially' supported by Microsoft. Split-mirror backups depend on the ability to either quiesce the database momentarily or by using a hot backup mode (ala oracle).SQL server doesn't have either of those capabilities - yet.Anyway, why don't you describe the problem you are having and maybe there is another solution for you. Maybe you could start by giving some information about your database, the hardware you run on and the methods you have used for backup. Any specific error messages you are getting would also be useful.-ec |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-05-28 : 04:22:08
|
| HiYes, I am only in possession of half the facts unfortunately. The infrastructure people have a 3rd party piece of software that takes snapshots of the current database situation, whilst it is doing that it somehow pauses the IO. The log entries are like Database blah: IO is frozen for snapshotDatabase backed up: Database: blah, creation date(time): 2004/05/20(08:54:33), pages dumped: 1, first LSN: 2069:293:1, last LSN: 2069:295:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'Data Protector_(DEFAULT)_blah_04_00_22'}).Database blah: IO is thawedExcept that once the databases hit a certain number of GB in size, the process is not working. My understanding is that the vendor of the software has now stopped trying to support this issue, so I am left looking for other solutions.-------Moo. :) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-28 : 07:32:04
|
| Are you talking about EMC Replication Manager and SnapView by any chance?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-06-03 : 04:29:10
|
| Nope, but that probably works the same way.-------Moo. :) |
|
|
|
contiguous1
Starting Member
1 Post |
Posted - 2004-06-25 : 00:46:19
|
| The 'IO is thawed' could be coming from Computers Associates Brightstor 10.x backup and recovery software. It has just been put on a multi instance server here and am seeing similar messages in some of the logs. Possible that you have an incorrect setup, as there are a couple of optiosn available for backup and restore. Will let you know what i found out here.Brian Sutherland in AU |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-25 : 13:39:28
|
quote:
Originally posted by RickD
Good luck with trying to find anything faster than Sql backups...
SQL Litespeed is soooooo much faster than SQL backups. We only use it on databases that are rather large though. For dbs over 100GB, the full backup was taking about 2 hours with SQL backup, with SQL Litespeed it cut that time in half plus the file size down by 75%. Now restores are a different story. Restores are slower with SQL Litespeed, but it's worth it for the large databases to save backup time and disk space.Tara |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2004-06-26 : 00:33:02
|
| I'm with Tara,I backup 250GB with SQL Litespeed and no problem.A good understanding of the growth patterns of the database and a solid filegroup backup strategy is a good starting point.The number of processors matters for SQL Lite and Restore/Backup can be quicker if you create a raid 0 array of 36GB eSATA WD which come with 5yr warranty with 10 drives it will give between 200 -300 MB/s this will get you back up and running within 15 minutes with a decent controller throughput and for less than 10K it's worth the investment for a company that has a 250GB database.Once the file is recovered you can move it a little at a time to the more fault tolerant arrays, another reason to create reasonably sized file groups so that you can move them a few at a time and restore overtime and then later when they are all moved figure out the best time to detach and attach to the more reliable array.SATA array also create for tempdb work especially if you add a couple hotspares. If the drives crash and you set up the sql instance to shutdown and restart it will recover to the new drives and be operational with less throughput until you can fix the rest of the array. You must understand the risks and only use it in certain scenarios ex: datawarehousing aggregation if the datawarehouse is not available to users during a window of time.If you need more availability than 15 minutes consider clustering, or log shipping. |
|
|
|
iapetus
Starting Member
15 Posts |
Posted - 2004-06-29 : 01:01:55
|
| All of those third party vendors use the VDI objects provided by Microsoft. You could code your own. However, none of those keeps any information in the MSDB backup history tables.Moreover, even on a single logical volume, on a multi-processor server, BACKUP DATABASE and BACKUP LOG can both use parallelism to multiple backup files and improve throughput.I took a slug of 2 proc box that would take nearly 6 hours to backup a 30 GB database and reduced it to less than 2 hours using merely 4 concurrent backup files. For backup space, send to a compressed OS directory. That will work for the restores as well.As far as Hot vs. Cold backups...hogwash! SQL Server uses virtual circular log files within the active log. You can back up committed LSNs without slowing down or interfering with existing transactions. If you are running in Simple recovery mode, then a Full backup live will not miss any more than what you will miss between backups anyway. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-29 : 12:02:31
|
| Yeah but what about databases that are over 100GB? That's where we use SQL LiteSpeed. I certainly wouldn't bother with the cost of the license for only 30GB.Tara |
|
|
|
Next Page
|
Large database backups