Disk Management Help 1

[HestonJames]

Good Morning Guys,

I'm looking for some advice on how best to manage my disks and partitions on the server. It's only a small box, at the moment we just have a single physical disk with a single partition which contains everything, windows, applications, sql server and all the databases.

My understanding is that I should really have my DB Files and Logs on a different disk from the OS and Server, is that correct?

I have a spare physical disk here which we can stick in, how would you suggest I partition the two disks and locate my database data and log files?

This is all new territory to me so I appreciate the help.

Cheers chaps,

Heston

 

[hmckillop]

There are things you do in theory and there are things you do in practice, they all depend on a whole set of variables.

What you state around seperation of log files, and db files on different disks, even partitioning data within different logical and physical filegroups and in theory it is 100% correct.
However, if your db is small e.g. < 50Mb <100Mb etc. and your user base is small and infrequent, then the effort required to setup somethings is inpractical, particularly where you may already have good backup DR processes.

In essence what I am asking is, can you provide more information regarding the size of DB, user load (e.g. 5 users all day usage), data sizes in queries etc

This information may help provide a more complete answer to your query.

Some nice links include

http://blogs.msdn.com/buckwoody/arc...st-practices-setting-a-default-filegroup.aspx

http://sqlcat.com/top10lists/archiv...performance-issues-for-oltp-applications.aspx

http://msdn.microsoft.com/en-us/library/dd758814.aspx

"I'm living so far beyond my income that we may almost be said to be living apart

 

[HestonJames]

Hey hmckillop, thanks for the reply my man, I appreciate it.

I'll do some reading through those links this afternoon, after a quick scan just now they look to be very interesting, thanks.

To answer your questions regarding DB size and traffic. The database is currently around 1.3GB in size (data/logs combined). We have around 50 clients at present all posting around 5-10 rows of data to the database every minute (average over a 24 hour period). These are the real frequent calls to the database, other calls are less frequent but are rather complex report queries which are generally run on lumps of data for a day/week/month or year.

I don't putting in the leg work to get this right as the database will continue to grow at an ever accelerating rate as we start to take more clients onto the network.

Does that offer any help with understanding the scenario?

Thanks,

Heston

 

[hmckillop]

Hello Heston

First recommendation would be to seperate your log file to your new disc, this means the contention on the existing disc should go down.

Based on your current workload I would think the server should cope comfortably with the throughput you listed, however if it becomes a problem and you have a single set of tables where the issue lies, you may wish to split these to seperate discs via partitioning.

Your existing disc throughput can be tested with a handy tool called SQLIO, which simulates IO throughput and performance similiar to SQL Server would do.

http://www.microsoft.com/Downloads/...5B-84E4-4F24-8D65-CB53442D9E19&displaylang=en

If you are looking for a long term solution, you may wish to look at partitioning on seperate filegroups and physical files, each being contained on their own discs

Like anything to do with SQL, its all about testing and doing whats best for your particularly circumstances.

If you need specific pointers for anything please post back,

cheers

"I'm living so far beyond my income that we may almost be said to be living apart

 

[HestonJames]

Hey hmckillop,

Sorry for the late reply, I've been away for a couple of days. Thanks a great deal for the pointers. I'll look at getting this second physical disk up and running on a development box and do a little testing, if everything works out then I'll make the changes to production as well.

Cheers my man,

Heston