Distributing primary filegroup into four drives

Question

Distributing primary filegroup into four drives

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
January 26, 2010 at 4:18 pm

#229388

Is it possible to distribute Primary file group into four drives. We have a 3rd party application which creates a Database dynamically on Primary file group.
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Yes you could. You would need to create 3 more files for the primary filegroup, with each one created on a different drive / lun / raid.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JeremyE SSCoach Points: 16212 More actions · Answer 2

Yes, you just need to create the data files that belong to the filegroup on each of the 4 drives.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Existing data won't move, but as you add new data/objects, they will distribute across the four drives.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Steve Jones - Editor (1/26/2010)
Existing data won't move, but as you add new data/objects, they will distribute across the four drives.

True. I think that you can manipulate that, however. If you drop and recreate your clustered indexes (almost as if you were going to move to a different filegroup), I believe the data will re-distribute. The trickier part in this scenario is the moving of BLOBs. Those require re-creating the table with the filegroup designation. This process though will require downtime and a good backup of your data.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 5

balbirsinghsodhi

SSCrazy Eights

Points: 9210

January 26, 2010 at 4:59 pm

#1109006

Great.. That's what I want.

Thank you guys.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 6

I was just wondering about that after I posted. Need to check if a rebuild of the CI does distribute across files.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

balbirsinghsodhi (1/26/2010)
Great.. That's what I want.
Thank you guys.

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Steve Jones - Editor (1/26/2010)
I was just wondering about that after I posted. Need to check if a rebuild of the CI does distribute across files.

I am trying to recall from memory having done this within the past few months. If memory serves correct - yes it does. I would need to go and retest it though.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JeremyE SSCoach Points: 16212 More actions · Answer 9

Rebuilding the clustered index does distribute the data to the additional data files however if you want the data to be filled equally across the data files you have to script out all the objects and data then re-import the data.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Thanks Edogg.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 11

Looks like it is little bit tricky when we move the existing data to the same file group. I did move existing data to different file groups but neve did on the same file group.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

If you are comfortable moving the data to a different filegroup, I think you would be OK doing it within the same filegroup. It is really the same process in moving your Clustered Indexes, heaps and blobs. Blobs are the worst part when moving the data to any filegroup - recreate the tables is a must. Heaps can be moved by simply creating a CI on the table specifying the correct filegroup.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 13

Thanks Jason..

I don't have a control on the creation of Database because it's a 3rd party tool so when database gets created, it will use the default primary filegroup and Filegroups are database specific not Server specific.

These databases gets created dynamically and uses the primary filegroup. so in this case I have to do another process to distribute the data among files after the database get created.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

NP.

If you have the Primary already split into multiple files - no problems at all. I think you would have no problem being able to transfer the data within the same filegroup as well. Good luck.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events