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.