Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know this may sound ridiculous but:
Is it possible to separate the Data Definition (i.e. Structures of my Tables, Views and other things) and Rows-Data on a File-Level in SQL Server.
I think it will be useful to have two or more filegroups on 2 or more physically separated drives and have files in them to solely store the definitions or row-data; so if your row-data HDD crashes, you wont lose the data structure...
Can this be done through Partitioning?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

The table schemas will always be kept in the PRIMARY file group. You could put the tables into another file group, but if you loose either set of disks the database will go suspect and you have to go into emergency mode and restore anyway. The pages which are allocated as belonging to the table will be gone so there wouldn't be any way to read the table. You could read the page that the schema is stored in but that's around it.

No matter what you'll have to restore to get the database into a usable state again.

share|improve this answer
    
Thank you mrdenny :) –  Achilles Sep 11 '11 at 19:25

As @Denny pointed out, schemas are always in the PRIMARY filegroup. There are potential availability benefits to having just schema in PRIMARY and data elsewhere, if you can make use of Partial Availability & Piecemeal Restore.

The usefullness of this depends on the nature of your data and if you have tables that are suitable for partitioning. For example, if your biggest tables contained Orders and OrderLines which are partitioned by OrderDate across quarterly filegroups and your remaining tables are relatively small by comparison, in the event of failure you would

  1. Restore the PRIMARY filegroup (instantly, as its tiny).
  2. Retore the filegroup containing the smaller supporting tables.
  3. Restore the filegroup allocated to the current quarter order data (again relatively quickly compared to restoring all history).
  4. Set the remaining partitions as offline.
  5. Bring the database online. You can now accept new orders and process existing orders.
  6. Gradually restore the remaining partitions and bring online in turn.

In a similar vain, if you were to suffer corruption to the database metadata (a rare occurance compared to corruption in data structures but not impossible), you would only need to restore the very small primary filegroup to get up and running.

The availability benefits this can bring to a large database are so significant, I try design partial availability in from the outset on new projects. A 500GB database restoring at 50MB/s will result in at least 3 hours of explaining why it's taking so long to the non-techs. With forethought and planning, it could be a fraction of that to get the business up and running again.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.