Can move a file outside of PRIMARY to a new file group?

So I messed up and didn't create file groups for my scheme
partitioning strategy... can I create a file group and then take a
file out of PRIMARY and move it to SAN1_FILEGROUP for example?
I'm thinking take the database OFFLINE
and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
Thanks
Erik
No, that is because SQL Server don't place objects on a certain file, it places objects on a
filegroup. So, create a new filegroup, add file(s) to it, move the objects, shrink the original file
using (EMPTYFILE) and then drop that file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik G" <info@.fdaregulatory.com> wrote in message
news:1174936993.128302.101110@.l75g2000hse.googlegr oups.com...
> So I messed up and didn't create file groups for my scheme
> partitioning strategy... can I create a file group and then take a
> file out of PRIMARY and move it to SAN1_FILEGROUP for example?
> I'm thinking take the database OFFLINE
> and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
> Thanks
> Erik
>

Can move a file outside of PRIMARY to a new file group?

So I messed up and didn't create file groups for my scheme
partitioning strategy... can I create a file group and then take a
file out of PRIMARY and move it to SAN1_FILEGROUP for example?
I'm thinking take the database OFFLINE
and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
Thanks
ErikNo, that is because SQL Server don't place objects on a certain file, it pla
ces objects on a
filegroup. So, create a new filegroup, add file(s) to it, move the objects,
shrink the original file
using (EMPTYFILE) and then drop that file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik G" <info@.fdaregulatory.com> wrote in message
news:1174936993.128302.101110@.l75g2000hse.googlegroups.com...
> So I messed up and didn't create file groups for my scheme
> partitioning strategy... can I create a file group and then take a
> file out of PRIMARY and move it to SAN1_FILEGROUP for example?
> I'm thinking take the database OFFLINE
> and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
> Thanks
> Erik
>

Can move a file outside of PRIMARY to a new file group?

So I messed up and didn't create file groups for my scheme
partitioning strategy... can I create a file group and then take a
file out of PRIMARY and move it to SAN1_FILEGROUP for example?
I'm thinking take the database OFFLINE
and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
Thanks
ErikNo, that is because SQL Server don't place objects on a certain file, it places objects on a
filegroup. So, create a new filegroup, add file(s) to it, move the objects, shrink the original file
using (EMPTYFILE) and then drop that file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik G" <info@.fdaregulatory.com> wrote in message
news:1174936993.128302.101110@.l75g2000hse.googlegroups.com...
> So I messed up and didn't create file groups for my scheme
> partitioning strategy... can I create a file group and then take a
> file out of PRIMARY and move it to SAN1_FILEGROUP for example?
> I'm thinking take the database OFFLINE
> and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
> Thanks
> Erik
>

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.
>
>

Can I run both a SQL 2000 instance and SQL 2005 Instance on same two node cluster?

I'd like to run a SQL 2000 instance and a SQL2005 instance on the same
two node windows cluster.
Each would have it's own disk groups.
Basically mixed versions of instances on a cluster.
Does anyone know where I can get Microsoft's official statement on
this?
Has anyone done this before?
Yes. This is allowed and supported under most scenarios, but there are some
limitations.
The Microsoft SQL Server support policy for Microsoft Clustering
http://support.microsoft.com/kb/327518
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sanctus" <mchi55@.hotmail.com> wrote in message
news:1174503282.467053.80730@.l77g2000hsb.googlegro ups.com...
> I'd like to run a SQL 2000 instance and a SQL2005 instance on the same
> two node windows cluster.
> Each would have it's own disk groups.
> Basically mixed versions of instances on a cluster.
> Does anyone know where I can get Microsoft's official statement on
> this?
> Has anyone done this before?
>