Data directories configuration

Question

Data directories configuration

SQL Galaxy

One Orange Chip

Points: 29472
More actions
April 16, 2011 at 9:48 am

#389301

Hi,
Server HP G4 Model with RAID5 configuration. 4GB RAM
we are planning create 5 different disk partition for best practice and reduce IO latency, improve performance of medium loading database as below configuration.
1. Drive -"C" -Total 80GB- For Operating System (win server 2008 Enterprise edition)
2. Drive - "D" -Total 350GB- For Installing SQL server 2008 Enterprise Edition Package and storing User data file and system data file.
3. Drive - "E "-Total 350GB - For backup purpose (user DB, System DB and Transaction Log backup)
4. Drive -"F" - Total 350 GB - For user database Transaction Log(all .LDF files)
5.Drive - "G" - Total 100GB - For Tempdb (mdf, ldf files)
Could any one suggestion me, above configuration sufficient for better performance and General practice of SQL SERVER 2008?
Thanks
ananda

Viewing 6 posts - 1 through 5 (of 5 total)

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

_Beetlejuice Ten Centuries Points: 1064 More actions · Answer 1

Hi

Are the 5 volumes created on the same physical disk or seperate disks? Are the disks locally attached or connected to a SAN?

Best Regards

Chris

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Thanks for reply...

1. All the 5 vloumes on created same 6 physical disk and single Array controler RAID 5, on same server

2. Not attached in SAN storage box.

rgds

ananda

_Beetlejuice Ten Centuries Points: 1064 More actions · Answer 3

If all of the volumes are on the same physical disk you will not get any pefromance benefit out of seperating them.

If you are just after placing them on separate volumes for best practice you could use the following configuration:

1 Volume\drive for system data files and system transaction logs (not includng tempdb)

1 volume\drive for user data files

1 volume\drive for user transaction log files

1 volume\drive for tempdb data files - Create a seperate tempdb data file for each physical processor.

1 volume\drive for tempdb log file

If this is a production Server never put SQL Backups on a shared local disk on the same server - if you lose the disk you have lost your databases and the backups. You are better off backing up accross the network to a remote location.

Best Regards

Chris

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Thanks for your valuable information. now I understood these configuration between SAN and same physical HD on same server.

one of the production database server as below, already I had done disk configuration on same physcial HD in server (Not SAN),

1. Drive 'C' -> OS purpose

2. Drive 'D' -> system data file, system log file & user data file

3. Drive 'E' -> Tempdb (both data & log file)

4. Drive 'F' -> Tlog (user database .ldf file)

5. Drive 'G' -> Backup (for all system & user database & Tlog).

As you told these type drive configuration on same physical disk, there no effect performance and IO latency. ( this applicable only on SAN box)

Now I cannot modified these drive confifuration because already done. please suggestion me what I can do? or let it leave this above configuration.

Thanks

ananda

Steve T SSCommitted Points: 1994 More actions · Answer 5

Based on what you are saying you have all the partitions on one physical drive or a set of physical drives(raid).

What you can do is add more drives to the system to separate the sql data directories. Let assume you give one new drive to seperate sql from the original drive. You can then shutdown sql and create the new partitions. Copy the data over to the newly created partitions. Rename the old paritions or remove their drive letters. Reassign the new drives and ensure that the security is copied over. Use robocopy for example with the security options. Restart sql server and it should come up like normal.

In your example, you don't have a separate drive for the user databases. You should add this.

1. Drive 'C' -> OS purpose ---stays on the old drive

2. Drive 'D' -> system data file, system log file & (Move user data files) ---stays on the old drive

3. Drive 'E' -> Tempdb (both data & log file) ---move to new E: drive

4. Drive 'F' -> Tlog (user database .ldf file) ---move to new F: drive

5. Drive 'G' -> Backup (for all system & user database & Tlog). ---move to new G drive. Move user databases to H: and tlogs to F:

6. Drive 'H' -> User Databases (Don't forgot to reconfigure sql so that new databases will be created here.)

If you actually want it to perform well also, these new drives would need to be some type of raid setup. I'm not sure what type of server you have, but a locally attached storage unit would help. This is similiar to a SAN, but attached directly to the server only.