Re: <<filegroup>> files from different hard disks:: tables(s) sto
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 12/24/04
- Next message: Susan Cranford: "Tricky CASE statement"
- Previous message: Anith Sen: "Re: Need sql gurus' help with this sp!!!"
- In reply to: Tania: "Re: <<filegroup>> files from different hard disks:: tables(s) sto"
- Next in thread: Tom Moreau: "Re: <<filegroup>> files from different hard disks:: tables(s) sto"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 24 Dec 2004 14:37:51 -0500
They really are for two different purposes. The multiple files spread
across multiple disks do nothing for fault tolerance unless the disks are
really arrays. If you really care nothing about fault tolerance (Meaning
you are prepared to loose data and rebuild from scratch) then a Raid 0 is
the fastest. But for a system that is mostly read only like yours a Raid 5
will not be that much slower than a Raid 0 and you will have some fault
tolerance. You will loose one disks capacity to the parity in a Raid 5 over
a Raid 0 though. If you have th ability to create a Raid (either 0 or 5)
you are usually better off than trying to imitate that with multiple files
on multiple no-raided disks.
-- Andrew J. Kelly SQL MVP "Tania" <Tania@discussions.microsoft.com> wrote in message news:3B0FB8F1-7A01-4BBF-BCFD-C0E5842AA5FB@microsoft.com... > Thanks Andrew. This is such good news. > > (1) > Is this a better solution than just seperating (table clustered index > data) > from non (clustered index data) on seperate hard drives ? > BTW, We have an appx. 90% query, 10% dml system that accesses the > database. > > (2) > is RAID (0 or 5 ?) a better solution than just creating a primary > filegroup > that contains files on multiple hard drives ? > > Tania > > "Andrew J. Kelly" wrote: > >> No the data will get filled in equal proportions between the files in >> the >> file group if the files have the same amount of free space in them. The >> algorithm uses a proportion fill method that is based on the amount of >> free >> space in each of the files in the filegroup. So it is extremely >> important >> to start out with files of the same size. Then SQL Server will >> essentially >> stripe the data (or indexes) evenly across all the files in the group. If >> you were to shrink one file and it now is smaller and thus has less free >> space than the others it will get a lesser portion than the others when >> filling. Eventually they will all even out as far as the free space >> grows >> due to this fill method. But it is best to keep all files the same size >> when possible to spread the load evenly across all of them. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Tania" <Tania@discussions.microsoft.com> wrote in message >> news:8A58AFDA-8768-4073-A0F8-8F53AF236D87@microsoft.com... >> > Thanks alot Tom >> > >> > So, to clarify .... this is not like a RAID 0 simulation at all ? >> > >> > Unless the one file in the filegroup gets full, the chances for having >> > the >> > data for one index shared between the 2 hard disks is zero ? >> > >> > Basically, I do not have RAID, and I do not worry about fault >> > tolerance. >> > I just want data to be striped across more than one hard drive for >> > faster >> > IO >> > speed. >> > >> > Thanks >> > Tania >> > >> > >> > >> > "Tom Moreau" wrote: >> > >> >> Relax! When you create tables and indexes in a multi-file filegroup, >> >> they >> >> will go to both files. If one of the files is full and cannot grow, >> >> then >> >> the remaining file will get the table and index data. >> >> >> >> When you restore, the files will go to their original locations unless >> >> you >> >> specify a different location. Thus, you could have a database that >> >> was >> >> originally on two drives and then restore it onto another box with >> >> only >> >> one >> >> drive - as long as you have the space. >> >> >> >> >> >> -- >> >> Tom >> >> >> >> --------------------------------------------------------------- >> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> >> SQL Server MVP >> >> Columnist, SQL Server Professional >> >> Toronto, ON Canada >> >> www.pinnaclepublishing.com >> >> >> >> >> >> "Tania" <anonymous@discussions.microsoft.com> wrote in message >> >> news:104101c4e9c1$cee34fa0$a601280a@phx.gbl... >> >> Hi >> >> >> >> I am feeling very ignorant on these matters, so please >> >> help out a technologically disabled woman !! >> >> >> >> I want an easy way to store table data on different hard >> >> drives without worrying how that happens in the >> >> background: >> >> >> >> I created an additional filegroup 'fg_test' for my >> >> database and added 2 files to it. Each of the files is >> >> located on a different hard drive. >> >> Then I made fg_test my primary filegroup. >> >> >> >> My questions: >> >> (1) >> >> If I create new tables and indexes, on which hard drive >> >> will these tables and indexes end up ? >> >> (2) >> >> What happens in the case of a backup and complete restore >> >> for a database of which the primary filegroup points to 2 >> >> hard drives ? Where will the tables go ? >> >> >> >> Merry Christmas ... and thanks a million ! >> >> Tania >> >> >> >> >> >> >> >> >> >> >> >> >>
- Next message: Susan Cranford: "Tricky CASE statement"
- Previous message: Anith Sen: "Re: Need sql gurus' help with this sp!!!"
- In reply to: Tania: "Re: <<filegroup>> files from different hard disks:: tables(s) sto"
- Next in thread: Tom Moreau: "Re: <<filegroup>> files from different hard disks:: tables(s) sto"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
