June 12, 2009 at 11:27 am
I have a large database (18TB) which will keep growing around 700GB to 1TB every month. I was wondering if there are any sugestions for the administration.
1. We create a new filegroup every month and storage the new tables there
2. It is in full recovery mode, we make log backups every 20 minutes, but immediately delete them because they are too big. We don't need point in time restore.
3. Our backup method is ugly: we create a new database, copy the tables of the last filegroup, detach it and backup to tape. We script the indexes and keys.
4. Currently we have 15 SAN drives in the server for the datafiles (Is there a limit of drives that can be presented to a server?)
5. Specifics: Windows 2003 ENT, SQL Server 2000 ENT, 8 Processors, 32GB RAM.
Is there any size limit for a sql 2000 database?, how would you administer it?
Thanks!
June 12, 2009 at 4:22 pm
Antero Ruiz (6/12/2009)
I have a large database (18TB) which will keep growing around 700GB to 1TB every month. I was wondering if there are any sugestions for the administration.1. We create a new filegroup every month and storage the new tables there
2. It is in full recovery mode, we make log backups every 20 minutes, but immediately delete them because they are too big. We don't need point in time restore.
3. Our backup method is ugly: we create a new database, copy the tables of the last filegroup, detach it and backup to tape. We script the indexes and keys.
4. Currently we have 15 SAN drives in the server for the datafiles (Is there a limit of drives that can be presented to a server?)
5. Specifics: Windows 2003 ENT, SQL Server 2000 ENT, 8 Processors, 32GB RAM.
Is there any size limit for a sql 2000 database?, how would you administer it?
Thanks!
Several questions here... hmmm.... ok, let's start saying that for SQL2000, max database size is 1,048,516 TB (check here for additional info: http://msdn.microsoft.com/en-us/library/aa933149.aspx)
Now, why are you still using SQL2000 buddy? you are losing a lot of good backup and data size features that we have now on SQL2005 and SQL2008: backup compression, partitioning, etc, etc. I would suggest move ASAP... not just because management issues but because support and security also.
You should start analyzing why your database is growing so much. What's the nature of your database? It seems (in my humble opinion) that some way, you are keeping old data there that probably you can move to Tape and delete then. Try to identify those tables or rows that you can archive and delete; you'll save space and will help speed up the backup process at same time.
Regarding your Backup strategy here is what I suggest
1. Run a weekly Full Backup (just 1 only, could be Sundays if you want)
2. Run daily Diff. They will reduce the amount of Tlogs you need to use in case of a restore
Now, depending of how critical your database is
3a. Run Tlogs every 2 or 4 hrs.
or
3b. Change recovery Model to Simple and do not run Tlog backups.
Finally, I do not remember very well, but I think you can run Filegroup backups on SQL2000. If you have a Full database backup already, you can alternate subsequent Filegroup backups and rotate. For instance .... if you have 3 Filegroups, you can run a Full backup for FG1 Monday, for FG2, one Wednesday ... and for FG3 a full one Friday or Satudary. It will take less time to backup
June 13, 2009 at 4:53 am
If cost is not a contraint try using backup Software Like litespeed or legato which proves fruitful
June 13, 2009 at 5:02 am
Antero Ruiz (6/12/2009)
2. It is in full recovery mode, we make log backups every 20 minutes, but immediately delete them because they are too big. We don't need point in time restore.
You're taking log backup just to reduce the size of the log. Since you're deleting them, why not keep the database in simple recovery mode?
3. Our backup method is ugly: we create a new database, copy the tables of the last filegroup, detach it and backup to tape. We script the indexes and keys.
didn't understand what do u mean by last filegroup? Does this mean oldest filegroup which your no longer need? or the latest filegroup? You can definately take filegroup backup. No need to move its content to a new db and then back it up.
http://msdn.microsoft.com/en-us/library/aa176754(SQL.80).aspx tells you how to backup a filegroup.
June 15, 2009 at 1:28 pm
Thanks.
We can't run a full weekly backup because we don't have enough storage for the gigantic backup file, besides, when it was just 10TB we tried to run it and it never finished; we tried few products but we soon gave up due to the storage issue.
The db contains numeric/statistics information and while I agree that we could delete old data, the users claim that they use all of it.
The filegroup backup doesn't work for us because we delete the log backups, and in order to restore filegroups, sql requires log backups as well.
June 15, 2009 at 1:37 pm
Antero Ruiz (6/15/2009)
We can't run a full weekly backup because we don't have enough storage for the gigantic backup file, besides, when it was just 10TB we tried to run it and it never finished; we tried few products but we soon gave up due to the storage issue.The db contains numeric/statistics information and while I agree that we could delete old data, the users claim that they use all of it.
The filegroup backup doesn't work for us because we delete the log backups, and in order to restore filegroups, sql requires log backups as well.
Basically this means there is no backup strategy at all and no backup happening? Are users' willing to risk their data? Why not implement log backups every 15-30 mins? You can take one filegroup backup every week during off-peak hours. With such a huge database, there cant be any shortcuts.
I'd like to see senior guys here commenting on your requirement:-)
June 15, 2009 at 2:16 pm
Instead of a new filegroup every month, I'd seriously look into separating the data into and Old database and a Current database. Flush the data into the old database daily/monthly/weekly. That'll simplify the whole thing tremendously.
I'd definitely look into upgrading to SQL 2008. Data compression and inline backup compression will definitely pay for themselves in this setup. Synonyms will make an Old+Current solution simpler, too.
Change it to simple recovery. If you aren't keeping the log backups, there's no point in making them. All you're doing it wasting CPU and IO, unless there's something I'm seriously missing on this.
If you move data daily or weekly from Current to Old, then you just need to do a Diff backup of each at that time.
You should also set up monitoring of data use in the Old database, which would allow you to know when/if you can retire data. If you can't retire data, database compression (SQL 2008), or read-only and file compression (SQL 2000/2005), will be useful.
If you do upgrade to 2008, definitely look into partioning as well.
How often do you load data into it? Is it an ETL process that loads in batches, or is it some immensely transactional thing with tons of inserts per second that only load one or two rows at a time?
If transactional, I'd bet the data needs better normalization to reduce size. Can't be sure about that, but it's a pretty good bet.
Does any of that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 15, 2009 at 2:17 pm
ps (6/15/2009)
Antero Ruiz (6/15/2009)
We can't run a full weekly backup because we don't have enough storage for the gigantic backup file, besides, when it was just 10TB we tried to run it and it never finished; we tried few products but we soon gave up due to the storage issue.The db contains numeric/statistics information and while I agree that we could delete old data, the users claim that they use all of it.
The filegroup backup doesn't work for us because we delete the log backups, and in order to restore filegroups, sql requires log backups as well.
Basically this means there is no backup strategy at all and no backup happening?
WTF? You are playing with fire, to be sure. Storeage issue?? How about a recoverability issue? No backups = no job security = no business = no paycheck. Get the picture? Do you have sign off from your superiors for this (lack of) strategy?
-- You can't be late until you show up.
June 15, 2009 at 2:38 pm
Antero Ruiz (6/15/2009)
We can't run a full weekly backup because we don't have enough storage for the gigantic backup file, besides, when it was just 10TB we tried to run it and it never finished; we tried few products but we soon gave up due to the storage issue.The filegroup backup doesn't work for us because we delete the log backups, and in order to restore filegroups, sql requires log backups as well.
So what is your recovery strategy if you get database corruption (I'm guessing that you're also not running CheckDB) or, if you have hard drive failures or if someone accidentally deletes vital data?
First things first, get more storage space. That's not a joke or a stupid suggestion. You need to have enough drive space to keep at least one, preferably two full backups, the diff backups that go with them and all the log backups. If you don't have space for that
Once you've got that, I would work out a combination of filegroup and differential filegroup backups that minimise backup time but still allow you to restore in a reasonable time if necessary. Personally I'd rather design for faster restores than faster backups. Time taken for a restore is time when the DB's down, the users are screaming and the company's losing money.
In addition to this, I would look at a 3rd party backup tool if you can't upgrade to 2008 (and upgrading does require testing). I've had good experience with Litespeed on a 1.2TB database, YMMV, there are several ones out there, give them a try and see how they handle the load and the size.
If you're on a SAN see if it supports any of the SAN snapshot techniques. They do require a lot of space but they're very, very fast.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2009 at 4:12 pm
Thanks for all your responses.
Yes, the users and managers are aware of this problem. Unfortunately, the company cannot afford more storage other than the 2TB they buy every 3 months for the new data, the problem is not only money but also the available space and temperature in our server room. That's why our backup 'strategy' is so cumbersome and no practical.
As one of you suggested, I think it would make sense if we keep the most recent data (last couple of months) and the core tables in a SQL2008 database and keep old data (older than 2 months) in our current sql 2000. Since Filegroups backup is not an option, we will need to move the data from sql2008 to sql2000 (2+ months old, every month) from one server to another. What would be the best way to do it?
Thanks.
June 15, 2009 at 4:28 pm
It sounds like you're in a bad situation. Honestly, you are playing with fire here. One issue, and it's possible that you will be without data, and then without a job if this is important. If it is, they can find the money. It sounds like they're just gambling nothing will happen. If you're buying 8TB a year, you are going to have serious issues before too long.
As for moving the data from SS2K8 to SS2K, I'd use SSIS. It works well, and if you have some datetime or other way of querying the data, a package could be built to easily move xxx data every month, week, whatever.
June 16, 2009 at 5:50 am
Antero Ruiz (6/15/2009)
Thanks for all your responses.Yes, the users and managers are aware of this problem. Unfortunately, the company cannot afford more storage other than the 2TB they buy every 3 months for the new data, the problem is not only money but also the available space and temperature in our server room. That's why our backup 'strategy' is so cumbersome and no practical.
As one of you suggested, I think it would make sense if we keep the most recent data (last couple of months) and the core tables in a SQL2008 database and keep old data (older than 2 months) in our current sql 2000. Since Filegroups backup is not an option, we will need to move the data from sql2008 to sql2000 (2+ months old, every month) from one server to another. What would be the best way to do it?
Thanks.
Antero?
Users will always say nothing can be deleted from the database, everything is important, lol. Talk to their managers or manager right away and explain your situation. You value your job? imagine that database crash tonight .... what do you think those users are going to say or who are gonna blame to? I work for one of the most biggest IT companies in the worlds, we manage lot...lot of data ... of course we have money for hardware but archiving is vital! Keeping old tables on your database not just increase the size, affects performance when running queries.
For moving table or migrate data between boxes, I think you can use SSIS or DTS. You can move all the old tables or rows to a 2nd box, and then delete all that data from the production box; once you free up some space, you can setup a Full Backup locally and one or two differential weekly, plus Tlog backups daily.
Convince your supervisor of spend more money on Tape Backup solution ... you will cover your a* ...in case of a fire or a big crash; tape backups is always the way to go instead of local backups.
Finally, if they refuse to give you the money that you need, try to put everything on paper or email ... remember, the rope always break on the weakest point 😉 ... if you keep records of your problem, you will keep your job as well when the db goes down and you have no backup.
June 16, 2009 at 1:57 pm
It is possible that data does not decay to uselessness in this situation. Even "old data" is sometimes critical to current data and operations.
But it's rare that you can't at least move it off to a read-only format and compress the heck out of it. More often than not, you can even summarize it into a pre-aggregated format, and dump the raw data. Not saying that applies here, but if it might, it can cut data size by a huge amount if done correctly, while still retaining its usefulness in reports and such.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply