Data file structure 1

[icemel]

Hi,

I was wondering if anyone could provide me with best practices or articles on the data/log/index file layout and directory naming conventions for SQL Server.

Not so much where to put indexes and tables, RAID and all that, but more generally... the hierarchical layout of datafiles, etc for naming purposes and and ease of administration.

Thanks

 

[mrdenny]

I use the Microsoft folder structor. x:\MSSQL\MSSQL\Data. All mdf, ldf and ndf files are in that folder. The difference is what drive they are on.

For Files in the Primary file group I stick with the Microsoft names (Database_Data.mdf then Database_Data_01.ndf, Database_Data_02.ndf, etc).

For other file groups (and I try not to put anything in the primary file group except for the system objects) I do Database_FileGroup_Data_01.ndf, Database_FileGroup_Data_02.ndf, etc. File Groups I usually name DATA_01 or INDEX_01. If it's for something more specific then that I'll get more specific as needed.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[icemel]

I use the Microsoft folder structor. x:\MSSQL\MSSQL\Data. All mdf, ldf and ndf files are in that folder. The difference is what drive they are on.

Not sure how you do this? I was wondering if you could explain this a little more. Especially, "The difference is what drive they are on." if they are all on the "x" drive.

Thanks

 

[mrdenny]

x is any drive.

My typical layout will loko something like this.
Data files in e:\mssql\mssql\data
Log Files in l:\mssql\mssql\data
TempDB in t:\mssql\mssql\data

If I put indexes on a seperate drive the'll go in i:\mssql\mssql\data.

As the data drives need to be expanded I'll add:
f:\mssql\mssql\data
g:\mssql\mssql\data
etc

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[icemel]

Have a question, why list mssql\mssql twice?

 

[mrdenny]

So that if you have more than once instance installed do don't clutter the drive root.

I've got several servers with more than one instance. They look like this.
e:\mssql\mssql$name1\data
e:\mssql\mssql$name2\data

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com