Hi folks
I want to use 3 raid 1 for existing db. if i was creating this db new, I
would be able to use 3 different files for the same filegroup and that way my
db would use them all.
But I have an existing db which uses 1 data file and I want to migrate it so
it will start using 3 files. Do you know how to do that?
It must be the way. Otherwise, restore of any multi file db would be limited
to always restoring to 1 data file.
Restore statement clause 'move' produces error if I mention more than 1
'move to' file.
Another question: do you recommend from your experience to use multiple
files for the same filegroup?

> But I have an existing db which uses 1 data file and I want to migrate it
> so
> it will start using 3 files. Do you know how to do that?

You cannot change the number of files during a restore. You'll need to
first restore the database, add new empty files and then use DBCC SHRINKFILE
to migrate existing data to the new files.

> Another question: do you recommend from your experience to use multiple
> files for the same filegroup?

I usually let the the hardware take care of the striping (RAID 10) using a
single file unless the file is very large. In that case, multiple smaller
files are more manageable.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Gene." <Gene@discussions.microsoft.com> wrote in message
news:CB47AFC5-0895-401A-B193-041CCF2E319A@microsoft.com...
> Hi folks
> I want to use 3 raid 1 for existing db. if i was creating this db new, I
> would be able to use 3 different files for the same filegroup and that way
> my
> db would use them all.
> But I have an existing db which uses 1 data file and I want to migrate it
> so
> it will start using 3 files. Do you know how to do that?
> It must be the way. Otherwise, restore of any multi file db would be
> limited
> to always restoring to 1 data file.
> Restore statement clause 'move' produces error if I mention more than 1
> 'move to' file.
> Another question: do you recommend from your experience to use multiple
> files for the same filegroup?

I just wanted to make sure you understand that breaking a file into
three pieces and placing them on three different drives does not give
the same performance as RAID striping. The advantage of striping is
that the next block is always on the next drive. That means a scan of
a range keeps all the drives busy and productive.

I've never broken up a database from one to multiple files; I would
just be giving an answer from Books Online so you might as well do
that yourself.

Roy Harvey
Beacon Falls, CT

On Fri, 12 Sep 2008 05:02:00 -0700, Gene.
<Gene@discussions.microsoft.com> wrote:

>Hi folks
>I want to use 3 raid 1 for existing db. if i was creating this db new, I
>would be able to use 3 different files for the same filegroup and that way my
>db would use them all.
>But I have an existing db which uses 1 data file and I want to migrate it so
>it will start using 3 files. Do you know how to do that?
>It must be the way. Otherwise, restore of any multi file db would be limited
>to always restoring to 1 data file.
>Restore statement clause 'move' produces error if I mention more than 1
>'move to' file.
>Another question: do you recommend from your experience to use multiple
>files for the same filegroup?

Roy took care of the "Should I?" aspect. Let me comment on the backup part:

When you restore, the database you restore into need to have the same number of database files as
the one you produced the backup off. So, you cannot use backup/restore to get more or less database
files.

If you *really* want to do this, then add files to the existing database. Set the files to a good
reasonable size. Shrink the file that currently has data ("pushing pages" over to the other files),
down so that each file has about the same amount of pages. Then increase the size for the one you
shrank to it equals the size of the other files.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Gene." <Gene@discussions.microsoft.com> wrote in message
news:CB47AFC5-0895-401A-B193-041CCF2E319A@microsoft.com...
> Hi folks
> I want to use 3 raid 1 for existing db. if i was creating this db new, I
> would be able to use 3 different files for the same filegroup and that way my
> db would use them all.
> But I have an existing db which uses 1 data file and I want to migrate it so
> it will start using 3 files. Do you know how to do that?
> It must be the way. Otherwise, restore of any multi file db would be limited
> to always restoring to 1 data file.
> Restore statement clause 'move' produces error if I mention more than 1
> 'move to' file.
> Another question: do you recommend from your experience to use multiple
> files for the same filegroup?

Hi Roy

In another post Gene stated that he was not seeing what he expected when he
was benchmarking iops for various RAID 10 sets.
eg a 4 drive RAID 10 set only gave him 10-35% more iops than a 2 drive
mirror.

When I benched our systems way back, I only looked at Mbps. I did see a near
linear proportional increase until I got near the SCSI channels limit.

Could Gene have a point that RAID 10 may not increase iops in the same way
as Mbps? If so what would be the best configure for an OLTP system?

Paul



"Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> wrote in message
news:v7okc410s9ra84t9oil8dtjjuq27o42krh@4ax.com...
> I just wanted to make sure you understand that breaking a file into
> three pieces and placing them on three different drives does not give
> the same performance as RAID striping. The advantage of striping is
> that the next block is always on the next drive. That means a scan of
> a range keeps all the drives busy and productive.
>
> I've never broken up a database from one to multiple files; I would
> just be giving an answer from Books Online so you might as well do
> that yourself.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Fri, 12 Sep 2008 05:02:00 -0700, Gene.
> <Gene@discussions.microsoft.com> wrote:
>
>>Hi folks
>>I want to use 3 raid 1 for existing db. if i was creating this db new, I
>>would be able to use 3 different files for the same filegroup and that way
>>my
>>db would use them all.
>>But I have an existing db which uses 1 data file and I want to migrate it
>>so
>>it will start using 3 files. Do you know how to do that?
>>It must be the way. Otherwise, restore of any multi file db would be
>>limited
>>to always restoring to 1 data file.
>>Restore statement clause 'move' produces error if I mention more than 1
>>'move to' file.
>>Another question: do you recommend from your experience to use multiple
>>files for the same filegroup?

Hi all.
I find all of these feedbacks extremely full. As Paul mentioned already,
I am after IOPS. In an ideal world, app would tolerate micro delays but in my
case, I see apps producing errors. It carries out 50 merge replications which
do add a lot of oil into fire. Activity should grow 100% in next year. And
yes, this server is very OLTP - no prolonged intensive activities, but rather
sharp, high and frequent spikes in io consumption. So my goal is take care of
let say 50% of them. Highest spikes are around 1200 iops - pretty intensive
and definately beyond mirrored pair ability.
I am quite dissapointed with the results of io tests which show 20% of
improvement for 4d array to compare with 2 drive array and 35% of improvement
of 10dr. array to compare with 2dr. I hoped to see at least 400%, better 500%.
Dell is highly against of 5 drives raid 0 span mirrored to another 5 drives
span saying it's too dangerious, may be it is.
So I do want to spread activity of one very active db over 3 different
raids. I am quite impressed with ability to shrink db and get it over 3 files.
Does it really work? I never read about it anywhere. I will definately try
it today.
Thanks all.

"Tibor Karaszi" wrote:

> Roy took care of the "Should I?" aspect. Let me comment on the backup part:
>
> When you restore, the database you restore into need to have the same number of database files as
> the one you produced the backup off. So, you cannot use backup/restore to get more or less database
> files.
>
> If you *really* want to do this, then add files to the existing database. Set the files to a good
> reasonable size. Shrink the file that currently has data ("pushing pages" over to the other files),
> down so that each file has about the same amount of pages. Then increase the size for the one you
> shrank to it equals the size of the other files.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Gene." <Gene@discussions.microsoft.com> wrote in message
> news:CB47AFC5-0895-401A-B193-041CCF2E319A@microsoft.com...
> > Hi folks
> > I want to use 3 raid 1 for existing db. if i was creating this db new, I
> > would be able to use 3 different files for the same filegroup and that way my
> > db would use them all.
> > But I have an existing db which uses 1 data file and I want to migrate it so
> > it will start using 3 files. Do you know how to do that?
> > It must be the way. Otherwise, restore of any multi file db would be limited
> > to always restoring to 1 data file.
> > Restore statement clause 'move' produces error if I mention more than 1
> > 'move to' file.
> > Another question: do you recommend from your experience to use multiple
> > files for the same filegroup?
>
>

Hi Gene

I fully take on board what Tibor and Roy said but if you have time and a
spare server it will be interesting to see the results.
I have tried adding three files and emptying the original and it does move
the data evenly to the new files. I did this on my single disk test server
so no benchmarks here.

In the loosest possible terms I am guessing the stripe size would
effectively be the growth unit size of the three files.

You could still end up with hot spots and of this is not true striping by
any definition.

Did you have a chance to measure Mbps on your raids. Did this increase as
expected?
Did you split the mirror pairs over two channels when you set them up?

Paul

I would be careful not to over generalize from Gene's experience. For
the hardware he has the measurements he made are perfectly valid, but
different drives, or controller, or bus interface, might give
different results.

If you wanted to build an ideal system, I would start with something
along the following lines. Put the OS (C on a mirrored pair,
possibly including the paging file, or possibly with paging on another
pair. Put the logs on mirrored or RAID 10, possibly isolating each
high activity database's logs. Put the databases on RAID 10, possibly
multiple sets. Put tempdb off on its own as it can bottleneck very
badly. I would want controllers with caches, especially
battery-backed write caching. And I would want FAST drives, LOTS of
them. Note that with drives growing to such huge capacity today it is
tempting to only use enough drives to hold the data. That is a false
economy if you are looking for performance, you still need lots of
spindles spinning. The key bottleneck in database hardware is still
disk performance, and while transfer rates are increasing very nicely
due to higher density, seek times and rotational latency have not
improved at anything like the same rate. Spreading the load over lots
of drives is still the best answer.

Of course every configuration is a compromise, and there is plenty of
guesswork involved. If there is an existing server running the
database a careful analysis of the load can clarify things before
configuring the new server.

Roy Harvey
Beacon Falls, CT

On Fri, 12 Sep 2008 15:13:37 +0100, "Paul Cahill" <anon@anon.com>
wrote:

>Hi Roy
>
>In another post Gene stated that he was not seeing what he expected when he
>was benchmarking iops for various RAID 10 sets.
>eg a 4 drive RAID 10 set only gave him 10-35% more iops than a 2 drive
>mirror.
>
>When I benched our systems way back, I only looked at Mbps. I did see a near
>linear proportional increase until I got near the SCSI channels limit.
>
>Could Gene have a point that RAID 10 may not increase iops in the same way
>as Mbps? If so what would be the best configure for an OLTP system?
>
>Paul

Paul, here are the numbers:
mbps
1 .6
2 3
4 3.3
10 4.1

I used 64k stipe size - that's what recommended in best practices for using
raids for sql servrer.
I tested adding 3 files to db, shrinking file and trying to 'empty' original
file.
'Empying' of file did not work. So you have to create 1 less files you want
to end up with.
i also have few questions regarding it.
1. BOL for 2k sql mentioned that data distributed between in proportion to
size of the files. Is it still true for 2005. BOL for 2005 do not mention
anything in this regard.

Side point: you would probably have to shrink 2 times: 1st time just to
reduce file size as much as possible. Than create other files with sizes
equal to shrinked one and shrink again for data distribution.
2. is there any way to see how much of actual data in each file?

Thanks, Gene.

"Paul Cahill" wrote:

> Hi Gene
>
> I fully take on board what Tibor and Roy said but if you have time and a
> spare server it will be interesting to see the results.
> I have tried adding three files and emptying the original and it does move
> the data evenly to the new files. I did this on my single disk test server
> so no benchmarks here.
>
> In the loosest possible terms I am guessing the stripe size would
> effectively be the growth unit size of the three files.
>
> You could still end up with hot spots and of this is not true striping by
> any definition.
>
> Did you have a chance to measure Mbps on your raids. Did this increase as
> expected?
> Did you split the mirror pairs over two channels when you set them up?
>
> Paul
>
>

By the way, i use iometer for testing. As much as IO testing is popular
topic, I could not find anything other than sqlio from ms for that matter.
Is iometer accurate and trustable? sqlio produces really strange results
which show that raid 10 of 10 dr. performs in each aspect worse than mirrored
pair.
What are you guys using for io testing?

"Paul Cahill" wrote:

> Hi Gene
>
> I fully take on board what Tibor and Roy said but if you have time and a
> spare server it will be interesting to see the results.
> I have tried adding three files and emptying the original and it does move
> the data evenly to the new files. I did this on my single disk test server
> so no benchmarks here.
>
> In the loosest possible terms I am guessing the stripe size would
> effectively be the growth unit size of the three files.
>
> You could still end up with hot spots and of this is not true striping by
> any definition.
>
> Did you have a chance to measure Mbps on your raids. Did this increase as
> expected?
> Did you split the mirror pairs over two channels when you set them up?
>
> Paul
>
>

Paul, if you are after throughtput, you likely be better off with raid 5. I
really don't like to use it for 99% of situations because it only favors
reads, writes are horrible and get even worse with more concurent update
accesses.

"Paul Cahill" wrote:

> Hi Gene
>
> I fully take on board what Tibor and Roy said but if you have time and a
> spare server it will be interesting to see the results.
> I have tried adding three files and emptying the original and it does move
> the data evenly to the new files. I did this on my single disk test server
> so no benchmarks here.
>
> In the loosest possible terms I am guessing the stripe size would
> effectively be the growth unit size of the three files.
>
> You could still end up with hot spots and of this is not true striping by
> any definition.
>
> Did you have a chance to measure Mbps on your raids. Did this increase as
> expected?
> Did you split the mirror pairs over two channels when you set them up?
>
> Paul
>
>

> 1. BOL for 2k sql mentioned that data distributed between in proportion to
> size of the files. Is it still true for 2005.

Yes


> 2. is there any way to see how much of actual data in each file?

DBCC SHOWFILESTATS

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Gene." <Gene@discussions.microsoft.com> wrote in message
newsB6E2888-C27B-48AD-801A-E919429ECF67@microsoft.com...
> Paul, here are the numbers:
> mbps
> 1 .6
> 2 3
> 4 3.3
> 10 4.1
>
> I used 64k stipe size - that's what recommended in best practices for using
> raids for sql servrer.
> I tested adding 3 files to db, shrinking file and trying to 'empty' original
> file.
> 'Empying' of file did not work. So you have to create 1 less files you want
> to end up with.
> i also have few questions regarding it.
> 1. BOL for 2k sql mentioned that data distributed between in proportion to
> size of the files. Is it still true for 2005. BOL for 2005 do not mention
> anything in this regard.
>
> Side point: you would probably have to shrink 2 times: 1st time just to
> reduce file size as much as possible. Than create other files with sizes
> equal to shrinked one and shrink again for data distribution.
> 2. is there any way to see how much of actual data in each file?
>
> Thanks, Gene.
>
> "Paul Cahill" wrote:
>
>> Hi Gene
>>
>> I fully take on board what Tibor and Roy said but if you have time and a
>> spare server it will be interesting to see the results.
>> I have tried adding three files and emptying the original and it does move
>> the data evenly to the new files. I did this on my single disk test server
>> so no benchmarks here.
>>
>> In the loosest possible terms I am guessing the stripe size would
>> effectively be the growth unit size of the three files.
>>
>> You could still end up with hot spots and of this is not true striping by
>> any definition.
>>
>> Did you have a chance to measure Mbps on your raids. Did this increase as
>> expected?
>> Did you split the mirror pairs over two channels when you set them up?
>>
>> Paul
>>
>>

In the past I've used the old Sql Stress test, SiSoft Sandra, ATTO and
iometer.

When you create your raid 10's are you sure you are getting a stripe and not
a span of mirrors? Old Dell Perc3's would create a span if set up through
the windows UI.

As an aside, on our reporting server I get much better performance on
complex queries if I set our controller caches to 100% write 0% read.
We have battery backed up controllers


"Gene." <Gene@discussions.microsoft.com> wrote in message
news:07816E0E-C9FE-495E-AFDB-2419E972080F@microsoft.com...
> By the way, i use iometer for testing. As much as IO testing is popular
> topic, I could not find anything other than sqlio from ms for that matter.
> Is iometer accurate and trustable? sqlio produces really strange results
> which show that raid 10 of 10 dr. performs in each aspect worse than
> mirrored
> pair.
> What are you guys using for io testing?
>
> "Paul Cahill" wrote:
>
>> Hi Gene
>>
>> I fully take on board what Tibor and Roy said but if you have time and a
>> spare server it will be interesting to see the results.
>> I have tried adding three files and emptying the original and it does
>> move
>> the data evenly to the new files. I did this on my single disk test
>> server
>> so no benchmarks here.
>>
>> In the loosest possible terms I am guessing the stripe size would
>> effectively be the growth unit size of the three files.
>>
>> You could still end up with hot spots and of this is not true striping by
>> any definition.
>>
>> Did you have a chance to measure Mbps on your raids. Did this increase as
>> expected?
>> Did you split the mirror pairs over two channels when you set them up?
>>
>> Paul
>>
>>