asked
Handeling Databases larger than the 2Tb LUN Allotment.
In previous versions of SQL. The best way to handle databases larger than 2Tb was to add multiple MDF files. Sometimes splitting off to seperate Luns. Is this still the best way of doing so or since 2008 was released. Have there been better methods introduced that manage large databases better? Currently as a test, I have taken a 200Gb database and split it into 4 other data files. Capping each one at 60Gb. Providing me with 240Gb of storage and the ability to add more MDF files as I grow. Granted this is only a test and I plan to do the same with a database I have now that is nearing it's 2Tb limit of the lun. If there are better ways than adding MDF's or seperating luns for large databases. Please let me know. Thank you.
Breaking the database up into file groups (not just files) is still a great way to manage a large database. Not just to fit within a predefined LUN size, but also for managing backup and restores, a variety of maintenance and partitioning activities and IO subsystem quality of service management if your shop goes for the cheaper subsystems for less frequently accessed or less critical data. Probably the entire 2TB of data isn't changing daily, so you can back up the active portion of the database frequently and back up the read only or slowly changing portions less frequently. You can fit these partial backups into a smaller maintenance window, and you aren't backing the entire thing up to disk every time. You also get better restore options with multiple file groups. With a single file group, then entire database is unavailable for the entire duration of a restore. With multiple file groups, only the file group begin restored will be offline. So, for instance, if you lose a portion of the database that contains infrequently used data, you can restore that portion of the database while the rest of the database remains online and servicing user connections.
