patternMinor
Is it possible in SQL Server 2008 to have Structures in one file and Rows Data in another file?
Viewed 0 times
rowsfile2008sqlandpossibleoneanotherserverdata
Problem
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?
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?
Solution
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
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.
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
- Restore the PRIMARY filegroup (instantly, as its tiny).
- Retore the filegroup containing the smaller supporting tables.
- Restore the filegroup allocated to the current quarter order data (again relatively quickly compared to restoring all history).
- Set the remaining partitions as offline.
- Bring the database online. You can now accept new orders and process existing orders.
- 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.
Context
StackExchange Database Administrators Q#5493, answer score: 5
Revisions (0)
No revisions yet.