Can Indexes LIve on another server?

I know I can split up data and indexes onto different file groups, but can I
then move the index file group to another server? if so, what are the pros &
cons?
thanks in advance!!Yes, you can certainly have a filegroup which lives on another physical
resource. There are two very strong divantages to this:
(a) network latency unless you are on gig+ ethernet...
(b) reliance on multiple machines to be up for one server to be considered
"available." How do you plan to recycle machines after applying a service
pack or hotfix?
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>I know I can split up data and indexes onto different file groups, but can
>I
> then move the index file group to another server? if so, what are the pros
> &
> cons?
> thanks in advance!!|||what are the performance gains/issues?
I am looking at splitting up a 1 tb data file into a 300gb data file and a
700gb index file. I have a gb network, but I am worried about administrating
mutiple machines.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, you can certainly have a filegroup which lives on another physical
> resource. There are two very strong divantages to this:
> (a) network latency unless you are on gig+ ethernet...
> (b) reliance on multiple machines to be up for one server to be considered
> "available." How do you plan to recycle machines after applying a service
> pack or hotfix?
>
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>
>|||Why not just additional drive(s) to the same server?
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:29753642-4A9E-4DC3-975B-578EBFD963AC@.microsoft.com...
> what are the performance gains/issues?
> I am looking at splitting up a 1 tb data file into a 300gb data file and a
> 700gb index file. I have a gb network, but I am worried about
> administrating
> mutiple machines.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Incorrect. olny local drives can be used for this purpose or special SAN
that you probably don't have.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Yes, you can certainly have a filegroup which lives on another physical
> resource. There are two very strong divantages to this:
> (a) network latency unless you are on gig+ ethernet...
> (b) reliance on multiple machines to be up for one server to be considered
> "available." How do you plan to recycle machines after applying a service
> pack or hotfix?
>
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>|||And only enterprize edition supports VIA SANs. others will work if a SAN
drive is presented by SAN driver as local.
however another tip here is:
BOL
Note When running on Windows NT, SQL Server performance can be improved
further if the databases are created on disks formatted using NTFS and,
specifically, 64-KB extent sizes. In Windows 2000, setting the file system
cluster size to 64-KB extent size using the /A format option will also
improve performance. For more information about formatting an NTFS disk, see
the Windows NT or Windows 2000 documentation.
"Farmer" <someone@.somewhere.com> wrote in message
news:ez99%23ilrFHA.3068@.TK2MSFTNGP14.phx.gbl...
> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||Am I reading that you have 700gb of indexing on 300gb of tables?
Consider if you are indexing on too many columns:
http://msdn.microsoft.com/library/d.../>
indexes.asp
Also, look into the extent of index fragmentation:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:29753642-4A9E-4DC3-975B-578EBFD963AC@.microsoft.com...
> what are the performance gains/issues?
> I am looking at splitting up a 1 tb data file into a 300gb data file and a
> 700gb index file. I have a gb network, but I am worried about
> administrating
> mutiple machines.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
Uh, we have multiple Clariions. But thanks for the tip.|||And here's the full story on creating database files on network drives.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Farmer" <someone@.somewhere.com> wrote in message news:ez99%23ilrFHA.3068@.TK2MSFTNGP14.phx.
gbl...
> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||Well, we are currently delivering on Del 2650's with a powervault loaded wit
h
16 - 15k 73gb hard drives configured into one raid-5 drive. The search times
on this 1tb table are very fast, but I am looking to scale it out. I would
like to seperate the indexes and the data onto different disks, but then
started wondering if I could store the data on one server, and say store the
indexes on 3-4 other servers that point to the data servers.
My research has found that 70-80% of query times is caught up in the indexes
looking for the right keys to use to go get the data with. I have 26 indexes
built on my table that covers 95% of the queries uses according to our log
files. I would like to spread out the work done in the index search to a fe
w
specialized servers if possible. I dont think that MS can do this, but I can
probably build something myself that could.
thanks!!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Uh, we have multiple Clariions. But thanks for the tip.
>
>