July 27, 2015 at 1:27 am
I have been creating databases in SQL 2008 with a primary filegroup for the system objects and a secondary, marked Default, for the data.
We are preparing a migration to SQL 2014, and the administrator is complaining he won't adopt this structure on the new servers because 'there is no benefit' and 'a backup cannot be restored (!?)'.
I've spent the last two hours googling, and found plenty of references stating this arrangement is "Best Practice", but very little to back up this statement.
Can anyone point me in the direction of convincing documentation giving reasons why this is (or is not) the case?
Secondly, would anyone care to argue the benefits and drawbacks of this structure.
Am I simply making work for myself and complicating the lives of the other team members?
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
July 27, 2015 at 2:51 am
The advantage is that, if you're in full recovery model and know how to do piecemeal restores, you can restore primary very, very fast (it's only the system tables) and then restore the critical application tables (if they're in their own filegroup) and then restore the less important table (if they're in their own filegroup), etc, etc
If it's just system in one filegroup and everything else in another, then it's not all that useful except as a starting point for further splitting.
No disadvantages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2015 at 1:53 pm
Gail,
Many (somewhat belated) thanks for your response.
Unfortunately I won't convince anyone where I work on recoverability grounds.
We are in the extremely luxurious position of being able to take an extended outage of up to 2 weeks. The content of almost all our databases is rebuilt from a base set of data; the results are published to an external provider where they can be called off via a web application. If that doesn't get updated for a couple of weeks, most users won't care.
Thanks again
Otto Schreibke
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply