November 17, 2004 at 1:26 pm
Hi everyone,
a customer of me uses a standard database design and I would like to hear some opinions about it.
The server are configured as follow:
C:\system partition, D:\ Applications (incl. SQL Server), E:\ Primary datafiles, F:\ Secondary Datafiles, G:\ Indexes and H:\ Transaction Logs.
The Primary datafiles should only contain the system objects for each database and is just 1 Gb. All usertables should be placed on F: and the indexes on G:
All partitions are on a HP SAN. The databases so far are not very big (max. 10Gb).
My personal view is that there's no perfomance advantage in the current situation. For maintenance there might be a small by placing usertables and indexes on separate files, but on the other side they have lots of problems with databases belonging to 3rd party applications which create database objects by default on the primary filegroup. Also I have to migrate several databases from backups and in order to design them as requested I need to restore those first to a staging server and then import the data to the production server.
What do you guys think. Am I missing something ? Should I convince the customer that he make like unnecessarily complicated ? Any comments are welcome
M
[font="Verdana"]Markus Bohse[/font]
November 18, 2004 at 1:32 am
From my understanding so far, in most configurations I don't see any advantage in having multiple filegroups. I'd rather have one filegroup spread over as many physical drives as possible. Multiple filegroups make, among other, sense in VLDB environments with respect to backups. But there is always some administrative overhead. I guess, you already know some of these links, however they might be of some help
http://www.microsoft.com/technet/prodtechnol/sql/2000/plan/ssmsam.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_0m5g.asp
Interestingly, I found that the IBM redbook domain website has many useful informations about this topic.
http://publib-b.boulder.ibm.com/redbooks.nsf/portals/Data
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply