April 2, 2012 at 9:04 am
Good Day ,
we are running a large database on SQL Server 2005 ( 800 gig) . Is there any way we can store the large database on less space in a Live environment ? E.g archive drives or is the only solution to store the database in its current format and spend the money on additional space ? Also this backup takes 37 hours to make a full backup to a shared drive . We used multiple file backups, but that caused the application to fall over . Any ideas ?
April 2, 2012 at 10:08 am
I'm not really sure what you are asking. 800gb is 800gb, so how can you store 800gb on less space? Compression may help, but you still need the space that is taken up. Is any of your data read-only (not necessarily set as such, but from a business case)? If so, there are a number of things you can do depending on the nature of your data.
-Partitioning
-BCP out old data and archive then purge
-Move out of current database into a data warehouse optimized for storage
We really need to know more about your data and how you could archive it or purge it.
Jared
CE - Microsoft
April 2, 2012 at 10:15 am
There are probably many (MANY) answers to this one...my thoughts are based upon a lot of assumptions and may or may not be applicable to your scenario...
Are you using backup compression (i.e. litespeed or hyperbac), if not you should look at doing so immediately as it will speed up your backup/restore times. Judging by your 37 hour backup time frame I assume this answer is "no". Download the trial software and test it out. We backup a just over a TB
here in under 6 hours using RedGate's Hyperbac.
Considering partitioning your data. If you company only uses a rolling 12 months of data, then partition your data by year/quarters/months, and then mark the older data partitions as read-only, back them up once to a safe network location/SAN/Tape/Etc and then stop backing up those read-only partitions -This should save considerable time depending on your system. No sense in backing up data that no longer can change.
Consider using multiple file groups, especially for your NCI's. Exclude all of your non-clustered index files. Depending on your SLA/DR guidelines, it might be faster for you to rebuild all the indexes manually after you've restored the DB's (this would mean you'd have to script them out to somewhere safe in advance). At our company 40% of the overall DB size is just indexes (fun, fun, fun)...and while I haven't fully implemented this one yet, It is a project I have on my radar...
Sounds like there is some serious network latency to this "shared" backup location. Do you have space available to back the DB's up locally and then copy it over to the shared resource after-the-fact? If so, try that to see how long the backup typically takes (this may be better/worse depending on the SQL server hardware)
Well, that's all I've got :w00t: hopefully it'll be enough to spark some input from the incredibly great people who contribute to this site!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 2, 2012 at 10:18 am
Thank you for the feedback. Unfortunately we cannot back the database up locally due to space constraints on the server .
April 2, 2012 at 10:27 am
Good day.
The database is 800 gig in size and we cannot move the data to a warehouse . We cannot purge the older data either .
April 2, 2012 at 10:29 am
Lian (4/2/2012)
Good day.The database is 800 gig in size and we cannot move the data to a warehouse . We cannot purge the older data either .
Can you make any of the old data read-only and partition it? If not, buy more disks.
Jared
CE - Microsoft
April 2, 2012 at 10:33 am
What edition of SQL Server 2008 are you running?
April 2, 2012 at 10:34 am
For backups take the advice earlier and try LiteSpeed and Hyperbac. Remember to try restores too.... I backup about 500 GB to a remote server in < 25 minutes using LiteSpeed. Took a few tries to find the best combination of compression ratio and speed.
As far as the data. It really depends on how your data is structured. I.e. if most of the data is in one table and you have an easy way to partition it, I would try that if you can't archive the data.
Or you simply might just need better hardware 😉 800 GB really isn't all that large anymore. (it's not tiny either). If it is mostly a single table, that would be a good sized table.
April 2, 2012 at 10:38 am
Hi,
we are running SQL Server 2005 Enterprise Edition 64 bit
April 2, 2012 at 10:41 am
Okay, nevermind what I was thinking. You posted this in a SQL Server 2008 forum so I was going to make a SQL Server 2008 suggestion.
Edit: And I didn't catch that you were runnig SQL Server 2005 in you initial post.
April 2, 2012 at 10:43 am
If there is no option to purge ur data and move ur data to a dataware house and still wanting ur data to be lively,
Frist thing is u might want to consider is backuping ur data with a 3rd part tool (Red-gate or lite speed). Its easy, fast and much compressed backups (do not forget to check ur backups taken by restoring on ur lower envs..just to make sure ur backups are healthy). If u are backing up ur database to a shared drive, def u would have a network latency depending on ur network.
Next is, u have to request for an addition space!!!
Good luck!!!
April 2, 2012 at 10:43 am
Lian (4/2/2012)
Hi,we are running SQL Server 2005 Enterprise Edition 64 bit
I'd like to hear your suggestion anyway Lynn!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 2, 2012 at 10:58 am
MyDoggieJessie (4/2/2012)
Lian (4/2/2012)
Hi,we are running SQL Server 2005 Enterprise Edition 64 bit
I'd like to hear your suggestion anyway Lynn!
In SQL Server 2008 and newer when running Enterprise Edition, I would also look at the possibility of implementing page or row data compression. I was looking at it at a previous employer and if we implemented page compression on 6 tables we could have reduced the overall space used by the database by almost 50%. I left before being able to present this as an option to help reduce disk utilization.
April 2, 2012 at 12:03 pm
Lynn Pettis (4/2/2012)
MyDoggieJessie (4/2/2012)
Lian (4/2/2012)
Hi,we are running SQL Server 2005 Enterprise Edition 64 bit
I'd like to hear your suggestion anyway Lynn!
In SQL Server 2008 and newer when running Enterprise Edition, I would also look at the possibility of implementing page or row data compression. I was looking at it at a previous employer and if we implemented page compression on 6 tables we could have reduced the overall space used by the database by almost 50%. I left before being able to present this as an option to help reduce disk utilization.
Although, compressing storage won't help backups.
The worst thing in this situation is that if you have a catastrophic outage, your restore process will take about 36 hours.
I'm with most of the others, you need to get backup compression working for you. I have a preference for the Red Gate tools (since I work for them). You also need to be sure that, if you're going to use multiple files for the backups, that these are on multiple disks and through more than one backup path (meaning multiple NIC cards or multiple disk controllers) otherwise you're just jamming more stuff down the same pipe. If you don't have multiple storage locations AND multiple paths, don't use multiple files. You might look at using differential backups in addition to the full. It'll ensure you're getting more stuff backed up in shorter periods of time, but your restore process is still going to be highly problematic.
I'm with several others, you need to break up your storage to ensure the most flexibility on your system.
Another option, not perfect, and not as good as a backup (absolutely not a replacement for good, tested backups) is to set up mirroring so that you've got your database in a second location.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 2, 2012 at 12:37 pm
Lynn, I am not overly familiar with this type of compression, can you recommend some good links/books?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply