Large system coming, looking for advice of how to maintain, over 2TB.
-
We have a new system coming that will hold our restaurant polling detail and we will hold 18 months in the live database. This will grow very quickly and will be well over 2TB. This is a vendor app so I don't have much say in the table structure/layout.
I maintain a LOT of dbs here, and some highly used ones, however I have never had to deal with a 2TB or larger type install. So my questions become:
How do folks do Update Stats, do you build live scripts and run them for non large tables weekly and then have a script that gens them for the very large ones and run them, say monthly?
How about Integrity Checks, for 2TB DBs I cannot imagine that you can run it daily... I was thinking of having the large db use the with Physical Only option due to the sheer size. However, that opens up the likely hood of internal corruption that I may not find.
Backups, do all shops that have this size of db use a backup software companies backup due to the size and length of time it takes to run the normal Microsoft ones? This will be SQL2008R2 so I can use the WITH COMPRESSION option.
Any other things that I am not thinking about to plan for?
Yes, we are going to lay out the drives specifically and have TEMP DB, MDF, LDF on different spindles.
-
The answers to your questions really depend on how long it will take to run the maintenance processes, and that will depend on the speed of the server.
I doubt that third party backup compression tools will perform any better that the SQL Server native backup compression.
If you have the time and the server is fast enough, run DBCC CHECKDB every day. The sooner you find a problem, the less effort will be needed to fix it. The speed of your disk storage is usually the factor that determines how fast DBCC CHECKDB will run.
The sp_updatestats stored procedure is smart enough to only rebuild stats when needed, so you should be able to run it fairly often.
You will need to plan for index rebuild or reorgs, and a lot will depend on your uptime requirements. If you have to be up 24/7, it is usually easier to do reorgs, but you still have to see how long they take and how much impact they have on application performance.
Make sure your server has enough disk space to restore another copy of the database for situations where you might need to recover data.
-
Michael Valentine Jones (1/14/2013)
The sp_updatestats stored procedure is smart enough to only rebuild stats when needed, so you should be able to run it fairly often.I have been badly burnt by running this on a VLDB. The sample rate was way too low and I had no control over what was being updated. Instead I have a custom index rebuild job running on Friday night and a custom statistics update (with fullscan) running on Saturday night. I have also turned off auto-update stats.
You can run a daily DBCC CHECKDB on a restored copy of the database.
For backups we use native backup with compression.
-
Interesting thoughts, thanks.
Has anyone done any comparision to the SQL2008R2 backups with compression as far as time taken to run and size of backup and compared it to, say LiteSpeed? I would be curious to know how they compare for a VLDB. I dont have anything yet over 1TB or I'd try and compare.
-
Markus (1/15/2013)
Interesting thoughts, thanks.Has anyone done any comparision to the SQL2008R2 backups with compression as far as time taken to run and size of backup and compared it to, say LiteSpeed? I would be curious to know how they compare for a VLDB. I dont have anything yet over 1TB or I'd try and compare.
I found SQL Backups with native compression to be faster and slightly smaller than LiteSpeed backups.
I also found restoring LiteSpeed transaction logs backups to be much slower. I had a situation where I needed to restore 14 days of tran log backups (made every 15 minutes, 24x7) and it took a very long time, maybe a day. I think there is a lot of overhead to activating the extended stored procedure that LiteSpeed uses for the tran log restore, and that was most of the runtime.
That was about 2 years ago, so I can't say if LiteSpeed has improved that by now in a later version.
Can't speak for any other third party products.
-
Michael Valentine Jones (1/15/2013)
Markus (1/15/2013)
Interesting thoughts, thanks.Has anyone done any comparision to the SQL2008R2 backups with compression as far as time taken to run and size of backup and compared it to, say LiteSpeed? I would be curious to know how they compare for a VLDB. I dont have anything yet over 1TB or I'd try and compare.
I found SQL Backups with native compression to be faster and slightly smaller than LiteSpeed backups.
I also found restoring LiteSpeed transaction logs backups to be much slower. I had a situation where I needed to restore 14 days of tran log backups (made every 15 minutes, 24x7) and it took a very long time, maybe a day. I think there is a lot of overhead to activating the extended stored procedure that LiteSpeed uses for the tran log restore, and that was most of the runtime.
That was about 2 years ago, so I can't say if LiteSpeed has improved that by now in a later version.
Can't speak for any other third party products.
Very interesting. We have an outside vendor that hosts an application for us but gives us the logs to apply to a copy of the db here so we can run reports on that data. They were using SQL Lite when it was SQL2005 and it did seem to run somewhat long... one log per 15 minutes is about 90ish logs a day. They varied in size greatly though. When they/we upgraded to SQL2008R2 last month I asked if they could switch to Native SQL Backups using the WITH COMPRESSION instead of SQL Lite they agreed. From what little I have seen I believe it runs faster and the size of the files aren't really that much different I don't think. To me it takes one part of the complexity out. I just didn't know that if you get well over 1TB if the SQL Lite started to have a good return vs 2008R2 is all.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply